CODEWITHSUNDEEP
sqlsql-serverreporting-services
JayZee64
JayZee64

Reputation: 13

Transpose SQL Server Dates

I am not sure if I need a transpose, pivot but here goes.

What I am trying to achieve is working out a certain amount of points per day that are dependant on a persons length of stay that is >= 2.

Currently I'm am using case statements to get my dates and my points, here is a small portion on how I am doing this:

CASE WHEN DATEDIFF(DAY, ADMISSION_OFFERS.TCI_DTTM, ADMISSION_OFFERS.EXPDS_DTTM)
>= 2 THEN DATEADD(DAY, 1, ADMISSION_OFFERS.TCI_DTTM) ELSE NULL END AS [Date 2],
CASE WHEN DATEDIFF(DAY, ADMISSION_OFFERS.TCI_DTTM, ADMISSION_OFFERS.EXPDS_DTTM)
>= 2 THEN '0.7' ELSE NULL END AS [Date 2 Point]

Doing it this way I get this result for 2 people:

|Id     |Date 1     |Date 1 Point   |Date 2     |Date 2 Point   |Date 3     |Date 3 Point   |Date 4     |Date 4 Point   |Date 5     |Date 5 Point   |Date 6     |Date 6 Point   |Type   |
|1234567|21/11/2014 |0.7            |22/11/2014 |0.7            |23/11/2014 |0.7            |24/11/2014 |0.35           |NULL       |0              |NULL       |0              |Knee   |
|9876543|04/10/2014 |0.7            |05/10/2014 |0.7            |06/10/2014 |0.7            |07/10/2014 |0.7            |08/10/2014 |0.7            |09/10/2014 |0.35           |Hip    |

How I need it it to be for SSRS:

Id      |Date       |Point  |Type
1234567 |21/11/2014 |0.7    |Knee
1234567 |22/11/2014 |0.7    |Knee
1234567 |23/11/2014 |0.7    |Knee
1234567 |24/11/2014 |0.35   |Knee
1234567 |NULL       |0      |Knee
1234567 |NULL       |0      |Knee
9876543 |04/10/2014 |0.7    |Hip
9876543 |05/10/2014 |0.7    |Hip
9876543 |06/10/2014 |0.7    |Hip
9876543 |07/10/2014 |0.7    |Hip
9876543 |08/10/2014 |0.7    |Hip
9876543 |09/10/2014 |0.35   |Hip

Once done I need date part the date field for SSRS.

Upvotes: 0

Views: 67

Answers (2)

feohoTl
feohoTl

Reputation: 65

using a tmp table for your case statement query,

WITH tmpTable AS
(Your case statement query)
SELECT Id, Data1, Point1, [TYPE] FROM tmpTable UNION
SELECT Id, Data2, Point2, [TYPE] FROM tmpTable UNION
SELECT Id, Data3, Point3, [TYPE] FROM tmpTable

Upvotes: 1

JesalynOlson
JesalynOlson

Reputation: 513

An easy way would just be to union the values of the dates together into one list.

For instance if you put the results of your query into a temp table you will write:

SELECT Id, [Date 1] AS Date, [Date 1 Point] AS Point, Type
FROM #Temp
UNION ALL
SELECT Id, [Date 2] AS Date, [Date 2 Point] AS Point, Type
FROM #Temp
UNION ALL
SELECT Id, [Date 3] AS Date, [Date 3 Point] AS Point, Type
FROM #Temp
UNION
SELECT Id, [Date 4] AS Date, [Date 4 Point] AS Point, Type
FROM #Temp

Upvotes: 0

Related Questions