Number of records for Month X, created between 7AM and Noon

13 Jul 2005 8:30 PM

Patrick Rouse

I have a table "patient_encounter" that stores a DateTime value for each
record when it is created as "enc_timestamp". How can I determine how many
records exist that were created between times X and Y for a given month? I
want to see how many records were created for each month, for days Monday -
Friday, from 6AM-12PM, and from 12PM to 6PM.

Thanks.

--
Patrick Rouse
Microsoft MVP - Terminal Server
http://www.workthin.com

13 Jul 2005 8:41 PM

Aaron Bertrand [SQL Server MVP]

DECLARE @sd SMALLDATETIME
SET @sd = '20050601' -- first day of month you want to track
-- I assume this will be a parameter to a stored procedure

SELECT COUNT(*)
FROM Patient_Encounter
    WHERE enc_timestamp >= @sd
    AND enc_timestamp < DATEADD(MONTH, 1, @sd)
    -- assumes "normal" DATEFIRST, e.g. Sunday = 1
    AND DATEPART(DW, enc_timestamp) BETWEEN 2 AND 6
    AND DATEPART(HOUR, enc_timestamp) >= 6
    AND DATEPART(HOUR, enc_timestamp) < 12

Note that the indexes are pretty much thrown out the window here. If this
is a large table there are probably some workarounds that will yield
marginally better performance...

Show quote

"Patrick Rouse" <PatrickRo***@discussions.microsoft.com> wrote in message
news:00208702-50DD-405C-AD59-3930942B0C56@microsoft.com...
>I have a table "patient_encounter" that stores a DateTime value for each
> record when it is created as "enc_timestamp". How can I determine how
> many
> records exist that were created between times X and Y for a given month?
> I
> want to see how many records were created for each month, for days
> Monday -
> Friday, from 6AM-12PM, and from 12PM to 6PM.
>
> Thanks.
>
> --
> Patrick Rouse
> Microsoft MVP - Terminal Server
> http://www.workthin.com

13 Jul 2005 8:49 PM

Patrick Rouse

Thanks.

--
Patrick Rouse
Microsoft MVP - Terminal Server
http://www.workthin.com

Show quote

"Aaron Bertrand [SQL Server MVP]" wrote:

> DECLARE @sd SMALLDATETIME
> SET @sd = '20050601' -- first day of month you want to track
> -- I assume this will be a parameter to a stored procedure
>
>
> SELECT COUNT(*)
> FROM Patient_Encounter
>     WHERE enc_timestamp >= @sd
>     AND enc_timestamp < DATEADD(MONTH, 1, @sd)
>     -- assumes "normal" DATEFIRST, e.g. Sunday = 1
>     AND DATEPART(DW, enc_timestamp) BETWEEN 2 AND 6
>     AND DATEPART(HOUR, enc_timestamp) >= 6
>     AND DATEPART(HOUR, enc_timestamp) < 12
>
> Note that the indexes are pretty much thrown out the window here. If this
> is a large table there are probably some workarounds that will yield
> marginally better performance...
>
>
>
> "Patrick Rouse" <PatrickRo***@discussions.microsoft.com> wrote in message
> news:00208702-50DD-405C-AD59-3930942B0C56@microsoft.com...
> >I have a table "patient_encounter" that stores a DateTime value for each
> > record when it is created as "enc_timestamp". How can I determine how
> > many
> > records exist that were created between times X and Y for a given month?
> > I
> > want to see how many records were created for each month, for days
> > Monday -
> > Friday, from 6AM-12PM, and from 12PM to 6PM.
> >
> > Thanks.
> >
> > --
> > Patrick Rouse
> > Microsoft MVP - Terminal Server
> > http://www.workthin.com
>
>
>

13 Jul 2005 8:55 PM

Alejandro Mesa

Try,

