Post reply

Query for Monthly Report - Different on 1st Day of Month

  • May 3, 2010 at 12:22 pm

    #218360

    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

    #1161448

    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

    #1161498

    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