Prev: Textbox evaluates differently in PDF
Next: Reporting Hardware Specs for a 20TB Oracle DB
From: samcham on 3 May 2010 15:41
I'm using SSRS to write reports, and am very new to SQL queries.

I have a report that shows totals for each day in the month to-date, for two
data elements. It needs to run daily via an automatic snapshot, and be
distributed via e-mail subscription. As such, the time frame cannot be a
user input item.

The data is always a day behind, so on the first day of the month, it needs
to report the previous month, not the current month. The field I need to key
on is called DateTime.

My current query for the month of April 2010 is:

SELECT DateTime, Sum(PatientDays) AS 'PatientDays', Sum(Admit) AS 'Admits'
FROM AdmStatsLocation
WHERE DateTime > '03/31/2010' AND DateTime < '05/01/2010'
GROUP BY DateTime

Can anyone help with an example of how to write an SQL query that will run
the report for the current month, except that if it is the first of the
month, it will report on all days of the previous month?

Thanks, Sam.
From: Uri Dimant on 4 May 2010 12:36
select dateadd(month,datediff(month,0,getdate())-1,0)-1

SELECT DateTime, Sum(PatientDays) AS 'PatientDays', Sum(Admit) AS 'Admits'
FROM AdmStatsLocation
WHERE DateTime > dateadd(month,datediff(month,0,getdate())-1,0)-1

AND DateTime < dateadd(month,datediff(month,0,getdate()),0)

GROUP BY DateTime

"samcham" <samcham(a)discussions.microsoft.com> wrote in message
news:17F9019F-2055-42DD-BDB6-C91FC5D1B8C9(a)microsoft.com...
> I'm using SSRS to write reports, and am very new to SQL queries.
>
> I have a report that shows totals for each day in the month to-date, for
> two
> data elements. It needs to run daily via an automatic snapshot, and be
> distributed via e-mail subscription. As such, the time frame cannot be a
> user input item.
>
> The data is always a day behind, so on the first day of the month, it
> needs
> to report the previous month, not the current month. The field I need to
> key
> on is called DateTime.
>
> My current query for the month of April 2010 is:
>
> SELECT DateTime, Sum(PatientDays) AS 'PatientDays', Sum(Admit) AS 'Admits'
> FROM AdmStatsLocation
> WHERE DateTime > '03/31/2010' AND DateTime < '05/01/2010'
> GROUP BY DateTime
>
> Can anyone help with an example of how to write an SQL query that will run
> the report for the current month, except that if it is the first of the
> month, it will report on all days of the previous month?
>
> Thanks, Sam.


 | 
Pages: 1
Prev: Textbox evaluates differently in PDF
Next: Reporting Hardware Specs for a 20TB Oracle DB