Prev: parse uri
Next: NTUserName not working in Profiler as a filter.
From: amj1020 on 1 Mar 2010 17:58
Here is my table:
CREATE TABLE [dbo].[FD__PROGRAM_CLIENT](
[ClientKey] [int] NULL,
[PgmKey] [int] NULL,
[Date_Admit_Program] [datetime] NULL,
[Date_Discharged_Program] [datetime] NULL,

CONSTRAINT [PK_SYS_FD__PROGRAM_CLIENT] PRIMARY KEY CLUSTERED

SAMPLE DATA:

609 6 2008-05-27 00:00:00.000 2008-06-02 00:00:00.000
459 4 2008-07-30 00:00:00.000 2008-08-12 00:00:00.000
605 6 2008-06-17 00:00:00.000 2008-06-30 00:00:00.000
607 6 2008-04-23 00:00:00.000 NULL
1671 4 2008-07-15 00:00:00.000 2008-07-16 00:00:00.000
1757 6 2008-05-17 00:00:00.000 NULL
1757 6 2008-07-17 00:00:00.000 2008-07-17 00:00:00.000


I am trying to select clients that are in a program on a certain day.

I need to see who is in house for the May 2008 so my results should
be:
609 6 2008-05-27 00:00:00.000 2008-06-02 00:00:00.000
607 6 2008-04-23 00:00:00.000 NULL
1757 6 2008-05-17 00:00:00.000 NULL

Can someone hlep me with this?
From: Hugo Kornelis on 1 Mar 2010 19:24
On Mon, 1 Mar 2010 14:58:24 -0800 (PST), amj1020 wrote:

>Here is my table:
>CREATE TABLE [dbo].[FD__PROGRAM_CLIENT](
> [ClientKey] [int] NULL,
> [PgmKey] [int] NULL,
> [Date_Admit_Program] [datetime] NULL,
> [Date_Discharged_Program] [datetime] NULL,
>
> CONSTRAINT [PK_SYS_FD__PROGRAM_CLIENT] PRIMARY KEY CLUSTERED

Hi amj1020,

Why do you declare ClientKey as nullable when it's a primary key? That
will override the NULL constraint.

Why are PgmKey and Date_Admit_Program nullable? How to interpret a NULL
admittance date?

From the rest of your post, I assume that a null discharge date implies
"still in the program". I'll assume that the other columns in your
actual table are in fact NOT NULL (as the sample data indicates).

(snip)
>I am trying to select clients that are in a program on a certain day.
>
>I need to see who is in house for the May 2008 so my results should
>be:

Do you need to know who's in the program on one day, or during a period?
Your first sentence implies the first; your second the latter.

To get all people in the program in May 2008, use this:

SELECT ClientKey, PgmKey, Date_Admit_Program, Date_Discharged_Program
FROM dbo.FD__PROGRAM_CLIENT
WHERE Date_Admit_Program < '20080601'
AND COALESCE(Date_Discharge_Program, '99991231') >= '20080501';

The logic here is that a client is in the program in May 2008 if (s)he
was admitted before June 2008 and not discharged before May 2008 (or not
discharged at all).

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
From: Plamen Ratchev on 2 Mar 2010 10:20
amj1020 wrote:
> Can you tell me what COALESCE does?

http://msdn.microsoft.com/en-us/library/ms190349.aspx

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: parse uri
Next: NTUserName not working in Profiler as a filter.