Need help writing stored procedure

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

Need help writing stored procedure

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-08-18 : 11:59:57

This query:

select count(claim) as claim, FO
from pendingcases
where fo='a00'
group by claim, FO

Gives me this:


Claim         FO
1	      A00
1	      A00
1	      A00
1	      A00
1	      A00
1	      A00

How do I get this to appear:

Claim FO
6 A00
6 A01

Here's the table below. I have other's that I want summed up.



FO              Claim   CI      PerClaim  SA              Bgndte
A00	   	14788	02	14788	ISCH	         10/1998 
A00	   	15392	02	14788	ISCH	         08/2002
A00	   	16878	03	53091	AXVI	         05/1999
A00	   	22471	04	22471	AXVI	         02/2003
A00	   	32492	03	35690	AXVI	         01/2001
A00	   	32492	02	35690	AXVI	         01/1998
A01	   	43893	05	43893	AXVI	         01/2006
A01	   	52482	06	55855	AHOD	         06/2004
A01	   	52482	06	61919	AXVI	         06/2004
A01	   	54673	04	54673	AMAR	         07/2006
A01	   	54673	02	54673	AMAR	         03/2008
A01	   	55375	01	57149	AMAR	         01/1999
A02	   	60039	02	52782	AMAR	         06/2005
A02	   	60039	02	52761	AMAR	         06/2005
A02	   	60071	03	52759	AMAR	         08/2003
A02	   	60071	03	52743	AMAR	         08/2003
A02	   	60071	03	51570	AMAR	         04/2004
A03	   	55197	03	56482	AMAR	         09/1999
A03	   	55197	03	54904	AMAR	         09/1999
A03	   	55259	03	55259	AMAR	         10/2003
A03	   	55711	02	54982	AHOD	         08/2000
A03	   	55887	02	55887	ISCH	         09/1998
A03	   	55989	03	55989	AMAR	         06/2004
A03	   	55995	04	55813	AMAR	         10/2002
A04	   	46215	07	46215	AMAR	         06/2005
A04	   	46215	07	46427	AMAR	         06/2005
A04	   	46269	01	46385	ISCH	         02/1998
A04	   	46419	04	46419	AMAR	         12/2006
A04	   	46419	04	46123	AMAR	         12/2006
A05	   	60108	04	60047	AXVI	         06/1998
A05	   	64918	05	58517	AMAR	         07/2006
A05	   	64918	05	58541	AMAR	         07/2006
A05	   	64922	03	58592	AMAR	         05/2004
A05	   	64922	03	52691	AMAR	     	 05/2004
A06	   	03180	05	42353	AXVI	         10/1999
A06	   	03180	05	41653	AXVI	         10/1999
A06	   	04094	02	04992	ISCH	         11/2007
A06	   	04102	03	41653	AXVI	         05/2000
A07	   	42795	02	42755	AMAR	         05/2010
A07	   	50117	06	50225	AXVI	         08/2002
A07	   	50117	06	50127	AXVI	         08/2002
A07	   	50117	06	50121	AXVI	         08/2002
A07	   	50117	06	50117	ISCH	         08/2002
A07	   	50119	01	50117	ISCH	         02/1999

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-18 : 12:31:15

COUNT(*)????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2010-08-18 : 12:32:44

select count(claim) as claim, FO
from pendingcases
where fo='a00'
group by ~~claim,~~ FO

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-08-18 : 12:44:52

Thanks both of you that worked! I added distinct to get them all listed and took out the where clause.

select count(distinct claim) as claim, fo
from pendingcases
group by fo
order by fo

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-08-18 : 14:52:14

I'm back

I figured I now need to add the counts into one table then do my sums from there.

I got this to work separatly how do I get this to run and insert into Test1?

I tried this it's giving me incorrect syntax near 'b'

insert into test1
select a.mmpending, b.recpending, a.fo
from
(select count(claim) as MMPending, fo
from pendingcases
group by fo
)a

right join
(SELECT count(distinct claim) as RecPending, p.FO, n.REGION_LTR, n.regionacronym
FROM PendingCases AS p
INNER JOIN DocFile n
ON n.DOC = p.FO
group by ssn, fo, region_ltr, regionacronym
)b

MSquared
Yak Posting Veteran

52 Posts

Posted - 2010-08-18 : 15:10:34

I think you're making it harder than it needs to be. try this:


declare @t table (
FO varchar (3), Claim varchar(10),   CI CHAR(2), 
      PerClaim VARCHAR(6),  SA CHAR(4),
       Bgndte char(7))
       
insert into @t
select 'A00','14788','02','14788','ISCH','10/1998 ' union all 
select 'A00','15392','02','14788','ISCH','08/2002' union all 
select 'A00','16878','03','53091', 'AXVI','05/1999' union all 
select 'A00','22471','04','22471', 'AXVI','02/2003' union all 
select 'A00','32492','03','35690', 'AXVI','01/2001' union all 
select 'A00','32492','02','35690', 'AXVI','01/1998' union all 
select 'A01','43893','05','43893', 'AXVI','01/2006' union all 
select 'A01','52482','06','55855', 'AHOD','06/2004' union all 
select 'A01','52482','06','61919', 'AXVI','06/2004' union all 
select 'A01','54673','04','54673', 'AMAR','07/2006' union all 
select 'A01','54673','02','54673', 'AMAR','03/2008' union all 
select 'A01','55375','01','57149', 'AMAR','01/1999' union all 
select 'A02','60039','02','52782', 'AMAR','06/2005' union all 
select 'A02','60039','02','52761', 'AMAR','06/2005' union all 
select 'A02','60071','03','52759', 'AMAR','08/2003' union all 
select 'A02','60071','03','52743', 'AMAR','08/2003' union all 
select 'A02','60071','03','51570', 'AMAR','04/2004' union all 
select 'A03','55197','03','56482', 'AMAR','09/1999' union all 
select 'A03','55197','03','54904', 'AMAR','09/1999' union all 
select 'A03','55259','03','55259', 'AMAR','10/2003' union all 
select 'A03','55711','02','54982', 'AHOD','08/2000' union all 
select 'A03','55887','02','55887','ISCH','09/1998' union all 
select 'A03','55989','03','55989', 'AMAR','06/2004' union all 
select 'A03','55995','04','55813', 'AMAR','10/2002' union all 
select 'A04','46215','07','46215', 'AMAR','06/2005' union all 
select 'A04','46215','07','46427', 'AMAR','06/2005' union all 
select 'A04','46269','01','46385','ISCH','02/1998' union all 
select 'A04','46419','04','46419', 'AMAR','12/2006' union all 
select 'A04','46419','04','46123', 'AMAR','12/2006' union all 
select 'A05','60108','04','60047', 'AXVI','06/1998' union all 
select 'A05','64918','05','58517', 'AMAR','07/2006' union all 
select 'A05','64918','05','58541', 'AMAR','07/2006' union all 
select 'A05','64922','03','58592', 'AMAR','05/2004' union all 
select 'A05','64922','03','52691', 'AMAR','05/2004' union all 
select 'A06','03180','05','42353', 'AXVI','10/1999' union all 
select 'A06','03180','05','41653', 'AXVI','10/1999' union all 
select 'A06','04094','02','04992','ISCH','11/2007' union all 
select 'A06','04102','03','41653', 'AXVI','05/2000' union all 
select 'A07','42795','02','42755', 'AMAR','05/2010' union all 
select 'A07','50117','06','50225', 'AXVI','08/2002' union all 
select 'A07','50117','06','50127', 'AXVI','08/2002' union all 
select 'A07','50117','06','50121', 'AXVI','08/2002' union all 
select 'A07','50117','06','50117','ISCH','08/2002' union all 
select 'A07','50119','01','50117','ISCH','02/1999'

select FO, count(claim) as MMPending, count(distinct claim) as RecPending
from @t
group by fo

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-08-18 : 15:26:08

Wow thanks what a huge difference I didn't realize I could do a count on the same field (claim). My way was complicated!

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-08-23 : 10:39:32

I'm back...

I have to compare last weeks data to see which records were cleared with this weeks data.

So If you take this table (that MSquared set up for me)


declare @t table (
FO varchar (3), Claim varchar(10),   CI CHAR(2), 
      PerClaim VARCHAR(6),  SA CHAR(4),
       Bgndte char(7))
       
insert into @t
select 'A00','14788','02','14788','ISCH','10/1998 ' union all 
select 'A00','15392','02','14788','ISCH','08/2002' union all 
select 'A00','16878','03','53091', 'AXVI','05/1999' union all 
select 'A00','22471','04','22471', 'AXVI','02/2003' union all 
select 'A00','32492','03','35690', 'AXVI','01/2001' union all 
select 'A00','32492','02','35690', 'AXVI','01/1998' union all 
select 'A01','43893','05','43893', 'AXVI','01/2006' union all 
select 'A01','52482','06','55855', 'AHOD','06/2004' union all 
select 'A01','52482','06','61919', 'AXVI','06/2004' union all 
select 'A01','54673','04','54673', 'AMAR','07/2006' union all 
select 'A01','54673','02','54673', 'AMAR','03/2008' union all 
select 'A01','55375','01','57149', 'AMAR','01/1999' union all 
select 'A02','60039','02','52782', 'AMAR','06/2005' union all 
select 'A02','60039','02','52761', 'AMAR','06/2005' union all 
select 'A02','60071','03','52759', 'AMAR','08/2003' union all 
select 'A02','60071','03','52743', 'AMAR','08/2003' union all 
select 'A02','60071','03','51570', 'AMAR','04/2004' union all 
select 'A03','55197','03','56482', 'AMAR','09/1999' union all 
select 'A03','55197','03','54904', 'AMAR','09/1999' union all 
select 'A03','55259','03','55259', 'AMAR','10/2003' union all 
select 'A03','55711','02','54982', 'AHOD','08/2000' union all 
select 'A03','55887','02','55887','ISCH','09/1998' union all 
select 'A03','55989','03','55989', 'AMAR','06/2004' union all 
select 'A03','55995','04','55813', 'AMAR','10/2002' union all 
select 'A04','46215','07','46215', 'AMAR','06/2005' union all 
select 'A04','46215','07','46427', 'AMAR','06/2005' union all 
select 'A04','46269','01','46385','ISCH','02/1998' union all 
select 'A04','46419','04','46419', 'AMAR','12/2006' union all 
select 'A04','46419','04','46123', 'AMAR','12/2006' union all 
select 'A05','60108','04','60047', 'AXVI','06/1998' union all 
select 'A05','64918','05','58517', 'AMAR','07/2006' union all 
select 'A05','64918','05','58541', 'AMAR','07/2006' union all 
select 'A05','64922','03','58592', 'AMAR','05/2004' union all 
select 'A05','64922','03','52691', 'AMAR','05/2004' union all 
select 'A06','03180','05','42353', 'AXVI','10/1999' union all 
select 'A06','03180','05','41653', 'AXVI','10/1999' union all 
select 'A06','04094','02','04992','ISCH','11/2007' union all 
select 'A06','04102','03','41653', 'AXVI','05/2000' union all 
select 'A07','42795','02','42755', 'AMAR','05/2010' union all 
select 'A07','50117','06','50225', 'AXVI','08/2002' union all 
select 'A07','50117','06','50127', 'AXVI','08/2002' union all 
select 'A07','50117','06','50121', 'AXVI','08/2002' union all 
select 'A07','50117','06','50117','ISCH','08/2002' union all 
select 'A07','50119','01','50117','ISCH','02/1999'

select FO, count(claim) as MMPending, count(distinct claim) as RecPending
from @t
group by fo

And call it LastMM

Then delete a few of the rows from LastMM and call that table currentMM

How can I compare the two tables to see all of the totals of the cleared records?

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-08-23 : 13:39:32

Okay trying to rewrite the question...

I have a file that runs once a week and puts the info into a table called CurrentWeek. How do I write a store procedure to get the counts that were cleared when I compare Table LastWeek to CurrentWeek?

This query gives me counts that are pending:

select FO, count(claim) as MMPending, count(distinct claim) as RecPending
from CurrentWeek
group by fo

Now I'm I want to get the cleared by comparing the two tables CurrentWeek and LastWeek.

I hope this makes sense.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 13:51:35

for that i think you need to compare with actual data rather than grouping. whats the primary key of your claim table?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-08-23 : 13:53:49

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-08-23 : 13:55:44

Sorry its PendID. That's the key in both tables LastWeek and CurrentWeek

Is that what you mean?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 13:59:38

yup. then just do like


SELECT l.*
FROM LastWeek l
LEFT JOIN CurrentWeek c
ON c.PendID = l.PendID
WHERE c.PendID IS NULL

this will give details of claims present in last year but not in current year (cleared claims). you may take count of this then.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-08-23 : 14:15:15

Ugg...that didn't work.

The PendID can be different in each table because CurrentWeek might not have the a record that's in Lastweek because it's cleared. I tried FO which is in both tables but that didn't work either.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 14:19:20

ok..thats true. but you want only those which were in last week but not in current week right?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-08-23 : 14:36:50

Got this to work:

SELECT A.FO, A.MMPending, A.RecPending, B.MMPending, B.RecPending
FROM

(select FO, count(claim) as MMPending, count(distinct claim) as RecPending
from CurrentWeek
group by fo) A

INNER JOIN
(select FO, count(claim) as MMPending, count(distinct claim) as RecPending
from LastWeek
group by fo) B

ON A.FO = B.FO

Now how do I get the counts from subtracting MMPending from MMPending and RecPending and RecPending?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 14:38:51

[code]SELECT A.FO, A.MMPending, A.RecPending, B.MMPending, B.RecPending,(B.MMPending+B.RecPending) - (A.MMPending+ A.RecPending)
FROM

(select FO, count(claim) as MMPending, count(distinct claim) as RecPending
from CurrentWeek
group by fo) A

INNER JOIN
(select FO, count(claim) as MMPending, count(distinct claim) as RecPending
from LastWeek
group by fo) B
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-08-23 : 14:56:32

Yippie almost there

How do I get rid of the negative numbers?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 15:01:21

for negative numbers what do you want to return?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

JJ297
Aged Yak Warrior

940 Posts

Posted - 2010-08-23 : 15:11:13

[code]

FO MMPending RecPending MMPending RecPending Clear
346 52 38 51 38 -1

[/code]

The clear should be just 1 instead of negative one as it's subtracting 52 from 51

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-23 : 15:16:17

then take as

ABS((B.MMPending+B.RecPending) - (A.MMPending+ A.RecPending))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources