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
 Daily Calculation need help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

osirisa
Constraint Violating Yak Guru

USA
289 Posts

Posted - 11/14/2007 :  17:10:35  Show Profile  Click to see osirisa's MSN Messenger address  Reply with Quote
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)

USA
7020 Posts

Posted - 11/14/2007 :  17:14:07  Show Profile  Reply with Quote
Would it be possible for you to let us know what the syntax error message that you are geting is?



CODO ERGO SUM
Go to Top of Page

osirisa
Constraint Violating Yak Guru

USA
289 Posts

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

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 11/14/2007 :  17:20:42  Show Profile  Reply with Quote
Put a SUM() around your case statement.

CODO ERGO SUM
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 11/14/2007 :  17:20:59  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"

Edited by - SwePeso on 11/14/2007 17:21:51
Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

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

Go to Top of Page

osirisa
Constraint Violating Yak Guru

USA
289 Posts

Posted - 11/14/2007 :  17:27:17  Show Profile  Click to see osirisa's MSN Messenger address  Reply with Quote
Thank You both !!!!
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.08 seconds. Powered By: Snitz Forums 2000