Update Query Help

Question

Update Query Help

Anjan@Sql

SSCrazy

Points: 2316
More actions
June 2, 2014 at 11:46 am

#294071

Create Table Tab1
(
weekdate date,
Aid int,
Cid i nt,
Cdate date
)
--The number of entries for a clientid may varies ,5 entries for 01 as below
Insert into Tab1 values('2014-02-13',01,121,'2014-03-15')
Insert into Tab1 values('2014-02-03',01,123,'2014-01-22')
Insert into Tab1 values('2014-01-03',01,123,'2014-02-13')
Insert into Tab1 values('2014-08-03',01,123,'2014-06-18')
Insert into Tab1 values('2014-07-03',01,123,'2014-04-01')
--i need an update query
when i ran my update query the first accountid entry Cdate should changed to
seven days less
ex:- when i ran today(2014-06-02), the first accountid Cdate should change to
2014-05-26
simillarly the second accountid Cdate should change to 14 days less than the rundate(2weeks)
and so on .
The query should run based on the number of entries in the Table.
Any help would be appreciated
apologies for the wrong format post

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Ananth@Sql (6/2/2014)
Create Table Tab1
(
weekdate date,
Aid int,
Cid i nt,
Cdate date
)
--The number of entries for a clientid may varies ,5 entries for 01 as below
Insert into Tab1 values('2014-02-13',01,121,'2014-03-15')
Insert into Tab1 values('2014-02-03',01,123,'2014-01-22')
Insert into Tab1 values('2014-01-03',01,123,'2014-02-13')
Insert into Tab1 values('2014-08-03',01,123,'2014-06-18')
Insert into Tab1 values('2014-07-03',01,123,'2014-04-01')
--i need an update query
when i ran my update query the first accountid entry Cdate should changed to
seven days less
ex:- when i ran today(2014-06-02), the first accountid Cdate should change to
2014-05-26
simillarly the second accountid Cdate should change to 14 days less than the rundate(2weeks)
and so on .
The query should run based on the number of entries in the Table.
Any help would be appreciated
apologies for the wrong format post

Nice job posting ddl and sample data. However, your explanation doesn't make any sense. Why bother storing dates at all if you are just going to change them based on the current date? Or are you saying you want to set the date to 7 days earlier than the current value?

I have no idea what you mean by "The query should run based on the number of entries in the Table".

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Anjan@Sql SSCrazy Points: 2316 More actions · Answer 2

i want to set the Cdate seven days less than the rundate for the first Aid and 14 days less than the run date for the

second entry in the table and so on .

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Ananth@Sql (6/2/2014)
i want to set the Cdate seven days less than the rundate for the first Aid and 14 days less than the run date for the
second entry in the table and so on .

What in your data defines the order? From the sample data you posted I assume you mean in that order. The problem is there is no order to a table and the data you provided is also unordered. Is there a column you can use to specify the order?

You should use a tally/numbers for this kind of thing. If you can tell me what column to use as an ordering condition I can show you how this works.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Anjan@Sql SSCrazy Points: 2316 More actions · Answer 4

Thanks you sean lange for the quick replies.

you can use the wdate the first column for ordering.(May be i was given wrong sample data)

Sean Lange SSC Guru Points: 286590 More actions · Answer 5

Ananth@Sql (6/2/2014)
Thanks you sean lange for the quick replies.
you can use the wdate the first column for ordering.(May be i was given wrong sample data)

Do you have a primary key on this table?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Anjan@Sql SSCrazy Points: 2316 More actions · Answer 6

Anjan@Sql

SSCrazy

Points: 2316

June 2, 2014 at 12:39 pm

#1718319

The wdate is the primary key column.

Sean Lange SSC Guru Points: 286590 More actions · Answer 7

With all the info revealed we can do this pretty easily.

with MyNewValues as

(

select weekdate, ROW_NUMBER() over(order by weekdate) as RowNum

from Tab1 t

)

update t

set Cdate = DATEADD(DAY, -1 * (RowNum * 7), GETDATE())

from Tab1 t

join MyNewValues v on v.weekdate = t.weekdate;

select * from Tab1;

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 8

wild guess....OP please provide expected results based on your sample data.....thanks

;with cte_tab as (

SELECT weekdate

, Aid

, Cid

, row_number() OVER (PARTITION BY AID ORDER BY weekdate DESC) rn

FROM Tab1

)

SELECT weekdate

, Aid

, Cid

, rn

, DATEADD(day, DATEDIFF(day, 0, (dateadd(day, rn * - 7, getdate()))), 0) dateforupdate

FROM cte_tab

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day