query one table three for three different where variables

  • I am not sure I understand your question - I think you are asking if you wanted to get the data for the day prior to the last day of the month?  Or any number of days prior?

    If so - then you start from the end of the month and subtract that many days.  To make the code clear as to your intentions - use the appropriate functions.  Although it is perfectly fine subtracting days from a datetime data type - it doesn't work for the date/datetime2/datetimeoffset data types.

    DECLARE @last_day int = DATEADD(DAY, -1, EOMONTH(getdate(), -1))  -- One day prior to end of previous month

    So here - we get the end of the month using EOMONTH with the second parameter.  The second parameter allows us to add/subtract the number of months from the first parameter.  We then wrap that with a DATEADD to add the appropriate number of days before/after the value returned from the EOMONTH function.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Sorry Jeffrey, I was not very clear there.

    What I am looking it is for example today is the 22nd and lets say the boss wants to go up to the 21st.

    Thanks,

  • Hi Jeffrey,

    I think this is what I need to add/change

    Declare @last_day datetime = DATEADD(day, -1, convert(date, GETDATE())) 

    Is this correct?

  • Jeffrey, here is the complete script. This appears to be working;

    Note I added an additional Declare for @prev_date. Then changed  [End of Month] to [Yesterday] in the select statement.

    Does that all look correct to you?

     

    Thanks,

    Use tmdsDatabaseStatistics;

    Declare @current_date datetime = getdate(); -- Run for the current date
    Declare @prev_date datetime= DATEADD(DAY, -1, @current_date); -- Run for the current date
    Declare @last_day int = day(eomonth(@current_date)) -- Last day of the month for current date
    , @current_month int = month(@current_date)
    , @current_year int = year(@current_date);

    Select [CP Name] = ControlPointName
    , Codeline
    , Type = cl.Legacytype
    , [Sub Division] = SubName
    , [Control Failures] = sum(ControlFailCount)
    , [Control Point Down] = sum(ControlPointStatusDownCount)
    , Failures = FailureCount
    , [First of Month] = sum(Case When DAY = 1 Then FailureCount Else 0 End)
    , [Middle of Month] = sum(Case When DAY = 15 Then FailureCount Else 0 End)
    , [Yesterday] = sum(Case When DAY = @last_day Then FailureCount Else 0 END)
    From tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay sld WITH (NOLOCK)
    Inner Join tmdsDatabaseStatic.dbo.tblCodeLines cl On cl.CodelineNumber = sld.codeline
    Where ControlPointName NOT LIKE 'W[DM]%'
    And DAY In (1, 15, @prev_date)
    And month = @current_month
    And year = @current_year
    And FailureCount > '500'
    Group By
    ControlPointName
    , codeline
    , cl.LegacyType
    , SubName
    , FailureCount
    Order By
    Failures desc;
  • There are several ways to approach this - one way is to have separate scripts and you just pick the one you want.  So one will be current month to date and the other would be previous month.  The same code except you modify the calculation for current date and @last_day.

    Another option is to use a flag - and based on that flag you set @current_date and @last_day to the appropriate values.

    For example, here I am calculating month to date based on current day.  If the current day is greater than 16 then report month to date - prior to the 15th report previous month.  Now, if you want to be able to report month to date prior to the 15th - then additional changes *might* be needed because the 15th is hard-coded.

        Use tmdsDatabaseStatistics;

    --==== Use MTD after the 16th of the month up to the last day of the month
    Declare @previous_date date = iif(day(getdate() > 16, dateadd(day, -1, getdate()), eomonth(getdate(), -1));

    --==== Get report parameters
    Declare @last_day int = day(@previous_date)
    , @report_month int = month(@previous_date)
    , @report_year int = year(@previous_date);

    Select [CP Name] = ControlPointName
    , Codeline
    , Type = cl.Legacytype
    , [Sub Division] = SubName
    , [Control Failures] = sum(ControlFailCount)
    , [Control Point Down] = sum(ControlPointStatusDownCount)
    , [Report Month] = sld.[Month]
    , [Report Year] = sld.[Year]
    , Failures = FailureCount
    , [First of Month] = sum(Case When DAY = 1 Then FailureCount Else 0 End)
    , [Middle of Month] = sum(Case When DAY = 15 Then FailureCount Else 0 End)
    , [End of Month] = sum(Case When DAY = @last_day Then FailureCount Else 0 END)
    From tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay sld
    Inner Join tmdsDatabaseStatic.dbo.tblCodeLines cl On cl.CodelineNumber = sld.codeline
    Where ControlPointName NOT LIKE 'W[DM]%'
    And DAY In (1, 15, @last_day)
    And month = @report_month
    And year = @report_year
    And FailureCount > '500'
    Group By
    ControlPointName
    , codeline
    , cl.LegacyType
    , SubName
    , [Month]
    , [Year]
    Order By
    Failures desc;

    And I highly recommend that you update the columns in this query to use the table alias everywhere.  For example - is ControlPointName coming from table tblStatisticsLocationDay or for tblCodeLines?  Guessing it comes from the former - so it should be sld.ControlPointName everywhere in the query.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Thanks Jeffrey, that does  help a lot. I will modify the alias piece, just have not gotten to it  yet. Function before form... Yes sld.ControlPointName would be correct.

    I really appreciate the explanations with the code example. they are very helpful.

    I think that about covers all the things on my hit list. now jut clean it up and put into a stored procedure and call it a day.

    Have a great weekend all of you!

  • Thank you for the feedback - if converting to a stored procedure then you want to determine what parameter(s) to pass to the procedure.  It would depend on the usage though and how the procedure will be used - and my not need any parameters at all.

    You could pass into the procedure the current date - make it optional (null), and if null use the calculation in the code.  Or - you could calculate the last day from the calling code/report and have @last_day as the parameter.  Many options - but really depends on how it will be called.

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Great suggestions! Much appreciated.

Viewing 8 posts - 31 through 38 (of 38 total)

You must be logged in to reply to this topic. Login to reply