CODEWITHSUNDEEP
sql-servert-sql
newdataguy
newdataguy

Reputation: 3

Referencing the current row outer apply column within separate outer join

Recently I've been tasked with creating a report that outputs sales information by Date of Business and Hour of the Day.

Here is the query I have currently written.

WITH CTE AS
(
    SELECT 0 AS Count
    UNION ALL
    SELECT Count + 1
    FROM CTE
    WHERE Count + 1 <= 23
),
ALLDATES AS
(
    SELECT CONVERT(datetime, @startDate) AS [DOB]
    UNION ALL
    SELECT DATEADD(DAY, 1, [DOB])
    FROM AllDates
    WHERE [DOB] < @endDate
)
SELECT D.DOB, A.Count AS [Hour], CONCAT(A.Count, ':00') AS [DisplayHour]
    , B.OrderModeName, COALESCE(B.Sales_Total, 0) AS [Sales]
    , COALESCE(B.Comps, 0) AS Comps, COALESCE(B.Promos, 0) AS Promos
FROM CTE AS A
OUTER APPLY (SELECT DOB FROM ALLDATES) D
LEFT OUTER JOIN (
    SELECT DATEPART(HH, ItemDetail.TransactionTime) AS [Hour]
        , OrderMode.OrderModeName, SUM(ItemDetail.GrossPrice) Sales_Total
        , SUM(CompAmount) AS Comps, SUM(PromoAmount) AS Promos
    FROM ItemDetail
    INNER JOIN OrderMode ON OrderMode.OrderModeID = ItemDetail.OrderModeID
    WHERE ItemDetail.DOB = D.DOB /*NEED HELP HERE*/ AND LocationID IN (
        SELECT LocationID
        FROM LocationGroupMember
        WHERE LocationGroupID = '@locationGroupID'
    )
    GROUP BY ItemDetail.DOB, DATEPART(HH, ItemDetail.TransactionTime), OrderMode.OrderModeName
) AS B
ON A.Count = B.Hour
ORDER BY D.DOB, A.Count

Where I am struggling is being able to reference the current row's DOB column that is coming from the OUTER APPLY.

I have tried WHERE ItemDetail.DOB = D.DOB, however I receive an error that the identifier can't be bound. Am I correct that in understanding that the outer applied data is not visible to the subquery within the join?

Here is an example of the output I'm expecting:

DOB      | Hour | Display Hour | OrderModeName | Sales  | Comps | Promos
1/8/2020 | 17   | 17:00        | Order         | 163.17 |  0    |  0 <-- Sales for Hour and Order Mode present

1/8/2020 | 23   | 23:00        |               | 0      |  0    |  0 <-- No sales at all for a given hour

Thanks in advance for any direction and advice!

Upvotes: 0

Views: 102

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89361

The basic pattern here is to CROSS JOIN to define the result "grain" and then LEFT JOIN the fact table to populate the rows for which data exists. EG

WITH CTE AS
(
SELECT 0 AS Count
UNION ALL
SELECT Count + 1
FROM CTE
WHERE Count + 1 <= 23
),
ALLDATES AS
(
    SELECT CONVERT(datetime, @startDate) AS [DOB]
    UNION ALL
    SELECT DATEADD(DAY, 1, [DOB])
    FROM AllDates
    WHERE [DOB] < @endDate
),
ALLHOURS as
(
  SELECT D.DOB, A.Count AS [Hour], CONCAT(A.Count, ':00') AS [DisplayHour]
  FROM CTE AS A
  CROSS JOIN ALLDATES D
),
ITEM_SUMMARY as
(
    SELECT DOB, DATEPART(HH, ItemDetail.TransactionTime) AS [Hour], OrderMode.OrderModeName, SUM(ItemDetail.GrossPrice) Sales_Total, SUM(CompAmount) AS Comps, SUM(PromoAmount) AS Promos
    FROM ItemDetail
    INNER JOIN OrderMode ON OrderMode.OrderModeID = ItemDetail.OrderModeID
     AND LocationID IN (SELECT LocationID FROM LocationGroupMember WHERE LocationGroupID = @locationGroupID)
    where DOB >= @startDate 
      and DOB < @endDate
    GROUP BY ItemDetail.DOB, DATEPART(HH, ItemDetail.TransactionTime), OrderMode.OrderModeName
)
select ALLHOURS.DOB, 
       ALLHOURS.Count AS [Hour], 
       CONCAT(ALLHOURS.Count, ':00') AS [DisplayHour], 
       ITEM_SUMMARY.OrderModeName, 
       COALESCE(ITEM_SUMMARY.Sales_Total, 0) AS [Sales], 
       COALESCE(ITEM_SUMMARY.Comps, 0) AS Comps, 
       COALESCE(ITEM_SUMMARY.Promos, 0) AS Promos
from ALLHOURS
LEFT OUTER JOIN ITEM_SUMMARY 
 on ITEM_SUMMARY.DOB = ALLHOURS.DOB 
 and ITEM_SUMMARY.Hour = ALLHOURS.Hour

Upvotes: 0

Related Questions