View which contains data for every day of the year

Currently we have data which contains data for every weekday, however no values are available for Sat or Sun. We need to create a few which contains a value for every day of the year. In the case of Sat or Sun the view needs to show the last available data, hence the values from friday.

We have a date table which contains every days date from 1970 to 2030.

For example we have:
Date                      Value
2010-02-04            1.1          
2010-02-05            1.8
2010-02-08            2.5
2010-02-09            2.8


Should become:
Date                      Value
2010-02-04            1.1          
2010-02-05            1.8
2010-02-06            1.8
2010-02-07            1.8
2010-02-08            2.5
2010-02-09            2.8


We are looking for a solution in a single view.
riffrackAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Harish VargheseProject LeaderCommented:
Can you share the sql for current view?
0
hspoulsenCommented:
I would create a new table I've called it Calendar here. It contains one row per day in the years you want to report on.

Then I would use an OUTER APPLY.

SELECT *
FROM Calendar C
OUTER APPLY (
  SELECT TOP 1 * from date D where c.Date<= d.Date ORDER BY d.Date desc
) as E

Check if it is fast enough.
You may need to pre-calculate the data into a separate table overnight, every day.
0
Louis01Commented:
Declare @StartDate date = '01-Jan-2013';
Declare @EndDate date = '31-Jan-2013';

With Calendar As
    (
    Select @StartDate As [Date]
    Union All
    Select DateAdd(d,1,[Date])
    From Calendar
    Where [Date] < @EndDate
    )
Select *
From Calendar
    Left Join MyTable
        On Calendar.[Date] = MyTable.[Date];

Open in new window

0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

riffrackAuthor Commented:
We don't have a current view. The example above is from a base table. We have 2 base tables which are relevant for this view.

Table1 : AllDates
Field1: AllDates_Date (DATE)

Table2: PriceHistory
Field1: PriceDate (DATE)
Field2: PriceValue (FLOAT)
0
Louis01Commented:
Try this:

CREATE VIEW dbo.view_name_here AS
With Calendar As
    (
    Select (select MIN(AllDates_Date) from AllDates) As [Date]
    Union All
    Select DateAdd(d,1,[Date])
    From Calendar
    Where [Date] < (select MAX(AllDates_Date) from AllDates)
    )
Select *
From Calendar
    Left Join PriceHistory
        On Calendar.[Date] = PriceHistory.PriceDate ;

Open in new window

0
hspoulsenCommented:
Hi riffrack,

Then my suggestion would look like this:
CREATE VIEW dbo.view_name_here AS
  SELECT c.AllDates_Date , e.PriceDate , e.PriceValue
  FROM AllDates C
  OUTER APPLY (
    SELECT TOP 1 *
    FROM PriceHistory D where c.AllDates_Date <= d.PriceDate
    ORDER BY d.PriceDate DESC
  ) as E;

I have included e.PriceDate in the view for debugging purposes. You can take it out if you do not want to have it there.

Best regards,
Henrik
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Harish VargheseProject LeaderCommented:
Surely, there are different methods to go about it using OUTER JOINs, CTEs, Aggregation, looping etc. But if performance is priority, you need to avoid all these. In that case, best option will be to store the effective price date against each calendar date in one table. You can use AllDates table itself. Add a column EffectivePriceDate to this table which will have same value as AllDates_Date on week days; on Saturdays and Sundays it will hold the date of previous Friday. You can do a one time update for the values in this column.
ALTER TABLE AllDates ADD EffectivePriceDate DATE

UPDATE AllDates
SET EffectivePriceDate = CASE DATENAME (dw, AllDates_Date) WHEN 'Saturday' then DATEADD(dd, -1, AllDates_Date)
                                  WHEN 'Sunday' then DATEADD(dd, -2, AllDates_Date)
                                  ELSE AllDates_Date END

Open in new window

And then create a simple view as below:
CREATE VIEW V_PriceHistory
As
SELECT D.AllDates_Date as PriceDate,
		P.PriceValue
FROM AllDates D, PriceHistory P
WHERE P.PriceDate = D.EffectivePriceDate

Open in new window


-Harish
0
awasieCommented:
Following solution based on SQL Server 2005.

Let me explain tables

every_date - contain all only dates from 2010-02-04 to 2010-02-09.
expert_exchange - contain the data you provided.

The most important part is the following statment

select dateadd(d, -((datepart(weekday, ed.every_date) + 1 + 0) % 7), ed.every_date)

If you change ed.every_date with any date it will give you the resent Friday date.

Table Structures

CREATE TABLE [dbo].[expert_exchange_everyday](
      [every_date] [datetime] NULL
) ON [PRIMARY]


CREATE TABLE [dbo].[expert_exchange](
      [date] [datetime] NULL,
      [value] [float] NULL
) ON [PRIMARY]



Following is SQL, hopefully you can create view.

select ex.* from (
select
case DATEPART(dw, ed.every_date)
when 1 then dateadd(d, -((datepart(weekday, ed.every_date) + 1 + 0) % 7), ed.every_date)
when 7 then dateadd(d, -((datepart(weekday, ed.every_date) + 1 + 0) % 7), ed.every_date)
else every_date
end desired_date
from expert_exchange_everyday ed
) ed
left join expert_exchange ex on ex.date = ed.desired_date

Best Regards,
Abdul Wasie Mushtaq
0
riffrackAuthor Commented:
@Henrik
That is exactly what I was looking for, thx! This would cater for all cases when data is missing, even if data is missing for bank holidays which don't fall onto a weekend.
0
riffrackAuthor Commented:
Great stuff, thx
0
hspoulsenCommented:
hi Riffrack,

Please note my original comment, that others second:
Check if it is fast enough.
You may need to pre-calculate the data into a separate table overnight, every day.

I also like the solution that Harish came up with.

Best regards,
Henrik
0
riffrackAuthor Commented:
Yes, I agree, the performance is an issue. The View itself is fast, however once I add a few more joins the performance drops drastically. Hence I have created SP to create a table, as indexed views are not possible using the apply statement.

The idea of Harish looks nice too, however your solution caters for all cases, including bank holidays etc. To be fair, I should have added this in my initial question, the missing data at weekends was just one of many cases.
0
Harish VargheseProject LeaderCommented:
At any point of time, if you want to consider the approach I suggested, here is a bit changed version:
--1. Update EffectivePriceDate for Saturdays and Sundays
--We will not have prices on weekends for sure
UPDATE AllDates
SET EffectivePriceDate = CASE DATENAME (dw, AllDates_Date) WHEN 'Saturday' then DATEADD(dd, -1, AllDates_Date)
                                  ELSE DATEADD(dd, -2, AllDates_Date) END
WHERE DATENAME (dw, AllDates_Date) IN ('Saturday', 'Sunday')

--2. Update EffectivePriceDate for all missing price dates till today (OR YESTERDAY?)
UPDATE D
SET EffectivePriceDate = (SELECT MAX(PriceDate) FROM PriceHistory P
                                  WHERE P.PriceDate <= D.AllDates_Date)
FROM AllDates D
WHERE EffectivePriceDate IS NULL
AND D.AllDates_Date <= GETDATE() --We dont want to update future dates

Open in new window

First UPDATE will take care of all weekends - past and future. The second UPDATE will take care of any missing prices. But then you need to run the second update on daily basis. You can even in put it in a trigger of PriceHistory table.

-Harish
0
riffrackAuthor Commented:
Hi Harish

That is really fantastic too, many thanks. Ver fast. I have an additional challange, in the PriceHistory table we have product and product_category as well. For Henrik's solution I was able to figure out how to add the additional group by clause myself. However I haven't been able to combine your solution with a group by clause.

I will open a new question and would be happy if you could extend your solution to add a group by clause.

riffrack
0
riffrackAuthor Commented:
0
Harish VargheseProject LeaderCommented:
Hi Riffrack,

In that case, you cannot have EffectivePriceDate column in AllDates table. You need to have a separate table as below, which will have all calendar dates for each product and their respective Effective Price Date:
CREATE TABLE EffectivePriceDates (Product char(20), AllDate datetime, EffectivePriceDate datetime, Primary Key(Product, AllDate))

Open in new window

This table should contain dates for every day including holidays and weekends. And in the view, instead of using AllDates table, use this new table, like below:
CREATE VIEW V_PriceHistory
As
SELECT D.AllDate as PriceDate, P.Product
		P.PriceValue
FROM EffectivePriceDates D, PriceHistory P
WHERE P.PriceDate = D.EffectivePriceDate
And P.Product = D.Product

Open in new window

This will definitely be best in terms of performance. The only drawback is that you need to maintain this table (add effective dates) on daily basis.
I will provide a different solution for this on the new question link.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.