admin管理员组

文章数量:1122846

I need to create an exception/audit report for our accounting section.
The business case is an employee is deployed to remote location and can claim lodging cost if they are away from their family. If their family has moved to their work location/area then the claim is terminated (not part of this query).

The maximum period they can claim is 48 months. There can be a break of up to 6 months (the count of months doesn’t change during a break). If the person exceeds 6 months of break the month counter reset to zero and the employee is not eligible to claim. If the break is 6 months or less the count of remote work continues.

In the sample data below employee ID 49 worked at a remote location from 2019-04-01 until 2020-07-30 with a 5-month break. Then he worked at a different location from 2022-07-31 till 2023-06-30.

Sample data for employe 376. Has worked at the remote location from 2020-08-01 to 2024-11-30. In the case a new file was started dated 2023-09-15. There was two claims for the month of 2023-10-01; the Repeat column indicate this.

Now, I can pull all the costing rows from the database to the app server, 205K rows and calculate the running sequence using a for loop but that has an impact on traffic (database is in a different city) and it increase processing on the IIS server. I want to be able to send a proper query to the database (Microsoft SQL Server 2016 (SP3-GDR) (KB5021129) - 13.0.6430.49 (X64) Jan 22 2023 17:38:22 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: )) and have the app handle the request and presentation only.

This exception or audit report I need to create must show the members that are approaching or have exceeded the 48 months limit. I’ve been playing with windowed rowset and it works if there is no gaps. But where there are gaps of six months or less the report doesn't give what is expected.

The current query is using TotalMonths as parameter but it gives me wrong results. If I use 40 months as the criteria employee 049 should not show. If I use 10 months as the criteria employee 049 should show, and not just the row where (expected) Months = 10 but all the rows for this employee.

To summarize the business rules

  • Employe can claim up to 48 months of expenses when working remotely away from family

  • Employe can take a break of up to six month. The month counter for the claim pauses and resume after the break.

  • If employee takes a break the month counter reset to zero.

  • If there is two claims for a month the partial days are checks. Claim is denied if PartialDays exceed the number of days for the month.

  • When an employee exceed the parameter given all the rows for that employee must be part of the report.

  • Claims where there is a duplicate month must also be flagged.

The only other alternative I haven't tried is using a cursor; and I'm not familiar with that. Is there a way to do this with one SQL statement?

Table structure

-- table structure
CREATE TABLE [dbo].[ir_Costs](
    [EID] [varchar](9) COLLATE Latin1_General_CI_AS NOT NULL,
    [CLMinitialDT] [date] NOT NULL,
    [MonthYear] [date] NOT NULL,
    [Lodging] [smallmoney] NULL,
    [others] [smallmoney] NULL,
    [DaysPartial] [tinyint] NULL,
 CONSTRAINT [PK_ir_Costs] PRIMARY KEY CLUSTERED 
(
    [EID] ASC,
    [CLMinitialDT] ASC,
    [MonthYear] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

The query The application code filters the input request and creates an ADODB command object. For our example the parameter would be @months = 40

DECLARE @months smallint=? -- the normal value for ? can be 48 +/-

SELECT Q1.* FROM 
( SELECT c.EID
    ,c.CLMinitialDT
    ,c.MonthYear
    ,LAG(c.MonthYear) OVER (PARTITION BY c.EID ORDER BY c.MonthYear ) as PrevMth
    ,c.Lodging 
    ,(SELECT SUM(Lodging + others) 
      FROM ir_Costs d 
      WHERE d.MonthYear <= c.MonthYear 
        and d.EID=c.EID and d.CLMinitialDT=c.CLMinitialDT 
      GROUP BY d.EID, d.CLMinitialDT
      ) AS RunningTotal 
    ,COUNT(*) OVER (PARTITION BY c.EID, c.MonthYear)-1 AS Repeats1
    ,COUNT(*) OVER (PARTITION BY c.EID) AS CostRows 
    ,NTILE(250) OVER (PARTITION BY c.EID ORDER BY c.monthyear,c.CLMinitialDT ) AS RowSeq
    ,datediff(MONTH
             ,LAG(c.MonthYear) OVER (PARTITION BY c.EID ORDER BY c.MonthYear )
             ,c.MonthYear )  as MonthGap
    ,datediff(MONTH, MIN(c.MonthYear) OVER (PARTITION BY c.EID )
             ,MAX(c.MonthYear) OVER (PARTITION BY c.EID )) + 1 as TotalMonths 
    ,c.DaysPartial
  FROM ir_Costs c 
) Q1  
WHERE (TotalMonths >= @months OR @months IS NULL)
ORDER BY EID, MonthYear

I also tried this with the same result

DECLARE @months smallint=48;
WITH Q1 AS ( SELECT c.EID
    ,c.CLMinitialDT 
    ,c.MonthYear
    ,LAG(c.MonthYear) OVER (PARTITION BY c.EID ORDER BY c.MonthYear ) as PrevMonth
    ,c.Lodging
    ,(SELECT SUM(Lodging + others) 
      FROM ir_Costs d 
      WHERE d.MonthYear <= c.MonthYear 
         and d.EID=c.EID and d.CLMinitialDT =c.CLMinitialDT  
      GROUP BY d.EID, d.CLMinitialDT 
      ) AS RunningTotal 
    ,COUNT(*) OVER (PARTITION BY c.EID, c.MonthYear)-1 AS Repeats1
    ,NTILE(250) OVER (PARTITION BY c.EID ORDER BY c.monthyear,c.CLMinitialDT  ) AS RowSeq
    ,datediff(MONTH, LAG(c.MonthYear) OVER (PARTITION BY c.EID ORDER BY c.MonthYear )
                   ,c.MonthYear )  as MonthGap
    --,datediff(MONTH, MIN(c.MonthYear) OVER (PARTITION BY c.EID )
      --             ,MAX(c.MonthYear) OVER (PARTITION BY c.EID )) + 1 as TotalMonths 
     ,c.DaysPartial
  FROM ir_Costs c 
)
SELECT Q1.*,(COUNT(*) OVER (PARTITION BY Q1.EID)) AS CostRows, max(RowSeq) as TotalMonths 
FROM Q1 
GROUP BY EID,CLMinitialDT,MonthYear,PrevMonth,Lodging,RunningTotal,Repeats1,RowSeq,MonthGap,DaysPartial
ORDER BY EID, MonthYear

I've rerun the simulation in

Source Data

EID CLMinitialDT MonthYear Accomodation Others DaysPartial
049 2018-07-03 2019-04-01 435.98 0.00 30
049 2018-07-03 2019-05-01 435.98 0.00 30
049 2018-07-03 2019-06-01 435.98 0.00 30
049 2018-07-03 2019-07-01 130.79 0.00 9
049 2018-07-03 2020-01-01 261.59 0.00 18
049 2018-07-03 2020-02-01 435.98 0.00 28
049 2018-07-03 2020-03-01 435.98 0.00 30
049 2018-07-03 2020-04-01 435.98 0.00 30
049 2018-07-03 2020-05-01 435.98 0.00 30
049 2018-07-03 2020-06-01 435.98 0.00 30
049 2018-07-03 2020-07-01 421.45 0.00 29
049 2021-05-21 2022-08-01 53.60 0.00 4
049 2021-05-21 2022-12-01 77.58 0.00 16
049 2021-05-21 2023-01-01 415.46 0.00 30
049 2021-05-21 2023-02-01 415.46 0.00 28
049 2021-05-21 2023-03-01 415.46 0.00 30
049 2021-05-21 2023-04-01 415.46 0.00 30
049 2021-05-21 2023-05-01 415.46 0.00 30
049 2021-05-21 2023-06-01 439.97 0.00 30
049 2021-05-21 2023-07-01 263.98 0.00 18
376 2019-08-01 2020-08-01 1500.00 100.00 30
376 2019-08-01 2020-09-01 1500.00 0.00 30
376 2019-08-01 2020-10-01 1500.00 0.00 30
376 2019-08-01 2020-11-01 1500.00 0.00 30
376 2019-08-01 2020-12-01 1500.00 100.00 30
376 2019-08-01 2021-01-01 1500.00 0.00 30
376 2019-08-01 2021-02-01 1500.00 100.00 28
376 2019-08-01 2021-03-01 1500.00 0.00 30
376 2019-08-01 2021-04-01 1500.00 100.00 30
376 2019-08-01 2021-05-01 1500.00 0.00 30
376 2019-08-01 2021-06-01 1500.00 100.00 30
376 2019-08-01 2021-07-01 1500.00 100.00 30
376 2019-08-01 2021-08-01 1500.00 100.00 30
376 2019-08-01 2021-09-01 1500.00 100.00 30
376 2019-08-01 2021-10-01 1500.00 100.00 30
376 2019-08-01 2021-11-01 1500.00 100.00 30
376 2019-08-01 2021-12-01 1500.00 0.00 30
376 2019-08-01 2022-01-01 22.00 0.00 30
376 2019-08-01 2022-02-01 1522.00 0.00 28
376 2019-08-01 2022-03-01 1522.00 0.00 30
376 2019-08-01 2022-04-01 1522.00 0.00 30
376 2019-08-01 2022-05-01 1522.00 0.00 30
376 2019-08-01 2022-06-01 1522.00 0.00 30
376 2019-08-01 2022-07-01 1522.00 0.00 30
376 2019-08-01 2022-08-01 1522.00 0.00 30
376 2019-08-01 2022-09-01 1522.00 0.00 30
376 2019-08-01 2022-10-01 1522.00 0.00 30
376 2019-08-01 2022-11-01 1522.00 0.00 30
376 2019-08-01 2022-12-01 1522.00 0.00 30
376 2019-08-01 2023-01-01 1522.00 0.00 30
376 2019-08-01 2023-02-01 1522.00 0.00 28
376 2019-08-01 2023-03-01 1522.00 0.00 30
376 2019-08-01 2023-04-01 1552.00 0.00 30
376 2019-08-01 2023-05-01 1552.00 0.00 30
376 2019-08-01 2023-06-01 1552.00 0.00 30
376 2019-08-01 2023-07-01 1552.00 0.00 30
376 2019-08-01 2023-08-01 1552.00 0.00 30
376 2019-08-01 2023-10-01 1600.00 0.00 30
376 2023-09-15 2023-09-01 1600.00 0.00 30
376 2023-09-15 2023-10-01 1600.00 0.00 30
376 2023-09-15 2023-11-01 1600.00 0.00 30
376 2023-09-15 2023-12-01 1600.00 0.00 30
376 2023-09-15 2024-01-01 1600.00 0.00 30
376 2023-09-15 2024-02-01 1600.00 0.00 28
376 2023-09-15 2024-03-01 1600.00 0.00 30
376 2023-09-15 2024-04-01 1600.00 112.20 30
376 2023-09-15 2024-05-01 1600.00 0.00 30
376 2023-09-15 2024-06-01 1600.00 119.89 30
376 2023-09-15 2024-07-01 1600.00 0.00 30
376 2023-09-15 2024-08-01 1680.00 216.85 30
376 2023-09-15 2024-09-01 1780.00 0.00 30
376 2023-09-15 2024-10-01 1969.00 0.00 30
376 2023-09-15 2024-11-01 1680.00 210.53 30

I need to create an exception/audit report for our accounting section.
The business case is an employee is deployed to remote location and can claim lodging cost if they are away from their family. If their family has moved to their work location/area then the claim is terminated (not part of this query).

The maximum period they can claim is 48 months. There can be a break of up to 6 months (the count of months doesn’t change during a break). If the person exceeds 6 months of break the month counter reset to zero and the employee is not eligible to claim. If the break is 6 months or less the count of remote work continues.

In the sample data below employee ID 49 worked at a remote location from 2019-04-01 until 2020-07-30 with a 5-month break. Then he worked at a different location from 2022-07-31 till 2023-06-30.

Sample data for employe 376. Has worked at the remote location from 2020-08-01 to 2024-11-30. In the case a new file was started dated 2023-09-15. There was two claims for the month of 2023-10-01; the Repeat column indicate this.

Now, I can pull all the costing rows from the database to the app server, 205K rows and calculate the running sequence using a for loop but that has an impact on traffic (database is in a different city) and it increase processing on the IIS server. I want to be able to send a proper query to the database (Microsoft SQL Server 2016 (SP3-GDR) (KB5021129) - 13.0.6430.49 (X64) Jan 22 2023 17:38:22 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: )) and have the app handle the request and presentation only.

This exception or audit report I need to create must show the members that are approaching or have exceeded the 48 months limit. I’ve been playing with windowed rowset and it works if there is no gaps. But where there are gaps of six months or less the report doesn't give what is expected.

The current query is using TotalMonths as parameter but it gives me wrong results. If I use 40 months as the criteria employee 049 should not show. If I use 10 months as the criteria employee 049 should show, and not just the row where (expected) Months = 10 but all the rows for this employee.

To summarize the business rules

  • Employe can claim up to 48 months of expenses when working remotely away from family

  • Employe can take a break of up to six month. The month counter for the claim pauses and resume after the break.

  • If employee takes a break the month counter reset to zero.

  • If there is two claims for a month the partial days are checks. Claim is denied if PartialDays exceed the number of days for the month.

  • When an employee exceed the parameter given all the rows for that employee must be part of the report.

  • Claims where there is a duplicate month must also be flagged.

The only other alternative I haven't tried is using a cursor; and I'm not familiar with that. Is there a way to do this with one SQL statement?

Table structure

-- table structure
CREATE TABLE [dbo].[ir_Costs](
    [EID] [varchar](9) COLLATE Latin1_General_CI_AS NOT NULL,
    [CLMinitialDT] [date] NOT NULL,
    [MonthYear] [date] NOT NULL,
    [Lodging] [smallmoney] NULL,
    [others] [smallmoney] NULL,
    [DaysPartial] [tinyint] NULL,
 CONSTRAINT [PK_ir_Costs] PRIMARY KEY CLUSTERED 
(
    [EID] ASC,
    [CLMinitialDT] ASC,
    [MonthYear] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

The query The application code filters the input request and creates an ADODB command object. For our example the parameter would be @months = 40

DECLARE @months smallint=? -- the normal value for ? can be 48 +/-

SELECT Q1.* FROM 
( SELECT c.EID
    ,c.CLMinitialDT
    ,c.MonthYear
    ,LAG(c.MonthYear) OVER (PARTITION BY c.EID ORDER BY c.MonthYear ) as PrevMth
    ,c.Lodging 
    ,(SELECT SUM(Lodging + others) 
      FROM ir_Costs d 
      WHERE d.MonthYear <= c.MonthYear 
        and d.EID=c.EID and d.CLMinitialDT=c.CLMinitialDT 
      GROUP BY d.EID, d.CLMinitialDT
      ) AS RunningTotal 
    ,COUNT(*) OVER (PARTITION BY c.EID, c.MonthYear)-1 AS Repeats1
    ,COUNT(*) OVER (PARTITION BY c.EID) AS CostRows 
    ,NTILE(250) OVER (PARTITION BY c.EID ORDER BY c.monthyear,c.CLMinitialDT ) AS RowSeq
    ,datediff(MONTH
             ,LAG(c.MonthYear) OVER (PARTITION BY c.EID ORDER BY c.MonthYear )
             ,c.MonthYear )  as MonthGap
    ,datediff(MONTH, MIN(c.MonthYear) OVER (PARTITION BY c.EID )
             ,MAX(c.MonthYear) OVER (PARTITION BY c.EID )) + 1 as TotalMonths 
    ,c.DaysPartial
  FROM ir_Costs c 
) Q1  
WHERE (TotalMonths >= @months OR @months IS NULL)
ORDER BY EID, MonthYear

I also tried this with the same result

DECLARE @months smallint=48;
WITH Q1 AS ( SELECT c.EID
    ,c.CLMinitialDT 
    ,c.MonthYear
    ,LAG(c.MonthYear) OVER (PARTITION BY c.EID ORDER BY c.MonthYear ) as PrevMonth
    ,c.Lodging
    ,(SELECT SUM(Lodging + others) 
      FROM ir_Costs d 
      WHERE d.MonthYear <= c.MonthYear 
         and d.EID=c.EID and d.CLMinitialDT =c.CLMinitialDT  
      GROUP BY d.EID, d.CLMinitialDT 
      ) AS RunningTotal 
    ,COUNT(*) OVER (PARTITION BY c.EID, c.MonthYear)-1 AS Repeats1
    ,NTILE(250) OVER (PARTITION BY c.EID ORDER BY c.monthyear,c.CLMinitialDT  ) AS RowSeq
    ,datediff(MONTH, LAG(c.MonthYear) OVER (PARTITION BY c.EID ORDER BY c.MonthYear )
                   ,c.MonthYear )  as MonthGap
    --,datediff(MONTH, MIN(c.MonthYear) OVER (PARTITION BY c.EID )
      --             ,MAX(c.MonthYear) OVER (PARTITION BY c.EID )) + 1 as TotalMonths 
     ,c.DaysPartial
  FROM ir_Costs c 
)
SELECT Q1.*,(COUNT(*) OVER (PARTITION BY Q1.EID)) AS CostRows, max(RowSeq) as TotalMonths 
FROM Q1 
GROUP BY EID,CLMinitialDT,MonthYear,PrevMonth,Lodging,RunningTotal,Repeats1,RowSeq,MonthGap,DaysPartial
ORDER BY EID, MonthYear

I've rerun the simulation in https://dbfiddle.uk/CRlw1sni

Source Data

EID CLMinitialDT MonthYear Accomodation Others DaysPartial
049 2018-07-03 2019-04-01 435.98 0.00 30
049 2018-07-03 2019-05-01 435.98 0.00 30
049 2018-07-03 2019-06-01 435.98 0.00 30
049 2018-07-03 2019-07-01 130.79 0.00 9
049 2018-07-03 2020-01-01 261.59 0.00 18
049 2018-07-03 2020-02-01 435.98 0.00 28
049 2018-07-03 2020-03-01 435.98 0.00 30
049 2018-07-03 2020-04-01 435.98 0.00 30
049 2018-07-03 2020-05-01 435.98 0.00 30
049 2018-07-03 2020-06-01 435.98 0.00 30
049 2018-07-03 2020-07-01 421.45 0.00 29
049 2021-05-21 2022-08-01 53.60 0.00 4
049 2021-05-21 2022-12-01 77.58 0.00 16
049 2021-05-21 2023-01-01 415.46 0.00 30
049 2021-05-21 2023-02-01 415.46 0.00 28
049 2021-05-21 2023-03-01 415.46 0.00 30
049 2021-05-21 2023-04-01 415.46 0.00 30
049 2021-05-21 2023-05-01 415.46 0.00 30
049 2021-05-21 2023-06-01 439.97 0.00 30
049 2021-05-21 2023-07-01 263.98 0.00 18
376 2019-08-01 2020-08-01 1500.00 100.00 30
376 2019-08-01 2020-09-01 1500.00 0.00 30
376 2019-08-01 2020-10-01 1500.00 0.00 30
376 2019-08-01 2020-11-01 1500.00 0.00 30
376 2019-08-01 2020-12-01 1500.00 100.00 30
376 2019-08-01 2021-01-01 1500.00 0.00 30
376 2019-08-01 2021-02-01 1500.00 100.00 28
376 2019-08-01 2021-03-01 1500.00 0.00 30
376 2019-08-01 2021-04-01 1500.00 100.00 30
376 2019-08-01 2021-05-01 1500.00 0.00 30
376 2019-08-01 2021-06-01 1500.00 100.00 30
376 2019-08-01 2021-07-01 1500.00 100.00 30
376 2019-08-01 2021-08-01 1500.00 100.00 30
376 2019-08-01 2021-09-01 1500.00 100.00 30
376 2019-08-01 2021-10-01 1500.00 100.00 30
376 2019-08-01 2021-11-01 1500.00 100.00 30
376 2019-08-01 2021-12-01 1500.00 0.00 30
376 2019-08-01 2022-01-01 22.00 0.00 30
376 2019-08-01 2022-02-01 1522.00 0.00 28
376 2019-08-01 2022-03-01 1522.00 0.00 30
376 2019-08-01 2022-04-01 1522.00 0.00 30
376 2019-08-01 2022-05-01 1522.00 0.00 30
376 2019-08-01 2022-06-01 1522.00 0.00 30
376 2019-08-01 2022-07-01 1522.00 0.00 30
376 2019-08-01 2022-08-01 1522.00 0.00 30
376 2019-08-01 2022-09-01 1522.00 0.00 30
376 2019-08-01 2022-10-01 1522.00 0.00 30
376 2019-08-01 2022-11-01 1522.00 0.00 30
376 2019-08-01 2022-12-01 1522.00 0.00 30
376 2019-08-01 2023-01-01 1522.00 0.00 30
376 2019-08-01 2023-02-01 1522.00 0.00 28
376 2019-08-01 2023-03-01 1522.00 0.00 30
376 2019-08-01 2023-04-01 1552.00 0.00 30
376 2019-08-01 2023-05-01 1552.00 0.00 30
376 2019-08-01 2023-06-01 1552.00 0.00 30
376 2019-08-01 2023-07-01 1552.00 0.00 30
376 2019-08-01 2023-08-01 1552.00 0.00 30
376 2019-08-01 2023-10-01 1600.00 0.00 30
376 2023-09-15 2023-09-01 1600.00 0.00 30
376 2023-09-15 2023-10-01 1600.00 0.00 30
376 2023-09-15 2023-11-01 1600.00 0.00 30
376 2023-09-15 2023-12-01 1600.00 0.00 30
376 2023-09-15 2024-01-01 1600.00 0.00 30
376 2023-09-15 2024-02-01 1600.00 0.00 28
376 2023-09-15 2024-03-01 1600.00 0.00 30
376 2023-09-15 2024-04-01 1600.00 112.20 30
376 2023-09-15 2024-05-01 1600.00 0.00 30
376 2023-09-15 2024-06-01 1600.00 119.89 30
376 2023-09-15 2024-07-01 1600.00 0.00 30
376 2023-09-15 2024-08-01 1680.00 216.85 30
376 2023-09-15 2024-09-01 1780.00 0.00 30
376 2023-09-15 2024-10-01 1969.00 0.00 30
376 2023-09-15 2024-11-01 1680.00 210.53 30

Example (with a break) In Aug 2020 the employee returned home for two year. The RowSeq must return to zero.

EID CLMinitialDT MonthYear PreviousMonth Lodging RunningTotal Repeats CostRows RowSeq MonthGap TotalMonths DaysPartial
49 2018-07-03 2019-04-01 NULL 435.98 435.98 0 20 1 NULL 52 30
49 2018-07-03 2019-05-01 2019-04-01 435.98 871.96 0 20 2 1 52 30
49 2018-07-03 2019-06-01 2019-05-01 435.98 1307.94 0 20 3 1 52 30
49 2018-07-03 2019-07-01 2019-06-01 130.79 1438.73 0 20 4 1 52 9
49 2018-07-03 2020-01-01 2019-07-01 261.59 1700.32 0 20 5 6 52 18
49 2018-07-03 2020-02-01 2020-01-01 435.98 2136.3 0 20 6 1 52 28
49 2018-07-03 2020-03-01 2020-02-01 435.98 2572.28 0 20 7 1 52 30
49 2018-07-03 2020-04-01 2020-03-01 435.98 3008.26 0 20 8 1 52 30
49 2018-07-03 2020-05-01 2020-04-01 435.98 3444.24 0 20 9 1 52 30
49 2018-07-03 2020-06-01 2020-05-01 435.98 3880.22 0 20 10 1 52 30
49 2018-07-03 2020-07-01 2020-06-01 421.45 4301.67 0 20 11 1 52 29
49 2021-05-21 2022-08-01 2020-07-01 53.6 4355.27 0 20 12 25 52 4
49 2021-05-21 2022-12-01 2022-08-01 77.58 4432.85 0 20 13 4 52 16
49 2021-05-21 2023-01-01 2022-12-01 415.46 4848.31 0 20 14 1 52 30
49 2021-05-21 2023-02-01 2023-01-01 415.46 5263.77 0 20 15 1 52 28
49 2021-05-21 2023-03-01 2023-02-01 415.46 5679.23 0 20 16 1 52 30
49 2021-05-21 2023-04-01 2023-03-01 415.46 6094.69 0 20 17 1 52 30
49 2021-05-21 2023-05-01 2023-04-01 415.46 6510.15 0 20 18 1 52 30
49 2021-05-21 2023-06-01 2023-05-01 439.97 6950.12 0 20 19 1 52 30
49 2021-05-21 2023-07-01 2023-06-01 263.98 7214.1 0 20 20 1 52 18

EXPECTED Here we want the row sequence RowSeq to return to zero after a gap greater than six months.

EID CLMinitialDT MonthYear PreviousMonth Lodging RunningTotal Repeats CostRows RowSeq MonthGap TotalMonths DaysPartial
49 2018-07-03 2019-04-01 NULL 435.98 435.98 0 20 0 NULL 10 30
49 2018-07-03 2019-05-01 2019-04-01 435.98 871.96 0 20 1 1 10 30
49 2018-07-03 2019-06-01 2019-05-01 435.98 1307.94 0 20 2 1 10 30
49 2018-07-03 2019-07-01 2019-06-01 130.79 1438.73 0 20 3 1 10 9
49 2018-07-03 2020-01-01 2019-07-01 261.59 1700.32 0 20 4 6 10 18
49 2018-07-03 2020-02-01 2020-01-01 435.98 2136.3 0 20 5 1 10 28
49 2018-07-03 2020-03-01 2020-02-01 435.98 2572.28 0 20 6 1 10 30
49 2018-07-03 2020-04-01 2020-03-01 435.98 3008.26 0 20 7 1 10 30
49 2018-07-03 2020-05-01 2020-04-01 435.98 3444.24 0 20 8 1 10 30
49 2018-07-03 2020-06-01 2020-05-01 435.98 3880.22 0 20 9 1 10 30
49 2018-07-03 2020-07-01 2020-06-01 421.45 4301.67 0 20 10 1 10 29
49 2021-05-21 2022-08-01 2020-07-01 53.6 4355.27 0 20 0 25 10 4
49 2021-05-21 2022-12-01 2022-08-01 77.58 4432.85 0 20 1 4 10 16
49 2021-05-21 2023-01-01 2022-12-01 415.46 4848.31 0 20 2 1 10 30
49 2021-05-21 2023-02-01 2023-01-01 415.46 5263.77 0 20 3 1 10 28
49 2021-05-21 2023-03-01 2023-02-01 415.46 5679.23 0 20 4 1 10 30
49 2021-05-21 2023-04-01 2023-03-01 415.46 6094.69 0 20 5 1 10 30
49 2021-05-21 2023-05-01 2023-04-01 415.46 6510.15 0 20 6 1 10 30
49 2021-05-21 2023-06-01 2023-05-01 439.97 6950.12 0 20 7 1 10 30
49 2021-05-21 2023-07-01 2023-06-01 263.98 7214.1 0 20 8 1 10 18

Another employee (first 35 months skipped)
In this example the employe did not have a break/gap but there is a repeat claim for 2023-10-01 and it introduce an error in the running total; and CostRows > TotalMonths

EID CLMinitialDT MonthYear PreviousMonth Lodging RunningTotal Repeats CostRows RowSeq MonthGap TotalMonths DaysPartial
376 2019-08-01 2023-07-01 2023-06-01 1552 54038 0 53 36 1 52 30
376 2019-08-01 2023-08-01 2023-07-01 1552 55590 0 53 37 1 52 30
376 2023-09-15 2023-09-01 2023-08-01 1600 1600 0 53 38 1 52 30
376 2019-08-01 2023-10-01 2023-10-01 1600 57190 1 53 39 0 52 30
376 2023-09-15 2023-10-01 2023-09-01 1600 3200 1 53 40 1 52 30
376 2023-09-15 2023-11-01 2023-10-01 1600 4800 0 53 41 1 52 30
376 2023-09-15 2023-12-01 2023-11-01 1600 6400 0 53 42 1 52 30
376 2023-09-15 2024-01-01 2023-12-01 1600 8000 0 53 43 1 52 30
376 2023-09-15 2024-02-01 2024-01-01 1600 9600 0 53 44 1 52 28
376 2023-09-15 2024-03-01 2024-02-01 1600 11200 0 53 45 1 52 30
376 2023-09-15 2024-04-01 2024-03-01 1600 12912.2 0 53 46 1 52 30
376 2023-09-15 2024-05-01 2024-04-01 1600 14512.2 0 53 47 1 52 30
376 2023-09-15 2024-06-01 2024-05-01 1600 16232.09 0 53 48 1 52 30
376 2023-09-15 2024-07-01 2024-06-01 1600 17832.09 0 53 49 1 52 30
376 2023-09-15 2024-08-01 2024-07-01 1680 19728.94 0 53 50 1 52 30
376 2023-09-15 2024-09-01 2024-08-01 1780 21508.94 0 53 51 1 52 30
376 2023-09-15 2024-10-01 2024-09-01 1969 23477.94 0 53 52 1 52 30
376 2023-09-15 2024-11-01 2024-10-01 1680 25368.47 0 53 53 1 52 30

EXPECTED The Running Total should continue to add properly as there are no gaps in the sequence.

EID CLMinitialDT MonthYear PreviousMonth Lodging RunningTotal Repeats1 CostRows RowSeq MonthGap TotalMonths DaysPartial
376 2019-08-01 2023-07-01 2023-06-01 1552.00 54038.00 0 53 36 1 52 30
376 2019-08-01 2023-08-01 2023-07-01 1552.00 55590.00 0 53 37 1 52 30
376 2023-09-15 2023-09-01 2023-08-01 1600.00 1600.00 0 53 38 1 52 30
376 2019-08-01 2023-10-01 2023-10-01 1600.00 57190.00 1 53 39 0 52 30
376 2023-09-15 2023-10-01 2023-09-01 1600.00 3200.00 1 53 40 1 52 30
376 2023-09-15 2023-11-01 2023-10-01 1600.00 4800.00 0 53 41 1 52 30
376 2023-09-15 2023-12-01 2023-11-01 1600.00 6400.00 0 53 42 1 52 30
376 2023-09-15 2024-01-01 2023-12-01 1600.00 8000.00 0 53 43 1 52 30
376 2023-09-15 2024-02-01 2024-01-01 1600.00 9600.00 0 53 44 1 52 28
376 2023-09-15 2024-03-01 2024-02-01 1600.00 11200.00 0 53 45 1 52 30
376 2023-09-15 2024-04-01 2024-03-01 1600.00 12912.20 0 53 46 1 52 30
376 2023-09-15 2024-05-01 2024-04-01 1600.00 14512.20 0 53 47 1 52 30
376 2023-09-15 2024-06-01 2024-05-01 1600.00 16232.09 0 53 48 1 52 30
376 2023-09-15 2024-07-01 2024-06-01 1600.00 17832.09 0 53 49 1 52 30
376 2023-09-15 2024-08-01 2024-07-01 1680.00 19728.94 0 53 50 1 52 30
376 2023-09-15 2024-09-01 2024-08-01 1780.00 21508.94 0 53 51 1 52 30
376 2023-09-15 2024-10-01 2024-09-01 1969.00 23477.94 0 53 52 1 52 30
376 2023-09-15 2024-11-01 2024-10-01 1680.00 25368.47 0 53 53 1 52 30

SOLUTION

Thank you @ValNik for you help. The final query is:

DECLARE @months smallint=48;
SELECT * 
FROM(
select *
  ,max(RunningMonth)over(partition by EID) maxRunningMonth
from(
select *
  ,count(case when isBreak=0 then coalesce(MonthGap,0) else 0 end)
      over(partition by EID,grN order by MonthYear) RunningMonth
  ,SUM(Lodging + coalesce(others,0.0)) 
      OVER (PARTITION BY EID,grN ORDER BY MonthYear) as RunningTotal
from(
select *
  ,sum(isBreak)
    over(partition by EID order by MonthYear) grN
from(
  SELECT c.EID
    ,c.CLMinitialDT
    ,c.MonthYear
    ,LAG(c.MonthYear) OVER (PARTITION BY c.EID ORDER BY c.MonthYear ) as PrevMth
    ,c.Lodging 
    ,c.others
  --  ,SUM(Lodging + coalesce(others,0.0)) over(partition by EId,CLMinitialDt order by MonthYear) RunningTotal
    ,COUNT(*) OVER (PARTITION BY c.EID, c.MonthYear)-1 AS Repeats1
    ,datediff(MONTH, LAG(c.MonthYear) OVER (PARTITION BY c.EID ORDER BY c.MonthYear )
                   ,c.MonthYear )  as MonthGap
    ,case when datediff(MONTH, LAG(c.MonthYear) OVER (PARTITION BY c.EID ORDER BY c.MonthYear )
                   ,c.MonthYear )>6 then 1 
     else 0 end isBreak
    ,datediff(MONTH, MIN(c.MonthYear) OVER (PARTITION BY c.EID )
                   ,MAX(c.MonthYear) OVER (PARTITION BY c.EID )) + 1 as TotalMonths 
  FROM ir_Costs c 
)a
)b
)c
)d WHERE (maxRunningMonth >= @months OR @months IS NULL)

Can be viewed here https://dbfiddle.uk/TrKMACHd

  • sql-server
  • sequence
  • common-table-expression
  • date-range
  • gaps-and-islands
Share Improve this question edited Nov 28, 2024 at 17:12 Jean-Marc asked Nov 22, 2024 at 20:40 Jean-MarcJean-Marc 1096 bronze badges 9
  • 3 Please do not post images of data try to create it in a temp table or something that other people can test/use. See here: stackoverflow.com/help/minimal-reproducible-example – Brad Commented Nov 22, 2024 at 21:01
  • Provide source table structure and test data. – ValNik Commented Nov 25, 2024 at 21:22
  • Just to clarify, the Expected column is what I want to read in the RowSeq column, and the TotalMonths should have the maximum value of the Expected column. That will be used to filter the report for people exceeding X months in the sequence. – Jean-Marc Commented Nov 27, 2024 at 17:21
  • You need to show the desired result instead of the result of your current query, i.e. what you would like to get. The purpose of the Days Partial column is also unclear. Explain or remove from the example as superfluous. You can also exclude the columns Accommodation Type CD, Rom cd from the example. Their purpose is not clear and is not necessary for the task. – ValNik Commented Nov 27, 2024 at 21:41
  • In your example, for (EmplId,CLMinitialDT) (49,'2018-07-03') 48(52) months are not achieved, because the calculation starts again after a gap of 25 months. Are these rows needed in the output result? Add an example of the data so that you can see what is expected as a result when the duration approaches 48(52) months. Also see an example of dividing (gaps and islands) into parts that can be considered separately (dbfiddle.uk/y3_0BQ1a). – ValNik Commented Nov 27, 2024 at 21:47
 |  Show 4 more comments

1 Answer 1

Reset to default 0

Thank you @ValNik for you help. The final query is:

DECLARE @months smallint=?; -- where ? is the number of months of continuous claim (with allowed gaps of 6 months) 
SELECT * 
FROM(
select *
  ,max(RunningMonth)over(partition by EID) maxRunningMonth
from(
select *
  ,count(case when isBreak=0 then coalesce(MonthGap,0) else 0 end)
      over(partition by EID,grN order by MonthYear) RunningMonth
  ,SUM(Lodging + coalesce(others,0.0)) 
      OVER (PARTITION BY EID,grN ORDER BY MonthYear) as RunningTotal
from(
select *
  ,sum(isBreak)
    over(partition by EID order by MonthYear) grN
from(
  SELECT c.EID
    ,c.CLMinitialDT
    ,c.MonthYear
    ,LAG(c.MonthYear) OVER (PARTITION BY c.EID ORDER BY c.MonthYear ) as PrevMth
    ,c.Lodging 
    ,c.others
  --  ,SUM(Lodging + coalesce(others,0.0)) over(partition by EId,CLMinitialDt order by MonthYear) RunningTotal
    ,COUNT(*) OVER (PARTITION BY c.EID, c.MonthYear)-1 AS Repeats1
    ,datediff(MONTH, LAG(c.MonthYear) OVER (PARTITION BY c.EID ORDER BY c.MonthYear )
                   ,c.MonthYear )  as MonthGap
    ,case when datediff(MONTH, LAG(c.MonthYear) OVER (PARTITION BY c.EID ORDER BY c.MonthYear )
                   ,c.MonthYear )>6 then 1 
     else 0 end isBreak
    ,datediff(MONTH, MIN(c.MonthYear) OVER (PARTITION BY c.EID )
                   ,MAX(c.MonthYear) OVER (PARTITION BY c.EID )) + 1 as TotalMonths 
  FROM ir_Costs c 
)a
)b
)c
)d WHERE (maxRunningMonth >= @months OR @months IS NULL)

Can be viewed here https://dbfiddle.uk/TrKMACHd

本文标签：

版权声明：本文标题：sql server - SQL query to audit employee claim based on specific sequence of rows defined by criteria - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1736300989a1931021.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。