Recursive Join Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Recursive Join Problem

Author

Topic

tlehner
Starting Member

2 Posts

Posted - 2006-06-30 : 10:51:17

The following is a complete mock-up of a real situation, so I hope I haven't screwed anything up by trying to translate a much more complex situation into this fake one (syntax, naming, etc.). Anyway, I'm not sure how to fix my problem, but here goes:

From this "sales" table:


sale_id           date                  salesperson       sale_$
1                 1/1/2006              bob               20
2                 1/2/2006              joe               90
3                 1/2/2006              bob               20
4                 1/3/2006              tom               30
5                 1/4/2006              bob               70
6                 2/1/2006              bob               10
7                 2/1/2006              tom               40
8                 2/2/2006              bob               10
9                 2/3/2006              gus               60

I'd like to get the following output from a select query:


salesperson  salesperson  jan06_sales  jan06_$  feb06_sales  feb06_$
bob          bob          3            110      2            20
             gus          0            0        1            60
joe                       1            90       0            0
tom          tom          1            30       1            40

Here's where I'm currently at:


select 
            jan06.salesperson, 
            feb06.salesperson, 
            count(jan06.sale_id) as 'jan06_sales', 
            sum(jan06.sale_$) as 'jan06_$', 
            count(feb06.sale_id) as 'feb06_sales', 
            sum(feb06.sale_$) as 'feb06_$' 
from 
            (select * from sales
             where date >= 1/1/2006 and date <= 1/31/06) as jan06 
full outer join 
            (select * from sales
             where date >= 2/1/2006 and date <= 2/28/06) as feb06 
on 
            jan06.salesperson = feb06.salesperson  
group by 
            jan06.salesperson, 
            feb06.salesperson

This gives me the proper output for everyone except for bob (or anyone else who would have multiple sales in multiple months), who shows up as having 6 sales in both months and equally inflated $ numbers. I'm guessing that 3 jan sales multiplied by 2 feb sales is 6 (so my join is giving me a cross-product...I should've known, eh?). How do I get around this? Am I joining on the wrong field? Grouping incorrectly? Using the wrong type of join?

While I realize that I could run a procedure to poop this out, it would be much better to do it with a query. This will go into an asp page (yes, through an ADO connection to SQL Server 2005) and I'd rather hit the database with one query that I can spit out (the above query currently returns in under a second) instead of picking a sales guys and loading variables with his monthly data...and hitting the database many times in the process, only to move on to all the other sales guys one by one.

Let me know if you've got a solution! Thanks in advance!

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-06-30 : 11:42:12

try this link, see if it helps http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6216

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

eurob
Posting Yak Master

100 Posts

Posted - 2006-06-30 : 11:48:23

I'm not sure why you need two columns for salesperson.


select salesperson,
sum(case when month(date)=1 then 1 else 0 end) 'Jan06_sales',
sum(case when month(date)=1 then sale_$ else 0 end) 'Jan06_sales$',
sum(case when month(date)=2 then 1 else 0 end) 'Feb06_sales',
sum(case when month(date)=2 then sale_$ else 0 end) 'Feb06_sales$'
from rv_sales
group by salesperson
order by salesperson

result:


       jan06s   jan06$  feb06s  feb06$
bob	3	110	2	20
gus	0	0	1	60
joe	1	90	0	0
tom	1	30	1	40

hth

robert

tlehner
Starting Member

2 Posts

Posted - 2006-06-30 : 13:05:40

DonAtWork, thanks for the pivot suggestion...I will be doing some more reading into that and using that on some other projects for sure.

eurob, you are a bad-ass kung-fu master! That's fits what I'm doing precisely. Thanks again.

Take care!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-01 : 01:53:14

Also, read about Cross-tab Reports in sql server help file

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources