asked on

Sql Server Question

Is there a way to correctly write this? I want the sum of the difference between the date started(Started) and The Max Completed date for the Case Inspection records where Result = In Compliance and the case_no is in the date range.

Select SUM(DATEDIFF(dd, STARTED,
(select max(COMPLETED_DATE) from Case_Inspections
where RESULT = 'In Compliance'
and CASE_NO in (select CASE_NO from Case_Main
where STARTED >= '2015-06-01' and STARTED <= '2015-06-30'))))

Kyle Abrahams, PMP

declare @maxDate datettime

--get the max date
set @maxDate =  (select max(COMPLETED_DATE) from Case_Inspections
              where RESULT = 'In Compliance'
            and CASE_NO in (select CASE_NO from Case_Main
            where STARTED >= '2015-06-01' and STARTED <= '2015-06-30'))

-- do your sum
select sum (DateDiff(dd,started,@maxDate)) from  Case_Inspections
              where RESULT = 'In Compliance'
            and CASE_NO in (select CASE_NO from Case_Main
            where STARTED >= '2015-06-01' and STARTED <= '2015-06-30'))

Open in new window

Jim Horn

> and STARTED <= '2015-06-30'))))
As an aside, this query will not return all rows dated 2015-06-30 that have a date value after midnight, as '2015-06-30' translates to '2015-06-30 00:00:00'. Better to use < '2015-07-01'.

See Beware of Between for some examples.

qbjgqbjg

ASKER

I don't think I explained it well enough. The sum I need is for the difference for each case_no in the date range between the max completion date for the case_no and the date started for that case_no.

Jim Horn

A couple of things would really help here..

Change the question title. It's in a SQL Server zone, so we already know it's a SQL Server question.

A before and after data mockup would help, as it's hard to conceptualize your question based on the description.

qbjgqbjg

ASKER

Inspection Records

Case_NO 10
Started 06/01/2015
Completed_Date 06/12/2015
Completed_Date 06/18/2015 DateDiff is 17

Case_No 20
Started 06/01/2015
Completed_Date 06/10/2015
Completed_Date 06/21/2015 DateDiff is 20

Sum is 27

qbjgqbjg

ASKER

Actually sum is 37, sorry about that.

ASKER CERTIFIED SOLUTION

Kyle Abrahams, PMP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

qbjgqbjg

ASKER

i will give it a shot. I will let you know.

qbjgqbjg

ASKER

That worked. However, I do not quite understand these 2 columns:
, datediff(dd, min(started)
, max(completed_date)) theDateDiff

SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

qbjgqbjg

ASKER

Thanks. I actually figured it out. Thanks everyone.

qbjgqbjg

ASKER

I wanted to also select Paul's answer, but I did not see a way to mark it.

PortletPaul

See: How do I accept multiple comments as my solution?
http://support.experts-exchange.com/customer/portal/articles/608596

& It is possible to "unaccept" a question too.
To do this look for the "Request Attention" link immediately under your question and ask an administrator to do that for you.

However, there is no need to do any of this for this question.

qbjgqbjg

ASKER

Thanks. This used to be much easier before the website was changed.