Can Somebody help on this stored procedure

Question

Post reply

Can Somebody help on this stored procedure

Best-330874

SSCrazy

Points: 2350
More actions
November 15, 2006 at 8:23 am

#115082

Hi,
   i need help where should i incorporate this logic below in my stored procedure.-------------------------------------------------------------------------------------
SELECT CalcAction FROM ClaimCounts_Test
WHERE CalcAction='Total Record Count'OR CalcAction='Record Count of Unique Claims '
ORDER BY CASE
WHEN CalcAction='Total Record Count' THEN 2
WHEN CalcAction='Record Count of Unique Claims' THEN 1
END
--------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------
stored procedure
--------------------------------------------------------------------------------------
CREATE PROCEDURE GE_Claim_Record_CountsTest
       @Month_of_file_filter datetime
AS
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ClaimCounts_Test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
CREATE TABLE [dbo].[ClaimCounts_Test] (
       [Month_of_file] [datetime] NULL ,
       [CalcAction] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
       [TableValue] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
       [CalculatedValue] [int] NULL
) ON [PRIMARY]
INSERT INTO ClaimCounts_Test (Month_of_file, CalcAction, TableValue, CalculatedValue)
       SELECT @Month_of_file_filter as Month_of_file, 'Total Record Count' as CalcAction, SPACE(1) as TableValue, COUNT(*) as CalculatedValue
       FROM GE_Claim
       WHERE Month_of_file = @Month_of_file_filter
INSERT INTO ClaimCounts_Test (Month_of_file, CalcAction, TableValue, CalculatedValue)
       SELECT @Month_of_file_filter as Month_of_file, 'Record Count of Unique Claims' as CalcAction, SPACE(1) as TableValue, COUNT(DISTINCT               PolicyNumber) as CalculatedValue
       FROM GE_Claim
       WHERE Month_of_file = @Month_of_file_filter
GO

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Please do not cross-post. I already tried to answer that question here :

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=323014

Best-330874 SSCrazy Points: 2350 More actions · Answer 2

yes you did but i am not cleared yet so thats why iam asking again

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

You can ask for as many clarifications as you want in the same tread. This will keep us from having 10 conversations going at the same time!

Best-330874 SSCrazy Points: 2350 More actions · Answer 4

Best-330874

SSCrazy

Points: 2350

November 15, 2006 at 8:37 am

#671983

ok

Dan Murray Right there with Babe Points: 779 More actions · Answer 5

I guess I'm confused by the terms 'Total Record Count'OR CalcAction='Record Count of Unique Claims ', I'll assume that these are counts of actual record conditions. Also the order by clause is confusing. You seem to selecting one field but attempting to order two. I think you're trying to return either '1' or '2' in the field depending on their relation to your two variables, 'Total Record Count'OR 'Record Count of Unique Claims'.

That being the case I'd generate these terms first and then plug them into the query, i.e.

declare @TotRecCnt as int

declare @totUniqRecCnt as int

select @TotRecCnt = (select count(*) where Condition='abc...')

select @TotUniqRecCnt = (select count(*) where Condition='xyz...')

SELECT (case when CalcAction = @TotRecCnt then 1 when CalcAction = @TotUniqRecCnt then 2 end) CalcAction

from tblWhatEver

ORDER BY CalcAction

Hope this makes sense.

Good Hunting!