Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Data in date column to decrement with one Expand / Collapse
Author
Message
Posted Wednesday, July 3, 2013 6:17 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 5:00 PM
Points: 26, Visits: 275
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.
Post #1470273
Posted Thursday, July 4, 2013 12:04 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 25, 2014 6:54 PM
Points: 1,193, Visits: 1,666
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

Post #1470310
Posted Thursday, July 4, 2013 11:00 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 5:00 PM
Points: 26, Visits: 275
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
Post #1470555
Posted Thursday, July 4, 2013 11:49 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 27, 2013 5:37 AM
Points: 306, Visits: 524
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
Post #1470561
Posted Friday, July 5, 2013 1:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:46 AM
Points: 7,224, Visits: 13,696
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.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1470604
Posted Friday, July 5, 2013 5:39 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:13 PM
Points: 36,995, Visits: 31,514
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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1470888
Posted Sunday, July 7, 2013 5:59 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 5:00 PM
Points: 26, Visits: 275
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

IF EXISTS (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

IF EXISTS (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*/
SELECT IDENTITY (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

ON CostAccrual.[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
ON BusinessPartnerRefTypes.BusinessPartnerReferenceValue = CostAccrual.[Vendor Id]

WHERE
CostAccrual.Amount >= 100 -- As per email from Don


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





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


/*Select statement to create the monthly accruals report*/
SELECT Convert(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'
INTO dbo.testmonthlyacrr
FROM [trn].[temp_tblMonthlyAccrr] MonthlyAccruals



END

GO

Post #1471003
Posted Sunday, July 7, 2013 10:06 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 25, 2014 6:54 PM
Points: 1,193, Visits: 1,666
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

Post #1471028
Posted Monday, July 8, 2013 5:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:46 AM
Points: 7,224, Visits: 13,696
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




“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1471123
Posted Monday, July 8, 2013 8:37 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, August 6, 2014 5:00 PM
Points: 26, Visits: 275
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)
-1 15/10/2013
-2 14/10/2013
-3 13/10/2013
-4 12/10/2013
-5 11/10/2013

it keep on decrementing the accrualdate till the last record.
apologies if it is still not clear hope this help you .
Post #1471423
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse