Is this even possible...?

# Thread: Is this even possible...?

1. Senior Member
Join Date
Dec 1969
Posts
490

## Is this even possible...?

Is there a way in a SQL statement to do this, it&#039;s for an inventory. I&#039;m trying to calculate the number of items on back order. Here are my two fields...<BR><BR>Table : Field : Value<BR>----------------------------------<BR>Inventory : sku : 1234<BR>Inventory : Inv_Quantity : 2<BR>Allocated : sku : 1234<BR>Allocated : All_Quantity : 5<BR><BR>If the quantity allocated minus the quantity in inventory is less than 0 then I want it to tell me how many items need to be back ordered. In this case, the number of items on back order would be 3. I could do it in asp, but I&#039;m wondering if there&#039;s a way in a SQL statement to return all rows that have a backorder quantity along with what that quantity is? <BR><BR>BTW, I&#039;m using SQL Server 2000.<BR><BR>Thanks,<BR>Wil

2. Senior Member
Join Date
Dec 1969
Posts
11,247

## RE: Is this even possible...?

select sum(a.all_quantity - i.inv_quantity) as backorder , i.sku<BR>from inventory i join allocated a on i.sku = a.sku <BR>where a.all_quantity &#062; i.inv_quantity<BR>group by sku<BR><BR><BR>Or something like that anyhow

3. Senior Member
Join Date
Dec 1969
Posts
96,118

## That would never work...

You apparently are assuming that there might be multiple instances of the same SKU in the Allocated table. That&#039;s fine.<BR><BR>But then you WHERE clause checks ONLY the individual records!<BR><BR>So if he had something like this:<BR><BR>Table : Field : Value <BR>---------------------------------- <BR>Inventory : sku : 1234 <BR>Inventory : Inv_Quantity : 7<BR>Allocated : sku : 1234 <BR>Allocated : All_Quantity : 5 <BR>Allocated : sku : 1234 <BR>Allocated : All_Quantity : 6 <BR><BR>Your WHERE clause would never see a case where<BR> a.all_quantity &#062; i.inv_quantity<BR>so it would never trigger any output to the record.<BR><BR>On top of that, you would be getting the SUM of <BR> 5 - 7 <BR>plus<BR> 5 - 6<BR>because remember each record is going to be done *INDIVIDUALLY*! So you&#039;d get a SUM of -3!!! When the actual backorder number should be 7-(5+6) or -4.<BR><BR><BR>If you are going to assume multiple records with the same SKU in the Allocated table, then you need to do something like this:<BR><BR> SELECT SUM(a.all_quantity) - AVG(i.inv_quantity) as backorder, i.sku<BR> FROM inventory AS i, allocated AS a <BR> WHERE i.sku = a.sku<BR> GROUP BY sku<BR> HAVING backorder &#062; 0<BR><BR>That works because AVG(i.inv_quantity) will be the same value as any one of the i.inv_quantity (they&#039;ll all be identical for a given SKU). So you will get SUM(5+6) - AVG(7 and 7) or 11-7, so 4.<BR><BR>If there is never more than one instance of the same SKU in the Allocated table, then it&#039;s simpler:<BR><BR> SELECT a.all_quantity - i.inv_quantity AS backorder, i.sku<BR> FROM inventory AS i, allocated AS a <BR> WHERE i.sku = a.sku<BR> AND a.all_quantity &#062; i.inv_quantity<BR><BR><BR> <BR> <BR><BR><BR>**************<BR><BR>You seem to be assuming that there are multiple records

4. Senior Member
Join Date
Dec 1969
Posts
490

## RE: Is this even possible...?

Thanks WK, I&#039;ll have to try it tomorrow. It&#039;s time to go home!! :)<BR><BR>Wil<BR>

5. Senior Member
Join Date
Dec 1969
Posts
96,118

## Forgot to say...

The reason the HAVING works when the WHERE doesn&#039;t is because it checks the result *AFTER* the aggregation takes place, instead of before.<BR><BR>I thought of an even nuttier example that would really whack out WK&#039;s code:<BR><BR>Table : Field : Value <BR>---------------------------------- <BR>Inventory : sku : 1234 <BR>Inventory : Inv_Quantity : 7<BR><BR>Allocated : sku : 1234 <BR>Allocated : All_Quantity : 5 <BR>Allocated : sku : 1234 <BR>Allocated : All_Quantity : 5 <BR>Allocated : sku : 1234 <BR>Allocated : All_Quantity : 5 <BR>Allocated : sku : 1234 <BR>Allocated : All_Quantity : 5 <BR>Allocated : sku : 1234 <BR>Allocated : All_Quantity : 5 <BR>Allocated : sku : 1234 <BR>Allocated : All_Quantity : 5 <BR>Allocated : sku : 1234 <BR>Allocated : All_Quantity : 5 <BR>Allocated : sku : 1234 <BR>Allocated : All_Quantity : 5 <BR>Allocated : sku : 1234 <BR>Allocated : All_Quantity : 5 <BR>Allocated : sku : 1234 <BR>Allocated : All_Quantity : 8<BR><BR>WK&#039;s query would give you an answer of 1 for the backorder! Really. Try it out.<BR><BR>

6. Senior Member
Join Date
Dec 1969
Posts
96,118

## Wimping out, huh?

Real programmers stay until the job is done.<BR><BR>&#060;grin size="major" style="s.e." /&#062;<BR><BR>

7. Senior Member
Join Date
Dec 1969
Posts
490

## RE: That would never work...

There are never dupes in the allocated table, it&#039;s being dynamically generated by all the orders pending.<BR><BR>Thanks Bill!<BR>and WK!<BR>

8. Senior Member
Join Date
Dec 1969
Posts
490

## RE: Wimping out, huh?

Hence the reason I&#039;m going home lol

9. Senior Member
Join Date
Dec 1969
Posts
11,247

## Wooow

I&#039;m in the **** tonight, maybe that coffee didn&#039;t work after all!<BR>Yeah your right Bill. I should have left it alone and worked on the DB tomorrow. OR bettershill Left it to the *expert* ;-)

10. Senior Member
Join Date
Dec 1969
Posts
11,247

## And then some!

I must be sick sitting here doing this at this time of night

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•