Solved

SQL Pivot Help

Posted on 2014-01-28
7
662 Views
Last Modified: 2014-01-30
Team - I need your assistance for the SQL Pivot question.

I have Raw Data with many columns, however i am going to work only with 8 columns.

ProgramID
ProjectID
Project Name
PEGateDate
PCGateDate
LPAGateDate
LAGateDate
LRGateDate

Question i have is, all gate dates above will have a date(MM/DD/YYYY) format. What i want to show is
 
Output
Middle columns(FY13/14) will show current year and the previous column(FY12/13) is for previous year and like wise the last column is Next year.
Months will start from July to June
Every gate date has a color code attached.

Lets say - PEGatedate is 4th January 2014 and PCGatedate is 5th March2014,
in the output, For J (after OND) in FY13/14, it should say "DatePC" and for Feb also. And for M (after JF in FY13/14), it should say "DatePC"

Kindly help.

Regards,
Manjunath
0
Comment
Question by:Manju
  • 4
  • 2
7 Comments
 
LVL 24

Expert Comment

by:chaau
Comment Utility
It would help if you post some sample data. it will help even greatly if you create an SQL Fiddle with your data. It is not really clear what column should be used for PIVOT, and how the colours are controlled
0
 
LVL 6

Author Comment

by:Manju
Comment Utility
Chaau - I dont know how to create a fiddle. tried doing that for sometime, posting them here.

Lets say i have the below table.


CREATE TABLE programstatus(
      ProgramID varchar(255),
      ProjectID varchar(255),
      ProjectName varchar(255),
      PEDate datetime,
      PCDate datetime,
      LPADate datetime,
      LADate datetime ,
      LRDate datetime
      ) ;

INSERT INTO programstatus
(ProgramID, ProjectID, ProjectName, PEDate, PCDate, LPADate, LADate, LRDate)
VALUES
('123456', '4455', 'Test Project A', 01/11/2012, 03/04/2012, 07/23/2012, 04/07/2013, 10/07/2013),
('123456', '4456', 'Test Project B', 01/11/2014, 01/13/2014, 07/23/2014, 09/02/2014, 01/15/2015),
('123456', '4457', 'Test Project C', 06/04/2013, 07/06/2013, 09/23/2013, 12/07/2013, 04/07/2014),
('123456', '4458', 'Test Project D', 01/11/2012, 03/04/2012, 07/23/2012, 04/07/2013, 10/07/2013),
('123456', '4459', 'Test Project E', 01/11/2014, 01/13/2014, 07/23/2014, 09/02/2014, 01/15/2015),
('12345', '4460', 'Test Project F', 06/04/2013, 07/06/2013, 09/23/2013, 12/07/2013, 04/07/2014),
('12345', '4523', 'Test Project G', 01/11/2012, 03/04/2012, 07/23/2012, 04/07/2013, 10/07/2013),
('12345', '4524', 'Test Project H', 01/11/2014, 01/13/2014, 07/23/2014, 09/02/2014, 01/15/2015),
('12345', '4451', 'Test Project I', 01/11/2012, 03/04/2012, 07/23/2012, 04/07/2013, 10/07/2013),
('12345', '4450', 'Test Project J', 06/04/2013, 07/06/2013, 09/23/2013, 12/07/2013, 04/07/2014),
('1234568', '4566', 'Test Project K', 01/11/2012, 03/04/2012, 07/23/2012, 04/07/2013, 10/07/2013),
('1234568', '4123', 'Test Project L', 01/11/2014, 01/13/2014, 07/23/2014, 09/02/2014, 01/15/2015),
('1234568', '3124', 'Test Project M', 06/04/2013, 07/06/2013, 09/23/2013, 12/07/2013, 04/07/2014);
0
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
Hi,

I struggled with the sample data you provided. I ended up with this to get it to work:
use ExpertsExchange 
go

if exists(
	select *
	from sys.objects
	where
		object_id = object_id( N'dbo.programstatus' )
		and type in( N'U' )
	)
	drop table dbo.programstatus
;
go

if not exists(
	select *
	from sys.objects
	where
		object_id = object_id( N'dbo.' )
		and type in( N'U' )
	)
	create TABLE dbo.programstatus(
      ProgramID varchar(255),
      ProjectID varchar(255),
      ProjectName varchar(255),
      PEDate datetime,
      PCDate datetime,
      LPADate datetime,
      LADate datetime ,
      LRDate datetime 
      ) ;

set dateformat mdy

INSERT INTO dbo.programstatus
(ProgramID, ProjectID, ProjectName, PEDate, PCDate, LPADate, LADate, LRDate)
VALUES
('123456', '4455', 'Test Project A', '01/11/2012', '03/04/2012', '07/23/2012', '04/07/2013', '10/07/2013'),
('123456', '4456', 'Test Project B', '01/11/2014', '01/13/2014', '07/23/2014', '09/02/2014', '01/15/2015'),
('123456', '4457', 'Test Project C', '06/04/2013', '07/06/2013', '09/23/2013', '12/07/2013', '04/07/2014'),
('123456', '4458', 'Test Project D', '01/11/2012', '03/04/2012', '07/23/2012', '04/07/2013', '10/07/2013'),
('123456', '4459', 'Test Project E', '01/11/2014', '01/13/2014', '07/23/2014', '09/02/2014', '01/15/2015'),
('12345', '4460', 'Test Project F', '06/04/2013', '07/06/2013', '09/23/2013', '12/07/2013', '04/07/2014'),
('12345', '4523', 'Test Project G', '01/11/2012', '03/04/2012', '07/23/2012', '04/07/2013', '10/07/2013'),
('12345', '4524', 'Test Project H', '01/11/2014', '01/13/2014', '07/23/2014', '09/02/2014', '01/15/2015'),
('12345', '4451', 'Test Project I', '01/11/2012', '03/04/2012', '07/23/2012', '04/07/2013', '10/07/2013'),
('12345', '4450', 'Test Project J', '06/04/2013', '07/06/2013', '09/23/2013', '12/07/2013', '04/07/2014'),
('1234568', '4566', 'Test Project K', '01/11/2012', '03/04/2012', '07/23/2012', '04/07/2013', '10/07/2013'),
('1234568', '4123', 'Test Project L', '01/11/2014', '01/13/2014', '07/23/2014', '09/02/2014', '01/15/2015'),
('1234568', '3124', 'Test Project M', '06/04/2013', '07/06/2013', '09/23/2013', '12/07/2013', '04/07/2014'); 

select *
from dbo.programstatus
;

set dateformat dmy

Open in new window


Thinking about it, I suggest that the easiest way conceptually is to code 36 columns worth of case statements like this:
use ExpertsExchange 
go

select 
	ps.ProjectName
	, case
		when ps.PEDate < '2012-08-01' and ps.PCDate >= '2012-07-01' then 'PE' -- DateRange covers part of july 2012
		when ps.PCDate < '2012-08-01' and ps.LPADate >= '2012-07-01' then 'PC'
		when ps.LPADate < '2012-08-01' and ps.LADate >= '2012-07-01' then 'LPA'
		when ps.LADate < '2012-08-01' and ps.LRDate >= '2012-07-01' then 'LA'
	end as Jul2012
	, case
		when ps.PEDate  < '2012-09-01' and ps.PCDate  >= '2012-08-01' then 'PE' -- DateRange covers part of august 2012
		when ps.PCDate  < '2012-09-01' and ps.LPADate >= '2012-08-01' then 'PC'
		when ps.LPADate < '2012-09-01' and ps.LADate  >= '2012-08-01' then 'LPA'
		when ps.LADate  < '2012-09-01' and ps.LRDate  >= '2012-08-01' then 'LA'
	end as Aug2012
	, case
		when ps.PEDate  < '2012-10-01' and ps.PCDate  >= '2012-09-01' then 'PE' -- DateRange covers part of august 2012
		when ps.PCDate  < '2012-10-01' and ps.LPADate >= '2012-09-01' then 'PC'
		when ps.LPADate < '2012-10-01' and ps.LADate  >= '2012-09-01' then 'LPA'
		when ps.LADate  < '2012-10-01' and ps.LRDate  >= '2012-09-01' then 'LA'
	end as Sep2012
	, case
		when ps.PEDate  < '2012-11-01' and ps.PCDate  >= '2012-10-01' then 'PE' -- DateRange covers part of august 2012
		when ps.PCDate  < '2012-11-01' and ps.LPADate >= '2012-10-01' then 'PC'
		when ps.LPADate < '2012-11-01' and ps.LADate  >= '2012-10-01' then 'LPA'
		when ps.LADate  < '2012-11-01' and ps.LRDate  >= '2012-10-01' then 'LA'
	end as Oct2012
	, case
		when ps.PEDate  < '2012-12-01' and ps.PCDate  >= '2012-11-01' then 'PE' -- DateRange covers part of august 2012
		when ps.PCDate  < '2012-12-01' and ps.LPADate >= '2012-11-01' then 'PC'
		when ps.LPADate < '2012-12-01' and ps.LADate  >= '2012-11-01' then 'LPA'
		when ps.LADate  < '2012-12-01' and ps.LRDate  >= '2012-11-01' then 'LA'
	end as Nov2012
	, case
		when ps.PEDate  < '2013-01-01' and ps.PCDate  >= '2012-12-01' then 'PE' -- DateRange covers part of august 2012
		when ps.PCDate  < '2013-01-01' and ps.LPADate >= '2012-12-01' then 'PC'
		when ps.LPADate < '2013-01-01' and ps.LADate  >= '2012-12-01' then 'LPA'
		when ps.LADate  < '2013-01-01' and ps.LRDate  >= '2012-12-01' then 'LA'
	end as Dec2012
from dbo.programstatus ps
;

Open in new window


Now I'm undoubtedly missing something in your explanation, as I can't see how from five dates you get five date ranges, so my apologies.

Anyway, the gist of my solution is that you need to generate something like the output from the above, and then use whatever as a presentation tool that converts the value to a colour.

HTH
  David
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
Comment Utility
OK. I must admit, it was quite a task. Thanks for the nice question, got me occupied for quite a while. Here you go: a solution:
;with years as(
  SELECT 2012 as y union all
  SELECT 2013 as y union all
  SELECT 2014 as y),
months as (
  SELECT 1 as m UNION all
  SELECT m+1 FROM months WHERE m < 12),
alldates as (
  SELECT DATEADD(year, y-1900, DATEADD(month, m-1, DATEADD(day, 0, 0))) AS dmy
  FROM months cross join years),
firstdays as (
  select ProgramID, ProjectID, ProjectName, 
  DATEADD(month, DATEDIFF(month, 0, PEDate), 0) PEDate, 
  DATEADD(month, DATEDIFF(month, 0, PCDate), 0) PCDate, 
  DATEADD(month, DATEDIFF(month, 0, LPADate), 0) LPADate, 
  DATEADD(month, DATEDIFF(month, 0, LADate), 0) LADate, 
  DATEADD(month, DATEDIFF(month, 0, LRDate), 0) LRDate
  from programstatus)
select fd.ProgramID, 
fd.ProjectID, 
fd.ProjectName,
ad.dmy,
CASE 
 WHEN fd.PEDate <= ad.dmy AND fd.PCDate > ad.dmy THEN 'PEDate'
 WHEN fd.PCDate <= ad.dmy AND fd.LPADate > ad.dmy THEN 'PCDate'
 WHEN fd.LPADate <= ad.dmy AND fd.LADate > ad.dmy THEN 'LPADate'
 WHEN fd.LADate <= ad.dmy AND fd.LRDate > ad.dmy THEN 'LADate'
 WHEN fd.LRDate = ad.dmy THEN 'LRDate' 
 ELSE '' 
END AS value
INTO #temp
from firstdays fd 
CROSS JOIN alldates ad 
ORDER BY 1,2,3,4,5;

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(convert(varchar(10), c.dmy, 121)) 
            FROM #temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT ProgramID, ProjectID, ProjectName, ' + @cols + ' from 
            (
                select ProgramID, ProjectID, ProjectName
                    , dmy
                    , value
                from #temp
           ) x
            pivot 
            (
                 max(value)
                for dmy in (' + @cols + ')
            ) p '


execute(@query)

DROP TABLE #temp

Open in new window

And here are the results:
| PROGRAMID | PROJECTID |    PROJECTNAME | 2012-01-01 | 2012-02-01 | 2012-03-01 | 2012-04-01 | 2012-05-01 | 2012-06-01 | 2012-07-01 | 2012-08-01 | 2012-09-01 | 2012-10-01 | 2012-11-01 | 2012-12-01 | 2013-01-01 | 2013-02-01 | 2013-03-01 | 2013-04-01 | 2013-05-01 | 2013-06-01 | 2013-07-01 | 2013-08-01 | 2013-09-01 | 2013-10-01 | 2013-11-01 | 2013-12-01 | 2014-01-01 | 2014-02-01 | 2014-03-01 | 2014-04-01 | 2014-05-01 | 2014-06-01 | 2014-07-01 | 2014-08-01 | 2014-09-01 | 2014-10-01 | 2014-11-01 | 2014-12-01 |
|-----------|-----------|----------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|
|     12345 |      4450 | Test Project J |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |     PEDate |     PCDate |     PCDate |    LPADate |    LPADate |    LPADate |     LADate |     LADate |     LADate |     LADate |     LRDate |            |            |            |            |            |            |            |            |
|     12345 |      4451 | Test Project I |     PEDate |     PEDate |     PCDate |     PCDate |     PCDate |     PCDate |    LPADate |    LPADate |    LPADate |    LPADate |    LPADate |    LPADate |    LPADate |    LPADate |    LPADate |     LADate |     LADate |     LADate |     LADate |     LADate |     LADate |     LRDate |            |            |            |            |            |            |            |            |            |            |            |            |            |            |
|     12345 |      4460 | Test Project F |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |     PEDate |     PCDate |     PCDate |    LPADate |    LPADate |    LPADate |     LADate |     LADate |     LADate |     LADate |     LRDate |            |            |            |            |            |            |            |            |
|     12345 |      4523 | Test Project G |     PEDate |     PEDate |     PCDate |     PCDate |     PCDate |     PCDate |    LPADate |    LPADate |    LPADate |    LPADate |    LPADate |    LPADate |    LPADate |    LPADate |    LPADate |     LADate |     LADate |     LADate |     LADate |     LADate |     LADate |     LRDate |            |            |            |            |            |            |            |            |            |            |            |            |            |            |
|     12345 |      4524 | Test Project H |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |     PCDate |     PCDate |     PCDate |     PCDate |     PCDate |     PCDate |    LPADate |    LPADate |     LADate |     LADate |     LADate |     LADate |
|    123456 |      4455 | Test Project A |     PEDate |     PEDate |     PCDate |     PCDate |     PCDate |     PCDate |    LPADate |    LPADate |    LPADate |    LPADate |    LPADate |    LPADate |    LPADate |    LPADate |    LPADate |     LADate |     LADate |     LADate |     LADate |     LADate |     LADate |     LRDate |            |            |            |            |            |            |            |            |            |            |            |            |            |            |
|    123456 |      4456 | Test Project B |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |     PCDate |     PCDate |     PCDate |     PCDate |     PCDate |     PCDate |    LPADate |    LPADate |     LADate |     LADate |     LADate |     LADate |
|    123456 |      4457 | Test Project C |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |     PEDate |     PCDate |     PCDate |    LPADate |    LPADate |    LPADate |     LADate |     LADate |     LADate |     LADate |     LRDate |            |            |            |            |            |            |            |            |
|    123456 |      4458 | Test Project D |     PEDate |     PEDate |     PCDate |     PCDate |     PCDate |     PCDate |    LPADate |    LPADate |    LPADate |    LPADate |    LPADate |    LPADate |    LPADate |    LPADate |    LPADate |     LADate |     LADate |     LADate |     LADate |     LADate |     LADate |     LRDate |            |            |            |            |            |            |            |            |            |            |            |            |            |            |
|    123456 |      4459 | Test Project E |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |     PCDate |     PCDate |     PCDate |     PCDate |     PCDate |     PCDate |    LPADate |    LPADate |     LADate |     LADate |     LADate |     LADate |
|   1234568 |      3124 | Test Project M |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |     PEDate |     PCDate |     PCDate |    LPADate |    LPADate |    LPADate |     LADate |     LADate |     LADate |     LADate |     LRDate |            |            |            |            |            |            |            |            |
|   1234568 |      4123 | Test Project L |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |            |     PCDate |     PCDate |     PCDate |     PCDate |     PCDate |     PCDate |    LPADate |    LPADate |     LADate |     LADate |     LADate |     LADate |
|   1234568 |      4566 | Test Project K |     PEDate |     PEDate |     PCDate |     PCDate |     PCDate |     PCDate |    LPADate |    LPADate |    LPADate |    LPADate |    LPADate |    LPADate |    LPADate |    LPADate |    LPADate |     LADate |     LADate |     LADate |     LADate |     LADate |     LADate |     LRDate |            |            |            |            |            |            |            |            |            |            |            |            |            |            |

Open in new window

I recommend you try to understand what I have done. Comment the last line (DROP TABLE #temp), and replace it with SELECT * FROM #temp ORDER BY 1,2,3,4. By doing this you will find that I have prepared a working table with the months for the three years with the values set. This is a small explanation:
1. years CTE have just three years: 2012, 2013, 2014
2. months CTE is just a recursive CTE with numbers 1...12
3. alldates CTE is a CTE with all months for 2012, 2013, 2014
4. firstdates is your table, but the Dates are all converted to the first days of the months, i.e. if your data has PEDate set to 4th of July, the CTE will contain 1st of July
5. By cross joining last two CTEs I am able to build a working table #temp with all the required information. Now it is just a matter of creating a dynamic PIVOT table using the data.

Note: at the moment it is working from January to December. It is very easy to convert to work from June to July. A hint - use DateAdd unction when building alldates CTE. Please try to do it yourself (a small exercise for you). If you are unable to do so, I will be do.

SQL Fiddle
0
 
LVL 6

Author Comment

by:Manju
Comment Utility
@David - No offense but Chaau's solution was crystal clear & scalable.

@Chaau - I got to hand it to you. You're awesome. This precisely answered my query. Given a choice, I'd give you 5000 points.

One question though. I am using the query in SSRS to get the months from July. So I'm good to go there, however I am unable to pass a parameter. I already have a parameter (@programnumber) in SSRS and whenever I try to pass the same in the query, it throws an error. Can you help please?
0
 
LVL 6

Author Comment

by:Manju
Comment Utility
@Chaau - Btw, I created an SP and modified the exec query part as below:

execute(@query + 'where [TBL_Programs#Request No] in (''@pn varchar(max)'')')

DROP TABLE #temp

Earlier I got "Must declare a scalar variable @Pn"

Now I am getting "Error converting data type varchar to float."

Please help
0
 
LVL 6

Author Comment

by:Manju
Comment Utility
nevermind. I got it. Thanks
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now