Service Call Log Question

• texpic

  SSCertifiable

  Points: 5885

  More actions

  September 20, 2018 at 1:20 pm

  #377515

  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

• Lowell

  SSC Guru

  Points: 323504

  More actions

  September 20, 2018 at 2:23 pm

  #2006495

  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!

• texpic

  SSCertifiable

  Points: 5885

  More actions

  September 20, 2018 at 2:25 pm

  #2006496

  Thank you, that works!

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  September 21, 2018 at 10:56 am

  #2006618

  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)

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  September 21, 2018 at 1:07 pm

  #2006632

  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)