September 20, 2018 at 1:20 pm
This is an oversimplified example of a project. There are in reality 10,000 or so trigger daterecords, about 500 accounts and the daily data spans over the year. This is analyzing a customer service log, wereceived the service call on #TriggerDate.MyDate. Need to calculate, for the account thatcalled in, the number of sales for the 30 days before and the 30 daysafter. To keep things simple I used 1sale count day; also the day of the callis not counted.
Thanks for any help!
CREATE
TABLE #TriggerDate
(MyAccount int, MyDate date)
INSERT INTO #TriggerDate
SELECT 1, '2018-07-28' UNION ALL
SELECT 1, '2018-08-02' UNION ALL
SELECT 2, '2018-08-03' UNION ALL
SELECT 3, '2018-07-31'
CREATE TABLE #History
(MyAccount int, DateSold date, CountSold int)
INSERT INTO #History
SELECT 1, '2018-07-25', 1 UNION ALL
SELECT 1, '2018-07-26', 1 UNION ALL
SELECT 1, '2018-07-27', 1 UNION ALL
SELECT 1, '2018-07-28', 1 UNION ALL
SELECT 1, '2018-07-29', 1 UNION ALL
SELECT 1, '2018-07-30', 1 UNION ALL
SELECT 1, '2018-07-31', 1 UNION ALL
SELECT 1, '2018-08-01', 1 UNION ALL
SELECT 1, '2018-08-02', 1 UNION ALL
SELECT 1, '2018-08-03', 1 UNION ALL
SELECT 1, '2018-08-04', 1 UNION ALL
SELECT 1, '2018-08-05', 1 UNION ALL
SELECT 1, '2018-08-06', 1 UNION ALL
SELECT 2, '2018-07-25', 1 UNION ALL
SELECT 2, '2018-07-26', 1 UNION ALL
SELECT 2, '2018-07-27', 1 UNION ALL
SELECT 2, '2018-07-28', 1 UNION ALL
SELECT 2, '2018-07-29', 1 UNION ALL
SELECT 2, '2018-07-30', 1 UNION ALL
SELECT 2, '2018-07-31', 1 UNION ALL
SELECT 2, '2018-08-01', 1 UNION ALL
SELECT 2, '2018-08-02', 1 UNION ALL
SELECT 2, '2018-08-03', 1 UNION ALL
SELECT 2, '2018-08-04', 1 UNION ALL
SELECT 2, '2018-08-05', 1 UNION ALL
SELECT 2, '2018-08-06', 1 UNION ALL
SELECT 3, '2018-07-25', 1 UNION ALL
SELECT 3, '2018-07-26', 1 UNION ALL
SELECT 3, '2018-07-27', 1 UNION ALL
SELECT 3, '2018-07-28', 1 UNION ALL
SELECT 3, '2018-07-29', 1 UNION ALL
SELECT 3, '2018-07-30', 1 UNION ALL
SELECT 3, '2018-07-31', 1 UNION ALL
SELECT 3, '2018-08-01', 1 UNION ALL
SELECT 3, '2018-08-02', 1 UNION ALL
SELECT 3, '2018-08-03', 1 UNION ALL
SELECT 3, '2018-08-04', 1 UNION ALL
SELECT 3, '2018-08-05', 1 UNION ALL
SELECT 3, '2018-08-06', 1
September 20, 2018 at 2:23 pm
I got it to work as expected by using a SUM(case..) format, with a group by.
My ending date might need to be changed, as i seem to be off by one unit for Days After.SELECT [T1].[MyAccount],
[T1].[MyDate],
SUM( CASE
WHEN [HistData].[DateSold] >= DATEADD(dd, -30, [T1].[MyDate])
AND [HistData].[DateSold] < [T1].[MyDate] THEN
[HistData].[CountSold]
ELSE
0
END
) AS [Sales30DaysBefore],
SUM( CASE
WHEN [HistData].[DateSold] >= [T1].[MyDate]
AND [HistData].[DateSold] < DATEADD(dd, 30, [T1].[MyDate]) THEN
[HistData].[CountSold]
ELSE
0
END
) AS [Sales30DaysAfter]
FROM [#TriggerDate] AS [T1]
INNER JOIN [#History] AS [HistData]
ON [HistData].[MyAccount] = [T1].[MyAccount]
GROUP BY [T1].[MyAccount],
[T1].[MyDate]
ORDER BY [T1].[MyAccount];
Lowell
September 20, 2018 at 2:25 pm
Thank you, that works!
September 21, 2018 at 10:56 am
Here you go...CREATE TABLE #TriggerDate (
MyAccount int,
MyDate date
);
INSERT INTO #TriggerDate (MyAccount, MyDate)
SELECT 1, '2018-07-28' UNION ALL
SELECT 1, '2018-08-02' UNION ALL
SELECT 2, '2018-08-03' UNION ALL
SELECT 3, '2018-07-31';
CREATE TABLE #History (
MyAccount int,
DateSold date,
CountSold int
);
INSERT INTO #History (MyAccount, DateSold, CountSold)
SELECT 1, '2018-07-25', 1 UNION ALL
SELECT 1, '2018-07-26', 1 UNION ALL
SELECT 1, '2018-07-27', 1 UNION ALL
SELECT 1, '2018-07-28', 1 UNION ALL
SELECT 1, '2018-07-29', 1 UNION ALL
SELECT 1, '2018-07-30', 1 UNION ALL
SELECT 1, '2018-07-31', 1 UNION ALL
SELECT 1, '2018-08-01', 1 UNION ALL
SELECT 1, '2018-08-02', 1 UNION ALL
SELECT 1, '2018-08-03', 1 UNION ALL
SELECT 1, '2018-08-04', 1 UNION ALL
SELECT 1, '2018-08-05', 1 UNION ALL
SELECT 1, '2018-08-06', 1 UNION ALL
SELECT 2, '2018-07-25', 1 UNION ALL
SELECT 2, '2018-07-26', 1 UNION ALL
SELECT 2, '2018-07-27', 1 UNION ALL
SELECT 2, '2018-07-28', 1 UNION ALL
SELECT 2, '2018-07-29', 1 UNION ALL
SELECT 2, '2018-07-30', 1 UNION ALL
SELECT 2, '2018-07-31', 1 UNION ALL
SELECT 2, '2018-08-01', 1 UNION ALL
SELECT 2, '2018-08-02', 1 UNION ALL
SELECT 2, '2018-08-03', 1 UNION ALL
SELECT 2, '2018-08-04', 1 UNION ALL
SELECT 2, '2018-08-05', 1 UNION ALL
SELECT 2, '2018-08-06', 1 UNION ALL
SELECT 3, '2018-07-25', 1 UNION ALL
SELECT 3, '2018-07-26', 1 UNION ALL
SELECT 3, '2018-07-27', 1 UNION ALL
SELECT 3, '2018-07-28', 1 UNION ALL
SELECT 3, '2018-07-29', 1 UNION ALL
SELECT 3, '2018-07-30', 1 UNION ALL
SELECT 3, '2018-07-31', 1 UNION ALL
SELECT 3, '2018-08-01', 1 UNION ALL
SELECT 3, '2018-08-02', 1 UNION ALL
SELECT 3, '2018-08-03', 1 UNION ALL
SELECT 3, '2018-08-04', 1 UNION ALL
SELECT 3, '2018-08-05', 1 UNION ALL
SELECT 3, '2018-08-06', 1;
SELECT
TD.MyAccount,
TD.MyDate,
SUM(CASE WHEN H.DateSold BETWEEN DR.LowRangeStart AND DR.LowRangeEnd THEN H.CountSold END) AS Sales30DaysBefore,
SUM(CASE WHEN H.DateSold BETWEEN DR.HighRangeStart AND DR.HighRangeEnd THEN H.CountSold END) AS Sales30DaysAfter
FROM #TriggerDate AS TD
CROSS APPLY (
SELECT
DATEADD(day, -30, TD.MyDate) AS LowRangeStart,
DATEADD(day, -1, TD.MyDate) AS LowRangeEnd,
DATEADD(day, 1, TD.MyDate) AS HighRangeStart,
DATEADD(day, 30, TD.MyDate) AS HighRangeEnd
) AS DR
INNER JOIN #History AS H
ON TD.MyAccount = H.MyAccount
AND (
H.DateSold BETWEEN DR.LowRangeStart AND DR.LowRangeEnd
OR
H.DateSold BETWEEN DR.HighRangeStart AND DR.HighRangeEnd
)
GROUP BY
TD.MyAccount,
TD.MyDate
ORDER BY
TD.MyAccount,
TD.MyDate;
DROP TABLE #TriggerDate;
DROP TABLE #History;
Here's the results:
Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy)
September 21, 2018 at 1:07 pm
And this will be a much better performing scenario due to the clustered indexes:CREATE TABLE #TriggerDate (
MyAccount int,
MyDate date,
UNIQUE CLUSTERED
(
MyAccount ASC,
MyDate ASC
)
);
INSERT INTO #TriggerDate (MyAccount, MyDate)
SELECT 1, '2018-07-28' UNION ALL
SELECT 1, '2018-08-02' UNION ALL
SELECT 2, '2018-08-03' UNION ALL
SELECT 3, '2018-07-31';
CREATE TABLE #History (
MyAccount int,
DateSold date,
CountSold int,
UNIQUE CLUSTERED
(
MyAccount ASC,
DateSold ASC
)
);
INSERT INTO #History (MyAccount, DateSold, CountSold)
SELECT 1, '2018-07-25', 1 UNION ALL
SELECT 1, '2018-07-26', 1 UNION ALL
SELECT 1, '2018-07-27', 1 UNION ALL
SELECT 1, '2018-07-28', 1 UNION ALL
SELECT 1, '2018-07-29', 1 UNION ALL
SELECT 1, '2018-07-30', 1 UNION ALL
SELECT 1, '2018-07-31', 1 UNION ALL
SELECT 1, '2018-08-01', 1 UNION ALL
SELECT 1, '2018-08-02', 1 UNION ALL
SELECT 1, '2018-08-03', 1 UNION ALL
SELECT 1, '2018-08-04', 1 UNION ALL
SELECT 1, '2018-08-05', 1 UNION ALL
SELECT 1, '2018-08-06', 1 UNION ALL
SELECT 2, '2018-07-25', 1 UNION ALL
SELECT 2, '2018-07-26', 1 UNION ALL
SELECT 2, '2018-07-27', 1 UNION ALL
SELECT 2, '2018-07-28', 1 UNION ALL
SELECT 2, '2018-07-29', 1 UNION ALL
SELECT 2, '2018-07-30', 1 UNION ALL
SELECT 2, '2018-07-31', 1 UNION ALL
SELECT 2, '2018-08-01', 1 UNION ALL
SELECT 2, '2018-08-02', 1 UNION ALL
SELECT 2, '2018-08-03', 1 UNION ALL
SELECT 2, '2018-08-04', 1 UNION ALL
SELECT 2, '2018-08-05', 1 UNION ALL
SELECT 2, '2018-08-06', 1 UNION ALL
SELECT 3, '2018-07-25', 1 UNION ALL
SELECT 3, '2018-07-26', 1 UNION ALL
SELECT 3, '2018-07-27', 1 UNION ALL
SELECT 3, '2018-07-28', 1 UNION ALL
SELECT 3, '2018-07-29', 1 UNION ALL
SELECT 3, '2018-07-30', 1 UNION ALL
SELECT 3, '2018-07-31', 1 UNION ALL
SELECT 3, '2018-08-01', 1 UNION ALL
SELECT 3, '2018-08-02', 1 UNION ALL
SELECT 3, '2018-08-03', 1 UNION ALL
SELECT 3, '2018-08-04', 1 UNION ALL
SELECT 3, '2018-08-05', 1 UNION ALL
SELECT 3, '2018-08-06', 1;
SELECT
TD.MyAccount,
TD.MyDate,
SUM(CASE WHEN H.DateSold BETWEEN DR.LowRangeStart AND DR.LowRangeEnd THEN H.CountSold END) AS Sales30DaysBefore,
SUM(CASE WHEN H.DateSold BETWEEN DR.HighRangeStart AND DR.HighRangeEnd THEN H.CountSold END) AS Sales30DaysAfter
FROM #TriggerDate AS TD
CROSS APPLY (
SELECT
DATEADD(day, -30, TD.MyDate) AS LowRangeStart,
DATEADD(day, -1, TD.MyDate) AS LowRangeEnd,
DATEADD(day, 1, TD.MyDate) AS HighRangeStart,
DATEADD(day, 30, TD.MyDate) AS HighRangeEnd
) AS DR
INNER JOIN #History AS H
ON TD.MyAccount = H.MyAccount
AND (
H.DateSold BETWEEN DR.LowRangeStart AND DR.LowRangeEnd
OR
H.DateSold BETWEEN DR.HighRangeStart AND DR.HighRangeEnd
)
GROUP BY
TD.MyAccount,
TD.MyDate
ORDER BY
TD.MyAccount,
TD.MyDate;
DROP TABLE #TriggerDate;
DROP TABLE #History;
Steve (aka sgmunson)
Rent Servers for Income (picks and shovels strategy)
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy