December 2, 2014 at 8:42 am
Hello everybody
I have the following query that will serve as a basis for SSRS report
SELECT TOP (1000) d.Project_Name, d.Status, d.Country, d.Region, p.Period, p.Quarter, p.Year, d.Brand, d.Store_Opens_Actual, d.DA, d.DPN, d.StoreNumber,
CONVERT(VARCHAR(10), CASE WHEN ISDATE(d .Store_Opens_Actual) = 1 THEN d .Store_Opens_Actual WHEN ISDATE(d .Store_Opens_Forecast) = 1 AND
ISDATE(d .Store_Opens_Actual) = 0 THEN d .Store_Opens_Forecast WHEN ISDATE(d .Store_Opens_Baseline) = 1 AND ISDATE(d .Store_Opens_Forecast) = 0 AND
ISDATE(d .Store_Opens_Actual) = 0 THEN d .Store_Opens_Baseline END, 101) AS Store_Open, CASE WHEN ISDATE(d .SAR_Accepted)
= 1 THEN 'YES' ELSE 'NO' END AS SAR_Accepted, CASE WHEN ISDATE(d .Construction_Started) = 1 THEN 'YES' ELSE 'NO' END AS Const_Started,
CASE WHEN ISDATE(d .Lease_Signed) = 1 THEN 'YES' ELSE 'NO' END AS Lease_Signed, CASE WHEN ISDATE(d .Closed_Date_Actual)
= 1 THEN 'YES' ELSE 'NO' END AS Closed, CONVERT(VARCHAR(10), CASE WHEN ISDATE(d .Closed_Date_Actual)
= 1 THEN d .Closed_Date_Actual WHEN ISDATE(d .Closed_Date_Forecast) = 1 AND ISDATE(d .Closed_Date_Actual) = 0 THEN d .Closed_Date_Forecast END, 101)
AS Store_Closed, CASE WHEN ISDATE(d .Store_Opens_Actual) = 1 THEN 'Y' ELSE 'N' END AS Open_Flag
FROM dbo.forecast_data AS d LEFT OUTER JOIN
dbo.Periods AS p ON (CASE WHEN ISDATE(d .Store_Opens_Actual) = 1 THEN d .Store_Opens_Actual WHEN ISDATE(d .Store_Opens_Forecast) = 1 AND
ISDATE(d .Store_Opens_Actual) = 0 THEN d .Store_Opens_Forecast WHEN ISDATE(d .Store_Opens_Baseline) = 1 AND ISDATE(d .Store_Opens_Forecast) = 0 AND
ISDATE(d .Store_Opens_Actual) = 0 THEN d .Store_Opens_Baseline END) >= p.PeriodStarts AND (CASE WHEN ISDATE(d .Store_Opens_Actual)
= 1 THEN d .Store_Opens_Actual WHEN ISDATE(d .Store_Opens_Forecast) = 1 AND ISDATE(d .Store_Opens_Actual)
= 0 THEN d .Store_Opens_Forecast WHEN ISDATE(d .Store_Opens_Baseline) = 1 AND ISDATE(d .Store_Opens_Forecast) = 0 AND ISDATE(d .Store_Opens_Actual)
= 0 THEN d .Store_Opens_Baseline END) <= p.PeriodEnds
WHERE (d.FileID = '411') AND (d.Status <> '12 - Dead Project') AND (d.Relocation <> 'Yes') AND (d.Stoplight_Status <> 'Red') AND
((SELECT CASE WHEN ISDATE(fd.Store_Opens_Actual) = 1 THEN fd.Store_Opens_Actual WHEN ISDATE(fd.Store_Opens_Forecast) = 1 AND
ISDATE(fd.Store_Opens_Actual) = 0 THEN fd.Store_Opens_Forecast WHEN ISDATE(fd.Store_Opens_Baseline) = 1 AND
ISDATE(fd.Store_Opens_Forecast) = 0 AND ISDATE(fd.Store_Opens_Actual) = 0 THEN fd.Store_Opens_Baseline END AS Expr1
FROM dbo.forecast_data AS fd
WHERE (recID = d.recID)) BETWEEN
(SELECT PeriodStarts
FROM dbo.Periods
WHERE (Period = 1) AND (Year = 2014)) AND
(SELECT PeriodEnds
FROM dbo.Periods AS Periods_1
WHERE (Period = 12) AND (Year = 2014)))
ORDER BY Store_Open
This returns a dataset, that I need to convert into a PIVOT table that should look like the attached spreadsheet.
Having trouble writing the PIVOT table query. I feel like I am missing something conceptually as I am not doing any summing or aggregation. I don't know if dynamics SQL is the solution here or which route to take. I don't know if there is such things as PIVOTING without aggregation. CROSS TAB came to my mind as well. Please, help.
thanks in advance,
Petr
December 2, 2014 at 10:11 am
Duplicate post.
All answers in here: http://www.sqlservercentral.com/Forums/Topic1640806-3412-1.aspx
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply