Solved

T-SQL join same table

Posted on 2008-10-17
6
703 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 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

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…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

830 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