# Service Call Log Question

• 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

• 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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Thank you, that works!

• Here you go...
`CREATE TABLE #TriggerDate (    MyAccount int,    MyDate date);INSERT INTO #TriggerDate (MyAccount, MyDate)SELECT 1, '2018-07-28' UNION ALLSELECT 1, '2018-08-02' UNION ALLSELECT 2, '2018-08-03' UNION ALLSELECT 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 ALLSELECT 1, '2018-07-26', 1 UNION ALLSELECT 1, '2018-07-27', 1 UNION ALLSELECT 1, '2018-07-28', 1 UNION ALLSELECT 1, '2018-07-29', 1 UNION ALLSELECT 1, '2018-07-30', 1 UNION ALLSELECT 1, '2018-07-31', 1 UNION ALLSELECT 1, '2018-08-01', 1 UNION ALLSELECT 1, '2018-08-02', 1 UNION ALLSELECT 1, '2018-08-03', 1 UNION ALLSELECT 1, '2018-08-04', 1 UNION ALLSELECT 1, '2018-08-05', 1 UNION ALLSELECT 1, '2018-08-06', 1 UNION ALLSELECT 2, '2018-07-25', 1 UNION ALLSELECT 2, '2018-07-26', 1 UNION ALLSELECT 2, '2018-07-27', 1 UNION ALLSELECT 2, '2018-07-28', 1 UNION ALLSELECT 2, '2018-07-29', 1 UNION ALLSELECT 2, '2018-07-30', 1 UNION ALLSELECT 2, '2018-07-31', 1 UNION ALLSELECT 2, '2018-08-01', 1 UNION ALLSELECT 2, '2018-08-02', 1 UNION ALLSELECT 2, '2018-08-03', 1 UNION ALLSELECT 2, '2018-08-04', 1 UNION ALLSELECT 2, '2018-08-05', 1 UNION ALLSELECT 2, '2018-08-06', 1 UNION ALLSELECT 3, '2018-07-25', 1 UNION ALLSELECT 3, '2018-07-26', 1 UNION ALLSELECT 3, '2018-07-27', 1 UNION ALLSELECT 3, '2018-07-28', 1 UNION ALLSELECT 3, '2018-07-29', 1 UNION ALLSELECT 3, '2018-07-30', 1 UNION ALLSELECT 3, '2018-07-31', 1 UNION ALLSELECT 3, '2018-08-01', 1 UNION ALLSELECT 3, '2018-08-02', 1 UNION ALLSELECT 3, '2018-08-03', 1 UNION ALLSELECT 3, '2018-08-04', 1 UNION ALLSELECT 3, '2018-08-05', 1 UNION ALLSELECT 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 Sales30DaysAfterFROM #TriggerDate AS TDCROSS 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 DRINNER 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.MyDateORDER BY    TD.MyAccount,    TD.MyDate;`

`DROP TABLE #TriggerDate;DROP TABLE #History;`

Here's the results:

Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Make Guaranteed Income

• 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 ALLSELECT 1, '2018-08-02' UNION ALLSELECT 2, '2018-08-03' UNION ALLSELECT 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 ALLSELECT 1, '2018-07-26', 1 UNION ALLSELECT 1, '2018-07-27', 1 UNION ALLSELECT 1, '2018-07-28', 1 UNION ALLSELECT 1, '2018-07-29', 1 UNION ALLSELECT 1, '2018-07-30', 1 UNION ALLSELECT 1, '2018-07-31', 1 UNION ALLSELECT 1, '2018-08-01', 1 UNION ALLSELECT 1, '2018-08-02', 1 UNION ALLSELECT 1, '2018-08-03', 1 UNION ALLSELECT 1, '2018-08-04', 1 UNION ALLSELECT 1, '2018-08-05', 1 UNION ALLSELECT 1, '2018-08-06', 1 UNION ALLSELECT 2, '2018-07-25', 1 UNION ALLSELECT 2, '2018-07-26', 1 UNION ALLSELECT 2, '2018-07-27', 1 UNION ALLSELECT 2, '2018-07-28', 1 UNION ALLSELECT 2, '2018-07-29', 1 UNION ALLSELECT 2, '2018-07-30', 1 UNION ALLSELECT 2, '2018-07-31', 1 UNION ALLSELECT 2, '2018-08-01', 1 UNION ALLSELECT 2, '2018-08-02', 1 UNION ALLSELECT 2, '2018-08-03', 1 UNION ALLSELECT 2, '2018-08-04', 1 UNION ALLSELECT 2, '2018-08-05', 1 UNION ALLSELECT 2, '2018-08-06', 1 UNION ALLSELECT 3, '2018-07-25', 1 UNION ALLSELECT 3, '2018-07-26', 1 UNION ALLSELECT 3, '2018-07-27', 1 UNION ALLSELECT 3, '2018-07-28', 1 UNION ALLSELECT 3, '2018-07-29', 1 UNION ALLSELECT 3, '2018-07-30', 1 UNION ALLSELECT 3, '2018-07-31', 1 UNION ALLSELECT 3, '2018-08-01', 1 UNION ALLSELECT 3, '2018-08-02', 1 UNION ALLSELECT 3, '2018-08-03', 1 UNION ALLSELECT 3, '2018-08-04', 1 UNION ALLSELECT 3, '2018-08-05', 1 UNION ALLSELECT 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 Sales30DaysAfterFROM #TriggerDate AS TDCROSS 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 DRINNER 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.MyDateORDER BY    TD.MyAccount,    TD.MyDate;`

`DROP TABLE #TriggerDate;DROP TABLE #History;`

Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Make Guaranteed Income

Viewing 5 posts - 1 through 5 (of 5 total)