Using multiple SQL select queries of the same data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Using multiple SQL select queries of the same data

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-08-29 : 07:35:17

Mark writes "I've been beating my head against the wall, and done enough searching of the web that I'm reasonably confident that it's not exactly a "simple" solution.

Say I have a table that has three columns: a list of names, a list of values, and a list of dates. Example:


NAME     VALUE    DATE
----     -----    ----
AAA      $50.00   04/01/2005
AAA      $55.00   04/02/2005
BBB      $25.00   04/01/2005
BBB      $30.00   04/02/2005
BBB      $45.00   04/03/2005
CCC      $50.00   04/02/2005
...etc

What I want to do is to be able to give something (stored procedure, view, select statement, whatever works really) a start date and end date, and have it return all the enties that match those start and end dates like so (I'm assuming I submitted 04/01 as the start date, 04/02 as the end date):


NAME     START VALUE    END VALUE   START DATE  END DATE
----     -----------    ---------   ----------  ----------
AAA      $50.00         $55.00      04/01/2005  04/02/2005
BBB      $25.00         $30.00      04/01/2005  04/02/2005
CCC      NULL           $50.00      NULL        04/02/2005

Now, I think I could do it using a temporary table and doing the insert/update thing, but I'm not sure how to match up the data and don't know how to handle it when something isn't there on the start date, but is there on the end date (as with CCC above).

I should also say that the end product data is going to be used in SQL Reporting Services, so if you guys know a way to do it using multiple sql datasets that ultimately can be put into the same table, that works too. HELP!!!

-Mark"

will_01
Starting Member

1 Post

Posted - 2005-08-31 : 14:43:51

Try this.

declare @startdate as datetime
declare @enddate as datetime

set @startdate='4/1/2005'
set @enddate='4/2/2005'

select c.name, c.startvalue,d.endvalue,c.startdate,d.enddate from

(

select w.name, x.startvalue,x.startdate

from
(select distinct name from products) w

left outer join

(
select a.name, b.value as startvalue,a.startdate from
(
select name, date as startdate
from products where date=@startdate

) a
left outer join
products b
on(a.name=b.name and a.startdate=b.date)
) x

on (w.name=x.name)

) c

left outer join

(
select w.name, y.endvalue,y.enddate

from
(select distinct name from products) w

left outer join

(
select a.name, b.value as endvalue,a.enddate from
(
select name, date as enddate
from products where date=@enddate

) a
left outer join
products b
on(a.name=b.name and a.enddate=b.date)
) y

on(w.name=y.name)
) d

on(c.name=d.name)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources