Fixing CROSS APPLY function- one row to multiple

Question

I’ve been trying to find a way to split my rows into multiple rows. I came across a CROSS APPLY example and tried to recreate it. I was able to do this, however, I cannot get anything to filter from the WHERE clause. The code looks messy, so I am wondering if I need to just trying a nesting function or something completely different? No matter if I change the first where clause, the second where clause, or both WHERE clauses, the filtering does not occur (such as removing Terry Johnson because TYPE column says ‘LOT’). I am using Microsoft SQL Server Management Studio, and I have been looking everywhere for an answer or alternative to CROSS APPLY. I may not be searching the correct words for what I need. If you can help point me in the direction, that would be great. Thanks!

all three tables as a picture

My original data looks like this:

Div------- Job #------- Type---- Sales Agent--- Cancel Date--- Contract Approved Date
CAE----- Aaab-001--- SNGL--- Joe Smith-------- 02/01/2018------ 01/15/2018
CLT----- Eeef-203----- DUPL--- Jane Smiths---- 05/28/2018------- 05/02/2018
CAE---- Zzzz-101----- LOT----- Terry Johnson----- (blank)---------- 06/01/2018

Output looks like this:
Div------- Job #------- Type---- Sales Agent--- Cancel Date--- Contract Approved Date
CAE----- Aaab-001--- SNGL--- Joe Smith-------- Null---------------- 01/15/2018
CAE----- Aaab-001--- SNGL--- Joe Smith------ 02/01/2018------- Null
CLT----- Eeef-203----- DUPL--- Jane Smiths---- Null---------------- 05/02/2018
CLT----- Eeef-203----- DUPL--- Jane Smiths---- 05/28/2018------- Null
CAE---- Zzzz-101----- LOT----- Terry Johnson----- (blank)---------- 06/01/2018

Want data to look like this (by filtering out LOT):
Div------- Job #------- Type---- Sales Agent--- Cancel Date--- Contract Approved Date
CAE----- Aaab-001--- SNGL--- Joe Smith-------- Null---------------- 01/15/2018
CAE----- Aaab-001--- SNGL--- Joe Smith------ 02/01/2018------- Null
CLT----- Eeef-203----- DUPL--- Jane Smiths---- Null---------------- 05/02/2018
CLT----- Eeef-203----- DUPL--- Jane Smiths---- 05/28/2018------- Null

SELECT
[Ounit Code] AS "Div",
[Project Code] + '-' + [Lot] AS [Job #],
[Sales Agent],
cr.[Cancel Date],
cr.[Contract Approved Date]

FROM [dbo].[Sales Summ] AS ss

CROSS APPLY  (VALUES
    ([Cancel Date],NULL),
    (NULL,[Contract Approved Date]))

    CR ([Cancel Date], [Contract Approved Date])

WHERE ss.[Cancel Date] IS NOT NULL AND ss.[Contract Approved Date] IS NOT NULL
      AND TYPE <> ‘LOT’

UNION

SELECT  
[Ounit Code] AS "Div",
[Project Code] + '-' + [Lot] AS [Job #],
[Sales Agent],
ss.[Cancel Date],
ss.[Contract Approved Date]

FROM [dbo].[Sales Summ] AS ss
WHERE ss.[Cancel Date] IS NULL OR ss.[Contract Approved Date] IS NULL
      AND TYPE <> ‘LOT’

Based on that screenshot. That expected result is just the first query from the UNION. So what if you remove the union and the select under it? — LukStorms, Jul 24 '18 at 13:24

score 1 · Accepted Answer · answered Jul 24 '18 at 13:35

you can try this.

DECLARE @Table TABLE (Div VARCHAR(10), [Job #] VARCHAR(20),Type VARCHAR(20), [Sales Agent] VARCHAR(20), [Cancel Date] DATE, [Contract Approved Date] DATE)
INSERT INTO @Table VALUES
('CAE', 'Aaab-001', 'SNGL', 'Joe Smith', '02/01/2018','01/15/2018'),
('CLT', 'Eeef-203', 'DUPL', 'Jane Smiths', '05/28/2018','05/02/2018'),
('CAE', 'Zzzz-101', 'LOT', 'Terry Johnson', NULL, '06/01/2018')


select Div , [Job #] ,Type , [Sales Agent] , X.[Cancel Date] , X.[Contract Approved Date] 
FROM @Table 
 CROSS APPLY ( VALUES ([Cancel Date], NULL) , (NULL, [Contract Approved Date] )) X ([Cancel Date] , [Contract Approved Date] ) 
WHERE Type <> 'LOT'

Result:

Div        Job #                Type                 Sales Agent          Cancel Date Contract Approved Date
---------- -------------------- -------------------- -------------------- ----------- ----------------------
CAE        Aaab-001             SNGL                 Joe Smith            2018-02-01  NULL
CAE        Aaab-001             SNGL                 Joe Smith            NULL        2018-01-15
CLT        Eeef-203             DUPL                 Jane Smiths          2018-05-28  NULL
CLT        Eeef-203             DUPL                 Jane Smiths          NULL        2018-05-02

Thank you for the help! I am still learning and wasn't sure how the UNION was working with the CROSS APPLY and this is better. — KMR, Jul 24 '18 at 13:55

score 0 · Answer 2 · answered Jul 24 '18 at 13:22

UNION ALL should work :

SELECT t.*
FROM ( (SELECT Div, Job, Type, SalesAgent, NULL as CancelDate, ContractApprovedDate
        FROM [dbo].[Sales Summ]
       ) UNION ALL
       (SELECT Div, Job, Type, SalesAgent, CancelDate, NULL as ContractApprovedDate
        FROM [dbo].[Sales Summ]
       )
     ) t
WHERE t.type <> 'LOT';

Fixing CROSS APPLY function- one row to multiple

2 Answers2