problem in writing a sql query

Question

Getting a problem in writing an sql query. two tables:

1st: created patient table                        2nd: already created doc table
     patientid  patientname  docid  workstatus          docid  docname
      1          aaa          2          10               1      ggg
      2          bbb          2          20               2      hhh
      3          ccc          1          10               3      iii 
      4          ddd          3          10
      5          eee          3          20
      6          fff          2          10

expected output:

docname workstatus(10) workstatus(20)
ggg      1               0
hhh      2               1
iii      1               1

can also use temporary tables between the queries

Thanks in advance

Please format this if you expect anyone to read it or respond to it. — Larry Lustig
– Larry Lustig, Commented Oct 18, 2010 at 20:19
If I had edit capability, I would edit the tables to look like tables. @satwik, could you help here? — Andrew
– Andrew, Commented Oct 18, 2010 at 20:19
Format properly and avoid shortcuts (pblm). Refer to these guidelines before posting a question: tinyurl.com/so-hints — rkg
– rkg, Commented Oct 18, 2010 at 20:20
BTW patient name is an extremely poor choice for a column. What happens when you want to sort the names in a report in alphabetical order by Lastname, firstname (the most common sort choice for names). And you will get trash in this column like John Smith and Smith, John or Smith, John, IV, or JOhn Smith III, or Smith IV, John and you won't be able to diplay the data in any consistent format. Always store all names in separate firstname, middlename, lastname and suffix fields. — HLGEM
– HLGEM, Commented Oct 18, 2010 at 21:34

Laramie · Accepted Answer · 2010-10-18 20:54:45Z

4

Try this

Full working example

declare @patient as table(
patientID int IDENTITY(1,1) NOT NULL,
patientName varchar(25),
docID int,
workstatus smallint
)

declare @doc as table(
docID int IDENTITY(1,1) NOT NULL,
docname varchar(25)
)

insert into @patient
select 'aaa', 2, 10
union all
select 'bbb', 2, 20
union all
select 'ccc', 1, 10
union all
select 'ddd', 3, 10
union all
select 'eee', 3, 20
union all
select 'fff', 2, 10


insert into @doc
select 'ggg'
union all
select 'hhh'
union all
select 'iii'

select docname, 
      SUM(case when t1.workstatus = 10 THEN 1 ELSE 0 END) as [workstatus(10)],
      SUM(case when t1.workstatus = 20 THEN 1 ELSE 0 END) as [workstatus(20)] 
    from @patient t1
    inner join @doc t2 on t1.docid=t2.docid
    GROUP BY docname

edited Oct 18, 2010 at 20:54

answered Oct 18, 2010 at 20:26

Laramie

5,5972 gold badges41 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

satwik Over a year ago

Thanks for help but its not showing anything in workstatus column

Laramie Over a year ago

Hmmm. Can I see your exact Query?

satwik Over a year ago

I just got this much and i asked to get the output like this as shown above and they asked me to use the temp tables

satwik Over a year ago

create table patients(patientid,patientname,doctorid,workflowstatus) 1 'john' 2 400 2 'steve' 2 370 3 'dave' 1 400 4 'yyyy' 3 400 5 'zzzz' 3 370 6 'dddd' 2 400 create table Doctor(doctorid,doctorname). 1 'ravi' 2 'kkk' 3 'ttt' write a query to display doctorname,totalpatientcount(workflowstatus=400),totalpatientcount(workflowstatus=370) expected output: 'lll',1,0 'kkk',2,1 'ttt',1,1 Clue:try to think of using temporary tables in between your queries

Laramie Over a year ago

This looks like homework. I'm not sure what your prof meant by "try to think of using temp tables". If the answer helped you learn, please mark it as correct!

|

Vishal · Accepted Answer · 2010-10-18 20:29:53Z

0

Select  d.docname, 
        SUM(case when c.workstatus = 10 THEN 1 ELSE 0 END) as [WorkStatus(10)],
        SUM(case when c.workstatus = 20 THEN 1 ELSE 0 END) as [WorkStatus(20)] 
from created_patient_table c
inner join already_created_doc_table d on c.docid=d.docid
group by d.docid,d.docname

answered Oct 18, 2010 at 20:29

Vishal

12.4k20 gold badges85 silver badges129 bronze badges

Collectives™ on Stack Overflow

problem in writing a sql query

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related