December 6, 2012 at 4:37 pm
Hello all!
I am trying to figure out how to increment an identity value only if a record exists. Let me give some tables and stored procedures and explain a bit further.
Source:
USE [TEST_DATABASE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Source](
[Request_ID] [int] NULL,
[Request_Date] [datetime] NULL,
[Request_Status] [nvarchar](10) NULL,
) ON [PRIMARY]
GO
Destination:
USE [TEST_DATABASE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Dest](
[Request_ID] [int] NULL,
[Request_Date] [datetime] NULL,
[Request_Status] [nvarchar](10) NULL
[PullDateTime] [datetime] NULL,
[FY] [int] NULL,
[FP] [int] NULL,
[Pull#] [int] NULL
) ON [PRIMARY]
GO
"FY" is the fiscal year from a lookup table and "FP" is the financial period from the lookup table.
This is an example of many source and destination tables that will be used.
For each source and destination I have a stored procedure like the following:
USE [TEST_DATABASE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_Insert_Dest]
@PullDateTime Datetime,
@FY INT,
@FP INT,
@Pull# INT
AS BEGIN
SET NOCOUNT ON;
INSERT INTO TEST_DATABASE.dbo.Source
SELECT
Request_ID,
Request_Date,
Request_Status,
@PullDateTime AS PullDateTime,
@FY AS FY,
@FP AS FP,
@Pull# AS Pull#
FROM TEST_DATABASE.dbo.Source
END
GO
Since this is just one of many insert stored procedures I need to run periodically, I will use one master stored procedure to call all the insert stored procedures:
USE TEST_DATABASE
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE sp_Run_Inserts
@PullDatetime Datetime,
@FY int,
@FP int,
@Pull# INT
AS BEGIN
SET NOCOUNT ON;
DECLARE
@Pull_date = Getdate(),
@Fiscal_Year int = (Select FY FROM FiscalCalendar WHERE GETDATE() BETWEEN 2012-11-01 AND 2012-11-30),
@Fiscal_Period int = (SELECT FP FROM FiscalCalendar WHERE GETDATE() BETWEEN 2012-11-01 AND 2012-11-30),
@Pull_# = ?????? <---Here is my issue
EXEC sp_Insert_Dest @Pull_date ,@Fiscal_Year,@Fiscal_Period,@Pull_#
END
What I am trying to accomplish is when I run the master stored procedure I get the FY, FP and the PullDateTime. I need to add the pull#, starting at 1. If within the same fiscal year and financial period I run the same stored procedure I need to increment the Pull# by 1.
Next month the same table will be used to store that months records. I need reset the pull# back to 1 and if the same fiscal period is run again then increment to 2. Changes may be made to the source and I need to keep a historical record in the destination table.
Since I need to reset that pull# back to 1 each time a new fiscal year and or financial period is used, I cannot use a simple ID column.
I looked at @@identity, ident_current, scope_identity and I am lost! Please let me know if something is unclear or if you need more info! TIA!
-Squid
December 6, 2012 at 4:45 pm
I forgot to mention, that the master stored procedure will be called from a scheduled job calling the following t-sql:
exec sp_Run_Inserts null,null,null,null
This way it runs without any inputs needed each month or on demand from the users.
December 6, 2012 at 5:01 pm
I'm not sure that this is great for performance, but you could do something like this.
I might be wrong, so you should test with your data.
USE [TEST_DATABASE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_Insert_Dest]
@PullDateTime Datetime,
@FY INT,
@FP INT
AS BEGIN
SET NOCOUNT ON;
INSERT INTO TEST_DATABASE.dbo.Source
SELECT
Request_ID,
Request_Date,
Request_Status,
@PullDateTime AS PullDateTime,
@FY AS FY,
@FP AS FP,
Request_ID - (SELECT MAX(Request_ID)
FROM TEST_DATABASE.dbo.Source
WHERE FY <= @FY OR FP < @FP) AS Pull#
FROM TEST_DATABASE.dbo.Source
END
GO
One thing that troubles me is the way you get @FY and @FP
@Fiscal_Year int = (Select FY FROM FiscalCalendar WHERE GETDATE() BETWEEN 2012-11-01 AND 2012-11-30),
@Fiscal_Period int = (SELECT FP FROM FiscalCalendar WHERE GETDATE() BETWEEN 2012-11-01 AND 2012-11-30),
That doesn't seem correct, I hope that you don't have the date literals in your code and you have fields/columns instead.
December 6, 2012 at 5:11 pm
Luis Cazares (12/6/2012)
I'm not sure that this is great for performance, but you could do something like this.I might be wrong, so you should test with your data.
USE [TEST_DATABASE]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_Insert_Dest]
@PullDateTime Datetime,
@FY INT,
@FP INT
AS BEGIN
SET NOCOUNT ON;
INSERT INTO TEST_DATABASE.dbo.Source
SELECT
Request_ID,
Request_Date,
Request_Status,
@PullDateTime AS PullDateTime,
@FY AS FY,
@FP AS FP,
Request_ID - (SELECT MAX(Request_ID)
FROM TEST_DATABASE.dbo.Source
WHERE FY <= @FY OR FP < @FP) AS Pull#
FROM TEST_DATABASE.dbo.Source
END
GO
One thing that troubles me is the way you get @FY and @FP
@Fiscal_Year int = (Select FY FROM FiscalCalendar WHERE GETDATE() BETWEEN 2012-11-01 AND 2012-11-30),
@Fiscal_Period int = (SELECT FP FROM FiscalCalendar WHERE GETDATE() BETWEEN 2012-11-01 AND 2012-11-30),
That doesn't seem correct, I hope that you don't have the date literals in your code and you have fields/columns instead.
Luis, thanks, I will have a look at this properly later, when my brain is not as fried as it is now! (been a long week!)
I put the literals in as place holder to simplify things in my example. I have a lookup table that has the fiscal year, financial period, current report start date and report end date. I use variables to call the report start date and end date. I then use getdate() to lookup where the variable falls into the current report period.
@ReportStartDate and @ReportEndDate
My actual code uses: @Fiscal_Year int = (Select FY FROM FiscalCalendar WHERE GETDATE() BETWEEN @ReportStartDate AND @ReportEndDate)
This way if the fiscal calendar is ever changed, I change the lookup table and my code is not broken 🙂 Does that make sense?
December 6, 2012 at 5:38 pm
That doesn't make sense either.
Your not using columns from your table to filter, you're getting the last row returned by SQL Server (which might not always be the one you expect).
Unless I'm wrong, you might have troubles with that.
December 6, 2012 at 5:55 pm
Luis Cazares (12/6/2012)
That doesn't make sense either.Your not using columns from your table to filter, you're getting the last row returned by SQL Server (which might not always be the one you expect).
Unless I'm wrong, you might have troubles with that.
I posted the lookup code wrong, here is the real code:
@_FY int = (Select FY FROM FiscalCalendar WHERE GETDATE() BETWEEN CurrentStartDate AND CurrentEndDate)
Here is the format of my lookup table:
USE [CMMS360]
GO
/****** Object: Table [dbo].[FiscalCalendar] Script Date: 12/06/2012 16:41:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FiscalCalendar](
[FY] [int] NULL,
[FP] [int] NULL,
[ReportStartDate] [datetime] NULL,
[ReportEndDate] [datetime] NULL,
[CurrentStartDate] [datetime] NULL,
[CurrentEndDate] [datetime] NULL
) ON [PRIMARY]
GO
I have this loaded out for every month range I use.
Here is the data:
INSERT INTO FiscalCalendar
'2013','1','Jul 1 2012 12:00AM','Jul 31 2012 11:59PM','Aug 1 2012 12:00AM','Aug 31 2012 11:59PM', Union All
Select'2013','2','Aug 1 2012 12:00AM','Aug 31 2012 11:59PM','Sep 1 2012 12:00AM','Sep 30 2012 11:59PM', Union All
Select'2013','3','Sep 1 2012 12:00AM','Sep 30 2012 11:59PM','Oct 1 2012 12:00AM','Oct 31 2012 11:59PM', Union All
Select'2013','4','Oct 1 2012 12:00AM','Oct 31 2012 11:59PM','Nov 1 2012 12:00AM','Nov 30 2012 11:59PM', Union All
Select'2013','5','Nov 1 2012 12:00AM','Nov 30 2012 11:59PM','Dec 1 2012 12:00AM','Dec 31 2012 11:59PM', Union All
Select'2013','6','Dec 1 2012 12:00AM','Dec 31 2012 11:59PM','Jan 1 2013 12:00AM','Jan 31 2013 11:59PM', Union All
Select'2013','7','Jan 1 2013 12:00AM','Jan 31 2013 11:59PM','Feb 1 2013 12:00AM','Feb 28 2013 11:59PM', Union All
Select'2013','8','Feb 1 2013 12:00AM','Feb 28 2013 11:59PM','Mar 1 2013 12:00AM','Mar 31 2013 11:59PM', Union All
Select'2013','9','Mar 1 2013 12:00AM','Mar 31 2013 11:59PM','Apr 1 2013 12:00AM','Apr 30 2013 11:59PM', Union All
Select'2013','10','Apr 1 2013 12:00AM','Apr 30 2013 11:59PM','May 1 2013 12:00AM','May 31 2013 11:59PM', Union All
Select'2013','11','May 1 2013 12:00AM','May 31 2013 11:59PM','Jun 1 2013 12:00AM','Jun 30 2013 11:59PM', Union All
Select'2013','12','Jun 1 2013 12:00AM','Jun 30 2013 11:59PM','Jul 1 2013 12:00AM','Jul 31 2013 11:59PM', Union All
Select'2014','1','Jul 1 2013 12:00AM','Jul 31 2013 11:59PM','Aug 1 2013 12:00AM','Aug 31 2013 11:59PM', Union All
Select'2014','2','Aug 1 2013 12:00AM','Aug 31 2013 11:59PM','Sep 1 2013 12:00AM','Sep 30 2013 11:59PM', Union All
Select'2014','3','Sep 1 2013 12:00AM','Sep 30 2013 11:59PM','Oct 1 2013 12:00AM','Oct 31 2013 11:59PM', Union All
Select'2014','4','Oct 1 2013 12:00AM','Oct 31 2013 11:59PM','Nov 1 2013 12:00AM','Nov 30 2013 11:59PM', Union All
Select'2014','5','Nov 1 2013 12:00AM','Nov 30 2013 11:59PM','Dec 1 2013 12:00AM','Dec 31 2013 11:59PM', Union All
Select'2014','6','Dec 1 2013 12:00AM','Dec 31 2013 11:59PM','Jan 1 2014 12:00AM','Jan 31 2014 11:59PM', Union All
Select'2014','7','Jan 1 2014 12:00AM','Jan 31 2014 11:59PM','Feb 1 2014 12:00AM','Feb 28 2014 11:59PM', Union All
Select'2014','8','Feb 1 2014 12:00AM','Feb 28 2014 11:59PM','Mar 1 2014 12:00AM','Mar 31 2014 11:59PM', Union All
Select'2014','9','Mar 1 2014 12:00AM','Mar 31 2014 11:59PM','Apr 1 2014 12:00AM','Apr 30 2014 11:59PM'
December 6, 2012 at 6:04 pm
December 7, 2012 at 1:09 pm
Thanks for your help Luis. I've ended up doing this a bit differently and will pass the incremented pull# manually for now. You've given me food for thought for next time I attempt to automate this process 🙂 Thanks again!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply