May 3, 2010 at 12:22 pm
I'm very new to SQL queries, so please forgive the newbie question.
I have a report that shows daily totals 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.
My data is always a day behind, so on the first day of the month, it needs to report on all days of the previous month.
Here is my existing query, designed for the month of April 2010:
SELECT DateTime, Sum(Days) AS 'Days', Sum(Admit) AS 'Admits'
FROM DataLocation
WHERE DateTime > '03/31/2010' AND DateTime < '05/01/2010'
GROUP BY DateTime
Can anyone help me come up with a query that will report for each day of the current month, unless it is running on the 1st day of the month, in which case it needs to run for every day of the preceeding month?
Thanks!
May 3, 2010 at 12:39 pm
where
DateTime >=
-- First Day of Month for yesterdays date
dateadd(mm,datediff(mm,0,dateadd(dd,-1,getdate())),0)and
DateTime <
-- First Day of Next Month for yesterdays date
dateadd(mm,datediff(mm,0,dateadd(dd,-1,getdate()))+1,0)
May 3, 2010 at 2:21 pm
Thanks. I'll give that a try.
Is there another way to accomplish the same goal using expressions, or does it need to be in the query?
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply