I've a scenario when a SELECT CASE statement is limiting the number of rows returned. Whilst I'm not a professional DB, I get by with some reasonable SQL and I didn't think that a SELECT CASE would actually limit the results?, just perform the THEN part of the CASE if the WHEN qualified.
To give some context, I've a transaction table which logs whenever an item is moved between a depot and the customer on some rental activity. Each transaction is registered as a row with a reference order and date of the transaction. To get both the customer receipt transaction and back to depot transaction on a single line, I've used (what might be a crude!) FROM SELECT. However, this part seems ok.
I'm on a SQL Server 2012.
Now, depending on what dates all of this happened, I've a taken the first date and last date involved and applied a SELECT CASE to define some conditions and resulting Date Difference calculations.
DECLARE @RUNDATE AS DECIMAL(8) = '20170101'
DECLARE @DAYFIRST AS DATETIME = DATEADD(m, DATEDIFF(m, 0, CONVERT(DATE, CAST(@RUNDATE AS VARCHAR(8)), 112)),0)
DECLARE @DAYEND AS DATETIME = DATEADD(s, -1, DATEADD(m, DATEDIFF(m, 0, CONVERT(DATE, CAST(@RUNDATE AS VARCHAR(8)), 112))+1, 0))
DECLARE @DAYSINPERIOD AS DECIMAL(2) = DAY(EOMONTH(CONVERT(DATE, CAST(@RUNDATE AS VARCHAR(8)), 112)))
SELECT MTITNO AS 'Item Number',
MTBANO AS 'Serial Number',
MTRORN AS 'Rental Order',
MTRORL AS 'Rental Order Line',
MTTRQT AS 'Delivered Quantity',
CONVERT(DATE, CAST(MTTRDT AS VARCHAR(8)), 112) AS 'Customer Receipt Date', --received in M3 to the customer, although this is aligned to when qty left the yard manually by logistics
CONVERT(DATE, CAST(NEXTDATE AS VARCHAR(8)), 112) AS 'Return To Depot Date', --last date in the return chain, receiving to the yard. If still in transit this equals the termination date
CASE
WHEN CONVERT(DATE, CAST(MTTRDT AS VARCHAR(8)), 112) < @DAYFIRST AND NEXTDATE IS NULL --is started before current period and no return activity or termination
THEN @DAYSINPERIOD --simply count the days in the current month as utilised
ELSE 0
END AS 'On Rent Entire Period',
CASE
WHEN CONVERT(DATE, CAST(MTTRDT AS VARCHAR(8)), 112) < @DAYFIRST AND NEXTDATE >= @DAYFIRST AND NEXTDATE <= @DAYEND --is started before current period but has a return activity/termination date
THEN DATEDIFF("DD",@DAYFIRST,CONVERT(DATE, CAST(NEXTDATE AS VARCHAR(8)), 112))+1 --difference between start of month and the nextdate (+1 for date difference include all)
ELSE 0
END AS 'Started Previous Period, Ended This Period',
CASE
WHEN CONVERT(DATE, CAST(MTTRDT AS VARCHAR(8)), 112) >= @DAYFIRST AND NEXTDATE IS NULL --is started in the current period and no return activity or termination
THEN DATEDIFF(D,CONVERT(DATE, CAST(MTTRDT AS VARCHAR(8)), 112),@DAYEND)+1 --difference between start date and last day of the current month (in line with utilisation) (+1 for date differnce include all)
ELSE 0
END AS 'Started This Period, No End Date',
CASE
WHEN CONVERT(DATE, CAST(MTTRDT AS VARCHAR(8)), 112) >= @DAYFIRST AND NEXTDATE >= @DAYFIRST AND NEXTDATE <= @DAYEND --is started in the current period and has a return activity/termination date
THEN DATEDIFF(D,CONVERT(DATE, CAST(MTTRDT AS VARCHAR(8)), 112),CONVERT(DATE, CAST(NEXTDATE AS VARCHAR(8)), 112))+1 --different between start date and the nextdate (+1 for date differnce include all)
ELSE 0
END AS 'Started and Ended This Period', --current period rental days (should match utilisation as sum of all lines)
@DAYSINPERIOD AS 'Calendar Days in Month', --how many days in the current month
@DAYFIRST AS 'First of Month', --what is the date of the first day of the current month
@DAYEND AS 'Last of Month' --what is the date of the last day of the current month
FROM
(
SELECT MTITNO,
MTBANO,
MTRORN,
MTRORL,
MTTRQT,
MTTRDT,
(
SELECT MIN(MTTRDT)
FROM MVXJDTA.MITTRA T2
WHERE T1.MTITNO = T2.MTITNO
AND T1.MTBANO = T2.MTBANO
AND T1.MTRORN = T2.MTRORN
AND T1.MTRORL = T2.MTRORL
AND T1.MTTRQT = T2.MTTRQT
AND T2.MTTRDT > T1.MTTRDT
) AS NEXTDATE
FROM MVXJDTA.MITTRA T1
WHERE MTCONO = 880
AND MTTTYP = '50'
AND MTTRQT > 0
AND MTCAMU <> ''
AND MTRORN LIKE 'A0%'
) T
This only returns the first 2 rows in what should be an entire data set. 2 rows
However, if I comment out the 4th SELECT CASE, then I get all records returned?! all rows
-- CASE
-- WHEN CONVERT(DATE, CAST(MTTRDT AS VARCHAR(8)), 112) >= @DAYFIRST AND NEXTDATE >= @DAYFIRST AND NEXTDATE <= @DAYEND --is started in the current period and has a return activity/termination date
-- THEN DATEDIFF(D,CONVERT(DATE, CAST(MTTRDT AS VARCHAR(8)), 112),CONVERT(DATE, CAST(NEXTDATE AS VARCHAR(8)), 112))+1 --different between start date and the nextdate (+1 for date differnce include all)
-- ELSE 0
-- END AS 'Started and Ended This Period', --current period rental days (should match utilisation as sum of all lines)
The strange thing is that the 3rd row should match the condition I've just commented out.
Can anyone help me better understand why a SELECT CASE would be a limit to the rows returned? And where my probable poor syntax might be the cause!!!