| Author |
 Topic  |
|
|
sun919
Starting Member
22 Posts |
 Posted - 12/04/2006 : 00:38:13
|
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
|
Add t.number in GROUP BY clause! 
Harsh Athalye
India.
"Nothing is Impossible" |
 |
|
|
sun919
Starting Member
22 Posts |
Posted - 12/04/2006 : 11:46:35
|
try to that already and it didnt work it said t.number can not be multiple bound
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 12/04/2006 : 11:56:01
|
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 |
|
|
|
sun919
Starting Member
22 Posts |
Posted - 12/04/2006 : 12:16:00
|
thanks u peter i got the problem solve now
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 12/04/2006 : 13:37:54
|
It was the last GROUP BY t.number, right?
Peter Larsson
Helsingborg, Sweden |
|
|
|
sun919
Starting Member
22 Posts |
Posted - 12/05/2006 : 11:11:29
|
| yes |
|
|
|
sun919
Starting Member
22 Posts |
Posted - 12/06/2006 : 02:40:04
|
| If i want to sort the order from october 2005- sep 2006 how would i do with with above sql command |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29138 Posts |
Posted - 12/06/2006 : 02:55:36
|
Sometihng like this. I can't test it since I don't have the source tablesSELECT 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 |
|
|
|
sun919
Starting Member
22 Posts |
Posted - 12/06/2006 : 05:05:19
|
| Thanks u so much peter it works perfectly .... |
|
|
| |
Topic |
|
|
|
Store procedure problems with grouping
