Date Caluculation based on number of days

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

Date Caluculation based on number of days

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-02-14 : 13:55:17

I am trying to calculate Enddt in the below query and my testing shows it calculates incorrectly for weekdays and ok for weekends
Need help

the EndDt should be calculated based on three differant feilds which are approvedt,availabledt,TAT

in our database
1 - Sunday
2 - Monday
3 - Tuesday
4 - Wednesday
5 - Thursday
6 - Friday
7 - Saturday

update Customer_Target
set Enddt = case
When availabledt is null then  approvedt
When availabledt > approvedt then DATEADD(day,

       Tat%5 +

       CASE DATEPART(weekday,availabledt) + tat%5

           WHEN 7 THEN 2

           WHEN 1 THEN 1

       ELSE 0 END,

    DATEADD(week,tat/5,availabledt))
WHEN approvedt > availabledt then DATEADD(day,

       Tat%5 +

       CASE DATEPART(weekday,approvedt) + tat%5
     
     WHEN 7 THEN 2
            WHEN 1 THEN 1

       ELSE 0 END,

    DATEADD(week,tat/5,approvedt))
WHEN  approvedt = availabledt then DATEADD(day,

       Tat%5 +

       CASE DATEPART(weekday,approvedt) + tat%5

           WHEN 7 THEN 2

           WHEN 1 THEN 1

       ELSE 0 END,

    DATEADD(week,tat/5,approvedt))

Else approvedt
End
where requestid in

What am i doing wrong in the above.The results are very inconsistent.Need help please

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-14 : 13:57:03

Forget the query, what does EndDate Mean???

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-02-14 : 13:59:49

EndDate - A customer request has to be met bfore or by that date

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-14 : 13:59:53

no idea..How do you think we will be able to know without understanding whats your rule for calculatind Enddt. Also you're using field like Tat in calculation which you've not explained the relevance and we dont know what it really means
Rather than posting query, it would make more sense if you can explain your rule by means of pseudocode and then give some sample data and required output you want out of them

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-14 : 14:01:42

quote:
Originally posted by jim_jim

I am trying to calculate Enddt

Yeah...so How do you do that...and NO Code..pseudo or otherwise

Give me a Business Requirements Document (BRD)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-02-14 : 14:20:18

Hope i will make more sense with my below explanation

Enddt should be calcualted based on the feilds approvedt,availabledt,TAT

Enddt should be equal to approvedt + TAT(integer) number of business days when approvedt is greater than availabledt
Enddt should be equal to approvedt + TAT(integer) number of business days when approvedt is equal to availabledt
Enddt should be equal to availabledt + TAT(integer) number of business days when availabledt is greater than approvedt

Business Days = Weekdays -any holidays on weekdays are still business days

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-14 : 14:25:33

[code]
case when approvedt >= availabledt then approvedt else availabledt end + TAT % 5
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-02-14 : 16:29:56

How is it going to work
Say for exmplae tat was 10 then TAT%5 will be 0

Iam not sure whether i understood it correct

quote:
Originally posted by visakh16

case when approvedt >= availabledt then approvedt else availabledt end + TAT % 5

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-14 : 16:43:28

quote:
Originally posted by jim_jim

How is it going to work
Say for exmplae tat was 10 then TAT%5 will be 0

Iam not sure whether i understood it correct

quote:
Originally posted by visakh16

case when approvedt >= availabledt then approvedt else availabledt end + TAT % 5

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

that part was from your original suggestion. i just showed you case ...when to take values accordingly
i dont know about rest of your rules (you still havent explained what Tat is and how it contributes to your calculation)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2012-02-15 : 08:06:42

TAT is an integer number ranging from 1-100.This is a number of working days that we need to add to approvedt or availabledt based on which is greater.Working days being weekdays excluding saturday and sunday

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources