March 13, 2007 at 8:49 am
Knowing that the MAX() function eliminates NULLS before processing, I need to find a way to query a table of date records to return the MAX(date_out) for a client if there are no rows for the client with date_out = NULL, or return NULL if there is a row for the client with date_out = NULL. The table (simplified) looks something like this:
clientID date_in date_out
1 1/2/2007 1/21/2007
1 1/21/2007 2/4/2007
1 2/4/2007 NULL
2 1/5/2007 2/10/2007
2 2/10/2007 2/24/2007
etc.
So for each client ID I want to return the MAX(date_out) if there is no NULL date_out for that clientID or return NULL if there is a null. The result set for this data should be:
clientID min_date_in max_date_out
1 1/2/2007 NULL
2 1/5/2007 2/24/2007
I'm sure there is a simple solution but I haven't stumbled across it yet.
Thanks in advance
Tim
March 13, 2007 at 9:10 am
Select ClientID,
min_date_in,
Case When max_date_out = '01 Jan 2079' Then Null Else max_date_out End
From (
Select ClientID,
Min(date_in) As min_date_in,
Max(IsNull(date_out, '01 Jan 2079')) As max_date_out
From YourTable
Group By ClientID
) dt
March 13, 2007 at 9:15 am
Hi ,
Try this solution.........
select clientid , min(date_in) ,
case when max(isnull(date_out,GETDATE()))= getdate()
THEN NULL
ELSE max(date_out)
END [MAX_DATE]
from client
group by clientid
Regards
March 13, 2007 at 9:46 am
thanks for both solutions. I'll play around to see which works/performs best.
Tim
March 13, 2007 at 10:13 am
Amit:
Your solution returns the max(date_out) value, not the null value where present. The ISNULL() clause evaluates properly, but it looks like as soon as you throw the MAX() in front of it the NULL value generated is discarded, just like in a plain MAX() aggregate statement.
Tim
March 13, 2007 at 8:32 pm
COUNT does not count NULL values as well.
Use it:
SELECT CASE WHEN COUNT(date_out) < COUNT(*) THEN NULL ELSE MAX(date_out) END
_____________
Code for TallyGenerator
March 14, 2007 at 1:06 am
But it's better to include NULL interpretation in your script:
DECLARE @InfiniteDate datetime
SET @InfiniteDate = '9999-12-31 15:15'
SELECT date_int, NULLIF(MAX(ISNULL(date_out, @InfiniteDate)), @InfiniteDate)
FROM ...
_____________
Code for TallyGenerator
March 14, 2007 at 2:14 am
Probably from New York to Seattle via New Orleans, but can you try something like this:
CAST the date as a char or varchar, then query for the minimum lenght.
If the minimum length is 0, then select Null, else select the date.
HTH
Jurriaan
March 18, 2007 at 3:35 pm
This one uses EXISTS and UNION. It works for this data set, and I think it works for the general problem set.
DECLARE @YourTable TABLE (
clientID int,
date_in datetime,
date_out datetime
)
INSERT @YourTable
SELECT 1, '1/2/2007', '1/21/2007' UNION ALL
SELECT 1, '1/21/2007', '2/4/2007' UNION ALL
SELECT 1, '2/4/2007', NULL UNION ALL
SELECT 2, '1/5/2007', '2/10/2007' UNION ALL
SELECT 2, '2/10/2007', '2/24/2007'
SELECT
clientID,
MIN(date_in) AS 'min_date_in',
MAX(date_out) AS 'max_date_out'
FROM @YourTable Y
WHERE NOT EXISTS (
select 1 from @YourTable Y1
where Y1.clientID = Y.clientID
and Y1.date_out is null
)
GROUP BY clientID
UNION ALL
SELECT
clientID,
MIN(date_in) AS 'min_date_in',
NULL AS 'max_date_out'
FROM @YourTable Y
WHERE EXISTS (
select 1 from @YourTable Y1
where Y1.clientID = Y.clientID
and Y1.date_out is null
)
GROUP BY clientID
ORDER BY clientID
John Hopkins
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply