Trim down query

Hi,

I originally created this query to get a result from a single select statement
Its funny that it became a very long query having sets of joined table
Its also maybe annoying to look for it so im trying to trim it down

most of it have same joined tables only that it accepts different parameter or value
and some have some column condition (i,e.. either "1" value or "2" in a column)

I still post this hoping somebody could help me trim it down and not laugh at me :)

by the way, the problem of this long query obviously is it took lot of time to get the result

thanks in advance

Attachments

Stock_Inquiry.txt (9.94 KB)

ALTER PROCEDURE [dbo].[Inquiry]
(
@t nvarchar(15)
)
AS
Begin SET NOCOUNT ON 

Select Distinct([Product].Barcode), [Product].ItemCode, Tone.Tonality , Sized.Size, [AFS], [SOH], [TotalAdvance], [UndelPO], [Unserved], [TotalAFS], [TotalSOH] From Tone Left Join [Product] on Tone.Barcode = [Product].Barcode 
Left Join Sized on Sized.SizeID = Product.SizeID 
		

Left Join ( Select TxnDetails.TID, ( [po] + [adjadd] + [sorr] - [so] - [porr] - [break] ) as AFS From TxnHead Left Join TxnDetails on TxnDetails.TxnID = TxnHead.TxnID Left Join (Select TID, ISNULL(Sum(Quantity),0) as 'po' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID where Ttype= 1 and TxnHead.Deleted is Null Group by TID) poall on poall.TID = TxnDetails.TID Left Join (Select TID, ISNULL(Sum(Quantity),0) as 'so' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID where Ttype= 2 and TxnHead.Deleted is Null Group by TID) soall on soall.TID = TxnDetails.TID Left Join (Select TID, ISNULL(Sum(Quantity),0) as 'porr' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID where Ttype= 6 and Track = 1 and TxnHead.Deleted is Null Group by TID) powrr on powrr.TID = TxnDetails.TID Left Join (Select TID, ISNULL(Sum(Quantity),0) as 'sorr' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID where Ttype= 6 and Track = 2 and TxnHead.Deleted is Null Group by TID) sowrr on sowrr.TID = TxnDetails.TID Left Join (Select TID, ISNULL(Sum(Quantity),0) as 'adjadd' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID  where Ttype= 3 and TxnHead.Deleted is Null Group by TID) addj on addj.TID = TxnDetails.TID 
Left Join (Select TID, ISNULL(Sum(Quantity),0) as 'break' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID  where Ttype= 5 and TxnHead.Deleted is Null Group by TID) brek on brek.TID = TxnDetails.TID Where TxnHead.Deleted is Null) af on af.TID = Tone.TID



Left Join (Select TxnDetails.TID, ([RR] - [DR] - [powrr] - [break] + [sowrr] + [adjadd]) as SOH From TxnHead Left Join TxnDetails on TxnDetails.TxnID = TxnHead.TxnID Left Join Tone on Tone.TID = TxnDetails.TID Left Join (Select TDID, ISNULL(Sum(Quantity),0) as 'RR' From RDetails Group by TDID) porr on porr.TDID = TxnDetails.TDID Left Join (Select TDID, ISNULL(Sum(Quantity),0) as 'DR' From DDetails Group by TDID) sorr on sorr.TDID = TxnDetails.TDID Left Join (Select TID, ISNULL(Sum(Quantity),0) as 'sowrr' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID where Ttype= 6 and Track= 2 and Stat= 1 and TxnHead.Deleted is Null Group by TID) soret on soret.TID = TxnDetails.TID Left Join (Select TID, ISNULL(Sum(Quantity),0) as 'powrr' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID  where Ttype= 6 and Track= 1 and Stat= 1 and TxnHead.Deleted is Null Group by TID) poret on poret.TID = TxnDetails.TID Left Join (Select TID, ISNULL(Sum(Quantity),0) as 'adjadd' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID  where Ttype= 3 and TxnHead.Deleted is Null Group by TID) addj on addj.TID = TxnDetails.TID Left Join (Select TID, ISNULL(Sum(Quantity),0) as 'break' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID  where Ttype= 5 and TxnHead.Deleted is Null Group by TID) brek on brek.TID = TxnDetails.TID Where TxnHead.Deleted is Null) so on so.TID = Tone.TID 


Left Join (Select [Product].Barcode, ([po] + [adjadd] + [sorr] - [so] - [porr] - [break]) as TotalAFS From TxnHead Left Join TxnDetails on TxnDetails.TxnID = TxnHead.TxnID Left Join Tone on Tone.TID = TxnDetails.TID Left Join [Product] on Tone.Barcode = [Product].Barcode Left Join (Select Tone.Barcode, ISNULL(Sum(Quantity),0) as 'po' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID Left Join Tone on Tone.TID = TxnDetails.TID where Ttype= 1 and TxnHead.Deleted is Null Group by Barcode) poall on poall.Barcode = Tone.Barcode Left Join (Select Tone.Barcode, ISNULL(Sum(Quantity),0) as 'so' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID Left Join Tone on Tone.TID = TxnDetails.TID where Ttype= 2 and TxnHead.Deleted is Null Group by Barcode) soall on soall.Barcode = Tone.Barcode Left Join (Select Tone.Barcode, ISNULL(Sum(Quantity),0) as 'porr' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID Left Join Tone on Tone.TID = TxnDetails.TID where Ttype= 6 and Track = 1 and TxnHead.Deleted is Null Group by Barcode) powrr on powrr.Barcode = Tone.Barcode Left Join (Select Tone.Barcode, ISNULL(Sum(Quantity),0) as 'sorr' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID Left Join Tone on Tone.TID = TxnDetails.TID where Ttype= 6 and Track = 2 and TxnHead.Deleted is Null Group by Barcode) sowrr on sowrr.Barcode = Tone.Barcode  Left Join (Select Tone.Barcode, ISNULL(Sum(Quantity),0) as 'adjadd' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID Left Join Tone on Tone.TID = TxnDetails.TID where Ttype= 3 and TxnHead.Deleted is Null Group by Barcode) addj on addj.Barcode = Tone.Barcode Left Join (Select Tone.Barcode, ISNULL(Sum(Quantity),0) as 'break' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID Left Join Tone on Tone.TID = TxnDetails.TID where Ttype= 5 and TxnHead.Deleted is Null Group by Barcode) brek on brek.Barcode = Tone.Barcode Where TxnHead.Deleted is Null) toaf on toaf.Barcode = Tone.Barcode




Left Join (Select [Product].Barcode, ([RR] - [DR] - [powrr] - [break] + [sowrr] + [adjadd]) as TotalSOH
From TxnHead Left Join TxnDetails on TxnDetails.TxnID = TxnHead.TxnID Left Join Tone on Tone.TID = TxnDetails.TID  Left Join Product on Product.Barcode = Tone.Barcode Left Join (Select Tone.Barcode, ISNULL(Sum(RDetails.Quantity),0) as 'RR' From RDetails Left Join TxnDetails on TxnDetails.TDID = RDetails.TDID Left Join Tone on Tone.TID = TxnDetails.TID Group by Barcode) porr on porr.Barcode = Tone.Barcode Left Join (Select Tone.Barcode, ISNULL(Sum(DDetails.Quantity),0) as 'DR' From DDetails Left Join TxnDetails on TxnDetails.TDID = DDetails.TDID Left Join Tone on Tone.TID = TxnDetails.TID Group by Barcode) sorr on sorr.Barcode = Tone.Barcode Left Join (Select Tone.Barcode, ISNULL(Sum(Quantity),0) as 'sowrr' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID Left Join Tone on Tone.TID = TxnDetails.TID where Ttype= 6 and Track= 2 and Stat= 1 and TxnHead.Deleted is Null Group by Barcode) soret on soret.Barcode = Tone.Barcode Left Join (Select Tone.Barcode, ISNULL(Sum(Quantity),0) as 'powrr' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID  Left Join Tone on Tone.TID = TxnDetails.TID where Ttype= 6 and Track= 1 and Stat= 1 and TxnHead.Deleted is Null Group by Barcode) poret on poret.Barcode = Tone.Barcode Left Join (Select Tone.Barcode, ISNULL(Sum(Quantity),0) as 'adjadd' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID  Left Join Tone on Tone.TID = TxnDetails.TID where Ttype= 3 and TxnHead.Deleted is Null Group by Barcode) addj on addj.Barcode = Tone.Barcode Left Join (Select Tone.Barcode, ISNULL(Sum(Quantity),0) as 'break' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID  Left Join Tone on Tone.TID = TxnDetails.TID where Ttype= 5 and TxnHead.Deleted is Null Group by Barcode) brek on brek.Barcode = Tone.Barcode Where TxnHead.Deleted is Null) toso on toso.Barcode = Tone.Barcode 

		


Left Join (Select TxnDetails.TID, ([TotalOrder] + [Supp]  - [Total] - [RetNoRR]) as UndelPO From TxnHead Left Join TxnDetails on TxnDetails.TxnID = TxnHead.TxnID Left Join Tone on Tone.TID = TxnDetails.TID Left Join (Select TxnDetails.TID as id, Sum(Quantity) as 'TotalOrder' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID where Track = 1 and Ttype = 1 and TxnHead.Deleted is NULL Group by TID) pod on pod.id = Tone.TID Left Join (Select TDID as tx, ISNULL(Sum(Quantity),0) as 'Total' From RDetails Group by TDID) po on po.tx = TxnDetails.TDID Left Join (Select TxnDetails.TID as id, ISNULL(Sum(Quantity),0) as 'Supp' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID where Track = 2 and Ttype = 1 Group by TID) sup on sup.id = Tone.TID Left Join (Select TxnDetails.TID, Sum(Quantity) as 'RetNoRR' From TxnDetails Left Join TxnHead on TxnHead.TxnID = TxnDetails.TxnID where Ttype = 6 and Track= 1 and Stat = 2 and TxnHead.Deleted is Null Group by TxnDetails.TID) norr on norr.TID = Tone.TID  where TxnHead.Deleted is null) undel on undel.TID = Tone.TID 



Left Join (Select TxnDetails.TID, ([TotalOrder] + [Adv] - [Total] - [RetNoRR]) as Unserved From TxnHead Left Join TxnDetails on TxnDetails.TxnID = TxnHead.TxnID Left Join Tone on Tone.TID = TxnDetails.TID Left Join (Select TxnDetails.TID as id, Sum(Quantity) as 'TotalOrder' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID where Track = 1 and Ttype = 2 and TxnHead.Deleted is NULL Group by TID) pod on pod.id = Tone.TID 
Left Join (Select TDID as tx, ISNULL(Sum(Quantity),0) as 'Total' From DDetails Group by TDID) so on so.tx = TxnDetails.TDID Left Join (Select TxnDetails.TID as id, ISNULL(Sum(Quantity),0) as 'Adv' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID where Track = 2 and Ttype = 2 Group by TID) sup on sup.id = Tone.TID Left Join (Select TxnDetails.TID, ISNULL(Sum(Quantity),0) as 'RetNoRR' From TxnDetails Left Join TxnHead on TxnHead.TxnID = TxnDetails.TxnID where Ttype = 6 and Track= 2 and Stat = 2 and TxnHead.Deleted is Null Group by TxnDetails.TID) norr on norr.TID = Tone.TID where TxnHead.Deleted is null) unserv on unserv.TID = Tone.TID 

Left Join (Select TxnDetails.TID, [TotalAdvance]  From TxnHead Left Join TxnDetails on TxnDetails.TxnID = TxnHead.TxnID Left Join Tone on Tone.TID = TxnDetails.TID Left Join (Select TxnDetails.TID as id, ISNULL(Sum(Quantity),0) as 'TotalAdvance' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID where Track = 2 and Ttype = 2 and TxnHead.Deleted is null Group by TID) pod on pod.id = Tone.TID  where TxnHead.Deleted is null) adv on adv.TID = Tone.TID 
        
where Tone.TID = @t  
order by 2

Update:

I tried chopping each column result to different stored proc but each still took seconds in executing, especially that I have thousands of records.

My eyes, it burns! Sorry, just had to say that before anyone else can. It's also a bit much to read, and I'm wondering what the goal of the query/procedure is - maybe it can be improved?

You'll have to post the table structures along with a description of what you are trying to do with the query.

yeah I know, even me I'm a bit annoyed with the query.. Like I said, I tried to create a query to get a result, and originally that's what I produced.

I was thinking not to post it anymore cause I know the feeling.

The query basically needs to compute inventory (Tangible) and Available for sale (Intagible, includes orders not yet delivered), it also computes undelivered orders, unserved sales, total inventory and total available for sale. Each product has different characteristics (ie. Item1 has "A" and "B" characteristic.. It has single barcode but 2 char.. the naming convention we used is Item1A, Item1B)

So for example

Item1**A has inventory of 10 and Avail sales of 20

Item1**B has inventory of 30 and Avail sales of 40

The query shows individual record but for total inventory and Avail sales for Item1 will be 40 and 60 respectively

There are different transaction for every item, that is

Purchased
Sales
Adjustment
Brekage
Returns

All are in Transaction Header and Details Table

That is why the query used almost same tables, different conditions

Thanks and Yeah, It also burns my Eyes :D

I have 6 Tables

TxnHead and TxnDetails
(For Transactions Like, Purchased, Sales, Returs, Breakage, Adjustments)

Receiving and RDetails
(For Receiving of Purchased Orders)

Delivery and DDetails

(For Saving of Sales Delivery Records)

In Geting Inventory, Total Inventory we have formula --

(Received + Returs + adjustment - Sales - Breakage - Delivery)

Available for sales (per item with Characteristics), total Available for sales (Item disregarding characteristics--

(Purchased + received + adjustment + returns - breakage - delivery - sales - advance sales)

Hope this helps

Perhaps it would be easier to split this select into a number of smaller SELECTs each satisfying a seperate case (received, etc.) Then use the UNION operator to join the whole mess together.
Also, split the lines up. Seperate the code so that you can actually read each join and understand where it fits in in whole.
That would make the code a little easier to read and stop our eyes bleeding when we try to help.

Edited 2 Years Ago by pclfw

Im trying to combines almost same queries

how can I get sum of this select statement

(Select TID, Barcode, ISNULL(Sum(Quantity),0) as 'PO' From TxnDetails Left Join TxnHead on TxnDetails.TxnID = TxnHead.TxnID where Ttype= 1 and TxnHead.Deleted is Null Group by TID, Barcode) Purchased on Purchased.TID = Tone.TID

where this statement is joined with other select statement and need to get sum by TID and Barcode

If I have item with Barcode,TID, count, repectively

A         1       5
A         2       5
A         3       10
B         1       3

so, the sum of 'A' per TID will be

A1=5
A2=5
A3=10

and sum of 'A' per Barcode

A=20

Thanks