Solved

T-SQL join same table

Posted on 2008-10-17
6
709 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

617 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