May 5, 2020 at 3:47 pm
Hello Everyone,
I have placed raw data and final output required with create test data scripts, explained clearly below.
DROP TABLE #test1
create table #test1(IDColumn INT,MemberID INT,Startdate Date,Enddate Date,[7DayRedamit] bit,[30DayRedamit] bit,[60DayRedamit] bit,[90DayRedamit] bit,[180DayRedamit] bit)
Insert INto #test1 values(1,111,'4/3/2019','4/4/2019',0,0,0,0,0)
Insert INto #test1 values(2,111,'7/5/2019','7/7/2019',0,0,0,0,1)
Insert INto #test1 values(3,111,'7/16/2019','7/19/2019',0,1,1,1,1)
Insert INto #test1 values(4,222,'4/12/2019','5/2/2019',0,0,0,0,0)
Insert INto #test1 values(5,222,'7/5/2019','7/19/2019',0,0,0,1,1)
Insert INto #test1 values(6,222,'8/29/2019','9/16/2019',0,0,1,1,1)
Insert INto #test1 values(7,222,'10/5/2019','11/12/2019',0,1,1,1,1)
Table explanation below:
for memberID 111 on IDColumn 2 i have 180dayreadmit flagged as 1 because readmission happened after 90 days(datediff between row 2 start date -row 1 end date) and that is the reason it is flagged in 180dayreadmitflag.
now for same member similarly IDColumn 3 i have flagged all 30,60,90,180dayreadmit because readmission actually happened within 30 days(datediff between row 3 start date - row 2 end date) and it shown as readmission for 60,90 and 180 too.
Same logic for MemberID 222 on IDColumn 5 i have flagged 90,180dayreadmit because readmission actually happened within 90 days(datediff between row 5 start date - row 4 end date) and it shown as readmission for 90 and 180 too.
now for same memberID 222 similarly IDColumn 6 i have flagged all 60,90,180dayreadmit because readmission actually happened within 60 days(datediff between row 6 start date - row 7 end date) and it shown as readmission for 60,90 and 180 too.
now for same memberID 222 similarly IDColumn 7 i have flagged all 60,90,180dayreadmit because readmission actually happened within 60 days(datediff between row 7 start date - row 8 end date) and it shown as readmission for 60,90 and 180 too.
My required Output explanation below:
Now my output should show counts for 7,30,60,90,180dayreadmit as rows and in columns i want Quarters(based on enddate) as output, so here we have Enddate column dates from April to November so my Quarters are defined as April to June as 'Q1' and July to sep as 'Q2' and Oct to Dec as 'Q3'.
so counts in these quarters are defined based on for what enddate readmission happened and that month quarters(As said above) will be shown in columns .
I mean for member 111 180readmission happened on '2019-07-05' that is after '2019-04-04'(enddate) so this readmission should be counted to 'Q1' in 180 row and also this should count in 'Q1' column for one more time because 180readmission happened on '2019-07-16' that is after '2019-04-04'(enddate) in 180 row.
and for same memberID (111) 30,60,90,180 days readmission happened on '2019-07-16' that is after '2019-07-07'(enddate) so this should be counted to 'Q2' in 30,60,90,180 row.
for memberid(222) bit tricky because we should count in multiple quarters, see below explanation.
Similarly for memberID (222) 90,180 days readmission happened on '2019-07-05' that is after '2019-05-02'(enddate)this should be counted to 'Q1' 90,180 rows.
for same memberID (222) 60,90,180 days readmission happened on '2019-08-29' that is after '2019-07-19'(enddate)this should be counted to 'Q2' 60,90,180 rows and also in 'Q1' because readmission happened on '2019-08-29' that is after '2019-05-02' this sholud in 'Q1' column 180 row.
for same memberID (222) 30,60,90,180 days readmission happened on '2019-10-05' that is after '2019-09-16'(enddate)this should be counted to 'Q2' column 30,60,90,180 rows and also in 'Q2' column 90,180 rows because readmission happened on '2019-10-05' that is after '2019-07-19' in 90,180 row.
same memberID (222) 30,60,90,180 days readmission happened on '2019-10-05' that is after '2019-09-16'(enddate)this should be counted to 'Q2' column 30,60,90,180 rows and also in 'Q2' column 90,180 rows because readmission happened on '2019-10-05' that is after '2019-07-19' in 90,180 row.
and also this sholud in 'Q1' column 180dayreadmit row because readmission happend on '2019-10-05' that is after '2019-05-02' in 180 row.
Similary if there are any 'Q4' readmissions happened according to enddate those counts also needs to included for all rows/Columns respectively
Thanks and let me know if my explanation is not clear. please see below for table output:
so my output should be as below table:
--final output needed(for member 111 + 222)
Drop table #final
create table #final (Readmissions varchar(50),Q1Count int,Q2Count int,Q3Count int)
Insert into #final values ('7DayRedamit',0,0,0)
Insert into #final values ('30DayRedamit',0,2,0)
Insert into #final values ('60DayRedamit', 0,3,0)
Insert into #final values ('90DayRedamit', 0,4,0)
Insert into #final values ('180DayRedamit', 5,4,0)
i am splitting into 2 temp tables below for each member for clear understanding of above final output below.
Drop table #final_111
create table #final_111(Readmissions varchar(50),Q1Count int,Q2Count int,Q3Count int)
-- output for member 111
Insert into #final_111 values ('7DayRedamit',0,0,0)
Insert into #final_111 values ('30DayRedamit',0,1,0)
Insert into #final_111 values ('60DayRedamit', 0,1,0)
Insert into #final_111 values ('90DayRedamit', 0,1,0)
Insert into #final_111 values ('180DayRedamit', 2,1,0)
-- output for member 222
Drop table #final_222
create table #final_222(Readmissions varchar(50),Q1Count int,Q2Count int,Q3Count int)
Insert into #final_222 values ('7DayRedamit',0,0,0)
Insert into #final_222 values ('30DayRedamit',0,1,0)
Insert into #final_222 values ('60DayRedamit', 0,2,0)
Insert into #final_222 values ('90DayRedamit', 1,3,0)
Insert into #final_222 values ('180DayRedamit', 3,3,0)
Thanks in advance.
May 5, 2020 at 5:59 pm
How does this code look:
CREATE TABLE [#test1]
(
[IDColumn] INT
, [MemberID] INT
, [Startdate] DATE
, [Enddate] DATE
, [7DayRedamit] BIT
, [30DayRedamit] BIT
, [60DayRedamit] BIT
, [90DayRedamit] BIT
, [180DayRedamit] BIT
);
INSERT INTO [#test1]
VALUES
(
1
, 111
, '4/3/2019'
, '4/4/2019'
, 0
, 0
, 0
, 0
, 0
);
INSERT INTO [#test1]
VALUES
(
2
, 111
, '7/5/2019'
, '7/7/2019'
, 0
, 0
, 0
, 0
, 1
);
INSERT INTO [#test1]
VALUES
(
3
, 111
, '7/16/2019'
, '7/19/2019'
, 0
, 1
, 1
, 1
, 1
);
INSERT INTO [#test1]
VALUES
(
4
, 222
, '4/12/2019'
, '5/2/2019'
, 0
, 0
, 0
, 0
, 0
);
INSERT INTO [#test1]
VALUES
(
5
, 222
, '7/5/2019'
, '7/19/2019'
, 0
, 0
, 0
, 1
, 1
);
INSERT INTO [#test1]
VALUES
(
6
, 222
, '8/29/2019'
, '9/16/2019'
, 0
, 0
, 1
, 1
, 1
);
INSERT INTO [#test1]
VALUES
(
7
, 222
, '10/5/2019'
, '11/12/2019'
, 0
, 1
, 1
, 1
, 1
);
WITH [DataPlusQuarter]
AS
(
SELECT
[IDColumn]
, [MemberID]
, [Startdate]
, [Enddate]
, [7DayRedamit]
, [30DayRedamit]
, [60DayRedamit]
, [90DayRedamit]
, [180DayRedamit]
, DATEPART( QUARTER
, [Enddate]
) AS [Quarter]
FROM[#test1]
)
, [result]
AS
(
SELECT
'7DayReadmit' AS [Readmit]
, CASE
WHEN ([DataPlusQuarter].[Quarter] = 1)
THEN SUM(CAST([DataPlusQuarter].[7DayRedamit] AS INT))
END AS [Q1]
, CASE
WHEN ([DataPlusQuarter].[Quarter] = 2)
THEN SUM(CAST([DataPlusQuarter].[7DayRedamit] AS INT))
END AS [Q2]
, CASE
WHEN ([DataPlusQuarter].[Quarter] = 3)
THEN SUM(CAST([DataPlusQuarter].[7DayRedamit] AS INT))
END AS [Q3]
, CASE
WHEN ([DataPlusQuarter].[Quarter] = 4)
THEN SUM(CAST([DataPlusQuarter].[7DayRedamit] AS INT))
END AS [Q4]
FROM[DataPlusQuarter]
GROUP BY[DataPlusQuarter].[Quarter]
UNION
SELECT
'30DayReadmit' AS [Readmit]
, CASE
WHEN ([DataPlusQuarter].[Quarter] = 1)
THEN SUM(CAST([DataPlusQuarter].[30DayRedamit] AS INT))
END AS [Q1]
, CASE
WHEN ([DataPlusQuarter].[Quarter] = 2)
THEN SUM(CAST([DataPlusQuarter].[30DayRedamit] AS INT))
END AS [Q2]
, CASE
WHEN ([DataPlusQuarter].[Quarter] = 3)
THEN SUM(CAST([DataPlusQuarter].[30DayRedamit] AS INT))
END AS [Q3]
, CASE
WHEN ([DataPlusQuarter].[Quarter] = 4)
THEN SUM(CAST([DataPlusQuarter].[30DayRedamit] AS INT))
END AS [Q4]
FROM[DataPlusQuarter]
GROUP BY[DataPlusQuarter].[Quarter]
UNION
SELECT
'60DayReadmit' AS [Readmit]
, CASE
WHEN ([DataPlusQuarter].[Quarter] = 1)
THEN SUM(CAST([DataPlusQuarter].[60DayRedamit] AS INT))
END AS [Q1]
, CASE
WHEN ([DataPlusQuarter].[Quarter] = 2)
THEN SUM(CAST([DataPlusQuarter].[60DayRedamit] AS INT))
END AS [Q2]
, CASE
WHEN ([DataPlusQuarter].[Quarter] = 3)
THEN SUM(CAST([DataPlusQuarter].[60DayRedamit] AS INT))
END AS [Q3]
, CASE
WHEN ([DataPlusQuarter].[Quarter] = 4)
THEN SUM(CAST([DataPlusQuarter].[60DayRedamit] AS INT))
END AS [Q4]
FROM[DataPlusQuarter]
GROUP BY[DataPlusQuarter].[Quarter]
UNION
SELECT
'90DayReadmit' AS [Readmit]
, CASE
WHEN ([DataPlusQuarter].[Quarter] = 1)
THEN SUM(CAST([DataPlusQuarter].[90DayRedamit] AS INT))
END AS [Q1]
, CASE
WHEN ([DataPlusQuarter].[Quarter] = 2)
THEN SUM(CAST([DataPlusQuarter].[90DayRedamit] AS INT))
END AS [Q2]
, CASE
WHEN ([DataPlusQuarter].[Quarter] = 3)
THEN SUM(CAST([DataPlusQuarter].[90DayRedamit] AS INT))
END AS [Q3]
, CASE
WHEN ([DataPlusQuarter].[Quarter] = 4)
THEN SUM(CAST([DataPlusQuarter].[90DayRedamit] AS INT))
END AS [Q4]
FROM[DataPlusQuarter]
GROUP BY[DataPlusQuarter].[Quarter]
UNION
SELECT
'180DayReadmit' AS [Readmit]
, CASE
WHEN ([DataPlusQuarter].[Quarter] = 1)
THEN SUM(CAST([DataPlusQuarter].[180DayRedamit] AS INT))
ENDAS [Q1]
, CASE
WHEN ([DataPlusQuarter].[Quarter] = 2)
THEN SUM(CAST([DataPlusQuarter].[180DayRedamit] AS INT))
ENDAS [Q2]
, CASE
WHEN ([DataPlusQuarter].[Quarter] = 3)
THEN SUM(CAST([DataPlusQuarter].[180DayRedamit] AS INT))
ENDAS [Q3]
, CASE
WHEN ([DataPlusQuarter].[Quarter] = 4)
THEN SUM(CAST([DataPlusQuarter].[180DayRedamit] AS INT))
ENDAS [Q4]
FROM[DataPlusQuarter]
GROUP BY[DataPlusQuarter].[Quarter]
)
SELECT
[result].[Readmit]
, ISNULL(SUM([result].[Q1])
, 0
) AS [Q1]
, ISNULL(SUM([result].[Q2])
, 0
) AS [Q2]
, ISNULL(SUM([result].[Q3])
, 0
) AS [Q3]
, ISNULL(SUM([result].[Q4])
, 0
) AS [Q4]
FROM[result]
GROUP BY[result].[Readmit];
DROP TABLE [#test1];
Might not be the most efficient, but I think it is giving you what you want, right?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 5, 2020 at 7:16 pm
Hello, first of all thank you for responding on my question pretty fastly. Appreciated it!!!
This is working partially okay but for 180dayreadmits this is not working as expected. for any admission if there is any readmission for prior to 180 days that should be counted in 180 days also. similarly if the member is counted in 60 days that readmission should be counted in 90 and 180 days as well, same thing if we readmission in 30 days time frame it should it go 60,90,180 as well. please see my expected output explanation in my original post and also see I separated outputs in #final_111 and #final_222 for understanding purposes. so totally there are 9 readmissions for 180 readmit categories but now I am getting only 5 readmit counts from your code.
Thanks again!
May 5, 2020 at 8:21 pm
I realized the mistake. Actually multiple mistakes on my part.
First mistake I did was assumed that I could just add up the number in the xxxDayReadmit column, but you want it to add those up AND add up ones that are duplicated up. That is why you are expecting 9 rows of results with 7 rows of data. This can be fixed with a self join.
What makes things more difficult is you are wanting a "Readmit" to count for the quarter of the parent.
And the last mistake I made was I numbered the quarters wrong. Q4 should be Q3, Q3->Q2, Q2->Q1, and Q1->Q4.
Give me a little bit and I should have something better for you...
EDIT - I am getting close to an answer, but having some issues with the logic. Also, need a bit of clarification on something - Does your real result set have cases where the member ID has gaps? What I mean is hypothetically could we have 2 or more new rows with MemberID 111 happen in 2020 and thus outside the original set?
If so, the approach I was trying will not work.
But onto a different problem. Playing with the data, I think that your bit columns are useless when working on the final result as you expect it. The reason for this is best given by looking at Member ID 111:
ID column 1 has 0 across the board as this is the first occurrence. The next one is ID Column 2 which happened within 180 days of the first addmitance. Therefore we have 1 at this point. Next, we have ID column 3 which happened within 180 days of ID 1, so we have 2 that match up for the first end date. This works out nice... except that ID Column 3 indicates a 1 for 30 day as well which is not the case when comparing ID1 and ID 3.
So first thing to do is scrap the readmit columns as they are useless.
Next, lets build up 5 CTE's using the original data; one for 7 day, one for 30 day, one for 60 day, one for 90 day and one for 180 day. Add the quarter each thing happened on and sum it for the final result.
Give me a little bit and I should have a query for you, but feel free to work off of that idea... You may beat me to a result.
EDIT 2 - I think I have a solution:
CREATE TABLE [#test1]
(
[IDColumn] INT
, [MemberID] INT
, [Startdate] DATE
, [Enddate] DATE
, [7DayRedamit] BIT
, [30DayRedamit] BIT
, [60DayRedamit] BIT
, [90DayRedamit] BIT
, [180DayRedamit] BIT
);
INSERT INTO [#test1]
VALUES
(
1
, 111
, '4/3/2019'
, '4/4/2019'
, 0
, 0
, 0
, 0
, 0
);
INSERT INTO [#test1]
VALUES
(
2
, 111
, '7/5/2019'
, '7/7/2019'
, 0
, 0
, 0
, 0
, 1
);
INSERT INTO [#test1]
VALUES
(
3
, 111
, '7/16/2019'
, '7/19/2019'
, 0
, 1
, 1
, 1
, 1
);
INSERT INTO [#test1]
VALUES
(
4
, 222
, '4/12/2019'
, '5/2/2019'
, 0
, 0
, 0
, 0
, 0
);
INSERT INTO [#test1]
VALUES
(
5
, 222
, '7/5/2019'
, '7/19/2019'
, 0
, 0
, 0
, 1
, 1
);
INSERT INTO [#test1]
VALUES
(
6
, 222
, '8/29/2019'
, '9/16/2019'
, 0
, 0
, 1
, 1
, 1
);
INSERT INTO [#test1]
VALUES
(
7
, 222
, '10/5/2019'
, '11/12/2019'
, 0
, 1
, 1
, 1
, 1
);
WITH [SevenDay]
AS
(
SELECT
[t1].[Enddate]
, DATEPART( QUARTER
, [t1].[Enddate]
) AS [Quarter]
FROM[#test1] AS [t1]
JOIN[#test1] AS [t2]
ON [t1].[MemberID] = [t2].[MemberID]
AND [t1].[IDColumn] <> [t2].[IDColumn]
AND [t2].[Startdate] >= [t1].[Enddate]
AND [t2].[Startdate] < DATEADD( DAY
, 7
, [t1].[Enddate]
)
)
, [ThirtyDay]
AS
(
SELECT
[t1].[Enddate]
, DATEPART( QUARTER
, [t1].[Enddate]
) AS [Quarter]
FROM[#test1] AS [t1]
JOIN[#test1] AS [t2]
ON [t1].[MemberID] = [t2].[MemberID]
AND [t1].[IDColumn] <> [t2].[IDColumn]
AND [t2].[Startdate] >= [t1].[Enddate]
AND [t2].[Startdate] < DATEADD( DAY
, 30
, [t1].[Enddate]
)
)
, [SixtyDay]
AS
(
SELECT
[t1].[Enddate]
, DATEPART( QUARTER
, [t1].[Enddate]
) AS [Quarter]
FROM[#test1] AS [t1]
JOIN[#test1] AS [t2]
ON [t1].[MemberID] = [t2].[MemberID]
AND [t1].[IDColumn] <> [t2].[IDColumn]
AND [t2].[Startdate] >= [t1].[Enddate]
AND [t2].[Startdate] < DATEADD( DAY
, 60
, [t1].[Enddate]
)
)
, [NinetyDay]
AS
(
SELECT
[t1].[Enddate]
, DATEPART( QUARTER
, [t1].[Enddate]
) AS [Quarter]
FROM[#test1] AS [t1]
JOIN[#test1] AS [t2]
ON [t1].[MemberID] = [t2].[MemberID]
AND [t1].[IDColumn] <> [t2].[IDColumn]
AND [t2].[Startdate] >= [t1].[Enddate]
AND [t2].[Startdate] < DATEADD( DAY
, 90
, [t1].[Enddate]
)
)
, [OneEightyDay]
AS
(
SELECT
[t1].[Enddate]
, DATEPART( QUARTER
, [t1].[Enddate]
) AS [Quarter]
FROM[#test1] AS [t1]
JOIN[#test1] AS [t2]
ON [t1].[MemberID] = [t2].[MemberID]
AND [t1].[IDColumn] <> [t2].[IDColumn]
AND [t2].[Startdate] >= [t1].[Enddate]
AND [t2].[Startdate] < DATEADD( DAY
, 180
, [t1].[Enddate]
)
)
, [results]
AS
(
SELECT
'7DayReadmit' AS [Readmit]
, CASE
WHEN ([SevenDay].[Quarter] = 1)
THEN COUNT(1)
END AS [Q1]
, CASE
WHEN ([SevenDay].[Quarter] = 2)
THEN COUNT(1)
END AS [Q2]
, CASE
WHEN ([SevenDay].[Quarter] = 3)
THEN COUNT(1)
END AS [Q3]
, CASE
WHEN ([SevenDay].[Quarter] = 4)
THEN COUNT(1)
END AS [Q4]
, 1 AS [Sequence]
FROM[SevenDay]
GROUP BY[SevenDay].[Quarter]
UNION
SELECT
'30DayReadmit' AS [Readmit]
, CASE
WHEN ([ThirtyDay].[Quarter] = 1)
THEN COUNT(1)
END AS [Q1]
, CASE
WHEN ([ThirtyDay].[Quarter] = 2)
THEN COUNT(1)
END AS [Q2]
, CASE
WHEN ([ThirtyDay].[Quarter] = 3)
THEN COUNT(1)
END AS [Q3]
, CASE
WHEN ([ThirtyDay].[Quarter] = 4)
THEN COUNT(1)
END AS [Q4]
, 2 AS [Sequence]
FROM[ThirtyDay]
GROUP BY[ThirtyDay].[Quarter]
UNION
SELECT
'60DayReadmit' AS [Readmit]
, CASE
WHEN ([SixtyDay].[Quarter] = 1)
THEN COUNT(1)
END AS [Q1]
, CASE
WHEN ([SixtyDay].[Quarter] = 2)
THEN COUNT(1)
END AS [Q2]
, CASE
WHEN ([SixtyDay].[Quarter] = 3)
THEN COUNT(1)
END AS [Q3]
, CASE
WHEN ([SixtyDay].[Quarter] = 4)
THEN COUNT(1)
END AS [Q4]
, 3 AS [Sequence]
FROM[SixtyDay]
GROUP BY[SixtyDay].[Quarter]
UNION
SELECT
'90DayReadmit' AS [Readmit]
, CASE
WHEN ([NinetyDay].[Quarter] = 1)
THEN COUNT(1)
END AS [Q1]
, CASE
WHEN ([NinetyDay].[Quarter] = 2)
THEN COUNT(1)
END AS [Q2]
, CASE
WHEN ([NinetyDay].[Quarter] = 3)
THEN COUNT(1)
END AS [Q3]
, CASE
WHEN ([NinetyDay].[Quarter] = 4)
THEN COUNT(1)
END AS [Q4]
, 4 AS [Sequence]
FROM[NinetyDay]
GROUP BY[NinetyDay].[Quarter]
UNION
SELECT
'180DayReadmit' AS [Readmit]
, CASE
WHEN ([OneEightyDay].[Quarter] = 1)
THEN COUNT(1)
ENDAS [Q1]
, CASE
WHEN ([OneEightyDay].[Quarter] = 2)
THEN COUNT(1)
ENDAS [Q2]
, CASE
WHEN ([OneEightyDay].[Quarter] = 3)
THEN COUNT(1)
ENDAS [Q3]
, CASE
WHEN ([OneEightyDay].[Quarter] = 4)
THEN COUNT(1)
ENDAS [Q4]
, 5AS [Sequence]
FROM[OneEightyDay]
GROUP BY[OneEightyDay].[Quarter]
UNION
SELECT
'7DayReadmit'
, 0
, 0
, 0
, 0
, 1
UNION
SELECT
'30DayReadmit'
, 0
, 0
, 0
, 0
, 2
UNION
SELECT
'60DayReadmit'
, 0
, 0
, 0
, 0
, 3
UNION
SELECT
'90DayReadmit'
, 0
, 0
, 0
, 0
, 4
UNION
SELECT
'180DayReadmit'
, 0
, 0
, 0
, 0
, 5
)
SELECT
[results].[Readmit]
, ISNULL(SUM([results].[Q2])
, 0
) AS [Q1]
, ISNULL(SUM([results].[Q3])
, 0
) AS [Q2]
, ISNULL(SUM([results].[Q4])
, 0
) AS [Q3]
--, ISNULL(SUM([results].[Q1])
--, 0
--) AS [Q4]
FROM[results]
GROUP BY[results].[Readmit]
, [results].[Sequence]
ORDER BY[results].[Sequence];
DROP TABLE [#test1];
Numbers match up to your original query mostly. I think your original expected output had a typo in it. But I think this should work!
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 5, 2020 at 8:56 pm
Hello, Yes, I think what you said is correct, I want readmit count based on the quarter of the parent. (I think that's what I tried to explain in my original post, sorry if it is confusing).
numbering quarters is okay when I generate summary report for all members I can keep manually in the final report under those quarters as needed but counts should be correct in the final output for those Quarters...
Thanks again for your help!
May 5, 2020 at 9:14 pm
Hello, please see my answers below.
Your Question -- What I mean is hypothetically could we have 2 or more new rows with MemberID 111 happen in 2020 and thus outside the original set?
My reply -- This is just test dataset, so any point of time I will have past 3 quarters of data so in 2020 jan I need to report past 3 quarters readmissions count so my dataset will have April 2019 to dec 2019 data in startdate and endate. similary in April 2020 I will july 2019 to Mar 2020 data.
Your Question -- So first thing to do is scrap the readmit columns as they are useless.
My Reply-- those readmissions columns I produced based on start date and end date columns using self join for detail data and I thought those will be helpful while getting summarized data counts but if those are not useful ignore them.
Thanks again for your help and let me know if you have any Questions.
May 5, 2020 at 9:30 pm
I think the result I posted in my second edit should work with the potential for a gap. I think the only thing that you may want to add would be a "year" column. Otherwise, since we are breaking it up by quarter, January 1st, 2019 and January 1st 2020 are both in the same quarter so your counts may look weird.
And the readmit columns are useful only if you are looking at non-overlapping readmits. If each row ONLY had a 1:1 relationship and thus could only be counted once, then those numbers are useful. So if you have 6 rows, the HIGHEST count you could get would be 5 (as one of those rows would be a "root"), then those numbers are useful as you can just count them like I did in my first interpretation of the problem.
BUT since it is a 1:many relationship between rows, the readmit bits are not as useful. you need to break the results up per range (I used CTE's, you could use nested selects if you prefer) and then you can simply count the number of rows per quarter. Then because you want Jan-Mar to be Quarter 4, you just shuffle around some column headers.
Does the solution I posted work?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 5, 2020 at 10:13 pm
Solution posted absolutely works for the test data I provided, I will be testing with other members with actual data and will keep you posted may be in a day or two if I have any Questions. Thank you so much for you help. really appreciated.
May 7, 2020 at 11:43 am
This was removed by the editor as SPAM
May 7, 2020 at 2:00 pm
Hello, As mentioned above I tested for few other members and it is working as expected. First of all I would like to thank you very much for your help. It exactly works fine for most of the scenarios. Thank you Again!
I would like to know if this works if the startdate of the readmission is same as enddate of the prior admission, it should count for 7,30,60,90 and 180 as well. this is not actual overlap for a member.
there wont be January 1st, 2019 and January 1st 2020 at the same time because we are only looking for past 3 quarters at a time and also I need to name Quarters as per my requirement because quarters change each time for my pull. In below data 07/2019, 08/2019 and 09/2019 will be Q1 , 10/2019, 11/2019 and 12/2019 for Q2 and 01/2020,02/2020 and 03/2020 as Q3 in my report.FYI, for the below sample member 333 we don't have any admissions in Q1 so everything will go to Q2 and Q3.
Insert INto #test1 values(1,333,'10/17/2019','10/29/2019',0,0,0,0,0)
Insert INto #test1 values(2,333,'11/4/2019','11/11/2019',1, 1,1,1,1)
Insert INto #test1 values(3,333,'12/4/2019','12/9/2019',0,1,1,1,1)
Insert INto #test1 values(4,333,'12/20/2019','12/30/2019',0,1,1,1,1)
Insert INto #test1 values(5,333,'1/22/2020','1/27/2020',0,1,1,1,1)
Insert INto #test1 values(6,333,'2/4/2020', '2/7/2020', 0,1,1,1,1)
Insert INto #test1 values(7,333,'2/20/2020','3/4/2020' ,0,1,1, 1,1)
May 7, 2020 at 4:32 pm
Just so I understand what you are asking, you want to know if the following will be counted as 7, 30, 60, 90 and 180:
Insert INto #test1 values(1,333,'10/17/2019','10/29/2019',0,0,0,0,0)
Insert INto #test1 values(2,333,'10/29/2019','11/11/2019',1, 1,1,1,1)
I say try it out. I just did and I got 1 for each row in Q3 which I would think is expected. I am a little confused by your statement that "it should count for 7,30,60,90 and 180 as well. this is not actual overlap for a member" so I am not sure if you expect it to count or not count. If you want it to count, it currently does. If you want it to not count, adjust the WHERE clauses so it excludes the ones where start date = end date.
If you need your Quarters to be named dynamically, that is going to require dynamic SQL and you introduce risk by doing so. My opinion, if you need the columns named a certain way, do that at the application side, not on the SQL side (the reporting side or on Excel or wherever you are storing the data).
If you NEED it to be in the SQL side, you are going to have to use dynamic SQL. In order to help with that part, I'll need a bit more direction on what defines your quarters then. Based entirely on the data provided, I see nothing that can help me determine what makes a quarter. Is it just grabbing the latest enddate and that month is the end of the quarter and then working backwards by 3 quarters?
What I mean is if the dates range from January 1st to March 15th, Q3 is January to March, Q2 is October to Deccember and Q1 July to September, correct? But if the dates range from January 1st to April 15th, is Q3 February to April OR April to June? In the examples you provided, your latest enddate is always on a calendar quarter end. Is this a consistent and reliable assumption I can make? if so, it makes the calculation for the quarters easier. If not, some additional fiddling needs to be done...
My opinion though, I would not redefine the definition of "Quarters" in the SQL code and do it at the presentation layer (Excel, SSRS, application, etc.). BUT if you need to do it in SQL, dump the above query into a dynamic query and use variables to decide which column from the CTE goes into the final select.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
May 7, 2020 at 9:26 pm
Insert INto #test1 values(1,333,'10/17/2019','10/29/2019',0,0,0,0,0)
Insert INto #test1 values(2,333,'10/29/2019','11/11/2019',1, 1,1,1,1)
yes this is what I mean '10/29/2019' for enddate for one admission and startdate for another admission, this is pulling into my, I will adjust where clause as needed.
For Quarters definition as mentioned I will take care at SSRS level as dynamic SQL again may be little more complex. I will keep consistent Quarters at SQL end and will do anything needed at Reports side. Thank you!
Grateful to have experts like this. Thank you!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply