November 14, 2006 at 1:44 pm
Hi i want to sort CalcAction field in the table ? how can i do that ?
Total record count
Record Count of Unique Claims
Record Count by Status
------------------------------------------------------------------------------------
CREATE procedure GE_Claim_Record_Counts
@Month_of_file_filter datetime
AS
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ClaimCounts]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
CREATE TABLE [dbo].[ClaimCounts] (
[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]
end
INSERT INTO ClaimCounts (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 (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
INSERT INTO ClaimCounts (Month_of_file, CalcAction, TableValue, CalculatedValue)
SELECT @Month_of_file_filter as Month_of_file, 'Record Count by Status' as CalcAction, Status as TableValue, COUNT(*) as CalculatedValue
FROM GE_Claim
WHERE Month_of_file = @Month_of_file_filter
GROUP BY Status
GO
November 14, 2006 at 2:01 pm
No idea of what you want to do. Can you post the sample result you need from the selects in the inserts?
November 14, 2006 at 2:18 pm
wht i do after i execute this proc i get a table which i link to access and generate a report in excel.The format OF REPORT is as below. IWANT TO SORT CalcAction field IN THE SORT ORDER BELOW LIKE THIS
Total record count
Record Count of Unique Claims
Record Count by Status
EXCEL REPORT WHICH I GET
| CalcAction | TableValue | 12/1/2004 | 1/1/2005 | 2/1/2005 |
| Total Record Count | 2096300023 | 569121328 | 604306210 | |
| Record Count by Status | 2304 | |||
| Record Count of Unique Claims | 1132160 | 287680 | 287680 |
November 14, 2006 at 2:32 pm
I am not the expert on this one so I'll let others handle your request. You can also search this site for pivot queries.
November 14, 2006 at 2:33 pm
because you are not really sorting the results, but placing the results in a specific order, you have to do something like this:
SELECT * FROM ClaimCounts
ORDER BY CASE
WHEN CalcAction='Total Record Count' THEN 1
WHEN CalcAction='Total Record Count by Status ' THEN 2
WHEN CalcAction=Record Count of Unique Claims' THEN 3
Else 4 END
Lowell
November 14, 2006 at 2:36 pm
i need to sort the order in my store procedure for this field
November 14, 2006 at 2:41 pm
You can't sort the data in the table because tables have no order (this is the base idea of databases)... you can only sort the data in the selects and you can only do that by using an order by!
November 14, 2006 at 2:44 pm
the procedure you posted, procedure GE_Claim_Record_Counts, does not do any selects, therefor the data cannot be sorted.
do you mean when you do SELECT * FROM ClaimCounts, with NO ORDER BY statement, you want it in that order? if that is true, then you should know it cannot be done.
a relational database does not store the data in a specific order like an excel spreadhseet does. there is NO first row, second row, etc.
if you need the data in a specific order, you need to use the ORDER BY Clause.
data is kept in a specific order because of the primary key; the primary key sorts the data in order so that data can be searched via an index quickly.
In your case, if you changed the primary key to be the CalcAction column, it would sort it by the text description of the column, and not in the special order you wanted.
You should always use an ORDER BY Clause to get your data. it's good practice and resolves issues like this for you
Lowell
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply