Syntax for Duration, Max Date, Min Date
Posted on 2013-01-24
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)
tablename.fieldname INNER JOIN ON tablename.fieldname
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.