Get YTD Values for current financial year

Transact-SQL
1

I need to calculate and update the YTD (YTDDepr column) for each item for each day in a history table. But I only have the MTD values, and sometimes there can be adjustments at month end. I have a calendar table which gives the Financial Year Number (FYN) for every date (our year runs April to March).

The history table for 2 example items looks like this:

+--------+------------+---------+--------+
| ItemNo | StatusDate | MthDepr | YTDDepr |
+--------+------------+---------+--------+
|  88190 | 28/02/2022 | 23.24   |      0 |
|  88191 | 28/02/2022 | 77.84   |      0 |
|  88190 | 01/03/2022 | 0.83    |      0 |
|  88191 | 01/03/2022 | 2.78    |      0 |
|  88190 | 02/03/2022 | 1.66    |      0 |
|  88191 | 02/03/2022 | 5.56    |      0 |
|  88190 | 03/03/2022 | 2.49    |      0 |
|  88191 | 03/03/2022 | 8.34    |      0 |
+--------+------------+---------+--------+

The calendar table looks like this:

+-----+------------+------+
| SID |  CalDate   | FYN  |
+-----+------------+------+
| 547 | 24/02/2022 | 2022 |
| 557 | 25/02/2022 | 2022 |
| 567 | 26/02/2022 | 2022 |
| 577 | 27/02/2022 | 2022 |
| 587 | 28/02/2022 | 2022 |
| 597 | 01/03/2022 | 2022 |
| 607 | 02/03/2022 | 2022 |
| 617 | 03/03/2022 | 2022 |
| 627 | 04/03/2022 | 2022 |
| 637 | 05/03/2022 | 2022 |
| 647 | 06/03/2022 | 2022 |
+-----+------------+------+

The result for the 2 examples should look like this:

+--------+------------+---------+--------+
| ItemNo | StatusDate | MthDepr | YTDDepr |
+--------+------------+---------+--------+
|  88190 | 28/02/2022 | 23.24   | 255.64 |
|  88191 | 28/02/2022 | 77.84   | 856.24 |
|  88190 | 01/03/2022 | 0.83    | 256.47 |
|  88191 | 01/03/2022 | 2.78    | 859.02 |
|  88190 | 02/03/2022 | 1.66    | 257.30 |
|  88191 | 02/03/2022 | 5.56    | 861.80 |
|  88190 | 03/03/2022 | 2.49    | 258.13 |
|  88191 | 03/03/2022 | 8.34    | 864.58 |
+--------+------------+---------+--------+

What is the easiest way to get and set the amount for the YTDDepr column?

Many thanks
Martyn

2

hi

how would you calculate the YTDDepr ?

what is the formula ? or way ?

3

This is the method I had planned to use:

  1. Get the Financial Year Number (FYN) from the calendar table based on the current date.
  2. For each ItemNo, sum the MthDepr from the last day of every completed month with the same FYN as determined in step 1.
  3. To this value, for each ItemNo, add the MthDepr for the current date.
  4. Update this to the YTDDepr column for that itemno for the current date.
4

Step 1

-- Get the Financial Year Number (FYN) from the calendar table based on the current date.

-- select FYN from CalendarTable where CalDate = cast( getdate() as date )

-- here
CalDate
cast( getdate() as date )

<<<<<<    Format for both has to match >>>>

.. apples to apples .. oranges to oranges

5

I don't understand "864.58" ... shouldn't the total be 861.80 + 8.34 = 870.14?

Please provide the sample data as executable DDL rather than just an image. That is, CREATE TABLE and INSERT statement(s) that create the data.

6

Step 2

image
image1093×256 6.96 KB

7

Hi Scott

Yes, that was an error on my part. Here are the scripts for the table and data:

CREATE TABLE [Calendar](
	[SID] [int] NULL,
	[CalDate] [date] NULL,
	[FYN] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [FAR](
	[ItemNo] [varchar](12) NULL,
	[StatusDate] [date] NULL,
	[MthDepr] [decimal](15, 2) NULL,
	[YTDDepr] [decimal](15, 2) NULL
) ON [PRIMARY]
GO

INSERT Calendar ([SID], [CalDate], [FYN]) VALUES (547, CAST(N'2022-02-24' AS Date), 2022)
GO
INSERT Calendar ([SID], [CalDate], [FYN]) VALUES (557, CAST(N'2022-02-25' AS Date), 2022)
GO
INSERT Calendar ([SID], [CalDate], [FYN]) VALUES (567, CAST(N'2022-02-26' AS Date), 2022)
GO
INSERT Calendar ([SID], [CalDate], [FYN]) VALUES (577, CAST(N'2022-02-27' AS Date), 2022)
GO
INSERT Calendar ([SID], [CalDate], [FYN]) VALUES (587, CAST(N'2022-02-28' AS Date), 2022)
GO
INSERT Calendar ([SID], [CalDate], [FYN]) VALUES (597, CAST(N'2022-03-01' AS Date), 2022)
GO
INSERT Calendar ([SID], [CalDate], [FYN]) VALUES (607, CAST(N'2022-03-02' AS Date), 2022)
GO
INSERT Calendar ([SID], [CalDate], [FYN]) VALUES (617, CAST(N'2022-03-03' AS Date), 2022)
GO
INSERT Calendar ([SID], [CalDate], [FYN]) VALUES (627, CAST(N'2022-03-04' AS Date), 2022)
GO
INSERT Calendar ([SID], [CalDate], [FYN]) VALUES (637, CAST(N'2022-03-05' AS Date), 2022)
GO
INSERT Calendar ([SID], [CalDate], [FYN]) VALUES (647, CAST(N'2022-03-06' AS Date), 2022)
GO
INSERT FAR ([ItemNo], [StatusDate], [MthDepr], [YTDDepr]) VALUES (N'88190', CAST(N'2022-02-28' AS Date), CAST(23.24 AS Decimal(15, 2)), CAST(255.64 AS Decimal(15, 2)))
GO
INSERT FAR ([ItemNo], [StatusDate], [MthDepr], [YTDDepr]) VALUES (N'88191', CAST(N'2022-02-28' AS Date), CAST(77.84 AS Decimal(15, 2)), CAST(856.24 AS Decimal(15, 2)))
GO
INSERT FAR ([ItemNo], [StatusDate], [MthDepr], [YTDDepr]) VALUES (N'88190', CAST(N'2022-03-01' AS Date), CAST(0.83 AS Decimal(15, 2)), CAST(256.47 AS Decimal(15, 2)))
GO
INSERT FAR ([ItemNo], [StatusDate], [MthDepr], [YTDDepr]) VALUES (N'88191', CAST(N'2022-03-01' AS Date), CAST(2.78 AS Decimal(15, 2)), CAST(859.02 AS Decimal(15, 2)))
GO
INSERT FAR ([ItemNo], [StatusDate], [MthDepr], [YTDDepr]) VALUES (N'88190', CAST(N'2022-03-02' AS Date), CAST(1.66 AS Decimal(15, 2)), CAST(257.30 AS Decimal(15, 2)))
GO
INSERT FAR ([ItemNo], [StatusDate], [MthDepr], [YTDDepr]) VALUES (N'88191', CAST(N'2022-03-02' AS Date), CAST(5.56 AS Decimal(15, 2)), CAST(861.80 AS Decimal(15, 2)))
GO
INSERT FAR ([ItemNo], [StatusDate], [MthDepr], [YTDDepr]) VALUES (N'88190', CAST(N'2022-03-03' AS Date), CAST(2.49 AS Decimal(15, 2)), CAST(258.13 AS Decimal(15, 2)))
GO
INSERT FAR ([ItemNo], [StatusDate], [MthDepr], [YTDDepr]) VALUES (N'88191', CAST(N'2022-03-03' AS Date), CAST(8.34 AS Decimal(15, 2)), CAST(870.14 AS Decimal(15, 2)))
GO
8

It means the last day of that month has passed for that FYN. So for today, 7th March 2022, it means, get the MthDepr for 30th April 2021, 31st May 2021, 30th June 2021 etc. up to 28th February 2022, THEN ADD the MthDepr for today - 7th March 2022, to get YTDDepr.

9 

SELECT
    f.ItemNo,
    f.StatusDate,
    f.MthDepr,
    SUM(f.MthDepr) OVER(PARTITION BY f.ItemNo, c.FYN ORDER BY f.StatusDate 
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS YTDDepr
FROM dbo.FAR f
INNER JOIN dbo.Calendar c ON c.CalDate = f.StatusDate
10

Btw, you'll need these adjustments to the sample data to get a matching test result:


/* set up starting values as reflected in the expected results */
INSERT INTO dbo.FAR
SELECT '88190', '2022-01-31', 255.64 - 23.24, NULL
WHERE NOT EXISTS(SELECT 1 FROM dbo.FAR WHERE ItemNo = '88190' AND StatusDate = '2022-01-31')
SELECT '88191', '2022-01-31', 856.24 - 77.84, NULL
WHERE NOT EXISTS(SELECT 1 FROM dbo.FAR WHERE ItemNo = '88191' AND StatusDate = '2022-01-31')

INSERT INTO dbo.Calendar ([SID], [CalDate], [FYN])
SELECT 544, CAST(N'2022-01-31' AS Date), 2022
WHERE NOT EXISTS(SELECT 1 FROM dbo.Calendar WHERE CalDate = '2022-01-31')
1 Like
11

Hi Scott

Thank you for your help - this works for me in my tests so far.

Best regards
Martyn

12

Great, glad it helped.

Btw, the SUM() ... OVER()is is a "windowed" function and the good news is that performance on those is usually very good. SQL Server is optimized to handle those types of queries.