Adding parameter to a sum/group by clause

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

Adding parameter to a sum/group by clause

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2009-09-30 : 08:10:45

I have this store procedure that is sumed up and group up with a specific date in the clause [hst_back] >= '03/10/2009'), but in the Visual Studio developer they want to be able to pick a date range.

How would I go about doing that if I only have this field i my where clause and I want to make sure that when they pick a date range it is not earlier than 03/10/2009? I am a little confused.

if exists (
select *
from dbo.sysobjects
where id = object_id(N'[dbo].[Sum_Chaining_Claims_TAT_SUMMARY]')
and OBJECTPROPERTY(id, N'IsTable') = 1)
drop table [dbo].[Sum_Chaining_Claims_TAT_SUMMARY]

Create Table [Sum_Chaining_Claims_TAT_SUMMARY](
[Type] [varchar](50) NULL,
[Total Claims Sent] int NOT NULL,
[TAT Details With Weekends][varchar](50) NULL,
[OrderVal] [varchar](50) NULL,
[Number Received] int NULL,
[TotalCharges] [decimal](14,6) NULL,
[TotalSavings] [decimal](14,6) NULL,
[Negotiated Allowed Charges] [decimal](14,6) NULL,
[% of Claims] [decimal](14,2)
)

INSERT INTO Sum_Chaining_Claims_TAT_SUMMARY([Type],[OrderVal],[Total Claims Sent],[TAT Details With Weekends],[TotalCharges],[TotalSavings],[Negotiated Allowed Charges])
------------------------------------------------------------------------------------
---COALITION CLAIMS
------------------------------------------------------------------------------------

SELECT Distinct 'Coalition' AS Type,1 AS OrderVal,
Count(distinct [hst_id1]) as [Total Claims Sent],
[TAT Details With Weekends],
SUM(CAST([clm_tchg] AS MONEY)) AS TotalCharges,
SUM(CAST([clm_sppo] AS MONEY)) AS TotalSavings,
SUM(CAST([CLM_NEGO]AS MONEY)) AS [Negotiated Allowed Charges]
FROM ChainingHistory
WHERE
([hst_back] >= '03/10/2009') and
hst_podes like '%COALI%'
Group by [TAT Details With Weekends]

UPDATE Sum_Chaining_Claims_TAT_SUMMARY
SET [Number Received] =
case when [TAT Details With Weekends] = 'A: Equal to 1 Day' then
(select count(Distinct [hst_id1]) from ChainingHistory
where RIGHT(RTRIM(hst_podes), 7) = '=COALI=' and
/*[hst_att5] = '!P' and */([hst_back] >= '03/10/2009') AND [TAT Details With Weekends] = 'A: Equal to 1 Day')

when [TAT Details With Weekends] = 'B: Equal to 2 Day' then
(select count(Distinct [hst_id1]) from ChainingHistory
where RIGHT(RTRIM(hst_podes), 7) = '=COALI=' and
/*[hst_att5] = '!P' and */([hst_back] >= '03/10/2009') AND [TAT Details With Weekends] = 'B: Equal to 2 Day')

when [TAT Details With Weekends] = 'C: Equal to 3 Day' then
(select count(Distinct [hst_id1]) from ChainingHistory
where RIGHT(RTRIM(hst_podes), 7) = '=COALI=' and
/*[hst_att5] = '!P' and */([hst_back] >= '03/10/2009') AND [TAT Details With Weekends] = 'C: Equal to 3 Day')

when [TAT Details With Weekends] = 'D: Equal to 4 Day' then
(select count(Distinct [hst_id1]) from ChainingHistory
where RIGHT(RTRIM(hst_podes), 7) = '=COALI=' and
/*[hst_att5] = '!P' and */([hst_back] >= '03/10/2009') AND [TAT Details With Weekends] = 'D: Equal to 4 Day')

when [TAT Details With Weekends] = 'E: Equal to 5 Day' then
(select count(Distinct [hst_id1]) from ChainingHistory
where RIGHT(RTRIM(hst_podes), 7) = '=COALI=' and
/*[hst_att5] = '!P' and */([hst_back] >= '03/10/2009') AND [TAT Details With Weekends] = 'E: Equal to 5 Day')

when [TAT Details With Weekends] = 'F: Equal to 6 Day' then
(select count(Distinct [hst_id1]) from ChainingHistory
where RIGHT(RTRIM(hst_podes), 7) = '=COALI=' and
/*[hst_att5] = '!P' and */([hst_back] >= '03/10/2009') AND [TAT Details With Weekends] = 'F: Equal to 6 Day')

when [TAT Details With Weekends] = 'G: Equal to 7 Day' then
(select count(Distinct [hst_id1]) from ChainingHistory
where RIGHT(RTRIM(hst_podes), 7) = '=COALI=' and
/*[hst_att5] = '!P' and */([hst_back] >= '03/10/2009') AND [TAT Details With Weekends] = 'G: Equal to 7 Day')

when [TAT Details With Weekends] = 'H: Greater than 7 Days' then
(select count(Distinct [hst_id1]) from ChainingHistory
where RIGHT(RTRIM(hst_podes), 7) = '=COALI=' and
/*[hst_att5] = '!P' and */([hst_back] >= '03/10/2009') AND [TAT Details With Weekends] = 'H: Greater than 7 Days')
end
where Type = 'Coalition'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-30 : 08:12:12

Something like this

[hst_back] >= '03/10/2009' and [hst_back] between @date1 and @date2

Madhivanan

Failing to plan is Planning to fail

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2009-09-30 : 08:37:24

Thanks for you help! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources