join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

join

Author

Topic

ashleys
Starting Member

5 Posts

Posted - 2014-01-14 : 09:51:22

Hi Everyone,
Just want to output my result in a better way. Below is the query I am using where I am a doing a join and a union. I am compiling all purchases and issues for all items during a specific period.

SELECT Indetails.Category, Indetails.SerialNo, Indetails.Item, 'issue' as Code, isnull(OutDetails.Qty,0) as Qty, isnull(CONVERT(varchar,OutDetails.date,102),'NA') as Date, isnull(outdetails.sfno,'NA') as RefNo
FROM (SELECT Barcode, Item, Category FROM Item_Definition)
InDetails(SerialNo, Item, Category)
lEFT OUTER JOIN
(SELECT SerialNo, Qty, sfno, date, time,poffice from Store_Out_Details join Store_Out on Store_Out.SessionID=Store_Out_Details.SessionID where Store_Out.date between '2013-10-31' and '2014-01-01')
OutDetails(SerialNo, Qty, sfno, date, time, poffice)
ON Indetails.SerialNo=OutDetails.SerialNo
UNION
SELECT Indetails.Category, Indetails.SerialNo, Indetails.Item, 'purchase' as Code, isnull(OutDetails.Qty,0) as Qty, isnull(CONVERT(varchar,OutDetails.date,102),'NA') as Date, isnull(outdetails.PoNo,'NA') as RefNo
FROM (SELECT Barcode, Item, Category FROM Item_Definition)
InDetails(SerialNo, Item, Category)
lEFT OUTER JOIN
(SELECT SerialNo, Qty, store_po.PONo, store_po.Date, store_po.Time from Store_PO_Details join Store_PO on Store_PO.PONo=Store_PO_Details.PONo and Store_PO.Date = Store_PO_Details.date where Store_PO.date between '2013-10-31' and '2014-01-01')
OutDetails(SerialNo, Qty, PoNo, date, time)
ON Indetails.SerialNo=OutDetails.SerialNo
order by Indetails.Category, Indetails.Item, date ASC

Below is a sample of my output.

Category SerialNo Item Code Qty Date RefNo
Binding B cover issue 100 2013.11.21 17656
Binding B cover purchase 0 N/A N/A

Thing is where there is no purchase, it is showing up a record where purchase is 0 and same goes for issues if there is no issue for that period. How can I modify my query so as to not show this additional record when there is either no purchase or issue?

Thanks to advise.

Ashley

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-01-14 : 13:02:45

An easy way might be to use the ROW_NUMBER function to rank the rows and select the first one. Something like:

SELECT
	*
FROM
	(
		-- This is the original query plus ROW_NUMBER
		SELECT 
			Indetails.Category, 
			Indetails.SerialNo, 
			Indetails.Item, 
			'issue' as Code, 
			isnull(OutDetails.Qty,0) as Qty, 
			isnull(CONVERT(varchar,OutDetails.date,102),'NA') as Date, 
			isnull(outdetails.sfno,'NA') as RefNo
			ROW_NUMBER() OVER 
				(
					PARTITION BY 	
						Indetails.Category, 
						Indetails.SerialNo, 
						Indetails.Item,
					ORDER BY
						isnull(OutDetails.Qty,0) DESC
				) AS RowNum
		FROM (SELECT Barcode, Item, Category FROM Item_Definition)
		InDetails(SerialNo, Item, Category)
		lEFT OUTER JOIN
		(SELECT SerialNo, Qty, sfno, date, time,poffice from Store_Out_Details join Store_Out on Store_Out.SessionID=Store_Out_Details.SessionID where Store_Out.date between '2013-10-31' and '2014-01-01')
		OutDetails(SerialNo, Qty, sfno, date, time, poffice)
		ON Indetails.SerialNo=OutDetails.SerialNo
		UNION
		SELECT Indetails.Category, Indetails.SerialNo, Indetails.Item, 'purchase' as Code, isnull(OutDetails.Qty,0) as Qty, isnull(CONVERT(varchar,OutDetails.date,102),'NA') as Date, isnull(outdetails.PoNo,'NA') as RefNo
		FROM (SELECT Barcode, Item, Category FROM Item_Definition)
		InDetails(SerialNo, Item, Category)
		lEFT OUTER JOIN
		(SELECT SerialNo, Qty, store_po.PONo, store_po.Date, store_po.Time from Store_PO_Details join Store_PO on Store_PO.PONo=Store_PO_Details.PONo and Store_PO.Date = Store_PO_Details.date where Store_PO.date between '2013-10-31' and '2014-01-01')
		OutDetails(SerialNo, Qty, PoNo, date, time)
		ON Indetails.SerialNo=OutDetails.SerialNo
		order by Indetails.Category, Indetails.Item, date ASC
	) AS T
WHERE
	RowNum = 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources