Get Data before starting date

Question

as last solution , as last solution ,

https://learn.microsoft.com/en-us/answers/questions/1359033/expected-output-required

Condition

,if O_Value and O_Date is not null against item in itemmasterfile table, then qty from Probale table against relevant item before O_Date will be ignore and on word qty from O_Date will be consider .

Now i want to get data before starting date ,as below i tried but not expected result is coming

@LiHongMSFT-4306

Create table #ItemMasterFile (Codeitem int,I_Name varchar(50),O_QTY int,O_Date date)
Create table #Probale (Prdno int, Codeitem int,P_QTY int,P_Date date)

insert into #ItemMasterFile values (111,'Apple',Null,Null)
insert into #ItemMasterFile values (112,'Banana',5,'2023-08-31')
insert into #ItemMasterFile values (113,'Oragne',Null,Null)



insert into #Probale values (0002,112,1,'2023-08-25')
insert into #Probale values (0003,112,1,'2023-09-03')
insert into #Probale values (0004,112,1,'2023-09-04')
insert into #Probale values (0005,113,1,'2023-09-05')
insert into #Probale values (0005,111,1,'2023-09-03')



DECLARE @DateStart DATE,
@DateEnd DATE 
SET @DateStart = '2023-09-05'  
SET @DateEnd = '2023-09-06' 

;WITH CTE AS
(
SELECT I.Codeitem,P.P_QTY,
CASE WHEN O_DATE BETWEEN @DateStart AND @DateEnd THEN I.O_QTY ELSE NULL END AS O_QTY
FROM #Probale P 
JOIN #ItemMasterFile I ON I.Codeitem=P.Codeitem 
WHERE (P.P_Date >= I.O_Date) OR I.O_Date IS NULL and p.P_Date < @DateStart 
)
SELECT Codeitem,ISNULL(O_QTY,0) + SUM(P_QTY) AS QTY
FROM CTE

GROUP BY Codeitem,O_QTY 
ORDER BY Codeitem


drop table #ItemMasterFile
drop table #Probale

Expected Output

Answer 1

Hi @Analyst_SQL

Why the QTY of codeitem112 is 7 instead of 5?

You said this above: then qty from Probale table against relevant item before O_Date will be ignore

If I understand right, please check below query:

;WITH CTE AS(
SELECT I.Codeitem,P.P_Date
      ,CASE WHEN O_DATE < @DateStart THEN I.O_QTY ELSE NULL END AS O_QTY
	  ,CASE WHEN P_Date < @DateStart THEN P.P_QTY ELSE NULL END AS P_QTY
FROM #ItemMasterFile I LEFT JOIN #Probale P 
  ON I.Codeitem=P.Codeitem AND ((P.P_Date >= I.O_Date) OR I.O_Date IS NULL)
)
SELECT Codeitem,ISNULL(O_QTY,0) + SUM(ISNULL(P_QTY,0)) AS QTY
FROM CTE
GROUP BY Codeitem,O_QTY 
ORDER BY Codeitem

Answer 2

Hi @Analyst_SQL

Try this query:

DECLARE @DateStart DATE, @DateEnd DATE 
SET @DateStart = '2023-09-05'  
SET @DateEnd = '2023-09-06' 

;WITH CTE AS
(
SELECT I.Codeitem,P.P_Date
      ,CASE WHEN O_DATE < @DateStart THEN I.O_QTY ELSE NULL END AS O_QTY
	  ,CASE WHEN P_Date < @DateStart THEN P.P_QTY ELSE NULL END AS P_QTY
FROM #ItemMasterFile I JOIN #Probale P ON I.Codeitem=P.Codeitem
WHERE (P.P_Date >= I.O_Date) OR I.O_Date IS NULL
)
SELECT Codeitem,ISNULL(O_QTY,0) + SUM(ISNULL(P_QTY,0)) AS QTY
FROM CTE
GROUP BY Codeitem,O_QTY 
ORDER BY Codeitem

Best regards,

Cosmog Hong

Answer 3

Check a query adapted for new details:

select Codeitem,     
    case when O_QTY is not null and O_Date is not null then
        (select coalesce(sum(P_QTY), 0) from #Probale where Codeitem = m.Codeitem and P_Date >= m.O_Date and P_Date < @DateStart) +
            case when O_Date < @DateStart then O_QTY else 0 end     
    else
        (select coalesce(sum(P_QTY), 0) from #Probale where Codeitem = m.Codeitem and P_Date < @DateStart)     
    end as QTY from #ItemMasterFile m 
order by Codeitem

Answer 4

Check this query:

select Codeitem,
    case when O_QTY is not null and O_Date is not null then
        (select coalesce(sum(P_QTY), 0) from #Probale where Codeitem = m.Codeitem and P_Date >= m.O_Date and P_Date < @DateStart) + O_QTY
    else
        (select coalesce(sum(P_QTY), 0) from #Probale where Codeitem = m.Codeitem and P_Date < @DateStart)
    end as QTY
from #ItemMasterFile m
order by Codeitem