December 3, 2013 at 3:44 pm
We are a company that sells items and ships them in bulk.
I am tasked with making a report in SSRS 2008 R2 (native mode) that will give among other things the Average Gross Profit (GP) % (which is a Weighted Average of Percentages ) for each of the prior six months (i.e. the July report would have Jan-Jun data to get the average). The report is run monthly for the prior month on the 3rd business day of each month.
I have already done this in Crystal Reports XI with several formulas all culminating in one answer, but we are no longer supporting CR since the company wants to move all reports to SSRS (Native Mode: the rest of the system is going to the cloud and that breaks SharePoint's Integrated mode - btw I absolutely cannot stand the integrated mode's report server).
The basic formula is thus:
A = Number of Records/Sales in Month1
B = Number of Records/Sales in Month2
C = Number of Records/Sales in Month3
D = A+B+C
GP% = (SUM(GP by CUSTNmbr by Month)/SUM(Expense by CUSTNmbr by Month))*100 --(currently in use as GP%)
A * (GP% of Month1) = X
B * (GP% of Month2) = Y
C * (GP% of Month3) = Z
(X+Y+Z)/D = Avg GP% (weighted average)
So far in my attempt to pencil out the problem I have the following for a single month's numbers:
DocDate: date of sale - DateTime
CUSTNMBR: unique customer id - 6dig - XXX123
ROWID: unique number to each row in the DB - int
GrossProfit: nuff said - decimal (14,6)
ExPx: Expense - decimal (14,6)
DELCARE @StartDate DateTime;
DECLARE @EndDate DateTime;
Set @Startdate = '12/01/2013';
Set @EndDate = '12/31/2013';
SELECT distinct CUSTNMBR,DocDate,
COUNT(ROWID) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate)) AS A,
(SUM(GrossProfit) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate)) / SUM(ExPx) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate)) ) *100 AS GPPct_Month1,
COUNT(ROWID) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate))* ((SUM(GrossProfit) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate)) / SUM(ExPx) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate)) ) *100) AS X
FROM
ZZ_OmniDGPsrc
WHERE
(
DocDate >= @StartDate AND
DOCDATE <= @EndDate
)
AND
ExPx <> 0
ORDER BY
DocDate
Sample results:
------------------------------------------------
CUSNMBR~~~~~~~DOCDATE~~~~~~~~~~~A~~~~GPPct_Month1~~~~~~~X
NAT700~~~~~~~2013-12-02 00:00:00.000~~2~~~~29.305700~~~~~~~58.611400
AXE100~~~~~~~2013-12-02 00:00:00.000~~6~~~~38.910800~~~~~~~233.464800
COZ200~~~~~~~2013-12-02 00:00:00.000~~31~~~~22.954600~~~~~~~711.592600
I can see a couple of ways of iterating through the companies (over 1,000) for each month. I could use a CTE or a Cursor; but either way it looks like I'll need a block of code for each of the twelve months and then return results to a temp table for the prior six months to the StartDate.
The final product needs to either produce a table to draw from or be able to be in a StoredProcedure so the report can pull from a source with a saved execution plan.
How would you solve this?
Is there a advantage over the cursor/CTE approach?
Can it be done in a set or at least a less complex way?
December 3, 2013 at 5:25 pm
This problem should be solvable without a loop.
However, if you'd like a tested solution you're going to need to help us help you by providing:
- DDL to create the tables involved
- Consumable sample data as SQL that runs to populate the table above
- Expected results based on the sample data you provide
With those in hand, I'm sure someone can provide you with a tested, working solution. Perhaps even me.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
December 4, 2013 at 7:31 am
Sounds great!
I'll take a few moments today and get that together.
December 4, 2013 at 9:23 am
BTW - ignore my first query, I made some grouping mistakes and the numbers were way off :).
However, this time I am simply giving the DDL statement and the long form process that needs to happen.
Let me know if this is what is needed or if there is something missing.
And again, thank you for the help.
CREATE TABLE Test_OmniDGPsrc
(
ROWID INT NOT NULL,
PKGUID UNIQUEIDENTIFIER NOT NULL,
BRANCH CHAR(65) NOT NULL,
CUSTNAME CHAR(65) NOT NULL,
CUSTNMBR CHAR(15) NOT NULL,
SLPRSNID VARCHAR(15) NULL,
DOCDATE DATETIME NOT NULL,
ExPx NUMERIC(21,6) NULL,
Cost NUMERIC(20,5) NULL,
GrossProfit DECIMAL (14,6) NULL,
)
INSERT INTO Test_OmniDGPsrc
VALUES
(1,NEWID(),'YYZ','Joes Crabs','JJC112','1710','10/01/2013',248.00,204.00,44.00),
(2,NEWID(),'YYZ','Joes Crabs','JJC112','1815','10/15/2013',95.00,66.50,28.50),
(3,NEWID(),'CBS','Larry Lobster','LLB227','1910','10/05/2013',68.00,40.07,27.93),
(4,NEWID(),'CBS','Larry Lobster','LLB227','1910','10/15/2013',1225.00,0,1225.00),
(5,NEWID(),'CBS','Larry Lobster','LLB227','1910','10/05/2013',160.00,127.96,32.04),
(6,NEWID(),'CBS','Larry Lobster','LLB227','1910','10/20/2013',157.00,118.35,38.65),
(7,NEWID(),'CBS','Larry Lobster','LLB227','1910','10/07/2013',69.78,69.78,0.00),
(8,NEWID(),'CBS','Larry Lobster','LLB227','1910','10/15/2013',20.00,14.55,5.45),
(9,NEWID(),'XXX','Bobs Flowers','BBF171','1765','10/13/2013',1932.00,1695.84,236.16),
(10,NEWID(),'XXX','Macks Trucks','MMT885','2210','10/15/2013',9.00,5.65,3.35),
(11,NEWID(),'BBC','Joes Crabs','JJC112','1710','11/01/2013',95.00,66.50,28.50),
(12,NEWID(),'BBC','Joes Crabs','JJC112','1815','11/15/2013',69.78,69.78,0.00),
(13,NEWID(),'BBC','Joes Crabs','JJC112','1710','11/20/2013',21.25,16.52,4.73),
(14,NEWID(),'CBS','Larry Lobster','LLB227','1910','11/15/2013',1775.00,0,1775.00),
(15,NEWID(),'XXX','Bobs Flowers','BBF171','1765','11/15/2013',207.00,173.70,71.70),
(16,NEWID(),'XXX','Macks Trucks','MMT885','2210','11/15/2013',62.25,32.28,29.97),
(17,NEWID(),'ABC','Joes Crabs','JJC112','1710','12/01/2013',1496.25,479.55,1016.70),
(18,NEWID(),'ABC','Joes Crabs','JJC112','1815','12/15/2013',997.50,318.35,679.15),
(19,NEWID(),'ABC','Joes Crabs','JJC112','1710','12/20/2013',21.25,16.52,4.73),
(20,NEWID(),'ABC','Joes Crabs','JJC112','1815','12/15/2013',22.50,14.88,7.62),
(21,NEWID(),'ABC','Joes Crabs','JJC112','1815','12/15/2013',44.75,32.91,11.84),
(22,NEWID(),'CBS','Larry Lobster','LLB227','1910','12/05/2013',520.00,347.10,172.90),
(23,NEWID(),'CBS','Larry Lobster','LLB227','1910','12/15/2013',1225.00,0,1225.00),
(24,NEWID(),'XXX','Bobs Flowers','BBF171','1765','12/15/2013',205.00,153.50,51.50),
(25,NEWID(),'XXX','Macks Trucks','MMT885','2210','12/15/2013',21.75,14.73,7.02);
Sample calculations and results (longform):
FOR COMPANY BBF171
A = NUMBER OF RECORDS IN MONTH1 BY CUSTNMBR
A = 1
B = NUMBER OF RECORDS IN MONTH2 BY CUSTNMBR
B = 1
C = NUMBER OF RECORDS IN MONTH3 BY CUSTNMBR
C = 1
D = (A+B+C)
D= (1+1+1)
D= 3
---------------------------------
A * (GP% OF MONTH1) = X
GP% OF MONTH1 FOR BBF171 (GROSS/EXPX)
236.16 / 1932.00 = .1222
1 * (.1222) = X
X = .1222
---------------------------------
B * (GP% OF MONTH2) = Y
GP% OF MONTH2 FOR BBF171 (GROSS/EXPX)
71.70 / 207.00 = .346
1 * (.346) = Y
Y = .346
---------------------------------
C * (GP% OF MONTH3) = Z
GP% OF MONTH3 FOR BBF171 (GROSS/EXPX)
51.50 / 205.00 = .2512
1 * (.2512) = Z
Z = .2512
----------------------------
FOR COMPANY BBF171
(.1222+.346+.2512)/3 = .2398
AVG GP% FOR BBF171 = 23.98% <-- final result wanted for company BBF171
December 5, 2013 at 7:41 am
FYI - here is the better query (grouping handles properly) for a single month:
DECLARE @StartDate DateTime;
DECLARE @EndDate DateTime;
Set @Startdate = '10/01/2013';
Set @EndDate = '10/31/2013';
SELECT distinct CUSTNMBR,MONTH(DocDate) AS Mth,YEAR(DocDate) AS Yr,
COUNT(ROWID) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate)) AS A,
SUM(GrossProfit) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate)) AS M1_Gross,
SUM(ExPx) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate)) AS M1_ExPx,
(SUM(GrossProfit) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate)) / SUM(ExPx) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate)) ) AS GPPct_Month1,
COUNT(ROWID) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate))* ( SUM(GrossProfit) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate)) / SUM(ExPx) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate)) ) AS X
FROM
Test_OmniDGPsrc
WHERE
(
DocDate >= @StartDate AND
DOCDATE <= @EndDate
)
AND
ExPx <> 0
ORDER BY
CUSTNMBR
--cheers
December 5, 2013 at 7:59 am
Here is a month by month approach, but I need to have the best way to "loop" through a given set of six prior months to a month range that will be passed by a SP; the "StartDate" and "EndDate" will always be for a given month from the 1st to the last day.
DECLARE @StartDate DateTime;
DECLARE @EndDate DateTime;
Set @Startdate = '10/01/2013';
Set @EndDate = '10/31/2013';
SELECT distinct CUSTNMBR,MONTH(DocDate) AS Mth,YEAR(DocDate) AS Yr,
COUNT(ROWID) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate)) AS A,
SUM(GrossProfit) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate)) AS M1_Gross,
SUM(ExPx) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate)) AS M1_ExPx,
(SUM(GrossProfit) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate)) / SUM(ExPx) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate)) ) AS GPPct_Month1,
COUNT(ROWID) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate))* ( SUM(GrossProfit) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate)) / SUM(ExPx) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate)) ) AS X
INTO #_TempM1
FROM
Test_OmniDGPsrc
WHERE
(
DocDate >= @StartDate AND
DOCDATE <= @EndDate
)
AND
ExPx <> 0
ORDER BY
CUSTNMBR
--============= Month_2
Set @Startdate = '11/01/2013';
Set @EndDate = '11/30/2013';
SELECT distinct CUSTNMBR,MONTH(DocDate) AS Mth,YEAR(DocDate) AS Yr,
COUNT(ROWID) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate)) AS B,
SUM(GrossProfit) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate)) AS M2_Gross,
SUM(ExPx) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate)) AS M2_ExPx,
(SUM(GrossProfit) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate)) / SUM(ExPx) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate)) ) AS GPPct_Month2,
COUNT(ROWID) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate))* ( SUM(GrossProfit) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate)) / SUM(ExPx) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate)) ) AS Y
INTO #_TempM2
FROM
Test_OmniDGPsrc
WHERE
(
DocDate >= @StartDate AND
DOCDATE <= @EndDate
)
AND
ExPx <> 0
ORDER BY
CUSTNMBR
--============= Month_3
Set @Startdate = '12/01/2013';
Set @EndDate = '12/31/2013';
SELECT distinct CUSTNMBR,MONTH(DocDate) AS Mth,YEAR(DocDate) AS Yr,
COUNT(ROWID) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate)) AS C,
SUM(GrossProfit) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate)) AS M3_Gross,
SUM(ExPx) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate)) AS M3_ExPx,
(SUM(GrossProfit) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate)) / SUM(ExPx) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate)) ) AS GPPct_Month3,
COUNT(ROWID) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate))* ( SUM(GrossProfit) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate)) / SUM(ExPx) OVER(PARTITION BY CUSTNMBR,MONTH(DocDate),YEAR(DocDate)) ) AS Z
INTO #_TempM3
FROM
Test_OmniDGPsrc
WHERE
(
DocDate >= @StartDate AND
DOCDATE <= @EndDate
)
AND
ExPx <> 0
ORDER BY
CUSTNMBR
/*
Drop Table #_TempM1
Drop Table #_TempM2
Drop Table #_TempM3
*/
SELECT M1.CUSTNMBR,
SUM(A+B+C) OVER(PARTITION BY M1.CUSTNMBR) as D,
SUM(X+Y+Z) OVER(PARTITION BY M1.CUSTNMBR) / SUM(A+B+C) OVER(PARTITION BY M1.CUSTNMBR) AS AvgGPPct
FROM #_TempM1 M1
INNER JOIN #_TempM2 M2 ON M1.CUSTNMBR = M2.CUSTNMBR
INNER JOIN #_TempM3 M3 ON M2.CUSTNMBR = M3.CUSTNMBR
December 6, 2013 at 10:37 am
You can do this for all customers in one operation by first aggregating the data by CUSTNMBR and MONTHNBR, then aggregating that data by CUSTNMBR for the whole period.
Here's the code I used to do this with your sample data:
DECLARE @startdate datetime = '20131001'
,@enddate datetime = '20131231'
;WITH cte AS (
SELECT CUSTNMBR,
COUNT(ROWID) AS A,
-- ^^^ This what you were calling A for month 1, B for month 2, etc.
COUNT(ROWID) * ((SUM(GrossProfit)/SUM(ExPx)) * 100) AS X
-- ^^^ This is what you were calling X for month 1, Y for month 2, etc.:
-- (SUM(GrossProfit)/SUM(ExPx)) * 100 = GP%
FROM dbo.Test_OmniDGPsrc
WHERE DOCDATE >= @startdate AND DOCDATE <= @enddate
GROUP BY CUSTNMBR, MONTH(DOCDATE)
)
SELECT CUSTNMBR,
SUM(X)/SUM(A) AS AVG_GP_PCT
-- ^^^ SUM(X) is what you were calling (X+Y+Z) and
-- SUM(A) is what you were calling D, or (A+B+C)
FROM CTE
GROUP BY CUSTNMBR
Results:
CUSTNMBRAVG_GP_PCT
BBF171 23.994366
JJC112 42.891260
LLB227 81.040200
MMT885 39.214166
I'm practically certain that slight difference in the result for customer BBF171 is due to loss of precision from rounding in your manual calculations.
If you want to do this on a customer-by-customer basis, just add an @custNmbr variable and include it in the WHERE clause of the CTE.
Let us know how this works for you.
Jason
Jason Wolfkill
December 6, 2013 at 12:31 pm
Thank you very much! I will try this right away. If it works, I totally owe you a beer :exclamationmark:
December 6, 2013 at 12:46 pm
I don't know why I had a mental block for doing it this way; but this is perfect. All 868 rows returned in less than 1 second. Brilliantly done. Thanks again. :w00t:
December 6, 2013 at 1:19 pm
asheppardwork (12/6/2013)
I don't know why I had a mental block for doing it this way; but this is perfect. All 868 rows returned in less than 1 second. Brilliantly done. Thanks again. :w00t:
Glad to hear it. I'm not sure that I brought anything more to the party than a fresh perspective on the problem, but I'm happy that I could help.
Jason Wolfkill
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply