Help with stored procedure - 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 stored procedure

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-02-16 : 14:48:38

I have this stored procedure where I want to add the iCnt field from rpt 2 and 4 and make those results rpt 5. How can I add it to this stored procedure or would it be best to put the info into a temp file then add the fields afterwards to get report 5. I hope all makes sense

I have to add the iCnt fields of the rib(rpt 2) and spouse (rpt4) to get rib/spouse (rpt 5)

--nation Rib totals

Select Sort='1',Reg='NAT', Region='0', Area='00', Dist = '000', Doc = '000', convert(char,d.mnth_endt,1) as DowrDt, iCnt, ncnt, rpt='2' 
From
 (select mnth_endt, sum(zip_count) as iCnt 
from  [dbo].[iClaimsTemp1] 

where typ_of_actn='a' and abap_prc_cd NOT IN('y','s') and Bicnum IN('002','010') and inet_ind = '1'
Group by mnth_endt
)a
right join
(select mnth_endt, sum(zip_count) as ncnt 
from  [dbo].[iClaimsTemp1]  
Where typ_of_actn = 'a' and Bicnum = '002' and inet_ind = 'n' and abap_prc_cd NOT IN('y','s')
Group by mnth_endt
)b
on a.mnth_endt = b.mnth_endt


--nation totals - spouse  

insert  iClaims1
Select Sort='1',Reg='NAT', Region='0', Area='00', Dist = '000', Doc = '000', convert(char,h.mnth_endt,1) as DowrDt, iCnt, ncnt, rpt='4' 
From
 (select mnth_endt, sum(zip_count) as iCnt 
from  [dbo].[iClaimsTemp1]  
Where typ_of_actn='a' and Bicnum in ('003', '004') and inet_ind='4'
Group by mnth_endt
)c
right join
(select mnth_endt, sum(zip_count) as ncnt 
from  [dbo].[iClaimsTemp1]  
where (Typ_Of_Actn = 'A' and Bicnum In ( '003' , '004') and Inet_Ind = 'N')
Group by mnth_endt
)d
on c.mnth_endt = d.mnth_endt

--nation totals - rib/spouse

insert  iClaims1 
Select Sort='1',Reg='NAT', Region='0', Area='00', Dist = '000', Doc = '000', convert(char,hh.mnth_endt,1) as DowrDt, iCnt, ncnt, rpt='5' 
From
 (select mnth_endt, sum(zip_count) as iCnt 
from  [dbo].[iClaimsTemp1]  
[/b]What would I put here to get the totals of rpt 2 and rpt 4 to get rpt 5?[/b]


Group by mnth_endt
)e
right join
(select mnth_endt, sum(zip_count) as ncnt 
from  [dbo].[iClaimsTemp1]  
where (bicnum = '002' or bicnum = '202' or bicnum = '003' or bicnum = '004' or bicnum = '203' or bicnum = '204') and reg is not null and area is not null 
Group by mnth_endt
)f
on e.mnth_endt = f.mnth_endt

Here's some data from the table:



CREATE TABLE [dbo].[TblA](
	[Sort] [varchar](1) NOT NULL,
	[Reg] [varchar](3) NOT NULL,
	[Region] [varchar](1) NOT NULL,
	[Area] [varchar](2) NOT NULL,
	[Dist] [varchar](3) NULL,
	[Doc] [varchar](3) NOT NULL,
	[DowrDt] [smalldatetime] NULL,
	[iCnt] [int] NULL,
	[rpt] [varchar](1) NOT NULL
) ON [PRIMARY]


insert into TblA
select '1', 'NAT', '0', '00', '000', '000', '11/26/2010','24', '1'  union all
select '1', 'NAT', '0', '00', '000', '000', '12/24/2010','24', '1'  union all
select '1', 'NAT', '0', '00', '000', '000', '01/28/2011','56', '1'  union all
select '1', 'NAT', '0', '00', '000', '000', '11/26/2010','24', '2'  union all
select '1', 'NAT', '0', '00', '000', '000', '12/24/2010','59', '2'  union all
select '1', 'NAT', '0', '00', '000', '000', '01/28/2011','24', '2' union all
select '1', 'NAT', '0', '00', '000', '000', '11/26/2010','56', '3'  union all
select '1', 'NAT', '0', '00', '000', '000', '12/24/2010','99', '3'  union all
select '1', 'NAT', '0', '00', '000', '000', '01/28/2011','36', '3'  union all
select '1', 'NAT', '0', '00', '000', '000', '11/26/2010','56', '4'  union all
select '1', 'NAT', '0', '00', '000', '000', '12/24/2010','78', '4'  union all
select '1', 'NAT', '0', '00', '000', '000', '01/28/2011','23', '4'

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-02-16 : 17:13:09

JJ..what would be the expected report output?

Poor planning on your part does not constitute an emergency on my part.

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-02-16 : 18:18:32

I am adding the iCnt column where rpt is 2 and 4 by dowrdt.

So for 11/26/2010 I added rpt 2 = 24 + rpt 4 = 56 to give me the iCnt of 80


Sort Reg   Region  Area	Dist  Doc	Dowrdt	      iCnt     rpt
1    NAT     0	    00	000   000	11/26/2010	80	5
1    NAT     0      00  000   000	12/24/2010	137	5
1    NAT     0	    00	000   000	01/28/2010	47	5

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources