November 13, 2006 at 8:25 am
Hi ,Can somebody help me on this
------------------------------------------------------------------------------------
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, 'Record Count by Loss Month' as CalcAction,
Convert(Char(10),LossDate,111) as TableValue, COUNT(*) as CalculatedValue
FROM GE_Claim
WHERE Month_of_file = @Month_of_file_filter
GROUP BY LossDate
INSERT INTO ClaimCounts (Month_of_file, CalcAction, TableValue, CalculatedValue)
SELECT @Month_of_file_filter as Month_of_file, 'Record Count by Transaction Begin Month' as CalcAction,
Convert(Char(10),From_Date,111) as TableValue, COUNT(*) as CalculatedValue
FROM GE_Claim
WHERE Month_of_file = @Month_of_file_filter
GROUP BY From_Date
GO
November 13, 2006 at 8:48 am
Asim
Have you tried
GROUP BY YEAR(LossDate)
and
GROUP BY MONTH(From_Date)?
John
November 13, 2006 at 8:54 am
Hi,
if you want to GROUP BY some part of the date, the same part of date has to be displayed in the select list. You can't group by year, and then select precise date.
For example, this would be by month (month and year, as you specified)
SELECT @Month_of_file_filter as Month_of_file,
'Record Count by Loss Month' as CalcAction,
Convert(Char(7),LossDate,111) as TableValue,
COUNT(*) as CalculatedValue
FROM GE_Claim
WHERE Month_of_file = @Month_of_file_filter
GROUP BY Convert(Char(7),LossDate,111)
You can use YEAR(LossDate) or Convert(Char(4),LossDate,111) to get only the year.
November 13, 2006 at 9:11 am
You need to group by the formula for TableValue:
INSERT INTO ClaimCounts (Month_of_file, CalcAction, TableValue, CalculatedValue)
SELECT
@Month_of_file_filter AS Month_of_file
,'Record Count by Loss Month' AS CalcAction
,CONVERT(varchar(4), LossDate, 120) AS TableValue -- change here
,COUNT(*) AS CalculatedValue
FROM GE_Claim
WHERE Month_of_file = @Month_of_file_filter
GROUP BY CONVERT(varchar(4), LossDate, 120) -- change here
INSERT INTO ClaimCounts (Month_of_file, CalcAction, TableValue, CalculatedValue)
SELECT
@Month_of_file_filter AS Month_of_file
,'Record Count by Transaction Begin Month' AS CalcAction
,CONVERT(varchar(7), From_Date, 120) as TableValue -- change here
,COUNT(*) as CalculatedValue
FROM GE_Claim
WHERE Month_of_file = @Month_of_file_filter
GROUP BY CONVERT(varchar(7), From_Date, 120) -- change here
ps GROUP BY is evaluated before SELECT so you cannot use the alias.
November 13, 2006 at 9:22 am
thanks for the help
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply