Solved

T-SQL join same table

Posted on 2008-10-17
6
702 Views
Last Modified: 2012-05-05
Hi, TableA has the fields: Name, Date, Colour, Volume

Ed, 13/12/2008, Blue, 22
Ed, 14/12/2008, Grey, 26
Tom, 13/12/2008, Red, 15
Tom, 14/12/2008, Blue, 18

I want to have a query that does this:

Select Name, Colour, Volume(today) - Volume(today-1)  as 'Current Volume'

So I would get this (joining on Name) for the date 14/12/2008:
Ed, Grey, 4
Tom, Blue, 3

So basically, for the volume field, it is to be the difference between the volume today and volume yesterday.  I will be turning this into a stored procedure, so you can assume variables to hold dates.

0
Comment
Question by:sublimation
  • 3
  • 2
6 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 22738560
a self-join, please take this as a starter:
SELECT t.*, p.*
  FROM TableA t
  LEFT JOIN TableA p
    ON p.Name = t.Name
   AND p.[Date] = Dateadd(day, -1, t.[Date])

Open in new window

0
 
LVL 6

Expert Comment

by:divyeshhdoshi
ID: 22738758
select p.Name, sum(p.Volume) - sum(v.Volume)
from TableA p inner join TableA t on p.Name=t.Name
where p.[Date]=getDate() and t.[Date]=Dateadd(day,-1,t.[Date])
group by p.Name
0
 
LVL 6

Assisted Solution

by:divyeshhdoshi
divyeshhdoshi earned 150 total points
ID: 22738760
select p.Name, sum(p.Volume) - sum(t.Volume)
from TableA p inner join TableA t on p.Name=t.Name
where p.[Date]=getDate() and t.[Date]=Dateadd(day,-1,t.[Date])
group by p.Name
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 7

Expert Comment

by:Norush
ID: 22738920
Try with:
select p.name, (p.volume - t.volume)
from tblPerson p inner join tblPerson t on p.Name=t.Name
where p.[date] = convert(datetime, convert(varchar(24), GETDATE(), 111))
and t.[date] = convert(datetime, convert(varchar(24), GETDATE()-1, 111))

Open in new window

0
 
LVL 7

Accepted Solution

by:
Norush earned 350 total points
ID: 22738922
Or:
select p.name, sum(p.volume - t.volume)
from tblPerson p inner join tblPerson t on p.Name=t.Name
where p.[date] = convert(datetime, convert(varchar(24), GETDATE(), 111))
and t.[date] = convert(datetime, convert(varchar(24), GETDATE()-1, 111))
group by p.name

Open in new window

0
 
LVL 7

Expert Comment

by:Norush
ID: 22738948
Please note that i used the getdate() function as the 'variable' and i used a table called tblPerson in my testscenario
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
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.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

809 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