Trim down query

[Lethugs]

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

[Lethugs]

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.

[minitauros]

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?

[Reverend Jim]

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

[Lethugs]

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

[Lethugs]

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

[pclfw]

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.

[Lethugs]

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