December 16, 2013 at 9:04 am
I have looked through TechNet and SQL Server Central for a good way to pivot a difficult table. So far, the many different approaches have failed and I need a fresh perspective and help with the code.
I will provide the table language below.
The need is to output a table that will show a salespersons name with the Gross Profit each made in a month for the last 12 months (including the current) and then to take the GP made in the month from the current year and subtract from the GP made in the same month of the prior year.
One of the problems is that the salespersons do not all have the same data available for the same range; but the months must line up in order. So if a salesperson has no data for a month it should be NULL. However, every Month1,Month2,Month3, etc needs to be the same actual month year. So, just iterating through them does not work as the "month1" for someone with data in November as the earliest is not the same as the "month1" for someone with October as the earliest available.
The environment is SQL Server 2008 R2.
The table created will be used as a data source in SSRS 2008 R2.
Sample results:
StartDate: 11/01/2013
EndDate: 11/30/2013
M1 = 11/2013
M2 = 10/2013
M3 = 9/2013
...
M12 = 11/2012
CY = current year
PY = prior year
YOYGPDiff = Year Over Year Gross Profit Diff (CY - PY)
[names] [date per month CY] [Monthly GrossProfit CY] [date per month PY] [Monthly GrossProfit PY] [Monthly YOYGPDiff]
Name, DATE_CY1, MonthlyGP_CY1, DATE_PY1, MonthlyGP_PY1, YOYGPDiff_M1, ...
BOB BELL, 11-01-2013, 1333.650000, 11-01-2012, 10969.280000, -9635.630000, ...
DATE_CY12, MonthlyGP_CY12, MonthlyGP_PY12, YOYGPDiff_M12
11-01-2012, 10969.280000, 11254.240000, -284.960000
Name, DATE_CY1, MonthlyGP_CY1, DATE_PY1, MonthlyGP_PY1, YOYGPDiff_M1, ...
WAYNE SHAW, NULL, NULL, NULL, NULL, NULL,
DATE_CY2, MonthlyGP_CY2, DATE_PY2, MonthlyGP_PY2, YOYGPDiff_M2, ...
10-01-2013, 2770.060000, 10-01-2012, NULL, -2770.060000,
Name, DATE_CY1, MonthlyGP_CY1, DATE_PY1, MonthlyGP_PY1, YOYGPDiff_M1, ...
SANDY LIN, NULL, NULL, NULL, NULL, NULL,
DATE_CY2, MonthlyGP_CY2, DATE_PY2, MonthlyGP_PY2, YOYGPDiff_M2, ...
10-01-2013, 29281.200000, 10-01-2012, 28025.810000, 1255.39,
The source table is structured thusly:
CREATE TABLE GPByMonth (
[SPRSNSLN] [VARCHAR](61) NOT NULL,
[M] [INT] NOT NULL,
[Y] [INT] NOT NULL,
[MonthlyGP] [Decimal] (14,6) NULL,
[MonthlyRev] [Decimal] (14,6) NULL
)
Sample Data:
INSERT INTO GPByMonth VALUES ( 'BOB BELL',11,2011,11254.240000,56641.225000)
INSERT INTO GPByMonth VALUES ( 'BOB BELL',12,2011,17046.595000,98529.275000)
INSERT INTO GPByMonth VALUES ( 'BOB BELL',1,2012,13459.840000,68279.770000)
INSERT INTO GPByMonth VALUES ( 'BOB BELL',2,2012,18464.210000,96150.125000)
INSERT INTO GPByMonth VALUES ( 'BOB BELL',3,2012,20844.970000,87311.985000)
INSERT INTO GPByMonth VALUES ( 'BOB BELL',4,2012,20488.115000,91044.300000)
INSERT INTO GPByMonth VALUES ( 'BOB BELL',5,2012,16773.735000,93072.740000)
INSERT INTO GPByMonth VALUES ( 'BOB BELL',6,2012,15490.645000,78624.820000)
INSERT INTO GPByMonth VALUES ( 'BOB BELL',7,2012,17020.980000,85416.720000)
INSERT INTO GPByMonth VALUES ( 'BOB BELL',8,2012,24843.085000,116591.805000)
INSERT INTO GPByMonth VALUES ( 'BOB BELL',9,2012,13230.865000,66873.425000)
INSERT INTO GPByMonth VALUES ( 'BOB BELL',10,2012,31884.945000,147226.085000)
INSERT INTO GPByMonth VALUES ( 'BOB BELL',11,2012,10969.280000,58353.880000)
INSERT INTO GPByMonth VALUES ( 'BOB BELL',12,2012,15020.235000,74394.460000)
INSERT INTO GPByMonth VALUES ( 'BOB BELL',1,2013,18598.945000,99750.470000)
INSERT INTO GPByMonth VALUES ( 'BOB BELL',2,2013,21347.365000,84373.895000)
INSERT INTO GPByMonth VALUES ( 'BOB BELL',3,2013,19124.885000,84118.720000)
INSERT INTO GPByMonth VALUES ( 'BOB BELL',4,2013,19644.570000,105655.665000)
INSERT INTO GPByMonth VALUES ( 'BOB BELL',5,2013,18845.560000,117610.330000)
INSERT INTO GPByMonth VALUES ( 'BOB BELL',6,2013,26348.610000,115589.650000)
INSERT INTO GPByMonth VALUES ( 'BOB BELL',7,2013,11888.070000,68834.730000)
INSERT INTO GPByMonth VALUES ( 'BOB BELL',8,2013,18728.560000,108363.950000)
INSERT INTO GPByMonth VALUES ( 'BOB BELL',9,2013,20630.130000,85104.330000)
INSERT INTO GPByMonth VALUES ( 'BOB BELL',10,2013,30607.950000,141205.220000)
INSERT INTO GPByMonth VALUES ( 'BOB BELL',11,2013,1333.650000,7149.930000)
INSERT INTO GPByMonth VALUES ( 'WAYNE SHAW',2,2013,0.000000,0.000000)
INSERT INTO GPByMonth VALUES ( 'WAYNE SHAW',3,2013,148.180000,868.500000)
INSERT INTO GPByMonth VALUES ( 'WAYNE SHAW',4,2013,431.990000,1270.660000)
INSERT INTO GPByMonth VALUES ( 'WAYNE SHAW',5,2013,1858.620000,7467.040000)
INSERT INTO GPByMonth VALUES ( 'WAYNE SHAW',6,2013,2358.530000,6401.610000)
INSERT INTO GPByMonth VALUES ( 'WAYNE SHAW',7,2013,2845.230000,9904.800000)
INSERT INTO GPByMonth VALUES ( 'WAYNE SHAW',8,2013,3245.360000,11958.480000)
INSERT INTO GPByMonth VALUES ( 'WAYNE SHAW',9,2013,1252.880000,4136.580000)
INSERT INTO GPByMonth VALUES ( 'WAYNE SHAW',10,2013,2770.060000,9339.140000)
INSERT INTO GPByMonth VALUES ( 'SANDY LIN',5,2012,27553.890000,102369.560000)
INSERT INTO GPByMonth VALUES ( 'SANDY LIN',6,2012,31116.780000,114532.380000)
INSERT INTO GPByMonth VALUES ( 'SANDY LIN',7,2012,22435.350000,84591.400000)
INSERT INTO GPByMonth VALUES ( 'SANDY LIN',8,2012,37782.180000,127224.000000)
INSERT INTO GPByMonth VALUES ( 'SANDY LIN',9,2012,26547.040000,82395.190000)
INSERT INTO GPByMonth VALUES ( 'SANDY LIN',10,2012,28025.810000,96268.220000)
INSERT INTO GPByMonth VALUES ( 'SANDY LIN',11,2012,20203.100000,68700.390000)
INSERT INTO GPByMonth VALUES ( 'SANDY LIN',12,2012,23061.990000,82747.650000)
INSERT INTO GPByMonth VALUES ( 'SANDY LIN',1,2013,26721.780000,94176.800000)
INSERT INTO GPByMonth VALUES ( 'SANDY LIN',2,2013,24661.950000,82696.610000)
INSERT INTO GPByMonth VALUES ( 'SANDY LIN',3,2013,29719.660000,99566.070000)
INSERT INTO GPByMonth VALUES ( 'SANDY LIN',4,2013,28880.650000,93288.850000)
INSERT INTO GPByMonth VALUES ( 'SANDY LIN',5,2013,26343.260000,85788.420000)
INSERT INTO GPByMonth VALUES ( 'SANDY LIN',6,2013,25067.400000,84821.840000)
INSERT INTO GPByMonth VALUES ( 'SANDY LIN',7,2013,24196.470000,82984.570000)
INSERT INTO GPByMonth VALUES ( 'SANDY LIN',8,2013,27940.950000,97075.450000)
INSERT INTO GPByMonth VALUES ( 'SANDY LIN',9,2013,26307.840000,98757.000000)
INSERT INTO GPByMonth VALUES ( 'SANDY LIN',10,2013,29281.200000,105184.650000)
December 16, 2013 at 9:43 am
I'm not sure if this will help you but it sounds as if you need a calendar table (I used a CTE to get the needed months). I hope this gets you closer to what you need.
WITH CTE AS(
SELECT TOP 12 DATEADD( mm, (ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) -1) * -1, DATEADD( mm, DATEDIFF( mm, 0, GETDATE()), 0)) period
FROM sys.all_columns
)
SELECT *
FROM CTE
LEFT
JOIN GPByMonth Cur ON Cur.M = MONTH( period) AND Cur.Y = YEAR( Period)
LEFT
JOIN GPByMonth Prev ON Prev.M = MONTH( period)
AND Prev.Y = YEAR( Period) - 1
AND Cur.SPRSNSLN = Prev.SPRSNSLN
ORDER BY Cur.SPRSNSLN , period DESC
December 16, 2013 at 9:51 am
Thank you for your help. I in fact do have a calendar table setup with code from: http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html
It comes in very handy.
I will see if I can apply it to this solution.
btw- did I use improper etiquette for my code post? I saw the link in your signature and thought I followed the standards; but maybe I missed something?
December 16, 2013 at 10:00 am
The DDL and sample data were helpful. You could use the IFCode tags to avoid having a very large post and keep formatting but that's just style. 🙂
I got lost on the expected results, but maybe that was just me. I might have get confused because I couldn't relate the title with the description.
December 16, 2013 at 10:58 am
Luis,
I spent some time and got this to look a little better and hopefully will clear up some of my post.
What I need is a table that gives one row for each salesperson with a set of columns for each month/year period.
I have a comma delimited example below. Rather than fill all twelve periods (some 48+ columns) I put in a [...] to indicate the rest of the columns. I also included code at the bottom to create the results table with no data in it.
I appreciate your time on this and will be better in the future with my post etiquette. 🙂
--Better Sample Results model:
CY = current year
PY = prior year
YOYGPDiff = Year Over Year Gross Profit Diff (CY - PY)
[names] [date per month CY] [Monthly GrossProfit CY] [date per month PY] [Monthly GrossProfit PY] [Monthly YOYGPDiff]
Name,DATE_CY1,MonthlyGP_CY1,DATE_PY1,MonthlyGP_PY1,YOYGPDiff_M1,DATE_CY2,MonthlyGP_CY2,DATE_PY2,MonthlyGP_PY2,YOYGPDiff_M2,[...],DATE_CY12,MonthlyGP_CY12,MonthlyGP_PY12,YOYGPDiff_M12
BOB BELL,11-01-2013,1333.650000,11-01-2012,10969.280000,-9635.630000,10-01-2013,0.00,10-01-2012,0.00,0.00,[...],11-01-2012,10969.280000,11254.240000,-284.960000
WAYNE SHAW,11-01-2013,0.00,11-01-2012,0.00,0.00,10-01-2013,2770.060000,10-01-2012,0.00,-2770.060000,[...],11-01-2012,0.00,0.00,0.00
SANDY LIN,11-01-2013,0.00,11-01-2012,0.00,0.00,10-01-2013,29281.200000,10-01-2012,28025.810000,1255.39,[...],11-01-2012,0.00,0.00,0.00
-- Better Sample data and table :
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#GPByMonth','U') IS NOT NULL
DROP TABLE #GPByMonth
--===== Create the test table with
CREATE TABLE #GPByMonth (
[SPRSNSLN] [VARCHAR](61) NOT NULL,
[GPDate] [DateTime] NOT NULL,
[MonthlyGP] [Decimal] (14,6) NULL,
[MonthlyRev] [Decimal] (14,6) NULL
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #GPbyMonth ON
--===== Insert the test data into the test table
INSERT INTO #GPbyMonth
(SPRSNSLN, GPDATE, MonthlyGP, MonthlyRev)
SELECT 'BOB BELL','Nov 1 2011 12:00AM',11254.240000,56641.225000 UNION ALL
SELECT 'BOB BELL','Dec 1 2011 12:00AM',17046.595000,98529.275000 UNION ALL
SELECT 'BOB BELL','Jan 1 2012 12:00AM',13459.840000,68279.770000 UNION ALL
SELECT 'BOB BELL','Feb 1 2012 12:00AM',18464.210000,96150.125000 UNION ALL
SELECT 'BOB BELL','Mar 1 2012 12:00AM',20844.970000,87311.985000 UNION ALL
SELECT 'BOB BELL','Apr 1 2012 12:00AM',20488.115000,91044.300000 UNION ALL
SELECT 'BOB BELL','May 1 2012 12:00AM',16773.735000,93072.740000 UNION ALL
SELECT 'BOB BELL','Jun 1 2012 12:00AM',15490.645000,78624.820000 UNION ALL
SELECT 'BOB BELL','Jul 1 2012 12:00AM',17020.980000,85416.720000 UNION ALL
SELECT 'BOB BELL','Aug 1 2012 12:00AM',24843.085000,116591.805000 UNION ALL
SELECT 'BOB BELL','Sep 1 2012 12:00AM',13230.865000,66873.425000 UNION ALL
SELECT 'BOB BELL','Oct 1 2012 12:00AM',31884.945000,147226.085000 UNION ALL
SELECT 'BOB BELL','Nov 1 2012 12:00AM',10969.280000,58353.880000 UNION ALL
SELECT 'BOB BELL','Dec 1 2012 12:00AM',15020.235000,74394.460000 UNION ALL
SELECT 'BOB BELL','Jan 1 2013 12:00AM',18598.945000,99750.470000 UNION ALL
SELECT 'BOB BELL','Feb 1 2013 12:00AM',21347.365000,84373.895000 UNION ALL
SELECT 'BOB BELL','Mar 1 2013 12:00AM',19124.885000,84118.720000 UNION ALL
SELECT 'BOB BELL','Apr 1 2013 12:00AM',19644.570000,105655.665000 UNION ALL
SELECT 'BOB BELL','May 1 2013 12:00AM',18845.560000,117610.330000 UNION ALL
SELECT 'BOB BELL','Jun 1 2013 12:00AM',26348.610000,115589.650000 UNION ALL
SELECT 'BOB BELL','Jul 1 2013 12:00AM',11888.070000,68834.730000 UNION ALL
SELECT 'BOB BELL','Aug 1 2013 12:00AM',18728.560000,108363.950000 UNION ALL
SELECT 'BOB BELL','Sep 1 2013 12:00AM',20630.130000,85104.330000 UNION ALL
SELECT 'BOB BELL','Oct 1 2013 12:00AM',30607.950000,141205.220000 UNION ALL
SELECT 'BOB BELL','Nov 1 2013 12:00AM',1333.650000,7149.930000 UNION ALL
SELECT 'DUSTY SHAW','Aug 1 2012 12:00AM',61093.760000,141631.890000 UNION ALL
SELECT 'DUSTY SHAW','Sep 1 2012 12:00AM',53318.010000,121183.350000 UNION ALL
SELECT 'DUSTY SHAW','Oct 1 2012 12:00AM',82703.800000,219403.610000 UNION ALL
SELECT 'DUSTY SHAW','Nov 1 2012 12:00AM',43394.650000,122292.470000 UNION ALL
SELECT 'DUSTY SHAW','Dec 1 2012 12:00AM',52732.600000,141471.950000 UNION ALL
SELECT 'DUSTY SHAW','Jan 1 2013 12:00AM',60383.600000,153577.970000 UNION ALL
SELECT 'DUSTY SHAW','Feb 1 2013 12:00AM',50722.640000,133125.130000 UNION ALL
SELECT 'DUSTY SHAW','Mar 1 2013 12:00AM',71169.020000,181342.550000 UNION ALL
SELECT 'DUSTY SHAW','Apr 1 2013 12:00AM',96688.240000,263879.560000 UNION ALL
SELECT 'DUSTY SHAW','May 1 2013 12:00AM',60497.610000,150706.530000 UNION ALL
SELECT 'DUSTY SHAW','Jun 1 2013 12:00AM',49878.740000,130276.200000 UNION ALL
SELECT 'DUSTY SHAW','Jul 1 2013 12:00AM',93305.670000,228358.470000 UNION ALL
SELECT 'DUSTY SHAW','Aug 1 2013 12:00AM',69704.320000,165224.480000 UNION ALL
SELECT 'DUSTY SHAW','Sep 1 2013 12:00AM',71745.230000,172682.690000 UNION ALL
SELECT 'DUSTY SHAW','Oct 1 2013 12:00AM',64426.080000,171378.470000 UNION ALL
SELECT 'DUSTY SHAW','Nov 1 2013 12:00AM',270.000000,417.000000 UNION ALL
SELECT 'SANDY LIN','Nov 1 2011 12:00AM',53074.060000,216641.495000 UNION ALL
SELECT 'SANDY LIN','Dec 1 2011 12:00AM',57539.705000,248445.755000 UNION ALL
SELECT 'SANDY LIN','Jan 1 2012 12:00AM',53664.675000,224697.950000 UNION ALL
SELECT 'SANDY LIN','Feb 1 2012 12:00AM',54210.005000,224118.225000 UNION ALL
SELECT 'SANDY LIN','Mar 1 2012 12:00AM',49113.710000,209394.495000 UNION ALL
SELECT 'SANDY LIN','Apr 1 2012 12:00AM',42010.585000,185807.225000 UNION ALL
SELECT 'SANDY LIN','May 1 2012 12:00AM',52015.735000,218969.730000 UNION ALL
SELECT 'SANDY LIN','Jun 1 2012 12:00AM',41729.475000,183691.270000 UNION ALL
SELECT 'SANDY LIN','Jul 1 2012 12:00AM',37874.450000,175695.945000 UNION ALL
SELECT 'SANDY LIN','Aug 1 2012 12:00AM',33058.850000,151730.875000 UNION ALL
SELECT 'SANDY LIN','Sep 1 2012 12:00AM',24032.630000,104201.010000 UNION ALL
SELECT 'SANDY LIN','Oct 1 2012 12:00AM',47901.605000,207828.800000 UNION ALL
SELECT 'SANDY LIN','Nov 1 2012 12:00AM',33122.335000,129062.190000 UNION ALL
SELECT 'SANDY LIN','Dec 1 2012 12:00AM',50311.070000,219005.315000 UNION ALL
SELECT 'SANDY LIN','Jan 1 2013 12:00AM',34285.055000,163617.740000 UNION ALL
SELECT 'SANDY LIN','Feb 1 2013 12:00AM',37850.400000,154223.070000 UNION ALL
SELECT 'SANDY LIN','Mar 1 2013 12:00AM',49324.690000,193472.000000 UNION ALL
SELECT 'SANDY LIN','Apr 1 2013 12:00AM',74341.055000,285085.825000 UNION ALL
SELECT 'SANDY LIN','May 1 2013 12:00AM',36047.675000,173105.340000 UNION ALL
SELECT 'SANDY LIN','Jun 1 2013 12:00AM',41784.610000,186530.225000 UNION ALL
SELECT 'SANDY LIN','Jul 1 2013 12:00AM',45242.995000,214912.975000 UNION ALL
SELECT 'SANDY LIN','Aug 1 2013 12:00AM',60568.860000,234226.735000 UNION ALL
SELECT 'SANDY LIN','Sep 1 2013 12:00AM',33589.450000,140016.095000 UNION ALL
SELECT 'SANDY LIN','Oct 1 2013 12:00AM',76315.355000,287042.425000 UNION ALL
SELECT 'SANDY LIN','Nov 1 2013 12:00AM',1484.730000,5249.780000
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #GPbyMonth ON
--Better expected results sample table:
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#GPByMonth_Results','U') IS NOT NULL
DROP TABLE #GPByMonth_Results
--===== Create the test table with
CREATE TABLE #GPByMonth_Results (
[SPRSNSLN] [VARCHAR](61) NOT NULL,
[GPDate_CY1] [DATETIME] NOT NULL,
[GPDate_PY1] [DATETIME] NOT NULL,
[MonthlyGP_1] [Decimal] (14,6) NULL,
[MonthlyRev_1] [Decimal] (14,6) NULL,
[MonthlyYOYGPDiff_1] [Decimal] (14,6) NULL,
[GPDate_CY2] [DATETIME] NOT NULL,
[GPDate_PY2] [DATETIME] NOT NULL,
[MonthlyGP_2] [Decimal] (14,6) NULL,
[MonthlyRev_2] [Decimal] (14,6) NULL,
[MonthlyYOYGPDiff_2] [Decimal] (14,6) NULL,
[GPDate_CY3] [DATETIME] NOT NULL,
[GPDate_PY3] [DATETIME] NOT NULL,
[MonthlyGP_3] [Decimal] (14,6) NULL,
[MonthlyRev_3] [Decimal] (14,6) NULL,
[MonthlyYOYGPDiff_3] [Decimal] (14,6) NULL,
[GPDate_CY4] [DATETIME] NOT NULL,
[GPDate_PY4] [DATETIME] NOT NULL,
[MonthlyGP_4] [Decimal] (14,6) NULL,
[MonthlyRev_4] [Decimal] (14,6) NULL,
[MonthlyYOYGPDiff_4] [Decimal] (14,6) NULL,
[GPDate_CY5] [DATETIME] NOT NULL,
[GPDate_PY5] [DATETIME] NOT NULL,
[MonthlyGP_5] [Decimal] (14,6) NULL,
[MonthlyRev_5] [Decimal] (14,6) NULL,
[MonthlyYOYGPDiff_5] [Decimal] (14,6) NULL,
[GPDate_CY6] [DATETIME] NOT NULL,
[GPDate_PY6] [DATETIME] NOT NULL,
[MonthlyGP_6] [Decimal] (14,6) NULL,
[MonthlyRev_6] [Decimal] (14,6) NULL,
[MonthlyYOYGPDiff_6] [Decimal] (14,6) NULL,
[GPDate_CY7] [DATETIME] NOT NULL,
[GPDate_PY7] [DATETIME] NOT NULL,
[MonthlyGP_7] [Decimal] (14,6) NULL,
[MonthlyRev_7] [Decimal] (14,6) NULL,
[MonthlyYOYGPDiff_7] [Decimal] (14,6) NULL,
[GPDate_CY8] [DATETIME] NOT NULL,
[GPDate_PY8] [DATETIME] NOT NULL,
[MonthlyGP_8] [Decimal] (14,6) NULL,
[MonthlyRev_8] [Decimal] (14,6) NULL,
[MonthlyYOYGPDiff_8] [Decimal] (14,6) NULL,
[GPDate_CY9] [DATETIME] NOT NULL,
[GPDate_PY9] [DATETIME] NOT NULL,
[MonthlyGP_9] [Decimal] (14,6) NULL,
[MonthlyRev_9] [Decimal] (14,6) NULL,
[MonthlyYOYGPDiff_9] [Decimal] (14,6) NULL,
[GPDate_CY10] [DATETIME] NOT NULL,
[GPDate_PY10] [DATETIME] NOT NULL,
[MonthlyGP_10] [Decimal] (14,6) NULL,
[MonthlyRev_10] [Decimal] (14,6) NULL,
[MonthlyYOYGPDiff_10] [Decimal] (14,6) NULL,
[GPDate_CY11] [DATETIME] NOT NULL,
[GPDate_PY11] [DATETIME] NOT NULL,
[MonthlyGP_11] [Decimal] (14,6) NULL,
[MonthlyRev_11] [Decimal] (14,6) NULL,
[MonthlyYOYGPDiff_11] [Decimal] (14,6) NULL,
[GPDate_CY12] [DATETIME] NOT NULL,
[GPDate_PY12] [DATETIME] NOT NULL,
[MonthlyGP_12] [Decimal] (14,6) NULL,
[MonthlyRev_12] [Decimal] (14,6) NULL,
[MonthlyYOYGPDiff_12] [Decimal] (14,6) NULL,
)
December 16, 2013 at 11:36 am
I hope this will help you. You just need to complete the rest of the months with Copy-Paste-Replace.
WITH CTE AS(
SELECT TOP 12 DATEADD( mm, (ROW_NUMBER() OVER(ORDER BY(SELECT NULL)) -1) * -1, DATEADD( mm, DATEDIFF( mm, 0, GETDATE()), 0)) period
FROM sys.all_columns
),
Data AS(
SELECT period,
P.SPRSNSLN AS SPRSNSLN,
ISNULL( Cur.MonthlyGP, 0) MonthlyGP,
ISNULL( Cur.MonthlyRev, 0) MonthlyRev,
ISNULL( Cur.MonthlyGP, 0) - ISNULL( Prev.MonthlyGP, 0) AS MonthlyYOYGPDiff,
ROW_NUMBER() OVER( PARTITION BY P.SPRSNSLN ORDER BY period) MonthNo
FROM CTE
CROSS
JOIN (SELECT DISTINCT SPRSNSLN
FROM GPByMonth) P
LEFT
JOIN GPByMonth Cur ON Cur.M = MONTH( period)
AND Cur.Y = YEAR( Period)
AND P.SPRSNSLN = Cur.SPRSNSLN
LEFT
JOIN GPByMonth Prev ON Prev.M = MONTH( period)
AND Prev.Y = YEAR( Period) - 1
AND P.SPRSNSLN = Prev.SPRSNSLN
)
SELECT SPRSNSLN,
MAX( CASE WHEN MonthNo = 1 THEN period END) AS GPDate_CY1,
MAX( CASE WHEN MonthNo = 1 THEN DATEADD( YEAR, -1, period) END) AS GPDate_PY1,
SUM( CASE WHEN MonthNo = 1 THEN MonthlyGP END) AS MonthlyGP_1,
SUM( CASE WHEN MonthNo = 1 THEN MonthlyRev END) AS MonthlyRev_1,
SUM( CASE WHEN MonthNo = 1 THEN MonthlyYOYGPDiff END) AS MonthlyYOYGPDiff_1,
MAX( CASE WHEN MonthNo = 2 THEN period END) AS GPDate_CY2,
MAX( CASE WHEN MonthNo = 2 THEN DATEADD( YEAR, -1, period) END) AS GPDate_PY2,
SUM( CASE WHEN MonthNo = 2 THEN MonthlyGP END) AS MonthlyGP_2,
SUM( CASE WHEN MonthNo = 2 THEN MonthlyRev END) AS MonthlyRev_2,
SUM( CASE WHEN MonthNo = 2 THEN MonthlyYOYGPDiff END) AS MonthlyYOYGPDiff_2,
MAX( CASE WHEN MonthNo = 3 THEN period END) AS GPDate_CY3,
MAX( CASE WHEN MonthNo = 3 THEN DATEADD( YEAR, -1, period) END) AS GPDate_PY3,
SUM( CASE WHEN MonthNo = 3 THEN MonthlyGP END) AS MonthlyGP_3,
SUM( CASE WHEN MonthNo = 3 THEN MonthlyRev END) AS MonthlyRev_3,
SUM( CASE WHEN MonthNo = 3 THEN MonthlyYOYGPDiff END) AS MonthlyYOYGPDiff_3
FROM Data
GROUP BY SPRSNSLN
ORDER BY SPRSNSLN
December 16, 2013 at 1:18 pm
BAM! Well done sir. This works perfectly!
December 16, 2013 at 1:37 pm
I'm glad I could help. You made it easy. Thank you for taking the time of posting with all the details.
December 17, 2013 at 1:56 am
If this data is for SSRS then you do not have to pivot in T-SQL as you can use a matrix to do the pivoting and simplify the query.
Far away is close at hand in the images of elsewhere.
Anon.
December 17, 2013 at 2:24 pm
Good point. Only this is just a small part of a much larger dataset that all has to be in the same matrix. Unfortunately, SSRS does not like to have more than one DataSet in a matrix without a bunch of hoops and gotchas; so I use Stored Procedures to do most of the heavy lifting and only return a small amount of rows. This keeps the report processing lean and mean.
The original dataset would be well over 153,000 rows over 100 columns if I let the matrices do all the walking. When I did that the report took over 45 minutes to process. Now, with this final piece I can whittle that monster dataset down to the 24 rows of salespeople and 60 columns. I have found that in SSRS if you have more than just about 40 records and 50-60 columns of data the report is too slow for live viewing; and has to be cached nightly; which will not work for the salespeople who must check the report multiple times per day to see where they are. Also, because we use Dynamics GP (another "brilliant" Microsoft invention) the data is so normalized that it takes no less than 3-4 complex joins just to get a list of what was sold and who sold it for any given point in time.
I know its a long reply, but I wanted to let you know I appreciate the thought and have actually gone down that rabbit hole with no success.
Cheers!
December 18, 2013 at 4:12 am
asheppardwork (12/17/2013)
.. but I wanted to let you know I appreciate the thought and have actually gone down that rabbit hole with no success
No problem 😀
Just a thought I had, as I sometimes get hung up on doing everything in SQL and forgetting what SSRS can do.
As with all possible solutions, 'it depends' :w00t:
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply