Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

T-SQL join same table

Posted on 2008-10-17
6
Medium Priority
?
715 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 143

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 600 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 1400 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Integration Management Part 2
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

916 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