November 28, 2022 at 5:05 pm
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
November 28, 2022 at 9:33 pm
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
November 28, 2022 at 10:48 pm
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
November 28, 2022 at 11:02 pm
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
Change is inevitable... Change for the better is not.
November 29, 2022 at 12:08 am
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
Change is inevitable... Change for the better is not.
November 29, 2022 at 10:57 pm
Are you all set now?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply