April 8, 2013 at 7:37 pm
I'm trying to add a date range to this query, where the min date is the last 10 days, the max TheDate. The Min/Max are calculated for records that fall within thoe dates. I tried all kind of combinations and came up with nothing. Hope someone can solve this puzzle, Thanks!
CREATE TABLE #Sample
(TheDate datetime, Client varchar(10), Calls int, Answers int, Sales int)
INSERT INTO #Sample
SELECT '2013-04-03', 'A', 50, 20, 60 UNION ALL
SELECT '2013-04-04', 'A', 60, 30, 50 UNION ALL
SELECT '2013-04-05', 'A', 70, 40, 40 UNION ALL
SELECT '2013-04-06', 'A', 80, 50, 30 UNION ALL
SELECT '2013-04-07', 'A', 90, 60, 20 UNION ALL
SELECT '2013-04-08', 'A', 100, 70, 10 UNION ALL
SELECT '2013-04-03', 'B', 150, 120, 160 UNION ALL
SELECT '2013-04-04', 'B', 160, 130, 150 UNION ALL
SELECT '2013-04-05', 'B', 170, 140, 140 UNION ALL
SELECT '2013-04-06', 'B', 180, 150, 130 UNION ALL
SELECT '2013-04-07', 'B', 190, 160, 120 UNION ALL
SELECT '2013-04-08', 'B', 200, 170, 110
SELECT Client, TheDate,
COUNT(1) as Records, --number of days with records
MAX(Calls) as MaxCalls,
MIN(Calls) as MinCalls,
MAX(Answers) as MaxAnswers,
MIN(Answers) as MinAnswers,
MAX(Sales) as MaxSales,
MIN(Sales) as MinSales
FROM #Sample
WHERE TheDate BETWEEN DATEADD(D, -10, TheDate) AND TheDate
GROUP BY Client, TheDate
ORDER BY Client, TheDate
Result:
Client TheDate Records MaxCalls MinCalls MaxAnswe MinAnswe MaxSales MinSales
A 4/3/2013 1 50 50 20 20 60 60
A 4/4/2013 2 60 50 30 20 60 50
A 4/5/2013 3 70 50 40 20 60 40
A 4/6/2013 4 80 50 50 50 60 30
A 4/7/2013 5 90 50 60 60 60 20
A 4/8/2013 6 100 100 70 70 60 10
B 4/3/2013 1 150 150 120 120 160 160
B 4/4/2013 2 160 150 130 120 160 150
B 4/5/2013 3 170 150 140 120 160 140
B 4/6/2013 4 180 150 150 120 160 130
B 4/7/2013 5 190 150 160 120 160 120
B 4/8/2013 6 200 150 170 120 160 110
April 8, 2013 at 7:44 pm
I'm not sure I asked this right, every record has a 10-day date range based on the date for that record.
April 9, 2013 at 1:32 am
Try this rough and ready method. It's quick and simple to change. If you can make it work then there's a better algorithm described in this excellent article [/url]by Gabriel Priester.
DROP TABLE #Sample
CREATE TABLE #Sample
(TheDate datetime, Client varchar(10), Calls int, Answers int, Sales int)
SET DATEFORMAT YMD
INSERT INTO #Sample
SELECT '2013-04-03', 'A', 50, 20, 60 UNION ALL
SELECT '2013-04-04', 'A', 60, 30, 50 UNION ALL
SELECT '2013-04-05', 'A', 70, 40, 40 UNION ALL
SELECT '2013-04-06', 'A', 80, 50, 30 UNION ALL
SELECT '2013-04-07', 'A', 90, 60, 20 UNION ALL
SELECT '2013-04-08', 'A', 100, 70, 10 UNION ALL
SELECT '2013-04-09', 'A', 90, 80, 10 UNION ALL
SELECT '2013-04-10', 'A', 80, 90, 10 UNION ALL
SELECT '2013-04-11', 'A', 70, 100, 10 UNION ALL
SELECT '2013-04-12', 'A', 60, 110, 10 UNION ALL
SELECT '2013-04-13', 'A', 50, 120, 10 UNION ALL
SELECT '2013-04-14', 'A', 40, 130, 10 UNION ALL
SELECT '2013-04-15', 'A', 30, 140, 10 UNION ALL
SELECT '2013-04-03', 'B', 150, 120, 160 UNION ALL
SELECT '2013-04-04', 'B', 160, 130, 150 UNION ALL
SELECT '2013-04-05', 'B', 170, 140, 140 UNION ALL
SELECT '2013-04-06', 'B', 180, 150, 130 UNION ALL
SELECT '2013-04-07', 'B', 190, 160, 120 UNION ALL
SELECT '2013-04-08', 'B', 200, 170, 110
SELECT so.*, x.*
FROM #Sample so
OUTER APPLY (
SELECT
COUNT(1) as Records, --number of days with records
MAX(Calls) as MaxCalls,
MIN(Calls) as MinCalls,
MAX(Answers) as MaxAnswers,
MIN(Answers) as MinAnswers,
MAX(Sales) as MaxSales,
MIN(Sales) as MinSales
FROM #Sample si
WHERE si.Client = so.Client
AND si.TheDate BETWEEN DATEADD(day,-9,so.TheDate) AND so.TheDate
) x
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
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply