Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

avoiding a cursor 1

Status
Not open for further replies.
Chance1234

Chance1234

IS-IT--Management
Jul 25, 2001
7,871
US
this is bit of a newb question as i dont do much reporting type work with SQL,

I know the use of cursors is generally frowned upon , and i have the following scenario where i woudl use a cursor

Ive never really got the hang of this nested queries but should i be looking at them in the following scenario


First of all i have a table called clients

Code: 
client_id	client_name 		
1	Joe 		
2	Frank 		
3	Sid

Next i have a dealing table that looks something like this

Code: 
d_type	d_client_id	d_amount	d_day 
Reg	1	500	01/01/2007
Reg	1	60	02/01/2007
Reg	1	80	03/01/2007
Reg	2	60	01/01/2007
Reg	2	70	02/01/2007
Lump	1	90	02/01/2007
Lump	2	30	03/01/2007
Lump	2	20	04/01/2007
Lump	3	50	02/01/2007

I then need to produce a report along the folling

Code: 
Report 	Between 	01/01/2007	03/01/2007

client_name	Reg 	Lump	
Joe	640	90	
Frank	130	30	
Sid 	0	50




Chance,

F, G + Its official, its even on a organisation chart et all
 
Sort by date Sort by votes
Code: 
SELECT ClientsTable.ClientId,
       ClientsTable.Name,
       SUM(CASE WHEN OtherTable.D_Type = 'Reg'
                THEN D_Amount
                ELSE 0 END) AS Reg,
       SUM(CASE WHEN OtherTable.D_Type = 'Lump'
                THEN D_Amount
                ELSE 0 END) AS Lump
FROM ClientsTable
INNER JOIN OtherTable ON ClientsTable.ClientId = OtherTable.D_Client_Id

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP
 
Upvote 0 Downvote
Something like this ought to work:

Code: 
select c.client_id
	, sum(case when d.d_type = 'Reg' then d_amount else 0 end) as Reg
	, sum(case when d.d_type = 'Lump' then d_amount else 0 end) as Lump
from clients c
inner join dealing d
on c.client_id = d.d_client_id
where d_day between '20070101' and '20070301'

Basically, you want to sum the amount column only when certain conditions are met.

Hope this helps,

Alex

[small]----signature below----[/small]
Now you can go where the people are one!
Now you can go where they get things done!
 
Upvote 0 Downvote
Code: 
[COLOR=blue]declare[/color] @clients [COLOR=blue]table[/color] (client_id [COLOR=blue]tinyint[/color], client_name [COLOR=blue]varchar[/color](5))
[COLOR=blue]declare[/color] @dealing [COLOR=blue]table[/color] (d_type [COLOR=blue]varchar[/color](4), d_client_id [COLOR=blue]tinyint[/color], d_amount [COLOR=blue]smallint[/color], d_day [COLOR=blue]smalldatetime[/color])

[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @clients
[COLOR=blue]select[/color] 1, [COLOR=red]'Joe'[/color] union
[COLOR=blue]select[/color] 2, [COLOR=red]'Frank'[/color] union
[COLOR=blue]select[/color] 3, [COLOR=red]'Sid'[/color]

[COLOR=blue]insert[/color] [COLOR=blue]into[/color] @dealing
[COLOR=blue]select[/color] [COLOR=red]'Reg'[/color], 1, 500, [COLOR=red]'20070101'[/color] union
[COLOR=blue]select[/color] [COLOR=red]'Reg'[/color], 1, 60, [COLOR=red]'20070201'[/color] union
[COLOR=blue]select[/color] [COLOR=red]'Reg'[/color], 1, 80, [COLOR=red]'20070301'[/color] union
[COLOR=blue]select[/color] [COLOR=red]'Reg'[/color], 2, 60, [COLOR=red]'20070101'[/color] union
[COLOR=blue]select[/color] [COLOR=red]'Reg'[/color], 2, 70, [COLOR=red]'20070201'[/color] union
[COLOR=blue]select[/color] [COLOR=red]'Lump'[/color], 1, 90, [COLOR=red]'20070201'[/color] union
[COLOR=blue]select[/color] [COLOR=red]'Lump'[/color], 2, 30, [COLOR=red]'20070301'[/color] union
[COLOR=blue]select[/color] [COLOR=red]'Lump'[/color], 2, 20, [COLOR=red]'20070401'[/color] union
[COLOR=blue]select[/color] [COLOR=red]'Lump'[/color], 3, 50, [COLOR=red]'20070201'[/color]

[COLOR=blue]select[/color] c.client_name, 
   [COLOR=#FF00FF]coalesce[/color](r.reg_amount, 0), 
   [COLOR=#FF00FF]coalesce[/color](l.lump_amount, 0)
[COLOR=blue]from[/color] @clients c
   [COLOR=#FF00FF]left[/color] [COLOR=blue]join[/color] (
      [COLOR=blue]select[/color] sum(d_amount) reg_amount, d_client_id
      [COLOR=blue]from[/color] @dealing
      [COLOR=blue]where[/color] d_day >= [COLOR=red]'20070101'[/color]
         and d_day < [COLOR=red]'20070302'[/color]
         and d_type = [COLOR=red]'Reg'[/color]
      [COLOR=blue]group[/color] [COLOR=blue]by[/color] d_client_id
   ) r
   [COLOR=blue]on[/color] c.client_id = r.d_client_id
   [COLOR=#FF00FF]left[/color] [COLOR=blue]join[/color] (
      [COLOR=blue]select[/color] sum(d_amount) lump_amount, d_client_id
      [COLOR=blue]from[/color] @dealing
      [COLOR=blue]where[/color] d_day >= [COLOR=red]'20070101'[/color]
         and d_day < [COLOR=red]'20070302'[/color]
         and d_type = [COLOR=red]'Lump'[/color]
      [COLOR=blue]group[/color] [COLOR=blue]by[/color] d_client_id
   ) l
   [COLOR=blue]on[/color] c.client_id = l.d_client_id

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson
[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B> bites again.[/small]
 
Upvote 0 Downvote
And here tested version :)
Code: 
DECLARE @ClientsTable TABLE (clientid int, client_name varchar(200))
INSERT INTO @ClientsTable VALUES(1, 'Joe')
INSERT INTO @ClientsTable VALUES(2, 'Frank')
INSERT INTO @ClientsTable VALUES(3, 'Sid')


DECLARE @OtherTable TABLE (d_type varchar(200), d_client_id int, d_amount numeric(10,2), d_day datetime)
INSERT INTO @OtherTable VALUES('Reg', 1, 500, '01/01/2007')
INSERT INTO @OtherTable VALUES('Reg', 1, 60, '02/01/2007')
INSERT INTO @OtherTable VALUES('Reg', 1, 80, '03/01/2007')
INSERT INTO @OtherTable VALUES('Reg', 2, 60, '01/01/2007')
INSERT INTO @OtherTable VALUES('Reg', 2, 70, '02/01/2007')
INSERT INTO @OtherTable VALUES('Lump', 1, 90, '02/01/2007')
INSERT INTO @OtherTable VALUES('Lump', 2, 30, '03/01/2007')
INSERT INTO @OtherTable VALUES('Lump', 2, 20, '04/01/2007')
INSERT INTO @OtherTable VALUES('Lump', 3, 50, '02/01/2007')

SELECT ClientsTable.ClientId,
       ClientsTable.client_name,
       SUM(CASE WHEN OtherTable.D_Type = 'Reg'
                THEN D_Amount
                ELSE 0 END) AS Reg,
       SUM(CASE WHEN OtherTable.D_Type = 'Lump'
                THEN D_Amount
                ELSE 0 END) AS Lump
FROM @ClientsTable ClientsTable
INNER JOIN @OtherTable OtherTable ON ClientsTable.ClientId = OtherTable.D_Client_Id
WHERE d_day BETWEEN '20070101' AND '20070301'
GROUP BY ClientsTable.ClientId, ClientsTable.client_name

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
Microsoft MVP VFP
 
Upvote 0 Downvote
derr, too slow [dazed]

[small]----signature below----[/small]
Now you can go where the people are one!
Now you can go where they get things done!
 
Upvote 0 Downvote
You'll want to likely not use my solution because it involves derived tables - the case solution should be more efficient.

God, I suck....

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson
[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B> bites again.[/small]
 
Upvote 0 Downvote
kaht,

You don't suck. That derived table solution would still perform better than a cursor.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
I get it... give the moron a sympathy star.

Screw you guys, I'm goin home!
small.jpg


[lol]

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson
[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B> bites again.[/small]
 
Upvote 0 Downvote
How about this ?

Code: 
create table ClientsTest(client_id int, client_name varchar(20))
create table DealingTest(Dtype varchar(5), client_id int, amount numeric(8,2), Dday smalldatetime)

insert into ClientsTest values(1, 'Joe') 
insert into ClientsTest values(2, 'Frank')
insert into ClientsTest values (3, 'Sid')

insert into DealingTest values('Reg', 1, 500, '20070101')
insert into DealingTest values('Reg', 1, 60, '20070201')
insert into DealingTest values('Reg', 1, 80, '20070301') 
insert into DealingTest values('Reg', 2, 60, '20070101') 
insert into DealingTest values('Reg', 2, 70, '20070201') 
insert into DealingTest values('Lump', 1, 90, '20070201') 
insert into DealingTest values ('Lump', 2, 30, '20070301') 
insert into DealingTest values('Lump', 2, 20, '20070401') 
insert into DealingTest values ('Lump', 3, 50, '20070201')




select  client_name,isnull(sum([Lump]),0) as Lump, isnull(sum([Reg]),0) as Reg
from ( select a.client_id,a.client_name,b.Dtype,b.amount,Dday from ClientsTest a inner join DealingTest b on a.client_id = b.client_id)C
PIVOT
( sum(amount)
 For Dtype in ([Lump],[Reg])) as D
where Dday between '2007-01-01' and '2007-03-01'
group by client_name
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

SashaBuilder3
  • Locked
  • Question
select a single row based on conditions in multiple rows 1
Replies
3
Views
908
SashaBuilder3
SashaBuilder3
Steve Yu
  • Locked
  • Question
How secure is MS SQL on the Internet vs behind firewall inside a LAN 1
Replies
3
Views
625
Chris Miller
Chris Miller
Cpreston
  • Locked
  • Question
How to add a Letter to datepart
Replies
5
Views
814
Andrzejek
Andrzejek
grnzbra
  • Locked
  • Question
SELECT TOP 1 of Each of a Group
Replies
5
Views
602
grnzbra
grnzbra
Cpreston
  • Locked
  • Question
Update with a month name 1
Replies
9
Views
1K
gmmastros
gmmastros

Part and Inventory Search

Sponsor

Back
Top