Post DDL statement and sample data insert statements to ensure get responses.
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
You can try something like this. NOTE: The lack of data or expected results means that this code is untested.WITH cteDateCalc AS (
SELECT
trackid
, confirmed
, CalcDate = DATEADD(DAY, -8 + CASE DATEDIFF(DAY, 0, GETDATE()) % 7
WHEN 0 THEN 1
WHEN 5 THEN 2
WHEN 6 THEN 1
ELSE 0
END, GETDATE())
FROM #Codes
WHERE confirmed IS NOT NULL
)
SELECT
o.CustomerID
, CONFIRMED_LAST_WK = SUM(CASE WHEN cte.confirmed >= cte.CalcDate THEN 1
ELSE 0
END)
FROM #orders AS o
INNER JOIN cteDateCalc AS cte
ON o.trackid = cte.trackid
GROUP BY o.CustomerID
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply