Post reply

remove date constraint from a query

  • June 13, 2014 at 6:40 pm

    #396910

    I am trying to remove the dates from a query. my goal is to load it in ssas and add a time dimension.

    Right now i have to change the dates evrytime i run reports (monthly). Here is the quey

    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

  • June 13, 2014 at 9:31 pm

    #1721700

    Remove Dates? Where?

    Do you have a Calendar/Date dimension table? Could just be me, but I thought you had to add a column to your Fact table for the DateKey column and then join that to the DimDate table and then update the Fact table with the new DateID by joining on the matching date fields. Then you can delete the date column in the Fact table...

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply