SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Any Ideas
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

osirisa
Constraint Violating Yak Guru

USA
284 Posts

Posted - 11/12/2007 :  15:15:21  Show Profile  Click to see osirisa's MSN Messenger address  Reply with Quote
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_vol
from  TIES_Gathering.dbo.contract a
Inner Join TIES_Gathering.dbo.NOm b on a.contract_nbr = b.contract_nbr
Inner Join  TIES_Gathering.dbo.Nom_vol_detail c on c.Nom_id = b.Nom_id
where (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

Sweden
29910 Posts

Posted - 11/12/2007 :  15:33:44  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 11/12/2007 :  15:36:25  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
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_vol
from  TIES_Gathering.dbo.contract a
Inner Join TIES_Gathering.dbo.NOm b on a.contract_nbr = b.contract_nbr
Inner Join  TIES_Gathering.dbo.Nom_vol_detail c on c.Nom_id = b.Nom_id
where (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.

Go to Top of Page

osirisa
Constraint Violating Yak Guru

USA
284 Posts

Posted - 11/12/2007 :  15:59:16  Show Profile  Click to see osirisa's MSN Messenger address  Reply with Quote
Thank You Dataguru1971 Your answer IS VERY HELPFUL ---THANK YOU.
Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 11/12/2007 :  16:34:02  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
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.

Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 11/12/2007 :  17:06:40  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Go to Top of Page

osirisa
Constraint Violating Yak Guru

USA
284 Posts

Posted - 11/12/2007 :  17:11:54  Show Profile  Click to see osirisa's MSN Messenger address  Reply with Quote
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
Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 11/12/2007 :  17:14:06  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
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.

Go to Top of Page

osirisa
Constraint Violating Yak Guru

USA
284 Posts

Posted - 11/13/2007 :  10:02:00  Show Profile  Click to see osirisa's MSN Messenger address  Reply with Quote



Contract_nbr********Beg_eff_date******rcpt_nom_vol****rcpt_fuel****rcpt_act_vol***
GTH00001             2/1/2007           652            47            701
GTH00001             2/2/2007           652            47            798
GTH00001             2/3/2007           652            47            698
GTH00003             2/1/2007           653            48            699


TotalFuel as (rcpt_nom_vol -rcpt_fuel- c.rcpt_act_vol)

The Report form looks something like this

Contract_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!!!!!!



Go to Top of Page

osirisa
Constraint Violating Yak Guru

USA
284 Posts

Posted - 11/13/2007 :  10:03:53  Show Profile  Click to see osirisa's MSN Messenger address  Reply with Quote
The section that appear as calculation is
TotalFuel as (rcpt_nom_vol -rcpt_fuel- c.rcpt_act_vol)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000