July 3, 2013 at 6:17 pm
Hi, problem. I have a table that is having composite primary key e.g
startdate, counterparty, center, costname, currency, month, year
start date is having data till 31-05-2013.
i want to change 31-05-2013 to 30-05-2013,
30-05-2013 to 29-05-2013,
29-05-2013 to 28-05-2013
28-05-2013 to 27-05-2013
and keep going backward till the last row of the table.
how can i achive this using either a stored proc or a query . Please help in this regards.
really appreciate if some one can help.
thanks.
July 4, 2013 at 12:04 am
Is there any reason you cant just update the whole table in one statement? No problem with the composite primary key that way.
update tablename
set startdate= dateadd(d,-1,startdate)
from tablename
July 4, 2013 at 11:00 pm
thanks for your quick response. really appreciate that.
This will solve it but urpose is not achieved. i am using a cursor to fetch records with combination of primary keys and want to change the date to start off from 2013-05-301 . means
table is having 57000 records.
it will pick first row based on pk and changed its date from 2013-05-31 it is to 2013-05-30 then next row
changing it from 2013-05-30 to 2013-05-329 and so on till it finished all resulting in all unique columns.
hope you understand the situation 🙁
July 4, 2013 at 11:49 pm
asifejaz (7/4/2013)
thanks for your quick response. really appreciate that.This will solve it but urpose is not achieved. i am using a cursor to fetch records with combination of primary keys and want to change the date to start off from 2013-05-301 . means
table is having 57000 records.
it will pick first row based on pk and changed its date from 2013-05-31 it is to 2013-05-30 then next row
changing it from 2013-05-30 to 2013-05-329 and so on till it finished all resulting in all unique columns.
hope you understand the situation 🙁
Please clear what you want exactly,
As it does`t clear with what you have written above.
Neeraj Prasad Sharma
Sql Server Tutorials
July 5, 2013 at 1:20 am
asifejaz (7/4/2013)
thanks for your quick response. really appreciate that.This will solve it but urpose is not achieved. i am using a cursor to fetch records with combination of primary keys and want to change the date to start off from 2013-05-301 . means
table is having 57000 records.
it will pick first row based on pk and changed its date from 2013-05-31 it is to 2013-05-30 then next row
changing it from 2013-05-30 to 2013-05-329 and so on till it finished all resulting in all unique columns.
hope you understand the situation 🙁
Favouring a cursor-driven method over a set-based method for a simple one-table update suggests that updating the date column isn't the only step in the process or the description of your problem is missing significant detail. Please elaborate.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 5, 2013 at 5:39 pm
asifejaz (7/4/2013)
thanks for your quick response. really appreciate that.This will solve it but urpose is not achieved. i am using a cursor to fetch records with combination of primary keys and want to change the date to start off from 2013-05-301 . means
table is having 57000 records.
it will pick first row based on pk and changed its date from 2013-05-31 it is to 2013-05-30 then next row
changing it from 2013-05-30 to 2013-05-329 and so on till it finished all resulting in all unique columns.
hope you understand the situation 🙁
You do not need the cursor for that simple task. As already asked, what else is the cursor doing? At this point, I recommend you post your code and as much information about the columns in the table the cursor is using as possible.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 7, 2013 at 5:59 pm
hi , thanks for it. here is the details . table design, stored procedure that should be called to do the update based on composite primary key to get a unique combination
CREATE TABLE [dbo].[Monthlyaccr](
[AccrualDate] [date] NOT NULL,
[CostName] [varchar](100) NOT NULL,
[IncomeExpense] [varchar](50) NOT NULL,
[Counterparty] [varchar](65) NOT NULL,
[Memo] [varchar](100) NOT NULL,
[AccrualMonth] [varchar](3) NOT NULL,
[Year] [varchar](4) NOT NULL,
[AccrualAmount] [decimal](10, 2) NOT NULL,
[AccrualCurrency] [varchar](15) NOT NULL,
[AccrualFXtoBase] [decimal](10, 4) NOT NULL,
[ProfitCenter] [varchar](30) NOT NULL,
[Strategy] [varchar](30) NOT NULL,
[__not_for_extract_Corporate] [varchar](6) NOT NULL,
CONSTRAINT [PK_MonthlyAccrs] PRIMARY KEY CLUSTERED
(
[AccrualDate] ASC,
[CostName] ASC,
[IncomeExpense] ASC,
[Counterparty] ASC,
[AccrualMonth] ASC,
[Year] ASC,
[AccrualCurrency] ASC,
[ProfitCenter] ASC,
[Strategy] ASC,
[__not_for_extract_Corporate] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[MonthlyAccrs] WITH CHECK ADD CONSTRAINT [FK_MonthlyAccrs_CostComponents] FOREIGN KEY([CostName])
REFERENCES [dbo].[CostComponents] ([CostComponent])
GO
ALTER TABLE [dbo].[MonthlyAccrs] CHECK CONSTRAINT [FK_MonthlyAccrs_CostComponents]
GO
ALTER TABLE [dbo].[MonthlyAccrs] WITH CHECK ADD CONSTRAINT [FK_MonthlyAccrs_Counterparty] FOREIGN KEY([Counterparty])
REFERENCES [dbo].[BusinessPartnerSetup] ([BusinessPartnerShortName])
GO
ALTER TABLE [dbo].[MonthlyAccrs] CHECK CONSTRAINT [FK_MonthlyAccrs_Counterparty]
GO
ALTER TABLE [dbo].[MonthlyAccrs] WITH CHECK ADD CONSTRAINT [FK_MonthlyAccrs_Currencies] FOREIGN KEY([AccrualCurrency])
REFERENCES [dbo].[Currencies] ([CurrencyCode])
GO
ALTER TABLE [dbo].[MonthlyAccrs] CHECK CONSTRAINT [FK_MonthlyAccrs_Currencies]
GO
ALTER TABLE [dbo].[MonthlyAccrs] WITH CHECK ADD CONSTRAINT [FK_MonthlyAccrs_Strategies] FOREIGN KEY([__not_for_extract_Corporate], [Strategy])
REFERENCES [dbo].[Strategies] ([Corporate], [Strategy])
GO
ALTER TABLE [dbo].[MonthlyAccrs] CHECK CONSTRAINT [FK_MonthlyAccrs_Strategies]
GO
ALTER TABLE [dbo].[MonthlyAccrs] WITH CHECK ADD CONSTRAINT [CK_MonthlyAccrs_AccrualMonth] CHECK (([AccrualMonth]='Dec' OR [AccrualMonth]='Nov' OR [AccrualMonth]='Oct' OR [AccrualMonth]='Sep' OR [AccrualMonth]='Aug' OR [AccrualMonth]='Jul' OR [AccrualMonth]='Jun' OR [AccrualMonth]='May' OR [AccrualMonth]='Apr' OR [AccrualMonth]='Mar' OR [AccrualMonth]='Feb' OR [AccrualMonth]='Jan'))
GO
ALTER TABLE [dbo].[MonthlyAccrs] CHECK CONSTRAINT [CK_MonthlyAccrs_AccrualMonth]
GO
ALTER TABLE [dbo].[MonthlyAccrs] WITH CHECK ADD CONSTRAINT [CK_MonthlyAccrs_IncomeExpense] CHECK (([IncomeExpense]='Expense' OR [IncomeExpense]='Income'))
GO
ALTER TABLE [dbo].[MonthlyAccrs] CHECK CONSTRAINT [CK_MonthlyAccrs_IncomeExpense]
GO
ALTER TABLE [dbo].[MonthlyAccrs] WITH CHECK ADD CONSTRAINT [CK_MonthlyAccrs_Year] CHECK (([Year] like '19__' OR [Year] like '20__' OR [Year] like '21__'))
GO
ALTER TABLE [dbo].[MonthlyAccrs] CHECK CONSTRAINT [CK_MonthlyAccrs_Year]
GO
=======================
stored procedure to update the records in the the table using cursor and temp table.
CREATE PROCEDURE [trn].[test]
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @tblMonthlyAccruals sysname
DECLARE @tblMonthlyAccrualsReport sysname
DECLARE @sqlText varchar(2048)
--DECLARE @tempTablename sysname
SET@tblMonthlyAccruals = '[trn].[temp_tblMonthlyAccr]'
--SET@tblMonthlyAccrualsReport = '[trn].[tblMonthlyAccr]'
SET@sqlText = 'DROP TABLE ' + @tblMonthlyAccruals
IFEXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@tblMonthlyAccr) AND type in (N'U'))
BEGIN
print @sqlText
exec( @sqlText)
END
ELSE
PRINT@sqlText + ' - has not been executed as it does not exist.'
SET@sqlText = 'DROP TABLE ' + @tblMonthlyAccrualsReport
IFEXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(@tblMonthlyAccrualsReport) AND type in (N'U'))
BEGIN
print @sqlText
exec( @sqlText)
END
ELSE
PRINT@sqlText + ' - has not been executed as it does not exist.'
/*Copying data from Source table to table*/
SELECTIDENTITY (INT,1,1) AS VirtualID,--temp id
COALESCE((CONVERT(date,CostAccrual.[Transaction Date],106)),CONVERT(date,'1900-01-01',102)) as 'AccrualDate',
case
WHEN CostAccrual.[Cost Category] = 'Freight' and LTRIM(RTRIM(CostAccrual.[Vendor Full Name])) in ('ADM Clearing','test Operations - tRail')
THEN
'Rail Freight-EOM'
WHEN CostAccrual.[Cost Category] = 'Ft' and LTRIM(RTRIM(CostAccrual.[Vendor Full Name])) = 'Road Freight Trader'
THEN
'Road Ft-EOM'
ELSE
COALESCE(CostComponentsMapping.[EKACostComponentName],'<ErrorInCostName>')
end as 'CostName' ,
'Expense' as 'IncomeExpense',
COALESCE(BusinessPartnerRefTypes.BusinessPartnerShortName,'<ErrorInCounterparty>') as 'Counterparty',
case
when CostAccrual.[Cost Category] = 'Ft' Then COALESCE(CostAccrual.[Vehicle Id],'<ErrorInCostCategory>')
else COALESCE(cast(cast(CostAccrual.[Transaction No]as bigint)as varchar(255)),'<ErrorInCostCategory>')
end as 'Memo',
COALESCE(CONVERT(varchar(3),CostAccrual.[Transaction Date],107) ,'<ErrorInAccrualMonth>')as 'AccrualMonth',
COALESCE(CONVERT(varchar(4),CostAccrual.[Transaction Date],120),'<ErrorInYear>') as 'Year',
COALESCE(CONVERT(decimal(10,4),CostAccrual.[Total]),0) as 'AccrualAmount',
COALESCE(CostAccrual.[Currency],'<ErrorInAccrualCurrency>') as 'AccrualCurrency',
end as 'AccrualFXtoBase',
--COALESCE(CONVERT(decimal(10,4),[Rate]),0) as 'AccrualFXtoBase',
COALESCE(ProfitCenterMapping.[ProfitCenterName],'<ErrorInProfitCenter>') as 'ProfitCenter',
'abc' as 'Strategy',
'GCO' as '__not_for_extract_Corporate'
INTO[trn].[temp_tblMonthlyAccruals]
FROM[source].[CostAccrualReport] CostAccrual
LEFT OUTER JOIN[lookup].[ProfitCenterMapping] ProfitCenterMapping0
ONCostAccrual.[ID Centre]=ProfitCenterMapping.[ProfitCenter]
LEFT OUTER JOIN[lookup].[CostComponentsMapping] CostComponentsMapping
ON(CostAccrual.[Cost Category]=CostComponentsMapping.[GSCostComponentName]
and (CostAccrual.[Cost Category] <> 'Freight'
and CostAccrual.[Vendor Full Name] not in ('test Operations - Rail','Road Freight Trader')))
LEFT OUTER JOIN [dbo].[BusinessPartnerRefTypes] BusinessPartnerRefTypes
ONBusinessPartnerRefTypes.BusinessPartnerReferenceValue = CostAccrual.[Vendor Id]
WHERE
CostAccrual.Amount >= 100-- As per email from Don
DECLARE @Counter INT = 0
DECLARE @VirtualIDINT
DECLARE @VirtualID_curCURSOR
SET @VirtualID_cur= CURSOR FOR
SELECT VirtualID FROM [trn].[temp_tblMonthlyAccruals]
OPEN @VirtualID_cur
FETCH NEXT
FROM @VirtualID_cur INTO @VirtualID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @VirtualID
UPDATE[trn].[temp_tblMonthlyAccruals]
SET[AccrualDate] = DATEADD(dd, @Counter, '31-May-2013')
WHEREVirtualID = @VirtualID
SET @Counter = @Counter - 1
FETCH NEXT FROM @VirtualID_cur INTO @VirtualID
END
CLOSE @VirtualID_cur
DEALLOCATE @VirtualID_cur
WHILE EXISTS(SELECT[AccrualDate],
[Counterparty],
[ProfitCenter],
[CostName],
[AccrualCurrency]
FROM[trn].[temp_tblMonthlyAccruals]
GROUP BY [AccrualDate],
[Counterparty],
[ProfitCenter],
[CostName],
[AccrualCurrency]
HAVING count(*) > 1)
BEGIN
SET ROWCOUNT 1
UPDATE[trn].[temp_tblMonthlyAccruals]
SET[AccrualDate] = DATEADD(D, -1, [AccrualDate]) --changed day to d
FROM[trn].[temp_tblMonthlyAccruals] ma1
WHERE[AccrualDate] = ma1.[AccrualDate]
ANDEXISTS(SELECT[AccrualDate],
[Counterparty],
[ProfitCenter],
[CostName],
[AccrualCurrency]
FROM[trn].[temp_tblMonthlyAccruals] ma2
WHEREma1.[AccrualDate]=ma2.[AccrualDate]
andma1.[Counterparty]=ma2.[Counterparty]
andma1.[ProfitCenter]=ma2.[ProfitCenter]
andma1.[CostName]=ma2.[CostName]
andma1.[AccrualCurrency]=ma2.[AccrualCurrency]
GROUP BY ma2.[AccrualDate],
ma2.[Counterparty],
ma2.[ProfitCenter],
ma2.[CostName],
ma2.[AccrualCurrency]
HAVING count(*) > 1
)
SET ROWCOUNT 0
END
/*Select statement to create the monthly accruals report*/
SELECTConvert(char(2),MonthlyAccruals.[Accrualdate],106)+'-'+CONVERT(char(3),MonthlyAccruals.[Accrualdate],107)+'-'+CONVERT(char(4),MonthlyAccruals.[Accrualdate],120) as 'Accrual date',
MonthlyAccruals.[CostName] as 'Cost Name' ,
MonthlyAccruals.[IncomeExpense] as 'Income/Expense',
MonthlyAccruals.[Counterparty] as 'Counterparty',
MonthlyAccruals.[Memo] as 'Memo',
MonthlyAccruals.[AccrualMonth] as 'Accrual Month',
MonthlyAccruals.[Year] as 'Year',
MonthlyAccruals.[AccrualAmount] as 'Accrual Amount',
MonthlyAccruals.[AccrualCurrency] as 'Accrual Currency',
MonthlyAccruals.[AccrualFXtoBase] as 'Accrual to Base',
MonthlyAccruals.[ProfitCenter] as 'Profit Center',
MonthlyAccruals.[Strategy] as 'Strategy'
INTOdbo.testmonthlyacrr
FROM[trn].[temp_tblMonthlyAccrr] MonthlyAccruals
END
GO
July 7, 2013 at 10:06 pm
Currently this code is randomising the Accrualdate as there is no ORDER BY when populating the cursor:
Can you explain the purpose of this code?
DECLARE @Counter INT = 0
DECLARE @VirtualID INT
DECLARE @VirtualID_cur CURSOR
SET @VirtualID_cur = CURSOR FOR
SELECT VirtualID FROM [trn].[temp_tblMonthlyAccruals]
OPEN @VirtualID_cur
FETCH NEXT
FROM @VirtualID_cur INTO @VirtualID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @VirtualID
UPDATE [trn].[temp_tblMonthlyAccruals]
SET [AccrualDate] = DATEADD(dd, @Counter, '31-May-2013')
WHERE VirtualID = @VirtualID
SET @Counter = @Counter - 1
FETCH NEXT FROM @VirtualID_cur INTO @VirtualID
END
CLOSE @VirtualID_cur
DEALLOCATE @VirtualID_cur
I'm guessing the above code is designed to replace this:
WHILE EXISTS( SELECT [AccrualDate],
[Counterparty],
[ProfitCenter],
[CostName],
[AccrualCurrency]
FROM [trn].[temp_tblMonthlyAccruals]
GROUP BY [AccrualDate],
[Counterparty],
[ProfitCenter],
[CostName],
[AccrualCurrency]
HAVING count(*) > 1)
BEGIN
SET ROWCOUNT 1
UPDATE [trn].[temp_tblMonthlyAccruals]
SET [AccrualDate] = DATEADD(D, -1, [AccrualDate]) --changed day to d
FROM [trn].[temp_tblMonthlyAccruals] ma1
WHERE [AccrualDate] = ma1.[AccrualDate]
AND EXISTS(SELECT [AccrualDate],
[Counterparty],
[ProfitCenter],
[CostName],
[AccrualCurrency]
FROM [trn].[temp_tblMonthlyAccruals] ma2
WHERE ma1.[AccrualDate]=ma2.[AccrualDate]
and ma1.[Counterparty]=ma2.[Counterparty]
and ma1.[ProfitCenter]=ma2.[ProfitCenter]
and ma1.[CostName]=ma2.[CostName]
and ma1.[AccrualCurrency]=ma2.[AccrualCurrency]
GROUP BY ma2.[AccrualDate],
ma2.[Counterparty],
ma2.[ProfitCenter],
ma2.[CostName],
ma2.[AccrualCurrency]
HAVING count(*) > 1
)
SET ROWCOUNT 0
END
July 8, 2013 at 5:34 am
Here's a little test harness. It's a simplification of your problem - all you have to play with is the accrual date and the delta you want to apply to it.
It won't meet your requirement, but it will help you to describe your issue to us:
WITH SampleData AS (
SELECT TOP 1000 [AccrualDate] = CAST(DATEADD(d,ABS(checksum(NEWID())) % 100,GETDATE()) AS DATE)
FROM sys.columns
)
SELECT
AccrualDate,
delta1,
DATEADD(d,delta1,AccrualDate),
delta2,
DATEADD(d,delta2,AccrualDate)
FROM (
SELECT
[AccrualDate],
[delta1] = 0-DENSE_RANK() OVER(ORDER BY [AccrualDate] DESC),
[delta2] = 0-ROW_NUMBER() OVER(ORDER BY [AccrualDate] DESC)
FROM SampleData
) g
ORDER BY [AccrualDate] DESC
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 8, 2013 at 8:37 pm
Thanks for your help. Really appreciate that. I dragged in the project that is at its end before go live..
I ran the script and the out put that appears in last column after delta2 starting from 2013-10-15
and goes down decrementing by 1 day is the requirement
delta2(No column name)
-115/10/2013
-214/10/2013
-313/10/2013
-412/10/2013
-511/10/2013
it keep on decrementing the accrualdate till the last record.
apologies if it is still not clear 🙁 hope this help you .
July 9, 2013 at 6:57 am
asifejaz (7/8/2013)
Thanks for your help. Really appreciate that. I dragged in the project that is at its end before go live..I ran the script and the out put that appears in last column after delta2 starting from 2013-10-15
and goes down decrementing by 1 day is the requirement
delta2(No column name)
-115/10/2013
-214/10/2013
-313/10/2013
-412/10/2013
-511/10/2013
it keep on decrementing the accrualdate till the last record.
apologies if it is still not clear 🙁 hope this help you .
Which columns make up your primary key?
WITH Updater AS (
SELECT
[PK list], -- replace this with your list of columns in the composite PK
[AccrualDate],
[NewAccrualDate] = DATEADD(d,0-ROW_NUMBER() OVER(ORDER BY [AccrualDate] DESC),AccrualDate)
FROM SampleData
)
UPDATE Updater SET [AccrualDate] = [NewAccrualDate]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 10, 2013 at 10:58 pm
thanks. Sorry i didnt understand what you mean by this
WITH Updater AS (
pls let me know.
July 11, 2013 at 7:48 am
asifejaz (7/10/2013)
thanks. Sorry i didnt understand what you mean by thisWITH Updater AS (
pls let me know.
That's a Common Table Expression, a CTE[/url]. How long have you been working with SQL Server 2005?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 12, 2013 at 12:04 am
8 months.:angry:
July 12, 2013 at 1:01 am
asifejaz (7/12/2013)
8 months.:angry:
Don't worry, you'll get the hang of it - quicker, if you can attract the attention of someone here on ssc who's in your time zone 😀
Any questions about the proposed solution? Just ask.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply