create SQL View with calculation

Posted on 2009-04-21
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

    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

    LVL 11

    Accepted Solution

    select a.adate, a.anumber - b.bnumber
    from tablea a
    inner join tableb b on a.adate = b.bdate
    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    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 40

    Assisted Solution

    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now