Query all days in month with data range? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Query all days in month with data range?

Author

Topic

janetb
Yak Posting Veteran

71 Posts

Posted - 2013-09-18 : 08:29:58

0down votefavorite

I have a date range starting 7/1/2012 and ending 8/31/2012. For each person, I have an keyfield, admitDate, dischargeDate (tableA). I need a count of people for every day that the admit timeframe is between the date range, null=0. Any help appreciated. I'm assuming I can use something like below to get the date range, but then how do I get the count from to tableA using another date range?

declare @date_from datetime, @date_to datetime
set @date_from = '7/01/2012'
set @date_to = '8/31/2012'
;with dates as( select @date_from as dt union all select DATEADD(d,1,dt) from dates where dt<=@date_to) select d.dt from dates d

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-18 : 10:16:22

If your interval is less then 2048 days, you can use this

DECLARE	@DateFrom DATETIME = '20120701',
	@DateTo DATETIME = '20120831';

-- SwePeso
SELECT		DATEADD(DAY, v.Number, @DateFrom) AS theDate,
		COUNT(t.DateColumn) AS theCount
FROM		master.dbo.spt_values AS v
LEFT JOIN	dbo.TableA AS t ON t.DateColumn = DATEADD(DAY, v.Number, @DateFrom)
WHERE		v.Type = 'P'
		AND v.Number BETWEEN 0 AND DATEDIFF(DAY, @FromDate, @ToDate)
GROUP BY	DATEADD(DAY, v.Number, @DateFrom);

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

janetb
Yak Posting Veteran

71 Posts

Posted - 2013-09-18 : 10:32:41

Love the approach but it won't work for my needs. TableA doesn't have one date to compare - I only have date ranges, e.g., dateAdmit and dischargeDate. I need to count 1 for each day between dateAdmit and dischargeDate for each patient.

Make sense now?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-18 : 10:47:01

Much more!

DECLARE	@DateFrom DATETIME = '20120701',
	@DateTo DATETIME = '20120831';

-- SwePeso
SELECT		t.PatientID,
		DATEADD(DAY, v.Number, t.dateAdmit) AS theDate,
		COUNT(*) AS theCount
FROM		dbo.TableA AS t
INNER JOIN	master.dbo.spt_values AS v ON v.Type = 'P'
			AND v.Number BETWEEN 0 AND DATEDIFF(DAY, t.dateAdmit, t.dischargeDate)
GROUP BY	t.PatientID,
		DATEADD(DAY, v.Number, t.dateAdmit);

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-18 : 10:49:05

quote:
Originally posted by janetb

Love the approach but it won't work for my needs. TableA doesn't have one date to compare - I only have date ranges, e.g., dateAdmit and dischargeDate. I need to count 1 for each day between dateAdmit and dischargeDate for each patient.

Make sense now?

Are you trying to generate a result set that lists the number of patients who are in the hospital on each day? i.e., a result set with date in one column and number of patients who are in the hospital in the second column?

(Hospital, or school, or hotel, or whatever).

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-18 : 10:54:21

[code]SELECT PatientID,
SUM(1 + DATEDIFF(DAY, dateAdmit, dischargeDate)) AS theCount
FROM dbo.TableA
GROUP BY PatientID;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

janetb
Yak Posting Veteran

71 Posts

Posted - 2013-09-18 : 13:02:56

This was it with minimal changes -- SwePeso -- thanks so much!

DECLARE @DateFrom DATETIME = '20120701', @DateTo DATETIME = '20120831'
SELECT DATEADD(DAY, v.Number, t.admitDate) AS theDate, COUNT(*) AS theCount
FROM dbo.tableA AS t
INNER JOIN master.dbo.spt_values AS v ON v.Type = 'P'
AND v.Number BETWEEN 0 AND DATEDIFF(DAY, t.admitDate, t.dischargeD)
where DATEADD(DAY, v.Number, t.admitDate) >= @DateFrom and DATEADD(DAY, v.Number, t.admitDate)<= @DateTo
GROUP BY DATEADD(DAY, v.Number, t.admitDate) order by DATEADD(DAY, v.Number, t.admitDate);

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources