max and min dates

Question

Post reply

max and min dates

sm86

SSC Enthusiast

Points: 114
More actions
September 25, 2024 at 5:06 pm
Go to Answer
#4462369

Hi I am trying to get the first and last days for the following where date_next_compare is less than 28 but having more than that would mean the max date for that range. Sorry if isn't clear.
I currently have this:

I want to see two rows like below.
I tried doing min and max where date_next_compare is less than 28 but it gives me 1/4/22 and 12/27/22. Thanks.
CREATE TABLE ADMITS
(
ID_NUM INT
,SERVICE_DATE DATE NULL
,NEXT_SERV DATE NULL
,DATE_NEXT_COMPARE INT NULL
)
INSERT INTO ADMITS (ID_NUM, ,SERVICE_DATE ,NEXT_SERV,DATE_NEXT_COMPARE)
VALUES
(770577602, '1/4/2022', '1/7/2022',3)
,(770577602, '1/7/2022', '1/10/2022',3)
,(770577602, '1/10/2022', '12/19/2022',343)
,(770577602, '12/19/2022', '12/20/2022',1)
,(770577602, '12/20/2022', '12/21/2022',1)
,(770577602, '12/21/2022', '12/27/2022',6)
,(770577602, '12/27/2022', NULL,NULL);

drew.allen

SSC Guru

Points: 76996

September 25, 2024 at 5:47 pm

Answer

#4462387

You should always check your scripts before posting them on the Internet. You have an extra comma in your INSERT clause. You should also make sure that others don't need to clean up after you. The easiest way to do this is to use temp tables or table variables instead of permanent tables.

This gives your expected results:

WITH Admit_Groups AS
(
    SELECT *, COUNT(CASE WHEN a.DATE_NEXT_COMPARE > 28 THEN 1 ELSE NULL END) OVER(PARTITION BY a.ID_NUM ORDER BY a.SERVICE_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS Grp
    -- Using COUNT() instead of SUM() to prevent a NULL value for the first record
    FROM #ADMITS AS a
)
SELECT ag.ID_NUM, MIN(ag.SERVICE_DATE) AS Service_Date, MAX(ag.SERVICE_DATE) AS Next_Service
FROM Admit_Groups AS ag
GROUP BY ag.ID_NUM, ag.Grp
ORDER BY ag.ID_NUM, ag.Grp;

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

sm86 SSC Enthusiast Points: 114 More actions · Answer 1

Thank you and sorry about extra comma. I haven't seen ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING before. I'll have to see if I can find any information on them.

pietlinden SSC Guru Points: 63506 More actions · Answer 2

look up windowing functions. new since 2012 at the latest.

drew.allen SSC Guru Points: 76996 More actions · Answer 3

The endpoints of the window can be specified as any of the following in order:

UNBOUNDED PRECEDING
<n> PRECEDING
CURRENT ROW
<n> FOLLOWING
UNBOUNDED FOLLOWING

NOTE: The first endpoint must precede the second endpoint in order. This means that UNBOUNDED PRECEDING can only ever be the first endpoint and UNBOUNDED FOLLOWING can only ever be the second endpoint. The others can be either the first or second endpoint. Also note that you can have something like ROWS BETWEEN 3 PRECEDING and 1 PRECEDING as long as the order is maintained.

NOTE 2: The most common window by far is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so much so that it has it's own special abbreviation ROWS UNBOUNDED PRECEDING.

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA