January 14, 2014 at 5:15 am
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.
CategorySerialNo Item Code Qty Date RefNo
BindingBIN-COV-CARBinding back cover-carton A4 issue 100 2013.11.2617656
BindingBIN-COV-CARBinding back cover-carton A4 purchase0 NA NA
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
January 14, 2014 at 7:49 am
ashley.shookhye (1/14/2014)
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.
CategorySerialNo Item Code Qty Date RefNo
BindingBIN-COV-CARBinding back cover-carton A4 issue 100 2013.11.2617656
BindingBIN-COV-CARBinding back cover-carton A4 purchase0 NA NA
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
Maybe this is as simple as changing your left joins to inner joins?
If that doesn't do it you are going to need to post a lot more information. Please take a few minutes and read the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 15, 2014 at 10:02 pm
Inne Join works perfectly fine. Thank you so much.
January 15, 2014 at 10:02 pm
Inner join works perfectly fine. Thank you so much.
January 15, 2014 at 10:39 pm
ashley.shookhye (1/14/2014)
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
Can you filter it with a WHERE clause?
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 16, 2014 at 2:11 am
ashley.shookhye (1/15/2014)
Inner join works perfectly fine. Thank you so much.
Improving the signal-to-noise ratio of a query aids readability for you and for others who might inherit the code later. Try this - make sure you put in the missing table aliases;
SELECT
i.Category,
SerialNo= i.Barcode
i.Item,
Code= 'issue',
Qty= isnull(Qty,0), -- table alias missing
[Date]= isnull(CONVERT(varchar,so.[date],102),'NA'),
RefNo= isnull(sfno,'NA') -- table alias missing
FROM Store_Out_Details sod
INNER join Store_Out so
on so.SessionID = sod.SessionID
INNER JOIN Item_Definition i
ON i.Barcode = SerialNo -- table alias missing
WHERE so.[date] between '2013-10-31' and '2014-01-01'
UNION ALL -- UNION without ALL performs an unnecessary DISTINCT across the output
SELECT
i.Category,
SerialNo= i.Barcode
i.Item,
Code= 'purchase',
Qty= isnull(Qty,0), -- table alias missing
[Date]= isnull(CONVERT(varchar,sp.[date],102),'NA'),
RefNo= isnull(sp.PoNo,'NA')
FROM Store_PO_Details spd
INNER join Store_PO sp
on sp.PONo = spd.PONo
and sp.[Date] = spd.[date]
INNER JOIN Item_Definition i
ON i.Barcode = SerialNo -- table alias missing
WHERE sp.[date] between '2013-10-31' and '2014-01-01'
ORDER BY i.Category, i.Item, [date] ASC
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply