grouping select by month problem

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

grouping select by month problem

Author

Topic

sun919
Starting Member

22 Posts

Posted - 2006-11-23 : 05:17:21

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

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-23 : 05:27:52

This discussion might help solve your problem (until patron saint comes to your help):

[url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=75239[/url]

Also, the requirement there was for hourly report, your case looks much similar. A few modifications may solve the problem.

Harsh Athalye
India.
"Nothing is Impossible"

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-11-23 : 05:30:14

[code]SELECT DATENAME(year, Appointment.AppointmentDate) AS AppointmentYear
,DATENAME(month, Appointment.AppointmentDate) AS AppointmentMonth
,COUNT(DISTINCT Patient.HNID) AS Appointments
FROM Appointment
JOIN Patient
ON Appointment.HNID = Patient.HNID
WHERE DATEPART(year, Patient.Register) = 2006
GROUP BY DATENAME(year, Appointment.AppointmentDate)
,DATENAME(month, Appointment.AppointmentDate)

[/code]

Mark

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-23 : 05:30:55

[code]SELECT p.HN,
CASE WHEN p.Register >= '20060101' AND p.Register < '20070101' THEN 'New' ELSE 'Old' END CustomerType,
DATEADD(day, DATEDIFF(day, 0, RegisterDate), 0) RegisterDate,
SUM(CASE WHEN DATEPART(month, a.AppointmentDate) = 1 THEN 1 ELSE 0 END) 'January',
SUM(CASE WHEN DATEPART(month, a.AppointmentDate) = 2 THEN 1 ELSE 0 END) 'February',
SUM(CASE WHEN DATEPART(month, a.AppointmentDate) = 3 THEN 1 ELSE 0 END) 'March',
SUM(CASE WHEN DATEPART(month, a.AppointmentDate) = 4 THEN 1 ELSE 0 END) 'April',
SUM(CASE WHEN DATEPART(month, a.AppointmentDate) = 5 THEN 1 ELSE 0 END) 'May',
SUM(CASE WHEN DATEPART(month, a.AppointmentDate) = 6 THEN 1 ELSE 0 END) 'June',
SUM(CASE WHEN DATEPART(month, a.AppointmentDate) = 7 THEN 1 ELSE 0 END) 'July',
SUM(CASE WHEN DATEPART(month, a.AppointmentDate) = 8 THEN 1 ELSE 0 END) 'August',
SUM(CASE WHEN DATEPART(month, a.AppointmentDate) = 9 THEN 1 ELSE 0 END) 'September',
SUM(CASE WHEN DATEPART(month, a.AppointmentDate) = 10 THEN 1 ELSE 0 END) 'October',
SUM(CASE WHEN DATEPART(month, a.AppointmentDate) = 11 THEN 1 ELSE 0 END) 'November',
SUM(CASE WHEN DATEPART(month, a.AppointmentDate) = 12 THEN 1 ELSE 0 END) 'December',
SUM(1) TotalAppointments
FROM Patient p
INNER JOIN Appointment a ON a.HNID = p.HNID
WHERE a.AppointmentDate >= '20060101' AND a.AppointmentDate < '20070101'
GROUP BY p.HN,
CASE WHEN p.Register >= '20060101' AND p.Register < '20070101' THEN 'New' ELSE 'Old' END,
DATEADD(day, DATEDIFF(day, 0, RegisterDate), 0)
ORDER BY p.HN[/code]
Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-23 : 05:43:04

[code]-- Horizontal
SELECT q.CustomerType,
SUM(CASE WHEN q.theMonth = 1 THEN q.cnt ELSE 0 END) 'January',
SUM(CASE WHEN q.theMonth = 2 THEN q.cnt ELSE 0 END) 'February',
SUM(CASE WHEN q.theMonth = 3 THEN q.cnt ELSE 0 END) 'March',
SUM(CASE WHEN q.theMonth = 4 THEN q.cnt ELSE 0 END) 'April',
SUM(CASE WHEN q.theMonth = 5 THEN q.cnt ELSE 0 END) 'May',
SUM(CASE WHEN q.theMonth = 6 THEN q.cnt ELSE 0 END) 'June',
SUM(CASE WHEN q.theMonth = 7 THEN q.cnt ELSE 0 END) 'July',
SUM(CASE WHEN q.theMonth = 8 THEN q.cnt ELSE 0 END) 'August',
SUM(CASE WHEN q.theMonth = 9 THEN q.cnt ELSE 0 END) 'September',
SUM(CASE WHEN q.theMonth = 10 THEN q.cnt ELSE 0 END) 'October',
SUM(CASE WHEN q.theMonth = 11 THEN q.cnt ELSE 0 END) 'November',
SUM(CASE WHEN q.theMonth = 12 THEN q.cnt ELSE 0 END) 'December'
FROM (
SELECT CASE WHEN p.Register >= '20060101' AND p.Register < '20070101' THEN 'New' ELSE 'Old' END CustomerType,
DATEPART(month, a.AppointmentDate) theMonth,
1 cnt
FROM Appointments a
INNER JOIN Patients p ON p.HNID = a.HNID
WHERE a.AppointmentDate >= '20060101' AND a.AppointmentDate < '20070101'
GROUP BY p.HN,
CASE WHEN p.Register >= '20060101' AND p.Register < '20070101' THEN 'New' ELSE 'Old' END,
DATEPART(month, a.AppointmentDate)
) q
GROUP BY q.CustomerType
ORDER BY q.CustomerType

-- Vertical
SELECT t.Number [Month],
ISNULL(SUM(CASE WHEN z.CustomerType = 'New' THEN z.cnt ELSE 0 END), 0) 'New',
ISNULL(SUM(CASE WHEN z.CustomerType = 'Old' THEN z.cnt ELSE 0 END), 0) 'Old'
FROM (
SELECT Number
FROM master..spt_values
WHERE Name IS NULL
AND Number BETWEEN 1 AND 12
) t
LEFT JOIN (
SELECT CASE WHEN p.Register >= '20060101' AND p.Register < '20070101' THEN 'New' ELSE 'Old' END CustomerType,
DATEPART(month, a.AppointmentDate) theMonth,
1 cnt
FROM Appointments a
INNER JOIN Patients p ON p.HNID = a.HNID
WHERE a.AppointmentDate >= '20060101' AND a.AppointmentDate < '20070101'
GROUP BY p.HN,
CASE WHEN p.Register >= '20060101' AND p.Register < '20070101' THEN 'New' ELSE 'Old' END,
DATEPART(month, a.AppointmentDate)
) z ON z.theMonth = t.Number
GROUP BY t.Number[/code]
Peter Larsson
Helsingborg, Sweden

sun919
Starting Member

22 Posts

Posted - 2006-11-23 : 05:54:50

Thanks you all so much by following everyone , it works now many thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-23 : 05:57:01

Which one?

Peter Larsson
Helsingborg, Sweden

sun919
Starting Member

22 Posts

Posted - 2006-11-24 : 03:39:22

mark one is the result that i want .. Here is what my real end sql command result is :

SELECT COUNT(DISTINCT Appointment.HNID) AS Expr1, DATENAME(month, Appointment.AppointmentDate) AS AppointMonth
FROM Appointment INNER JOIN
(SELECT DISTINCT TOP (100) PERCENT HNID AS HNRegister, DATENAME(month, Register) AS MonthRegister
FROM Patient
WHERE (DATEPART(year, Register) = 2006)
GROUP BY DATENAME(Month, Register), HNID
ORDER BY DATENAME(Month, Register), HNRegister) AS derivedtbl_1 ON Appointment.HNID = derivedtbl_1.HNRegister AND
derivedtbl_1.MonthRegister = DATENAME(month, Appointment.AppointmentDate)
WHERE (DATEPART(year, Appointment.AppointmentDate) = 2006)
GROUP BY DATENAME(month, Appointment.AppointmentDate)
ORDER BY AppointMonth

Basically It find the new register patient each month in a year 2006 and matching on no. of new patient make a new appointment with the hospital within that month

many thanks to all
sun

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-24 : 03:53:25

Good luck!

Have you tried my "vertical" approach? It groups by "old" and "new" customertype at once. It also creates a row with 0 appointments if there are no appointments that month. You query above does not do that so you have to deal with that in your front-end. If you are unsure, replace "datepart(month" in the "vertical" with "datename(month".

Peter Larsson
Helsingborg, Sweden

sun919
Starting Member

22 Posts

Posted - 2006-11-28 : 07:25:08

Peter I ve try your vertical approach , it work really well also i got another problem to ask .. I ve try to find work out a query for counting number of new register patient during each month in the year 2006 and count number of other patient that visit . Since i still have to do 2 queries ONE IS TO Count new Patient FOR EACH MONTH DURING 2006 ( The above query that i post) AND another one is Count TOTAL PATIENT During 2006
which is

SELECT DATENAME(month, Appointment.AppointmentDate) AS AppointmentMonth, COUNT(DISTINCT Patient.HNID) AS Appointments
FROM Appointment INNER JOIN
Patient ON Appointment.HNID = Patient.HNID
WHERE (DATEPART(year, Patient.Register) = 2006) AND (DATEPART(year, Appointment.AppointmentDate) = 2006)
GROUP BY DATENAME(month, Appointment.AppointmentDate)
ORDER BY AppointmentMonth DESC

please have a look and help ...
many thanks
sun

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-28 : 07:44:27

Here is a method you can use to invoke almost any calculation of this kind.

SELECT		z.Category,
		t.theMonth [Month],
		SUM(CASE WHEN z.theMonth IS NULL THEN 0 ELSE z.theCount END) cnt
FROM		(
			SELECT		Number
			FROM		master..spt_values
			WHERE		Name IS NULL
					AND Number BETWEEN 1 AND 12
		) t
LEFT JOIN	(
			SELECT		'New registrations' theCategory,
					DATEPART(month, Register) theMonth,
					1 theCount
			FROM		Patients
			WHERE		Register >= '20060101'
					AND Register < '20070101'

			UNION ALL

			SELECT		'Appointments with new customers'
					DATEPART(month, a.AppointmentDate),
					1
			FROM		Appointments a
			INNER JOIN	Patients p ON p.HNID = a.HNID
			WHERE		a.AppointmentDate >= '20060101'
					AND a.AppointmentDate < '20070101'
					AND p.Register >= '20060101'
					AND p.Register < '20070101'

			UNION ALL

			SELECT		'Appointments with old customers'
					DATEPART(month, a.AppointmentDate),
					1
			FROM		Appointments a
			INNER JOIN	Patients p ON p.HNID = a.HNID
			WHERE		a.AppointmentDate >= '20060101'
					AND a.AppointmentDate < '20070101'
					AND p.Register < '20060101'

			UNION ALL

			SELECT		'Total appointments'
					DATEPART(month, a.AppointmentDate),
					1
			FROM		Appointments a
			WHERE		a.AppointmentDate >= '20060101'
					AND a.AppointmentDate < '20070101'
		) z ON z.theMonth = t.Number
GROUP BY	z.Category,
		t.Number
ORDER BY	z.Category,
		t.Number

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources