How can I create a view which shows monthly values on a daily basis

Viewed 76

I have a view which is defined by the following code

CREATE VIEW [dbo].V_SOME_VIEW AS
    WITH all_dates AS (SELECT DISTINCT(read_dtime) AS date FROM t_periodic_value),
            theObjects AS (SELECT * FROM t_object)    
    SELECT
        ad.date,
        objs.id, 
        pv1.value as theValue

    FROM all_dates ad
        LEFT JOIN theObjects objs ON
            objs.start_date <= ad.date AND (objs.end_date IS NULL OR (objs.end_date IS NOT NULL AND objs.end_date >= ad.date))
        LEFT JOIN t_periodic_value pv1 ON pv1.data_point_id = (SELECT id FROM t_data_point WHERE object_id = objs.id AND measurement_id = 'MonthlyValue')
            AND pv1.read_dtime = ad.date AND pv1.latest_ind = 1

GO

Which if I run a select for any given month gives me output along the lines of :

Date       |     ID        |    theValue
01/01/1990 |  someFacility |      1000
02/01/1990 |  someFacility |      NULL
03/01/1990 |  someFacility |      NULL
...

and so on for the rest of the month. Nulls are returned for every date except the first as the value is calculated on a monthly basis. Is there a way I can define the view so that for every other day in the month, the value from the 1st is used?

1 Answers
Related