[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 736
  • Last Modified:

SQL Pivot Help

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
Manju
Asked:
Manju
  • 4
  • 2
1 Solution
 
chaauCommented:
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
 
ManjuIT - Project ManagerAuthor Commented:
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
 
David ToddSenior DBACommented:
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
chaauCommented:
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
 
ManjuIT - Project ManagerAuthor Commented:
@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
 
ManjuIT - Project ManagerAuthor Commented:
@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
 
ManjuIT - Project ManagerAuthor Commented:
nevermind. I got it. Thanks
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now