How can I compute my Age_Group field? It's based on a calculated field, CURR_BAMTIME

[Jackie Bird]

USE [REP]
GO
/****** Object:  StoredProcedure [dbo].[rpt_AR]    
Script Date: 12/16/2010 11:35:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[rpt_AR]
AS
BEGIN
SET NOCOUNT ON;
Select
Case
When DB_Source = 'IMVPB' and DATEDIFF(dd, dAssignedDate, DATEADD(dd, 0, Getdate())) 
-(DATEDIFF(wk, dAssignedDate, DATEADD(dd, 0, Getdate())) * 2) 
End CURR_BAMTIME,
Case
    When DateDiff(dd,CURR_BAMTIME,GetDate()) < 4 Then '1-3'
    When DateDiff(dd,CURR_BAMTIME,GetDate()) between 4 and 6 Then '4-6'
End Age_Group,
 
From REP.dbo.IMSRPT 
Where Dateofrequest >= '1/1/2010 12:00:00 AM'
END
 

[ck9663]

Can you post some sample data and your required results?

~~ CK

[Jackie Bird]

Sure, thanks for asking.

Say CURR_BAMTME is 1, then Age_Group needs to be "1-3", or if CURR_BAMTIME is 2, then Age_Group needs to be "1-3". All the Whens are not listed to catch all possible values of CURR_BAMTIME, but the 1-3 Age_Group is good enough for the example here.

Thanks, again!

[ck9663]

How many combination are there? Depending on the combination, you might need a look-up table for your requirement rather that a long CASE statement.

Could you post it like a table? And give some data with different CURR_BAMTIME.

Good Luck!!!

~~ CK

[Jackie Bird]

The Age_Group ranges are 1-3, 4-6, 7-13 and 14+. CURR_BAMTIME can be a value from 1-500.