Annoying little query... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Annoying little query...

Author

Topic

Crespo24
Village Idiot

144 Posts

Posted - 2003-02-03 : 05:00:49

Hey people,

Say I have

01-JAN-2002 31-DEC-2002
01-JAN-2001 31-DEC-2001
01-JAN-2000 31-DEC-2000
01-AUG-1998 31-DEC-1999

01-JAN-2001 31-DEC-2001
01-JAN-2000 31-DEC-2000
01-JAN-1999 31-DEC-1999
01-AUG-1998 31-DEC-1998

what I want to do is take a year off the start date and the end date but keeping the minimum date (the one marked in red) the same.

Any ideas?

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-03 : 08:58:56

Hope this helps (check out BOL though):

SELECT DATEADD(year, -1, dteColumn)
FROM TABLE o
Where dteColumn NOT IN
(SELECT MIN(dteColumn) FROM TABLE i)

Good Luck

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-03 : 09:36:40

Don't think that quite works -- he still wants to return the min date as well, I believe. Try this:

SELECT
DateAdd(y,CASE WHEN StartDate =
(SELECT Min(StartDate) From YourTable) THEN 0 ELSE - 1 END, StartDate) as StartDate, DateAdd(y,-1,EndDate) as EndDate
FROM
YourTable

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-03 : 09:48:10

Looks good! I was assuming he wanted to do an update so he didn't have to incur a scan every time he wanted to do a select...

But if he needs to retain the data that way, then what are you going to do...maybe the front end could handle it? (But then how would you know the min date?)

Hey Crespo24: What the business reason behind what you're doing?

Brett

8-)

Crespo24
Village Idiot

144 Posts

Posted - 2003-02-03 : 09:58:31

quote:

Looks good! I was assuming he wanted to do an update so he didn't have to incur a scan every time he wanted to do a select...

But if he needs to retain the data that way, then what are you going to do...maybe the front end could handle it? (But then how would you know the min date?)

Hey Crespo24: What the business reason behind what you're doing?

Brett

8-)

Thank you very much for your help guys..

Basically .. the data represents amounts of contributions paid on a certain date. The problem occured while migrating it from the legacy system (which I did not do by the way). Anyway.. I was asked to move the dates back by one year, but to leave the start date as it is.

Thanks again....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources