Joining table to itself

In myTable I have
RequiredAmount
RequiredDate
GrantedAmount
GrantedDate
I wish to obtain one view showing the sum per year.
For the required part it would be:
SELECT YEAR(RequiredDate) AS myYear, SUM(RequiredAmount) AS reqAmount
FROM myTable
GROUP BY YEAR(RequiredDate)
And for the Granted part:
SELECT YEAR(GrantedDate) AS myYear, SUM(GrantedAmount) AS grantAmount
FROM myTable
GROUP BY YEAR(GrantedDate)
Now, what I need is a presentation with three columns:
myYear, reqAmount, grantAmount
I'm fooling around with the table joined to itself, but can't seem to
get it right...maybe wrong approach?
Regards /SnedkerRead about Cross-tab reports in sql server help file
Madhivanan
Morten Snedker wrote:
> In myTable I have
> RequiredAmount
> RequiredDate
> GrantedAmount
> GrantedDate
> I wish to obtain one view showing the sum per year.
> For the required part it would be:
> SELECT YEAR(RequiredDate) AS myYear, SUM(RequiredAmount) AS reqAmount
> FROM myTable
> GROUP BY YEAR(RequiredDate)
>
> And for the Granted part:
> SELECT YEAR(GrantedDate) AS myYear, SUM(GrantedAmount) AS grantAmount
> FROM myTable
> GROUP BY YEAR(GrantedDate)
>
> Now, what I need is a presentation with three columns:
> myYear, reqAmount, grantAmount
> I'm fooling around with the table joined to itself, but can't seem to
> get it right...maybe wrong approach?
>
> Regards /Snedker|||use a full outer join (self)..
something like this..
untested..
SELECT COALESCE(A.myYear,B.myYear), COALESCE(A.reqAmount,0),
COALESCE(B.grantAmount,0)
FROM
(SELECT YEAR(RequiredDate) AS myYear, SUM(RequiredAmount) AS reqAmount
FROM myTable
GROUP BY YEAR(RequiredDate)
) A FULL OUTER JOIN
(SELECT YEAR(GrantedDate) AS myYear, SUM(GrantedAmount) AS grantAmount
FROM myTable
GROUP BY YEAR(GrantedDate)) B
ON A.myYear= B.myYear
Hope this helps.
-Omni|||Can you see if this works for you
SELECT
YEAR(tDate) as TransYear,
SUM(CASE WHEN tType = 'R' then tAmt else 0 end) as SumReq,
SUM(CASE WHEN tType = 'G' then tAmt else 0 end) as GraReq
FROM
(
select 'R' as tType,reqAmount as tAmt,ReqDate as tDate FROM Mytable
UNION ALL
select 'G' as tType,GrantAmount as tAmt,GrantDate as tDate FROM Mytable
) x
GROUP BY YEAR(tDate)
- Sha Anand
"Morten Snedker" wrote:

> In myTable I have
> RequiredAmount
> RequiredDate
> GrantedAmount
> GrantedDate
> I wish to obtain one view showing the sum per year.
> For the required part it would be:
> SELECT YEAR(RequiredDate) AS myYear, SUM(RequiredAmount) AS reqAmount
> FROM myTable
> GROUP BY YEAR(RequiredDate)
>
> And for the Granted part:
> SELECT YEAR(GrantedDate) AS myYear, SUM(GrantedAmount) AS grantAmount
> FROM myTable
> GROUP BY YEAR(GrantedDate)
>
> Now, what I need is a presentation with three columns:
> myYear, reqAmount, grantAmount
> I'm fooling around with the table joined to itself, but can't seem to
> get it right...maybe wrong approach?
>
> Regards /Snedker
>|||Hi,
Nice solution.. But Why do you need a case? Can't it be something simple
like this.
select TransYear, sum(GrantAmount) SumGrant, sum(reqAmount) SumReq
from
(select 0 as GrantAmount,reqAmount ,year(ReqDate) as TransYear FROM Mytable
UNION ALL
select GrantAmount,0 as reqAmount, year(GrantDate) as TransYear FROM
Mytable) x
group by TransYear
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||If your a consultant you know your clients couldn't give
a whit about how you provide a solution just as long as you
give them one.Perhaps we can help.Check out RAC for easy
solutions to all kinds of data manipulation problems including
crosstabs on sql server.
www.rac4sql.net

join statement required

I have the following tables

tblFavourites
FavouriteSince datetime
UserCodeOwner int (the usercode of the user whose favouritelist this is)
UserCodeFavourite int (the usercode of the user who has been added to the favouritelist of usercodeowner)

EXAMPLE DATA
10/14/2006 7:32:30 PM 4 7
10/16/2006 11:24:01 PM 4 5
10/16/2006 10:55:08 PM 5 4

tblUserData
UserID uniqueidentifier
UserName nvarchar(50)
UserCode int

aspnet_Users
UserID uniqueidentifier
LastActivityDate datetime

Now I need a join statement that selects the fields
aspnet_Users.UserID,aspnet_Users.LastActivityDate,tblUserData.Username,tblUserData.UserCode,tblFavourites.FavouriteSince
for all tblFavourites.UserCodeFavourite where tblFavourites.UserCodeOwner=4

Could someone provide me with the join statement because I dont get it anymore :)

I think I'm a bit confused on your relationships... tblFavourites appears to have two columns that link to user records but its not clear where. Which table and column does UserCodeOwner and UserCodeFavourite link to? Do they link to the UserCode or UserID column of tblUserData or do they link to the aspnet_Users table instead?

Regards,

ZD

|||And I think I didnt explain that very well :)
The colums in the tblFavourites table link to the tblUserData.UserCode field.|||SELECT c.UserID,c.LastActivityDate,b.Username,b.UserCode,a.FavouriteSince
FROM tblFavourites a LEFT OUTER JOIN tblUserData b ON a.UserCodeOwner=b.UserCode
LEFT OUTER JOIN aspnet_Users c ON c.UserID=b.UserID
WHERE a.UserCodeOwner=4|||Thanks, but this statement selects the data for the UserCodeOwner (in this case 4).
I need the statement to select the data for all the UserCodeFavourite in which record the UserCodeOwner is 4.

Here's the content of tblUserData
UserID UserCode UserName

8ff3f242-2aab-499c-b0bf-f450969590ad4Jodie23e72b1a-b701-4b6d-9c39-83018dae8db15Jean71e1974f-8f1d-4e12-a0a6-7c8f3be87bc66Marie18ebec6d-adfa-4249-8c46-8daacca724e57Simon

Here's the content of tblFavourites:

FavouriteSince UserCodeOwner UserCodeFavourite

10/14/2006 7:32:30 PM4710/16/2006 10:55:08 PM5410/16/2006 11:24:01 PM4510/24/2006 10:07:46 PM79

And here's the content of aspnet_Users
UserID LastActivityDate
8ff3f242-2aab-499c-b0bf-f450969590ad 10/22/2006 10:32:44 PM
23e72b1a-b701-4b6d-9c39-83018dae8db1 10/16/2006 8:55:15 PM
230b9534-0d1e-4163-92f1-5f80927cac13 9/18/2006 7:53:49 PM
18ebec6d-adfa-4249-8c46-8daacca724e5 10/23/2006 9:55:45 PM

So in this case I want the LastActivityDate, UserName, FavouriteSince from UserCode 5 and 7
I need a select statement that for parameter usercode (value in this case 4) return:

LastActivityDate FavouriteSince UserName
10/16/2006 8:55:15 PM 10/16/2006 11:24:01 PM Jean
10/23/2006 9:55:45 PM 10/14/2006 7:32:30 PM Simon

I know this is tricky because a record in the aspnet_Users table is identified based on UserID, whereas in tblFavourites its on UserCode, so I need to get the matching UserCode and UserID from tblUserData in order to get the right lastactivitydate for a specific user.

Can you help me with the right statement?|||

SELECT u.LastActivityDate,f.FavouriteSince,ud.UserName

FROM tblFavourites f

JOIN tblUserData ud ON (ud.UserCode=f.UserCodeFavourite)

JOIN aspnet_Users u ON (u.UserID=ud.UserID)

WHERE f.UserCodeOwner=4

|||

If I understand your problem correctly, the following should work:

SELECT u.LastActivityDate
, f.FavouriteSince
, n.UserName
FROM aspnet_Users u, tblFavourites f, tblUserData n
WHERE n.UserID = u.UserID
AND n.UserCode = f.UserCodeOwner
AND n.UserCode = '4'

|||Sorry Motley - Didn't mean to step on your toes there. I didn't see your post until after I saved mine.|||Heh, no problem. As long as the question gets answered.|||

and that it is! :)