Daily Calculation need 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

Daily Calculation need help

Author

Topic

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2007-11-14 : 17:10:35

Hi: I am having a problem with the Syntax. I am trying to get the daily value for a contract_nbr. The selection below shows that I am selecting by Case statement when day = 2 (2nd Day) and the month is a parameter provider by the user. For this particular example, I am using 2 (February).
Thanks for the help !!!!

[code]
Set @Req_Month = '2'

SELECT Distinct a.contract_nbr,
Case when Day(c.Beg_eff_date) = 2 and month(c.Beg_eff_date)= @Req_Month
Then c.rcpt_nom_vol-c.rcpt_fuel-rcpt_act_vol
Else 0
End As Day_2
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')
Group by a.contract_nbr
[\code]

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-14 : 17:14:07

Would it be possible for you to let us know what the syntax error message that you are geting is?

CODO ERGO SUM

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2007-11-14 : 17:17:01

Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.rcpt_nom_vol' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.rcpt_fuel' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.rcpt_act_vol' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.beg_eff_date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'c.beg_eff_date' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-14 : 17:20:42

Put a SUM() around your case statement.

CODO ERGO SUM

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-14 : 17:20:59

Both DISTINCT and GROUP BY?

SET @Req_Month = 2

SELECT		a.contract_nbr,
		SUM(CASE
			WHEN DAY(c.Beg_eff_date) = 2 AND MONTH(c.Beg_eff_date) = @Req_Month THEN c.rcpt_nom_vol - c.rcpt_fuel - c.rcpt_act_vol 
			ELSE 0 
		END) As Day_2
FROM		TIES_Gathering.dbo.contract AS a
INNER JOIN	TIES_Gathering.dbo.NOm AS b on b.contract_nbr = a.contract_nbr
Inner Join	TIES_Gathering.dbo.Nom_vol_detail AS c on c.Nom_id = b.Nom_id
where		a.contract_sub_type = 'INT'
		and a.Contract_type_code ='GTH'
		and DATEADD(month, DATEDIFF(month, 0, current_timestamp), 0) < a.current_expirtn_date
		and c.rcpt_dlvry_ind = 'R'
Group by	a.contract_nbr
order by	a.contract_nbr

E 12°55'05.25"
N 56°04'39.16"

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-14 : 17:21:12

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=91542
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=92585
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=92388
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=92515

I am seeing a pattern develop.

If they aren't included in your group by clause, just going out on a limb, but you might want to include them in your group by clause.


SELECT Distinct a.contract_nbr,
Case when Day(c.Beg_eff_date) = 2 and month(c.Beg_eff_date)= @Req_Month
Then c.rcpt_nom_vol-c.rcpt_fuel-rcpt_act_vol
Else 0
End As Day_2
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')
Group by a.contract_nbr,
Case when Day(c.Beg_eff_date) = 2 and month(c.Beg_eff_date)= @Req_Month
Then c.rcpt_nom_vol-c.rcpt_fuel-rcpt_act_vol
Else 0
End

You need to make some progress on debugging your own basic syntax. As said previously, this forum should not simply be your default debugger.

Poor planning on your part does not constitute an emergency on my part.

osirisa
Constraint Violating Yak Guru

289 Posts

Posted - 2007-11-14 : 17:27:17

Thank You both !!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources