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'))))
qbjgqbjgConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kyle AbrahamsSenior .Net DeveloperCommented:
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 HornMicrosoft SQL Server Data DudeCommented:
>  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.
qbjgqbjgConsultantAuthor Commented:
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.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Jim HornMicrosoft SQL Server Data DudeCommented:
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.
qbjgqbjgConsultantAuthor Commented:
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
qbjgqbjgConsultantAuthor Commented:
Actually sum is 37, sorry about that.
Kyle AbrahamsSenior .Net DeveloperCommented:
select 
SUM(thedatediff) final
from
(
select case_no, datediff(dd, min(started), max(completed_date)) theDateDiff
from #t  -- replace with your actual table

--add where clause here.
group by case_no
) x

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
qbjgqbjgConsultantAuthor Commented:
i will give it a shot. I will let you know.
qbjgqbjgConsultantAuthor Commented:
That worked. However, I do not quite understand these 2 columns:
, datediff(dd, min(started)
 , max(completed_date)) theDateDiff
PortletPaulEE Topic AdvisorCommented:
I do not quite understand these 2 columns:
, datediff(dd, min(started)
 , max(completed_date)) theDateDiff
It isn't 2 columns, it is a single column using the datediff function which expects 3 parameters, like this:

DATEDIFF ( datepart , startdate , enddate )

Kyle's query, reformatted, could look like this which may make is easier to understand.
SELECT
      SUM(thedatediff) final
FROM (
      SELECT
            case_no
          , DATEDIFF( dd   ,MIN(started)  ,MAX(completed_date)   )    AS theDateDiff
      FROM #t  -- replace with your actual table

      --add where clause here.
      GROUP BY case_no
) x

Open in new window

The 3 parameters:
dd is the datepart parameter (by the way instead of dd you could use: day)
MIN(started) is the startdate parameter, and
MAX(completed_date)   is the enddate parameter
qbjgqbjgConsultantAuthor Commented:
Thanks. I actually figured it out. Thanks everyone.
qbjgqbjgConsultantAuthor Commented:
I wanted to also select Paul's answer, but I did not see a way to mark it.
PortletPaulEE Topic AdvisorCommented:
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.
qbjgqbjgConsultantAuthor Commented:
Thanks. This used to be much easier before the website was changed.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.