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 |