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.
| Author |
Topic |
|
osirisa
Constraint Violating Yak Guru
289 Posts |
 Posted - 2007-11-12 : 15:15:21
|
Hi:I created a Temp Table that hold a collection of records by date. I need to do create calculation that give me the Total fuel used per day: c.rcpt_nom_vol -c.rcpt_fuel- c.rcpt_act_vol = Total Fuel used per day.Then pull the result of this calculation and assign the result to the specific day of the month. For example is the Calculation Result is 4.25 on Feb 12, 2007. Then the record is insert into a Temp Table as 4.25 day #12. Does anyone has an idea of how to do this? Thanks and Let me know!!!!Create Table #TP_Daily_Imb( contract_nbr char (8), contract_sub_type char (3), contract_type_code char (3), current_expirtn_date datetime, nom_id char(12), nom_rev_nbr char(2), beg_eff_date datetime, rcpt_dlvry_ind float, rcpt_nom_vol float, rcpt_fuel float, rcpt_act_vol float, end_eff_date_DGC datetime) SELECT Distinct a.contract_nbr, a.contract_sub_type, a.contract_type_code,a.current_expirtn_date,b.nom_id, b.nom_rev_nbr,c.beg_eff_date, c.rcpt_dlvry_ind, c.rcpt_nom_vol, c.rcpt_fuel,c.rcpt_act_volfrom TIES_Gathering.dbo.contract aInner Join TIES_Gathering.dbo.NOm b on a.contract_nbr = b.contract_nbrInner Join TIES_Gathering.dbo.Nom_vol_detail c on c.Nom_id = b.Nom_idwhere (a.contract_sub_type = 'INT') and (a.Contract_type_code ='GTH')and (DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) < a.current_expirtn_date) and (c.rcpt_dlvry_ind ='R') |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-11-12 : 15:33:44
|
It would be SO MUCH SIMPLER with some accurate sample data to test on.Did you forget to post this vital piece of information this time too?
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-12 : 15:36:25
|
Specify the field as a computed column in your create table statement, then insert values into the table (except the computed column)Something like:Create Table #TP_Daily_Imb( contract_nbr char (8)not null , contract_sub_type char (3) not null, contract_type_code char (3) not null, current_expirtn_date datetime not null, nom_id char(12) not null, nom_rev_nbr char(2) not null, beg_eff_date datetime not null, rcpt_dlvry_ind float not null, rcpt_nom_vol float not null, rcpt_fuel float not null, rcpt_act_vol float not null, end_eff_date_DGC datetime not null TotalFuel as (rcpt_nom_vol -rcpt_fuel- c.rcpt_act_vol) Insert INto #TP_Daily_IMB (contract_nbr char , contract_sub_type, contract_type_code, current_expirtn_date, nom_id, nom_rev_nbr , beg_eff_date , rcpt_dlvry_ind, rcpt_nom_vol, rcpt_fuel , rcpt_act_vol , end_eff_date_DGC)SELECT Distinct a.contract_nbr, a.contract_sub_type, a.contract_type_code,a.current_expirtn_date,b.nom_id, b.nom_rev_nbr,c.beg_eff_date, c.rcpt_dlvry_ind, c.rcpt_nom_vol, c.rcpt_fuel,c.rcpt_act_volfrom TIES_Gathering.dbo.contract aInner Join TIES_Gathering.dbo.NOm b on a.contract_nbr = b.contract_nbrInner Join TIES_Gathering.dbo.Nom_vol_detail c on c.Nom_id = b.Nom_idwhere (a.contract_sub_type = 'INT') and (a.Contract_type_code ='GTH')and (DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) < a.current_expirtn_date) and (c.rcpt_dlvry_ind ='R')
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
osirisa
Constraint Violating Yak Guru
289 Posts |
Posted - 2007-11-12 : 15:59:16
|
| Thank You Dataguru1971 Your answer IS VERY HELPFUL ---THANK YOU. |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-12 : 16:34:02
|
quote:
Originally posted by osirisa
Thank You Dataguru1971 Your answer IS VERY HELPFUL ---THANK YOU.
You are welcome, but Peso is right. A better solution might be had with proper data to utlize. All I did was add the calculated column to your existing work.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-11-12 : 17:06:40
|
Well, at least that. We don't even know what kind of data there is in the table!Is it already aggregated? Do we need to aggregate in our suggestions?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
osirisa
Constraint Violating Yak Guru
289 Posts |
Posted - 2007-11-12 : 17:11:54
|
| Ok. Sorry, for the incomplete information. Is there anyway that I can upload a file, so you can see the actual data? Let me know |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-11-12 : 17:14:06
|
quote:
Originally posted by Peso
Well, at least that. We don't even know what kind of data there is in the table!Is it already aggregated? Do we need to aggregate in our suggestions?
E 12°55'05.25"N 56°04'39.16"
True dat!At the core, the question really was "how do I add a computed column". The fact that the temp table, or the reason for needing the column in the first place can likely be eliminated...well, you know ...
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
osirisa
Constraint Violating Yak Guru
289 Posts |
Posted - 2007-11-13 : 10:02:00
|
Contract_nbr********Beg_eff_date******rcpt_nom_vol****rcpt_fuel****rcpt_act_vol***GTH00001 2/1/2007 652 47 701GTH00001 2/2/2007 652 47 798GTH00001 2/3/2007 652 47 698GTH00003 2/1/2007 653 48 699 TotalFuel as (rcpt_nom_vol -rcpt_fuel- c.rcpt_act_vol) The Report form looks something like thisContract_Nbr***** Day 1***************** Day 2******* Day 3 ***** Day 4***** Day 31 up to the end of the month GTH00001 2/1/2007(calculation) 2/2/2007(calc.) same same up to the end of the month. Any ideas of how to do this Report. Thanks for the help!!!!!! |
|
|
|
osirisa
Constraint Violating Yak Guru
289 Posts |
Posted - 2007-11-13 : 10:03:53
|
| The section that appear as calculation isTotalFuel as (rcpt_nom_vol -rcpt_fuel- c.rcpt_act_vol) |
|
|
|
|
|
|
|
|
Any Ideas