Syntax for Duration, Max Date, Min Date

I am wrestling with a query and I need some expert help. I am trying to create four new fields in my select statement:

  ,MIN(SERVICE_DELIVERIES.SERVICE_PERIOD_START) AS SVCSTARTDT
  ,MAX(SERVICE_DELIVERIES.[SERVICE_PERIOD_START2]) AS SVCENDDR
  ,DATEDIFF(d, SVCSTARTDT), SVCENDDT)
  ,AVG(DATEDIFF)
 
FROM
tablename.fieldname INNER JOIN ON tablename.fieldname
WHERE
blah-blah-blah
GROUP BY
??????

I can't get Reporting Services to accept any of several variations on this theme.
What I need is to identify the earliest/latest service date (so I can explore a cohort of people getting certain services) and I need a new data element that gives me DURATION as an expression or function of Max - Min.  In a perfect world, I would be able to get for the DURATION data element a specific format of YY.YYY or YY.MM where the expression rounds up to the next month if even one day in the max month is a service date between or on Min/Max. (I'm looking for how many years and months a person got services in a program).  

Taking that one step further, I'd like to be able to get AVG_DURATION so I can use this in evaluating program tenure from date A to date B.

SO - to boil it all down, I need syntax/convention in T-SQL for Report Builder 3.0:

1.  MIN(SERVICE_DELIVERIES.SERVICE_PERIOD_START_DT) AS SVCSTARTDT
2.  MAX(SERVICE_DELIVERIES.SERVICE_PERIOD_START_DT) AS SVCENDDT
--Report Builder does NOT like using the same data element twice and I can't figure out how to differentiate the two--
3.  DURATION  (Difference between min and max date as YY,MM or YY.YYY)
4.  AVG_DURATION (self-evident)

Finally, I am using these in a query that has grouping applied, so which if any, and how, should these elements be represented in the GROUP BY clause?

Assistance VERY gratefully appreciated.
gberkeleyAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
in the GROUP BY, you don't put the alias: AS SVCSTARTDT

anyhow, wouldn't this work
DURATION(  MIN( SERVICE_DELIVERIES.SERVICE_PERIOD_START_DT ) 
   , MAX(SERVICE_DELIVERIES.SERVICE_PERIOD_START_DT)  
  ) 

Open in new window

0
 
lcohanConnect With a Mentor Database AnalystCommented:
"SO - to boil it all down, I need syntax/convention in T-SQL for Report Builder 3.0:"

Why not to use SQL Stored procedure in the report instead? That's what I would do to simplify things (and SQL syntax) as you have way more posibilities like that and just use the SQL SP in your SSRS report
0
 
gberkeleyAuthor Commented:
Hi lcohan,
Thanks for the fast response!
Perhaps a SPROC would be the better way to go. Unfortunately, that's WAY beyond my skill set.  If that's the right answer, then I'll need to wait for one of our DBAs to free up.  

Much appreciated,
j
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
gberkeleyAuthor Commented:
I think we're getting closer.... it seems it won't recognize Duration and it wants the DATEDIFF function. I just haven't been able to get the syntax straight that will let me use Min/max in same query.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
the date difference in seconds, maybe?

DATEDIFF( second, MIN ( ... ) , MAX ( ... ) )
0
 
gberkeleyAuthor Commented:
Report Builder still not cooperating.  I'm going to close out the question and refer it to the DBAs on my end; maybe they can figure it out.

Thanks, lcohan and angelIII!
0
 
gberkeleyAuthor Commented:
I really appreciated the fast responses and multiple attempts to assist.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.