create SQL View with calculation

Hi Experts,

Hopefully the image explains what i'm trying to achieve better than i can type it!

I'm trying to build "viewC", from the data in tableA and tableB.

I'm really struggling, please help!

Thanks
Jon





Bilderman-2009.04.21-21-36-52.png
jondangerAsked:
Who is Participating?
 
Nathan RileyConnect With a Mentor FounderCommented:
select a.adate, a.anumber - b.bnumber
from tablea a
inner join tableb b on a.adate = b.bdate
0
 
jondangerAuthor Commented:
I forgot to mention performance is critical as tableA contains 3,000,000 rows and grows by 50,000 rows per day.

tableB only grows by 1 row per day. (60 rows at present)

i'll add inserts for the test data in the next post :-)

CREATE TABLE [testdb].[dbo].[TableA](
	[aDate] [datetime] NULL,
	[aNumber] [smallint] NULL
) ON [PRIMARY]
 
CREATE TABLE [testdb].[dbo].[TableB](
	[bDate] [datetime] NULL,
	[bNumber] [smallint] NULL
) ON [PRIMARY]

Open in new window

0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
this should do:
select a.aDate
 , a.aNumber
 , a.aNumber - ( select top 1 b.bnumber from tableB b where b.bDate <= a.aDate order by b.bdate desc ) cNumber
 from tableA a 

Open in new window

0
 
SharathConnect With a Mentor Data EngineerCommented:
try this
select a.adate, a.anumber - b.bnumber
  from tablea a
  left join tableb b on dateadd(d,0,datediff(d,0,a.adate)) = dateadd(d,0,datediff(d,0,b.bdate))

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.