Expected Output Required

Question

I want output on DateFilter ,that if #ItemmasterFile O_QT is not null then Pick qty from #Itemmasterfile table

and also add P_QTY from #Probale base on O_date table else if O_QTY is null then Pick from Probale table qty

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',2,'2023-08-31')
insert into #ItemMasterFile values (113,'Oragne',Null,Null)


insert into #Probale values (0001,112,1,'2023-08-01')
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-08-02')
insert into #Probale values (0005,111,1,'2023-09-03')

Answer 1

Try this:

;WITH CTE AS
(
SELECT I.Codeitem,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
)
SELECT Codeitem,ISNULL(O_QTY,0) + SUM(P_QTY) AS QTY
FROM CTE
GROUP BY Codeitem,O_QTY 
ORDER BY Codeitem

Answer 2

Hi @Analyst_SQL

Try this:

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

;WITH CTE AS
(
 SELECT Codeitem,SUM(P_QTY) AS P_QTY
 FROM #Probale
 WHERE P_Date BETWEEN @DateStart AND @DateEnd
 GROUP BY Codeitem 
)
SELECT I_Name AS [Name],P_QTY + ISNULL(O_QTY,0) AS QTY
FROM CTE C LEFT JOIN #ItemMasterFile I ON I.Codeitem = C.Codeitem

Best regards,

Cosmog Hong

Answer 3

Hi @Analyst_SQL

How about this:

SELECT I.Codeitem,ISNULL(I.O_QTY,0) + SUM(P_QTY) AS QTY
FROM #ItemMasterFile I JOIN #Probale P ON I.Codeitem=P.Codeitem
WHERE (P_Date BETWEEN I.O_Date AND @DateEnd) OR I.O_QTY IS NULL
GROUP BY I.Codeitem,I.O_QTY 

Best regards,

Cosmog Hong

Answer 4

Hi @Analyst_SQL ,

I change the query of @LiHongMSFT-4306 a bit:

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

;WITH CTE AS
(
 SELECT P.Codeitem,
		SUM(P_QTY) AS P_QTY
 FROM	#Probale P
 LEFT JOIN #ItemMasterFile  I
 ON		P.Codeitem=I.Codeitem
 WHERE	P_Date BETWEEN @DateStart AND IIF(O_Date<@DateEnd,O_Date,@DateEnd)
 GROUP	BY P.Codeitem 
)
SELECT	I_Name,
		P_QTY + ISNULL(O_QTY,0) AS QTY
FROM CTE C 
	LEFT JOIN #ItemMasterFile I 
	ON I.Codeitem = C.Codeitem

This will result in:

Kind regards,

Wilko

---

Please do not forget to "Accept the answer” wherever the information provided helps you, this can be beneficial to other community members. If you have extra questions about this answer, please click "Comment".