CODEWITHSUNDEEP
sqlsql-server
CodeMonkey
CodeMonkey

Reputation: 477

Get Next Record for same group that is in different sub group

I have invoices for an employee within a store within a region. I'm trying to get the next invoice date for the same employee but at a different store in the same region. Also a second query in a different store and region.

I am trying to figure out a way to do a LEAD(invoice_date,1) OVER (PARTITION BY employee_id ORDER BY invoice_date) WHERE employee = employee but market <> market. Then a second time WHERE employee = employee and market = market but store <> store. With this requirement, I can use LEAD with partition for the same employee, store, and region, but am not sure how to conditionally partition by employee but only for region <> region and then a second time for store <> store. How can I condition the LEAD like this?

DROP TABLE [tmp].[store]
CREATE TABLE [tmp].[store](
            [salonpk] int NOT null IDENTITY(1,1), 
            [storename] varchar(20) NOT NULL,
            [market] varchar(20) NOT NULL,
            [store_id] int null         
) ON [PRIMARY]
GO


DROP TABLE [tmp].[invoices]
CREATE TABLE [tmp].[invoices](
            [invoicepk] int NOT null IDENTITY(1,1), 
            [employee_id] int null, 
            [store_id] int null, 
            [inm_invoicedate] datetime null
) ON [PRIMARY]
GO
DROP TABLE [tmp].[employee]
CREATE TABLE [tmp].[employee](
            [employeepk] int NOT null IDENTITY(1,1), 
            [employee_id] int null, 
            [store_id] int null,
            [role] varchar(10) NOT NULL,
            [active] bit NOT NULL,
            [termdate] datetime NULL,
            [fname] VARCHAR(20) null,
            [lname] VARCHAR(20) NULL
) ON [PRIMARY]
GO

DROP TABLE [tmp].[employeemaster]
CREATE TABLE [tmp].[employeemaster](
            [employeemasterpk] int NOT null IDENTITY(1,1), 
            [masteremployee_id] int not null,
            [employee_id] int null, 
            [store_id] int null, 
) ON [PRIMARY]
GO



INSERT INTO [tmp].[employee]
    SELECT 1,1,'manager',1,NULL,'steve','johnson' UNION ALL
    SELECT 2,2,'stylist',1,'1/1/1899','steve','johnson' UNION ALL
    SELECT 3,3,'manager',0,'8/1/2017','steve','johnson' UNION ALL
    SELECT 4,1,'stylist',1,NULL,'adam','thomas' UNION ALL
    SELECT 5,2,'stylist',0,'6/1/2017','adam','thomas' UNION ALL
    SELECT 6,1,'gm',1,NULL,'susan','smith' UNION ALL
    SELECT 7,3,'gm',1,NULL,'susan','smith' UNION ALL
    SELECT 8,1,'stylist',1,NULL,'craig','allen' UNION ALL
    SELECT 9,2,'stylist',0,'5/1/2018','craig','allen' UNION ALL
    SELECT 10,1,'stylist',0,'2/1/2018','nelson','brian' UNION ALL
    SELECT 11,4,'stylist',1,NULL,'nelson','brian' UNION ALL
    SELECT 12,1,'stylist',0,'2/1/2018','alex','canner' UNION ALL
    SELECT 13,4,'stylist',1,NULL,'alex','canner' UNION ALL
    SELECT 14,3,'stylist',1,NULL,'alex','canner'

    INSERT INTO [tmp].[employeemaster]
    SELECT 1,1,1 UNION ALL
    SELECT 1,2,2 UNION ALL
    SELECT 1,3,3 UNION ALL
    SELECT 2,4,1 UNION ALL
    SELECT 2,5,2 UNION ALL
    SELECT 3,6,1 UNION ALL
    SELECT 3,7,3 UNION ALL
    SELECT 4,8,1 UNION ALL
    SELECT 4,9,2 UNION ALL
    SELECT 5,10,1 UNION ALL
    SELECT 5,11,4 UNION ALL
    SELECT 6,12,1 UNION ALL
    SELECT 6,13,4 UNION ALL
    SELECT 6,14,3

    INSERT INTO [tmp].[store]
    SELECT 'Down Town Store', 'South', 1 UNION ALL
    SELECT 'South City Store','South', 4 UNION ALL
    SELECT 'East Side Store','East', 2 UNION ALL
    SELECT 'East Way Store','East', 3


    INSERT INTO [tmp].[invoices]
    SELECT 1,1,'2017/2/16' UNION ALL
    SELECT 1,1,'2018/1/1' UNION ALL
    SELECT 1,1,'2018/1/5' UNION ALL
    SELECT 1,1,'2018/2/1' UNION ALL 
    SELECT 1,1,'2018/2/10' UNION ALL
    SELECT 1,1,'2018/2/11' UNION ALL
    SELECT 1,1,'2018/2/12' UNION ALL
    SELECT 2,2,'2018/2/15' UNION ALL    
    SELECT 2,2,'2018/2/16' UNION ALL
    SELECT 2,2,'2018/2/17' UNION ALL
    SELECT 2,2,'2018/2/18' UNION ALL
    SELECT 2,2,'2018/2/19' UNION ALL
    SELECT 1,1,'2018/3/15' UNION ALL
    SELECT 3,3,'2018/3/25' UNION ALL

    SELECT 4,1,'2018/1/5' UNION ALL
    SELECT 4,1,'2018/1/25' UNION ALL
    SELECT 5,2,'2018/1/27' UNION ALL
    SELECT 4,1,'2018/2/1' UNION ALL
    SELECT 4,1,'2018/2/15' UNION ALL
    SELECT 4,1,'2018/2/15' UNION ALL
    SELECT 4,1,'2018/3/20' UNION ALL
    SELECT 5,2,'2018/3/22' UNION ALL

    SELECT 6,1,'2018/1/11' UNION ALL
    SELECT 6,1,'2018/3/15' UNION ALL
    SELECT 6,1,'2018/4/21' UNION ALL
    SELECT 6,1,'2018/4/22' UNION ALL
    SELECT 7,3,'2018/5/15' UNION ALL
    SELECT 6,1,'2018/6/21' UNION ALL
    SELECT 6,1,'2018/7/01' UNION ALL

    SELECT 8,1,'2017/5/15' UNION ALL
    SELECT 9,2,'2018/5/16' UNION ALL
    SELECT 9,2,'2018/5/17' UNION ALL

    SELECT 10,1,'2018/6/1' UNION ALL
    SELECT 10,1,'2018/1/12' UNION ALL
    SELECT 10,1,'2018/8/24' UNION ALL
    SELECT 11,4,'2018/6/2'

Here's what I'm trying to do, but change the next invoice to be from a different store in the same market for the same employee.

;WITH CTE AS 
(
SELECT 
    eg.[masteremployee_id]
    , s.[market]
    , i.[invoicepk]
    , i.[inm_invoicedate]   
    ,LEAD(i.inm_invoicedate, 1,i.inm_invoicedate) OVER (PARTITION BY eg.masteremployee_id ORDER BY i.inm_invoicedate ASC) AS NextInvoice    
FROM [tmp].[invoices] AS i
INNER JOIN [tmp].[employeemaster] AS eg
    ON i.[employee_id] = eg.[employee_id]
    AND i.[store_id] = eg.[store_id]
INNER JOIN [tmp].[store] AS s
    ON i.[store_id] = s.[store_id]
GROUP BY 
    eg.[masteremployee_id]
    , s.[market]
    , i.[invoicepk]
    , i.[inm_invoicedate]   
)
SELECT 
    *
FROM CTE 
ORDER BY [masteremployee_id], [market], [inm_invoicedate]

Desired Results:

masteremployee_id   market  invoicepk   inm_invoicedate NextInvoiceInOtherMarket
1   South   1   2/16/2017   2/15/2018
1   South   2   1/1/2018    2/15/2018
1   South   3   1/5/2018    2/15/2018
1   South   4   2/1/2018    2/15/2018
1   South   5   2/10/2018   2/15/2018
1   South   6   2/11/2018   2/15/2018
1   South   7   2/12/2018   2/15/2018
1   South   13  3/15/2018   3/25/2018
1   East    8   2/15/2018   3/15/2018
1   East    9   2/16/2018   3/15/2018
1   East    10  2/17/2018   3/15/2018
1   East    11  2/18/2018   3/15/2018
1   East    12  2/19/2018   3/15/2018
1   East    14  3/25/2018   NULL