select
    datename(month, enc_timestamp) as month_name,
    sum(case when datename(weekday, enc_timestamp) in ('Monday', 'Tuesday',
'Wednesday', 'Thursday', 'Friday') and enc_timestamp >= convert(varchar(11),
enc_timestamp, 126) + '06:00:00' and enc_timestamp < convert(varchar(11),
enc_timestamp, 126) + '12:00:00' then 1 else 0 end) as 6AM_12PM,
    sum(case when datename(weekday, enc_timestamp) in ('Monday', 'Tuesday',
'Wednesday', 'Thursday', 'Friday') and enc_timestamp >= convert(varchar(11),
enc_timestamp, 126) + '12:00:00' and enc_timestamp < convert(varchar(11),
enc_timestamp, 126) + '18:00:00' then 1 else 0 end) as 12PM_6PM
from
    patient_encounter
group by
    datename(month, enc_timestamp)

AMB

Show quote

"Patrick Rouse" wrote:

> I have a table "patient_encounter" that stores a DateTime value for each
> record when it is created as "enc_timestamp". How can I determine how many
> records exist that were created between times X and Y for a given month? I
> want to see how many records were created for each month, for days Monday -
> Friday, from 6AM-12PM, and from 12PM to 6PM.
>
> Thanks.
>
> --
> Patrick Rouse
> Microsoft MVP - Terminal Server
> http://www.workthin.com

13 Jul 2005 8:58 PM

Patrick Rouse

This is what I did:

DECLARE @sd SMALLDATETIME
SET @sd = '20050301' -- first day of month you want to track
-- I assume this will be a parameter to a stored procedure

SELECT COUNT(*) AS [Monday AM]
FROM vwApptList
    WHERE enc_timestamp >= @sd
    AND enc_timestamp < DATEADD(MONTH, 1, @sd)
    -- assumes "normal" DATEFIRST, e.g. Sunday = 1
    --AND DATEPART(DW, enc_timestamp) BETWEEN 2 AND 6
    AND DATEPART(DW, enc_timestamp) =2
    AND DATEPART(HOUR, enc_timestamp) >= 5
    AND DATEPART(HOUR, enc_timestamp) < 12
    AND mstr_list_item_desc ='New Patient'

SELECT COUNT(*) AS [Monday PM]
FROM vwApptList
    WHERE enc_timestamp >= @sd
    AND enc_timestamp < DATEADD(MONTH, 1, @sd)
    -- assumes "normal" DATEFIRST, e.g. Sunday = 1
    --AND DATEPART(DW, enc_timestamp) BETWEEN 2 AND 6
    AND DATEPART(DW, enc_timestamp) =2
    AND DATEPART(HOUR, enc_timestamp) >= 12
    AND DATEPART(HOUR, enc_timestamp) < 24
    AND mstr_list_item_desc ='New Patient'

I'll do this for each day of the week. I'm happy now.

--
Patrick Rouse
Microsoft MVP - Terminal Server
http://www.workthin.com

Show quote

"Alejandro Mesa" wrote:

> Try,
>
> select
>     datename(month, enc_timestamp) as month_name,
>     sum(case when datename(weekday, enc_timestamp) in ('Monday', 'Tuesday',
> 'Wednesday', 'Thursday', 'Friday') and enc_timestamp >= convert(varchar(11),
> enc_timestamp, 126) + '06:00:00' and enc_timestamp < convert(varchar(11),
> enc_timestamp, 126) + '12:00:00' then 1 else 0 end) as 6AM_12PM,
>     sum(case when datename(weekday, enc_timestamp) in ('Monday', 'Tuesday',
> 'Wednesday', 'Thursday', 'Friday') and enc_timestamp >= convert(varchar(11),
> enc_timestamp, 126) + '12:00:00' and enc_timestamp < convert(varchar(11),
> enc_timestamp, 126) + '18:00:00' then 1 else 0 end) as 12PM_6PM
> from
>     patient_encounter
> group by
>     datename(month, enc_timestamp)
>
>
> AMB
>
> "Patrick Rouse" wrote:
>
> > I have a table "patient_encounter" that stores a DateTime value for each
> > record when it is created as "enc_timestamp". How can I determine how many
> > records exist that were created between times X and Y for a given month? I
> > want to see how many records were created for each month, for days Monday -
> > Friday, from 6AM-12PM, and from 12PM to 6PM.
> >
> > Thanks.
> >
> > --
> > Patrick Rouse
> > Microsoft MVP - Terminal Server
> > http://www.workthin.com

13 Jul 2005 9:13 PM

Aaron Bertrand [SQL Server MVP]

> I'll do this for each day of the week. I'm happy now.

Why, when you can get it all in one result? This is one way, there are
others that will allow you to perform the aggregate inside the derived table
as an alternative...

SELECT
DATENAME(DW, dw)
+ CASE WHEN h < 12
THEN ' AM'
ELSE ' PM'
END,
dw,
COUNT(*)
FROM
(
SELECT
   dw = DATEPART(DW, enc_timestamp),
   h = DATEPART(HOUR, enc_timestamp)
FROM
   vwApptList
WHERE
   enc_timestamp >= @sd
   AND enc_timestamp < DATEADD(MONTH, 1, @sd)
   AND DATEPART(DW, enc_timestamp) BETWEEN 2 AND 6
   AND DATEPART(HOUR, enc_timestamp) >= 5
   AND mstr_list_item_desc ='New Patient'
) x
GROUP BY
DATENAME(DW, dw)
+ CASE WHEN h < 12
THEN ' AM'
ELSE ' PM'
END,
dw
ORDER BY
dw

13 Jul 2005 9:42 PM

Steve Kass

Patrick,

I believe Alejandro's suggestion will work best. The query must process
most of the rows of the table, and his suggestion will do it in one stream
aggregate without wasting the query optimizer's time. Here's a
variation that may (or may not) be more readable or manageable.

select
dateadd(month,SerialMonth,0) as month_beginning,
sum(IsWeekday*IsMorning) as MTWTF_6AM_12PM,
sum(IsWeekday*IsAfternoon) as MTWTF_12AM_6PM
from (
select
    SerialMonth,
    case daysSinceMonday % 7
      when 5 then 0
      when 6 then 0
    else 1 end as IsWeekday,
    case hoursSinceMidnight / 6 % 4
      when 1 then 1
    else 0 end as IsMorning,
    case hoursSince6AM / 6 % 4
      when 2 then 1
    else 0 end as IsAfternoon,
from (
    select
      dateadd(month,datediff(month,0,enc_timestamp),0) as SerialMonth,
      datediff(day,0,enc_timestamp) as daysSinceMonday,
      datediff(hour,0,enc_timestamp) - 6 as hoursSinceMidnight
    from patient_encounter
) as E
) as E
group by dateadd(month,SerialMonth,0)

Steve Kass
Drew University

Patrick Rouse wrote:

Show quote

>I have a table "patient_encounter" that stores a DateTime value for each
>record when it is created as "enc_timestamp". How can I determine how many
>records exist that were created between times X and Y for a given month? I
>want to see how many records were created for each month, for days Monday -
>Friday, from 6AM-12PM, and from 12PM to 6PM.
>
>Thanks.
>
>
>

13 Jul 2005 10:47 PM

--CELKO--

>> I want to see how many records were created for each month, for days Monday - Friday, from 6AM-12PM, and from 12PM to 6PM. <<

Build a report period table and uses a BETWEEN predicate:

CREATE TABLE ReportPeriods
(report_period_name CHAR(7) NOT NULL, -- 'Jul-Mon', etc.
start_time DATETIME NOT NULL,
end_time DATETIME NOT NULL,
CHECK (start_time < end_time),
PRIMARY KEY (start_time < end_time));

Now the query is simple, portable, adjustable and avoids complex, slow
temporal math. You can fill the table with a spreadsheet in about two
minutes.

SELECT R.report_name, COUNT(P.enc_timestamp)
FROM Patient_Encounter AS P, ReportPeriods AS R
WHERE R.enc_timestamp BETWEEN R.start_time AND R.end_time
GROUP BY R.report_name;