February 22, 2015 at 3:31 pm
I have 3 2005 SQL tables, an order table, quote table and invoice table. Structured like this:
SO TABLE
Rep Code SO Amount SO Date
1 100 1/2/2015
2 50 2/15/2015
3 20 2/20/2015
2 50 2/20/2015
1 85 2/20/2015
SQ Table
Rep Code SQ Amount SQ Date
1 50 2/2/2015
2 14 2/18/2015
3 67 2/19/2015
1 96 2/20/2015
2 54 2/20/2015
INV Table
Rep Code INV Amount INV Date
1 654 2/2/2015
2 312 2/18/2015
3 54 2/19/2015
1 6 2/20/2015
3 48 2/20/2015
What I want to do is write a query which will sum up each amount by rep code, so it would look something like this:
Rep Code SO Total SQ Total Inv Total
1 185 146 660
2 100 68 312
3 20 67 102
I would also like it to only pull the totals if the appropriate date for each one is lets say in the last week. (So Today -7)
Let me know if this needs any clarification. Thanks for the help!!!
February 22, 2015 at 8:14 pm
Perhaps this?
WITH SO (RepCode, SOAmount, SODate) AS
(
SELECT 1, 100, '1/2/2015'
UNION ALL SELECT 2, 50, '2/15/2015'
UNION ALL SELECT 3, 20, '2/20/2015'
UNION ALL SELECT 2, 50, '2/20/2015'
UNION ALL SELECT 1, 85, '2/20/2015'
),
SQ (RepCode, SQAmount, SQDate) AS
(
SELECT 1, 50, '2/2/2015'
UNION ALL SELECT 2, 14, '2/18/2015'
UNION ALL SELECT 3, 67, '2/19/2015'
UNION ALL SELECT 1, 96, '2/20/2015'
UNION ALL SELECT 2, 54, '2/20/2015'
),
INV (RepCode, INVAmount, INVDate) AS
(
SELECT 1, 654, '2/2/2015'
UNION ALL SELECT 2, 312, '2/18/2015'
UNION ALL SELECT 3, 54, '2/19/2015'
UNION ALL SELECT 1, 6, '2/20/2015'
UNION ALL SELECT 3, 48, '2/20/2015'
)
SELECT RepCode
,SOTotal = SUM(CASE [Type] WHEN 'SO' THEN SOAmount END)
,SQTotal = SUM(CASE [Type] WHEN 'SQ' THEN SOAmount END)
,INVTotal = SUM(CASE [Type] WHEN 'INV' THEN SOAmount END)
FROM
(
SELECT RepCode, SOAmount, SODate, [Type]='SO'
FROM SO
WHERE SODate >= DATEADD(day, -7, GETDATE())
UNION ALL
SELECT RepCode, SQAmount, SQDate, [Type]='SQ'
FROM SQ
WHERE SQDate >= DATEADD(day, -7, GETDATE())
UNION ALL
SELECT RepCode, INVAmount, INVDate, [Type]='INV'
FROM INV
WHERE INVDate >= DATEADD(day, -7, GETDATE())
) a
GROUP BY RepCode;
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
February 23, 2015 at 11:31 am
Assuming you have a Rep table that lists all active reps.
SELECT r.RepCode,
IsNull((Select SUM(s.SO_Amount)
FROM SO_Table so
WHERE so.RepCode = r.RepCode
AND so.SO_Date > DATEADD(Day, -7, GetDate())),0) as SO_Total,
IsNull((Select SUM(s.SQ_Amount)
FROM SQ_Table sq
WHERE sq.RepCode = r.RepCode
AND sq.SQ_Date > DATEADD(Day, -7, GetDate())),0) as SQ_Total,
IsNull((Select SUM(i.Inv_Amount)
FROM INV_Table i
WHERE i.RepCode = r.RepCode
AND i.Inv_Date > DATEADD(Day, -7, GetDate())),0) as INV_Total
FROM Reps r
WHERE r.RepActive = 'Y'
This also assumes you have a field in the Reps table (RepActive) that identifies your active reps.
February 23, 2015 at 1:05 pm
I didn't have a separate rep table. I thought about making one but I think I was able to figure out. This is the code I used:
SELECT t.RepCode ,
SUM(SoAmount) AS SoAmount ,
SUM(SqAmount) AS SqAmount ,
SUM(InvAmount) AS InvAmount
FROM (
SELECT COALESCE(so.RepCode, sq.RepCode, inv.RepCode) AS RepCode ,
ISNULL(so.SoAmount, 0) AS SoAmount ,
ISNULL(sq.SqAmount, 0) AS SqAmount ,
ISNULL(inv.InvAmount, 0) AS InvAmount
FROM ( SELECT RepCode ,
SUM(SoAmount) AS SoAmount
FROM SO
WHERE SoDate >= @start_date
AND SoDate <= @end_date
GROUP BY RepCode
) so
FULL JOIN ( SELECT RepCode ,
SUM(SqAmount) AS SqAmount
FROM SQ
WHERE SqDate >= @start_date
AND SqDate <= @end_date
GROUP BY RepCode
) sq ON so.RepCode = sq.RepCode
FULL JOIN ( SELECT RepCode ,
SUM(InvAmount) AS InvAmount
FROM INV
WHERE InvDate >= @start_date
AND InvDate <= @end_date
GROUP BY RepCode
) inv ON inv.RepCode = sq.RepCode
) t
GROUP BY t.RepCode
February 23, 2015 at 1:13 pm
From a data integrity standpoint you should REALLY have a Rep table.
Glad to hear you got it working.
Good Luck.
February 23, 2015 at 4:42 pm
david.dunst (2/23/2015)
Assuming you have a Rep table that lists all active reps.
SELECT r.RepCode,
IsNull((Select SUM(s.SO_Amount)
FROM SO_Table so
WHERE so.RepCode = r.RepCode
AND so.SO_Date > DATEADD(Day, -7, GetDate())),0) as SO_Total,
IsNull((Select SUM(s.SQ_Amount)
FROM SQ_Table sq
WHERE sq.RepCode = r.RepCode
AND sq.SQ_Date > DATEADD(Day, -7, GetDate())),0) as SQ_Total,
IsNull((Select SUM(i.Inv_Amount)
FROM INV_Table i
WHERE i.RepCode = r.RepCode
AND i.Inv_Date > DATEADD(Day, -7, GetDate())),0) as INV_Total
FROM Reps r
WHERE r.RepActive = 'Y'
This also assumes you have a field in the Reps table (RepActive) that identifies your active reps.
If you want to designate reps as active or inactive, I suggest using an effective date column rather than a flag.
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
February 24, 2015 at 9:21 am
Thanks everyone.
David I took your advice and create a Rep table and then used the query that you posted, worked great
February 24, 2015 at 10:21 am
dwain.c is correct that once you have a Rep table, you should really capture the HireDate and TerminationDate.
Then all you need to do is correct the Rep table WHERE clause to look at TerminationDate.
Glad it's working.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply