Pivot table that counts values from case output

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 2008 Forums

Transact-SQL (2008)

Pivot table that counts values from case output

Author

Topic

joebuzz83
Starting Member

16 Posts

Posted - 2012-01-03 : 15:37:17

Hello everyone,

I'm having a difficult time trying to get a report to function properlly.

The report shows the user which patients came to our medical centers and if not assigns a status of NoShow or Cancelled to them.

The status of Kept, Cancelled, Pending, No Show and Expected all have been assigned via Case..When..Then functions. Here is the code that assigns the status.

(Select (case when a.appt_kept_ind='Y' then 'Kept'
when (a.appt_kept_ind ='' or a.appt_kept_ind <> 'Y') and a.cancel_ind='N' and a.appt_date < CONVERT(char(8), GETDATE(),112) then 'No Show'
when a.appt_status='P' then 'Pending'
when a.cancel_ind='Y' then 'Cancelled'
when a.appt_kept_ind='N' and a.cancel_ind='N' and a.appt_date >= CONVERT(char(8), GETDATE(),112) then 'Expected'
end) as 'Appointment Status'
--, a.workflow_status, psm.description, p.last_name
from person p
join patient_status ps on p.person_id=ps.person_id
join patient_status_mstr psm on psm.patient_status_id=ps.patient_status_id
right join appointments a on p.person_id=a.person_id)

That returns a single column with a staus for 8000+ records.

I need to then take that data and assign the count of each status to a pivot table that shows the DATENAME(Month, ...) of the appointment.

I need the Report to look something like this:

---------------January-----Feb-----March
Kept-----------10----------20------3
Cancelled------2-----------9-------42
No Show--------Null--------5-------7

Could someone point me in the right direction? I was thinking of assigning the values from the above script into a variable, but I'm having trouble doing so.

Please help! Thanks.

Joe

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2012-01-03 : 16:03:31

Here is an example I found that might help you. I wish I could cite the author. Hope it helps.



/**
*  Semi Flexible Pivot Example
**/

DECLARE	@currentYear int
DECLARE	@yearsBack int
DECLARE	@myData TABLE (myID int, myDate datetime, myVal int)
DECLARE @counter int
SET	@currentYear = YEAR(GETDATE())

SET	@counter = 10
WHILE	@counter > -1
BEGIN
	INSERT	@myData
	SELECT	1, '01 Jan ' + CAST(YEAR(GETDATE()) - @counter AS VARCHAR), YEAR(GETDATE()) - @counter + 10000
	UNION
	SELECT	2, '01 Jan ' + CAST(YEAR(GETDATE()) - @counter AS VARCHAR), YEAR(GETDATE()) - @counter + 20000
	UNION
	SELECT	1, '01 Feb ' + CAST(YEAR(GETDATE()) - @counter AS VARCHAR), YEAR(GETDATE()) - @counter + 10000
	UNION
	SELECT	2, '01 Feb ' + CAST(YEAR(GETDATE()) - @counter AS VARCHAR), YEAR(GETDATE()) - @counter + 20000
	SET	@counter = @counter - 1
END

select * from @myData;

SELECT	myID, [10],[9],,[7],[6],[5],[4],[3],[2],[1]
FROM	(
			SELECT	myID,
					myVal,
					NTILE(10) OVER(PARTITION BY MyID ORDER BY YEAR(myDate) DESC) AS yearOffset
			FROM	@myData
		) ma
		PIVOT
		(
			sum(MyVal) FOR [yearOffset] IN ([10],[9],,[7],[6],[5],[4],[3],[2],[1])
		) p

joebuzz83
Starting Member

16 Posts

Posted - 2012-01-03 : 16:55:48

This is a step in the right direction for sure, but my situation is a bit more complex. I basically need to get this query to be the first column, but as distinct values:
(Select (case when a.appt_kept_ind='Y' then 'Kept'
when (a.appt_kept_ind ='' or a.appt_kept_ind <> 'Y') and a.cancel_ind='N' and a.appt_date < CONVERT(char(8), GETDATE(),112) then 'No Show'
when a.appt_status='P' then 'Pending'
when a.cancel_ind='Y' then 'Cancelled'
when a.appt_kept_ind='N' and a.cancel_ind='N' and a.appt_date >= CONVERT(char(8), GETDATE(),112) then 'Expected'
end) as 'Appointment Status'
--, a.workflow_status, psm.description, p.last_name
from person p
join patient_status ps on p.person_id=ps.person_id
join patient_status_mstr psm on psm.patient_status_id=ps.patient_status_id
right join appointments a on p.person_id=a.person_id)

Then ill need to count each record and put that into a columnh for each month.

I understand that this example creates some variables and tosses some data into a temp table, but it's pretty confusing to me. Could you perhaps help me a bit more?

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2012-01-03 : 17:03:02

Just put your query along with the expression you want to count in the derived table section of the PIVOT query I provided. Think of your expression as replacing "MyID" in my example. You'll have to change the PIVOT expression to be a COUNT and change YearOffset to be Month based, but this type of pattern should work for you.

joebuzz83
Starting Member

16 Posts

Posted - 2012-01-06 : 11:50:27

Thanks! I was able to get the pivot table to work using this logic.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources