SQL Report Help...Thanks
-
Dear All,
I am breaking my head on a particular report. This report is based on a table in SQL Server 2000 database. Please run the following scripts to create test table & data.......
CREATE TABLE test_sales
(
com_name varchar(3),
off_name varchar(3),
usr_name varchar(20),
status varchar(20),
amount decimal(9,2)
 
)INSERT INTO test_sales VALUES('abc', '123', 'andy', 'sold', 200)
INSERT INTO test_salest VALUES('abc', '123', 'andy', 'waiting', 400)
INSERT INTO test_salest VALUES('abc', '123', 'andy', 'dropped', 600)
INSERT INTO test_sales VALUES('abc', '123', 'mark', 'sold', 300)
INSERT INTO test_sales VALUES('abc', '123', 'mark', 'dropped', 400)
INSERT INTO test_sales VALUES('abc', '567', 'larsen', 'sold', 480)
INSERT INTO test_sales VALUES('abc', '123', 'larsen', 'waiting', 550)
INSERT INTO test_sales VALUES('def', '678', 'steve', 'sold', 500)
INSERT INTO test_sales VALUES('def', '678', 'robin', 'dropped', 350)
INSERT INTO test_sales VALUES('def', '907', 'mike', 'waiting', 450)
INSERT INTO test_sales VALUES('def', '907', 'sarah', 'sold', 460)
INSERT INTO test_sales VALUES('def', '806', 'dan', 'waiting', 230)
INSERT INTO test_sales VALUES('def', '564', 'natasha', 'dropped', 560)
When you finish, you will have a test table containing data like this....
----------------------------------------------------------------------
comp_name off_name usr_name status amount
----------------------------------------------------------------------
abc 123 andy sold £200
abc 123 mark sold £300
abc 567 larsen sold £480
abc 123 andy waiting £400
abc 123 andy dropped £600
abc 123 mark dropped £400
abc 567 larsen waiting £550
def 678 steve sold £500
def 907 mike waiting £450
def 678 robin dropped £350
def 907 sarah sold £460
def 806 dan waiting £230
def 564 natasha dropped £560
============================================
All I need is the following outputs.
Office-wise Sales Report
-----------------------------------------------------------------------
off_name/usr_name waiting sold dropped
-----------------------------------------------------------------------
123
andy £400 £200 £600
mark £0 £300 £400
567
larsen £550 £480 £0
678
steve £0 £500 £0
robin £0 £0 £350
907
mike £450 £0 £0
sarah £0 £460 £0
806
dan £230 £0 £0
564
natasha £0 £0 £560
------------------------------------------------------------------------
£1630 £1940 £1910
========================================================
Company-wise Sales Report
-----------------------------------------------------------------------
com_name waiting sold dropped
-----------------------------------------------------------------------
abc £950 £980 £1000
def £680 £960 £ 910
------------------------------------------------------------------------
£1630 £1940 £1910
========================================================
Please help me.....Thanks very much
Phani
-
Is this what you are looking for?
select off_name, usr_name,
sum(case when status = 'waiting' then amount else 0 end),
sum(case when status = 'sold' then amount else 0 end),
sum(case when status = 'dropped' then amount else 0 end)
from test_sales
group by off_name, usr_name
order by off_name, usr_name
select com_name,
sum(case when status = 'waiting' then amount else 0 end),
sum(case when status = 'sold' then amount else 0 end),
sum(case when status = 'dropped' then amount else 0 end)
from test_sales
group by com_name
order by com_name
select '',
sum(case when status = 'waiting' then amount else 0 end),
sum(case when status = 'sold' then amount else 0 end),
sum(case when status = 'dropped' then amount else 0 end)
from test_sales
-
Hi Jesper,
This is super stuff. Thanks a lot. Can you tell me how I can get the following output (without using any cursors)...
Your Solution
select off_name, usr_name,
sum(case when status = 'waiting' then amount else 0 end),
sum(case when status = 'sold' then amount else 0 end),
sum(case when status = 'dropped' then amount else 0 end)
from test_sales
group by off_name, usr_name
order by off_name, usr_name
The OUTPUT I need is.....
-----------------------------------------------------------------------
off_name/usr_name waiting sold dropped
-----------------------------------------------------------------------
123
andy £400 £200 £600
mark £0 £300 £400
-----------------------------------------------------------------------
Sorry to be a pain.
Phani
-
Try this:
select isnull(dt.usr_name, dt.off_name), dt.waiting, dt.sold, dt.dropped
from
(
select off_name, usr_name,
cast(sum(case when status = 'waiting' then amount else 0 end) as varchar)as waiting,
cast(sum(case when status = 'sold' then amount else 0 end) as varchar) as sold,
cast(sum(case when status = 'dropped' then amount else 0 end) as varchar) as dropped
from test_sales
group by off_name, usr_name
union all
select distinct off_name, null, '', '', '' from test_sales
)
dt
order by dt.off_name, dt.usr_name
-
Jesper,
Thats the exact output I want. Smashing stuff mate. Thank you very much.
Phani
-
Hi Jesper,
I am in need of your help mate. Last time I asked for 2 seperate outputs for company & office. Now the client is asking for an output like this....
Sales Report
-----------------------------------------------------------------------
com_name/off_name/usr_name waiting sold dropped
-----------------------------------------------------------------------
company: abc
Office: 123
andy £400 £200 £600
mark £0 £300 £400
Office: 567
larsen £550 £480 £0
Totals: £950 £980 £1000
company: def
Office: 678
steve £0 £500 £0
robin £0 £0 £350
907
mike £450 £0 £0
sarah £0 £460 £0
Office: 806
dan £230 £0 £0
Office: 564
natasha £0 £0 £560
Totals: £950 £980 £1000
========================================================
I was wondering if this kind of output can be obtained without using a cursor. Any help in this matter is deeply appreciated.
Cheers......Phani
-
Hello Phani,
as the requirements are beginning to be complicated, I would suggest that you go with the simplest solution
select com_name, off_name, usr_name,
sum(case when status = 'waiting' then amount else 0 end),
sum(case when status = 'sold' then amount else 0 end),
sum(case when status = 'dropped' then amount else 0 end)
from test_sales
group by com_name, off_name, usr_name
order by com_name, off_name, usr_name
and format the result in frontend - outside SQL server. Although it is possible to achieve certain level of formatting in SQLS, it is quite hard to maintain such code and the performance is not good.
-
Hi Vladan,
Thanks for the response mate. After wasting nearly 1 day on this problem, I also came to the same conclusion.
Phani
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply