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?