SQL Server Performance Forum – Threads Archive

grouping select by month problem

Hi, there
I have a question to ask regarding grouping. Basically how do you group data that are in different months
for example I want to retrieve number of patientthat had appointment in 2006, but I also want this to group the amount of each patient for each month (doing a yearly report), I have manage to specify each month for each query (which mean i need to do query 12 times) and I am wondering is there a better approach to do so. This is my sql command SELECT DISTINCT Patient.HN
FROM Appointment INNER JOIN
Patient ON Appointment.HNID = Patient.HNID
WHERE (Patient.Register BETWEEN CONVERT(DATETIME, ‘2006-01-01 00:00:00’, 102) AND CONVERT(DATETIME, ‘2007-01-01 00:00:00’, 102))
GROUP BY Appointment.AppointmentDate, Patient.HN
HAVING (Appointment.AppointmentDate BETWEEN CONVERT(DATETIME, ‘2006-10-01 00:00:00’, 102) AND CONVERT(DATETIME, ‘2006-11-01 00:00:00’, 102)) // That for checking during October In the end i want to be able to check on how many new and old patient come but I think if I know how to do this , similar command should be also to apply many thanks
sun

Untested…
SELECT DISTINCT Patient.HN, DATEADD(MONTH, DATEDIFF(MONTH, 0, Appointment.AppointmentDate), 0)
FROM Appointment INNER JOIN
Patient ON Appointment.HNID = Patient.HNID
WHERE (Patient.Register BETWEEN CONVERT(DATETIME, ‘2006-01-01 00:00:00’, 102) AND CONVERT(DATETIME, ‘2007-01-01 00:00:00’, 102))
GROUP BY Appointment.AppointmentDate, Patient.HN, DATEADD(MONTH, DATEDIFF(MONTH, 0, Appointment.AppointmentDate), 0) HAVING (Appointment.AppointmentDate BETWEEN CONVERT(DATETIME, ‘2006-10-01 00:00:00’, 102) AND CONVERT(DATETIME, ‘2006-11-01 00:00:00’, 102))
—
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de

i did a bit of shifting and use some of your i got the probelm solve now thanks …
sun

You’re welcome![<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] If you regulary deal with datetime related problems, you might this useful: http://www.sql-server-performance.com/fk_datetime.asp</a> – Frank Kalis Moderator Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

]]>