Case Statement- cal of days - 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

General SQL Server Forums

New to SQL Server Programming

Case Statement- cal of days

Author

Topic

s1patel
Starting Member

3 Posts

Posted - 2015-01-05 : 05:45:03

Hi I have to produce a report to calculate no of days based on user input start date and end date. I have tried to explain below.

say for eg: in the tables I have emp name
user 'Phani' started work from - EStart 20/11/2014 EEnd 10/01/2015 - total days --datediff
within his work period he did different roles:

Phani Marketing (prSt Date) 20/11/2014 prE date (28/11/2014) Total 9 days
Phani Admin (prSt Date) 29/11/2014 prE date (20/12/2014) Total 22 days
Phani CRM (prSt Date) 20/12/2014 prE date (10/01/2015) Total 22 days
Total days 53 Days
for this :
I calculated datediff + 1 and got sub jobs days BUT

say financial director wants to see Title of 'Sub Jobs' with 'Days' from 1st Dec to 31st Dec
so on paper I calulated as :
1-31 Dec 2014
Phani Marketing NULL (Do not fall in Req Dt)
Phani Admin 20 (Deduct 2 days of Nov & calculated 20 days of Dec)
Phani CRM 11 (Deduct 20 days of Nov and deduct 11 days of Jan so for Dec , we got 11 days)
Total days 31

HOW CAN I USE Case statement to calculate days for given start date and end date. I have to include all three totals, 1 for Job dates, 2, subjobs dates, 3 cal of days for a requested period. Please advise at the earliest.

Regards
Sonu

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-05 : 08:41:01

Would you please post"

1. DDL for the tables involved (CREATE TABLE statements)
2. DML to populate the tables (INSERT statements)
3. Desired output with the tables built in steps 1 and 2.

s1patel
Starting Member

3 Posts

Posted - 2015-01-05 : 10:04:09

Hi

This is an example to the production database.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-05 : 10:21:11

Would you please post:

1. DDL for the tables involved (CREATE TABLE statements)
2. Sample DML to populate the tables (INSERT statements)
3. Desired output with the tables built in steps 1 and 2.

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-01-05 : 12:44:16

This might work for you, but if you want complete query, you should provide the items, gbritton requested:

case
   when EStart>convert(date,'20141231',112)
     or EEnd<convert(date,'20141201',112)
   then null
   else datediff(dd
                ,case
                    when EStart>convert(date,'20141201',112)
                    then EStart
                    else convert(date,'20141201',112)
                 end
                ,case
                    when EEnd<convert(date,'20141231',112)
                    then EEnd
                    else convert(date,'20141201',112)
                 end
                )
end

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources