SQL Server Performance

grouping select by month problem

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by sun919, Nov 23, 2006.

  1. sun919 New Member

    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
  2. FrankKalis Moderator

    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
  3. sun919 New Member

    i did a bit of shifting and use some of your i got the probelm solve now thanks ...
    sun
  4. FrankKalis Moderator

    You're welcome![<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />If you regulary deal with datetime related problems, you might this useful: <br /<a target="_blank" href=http://www.sql-server-performance.com/fk_datetime.asp>http://www.sql-server-performance.com/fk_datetime.asp</a><br /><br />--<br />Frank Kalis<br />Moderator<br />Microsoft SQL Server MVP<br />Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a>

Share This Page