Update bulk data based on Dates

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

General SQL Server Forums

New to SQL Server Programming

Update bulk data based on Dates

Author

Topic

kamnandi
Starting Member

6 Posts

Posted - 2011-03-08 : 03:51:43

Hi There

I have a table with thousands of records. It has ID, Start_Date, End_Date, Minutes1 columns. The Minutes1 column is empty. I want to create a stored procedure that calculates the minutes between the two dates.

If date date difference is less than 2 hrs it should default the minute1 column to 0. Greater than 2 but less than 5 hrs is 510. Greater than 5 is 820. if End_Date is null the minute column should be null.

Can you please guide me in the right direction when creating a stored procedure to do this.

Thx a mill

matty
Posting Yak Master

161 Posts

Posted - 2011-03-08 : 04:06:16

[code]
SELECT ID, Start_Date, End_Date,
CASE WHEN DATEDIFF(hh,Start_Date, End_Date) < 2 THEN 0
WHEN DATEDIFF(hh,Start_Date, End_Date) BETWEEN 2 AND 5 THEN 510
WHEN DATEDIFF(hh,Start_Date, End_Date) > 5 THEN 820
ELSE NULL
END AS Minutes1
FROM YourTable
[/code]

kamnandi
Starting Member

6 Posts

Posted - 2011-03-08 : 04:19:26

Thx

Would this work:

Update YourTable set Minutes1 = (
CASE WHEN DATEDIFF(hh,Start_Date, End_Date) < 2 THEN 0
WHEN DATEDIFF(hh,Start_Date, End_Date) BETWEEN 2 AND 5 THEN 510
WHEN DATEDIFF(hh,Start_Date, End_Date) > 5 THEN 820
ELSE NULL
END)

quote:

Originally posted by matty


SELECT	ID, Start_Date, End_Date,
	CASE WHEN DATEDIFF(hh,Start_Date, End_Date) < 2 THEN 0
	     WHEN DATEDIFF(hh,Start_Date, End_Date) BETWEEN 2 AND 5 THEN 510
	     WHEN DATEDIFF(hh,Start_Date, End_Date) > 5 THEN 820
	     ELSE NULL
	END AS Minutes1 
FROM	YourTable

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-03-10 : 10:27:17

quote:
Originally posted by kamnandi

Thx

Would this work:

Update YourTable set Minutes1 = (
CASE WHEN DATEDIFF(hh,Start_Date, End_Date) < 2 THEN 0
WHEN DATEDIFF(hh,Start_Date, End_Date) BETWEEN 2 AND 5 THEN 510
WHEN DATEDIFF(hh,Start_Date, End_Date) > 5 THEN 820
ELSE NULL
END)

quote:
Originally posted by matty

SELECT ID, Start_Date, End_Date,
CASE WHEN DATEDIFF(hh,Start_Date, End_Date) < 2 THEN 0
WHEN DATEDIFF(hh,Start_Date, End_Date) BETWEEN 2 AND 5 THEN 510
WHEN DATEDIFF(hh,Start_Date, End_Date) > 5 THEN 820
ELSE NULL
END AS Minutes1
FROM YourTable

Yes. Also make sure to add where clause if needed

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources