Help with division in 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

General SQL Server Forums

New to SQL Server Programming

Help with division in query

Author

Topic

jhon11
Starting Member

42 Posts

Posted - 2007-12-14 : 12:44:44

Hi,

I have a table in which i have two colums say discription and counts.
the table has 10 rows.
This table is created by extracting data from other table means its not a table that exist in system.

Now in my last row i want discription as '%mailed' and count as row1/row2

can u tell me how to do that?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-12-14 : 14:07:24

Where's the query and a data example of your problem?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

jhon11
Starting Member

42 Posts

Posted - 2007-12-14 : 14:12:27

declare @intLoadInstanceId int
declare @productcode int
select @intLoadInstanceId='24'
select @productcode='2'

create table #mailingCounts
(
Decription varchar(100)
,Counts smallint
)

--Count of Total patients
Insert into #mailingCounts
select 'Count of Total Patients',count(distinct patientid)
from dbo.HCOFixed
where intLoadInstanceId=@intLoadInstanceId
and productcode=@productcode

--Count of patients in mailing
Insert into #mailingCounts
select 'Count of Patients in Mailing',count(distinct patientid)
from mailingtable
where intLoadInstanceId=@intLoadInstanceId
and productcode=@productcode

--Count of packets mailed
Insert into #mailingCounts
select 'Count of Packets mailed',count(*)
from mailingtable
where intLoadInstanceId=@intLoadInstanceId
and productcode=@productcode

--This section shows the number of packets mailed to each type of doctor

Insert into #mailingCounts
select 'Count of Packets Mailed to CMHC ',count(*)
from dbo.ProviderMailing p
inner join MailingTable m
on m.intLoadInstanceID=p.intLoadInstanceID
and m.productcode=p.productcode
and m.mailto=p.PrescriberID
where p.intLoadInstanceid = @intLoadInstanceid
and p.type ='Age'
and p.productcode=@productcode

Insert into #mailingCounts
select 'Count of Packets Mailed to Physician',count(*)
from dbo.ProviderMailing p
inner join MailingTable m
on m.intLoadInstanceID=p.intLoadInstanceID
and m.productcode=p.productcode
and m.mailto=p.PrescriberID
where p.intLoadInstanceid = @intLoadInstanceid
and p.type ='Gen'
and p.productcode=@productcode

Insert into #mailingCounts
select 'Count of Packets Mailed to Psychiatrist',count(*)
from dbo.ProviderMailing p
inner join MailingTable m
on m.intLoadInstanceID=p.intLoadInstanceID
and m.productcode=p.productcode
and m.mailto=p.PrescriberID
where p.intLoadInstanceid = @intLoadInstanceid
and p.type ='Psychiatrist'
and p.productcode=@productcode

Insert into #mailingCounts
select 'Count of Packets Mailed to Spec ',count(*)
from dbo.ProviderMailing p
inner join MailingTable m
on m.intLoadInstanceID=p.intLoadInstanceID
and m.productcode=p.productcode
and m.mailto=p.PrescriberID
where p.intLoadInstanceid = @intLoadInstanceid
and p.type ='Spec'
and p.productcode=@productcode

--final output
select * from #mailingCounts

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources