Accumulate Hours and Generate finish date

Question

Accumulate Hours and Generate finish date

Sandeep Saretha

SSChasing Mays

Points: 647
More actions
December 9, 2010 at 4:04 pm

#229507

Hi,
I am wotking on work performed in a day create the day when it will finish. considering 8 hrs in a day and excluding weekend.
Table
NamefItemIDfDateStart fDateDueHrs
A87631112/9/20104
A877111 4
A792311 148
B81261112/9/20108
B806411 4
B806811 1
Result
NamefItemIDfDateStart fDateDueHrs
A87631112/9/2010 12/9/20104
A87711112/9/2010 12/9/2010 4
A79231112/13/2010 1/6/2011 148
B81261112/9/2010 12/9/20108
B80641112/10/2010 12/10/2010 4
B80681112/10/2010 12/10/2010 1
Does anybody have any idea?
Strugging with this.
TIA

Viewing 3 posts - 1 through 2 (of 2 total)

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

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

You're not the only one. Where'd the 12/13 for Name A come in? That's a 4 day gap.

Please check my sig for the first link. It will show you how to grab your DDL and create easily consumable test data so others around here have a ready to go test bed to help answer your question.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Sandeep Saretha SSChasing Mays Points: 647 More actions · Answer 2

Sorry Here is the Test Data, Data in 'result' table should be the final data after processing

TIA

CREATE TABLE #mytable

( Name Varchar(1),

fItemID int, -- PK

StartDate DateTime,

fEndDate DateTime,

HrsInt)

Insert into #mytable

SELECT 'A','876311',Convert(Varchar,GETDATE(),101),Null,4

UNION ALL

SELECT 'A','877111',Null,Null,4

UNION ALL

SELECT 'A','792311',Null,Null,148

UNION ALL

SELECT 'B','806411',Convert(Varchar,GETDATE(),101),Null,8

UNION ALL

SELECT 'B','864611',Null,Null,8

UNION ALL

SELECT 'B','882811',Null,Null,12

Result

CREATE TABLE #Result

( Name Varchar(1),

fItemID int, -- PK

StartDate DateTime,

fEndDate DateTime,

HrsInt)

Insert into #Result

SELECT 'A','876311',Convert(Varchar,GETDATE(),101),Convert(Varchar,GETDATE(),101),4

UNION ALL

SELECT 'A','877111',Convert(Varchar,GETDATE(),101),Convert(Varchar,GETDATE(),101),4

UNION ALL

SELECT 'A','792311','12/10/2010','1/5/2011',148

UNION ALL

SELECT 'B','806411',Convert(Varchar,GETDATE(),101),Convert(Varchar,GETDATE(),101),8

UNION ALL

SELECT 'B','864611','12/10/2010','12/10/2010',8

UNION ALL

SELECT 'B','882811','12/13/2010','12/14/2010',12