December 21, 2022 at 8:02 pm
I am trying to avoid writing a stored procedure and trying to create a view that will return the data in the second set of columns (F through I) in my attached data based off the first set of columns (A through D). Is this even possible and if so, how? Any help is appreciated. I tried a number of things but no luck. Thanks.
December 21, 2022 at 8:50 pm
? Don't see anything attached ?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
December 21, 2022 at 11:41 pm
Don't attach it. Just copy it and paste it into your message. Make life easy on people here. =)
December 22, 2022 at 12:29 pm
Here is the data -- I tried with the row_number() function and various other things that didn't work. Any help is appreciated!
Table Data (view/query below this):
claim ID Claim status Rec Start Rec End
A P 1/1/2022 2/1/2022
A P 2/1/2022 2/10/2022
A P 2/10/2022 2/28/2022
A V 2/28/2022 3/2/2022
A P 3/2/2022 3/5/2022
A V 3/5/2022 3/10/2022
A C 3/10/2022 3/11/2022
A C 3/11/2022 12/31/9999
Query or view data
claim ID Claim status Rec Start Rec End
A P 1/1/2022 2/28/2022
A V 2/28/2022 3/2/2022
A P 3/2/2022 3/5/2022
A V 3/5/2022 3/10/2022
A C 3/10/2022 12/31/9999
December 22, 2022 at 12:41 pm
SELECT [claim ID], [Claim status], MIN([Rec Start]) [Rec Start], MAX([Rec End]) [Rec End]
FROM MyTable
GROUP BY [claim ID], [Claim status]
In the "Complex" example you have nothing to order the table by but seem to want different results for the same [claim ID] and [Claim status]. Unless you have something to order the rows by there is no way to know that:
A V 28/02/2022 02/03/2022
should not be grouped with
A V 05/03/2022 10/03/2022
December 22, 2022 at 1:13 pm
Johnathan, thank you for your reply but that only yields two rows.
December 22, 2022 at 1:24 pm
Johnathan, thank you for your reply but that only yields two rows.
It should yield three rows as it groups by [claim ID], [Claim status] and you have (A, P), (A, C), (A, V)
Like I said you need something to order the table by (you can't just rely on the order of the rows without a sort) to define that
A V 2/28/2022 3/2/2022
should not be grouped with
A V 3/5/2022 3/10/2022
Can you explain why there are two rows for this, not one?
December 22, 2022 at 3:10 pm
This is a packing intervals problem. You can Google "packing intervals" to find out more.
Here is a solution
/* Set up the sample data */
CREATE TABLE #Claims
(
Claim_ID CHAR(1)
, Claim_Status CHAR(1)
, Rec_Start DATE
, Rec_End DATE
, CONSTRAINT PK_Claims PRIMARY KEY (Claim_ID, Claim_Status, Rec_Start)
)
INSERT #Claims (Claim_ID, Claim_Status, Rec_Start, Rec_End)
VALUES ('A', 'P', '1/1/2022', '2/1/2022')
, ('A', 'P', '2/1/2022', '2/10/2022')
, ('A', 'P', '2/10/2022', '2/28/2022')
, ('A', 'V', '2/28/2022', '3/2/2022')
, ('A', 'P', '3/2/2022', '3/5/2022')
, ('A', 'V', '3/5/2022', '3/10/2022')
, ('A', 'C', '3/10/2022', '3/11/2022')
, ('A', 'C', '3/11/2022', '12/31/9999')
;
/* Start the actual solution. */
WITH New_Claim_Statuses AS
(
SELECT *, CASE WHEN c.Claim_Status = LAG(c.Claim_Status, 1, '') OVER(PARTITION BY c.Claim_ID ORDER BY c.Rec_Start) THEN 0 ELSE 1 END AS New_Claim_Status
FROM #Claims AS c
)
, New_Claim_Status_Groups AS
(
SELECT *, SUM(nc.New_Claim_Status) OVER(PARTITION BY nc.Claim_ID ORDER BY nc.Rec_Start ROWS UNBOUNDED PRECEDING) AS grp
FROM New_Claim_Statuses AS nc
)
SELECT g.Claim_ID, g.Claim_Status, MIN(g.Rec_Start) AS Rec_Start, MAX(g.Rec_End) AS Rec_End
FROM New_Claim_Status_Groups AS g
GROUP BY g.Claim_ID, g.Claim_Status, g.grp
ORDER BY g.Claim_ID, MIN(g.Rec_Start)
/* Clean up. */
DROP TABLE IF EXISTS #Claims;
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 29, 2022 at 12:23 pm
Thank you so much J. Drew Allen! That is exactly what I was looking for. I appreciate your help and detailed script.
December 30, 2022 at 3:57 pm
Thank you so much J. Drew Allen! That is exactly what I was looking for. I appreciate your help and detailed script.
Do you understand what it does and how it works well enough to support it? Especially if they allow non-contiguous dates within a group?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply