Pivot? - SQL Server Forums

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?

Author

Topic

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2011-07-30 : 09:30:06

Hello,
I have built a query that returns the number of appointments by each day in the month for a giving location. However, this query will only return the days that actually have appointments. So out of 31 days in a month the following days of the month will be selected 1,3,6,18,22.

I was looking into pivots and it seems that I can return a result set like this:


site month day appt total
A     1     2     4
A     1     4     8
A     1     6     1
B     1     3     8

and turn into this:


Site  1   2   3   4   5   6   7   8   9   10 .... 31 
A     4       5       3   2   1       3   5
B     4       2           2       1   3   5

Is this possible with this current query (see below)? Right now I am using server side programming to parse the result set in the above example. I would prefer to have SQL do this for me.

Thanks for any help


declare @futureDate datetime, @querydate datetime

--REPORT SHOW TODAYS DATE AND DATE IN 6 MONTHS FROM NOW
set @futureDate = dateadd(dd,1,dateAdd( mm, 6, getDate() ))
--set @queryDate = getdate()
set @queryDate = dateAdd( yyyy, -4, getdate() )

create table #temp( siteID int, siteName varchar(500), appointmentTotal int, DriveDate datetime )
insert into #temp(siteID, siteName, appointmentTotal,DriveDate )
	select 
		b.bloodbanksiteid,
		b.bloodbanksitename,
		count( a.apptdatetime ) as Appt_Total,
		dbo.fn_sod(o.opStartDateTime) as Drive_date
	from 
		appointment as a
	inner join 
		op as o on o.opid = a.opid
	inner join 
		bloodbanksite as b on b.bloodbanksiteid = o.bloodbanksiteid
	inner join
		donor as d on d.donorid = a.donorid	
	where 
		dbo.fn_sod(o.opStartDateTime) >= dbo.fn_sod(@queryDate)
	and 
	    dbo.fn_sod(o.opStartDateTime) < dbo.fn_sod(@futureDate)            
     group by 
		b.bloodbanksiteid,
		b.bloodbanksitename,
		dbo.fn_sod(o.opStartDateTime)

--2. REPORT SHOWS NUMBER OF APPOINTMENTS BY DATE 
--3. REPORT IS ORDERED BY FIXED LOCATION

select	
	siteID, siteName, appointmentTotal, driveDate, month(drivedate) as months , datename(d, driveDate) as dates
from 
	#temp

order by 
	
	months,
	siteName, 
	driveDate
	 

drop table #temp

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-07-30 : 10:15:11

Based on your sample data, I don't see how you would have data for 1,3,6,18 and 22; it seems like you will have data for 2,3,4, and 6. Regardless, it looks like there are two things need to do:

1. Get a row for each day for each site even when there are no appointments for a given day at a given site.

2. Pivot the data so they are displayed with the days as columns.

For the first part, it is best to use a calendar table. A calendar table would have all the days in your date range. You can do a left join to this calendar table to get rows for even those days in which you don't have any appointments.

For the second part - pivoting, if you are able to do it in the client code (for example, if you are using SQL Reporting Services to present the data, it is really easy to do generate this type of reports using dynamic column grouping). If you must do it in SQL, you can use dynamic pivoting.

quote:

Originally posted by jgonzalez14

Hello,
I have built a query that returns the number of appointments by each day in the month for a giving location. However, this query will only return the days that actually have appointments. So out of 31 days in a month the following days of the month will be selected 1,3,6,18,22.

I was looking into pivots and it seems that I can return a result set like this:


site month day appt total
A     1     2     4
A     1     4     8
A     1     6     1
B     1     3     8

and turn into this:


Site  1   2   3   4   5   6   7   8   9   10 .... 31 
A     4       5       3   2   1       3   5
B     4       2           2       1   3   5


declare @futureDate datetime, @querydate datetime

--REPORT SHOW TODAYS DATE AND DATE IN 6 MONTHS FROM NOW
set @futureDate = dateadd(dd,1,dateAdd( mm, 6, getDate() ))
--set @queryDate = getdate()
set @queryDate = dateAdd( yyyy, -4, getdate() )

create table #temp( siteID int, siteName varchar(500), appointmentTotal int, DriveDate datetime )
insert into #temp(siteID, siteName, appointmentTotal,DriveDate )
	select 
		b.bloodbanksiteid,
		b.bloodbanksitename,
		count( a.apptdatetime ) as Appt_Total,
		dbo.fn_sod(o.opStartDateTime) as Drive_date
	from 
		appointment as a
	inner join 
		op as o on o.opid = a.opid
	inner join 
		bloodbanksite as b on b.bloodbanksiteid = o.bloodbanksiteid
	inner join
		donor as d on d.donorid = a.donorid	
	where 
		dbo.fn_sod(o.opStartDateTime) >= dbo.fn_sod(@queryDate)
	and 
	    dbo.fn_sod(o.opStartDateTime) < dbo.fn_sod(@futureDate)            
     group by 
		b.bloodbanksiteid,
		b.bloodbanksitename,
		dbo.fn_sod(o.opStartDateTime)

--2. REPORT SHOWS NUMBER OF APPOINTMENTS BY DATE 
--3. REPORT IS ORDERED BY FIXED LOCATION

select	
	siteID, siteName, appointmentTotal, driveDate, month(drivedate) as months , datename(d, driveDate) as dates
from 
	#temp

order by 
	
	months,
	siteName, 
	driveDate
	 

drop table #temp

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources