Get count from previous month

Question

Post reply

Get count from previous month

sks_989

SSC Eights!

Points: 843
More actions
January 11, 2024 at 6:04 am

#4360052

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

Attachments:
You must be logged in to view attached files.

Viewing 10 posts - 1 through 9 (of 9 total)

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

Emperor100 SSCrazy Points: 2173 More actions · Answer 1

Use LAG or LEAD analytical functions.

https://learn.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver16

=======================================================================

Phil Parkin SSC Guru Points: 246987 More actions · Answer 2

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

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Ken McKelvey SSCoach Points: 18858 More actions · Answer 3

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;

This reply was modified 11 months, 3 weeks ago by Ken McKelvey.

ScottPletcher SSC Guru Points: 101124 More actions · Answer 4

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".

ScottPletcher SSC Guru Points: 101124 More actions · Answer 5

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".

Steve Collins SSCrazy Points: 2174 More actions · Answer 6

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

Ken McKelvey SSCoach Points: 18858 More actions · Answer 7

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))

sks_989 SSC Eights! Points: 843 More actions · Answer 8

yes that is right two consecutive previous months.

sks_989 SSC Eights! Points: 843 More actions · Answer 9

Thank you everyone for your help. Looks good with both solution.

Get count from previous month

Attachments: