0

I need help in removing the date dimension from the query below. In other words make the query independent of the date / time interval My goal is to load the table into SSAS so that i would not have to change the date every time i run reports. the query is huge (months, quarters, years, and aggregated date CR12,PR12 ...), i just gave a short example below I sincerly appreciate any help

drop table #tmptmp

SELECT *, (DATEDIFF(day, enrollmentsDate, ShipmentDate))  
- ((DATEDIFF(WEEK, enrollmentsenttDate, InitialShipmentDate) * 2) 
+(CASE WHEN DATENAME(DW, enrollmentsentDate) = 'Sunday' THEN 1 ELSE 0 END) 
+(CASE WHEN DATENAME(DW, ShipmentDate) = 'Saturday' THEN 1 ELSE 0 END)  
- (select count(*) from tblFactoryHolidayDates where Date >= enrollmentsentDate 
and Date < InitialShipmentDate)) as countdays into #tmptmp from
#tmpTouchpointsEnrollments
where EnrollmentSentDate is not null 


----------------------------
drop table #tmp

select * into #tmp
from #tmptmp
where countdays < 20


    drop table #tmpMetric

      Select 'GrandTotal' as Dummy,'Avg days' as Metrics,'1' as MetricOrder,

    Sum(case when Year(EnrollmentReceiveddate) ='2010' then (countdays) end) *1.0/ 
    count(case when Year(EnrollmentReceiveddate) ='2010' then (patientID) end) *1.0 as  Y2010,
    into #tmpMetric
    from #tmp

Thank you very much

user25830
  • 23
  • 1
  • 1
  • 5
  • Can't you cast the columns to the `DATE` data type? – Serpiton Jun 13 '14 at 14:30
  • possible duplicate of [How to return the date part only from a SQL Server datetime datatype](http://stackoverflow.com/questions/113045/how-to-return-the-date-part-only-from-a-sql-server-datetime-datatype) – Jenn Jun 13 '14 at 16:19
  • I might not have explained my issue right. I am hoping to get rid of case when Year(enrollmentReceivedate)='2010' and just leave Sum(enrollmentReceivedate)/count(enrollmentReceivedate) Thank you for your help – user25830 Jun 13 '14 at 17:05
  • Then you need to something like add `GROUP BY year` to the query(s), although you probably want to change your query because the current version is likely poorly performing. – Clockwork-Muse Jun 14 '14 at 00:23

0 Answers0