July 30, 2013 at 4:19 am
Hi All,
SQL Server 2008.
I have the following problem I need to try and solve...
I have a list of salesmen and I need to return back running totals (grouped by salesman) for each from a start date until an end date with all dates in the date range returned...
The current structure if simplified for this example (entities are as is, but I've removed a lot of the attributes I don't require here)...
Sales_Man
SalesmanID int
Name varchar(50)
Order
OrderID int
SalemanID int
OrderDate DateTime
Order_Line
OrderLineID int
OrderID int
OrderLineValue money
I figured I need a calendar table for this so I return back rows for each salesman regardless if they made any sales...
Tally_Date
DateFull Datetime
Where I am so far...
SELECT A.[DateFull],
COALESCE(t.[SalesmanName], '') AS [SalesmanName],
COALESCE(t.[TotalSales], 0.00) AS [TotalSales]
FROM [Tally_Date] A
OUTER APPLY ( SELECTISNULL(SUM(A.[OrderLineValue]), 0.00) AS [TotalSales],
ISNULL(C.[SalesmanName], '') AS [SalesmanName]
FROM [Order_Line] A
LEFT OUTER JOIN [Order] B ON A.[OrderID] = B.[OrderID]
LEFT OUTER JOIN [SalesMan] C ON B.[SalesmanID] = C.[SalesmanID]
WHERE A.[OrderDate] <= [DateFull]
GROUP BYISNULL(C.[SalesmanName], '')
) AS t
WHEREA.[DateFull] BETWEEN @StartDate AND @EndDate)
ORDER BY [DateFull] ASC,
[SalesmanName]
This is return all the sales force with running totals, but salesmen only appear in the result set once they have at least one sale.
If I completely remove the SalesManName studd, I get a a full set of dates with a running total for all salesman correctly but as soon as I try to group by salesman it all goes wrong...
Once I get this working, I'd like to also try returning back weekly and monthly totals in a separate query instead of by individual date...
Can some one help as I've been banging my head against a wall on this for hours?
Many thanks
Charlotte
July 30, 2013 at 4:41 am
Hi Charlotte
Some sample data in the same format as you provided in this thread would help. Thanks.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 30, 2013 at 4:59 am
Apologies,
Below is a small script to create the data 🙂
CREATE TABLE [dbo].[Salesman](
[SalesmanID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Name] VARCHAR(50) NULL
CONSTRAINT [PK_Salesman] PRIMARY KEY CLUSTERED
(
[SalesmanID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Order](
[OrderID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[SalesmanID] [int] NULL,
[OrderDate] [DATETIME] NULL
CONSTRAINT [PK_Order] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Order_line](
[OrderLineID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[OrderID] [int] NULL,
[OrderLineValue] [money] NULL
CONSTRAINT [PK_Order_line] PRIMARY KEY CLUSTERED
(
[OrderLineID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO [Salesman]
([Name])
VALUES
('David'),
('Jack'),
('Sam'),
('Sue'),
('Alice')
GO
INSERT INTO [Order]
([SalesmanID], [OrderDate])
VALUES
(1, '2013-07-03'),
(1, '2013-07-03'),
(1, '2013-07-06'),
(1, '2013-07-07'),
(1, '2013-07-07'),
(2, '2013-07-02'),
(2, '2013-07-05'),
(2, '2013-07-05'),
(2, '2013-07-09'),
(3, '2013-07-08')
GO
INSERT INTO [Order_line]
([OrderID], [OrderLineValue])
VALUES
(1, 100.00),
(1, 50.00),
(2, 50.00),
(3, 100.00),
(3, 100.00),
(4, 50.00),
(5, 80.00),
(5, 20.00),
(6, 300.00),
(6, 200.00),
(7, 100.00),
(8, 100.00),
(9, 100.00),
(10, 100.00),
(10, 150.00)
GO
Here is my current result set for July - notice that the employees who have no sales don't show up and that Sam only appears about halfway down once his first sale is recorded...
2013-07-01 00:00:00.0000.0000
2013-07-02 00:00:00.000Jack500.0000
2013-07-03 00:00:00.000David200.0000
2013-07-03 00:00:00.000Jack500.0000
2013-07-04 00:00:00.000David200.0000
2013-07-04 00:00:00.000Jack500.0000
2013-07-05 00:00:00.000David200.0000
2013-07-05 00:00:00.000Jack700.0000
2013-07-06 00:00:00.000David400.0000
2013-07-06 00:00:00.000Jack700.0000
2013-07-07 00:00:00.000David550.0000
2013-07-07 00:00:00.000Jack700.0000
2013-07-08 00:00:00.000David550.0000
2013-07-08 00:00:00.000Jack700.0000
2013-07-08 00:00:00.000Sam250.0000
2013-07-09 00:00:00.000David550.0000
2013-07-09 00:00:00.000Jack800.0000
2013-07-09 00:00:00.000Sam250.0000
2013-07-10 00:00:00.000David550.0000
2013-07-10 00:00:00.000Jack800.0000
2013-07-10 00:00:00.000Sam250.0000
2013-07-11 00:00:00.000David550.0000
2013-07-11 00:00:00.000Jack800.0000
2013-07-11 00:00:00.000Sam250.0000
2013-07-12 00:00:00.000David550.0000
2013-07-12 00:00:00.000Jack800.0000
2013-07-12 00:00:00.000Sam250.0000
2013-07-13 00:00:00.000David550.0000
2013-07-13 00:00:00.000Jack800.0000
2013-07-13 00:00:00.000Sam250.0000
2013-07-14 00:00:00.000David550.0000
2013-07-14 00:00:00.000Jack800.0000
2013-07-14 00:00:00.000Sam250.0000
2013-07-15 00:00:00.000David550.0000
2013-07-15 00:00:00.000Jack800.0000
2013-07-15 00:00:00.000Sam250.0000
2013-07-16 00:00:00.000David550.0000
2013-07-16 00:00:00.000Jack800.0000
2013-07-16 00:00:00.000Sam250.0000
2013-07-17 00:00:00.000David550.0000
2013-07-17 00:00:00.000Jack800.0000
2013-07-17 00:00:00.000Sam250.0000
2013-07-18 00:00:00.000David550.0000
2013-07-18 00:00:00.000Jack800.0000
2013-07-18 00:00:00.000Sam250.0000
2013-07-19 00:00:00.000David550.0000
2013-07-19 00:00:00.000Jack800.0000
2013-07-19 00:00:00.000Sam250.0000
2013-07-20 00:00:00.000David550.0000
2013-07-20 00:00:00.000Jack800.0000
2013-07-20 00:00:00.000Sam250.0000
2013-07-21 00:00:00.000David550.0000
2013-07-21 00:00:00.000Jack800.0000
2013-07-21 00:00:00.000Sam250.0000
2013-07-22 00:00:00.000David550.0000
2013-07-22 00:00:00.000Jack800.0000
2013-07-22 00:00:00.000Sam250.0000
2013-07-23 00:00:00.000David550.0000
2013-07-23 00:00:00.000Jack800.0000
2013-07-23 00:00:00.000Sam250.0000
2013-07-24 00:00:00.000David550.0000
2013-07-24 00:00:00.000Jack800.0000
2013-07-24 00:00:00.000Sam250.0000
2013-07-25 00:00:00.000David550.0000
2013-07-25 00:00:00.000Jack800.0000
2013-07-25 00:00:00.000Sam250.0000
2013-07-26 00:00:00.000David550.0000
2013-07-26 00:00:00.000Jack800.0000
2013-07-26 00:00:00.000Sam250.0000
2013-07-27 00:00:00.000David550.0000
2013-07-27 00:00:00.000Jack800.0000
2013-07-27 00:00:00.000Sam250.0000
2013-07-28 00:00:00.000David550.0000
2013-07-28 00:00:00.000Jack800.0000
2013-07-28 00:00:00.000Sam250.0000
2013-07-29 00:00:00.000David550.0000
2013-07-29 00:00:00.000Jack800.0000
2013-07-29 00:00:00.000Sam250.0000
2013-07-30 00:00:00.000David550.0000
2013-07-30 00:00:00.000Jack800.0000
2013-07-30 00:00:00.000Sam250.0000
2013-07-31 00:00:00.000David550.0000
2013-07-31 00:00:00.000Jack800.0000
2013-07-31 00:00:00.000Sam250.0000
Result set generated by:
SELECTA.[DateFull],
COALESCE(t.[SalesmanName], '') AS [SalesmanName],
COALESCE(t.[TotalSales], 0.00) AS [TotalSales]
FROM[Tally_Date] A
OUTER APPLY ( SELECTISNULL(SUM(A.[OrderLineValue]), 0.00) AS [TotalSales],
ISNULL(C.[Name], '') AS [SalesmanName]
FROM[Order_Line] A
LEFT OUTER JOIN [Order] B ON A.[OrderID] = B.[OrderID]
LEFT OUTER JOIN [SalesMan] C ON B.[SalesmanID] = C.[SalesmanID]
WHEREB.[OrderDate] <= [DateFull]
GROUP BYISNULL(C.[Name], '')
) AS t
WHEREA.[DateFull] BETWEEN '2013-07-01' AND '2013-07-31'
ORDER BY [DateFull] ASC,
[SalesmanName]
July 30, 2013 at 6:04 am
I use an inline tally table calendar generator, otherwise everything is the same as your environment:
;WITH Agg AS (
SELECT
s.SalesmanID,
s.Name,
c.DateRange,
o.OrderTotal,
seq = ROW_NUMBER() OVER(PARTITION BY s.SalesmanID ORDER BY c.DateRange)
FROM [dbo].[Salesman] s
CROSS JOIN dbo.IF_Calendar ('20130701','20130731','monday') c -- '2013-07-01' AND '2013-07-31'
LEFT JOIN (
SELECT
o.SalesmanID,
o.OrderDate,
[OrderTotal] = SUM(ol.OrderLineValue)
FROM [dbo].[Order] o
INNER JOIN [dbo].[Order_line] ol
ON ol.OrderID = o.OrderID
GROUP BY o.SalesmanID, o.OrderDate
) o
ON o.SalesmanID = s.SalesmanID AND o.OrderDate = c.DateRange
),
Calculator AS (
SELECT *,
RunningTotal = ISNULL(OrderTotal,0)
FROM Agg
WHERE seq = 1
UNION ALL
SELECT tr.*,
RunningTotal = lr.RunningTotal + ISNULL(tr.OrderTotal,0)
FROM Calculator lr
INNER JOIN Agg tr ON tr.SalesmanID = lr.SalesmanID AND tr.seq = lr.seq+1)
SELECT *
FROM Calculator
ORDER BY DateRange, SalesmanID
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 30, 2013 at 7:33 am
Many thanks for the response ChrisM,
What does your dbo.IF_Calendar function look like? I've tried replacing it with a join to my calendar table but it generates an error when running if I span more than a couple of months...
"The statement terminated. The maximum recursion 100 has been exhausted before statement completion." - any ideas?
Thanks
Charlotte
July 30, 2013 at 7:38 am
Your calendar function contains a recursive CTE. You can use option(MAXRECURSION 0) or something to get around your error message. Here's the definition of my calendar iTVF:
ALTER FUNCTION [dbo].[IF_Calendar]
(
@StartDate DATE,
@EndDate DATE,
@FirstWeekDay VARCHAR(10)
)
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
(
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a CROSS JOIN E1 b), --10E+2 or 100 rows
E3(N) AS (SELECT 1 FROM E2 a CROSS JOIN E2 b CROSS JOIN E1 c), --1M rows max
iTally AS ( -- generate sufficient rows to cover startdate to enddate inclusive
SELECT TOP(1+DATEDIFF(DAY,@StartDate,@EndDate))
rn = ROW_NUMBER() OVER(ORDER BY (SELECT NULL))-1
FROM E3
)
-- Do some date arithmetic
SELECT
a.DateRange,
c.[Year],
c.[Month],
c.[DayOfMonth],
c.AbsWeekno,
c.[DayName],
d.Holiday
FROM iTally
CROSS APPLY (SELECT DateRange = DATEADD(day,rn,@StartDate)) a
CROSS APPLY (VALUES ('Tuesday',1),('Wednesday',2),('Thursday',3),('Friday',4),('Saturday',5),('Sunday',6),('Monday',7)
) b (FirstWeekDay, FirstWeekdayOffset)
CROSS APPLY (
SELECT
[Year] = YEAR(a.DateRange),
[Month] = MONTH(a.DateRange),
[DayOfMonth] = DAY(a.DateRange),
AbsWeekno= DATEDIFF(day,FirstWeekdayOffset,a.DateRange)/7,
[DayName]= DATENAME(weekday,a.DateRange)
) c
CROSS APPLY (
SELECT Holiday = CASE
WHEN [Month] = 1 AND [DayOfMonth] = 1 THEN 'New Year'
WHEN [Month] = 5 AND [DayOfMonth] >= 25 AND [DayName] = 'Monday' THEN 'Memorial Day'
WHEN [Month] = 7 AND [DayOfMonth] = 4 THEN 'Independence Day'
WHEN [Month] = 9 AND [DayOfMonth] <= 7 AND [DayName] = 'Monday' THEN 'Labor Day'
WHEN [Month] = 11 AND [DayOfMonth] BETWEEN 22 AND 28 AND [DayName] = 'Thursday' THEN 'Thanksgiving Day'
WHEN [Month] = 12 AND [DayOfMonth] = 25 THEN 'Christmas Day'
ELSE NULL END
) d
WHERE b.FirstWeekDay = @FirstWeekDay
AND @EndDate IS NOT NULL
)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 30, 2013 at 8:18 am
Thanks ChrisM, that's great - it fixed my issue. Many thanks for your help.
Final question, any idea how to modify this to group by the week instead of the individual day so that it would return a year and week number instead of an individual date?
July 30, 2013 at 8:23 am
Charlottecb (7/30/2013)
Thanks ChrisM, that's great - it fixed my issue. Many thanks for your help.Final question, any idea how to modify this to group by the week instead of the individual day so that it would return a year and week number instead of an individual date?
Like this?
SELECT
s.SalesmanID,
s.Name,
c.Year, c.Month,
OrderTotal = SUM(o.OrderTotal),
seq = ROW_NUMBER() OVER(PARTITION BY s.SalesmanID ORDER BY c.Year, c.Month)
FROM [dbo].[Salesman] s
CROSS JOIN dbo.IF_Calendar ('20130701','20130731','monday') c -- '2013-07-01' AND '2013-07-31'
LEFT JOIN (
SELECT
o.SalesmanID,
o.OrderDate,
[OrderTotal] = SUM(ol.OrderLineValue)
FROM [dbo].[Order] o
INNER JOIN [dbo].[Order_line] ol
ON ol.OrderID = o.OrderID
GROUP BY o.SalesmanID, o.OrderDate
) o
ON o.SalesmanID = s.SalesmanID AND o.OrderDate = c.DateRange
GROUP BY s.SalesmanID,
s.Name,
c.Year, c.Month
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 30, 2013 at 9:54 am
Hi Chris,
Firstly, thanks again for taking the time to help me...
The final script you wrote doesn't quite give me what I need - If I add an order for a salesman in June, it highlights the issue...
it accumulates up for each individual month on a month by month basis but I need to accumulate up month on month so that Feb's total includes Jan's total, March would include Jan and Feb... Any suggestions?
Many thanks for persevering with me 🙂
July 31, 2013 at 2:08 am
Charlottecb (7/30/2013)
Hi Chris,Firstly, thanks again for taking the time to help me...
The final script you wrote doesn't quite give me what I need - If I add an order for a salesman in June, it highlights the issue...
it accumulates up for each individual month on a month by month basis but I need to accumulate up month on month so that Feb's total includes Jan's total, March would include Jan and Feb... Any suggestions?
Many thanks for persevering with me 🙂
This should work:
WITH PreppedDataSet AS (
SELECT
s.SalesmanID,
s.Name,
c.Year,
c.Month,
OrderTotal = SUM(o.OrderTotal),
seq = ROW_NUMBER() OVER(PARTITION BY s.SalesmanID ORDER BY c.Year, c.Month)
FROM [dbo].[Salesman] s
CROSS JOIN dbo.IF_Calendar ('20130101','20140101','monday') c -- '2013-07-01' AND '2013-07-31'
LEFT JOIN (
SELECT
o.SalesmanID,
o.OrderDate,
[OrderTotal] = SUM(ol.OrderLineValue)
FROM [dbo].[Order] o
INNER JOIN [dbo].[Order_line] ol
ON ol.OrderID = o.OrderID
GROUP BY o.SalesmanID, o.OrderDate
) o
ON o.SalesmanID = s.SalesmanID AND o.OrderDate = c.DateRange
GROUP BY s.SalesmanID,
s.Name,
c.Year, c.Month
),
Calculator AS (
SELECT
SalesmanID, Name, Year, Month, OrderTotal, seq,
RunningTotal = ISNULL(OrderTotal,0)
FROM PreppedDataSet
WHERE seq = 1
UNION ALL
SELECT
tr.SalesmanID, tr.Name, tr.Year, tr.Month, tr.OrderTotal, tr.seq,
RunningTotal = lr.RunningTotal + ISNULL(tr.OrderTotal,0)
FROM Calculator lr
INNER JOIN PreppedDataSet tr
ON tr.SalesmanID = lr.SalesmanID
AND tr.seq = lr.seq + 1
)
SELECT *
FROM Calculator c
ORDER BY c.Year, c.Month, c.SalesmanID
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 31, 2013 at 2:13 am
Chris, You're a star! That worked perfectly and has helped me out of a jam. Many thanks for all your help - much appreciated.:-D
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply