get count from each day with in a span time

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)

get count from each day with in a span time

Author

Topic

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2011-07-29 : 10:48:59

Hi,

I am trying to select the count of appointments from each day between now and 6 months from now. I would also like to have these grouped by sites. Each site can has appointments each day and needs to have a count of these appointments. I have tried looping through each site then looping through each day and grabbing a the count of appointments from that day. then insert this into a temp table. However this is not working at all :(. Any help would be awesome


declare @futureDate datetime, @totalDays int, @index int, @querydate datetime, @fixedSiteID int, @fixedSiteName varchar
set @futureDate = dateAdd( mm, 6, getDate() )
set @totalDays = cast( datediff( day, getdate(), @futureDate) as integer )
set @index = 1
set @queryDate = getdate()



create table #jtmgTable(
	siteName varchar(500),
	appointmentTotal int,
	DriveDate datetime,
	
)



--declare the cursor when looping over each fixed site set the site id and the site name
declare fixedsite_cursor cursor for 
--select the items from bloodbank
select 
	distinct b.bloodbanksiteid, b.bloodbanksiteid
from 
	bloodbanksite as b
order by 
	b.bloodbanksiteid
	
--start the cursor
open fixedsite_cursor
fetch next from fixedsite_cursor
into @fixedSiteID, @fixedSiteName
while @@FETCH_STATUS = 0

begin


	while (@index <= @totalDays)
	begin
		BEGIN
			insert into #jtmgTable(siteName, appointmentTotal,DriveDate )
			select 
				@fixedSiteName,
				count( a.apptdatetime ) as Appt_Total,
				dbo.fn_sod(@queryDate) 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 
				o.bloodbanksiteid = @fixedSiteID
			and
				dbo.fn_sod( o.opStartDateTime) = dbo.fn_sod(@queryDate)
		
			
			set @queryDate = dateAdd( d, 1, @queryDate)
			set @index = @index + 1
		END
	END

	

	fetch next from fixedsite_cursor
	into @fixedSiteID
end
close fixedsite_cursor
deallocate 	fixedsite_cursor


select siteName
	 
from
	#jtmgTable
order by 
	siteName

drop table #jtmgTable

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2011-07-29 : 11:08:05

Ok this seemed to give me the the results I needed but I am sure there is a better way. This gives me the site name, the date of the drive, and the number of appts for that date.


declare @futureDate datetime, @totalDays int, @index int, @querydate datetime, @fixedSiteID int, @fixedSiteName varchar
--set @futureDate = dateAdd( mm, 6, getDate() )
set @futureDate = dateAdd( dd, 6, getDate() )
set @totalDays = cast( datediff( day, getdate(), @futureDate) as integer )
set @index = 1
--set @queryDate = getDate()
set @queryDate = dateAdd( yyyy, -4, getdate() )

--drop table #jtmgTable

create table #jtmgTable( drive_date datetime, bloodbankID int,  appt_count int, siteName varchar(500) )


	while (@index <= @totalDays)
	begin
		BEGIN
			insert into #jtmgTable( drive_date, bloodbankID, appt_count, sitename )
			select 
				isNull( dbo.fn_sod(@queryDate), null) as Drive_date,
				b.bloodbanksiteid ,
				isNull( 
					( 
						select 
							count(a.apptdatetime) 
						from appointment as a 
							inner join  op as o on o.opid = a.opid 
						inner join 
							bloodbanksite as bt on bt.bloodbanksiteid = o.bloodbanksiteid
						where
							bt.bloodbanksiteID = b.bloodbanksiteid			
						and						
							dbo.fn_sod( o.opStartDateTime) = dbo.fn_sod(@queryDate) ), 0 
					) 
				as test,
				
				b.bloodbanksitename
			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)
		
			group by o.opStartDateTime, b.bloodbanksitename, b.bloodbanksiteid
			order by b.bloodbanksitename
			
			set @queryDate = dateAdd( d, 1, @queryDate)
			set @index = @index + 1
		END
	END



select 
	siteName, appt_count, drive_date  
from 
	#jtmgTable
order by 
	siteName, drive_date
	
drop table #jtmgTable

jgonzalez14
Yak Posting Veteran

73 Posts

Posted - 2011-07-29 : 11:23:04

I am not fimilar with PIVOTS but would this be a good situation to use one?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-29 : 11:49:38

i think below is sufficient. no need of cursor logic


declare @futureDate datetime, @querydate datetime
set @futureDate = dateadd(dd,1,dateAdd( mm, 6, getDate() ))
set @queryDate = getdate()

insert into #jtmgTable(siteName, appointmentTotal,DriveDate )
			select 
				b.bloodbanksiteid,
				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,dbo.fn_sod(o.opStartDateTime)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources