Prev: Query Help.
Next: Clustered and Keys
From: Erich93063 on 5 Aug 2010 17:33
I work for a company who sends traveling nurses out on jobs across the
country. They could have had several jobs with us over time and each
job they go on gets inserted into a table with the Start Date of when
they started each particular job. In this table, there is also a field
that is an ID of a "termination reason" and that gets populated if the
nurse gets fired or quits. So to make this easy, lets just say, if
they got fired form a job they go on, a termination code gets inserted
into their job record of "1" and if they quit, the termination code is
"2". If they do NOT get fired or quit the term code gets populated
with "3" meaning everything was fine with that assignment. This is on
a per job basis.

SO, I have a request on my plate where I need to figure out the
"anniversary date" of a given nurse. The criteria of the anniversary
date is defined as the employee's first day of employment on their
first assignment with our company. So its the start date of the first
assignment that we sent them on. So if this was easy, I would just do
SELECT min(startDate), but of course this isn't easy. :-) The
anniversary date should automatically adjust if the nurse is
terminated or quits an assignment or if the nurse has not worked in 24-
months. If a nurse quits or is terminated (has a termination code of
1 or 2) from an assignment or has not worked for 24-months, their
anniversary date should be the first day of employment on their next
assignment FOLLOWING the termination, quitting or not having worked
for 24 months.

Is this possible to do solely in SQL? Or would I need to do some
processing on the front end? Let me know if you need clarification on
anything.

THANKS!!!!!
From: Eric Isaacs on 5 Aug 2010 21:15
It's possible in SQL Server, but the question is whether the answer is
available in the collected data. Can they work more than one job at a
time? Is there an end date for each job in the table, or just a code
for the ending and a start date? If they can work more than one job
at a time and there is no end date, I would say it's not possible
because of your data structure. You would have to assume the last
start date, but if I start one job and then start a second job the
next day, complete the second job first (and there is no end date) in
the table and then quit. You don't know when I quit, if it was after
the second job completed or before the second job started.

Providing DDL of your table would be helpful in helping us help you.

-Eric Isaacs


From: --CELKO-- on 5 Aug 2010 22:58
"A problem well stated is a problem half solved." -- Charles F.
Kettering

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules. Temporal data should use ISO-8601
formats. Code should be in Standard SQL as much as possible and not
local dialect.

Sample data is also a good idea, along with clear specifications. It
is very hard to debug code when you do not let us see it. If you want
to learn how to ask a question on a Newsgroup, look at:
http://www.catb.org/~esr/faqs/smart-questions.html

What leaps out of your vague narrative is that you have no idea when
an assignment ends, only when it starts.
From: Erich93063 on 6 Aug 2010 12:21
Hi guys, thanks for your input. To answer your questions, no the
nurses cannot work two assignments at the same time and yes there is
an end date. I thought about posting DDL when I first submitted this,
but because I thought I was only dealing with two fields in this
query, I figured posting the DDL would just complicate things as there
are over 100 fields in the table and the query wouldn't be joining on
any other table. The only two fields that I am concerned with are
StartDate and TermCode. The table name is called assignments. There is
an EndDate field, but since they cant work two assignments at the same
time, I don't think we need to worry about that one. Let me know if
you think I should still post the DDL.

THANKS!!

On Aug 5, 7:58 pm, --CELKO-- <jcelko...(a)earthlink.net> wrote:
> "A problem well stated is a problem half solved." -- Charles F.
> Kettering
>
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. If you know how, follow ISO-11179 data element naming
> conventions and formatting rules. Temporal data should use ISO-8601
> formats. Code should be in Standard SQL as much as possible and not
> local dialect.
>
> Sample data is also a good idea, along with clear specifications. It
> is very hard to debug code when you do not let us see it. If you want
> to learn how to ask a question on a Newsgroup, look at:http://www.catb.org/~esr/faqs/smart-questions.html
>
> What leaps out of your vague narrative is that you have no idea when
> an assignment ends, only when it starts.

From: Erland Sommarskog on 6 Aug 2010 17:52
Erich93063 (erich93063(a)gmail.com) writes:
> Hi guys, thanks for your input. To answer your questions, no the
> nurses cannot work two assignments at the same time and yes there is
> an end date. I thought about posting DDL when I first submitted this,
> but because I thought I was only dealing with two fields in this
> query, I figured posting the DDL would just complicate things as there
> are over 100 fields in the table and the query wouldn't be joining on
> any other table. The only two fields that I am concerned with are
> StartDate and TermCode. The table name is called assignments. There is
> an EndDate field, but since they cant work two assignments at the same
> time, I don't think we need to worry about that one. Let me know if
> you think I should still post the DDL.

You would have to post the CREATE TABLE for all 100 columns. But you
could have posted the CREATE TABLE with the pertinent columns and
also INSERT statements with sample data, and the expected results
given the sample. That would help to clarify what you are asking for,
and it would be simple to develop a tested query.

Now, you did not do this, but if I understand this correctly, this
maybe work:

SELECT a.nurseid, min(a.startdate)
FROM nurses a
WHERE a.startdate > coalesce((SELECT MAX(b.startdate)
FROM nurses b
WHERE b.nurseid = a.nurseid
AND b.termcode IN (1, 2), '19000101')
AND a.startdate >=
coalesce((SELECT MAX(b.startdate)
FROM nurses b
WHERE b.nurseid = a.nurseid
AND datediff(MONTH,
(SELECT MAX(c.enddate)
FROM b.nurseid = c.nurseid
WHERE c.enddate < b.startdate),
b.startdate) > 24), '19000101')
GROUP BY a.nurseid

It is completely untested.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 |  Next  |  Last
Pages: 1 2
Prev: Query Help.
Next: Clustered and Keys