Get Date Monday Show results for weekend and then else 24 hours

  • Hi,

     

    I have a SQL query below in which i need to be able to see all results for TimeGenerated - Monday and 48 hours prior (the weekend) and then else - just 24 hours (for the rest of the week). This is what i have so far which just shows 24 hours. Any help appreciated.

     

    Select Distinct Top 1000000 tblAssets.AssetName,

    Count(tblNtlog.TimeGenerated) As Instances,

    tblNtlog.TimeGenerated,

    tblNtlogSource.Sourcename,

    tblNtlog.Eventcode

    From tblAssets

    Inner Join tblNtlog On tblAssets.AssetID = tblNtlog.AssetID

    Inner Join tblNtlogSource On tblNtlogSource.SourcenameID =

    tblNtlog.SourcenameID

    Inner Join tblNtlogMessage On tblNtlogMessage.MessageID = tblNtlog.MessageID

    Inner Join lansweeperdb.dbo.tblAssetCustom On tblAssets.AssetID =

    tblAssetCustom.AssetID

    Where tblNtlog.TimeGenerated > GetDate() - 1 And tblNtlogSource.Sourcename =

    'Microsoft-Windows-Kernel-Power' And tblNtlog.Eventcode = 41 And

    (tblAssetCustom.Model Like 'Virtual%' Or tblAssetCustom.Model Like

    'PowerEdge%')

    Group By tblAssets.AssetName,

    tblNtlog.TimeGenerated,

    tblAssets.Assettype,

    tblAssetCustom.Model,

    tblNtlogSource.Sourcename,

    tblNtlog.Eventcode

  • To generate sample data you could use a Tally table and calculate a datetime column.  This generates 400 rows and calculates a column 'sample_dt' which starts with the current datetime and increases in 1 hour increments.

    CREATE FUNCTION [dbo].[fnTally]
    /**********************************************************************************************************************
    Jeff Moden Script on SSC: https://www.sqlservercentral.com/scripts/create-a-tally-function-fntally
    **********************************************************************************************************************/ (@ZeroOrOne BIT, @MaxN BIGINT)
    RETURNS TABLE WITH SCHEMABINDING AS
    RETURN WITH
    H2(N) AS ( SELECT 1
    FROM (VALUES
    (1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    ,(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)
    )V(N)) --16^2 or 256 rows
    , H4(N) AS (SELECT 1 FROM H2 a, H2 b) --16^4 or 65,536 rows
    , H8(N) AS (SELECT 1 FROM H4 a, H4 b) --16^8 or 4,294,967,296 rows
    SELECT N = 0 WHERE @ZeroOrOne = 0 UNION ALL
    SELECT TOP(@MaxN)
    N = ROW_NUMBER() OVER (ORDER BY N)
    FROM H8
    ;
    GO
    select dateadd(hour, fn.n, getdate()) sample_dt
    from dbo.fnTally(1, 400) fn;

    It could be useful to run this code

    select datepart(weekday, getdate()+nums.n) day_of_week_number, 
    datename(weekday, getdate()+nums.n) day_of_week
    from (values (0),(1),(2),(3),(4),(5),(6)) nums(n)
    order by datepart(weekday, getdate()+nums.n);

    Combined you could try something like this

    select calc.wk_day_num, datename(weekday, calc.wk_day_num-2) wk_day_name,
    count(*) day_count
    from dbo.fnTally(1, 400) fn
    cross apply (values (dateadd(hour, fn.n, getdate()))) v(sample_dt)
    cross apply (values (case when datepart(weekday, v.sample_dt) in(1, 2, 7) then 2
    else datepart(weekday, v.sample_dt) end)) calc(wk_day_num)
    group by calc.wk_day_num, datename(weekday, calc.wk_day_num-2)
    order by calc.wk_day_num;

     

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • There are a couple of issues with your query - the first is that you have both DISTINCT and GROUP BY.  Since you are using aggregate functions there is no need for DISTINCT - the grouping will return distinct groups of data.

    The second issue is using TOP without an ORDER BY.  If you don't specify an order - then SQL Server can return the data in any order it chooses and then take the top rows.  It may not be needed either - since I am sure you don't really want one million rows returned.

    Other concerns - if TimeGenerated includes the time then using that column in the group by as-is doesn't make sense.  In fact, you probably will not get any other values to be grouped since they would all have different times.

    For your actual question - you need to know what GETDATE() returns.  This returns the date and time when the code is executed - and GETDATE() - 1 returns the previous day at the same time.  What you probably want is the full previous day - or the full Sat/Sun.  The easiest method is to use some variables:

    DECLARE @startDate datetime = cast(getdate() - 1 As date)  -- Yesterday at 00:00:00.000
    , @endDate datetime = cast(getdate() As date); -- Today at 00:00:00.000

    IF datepart(weekday, getdate()) = 2 -- Monday
    SET @startDate = cast(getdate() - 3 As date); -- Reset to Saturday at 00:00:00.000

    Now - using the variables in your query:

    Where tblNtlog.TimeGenerated >= @startDate
    And tblNtlog.TimeGenerated < @endDate --up to, but not including today at 00:00:00.000

    You could calculate the start date in a single statement and not use variables:

    Where tblNtlog.TimeGenerated >= cast(getdate() - iif(datepart(weekday, getdate()) = 2, 3, 1) As date)
    And tblNtlog.TimeGenerated < cast(getdate() As date) --up to, but not including today at 00:00:00.000

    And for the TimeGenerated - you could modify your query as follows:

    SELECT ...
    , DateGenerated = cast(TimeGenerated As date)
    , ...
    FROM ...
    GROUP BY
    ...
    , cast(TimeGenerated As date)
    ...

    This will group everything to the same 'day' - which will either be yesterday for all groups or sat/sun for the weekend.  If you want to break it out further, for example - into every hour you can use DATEADD(hour, DATEDIFF(hour, 0, TimeGenerated), 0) which will truncate the time to the hour.

    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

  • DMcMorris@welcom.co.uk wrote:

    Monday and 48 hours prior (the weekend) and then else - just 24 hours (for the rest of the week).

    Do you really mean 48 and 24 hours in your example or do you really mean that you want the full days of the weekend starting at midnight in the former case and midnight of the previous day in the latter case?

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • I try to avoid the use of things like the Week and WeekDay date parts for multiple reasons that would take too long to go into here.

    Based on what you posted as a WHERE clause, the following will work for the date criteria based on Mondays and then other days....

      WHERE tblNtlog.TimeGenerated > DATEADD(dd,IIF(DATEDIFF(dd,0,GETDATE())%7=0,-2,-1),GETDATE())
    AND ... the rest of your code

    The "0" is the underlying DateSerial# for the 1st of January, 1900, which is a Monday.  The code simply counts the number of since then, divides by 7, and checks if the remainder (formed by the Modulus (%) operator) =0.  If it is, then GETDATE() is on a Monday.  If not, it's some other day of the week.  The rest is pretty obvious.

    If you really want to stick with the direct date math thing, the following will also work...

      WHERE tblNtlog.TimeGenerated > GETDATE()-IIF(DATEDIFF(dd,0,GETDATE())%7=0,2,1)
    AND ... the rest of your code

    If you really mean, as previously said, that you want "Whole Days", post back.

    As for the rest of the query, the use of the DISTINCT keyword raises all sorts of Red Flags for me whether used in conjunction with a GROUP BY or not.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • @dmcmorriswelcom-co-uk ,

    Are you all set now?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Dear Lord... I'm a production DBA. Please grant me patience because, if you grant me strength, I'm gonna need bail money to go with it.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

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