[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


create SQL View with calculation

Posted on 2009-04-21
Medium Priority
Last Modified: 2012-05-06
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!


Question by:jondanger

Author Comment

ID: 24199054
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
CREATE TABLE [testdb].[dbo].[TableB](
	[bDate] [datetime] NULL,
	[bNumber] [smallint] NULL

Open in new window

LVL 12

Accepted Solution

Nathan Riley earned 800 total points
ID: 24199071
select a.adate, a.anumber - b.bnumber
from tablea a
inner join tableb b on a.adate = b.bdate
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 600 total points
ID: 24199076
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

LVL 41

Assisted Solution

Sharath earned 600 total points
ID: 24199222
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


Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question