gberkeley
asked on
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.SE RVICE_PERI OD_START) AS SVCSTARTDT
,MAX(SERVICE_DELIVERIES.[S ERVICE_PER IOD_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.SER VICE_PERIO D_START_DT ) AS SVCSTARTDT
2. MAX(SERVICE_DELIVERIES.SER VICE_PERIO D_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.
,MIN(SERVICE_DELIVERIES.SE
,MAX(SERVICE_DELIVERIES.[S
,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.SER
2. MAX(SERVICE_DELIVERIES.SER
--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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
the date difference in seconds, maybe?
DATEDIFF( second, MIN ( ... ) , MAX ( ... ) )
DATEDIFF( second, MIN ( ... ) , MAX ( ... ) )
ASKER
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!
Thanks, lcohan and angelIII!
ASKER
I really appreciated the fast responses and multiple attempts to assist.
ASKER
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