Post reply

Service Call Log Question

  • texpic

    SSCertifiable

    Points: 5880

    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: 323426

    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: 5880

    September 20, 2018 at 2:25 pm

    #2006496

    Thank you, that works!

  • sgmunson

    SSC Guru

    Points: 110443

    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)
    ‌:) 🙂 🙂
    ‌Health & Nutrition

  • sgmunson

    SSC Guru

    Points: 110443

    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)
    ‌:) 🙂 🙂
    ‌Health & Nutrition

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

You must be logged in to reply to this topic. Login to reply