Sql Query - 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

Transact-SQL (2000)

Sql Query

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2007-01-08 : 08:00:37

madhavi writes "Hello Guru,

select
p.name as hospital,
'accepted'=case when pr.preauthstatus=1 then count(pr.preauthstatus) end ,
'pending'=case when pr.preauthstatus=2 then count(pr.preauthstatus) end,
'rejected'=case when pr.preauthstatus=3 then count(pr.preauthstatus) end,
'Total'=count(pr.preauthstatus)
from preauthorization pr,provider p
where p.id=pr.providerid
group by p.name,pr.preauthstatus with cube
ORDER BY p.name

here accepted,rejected,pending are the values in preauthstatus table. This query is used for preparing REPORTS.

I'm getting query output like this ..........

HOSPITAL ACCEPTED PENDING REJECTED TOTAL

Asian Heart Institute 134 NULL NULL 134
Asian Heart Institute NULL NULL 4 4
Balaji Hospital 61 NULL NULL 61
Balaji Hospital NULL 1 NULL 1
Balaji Hospital NULL NULL 1 1
Bombay Hospital 298 NULL NULL 298
Bombay Hospital NULL 6 NULL 6
Bombay Hospital NULL NULL 56 56
BSES MGH Hospital 26 NULL NULL 26
BSES MGH Hospital NULL 1 NULL 1
BSES MGH Hospital NULL NULL 1 1
Cumballa Hill Hospital 29 NULL NULL 29
Cumballa Hill Hospital NULL NULL 2 2
Godrej Hospital 28 NULL NULL 28
Godrej Hospital NULL NULL 2 2
Gurunanak Hospital (Bandra) 102 NULL NULL 102
Gurunanak Hospital (Bandra) NULL 7 NULL 7
Gurunanak Hospital (Bandra) NULL NULL 20 20
Inlaks General Hospital 95 NULL NULL 95
Inlaks General Hospital NULL NULL 3 3
Joy Hospital 41 NULL NULL 41
Joy Hospital NULL NULL 5 5
Life Line Hospital 224 NULL NULL 224
Life Line Hospital NULL 1 NULL 1
Life Line Hospital NULL NULL 3 3
Prince Aly Khan 30 NULL NULL 30
Prince Aly Khan NULL 4 NULL 4
Prince Aly Khan NULL NULL 4 4
NULL 10
Sushrut Hospital 95 NULL NULL 95
Sushrut Hospital NULL NULL 10 10
Tata Memorial Hospital 3 NULL NULL 3
Tata Memorial Hospital NULL 1 NULL 1
Wockhardt Hospital 188 NULL NULL 188
Wockhardt Hospital NULL 6 NULL 6
Wockhardt Hospital NULL NULL 22 22

But i want like this.........

Accepted Rejected Pending Total Accepted Rejected Pending Total
Aadhar Nursing Home 4 0 3 7 2 0 0 2
Apurva Heart and Critical Care,Dhule 3 0 0 3 0 0 0 0
Arneja Heart Institute 12 0 0 12 8 0 0 8
Ashwini Hospital,Jalgaon 27 0 0 27 10 0 0 10
Ashwini hospital,Solapur 5 6 0 11 21 13 1 35
Asian Heart Institute 5 0 0 5 2 0 0 2
Bahekar Nursing Home 0 0 1 1 5 0 0 5
Bhagwati Hospital,Badlapur 0 1 0 1 0 0 0 0
Bhaktivedanta Hospital 1 0 0 1 0 0 0 0
Bibekar Hospital 1 1 0 2 0 0 0 0
Bombay Hospital & Medical Research Centre 15 6 1 22 7 2 1 10
City Medical Centre 2 0 0 2 1 0 0 1
Chatrapati Shahu Hospital,Gangakhed 0 0 0 0 1 0 0 1
Chopda Medicare & Research Centre Pvt. Ltd. 4 0 0 4 3 0 0 3
Deenanath Mangeshkar Hospital 42 6 0 48 11 1 0 12
Deepak Hospital 24 1 0 25 12 0 0 12
Desai Accident and General Hospital,Bhosari 0 1 0 1 1 0 0 1
Deshmukh Hospital 1 0 0 1 0 0 0 0
Deshpande Hospital ,Ahmednagar 16 0 0 16 9 0 0 9
Dhanvantari Hospital,Kulgaon,badlapur 1 0 0 1 0 0 0 0
Dhanvantari Fracture, Orthopaedic & Arthroscopy Centre, Kolhapur 3 0 0 3 1 0 0 1
Dhirubhai Ambani Hospital,Lodhivali 1 0 0 1 1 0 0 1
Doshi Nursing Home 4 1 0 5 6 1 0 7
Dr K G Deshpande Memorial Centre,Nagpur 2 0 0 2 0 0 0 0
Dr Kamat's Hospital 0 1 0 1 0 0 0 0
Dr Kunnure Hospital 1 0 0 1
Dr Nalavade Hospital 1 0 0 1 0 0 0 0
Dr Murkey Aayushkam Bal Rugnalaya 2 0 0 2 2 3 0 5
Dr Raghoji Kidney Hospital & Research Centre 15 2 0 17 5 1 0 6
Dr S.B. Gugale Memorial Hospital 5 1 1 7 1 1 0 2"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-08 : 08:37:44

How are we supposed to know how to break up the sums in the first example?

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources