CODEWITHSUNDEEP
sqlsql-server
Jack Daniel
Jack Daniel

Reputation: 2611

Cumulative list operations on Rows in SQL

I have a scenario where I need to compare the list of a values for a particular day with the previous day for same person. And for every day I need to maintain a difference of those two consecutive days.

This is data of sales people, every day they visit few houses from the whole list of houses in their town.

Below is the data I have:

Houses:

 Select Distinct House_id from tbl_houses;

Visits:

Select sales_person_id, Date, visited_house_id;

Now, I have two different tables like this:

House_id    
1
2
3
4
5

And, the sales persons visit's data is as follows:

sales_person_id  Date visited_house
1              12/6/16   1
1              12/6/16   2
2              12/6/16   1
2              13/6/16   3
3              12/6/16   3
1              13/6/16   1
1              13/6/16   3

A sales person can visit a already visited house and more than one sales person can visit the same house on same day. No restrictions on this.

And, the desired output as follows:

Sales_person_Id  Date  Visited_Houses  Not_Visited_Houses
       1       12/6/16     [1,2]          [3,4,5]
       1       13/6/16     [1,2,3]        [4,5]
       2       12/6/16     [1]            [2,3,4,5]
       2       13/6/16     [1,3]          [2,4,5]
       3       12/6/16     [3]            [1,2,4,5]

How can we achieve this in sql?

Upvotes: 0

Views: 115

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

Please check below small piece of code which could help you :

SELECT DISTINCT
       H.sales_person_id,
       H.Date,
       Visited_Houses = STUFF(
                             (
                                 SELECT ','+CONVERT(NVARCHAR(MAX), houseid)
                                 FROM history
                                 WHERE [sales_person_id] = H.sales_person_id
                                       AND [Date] = H.Date FOR XML PATH('')
                             ), 1, 1, '[')+']',
       Not_Visited_Houses = STUFF(
                                 (
                                     SELECT ','+CONVERT(NVARCHAR(MAX), TT.House_id)
                                     FROM
                                     (
                                         SELECT HS1.House_id,
                                                H1.sales_person_id
                                         FROM house HS1
                                              LEFT JOIN #history H1 ON H1.houseid = HS1.House_id
                                                                       AND H1.sales_person_id = H.sales_person_id AND H1.Date = H.Date 
                                     ) TT
                                     WHERE TT.sales_person_id IS NULL FOR XML PATH('')
                                 ), 1, 1, '[')+']'
FROM house HS
     INNER JOIN #history H ON H.houseid = HS.House_id;

Desired Output :

sales_person_id Date       Visited_Houses   Not_Visited_Houses 
--------------- ---------- ---------------- -------------------
1               2016-06-12 [1,2]            [3,4,5]
1               2016-06-13 [1,3]            [2,4,5]
2               2016-06-12 [1]              [2,3,4,5]
2               2016-06-13 [3]              [1,2,4,5]
3               2016-06-12 [3]              [1,2,4,5]

Note : The above output is as per your Data that you have given.

Hope, it will help you.

Upvotes: 1

Chris Mack
Chris Mack

Reputation: 5208

For SQL Server (you can alter the CTE at the top to use whatever date range you like):

WITH cteDates
AS
(
    SELECT CAST(GETDATE() AS date) DT
    UNION
    SELECT CAST(GETDATE() - 1 AS date)
)

SELECT
    SP.sales_person_id
    , D.[DT] [Date]
    , '[' + LEFT(V.Visited, LEN(V.Visited) - 1) + ']' Visited
    , '[' + LEFT(NV.NotVisited, LEN(NV.NotVisited) - 1) + ']' Visited
FROM
    cteDates D
    CROSS JOIN (SELECT DISTINCT sales_person_id FROM tbl_visits) SP
    OUTER APPLY
    (
        SELECT
            CAST(
                (
                    SELECT CAST(visited_house_id AS varchar) + ','
                    FROM tbl_visits
                    WHERE
                        sales_person_id = SP.sales_person_id
                        AND [Date] = D.DT
                    ORDER BY visited_house_id
                    FOR XML PATH ('')
                )
            AS varchar) Visited
    ) V
    OUTER APPLY
    (
        SELECT
            CAST(
                (
                    SELECT CAST(House_id AS varchar) + ','
                    FROM tbl_houses
                    WHERE House_id NOT IN
                        (
                            SELECT visited_house_id
                            FROM tbl_visits
                            WHERE
                                sales_person_id = SP.sales_person_id
                                AND [Date] = D.DT
                        )
                    ORDER BY House_id
                    FOR XML PATH ('')
                )
            AS varchar) NotVisited
    ) NV
ORDER BY
    sales_person_id
    , [Date]

Upvotes: 1

Related Questions