2

I know what I want the data to display but can't seem to figure out the correct logic for it. Example below.

Dataset

**'ID', 'Admission Mth',    'Admission Yr', 'Category', 'Facility', 'ID_Yr_Cat',    'ID_Yr_Cat_Fac',**
'123456',   'Jan',  '2017', 'Hospital', 'NYMC', '123456-2017-Hospital', '123456-2017-Hospital-NYMC',
'123456',   'Jul',  '2017', 'Hospital', 'NYMC', '123456-2017-Hospital', '123456-2017-Hospital-NYMC',
'123456',   'Oct',  '2018', 'Hospital', 'NYMC', '123456-2018-Hospital', '123456-2018-Hospital-NYMC',
'123456',   'Nov',  '2018', 'Hospital', 'NJMC', '123456-2018-Hospital', '123456-2018-Hospital-NJMC',
'789123',   'Feb',  '2017', 'Clinic',   'Philly Clinic',    '789123-2017-Clinic',   '789123-2017-Clinic-Philly Clinic',
'987654',   'May',  '2018', 'Hospital', 'PAMC', '987654-2018-Hospital', '987654-2018-Hospital-PAMC',
'456123',   'Sept', '2017', 'Clinic',   'Philly Clinic',    '456123-2017-Clinic',   '456123-2017-Clinic-Philly Clinic',
'456123',   'Aug',  '2018', 'Hospital', 'NYMC', '456123-2018-Hospital', '456123-2018-Hospital-NYMC',
'456123',   'Nov',  '2018', 'Hospital', 'NYMC', '456123-2018-Hospital', '456123-2018-Hospital-NYMC',
'456123',   'Dec',  '2018', 'Hospital', 'NJMC', '456123-2018-Hospital', '456123-2018-Hospital-NJMC'

I want the final results to display "1" flags for the hospital readmit.

Final results should show:

**'Hospital Readmit per Yr'**,
'0',
'1',
'0',
'1',
'0',
'0',
'0',
'0',
'1',
'1'

**'Hospital Readmit per Yr & Fac'**,
'0',
'1',
'0',
'0',
'0',
'0',
'0',
'0',
'1',
'0'

My thoughts were to use some sort of case when with a lead function including a partition. Just not sure how to write it out. I'm using SQL Server MS 2008.

anothermh
  • 9,815
  • 3
  • 33
  • 52
Cameron Cook
  • 21
  • 2
  • This can be achieved either with CASE WHEN or with LEAD or LAG function. However, since you mentioned you are using SQL server 2008, LEAD or LAG may not work as LEAD and LAG are the analytical functions which can be used in SQL Server 2012 or higher version. – KRM Dec 18 '18 at 03:31
  • Can you provide me with the code you would use to determine this? – Cameron Cook Dec 18 '18 at 03:47
  • Hope the code/query I shared in the answer below helps! Let me know if you have any questions regarding the query I shared. – KRM Dec 18 '18 at 04:14
  • Also, I feel the last two columns of your schema (i.e. 'ID_Yr_Cat' and 'ID_Yr_Cat_Fac') are having redundant data as that data is already captured in other columns. So as a good database design/schema, the last two columns can be avoided and the query can be achieved even without those two columns. Let me know if you have any questions. Would be glad to know if it helped. – KRM Dec 18 '18 at 04:24

1 Answers1

1

This can be achieved either with CASE WHEN or with LEAD or LAG function. However, since you mentioned you are using SQL server 2008, LEAD or LAG may not work as LEAD and LAG are the analytical functions which can be used in SQL Server 2012 or higher version. You may want to try something like this if you want to use CASE WHEN.

For getting [Hospital Readmit per Yr] flag value:

SELECT 
CASE WHEN A.[RowNumber]>1 THEN 1 else 0 END As [Hospital Readmit per Yr]
FROM (select ROW_NUMBER() OVER (PARTITION BY [ID_Yr_Cat] order by [Admission Mth], [Admission Yr]) as [RowNumber], * from hospital) as A

For getting [ID_Yr_Cat_Fac] flag value:

SELECT 
CASE WHEN A.[RowNumber]>1 THEN 1 else 0 END As [Hospital Readmit per Yr & Fac]
FROM (select ROW_NUMBER() OVER (PARTITION BY [ID_Yr_Cat_Fac] order by [Admission Mth], [Admission Yr]) as [RowNumber], * from hospital) as A

In case you want to look at ALL the columns to understand how the query is returning results, check the screenshots below: enter image description here

KRM
  • 1,185
  • 2
  • 13
  • 28
  • I really appreciate your help. I'm still a bit confused though. The purpose of my flags are for the ability to pivot the data in Excel. So I definitely still want to keep the two new columns. Are you suggesting I make a column with the row number label? Not sure how to do that. – Cameron Cook Dec 18 '18 at 04:41
  • I've also been able to use lead within a case when statement in sql server 2008. – Cameron Cook Dec 18 '18 at 04:42
  • these flags are for a subquery – Cameron Cook Dec 18 '18 at 04:43
  • I mentioned, "LEAD and LAG are the analytical functions which can be used in SQL Server 2012 or higher version" based on my readings on links below and based on my test in SQL 20008. https://learn.microsoft.com/en-us/sql/t-sql/functions/lead-transact-sql?view=sql-server-2017 https://stackoverflow.com/questions/22188514/alternate-of-lead-lag-function-in-sql-server-2008 – KRM Dec 18 '18 at 13:50
  • If you need those columns for any specific reason, that's totally fine. What I mean to say was, For example column "ID_Yr_Cat" contains combined information for ID, Admission Yr and Category and the separate information is already stored in Columns ID, Admission Yr and Category so storing the combined information in a new/separate column could be avoided, but if you need it for any reason that's totally fine. It was just my thought. – KRM Dec 18 '18 at 13:58
  • @CameronCook, Just curious to know how you were able to use the LEAD/LAG functions in SQL 2008 as those were introduced in SQL 2012. Did you run update compatibility-level command or something to make it work in SQL 2008? Like the compatibility level commands at this link?https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-2017 Also, if you were able to run the query I shared and it worked like the way youexpected, can you please validate the answer & upvote. And if its not working, do let me know what's your expectation. – KRM Dec 18 '18 at 15:56