• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 816
  • Last Modified:

Running Total for fiscal year

I have a function that sets the fiscal year to start on 7/1 regardless of the year.  I have a stored procedure that takes the value from the row MCRRRMDCD and increments MCRYTDRRMCDC by whatever number is in its row.  An example of the data is below.

TDATE                       MCRRRMCDC      MCRYTDRRMCDC
2010-01-05 00:00:00.000      NULL      NULL
2010-01-04 00:00:00.000      NULL      NULL
2010-01-03 00:00:00.000      NULL      NULL
2010-01-02 00:00:00.000      NULL      NULL
2010-01-01 00:00:00.000      NULL      NULL
2009-12-31 00:00:00.000      1      95
2009-12-30 00:00:00.000      1      94
2009-12-29 00:00:00.000      NULL      93
2009-12-28 00:00:00.000      NULL      93
2009-12-27 00:00:00.000      NULL      93
2009-12-26 00:00:00.000      NULL      93
2009-12-25 00:00:00.000      NULL      93
2009-12-24 00:00:00.000      NULL      93
2009-12-23 00:00:00.000      2      93
2009-12-22 00:00:00.000      NULL      91

What my problem is - starting on 1/1/2010, it stops incrementing...  It worked for all previous years.

Here is the function FiscalYear:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[FiscalYear](@date DATETIME) RETURNS INT AS
BEGIN
  DECLARE @fiscalyear INT
  SET @fiscalyear = YEAR(@date) + (CASE WHEN MONTH(@date) >= 7 THEN 1 ELSE 0 END)
  RETURN @fiscalyear
END

Here is the stored procedure that is being used to populate a table that is being updated with new data daily:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spDlyHIHMasterMCRYTDRRMCDC]
AS
BEGIN
--IF EXISTS DROP TABLE TEST1

--IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[tablename]')
--AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
--DROP TABLE [dbo].[TEST1];
--CREATE TABLE [dbo].[tablename] ( columns specification );

DROP TABLE TEST1
--BEGIN
CREATE TABLE TEST1 ([TDATE] datetime, MCRRRMCDC smallint,MCRYTDRRMCDC smallint)
CREATE CLUSTERED INDEX ndxTDate on TEST1([TDATE])

;WITH data AS ( SELECT CONVERT(VARCHAR(4), [TDATE], 120) ym, [TDATE], MCRRRMCDC, ROW_NUMBER() OVER ( PARTITION BY dbo.fiscalyear([TDATE]) ORDER BY [TDATE]) r
FROM dbo.tblHIHMaster)
insert into TEST1
SELECT data.[TDATE], data.MCRRRMCDC
, (SELECT SUM(x.MCRRRMCDC) FROM data x WHERE x.ym = data.ym AND x.r <= data.r ) MCRYTDRRMCDC
 FROM data
 UPDATE tblHIHMaster SET MCRYTDRRMCDC = ISNULL(TEST1.MCRYTDRRMCDC,0) FROM TEST1 WHERE tblHIHMaster.TDATE = TEST1.TDATE
END

Need assistance in figuring out why MCRYTDRRMCDC in table TEST1 stops incrementing from 1/1/2010 going forward...
0
saladart
Asked:
saladart
2 Solutions
 
sameer2010Commented:
Hi,

This is happening because ALL the rows in 2010 have MCRRRMCDC as NULL. Can you try updating that and see if MCRYTDRRMCDC gets updated?
0
 
saladartAuthor Commented:
Question: there are many rows with NULL before 2010 and they are being counted...  Why would the year have any impact on counting like the other years?

I'll put some numbers in 2010 dates and see what happens...
0
 
saladartAuthor Commented:
Ok - I updated the row with the date of 2010-01-01 - set MCRRRMCDC = 1.

What happened is it started incrementing the MCRYTDRRMCDC with 1...  Sounds like something in the FiscalYear function is wrong?  Here are the results now that there is a number in a date in 2010...

TDATE      MCRRRMCDC      row      MCRYTDRRMCDC
2010-01-05 00:00:00.000      NULL      189      1
2010-01-04 00:00:00.000      NULL      188      1
2010-01-03 00:00:00.000      NULL      187      1
2010-01-02 00:00:00.000      NULL      186      1
2010-01-01 00:00:00.000      1      185      1
2009-12-31 00:00:00.000      1      184      95
2009-12-30 00:00:00.000      1      183      94
2009-12-29 00:00:00.000      NULL      182      93
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
saladartAuthor Commented:
The function I used FiscalYear worked just fine up until 01-01-2010...  What other info can I provide to help get a resolution?

Sean
0
 
ralmadaCommented:
I don't see anything wrong with your FiscalYear function, What if you do it like this?
 

WITH data AS 
( SELECT CONVERT(VARCHAR(4), [TDATE], 120) ym, 
	[TDATE], 
	isnull(MCRRRMCDC, 0) MCRRRMCDC, 
	ROW_NUMBER() OVER ( PARTITION BY dbo.fiscalyear([TDATE]) ORDER BY [TDATE]) r 
FROM dbo.tblHIHMaster)

insert into TEST1
SELECT 	data.[TDATE], 
	data.MCRRRMCDC, 
	(SELECT SUM(x.MCRRRMCDC) FROM data x WHERE x.ym = data.ym AND x.r <= data.r ) MCRYTDRRMCDC
 FROM data

Open in new window

0
 
saladartAuthor Commented:
That doesn't work...  Here is the data that it generates...  I am MORE interested in why the function treats 1/1/2010 as a new fiscal year???

TDATE                       MCRRRMCDC      MCRYTDRRMCDC
2010-01-05 00:00:00.000      0      0
2010-01-05 00:00:00.000      NULL      NULL
2010-01-04 00:00:00.000      0      0
2010-01-04 00:00:00.000      NULL      NULL
2010-01-03 00:00:00.000      0      0
2010-01-03 00:00:00.000      NULL      NULL
2010-01-02 00:00:00.000      0      0
2010-01-02 00:00:00.000      NULL      NULL
2010-01-01 00:00:00.000      0      0
2010-01-01 00:00:00.000      NULL      NULL
2009-12-31 00:00:00.000      1      95
2009-12-31 00:00:00.000      1      157
2009-12-30 00:00:00.000      1      94
2009-12-30 00:00:00.000      1      157
2009-12-29 00:00:00.000      0      93
2009-12-29 00:00:00.000      NULL      157
2009-12-28 00:00:00.000      0      93
2009-12-28 00:00:00.000      NULL      157
2009-12-27 00:00:00.000      0      93
2009-12-27 00:00:00.000      NULL      157
2009-12-26 00:00:00.000      0      93
2009-12-26 00:00:00.000      NULL      157
2009-12-25 00:00:00.000      0      93
2009-12-25 00:00:00.000      NULL      157
2009-12-24 00:00:00.000      0      93
2009-12-24 00:00:00.000      NULL      157
2009-12-23 00:00:00.000      2      93
2009-12-23 00:00:00.000      2      157
2009-12-22 00:00:00.000      0      91
2009-12-22 00:00:00.000      NULL      157
2009-12-21 00:00:00.000      0      91
2009-12-21 00:00:00.000      NULL      157
2009-12-20 00:00:00.000      0      91


2009-12-20 00:00:00.000      NULL      157
0
 
Chris LuttrellSenior Database ArchitectCommented:
The problem appears to be in your YM column in your CTE, change it to use the fiscalyear function on the date, it was spliting your fiscal years in half and not working with the x.ym = data.ym in your subselect:
CONVERT(VARCHAR(4), dbo.fiscalyear([TDATE]), 120) ym
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spDlyHIHMasterMCRYTDRRMCDC] 
AS
BEGIN
--IF EXISTS DROP TABLE TEST1

--IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[tablename]')
--AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
--DROP TABLE [dbo].[TEST1];
--CREATE TABLE [dbo].[tablename] ( columns specification );

DROP TABLE TEST1
--BEGIN
CREATE TABLE TEST1 ([TDATE] datetime, MCRRRMCDC smallint,MCRYTDRRMCDC smallint)
CREATE CLUSTERED INDEX ndxTDate on TEST1([TDATE])

;WITH data AS ( SELECT CONVERT(VARCHAR(4), dbo.fiscalyear([TDATE]), 120) ym, [TDATE], MCRRRMCDC, ROW_NUMBER() OVER ( PARTITION BY dbo.fiscalyear([TDATE]) ORDER BY [TDATE]) r 
FROM dbo.tblHIHMaster)
insert into TEST1
SELECT data.[TDATE], data.MCRRRMCDC
, (SELECT SUM(x.MCRRRMCDC) FROM data x WHERE x.ym = data.ym AND x.r <= data.r ) MCRYTDRRMCDC
 FROM data
 UPDATE tblHIHMaster SET MCRYTDRRMCDC = ISNULL(TEST1.MCRYTDRRMCDC,0) FROM TEST1 WHERE tblHIHMaster.TDATE = TEST1.TDATE
END
GO

Open in new window

0
 
Chris LuttrellSenior Database ArchitectCommented:
actually you don't have to do the convert thing at all, just use the value from your function.
;WITH data AS ( SELECT dbo.fiscalyear([TDATE]) ym, [TDATE], MCRRRMCDC, ROW_NUMBER() OVER ( PARTITION BY dbo.fiscalyear([TDATE]) ORDER BY [TDATE]) r 
FROM dbo.tblHIHMaster)
insert into TEST1
SELECT data.[TDATE], data.MCRRRMCDC
, (SELECT SUM(x.MCRRRMCDC) FROM data x WHERE x.ym = data.ym AND x.r <= data.r ) MCRYTDRRMCDC
 FROM data
 UPDATE tblHIHMaster SET MCRYTDRRMCDC = ISNULL(TEST1.MCRYTDRRMCDC,0) FROM TEST1 WHERE tblHIHMaster.TDATE = TEST1.TDATE

Open in new window

0
 
SharathData EngineerCommented:
Why don't you post the sample data from your table tblHIHMaster and expected result?

Instead of your fiscal year function and SP, this may be achieved with simple query.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now