2   South   15  1/5/2018    1/27/2018
2   South   16  1/25/2018   1/27/2018
2   South   18  2/1/2018    3/22/2018
2   South   19  2/15/2018   3/22/2018
2   South   20  2/15/2018   3/22/2018
2   South   21  3/20/2018   3/22/2018
2   East    17  1/27/2018   2/1/2018
2   East    22  3/22/2018   NULL

3   South   23  1/11/2018   NULL
3   South   24  3/15/2018   NULL
3   South   25  4/21/2018   NULL
3   South   26  4/22/2018   NULL
3   South   28  6/21/2018   5/15/2018
3   South   29  7/1/2018    5/15/2018
3   East    27  5/15/2018   6/21/2018

4   South   30  5/15/2017   5/16/2018
4   East    31  5/16/2018   NULL
4   East    32  5/17/2018   NULL

5   South   34  1/12/2018   NULL
5   South   33  6/1/2018    NULL
5   South   36  6/2/2018    NULL
5   South   35  8/24/2018   NULL

Code I'm trying to replace with LEAD

;WITH CTE AS 
(
SELECT 
    eg.[masteremployee_id]
    , s.[market]
    , i.[invoicepk]
    , i.[inm_invoicedate]   
    , MIN(OTHERDOS.[inm_invoicedate]) AS NextInvoice
    , DATEDIFF(day, i.[inm_invoicedate], MIN(OTHERDOS.[inm_invoicedate])) AS DateDiff
FROM [tmp].[invoices] AS i
INNER JOIN [tmp].[employeeglobal] AS eg
    ON i.[employee_id] = eg.[employee_id]
    AND i.[store_id] = eg.[store_id]
INNER JOIN [tmp].[store] AS s
    ON i.[store_id] = s.[store_id]
INNER JOIN
    (
    SELECT 
        [inm_invoicedate]
        , eg.[masteremployee_id]
        , s.[market]
    FROM [tmp].[invoices] AS i
    INNER JOIN [tmp].[employeeglobal] AS eg
        ON i.[employee_id] = eg.[employee_id]
        AND i.[store_id] = eg.[store_id]
    INNER JOIN [tmp].[store] AS s
        ON i.[store_id] = s.[store_id]  
    ) AS OTHERDOS
    ON eg.[masteremployee_id] = OTHERDOS.[masteremployee_id]
WHERE s.[market] <> OTHERDOS.[market]
    AND i.[inm_invoicedate] <= OTHERDOS.[inm_invoicedate]   --should this go away?
GROUP BY 
    eg.[masteremployee_id]
    , s.[market]
    , i.[invoicepk]
    , i.[inm_invoicedate]   
)
SELECT 
    *
FROM CTE 
ORDER BY [masteremployee_id], [market] DESC, [inm_invoicedate]

Upvotes: 1

Views: 55

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Offhand, I can't think of a way to skip to the invoice in another store using window functions. You can use APPLY instead:

WITH CTE AS (
      SELECT eg.[masteremployee_id], s.[market], i.[invoicepk], i.[inm_invoicedate]   
      FROM [tmp].[invoices] i INNER JOIN
           [tmp].[employeemaster] eg
           ON i.[employee_id] = eg.[employee_id] AND
           i.[store_id] = eg.[store_id] INNER JOIN
           [tmp].[store] s
           ON i.[store_id] = s.[store_id]
      GROUP BY eg.[masteremployee_id], s.[market], i.[invoicepk], i.[inm_invoicedate]   
     )
SELECT cte.*, ctenext.inm_invoicedate
FROM CTE OUTER APPLY
     (SELECT TOP (1) ctenext.*
      FROM CTE ctenext
      WHERE ctenext.masteremployee_id = cte.masteremployee_id AND
            ctenext.store <> cte.store AND  -- not sure if this is the right column
            ctenext.inm_invoicedate > inm_invoicedate
      ORDER BY ctenext.inm_invoicedate DESC
     ) ctenext
ORDER BY [masteremployee_id], [market], [inm_invoicedate];

Upvotes: 1

Related Questions