Complicated SQL query - HELP - 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

General SQL Server Forums

New to SQL Server Programming

Complicated SQL query - HELP

Author

Topic

nizguy
Starting Member

37 Posts

Posted - 2009-10-29 : 15:24:22

I am working on the consolidated report for my company. I am getting stuck with the calculate the Wt% and the Revenue. I hope somebody can help.

Here is the sql query that I have now, How do I calculate the Weight % and the Revenu.The consolidated bill (type "Con") has a total weight, Net and Fuel.

Wt% = Shipment wt / wt in Consolidate bill (ie. 21014 / 25234 = .83, 1899 / 25234 = .08 etc...)
Rev = total Con * Wt% (ie. 6600 * .832765 = 5496.25, 6600 * .075256 = 496.69, etc....)

It seems confuse, I hope you understand. Thank you very much

Bill table:
bill_num char 10,
bill_type char 3,
weight int,
freight_net money,
fuel_sc money

Manifest table:
Manifest_ID char 6,
Bill_num char 10

select m.Manifest_id, b.bill_type, b.bill_num,b.weight,
b.freight_net + fuel_sc as 'Total Invoice'
from bill b inner join manifest m on b.bill_num = m.bill_num
where m.manifest_id = '93205'

Result from this query
Manifest# Type Bill# Weight Total Wt% Rev
93205 NRv 290180579 21014 NULL
93205 NRv 290202843 1899 NULL
93205 NRv 290559426 277 NULL
93205 NRv 290750356 1432 NULL
93205 NRv 290763900 292 NULL
93205 NRv 290790720 320 NULL
93205 Con 232207995 25234 6600

I want the new result to return these values
Manifest# Type Bill# Weight Total Wt% Rev
93205 NRv 290180579 21014 NULL 0.832765 5496.25
93205 NRv 290202843 1899 NULL 0.075256 496.69
93205 NRv 290559426 277 NULL 0.010977 72.45
93205 NRv 290750356 1432 NULL 0.056749 374.54
93205 NRv 290763900 292 NULL 0.011572 76.37
93205 NRv 290790720 320 NULL 0.012681 83.70
93205 Con 232207995 25234 6600 1.000000

thank you very very much

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-31 : 01:42:27

seems like this:-


select m.Manifest_id, b.bill_type, b.bill_num,b.weight, 
b.freight_net + fuel_sc as 'Total Invoice' into #Temp
from bill b inner join manifest m on b.bill_num = m.bill_num 
where m.manifest_id = '93205'

select t.*,
t.Weight*1.0/t1.Weight	AS [Wt%],
t.Total	* (t.Weight*1.0/t1.Weight) AS Rev
from #Temp t
cross apply (select Weight,Total
             from #Temp
             where  manifest_id=t.manifest_id
             and bill_type='Con'
             )t1

drop table #Temp

nizguy
Starting Member

37 Posts

Posted - 2009-11-02 : 00:43:38

Visakh16, I tried this sql query. However, my company uses Sql server 2000, and I think the cross apply is not supported. Is there any other way to do it without using cross apply?

thanks for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources