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 Tuesday, July 9, 2013 6:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:52 AM
Points: 6,826, Visits: 14,062
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)
-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 .


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]



“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 #1471593
Posted Wednesday, July 10, 2013 10:58 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:54 PM
Points: 26, Visits: 283
thanks. Sorry i didnt understand what you mean by this

WITH Updater AS (


pls let me know.


Post #1472428
Posted Thursday, July 11, 2013 7:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:52 AM
Points: 6,826, Visits: 14,062
asifejaz (7/10/2013)
thanks. Sorry i didnt understand what you mean by this

WITH Updater AS (


pls let me know.




That's a Common Table Expression, a CTE. How long have you been working with SQL Server 2005?


“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 #1472585
Posted Friday, July 12, 2013 12:04 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:54 PM
Points: 26, Visits: 283
8 months.
Post #1472866
Posted Friday, July 12, 2013 1:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:52 AM
Points: 6,826, Visits: 14,062
asifejaz (7/12/2013)
8 months.


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.


“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 #1472876
Posted Friday, July 12, 2013 1:24 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:54 PM
Points: 26, Visits: 283
the resolution didnt solve it.

it decrement the first row successfully. when moving to second row instead of decrementing it to one it decrements it to two , then to third row it decrements by three. e.g.

actual data to changed data
2013-06-31 to 2013-06-30
2013-06-29 to 2013-06-27
2013-06-25 to 2013-06-21

where as the requirement is to decrement each one by one only.
like
actual data to changed data
2013-06-31 to 2013-06-30
2013-06-29 to 2013-06-28
2013-06-25 to 2013-06-24

i hope you understand..
Post #1472880
Posted Friday, July 12, 2013 1:38 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:52 AM
Points: 6,826, Visits: 14,062
asifejaz (7/12/2013)
the resolution didnt solve it.

it decrement the first row successfully. when moving to second row instead of decrementing it to one it decrements it to two , then to third row it decrements by three. e.g.

actual data to changed data
2013-06-31 to 2013-06-30
2013-06-29 to 2013-06-27
2013-06-25 to 2013-06-21

where as the requirement is to decrement each one by one only.
like
actual data to changed data
2013-06-31 to 2013-06-30
2013-06-29 to 2013-06-28
2013-06-25 to 2013-06-24

i hope you understand..


Isn't this exactly the same as the solution posted by Andrew G, second post on this thread, subtract a day from each date?


“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 #1472887
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse