Convert a paramaterized query into a view in SQL Server

Question

Suppose I had the following table in SQL Server:

Date         ColA         ColB
1/1/2013     Val1A        Val1B
1/1/2013     Val2A        NULL
1/1/2013     Val3A        Val3B
1/2/2013     Val1A        NULL
1/2/2013     Val2A        Val4B

And, I'm looking to see the day-over-day changes for ColB - Which, in this case could look as follows:

Date         ColA         ColB_Today       ColB_Prev_Day
1/2/2013     Val1A        NULL             Val1B
1/2/2013     Val2A        Val4B            NULL
1/2/2013     Val3A        NULL             Val3B

So, to do this I created a fairly complex query (joining the table to itself) that has a single input variable and a calculated variable looking as follows:

DECLARE @Date Date = '1/2/2013', @PrevDay Date;
SET @PrevDay = (Select TOP 1 Date from MyTable where Date < @Date order BY Date desc);

SELECT
  @Date as 'Date',
  T1.ColA,
  T1.ColB as ColB_Today,
  T2.ColB as ColB_Prev_Day
FROM
  (SELECT * FROM MyTable where Date = @Date) T1,
 FULL OUTER JOIN
  (SELECT * FROM MyTable where Date = @PrevDay) T2
 ON
  T1.ColA = T2.ColA

Which is what I'm looking for.

Now, I'd like to be able to convert this into a View (with no parameters being passed) rather than this paramaterized query.

I hope that what I'm looking to do makes sense... In other words, the view would have the columns Date, ColA, ColB_Today & ColB_Prev_Day and it simply pulls the data from the table to create the same result without having to paramaterize date (simply, based upon the dates available in the table) - My biggest challenge seems to be the Val3A row above which has a value only for one of the 2 dates, not both...

Any thoughts???

Thanks!!

For one, please stop using ambiguous formats like 1/2/2013. Is that January 2nd or February 1st? Don't make us, other maintainers of the code, or SQL Server guess. Use unambiguous formats like YYYYMMDD. — Aaron Bertrand
– Aaron Bertrand, Commented Sep 3, 2013 at 18:57
Which date in your table would you pick to replace the input parameter? — Dan Bracuk
– Dan Bracuk, Commented Sep 3, 2013 at 19:05
If this is SQL Server 2012 you could use LAG to remove the join. — Conrad Frix
– Conrad Frix, Commented Sep 3, 2013 at 19:09
I'm using SQL Server 2008 and @AaronBertrand, That's not what's in my table... That's just what I used to write the example... My table is substantially different and much more complicated... this was just my version to show what I was looking to achieve in the eaisest possible way.... — John Bustos
– John Bustos, Commented Sep 3, 2013 at 19:09

roman · Accepted Answer · 2013-09-03 19:38:10Z

2

you can use something ugly like this:

with cte_dates as (
    select distinct [Date] from MyTable
), cte_vals as (
    select distinct ColA from MyTable
)
select
    d.[Date],
    v.ColA,
    T1.ColB as ColB_Today,
    T2.ColB as ColB_Prev_Day
from cte_dates as d
    cross join cte_vals as v
    left outer join MyTable as T1 on T1.ColA = v.ColA and T1.[Date] = d.[Date]
    outer apply (
        select top 1 TT.ColB
        from MyTable as TT
        where TT.ColA = v.ColA and TT.[Date] < d.[Date]
        order by TT.[Date] desc
    ) as T2

=> sql fiddle demo

It's hard to do better until I know more about your data. Do you have gaps between dates in your table?

edited Sep 3, 2013 at 19:38

answered Sep 3, 2013 at 19:04

roman

118k30 gold badges205 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

John Bustos Over a year ago

WOW - that is kind of an ugly solution... But it is still kind of cool!!! To answer your question, there will be gaps between my dates and I always want to return this date 1's data against the most previous date before it in such a manner that if 1 of the 2 dates is missing a ColA, it simply returns a NULL for the ColB value.... I hope that makes sense, but please ask any other questions you may have... !!THANKS FOR THE REPLY!!!

roman Over a year ago

try this one on your data to see if you have desired output

Conrad Frix Over a year ago

I think you mean D.[Date] where you've written C.Date also you get all the dates not just the ones for the max date.

roman Over a year ago

@ConradFrix I think that to get all possible dates is intended behaviour of OP question. Updated aliases, thank you

Conrad Frix Over a year ago

Maybe. I'm not completely sure what the OP is meant by My biggest challenge seems to be the Val3A row above which has a value only for one of the 2 dates, not both In any case if you want to match the desired output it's easy enough to fix

|

Collectives™ on Stack Overflow

Convert a paramaterized query into a view in SQL Server

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related