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
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Store procedure problems with grouping
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sun919
Starting Member

22 Posts

Posted - 12/04/2006 :  00:38:13  Show Profile  Reply with Quote
Hi there ,
I have a question asking regarding using stored procedure. Basically I have the following store procedure (with help from peso, Thanks man) but I have problem with saving it. When i try to save it give me the following error " t.number is invalid in the select list because it is not contain either aggregate function or group by clause" so how do come about and solve problem , please help

below are my store procedure coding :
SELECT t.number AS Month, ISNULL(SUM(CASE WHEN z.CustomerType = 'New' THEN z.cnt ELSE 0 END), 0) AS 'New',
ISNULL(SUM(CASE WHEN z.CustomerType = 'Old' THEN z.cnt ELSE 0 END), 0) AS 'Old'
FROM (SELECT number
FROM master.dbo.spt_values
WHERE (name IS NULL) AND (number BETWEEN 1 AND 12)) AS t LEFT OUTER JOIN
(SELECT CASE WHEN (DATEPART(month, p.register) = DATEPART(month, a.appointmentDate) AND DATEPART(year, p.register) = DATEPART(year,
a.AppointmentDate)) THEN 'New' ELSE 'Old' END AS CustomerType, DATEPART(month, a.AppointmentDate) AS theMonth, 1 AS cnt
FROM Appointment AS a INNER JOIN
Patient AS p ON p.HNID = a.HNID
WHERE (DATEPART(year,a.AppointmentDate) = @year)
GROUP BY p.HN, CASE WHEN (DATEPART(month, p.register) = DATEPART(month, a.appointmentDate) AND DATEPART(year, p.register) = DATEPART(year,
a.AppointmentDate)) THEN 'New' ELSE 'Old' END, DATEPART(month, a.AppointmentDate)) AS z
ON
z.theMonth = t.number

(where @year is int input)

many thanks
sun

harsh_athalye
Flowing Fount of Yak Knowledge

India
5509 Posts

Posted - 12/04/2006 :  00:56:36  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
Add t.number in GROUP BY clause!

Harsh Athalye
India.
"Nothing is Impossible"
Go to Top of Page

sun919
Starting Member

22 Posts

Posted - 12/04/2006 :  11:46:35  Show Profile  Reply with Quote
try to that already and it didnt work it said t.number can not be multiple bound
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 12/04/2006 :  11:56:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Work ok for me...
SELECT		t.number AS [Month],
		SUM(CASE WHEN z.CustomerType = 'New' THEN z.cnt ELSE 0 END) AS 'New', 
		SUM(CASE WHEN z.CustomerType = 'Old' THEN z.cnt ELSE 0 END) AS 'Old'
FROM		(
			SELECT	number
			FROM	master.dbo.spt_values
			WHERE	name IS NULL
				AND number BETWEEN 1 AND 12
		) AS t
LEFT JOIN	(
			SELECT		CASE
						WHEN DATEPART(month, p.register) = DATEPART(month, a.appointmentDate) AND DATEPART(year, p.register) = DATEPART(year, 
a.AppointmentDate) THEN 'New'
						ELSE 'Old'
					END AS CustomerType,
					DATEPART(month, a.AppointmentDate) AS theMonth,
					1 AS cnt
			FROM		Appointment AS a
			INNER JOIN	Patient AS p ON p.HNID = a.HNID
			WHERE		DATEPART(year, a.AppointmentDate) = @year
			GROUP BY	p.HN,
					CASE
						WHEN DATEPART(month, p.register) = DATEPART(month, a.appointmentDate) AND DATEPART(year, p.register) = DATEPART(year, 
a.AppointmentDate) THEN 'New'
						ELSE 'Old' END,
					DATEPART(month, a.AppointmentDate)
		) AS z ON z.theMonth = t.number
GROUP BY	t.number


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

sun919
Starting Member

22 Posts

Posted - 12/04/2006 :  12:16:00  Show Profile  Reply with Quote
thanks u peter i got the problem solve now
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 12/04/2006 :  13:37:54  Show Profile  Visit SwePeso's Homepage  Reply with Quote
It was the last GROUP BY t.number, right?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

sun919
Starting Member

22 Posts

Posted - 12/05/2006 :  11:11:29  Show Profile  Reply with Quote
yes
Go to Top of Page

sun919
Starting Member

22 Posts

Posted - 12/06/2006 :  02:40:04  Show Profile  Reply with Quote
If i want to sort the order from october 2005- sep 2006 how would i do with with above sql command
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 12/06/2006 :  02:55:36  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Sometihng like this. I can't test it since I don't have the source tables
SELECT		z.theYear [Year],
		t.number AS [Month],
		SUM(CASE WHEN z.CustomerType = 'New' THEN z.cnt ELSE 0 END) AS 'New', 
		SUM(CASE WHEN z.CustomerType = 'Old' THEN z.cnt ELSE 0 END) AS 'Old'
FROM		(
			SELECT	number
			FROM	master.dbo.spt_values
			WHERE	name IS NULL
				AND number BETWEEN 1 AND 12
		) AS t
LEFT JOIN	(
			SELECT		CASE
						WHEN DATEPART(month, p.register) = DATEPART(month, a.appointmentDate)
							AND DATEPART(year, p.register) = DATEPART(year, a.AppointmentDate) THEN 'New'
						ELSE 'Old'
					END AS CustomerType,
					DATEPART(year, a.AppointmentDate) AS theYear,
					DATEPART(month, a.AppointmentDate) AS theMonth,
					1 AS cnt
			FROM		Appointment AS a
			INNER JOIN	Patient AS p ON p.HNID = a.HNID
			WHERE		a.AppointmentDate BETWEEN '20051001' AND '20060930'
			GROUP BY	p.HN,
					CASE
						WHEN DATEPART(month, p.register) = DATEPART(month, a.appointmentDate)
							AND DATEPART(year, p.register) = DATEPART(year, a.AppointmentDate) THEN 'New'
						ELSE 'Old' END,
					DATEPART(year, a.AppointmentDate),
					DATEPART(month, a.AppointmentDate)
		) AS z ON z.theMonth = t.number
GROUP BY	z.theYear,
		t.number


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

sun919
Starting Member

22 Posts

Posted - 12/06/2006 :  05:05:19  Show Profile  Reply with Quote
Thanks u so much peter it works perfectly ....
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