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.
Go to Top of Page

s1patel
Starting Member

3 Posts

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

This is an example to the production database.
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -