Get count from previous month
-
I want to wrtie query where get count from previous month if match with key.
This is data we have
Output
1 record has match is 2 because it has in 2 subsequent previous month data was there with mainhost
2 record has match is 1 because it has in 1 subsequent previous month data was there with mainhost
3 record has match is 0 because it has in 0 subsequent previous month data was there with mainhost
4 record has match is 1 because it has in 1 subsequent previous month data was there with mainhost
5 record has match is 0 because it has in 0 subsequent previous month data was there with mainhost
6 record has match is 0 because it has in 0 subsequent previous month data was there with mainhost
Here is table definition
create table test_data ( id int , mainhost int, enter_date date )
insert into test_data Values ( 1 , 500 , '20231231') , ( 2 , 501 , '20231230') ,
( 3 , 500 , '20231130') , ( 4 , 500 , '20231031') , ( 5 , 501 , '20231130') ,
( 6 , 501 , '20230928')
select * from test_data
order by mainhost, enter_date desc
-
Use LAG or LEAD analytical functions.
https://learn.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver16
=======================================================================
-
Can you please confirm this statement:
1 record has match is 2 because it has in 2 subsequent previous month data was there with mainhost
You are referring to the 2023-12-31 row, with mainhost 500.
But there is only one row in 2023-11, with mainhost = 500. So why is match 2 and not 1?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
WITH MonthCheck
AS
(
SELECT mainhost, enter_date
,IIF(MONTH(enter_date) - 1
= LEAD(MONTH(enter_date)) OVER (PARTITION BY mainhost ORDER BY enter_date DESC)
, 1, 0) AS IsPrevMonth
FROM test_data
)
,Grps
AS
(
SELECT mainhost, enter_date, IsPrevMonth
,SUM(IsPrevMonth ^ 1) OVER (PARTITION BY mainhost ORDER BY enter_date DESC) AS Grp
FROM MonthCheck
)
SELECT mainhost, enter_date
,IIF(IsPrevMonth = 0, 0
,SUM(IsPrevMonth) OVER (PARTITION BY mainhost, Grp ORDER BY enter_date)
) AS [Match]
FROM Grps
ORDER BY mainhost, enter_date DESC; - Phil Parkin wrote:
Can you please confirm this statement:
1 record has match is 2 because it has in 2 subsequent previous month data was there with mainhost
You are referring to the 2023-12-31 row, with mainhost 500.
But there is only one row in 2023-11, with mainhost = 500. So why is match 2 and not 1?
I believe it's because, for Dec, previous rows exist for both Nov and Oct, i.e., two consecutive previous months.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
Ken's code is nice, but theoretically I believe it could yield a false positive if the previous month happened to be 13 months prior to the current month. For example, Feb 2023 followed by Jan 2022.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
-
This query looks for the minimum previous appropriate enter_date for each row and then calculates the DATEDIFF. The sample was expanded to include a greater than 12 month gap
drop table if exists #test_data;
go
create table #test_data ( id int , mainhost int, enter_date date );
insert into #test_data Values
( 1 , 500 , '20231231') , ( 2 , 501 , '20231230') ,
( 3 , 500 , '20231130') , ( 4 , 500 , '20231031') , ( 5 , 501 , '20231130') ,
( 6 , 501 , '20230928'),
( 7 , 500 , '20220928'),
( 11 , 500 , '20220828'),
( 8 , 501 , '20230628'),
( 9 , 501 , '20230528'),
( 10 , 501 , '20230428');
with data_cte as (
select *, row_number() over (partition by mainhost order by enter_date desc) rn
from #test_data)
select *, isnull(datediff(month, prev_dt.min_dt, d.enter_date), 0) dt_diff
from data_cte d
outer apply (select min(dc.enter_date)
from data_cte dc
where dc.mainhost=d.mainhost
and dc.enter_date<d.enter_date
and datediff(month, dc.enter_date, d.enter_date)=(dc.rn-d.rn)) prev_dt(min_dt);Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
- ScottPletcher wrote:
Ken's code is nice, but theoretically I believe it could yield a false positive if the previous month happened to be 13 months prior to the current month. For example, Feb 2023 followed by Jan 2022.
You are, of course, correct. I was thinking that the test data did not include the group possibility and did not look at anything else.
MONTH(enter_date) should be replaced with:
(YEAR(enter_date) * 100 + MONTH(enter_date))
-
yes that is right two consecutive previous months.
-
Thank you everyone for your help. Looks good with both solution.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply