August 26, 2014 at 7:55 am
Hi,
I have inherited (for my sins ) the following table. (sample data provided below). I have been asked to reformat & summarise the data but it isn't structured in a very user friendly format.
I need to transpose/summarise the data into the expected format (also below).
Notes / Info
1) Each row represents a single patient.
2) Each value under the dates columns represents the Doctor code that that patient was registered for that month.
3) Where there is a gap in Doctor codes then assume no doctor registered.
4) The problem is I need to use the column titles as data. So instead of seeing "Apr_14' I need to see '2014-04-01'.
5) Where the current month is either null or or with a doctor code then summary table must reflect based on current date.
Sample data.... (expected results below also).
USE [NCI_Person_Extract]
GO
/****** Object: Table [dbo].[PS_Table] Script Date: 08/26/2014 14:18:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PS_Table]
(
[NHS_NUMBER_ANON] [varchar](14) NULL,
[NHS_NUMBER] [nvarchar](14) NOT NULL,
[Apr_14] [varchar](6) NULL,
[May_14] [varchar](6) NULL,
[Jun_14] [varchar](6) NULL,
[Jul_14] [varchar](6) NULL,
[Aug_14] [varchar](6) NULL,
[Sep_14] [varchar](6) NULL,
[Oct_14] [varchar](6) NULL,
[Nov_14] [varchar](6) NULL,
[Dec_14] [varchar](6) NULL,
[Jan_15] [varchar](6) NULL,
[Feb_15] [varchar](6) NULL,
[Mar_15] [varchar](6) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[PS_Table] ([NHS_NUMBER_ANON], [NHS_NUMBER], [Apr_14], [May_14], [Jun_14], [Jul_14], [Aug_14], [Sep_14], [Oct_14], [Nov_14], [Dec_14], [Jan_15], [Feb_15], [Mar_15])
VALUES (N'1234 5678 9101', N'1234567891', N'Z12345', N'Z12345', N'Z12345', N'P12345', N'P12345', N'P12345', N'P12345', NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[PS_Table] ([NHS_NUMBER_ANON], [NHS_NUMBER], [Apr_14], [May_14], [Jun_14], [Jul_14], [Aug_14], [Sep_14], [Oct_14], [Nov_14], [Dec_14], [Jan_15], [Feb_15], [Mar_15])
VALUES (N'1098 7654 3210', N'1098765432', N'A12345', N'A12345', N'A12345', NULL, N'B12345', NULL, NULL, NULL, NULL, NULL, NULL, NULL)
INSERT [dbo].[PS_Table] ([NHS_NUMBER_ANON], [NHS_NUMBER], [Apr_14], [May_14], [Jun_14], [Jul_14], [Aug_14], [Sep_14], [Oct_14], [Nov_14], [Dec_14], [Jan_15], [Feb_15], [Mar_15])
VALUES (N'9999 9999 9999', N'9999999999', N'A82041', N'A82041', N'A82041', N'A82041', N'A82041', NULL, NULL, NULL, NULL, NULL, NULL, NULL)
Expected Results.
NHS_NUMBER_ANONNHS_NUMBERDoctor_Start_PeriodDoctor_End_PeriodDoctor_Code
1234 5678 9101 12345678912014-04-01 2014-06-30 Z12345
1234 5678 9101 12345678912014-07-01 null P12345
1098 7654 3210 10987654322014-04-01 2014-06-30 A12345
1098 7654 3210 10987654322014-08-01 null null
9999 9999 9999 99999999992104-04-01 null A82041
Thanks in advance for any help and if you need any more guidance please just get in touch.
August 26, 2014 at 8:41 am
I am able to bring it to this level.
Not getting how to handle the nulls in unipovt.
May be someone will be have a better answer.
declare @sample table(
NHS_NUMBER_ANON varchar(14),
NHS_NUMBER varchar(14),
datesvalue varchar(256),
date datetime)
insert into @sample select NHS_NUMBER_ANON,NHS_NUMBER,datesvalue,
left(dates,3) + ' 01,' + RIGHT(dates,2)
from (select * from PS_Table) P
UNPIVOT
(datesvalue for dates in (
[APR_14] ,
[May_14] ,
[Jun_14] ,
[Jul_14] ,
[Aug_14] ,
[Sep_14] ,
[Oct_14] ,
[Nov_14] ,
[Dec_14] ,
[Jan_15] ,
[Feb_15] ,
[Mar_15]
)
) as unpvt;
select * from @sample
select NHS_NUMBER_ANON,NHS_NUMBER
,MIN(date) as Doctor_Start_Period
,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,MAX(date))+1,0)) as Doctor_End_Period
,datesvalue as Doctor_Code
from @sample
group by NHS_NUMBER_ANON,NHS_NUMBER,datesvalue
declare @sample table(
NHS_NUMBER_ANON varchar(14),
NHS_NUMBER varchar(14),
datesvalue varchar(256),
date datetime)
insert into @sample select NHS_NUMBER_ANON,NHS_NUMBER,datesvalue,
left(dates,3) + ' 01,' + RIGHT(dates,2)
from (select * from PS_Table) P
UNPIVOT
(datesvalue for dates in (
[APR_14] ,
[May_14] ,
[Jun_14] ,
[Jul_14] ,
[Aug_14] ,
[Sep_14] ,
[Oct_14] ,
[Nov_14] ,
[Dec_14] ,
[Jan_15] ,
[Feb_15] ,
[Mar_15]
)
) as unpvt;
--NHS_NUMBER_ANON NHS_NUMBER Doctor_Start_Period Doctor_End_Period Doctor_Code
--1234 5678 9101 1234567891 2014-04-01 2014-06-30 Z12345
--1234 5678 9101 1234567891 2014-07-01 null P12345
--1098 7654 3210 1098765432 2014-04-01 2014-06-30 A12345
--1098 7654 3210 1098765432 2014-08-01 null null
--9999 9999 9999 9999999999 2104-04-01 null A82041
select distinct t.NHS_NUMBER_ANON,t.NHS_NUMBER,t.Doctor_Start_Period,
case when t1.Doctor_End_Period IS null then null else t.Doctor_End_Period end as Doctor_End_Period
,t.Doctor_Code
from (select NHS_NUMBER_ANON,NHS_NUMBER
,MIN(date) as Doctor_Start_Period
,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,MAX(date))+1,0)) as Doctor_End_Period
,datesvalue as Doctor_Code
from @sample
group by NHS_NUMBER_ANON,NHS_NUMBER,datesvalue) t
left join
( select NHS_NUMBER_ANON,NHS_NUMBER
,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,MAX(date))+1,0)) as Doctor_End_Period
from @sample
group by NHS_NUMBER_ANON,NHS_NUMBER) t1
on
t1.NHS_NUMBER_ANON = t.NHS_NUMBER_ANON
and t1.NHS_NUMBER = t.NHS_NUMBER
and isnull(t1.Doctor_End_Period,getdate()) != isnull(t.Doctor_End_Period,GETDATE())
August 26, 2014 at 9:35 am
This might not be better, but it's different. 😉
SELECT NHS_NUMBER_ANON,
NHS_NUMBER,
MIN(MonthDate) Doctor_Start_Period,
MAX(MonthDate) Doctor_End_Period,
Value Doctor_Code
FROM PS_Table
CROSS APPLY( VALUES(CAST( '20140401' AS datetime) , Apr_14),
('20140501' , May_14),
('20140601' , Jun_14),
('20140701' , Jul_14),
('20140801' , Aug_14),
('20140901' , Sep_14),
('20141001' , Oct_14),
('20141101' , Nov_14),
('20141201' , Dec_14),
('20150101' , Jan_15),
('20150201' , Feb_15),
('20150301' , Mar_15))x(MonthDate,Value)
WHERE Value IS NOT NULL
GROUP BY NHS_NUMBER_ANON,
NHS_NUMBER,
Value
ORDER BY NHS_NUMBER_ANON, Doctor_Start_Period
August 26, 2014 at 9:36 am
Many thanks, works a treat.
Regards
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply