November 15, 2006 at 9:59 am
I would miss the code for sp_Demo
Also I totally agree with vasc on this one. You are heading straight for problems with this solution (unless we misunderstand the requirements)!
November 15, 2006 at 10:06 am
i know how to avoid the duplicates which vasc told me but wht should i do for the sort order in the table ClaimCounts_Test
November 15, 2006 at 10:49 am
CAN SOMEBODY GUIDE ME ON THAT
November 15, 2006 at 11:28 am
are you online can you guide me what to do with this issue which i am facing
November 15, 2006 at 11:31 am
... was at lunnh break. Also we are not your servants. So if you need something of that order I suggest you hire yourself a programmer, or better, a DBA.
Does this give you the required results (if so we can use that as a template)?
--Demo table
DECLARE @demo TABLE (CalcAction VARCHAR(50) NOT NULL, TOTAL INT NOT NULL)
INSERT INTO @demo (CalcAction, Total) VALUES ('Total Record Count', 456)
INSERT INTO @demo (CalcAction, Total) VALUES ('Record Count of Unique Claims', 123)
INSERT INTO @demo (CalcAction, Total) VALUES ('Other total', 23445)
SELECT
CalcAction
, Total
FROM @Demo
ORDER BY CASE CalcAction
WHEN 'Record Count of Unique Claims' THEN 1
WHEN 'Total Record Count' THEN 2
ELSE 99 --shoot them at the end
END
November 15, 2006 at 11:37 am
Asim
Microsoft SQL Server is a relational database so rows in a table should have NO order. (Basic theory.) There often is some order but this cannot be guaranteed!
The only way you can guarantee order is when you use an ORDER BY clause with a SELECT statement.
November 15, 2006 at 11:37 am
I never said that you are my servant ,i just simply asked you .let me ask you a question you are a nice guy who helps every one but why do you show attitude.
Regarding hiring somebody you can say this now it is is ok becz iam new but thing do change and people do learn there is nothing impossible if people like you co-operate.
Thanks for the reply
November 15, 2006 at 11:44 am
Fine thanks , but looking at my code can you tell me how should i take care about 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
November 15, 2006 at 11:52 am
BTW you have a trailing space in count of unique claims that you have in the where that you don't have in the sort.... that sure can't help!
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
November 15, 2006 at 11:55 am
Just to clarify that. Asking for help 3 message in a row is how I felt like we had to serve you. I'm happy to hear that this is not your intent.
Also I am not trying to show attitude. It looks like we are not communicating very well at the moment!
November 15, 2006 at 12:05 pm
Asim,
>>can you tell me how should i take care about this logic below in my stored procedure
As you have failed to give a cogent description of what you are trying to do, the short answer is no.
Maybe you want something like the following, or maybe not….
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE GE_Claim_Record_CountsTest
@Month_of_file_filter datetime
AS
SET NOCOUNT ON
SELECT Month_of_file
,COUNT(*) AS TotalRecordCount
,COUNT(DISTINCT PolicyNumber) AS UniqueClaims
FROM GE_Claim
WHERE Month_of_file = @Month_of_file_filter
GO
or even:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE GE_Claim_Record_CountsTest
@Month_of_file_filter datetime
,@TotalRecordCount int OUTPUT
,@UniqueClaims int OUTPUT
AS
SET NOCOUNT ON
SELECT @TotalRecordCount = COUNT(*)
,@PolicyNumber = COUNT(DISTINCT PolicyNumber)
FROM GE_Claim
WHERE Month_of_file = @Month_of_file_filter
GO
November 15, 2006 at 12:07 pm
No offense was intendet there Asim but it seems that you are a real beginner. If your software is commercial than I would suggest you to get a pro opinion or help at least. If it is for your learning than is K.
regarding your problem: ppl answered allready ( I think ) but you refuse to listen...
You ll get the best solution if you describe WHAT you want to do ...
for example postin the GE_Claim table describing what data you store there
and what queries you want to run against using SP.
Also you might want to pay attention to "datetime" data and how you populate the GE_Claim table, and also to the @Month_of_file_filter parameter( I m not saying that your wrong here ...)
Vasc
November 15, 2006 at 3:03 pm
It's good to read manual time to time. At least when you are in trouble.
See BOL:
ORDER BY 1 means "order by 1st column in the query".
ORDER BY 2 means "order by 2nd column in the query".
Because you don't have 2nd column in the query it gives you random order. Occasionally in QA this random order matches your expectations, but it does not have to stay the same everywhere.
_____________
Code for TallyGenerator
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply