# Age Bucket in sql

### Question

• Hi all,

I need to solve it by using procedure.

I have follwoing table:-

Item NO|Date|Lot_No|Location|Quantity|Barcode No|State

I need to create age bucket with 0-10 days,10-20 days and 20-30 days based on dynamic date.

suppose i Pass @date='2010/2/1'

Then i need to calculate 0-10 days(21jan-31 jan[Last 10 days])
,10-20(11jan-20 jan) days and 20-30(1jan-10jan) days

then my output is like:-

Item NO|Lot_No|Location|Quantity|Barcode No|State|0-10 days|10-20 days|20-30 days

• Edited by Monday, January 13, 2014 5:52 AM
Monday, January 13, 2014 5:51 AM

• Hi,

```declare @date date
set @date = '2014/1/1'

select *, datediff(dd, @date, date),
case when datediff(dd, @date, date) between 0 and 10 then 'X' else NULL end as [0-10 days],
case when datediff(dd, @date, date) between 11 and 20 then 'X' else NULL end as [11-20 days],
case when datediff(dd, @date, date) between 21 and 30 then 'X' else NULL end as [21-30 days]
from items
```

• Marked as answer by Monday, January 13, 2014 8:18 AM
Monday, January 13, 2014 6:56 AM

### All replies

• Could you give sample data and expected output too? You would need to use pivot

Satheesh
My Blog

Monday, January 13, 2014 6:09 AM
• Please post sample data + desired result. Always state what version you are using...

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Monday, January 13, 2014 6:21 AM

• suppose the data:-

Item NO|      Date      |Lot_No |Location|Quantity|Barcode No|         State

1            2010/1/1       1     Delhi     10          B1             New Delhi
1            2010/1/5       1     Delhi     05          B1             New Delhi
2            2010/1/5       3     Punjab    10          B5             Chandi
1            2010/1/11      1     Delhi     10          B1             New Delhi
1            2010/1/17      1     Delhi     10          B1             New Delhi
2            2010/1/18      3     Punjab    10          B5             Chandi
1            2010/1/21      1     Delhi     10          B1             New Delhi
1            2010/1/30      1     Delhi     15          B1             New Delhi

Expected Output:-
Note:-Grouping should be done On Item NO,Lot_No,Location and Barcode No

Item NO|Lot_No|Location|Barcode No|State      |0-10 days|10-20 days|20-30 days
1       1      Delhi     B1         New Delhi    15       20         25
2       3      Punjab    B5         Chandi       10       10         0

• Edited by Monday, January 13, 2014 6:28 AM
Monday, January 13, 2014 6:28 AM
• sql server 2008

suppose the data:-

Item NO|      Date      |Lot_No |Location|Quantity|Barcode No|         State

1            2010/1/1       1     Delhi     10          B1             New Delhi
1            2010/1/5       1     Delhi     05          B1             New Delhi
2            2010/1/5       3     Punjab    10          B5             Chandi
1            2010/1/11      1     Delhi     10          B1             New Delhi
1            2010/1/17      1     Delhi     10          B1             New Delhi
2            2010/1/18      3     Punjab    10          B5             Chandi
1            2010/1/21      1     Delhi     10          B1             New Delhi
1            2010/1/30      1     Delhi     15          B1             New Delhi

Expected Output:-
Note:-Grouping should be done On Item NO,Lot_No,Location and Barcode No

Item NO|Lot_No|Location|Barcode No|State         |0-10 days|10-20 days|20-30 days
1                  1      Delhi         B1            New Delhi    15                    20         25
2                 3        Punjab    B5               Chandi        10                  10         0

• Edited by Monday, January 13, 2014 6:32 AM
Monday, January 13, 2014 6:29 AM
• Try below

```declare @date date ='2010/2/1'
select [Item NO],[Lot_No],[Location],[Quantity],[Barcode No],[State],
sum(case when Date between @date and dateadd(day,10,@date) then Quantity else 0 end) as [0-10 days],
sum(case when Date between dateadd(day,11,@date) and dateadd(day,20,@date) then Quantity else 0 end) as [11-20 days],
sum(case when Date between dateadd(day,21,@date) and dateadd(day,30,@date) then Quantity else 0 end) as [21-30 days]
from table1...
Group by [Item NO],[Lot_No],[Location],[Barcode No],[State]```

Thanks

Saravna Kumar C

Monday, January 13, 2014 6:33 AM
• You can start from the below code, (dynamic range)

```--CREATE TABLE #TEMP ([Item NO] INT, Date DATETIME,Lot_No INT, Location VARCHAR(10),Quantity INT,[Barcode No] INT,[State] INT)
--
--INSERT INTO #TEMP VALUES(6, GETDATE()-10,1,'ABC1',6,16,1)
--INSERT INTO #TEMP VALUES(1, GETDATE()-2,1,'ABC1',1,11,1)
--INSERT INTO #TEMP VALUES(2, GETDATE()-25,1,'ABC2',2,12,1)
--INSERT INTO #TEMP VALUES(3, GETDATE()-6,1,'ABC3',3,13,1)
--INSERT INTO #TEMP VALUES(4, GETDATE()-7,1,'ABC4',4,14,1)
--INSERT INTO #TEMP VALUES(5, GETDATE()-13,1,'ABC5',5,15,1)
--DELETE #TEMP
DECLARE @TEMP TABLE(ID INT IDENTITY,[LOWER LIMIT] INT, [UPPER LIMIT] INT)
DECLARE @date DATETIME,@MAX INT,@RANGE INT
SET @date='01/13/2014'
SET @RANGE=0
WHILE @RANGE <= (SELECT MAX(AGING) FROM (SELECT *,DATEDIFF(D,Date,@date) AGING FROM #TEMP) X)
BEGIN
IF @RANGE=0 INSERT INTO @TEMP	SELECT CAST(@RANGE AS VARCHAR) , CAST(@RANGE + 10 AS VARCHAR)
ELSE INSERT INTO @TEMP	SELECT CAST(@RANGE + 1 AS VARCHAR) , CAST(@RANGE + 10 AS VARCHAR)
SET @RANGE = @RANGE + 10
END
SELECT *,DATEDIFF(D,Date,@date) AGING,
(SELECT ID FROM @TEMP WHERE DATEDIFF(D,Date,@date)>=[LOWER LIMIT] AND DATEDIFF(D,Date,@date)<=[UPPER LIMIT]) [RANGE ID],
(SELECT  CAST([LOWER LIMIT] AS VARCHAR) + '-' + CAST([UPPER LIMIT] AS VARCHAR) FROM @TEMP WHERE [ID]
IN (SELECT ID FROM @TEMP WHERE DATEDIFF(D,Date,@date)>=[LOWER LIMIT] AND DATEDIFF(D,Date,@date)<=[UPPER LIMIT])) [RANGE]
FROM #TEMP```

Regards, RSingh

Monday, January 13, 2014 6:33 AM
• Hi,

```declare @date date
set @date = '2014/1/1'

select *, datediff(dd, @date, date),
case when datediff(dd, @date, date) between 0 and 10 then 'X' else NULL end as [0-10 days],
case when datediff(dd, @date, date) between 11 and 20 then 'X' else NULL end as [11-20 days],
case when datediff(dd, @date, date) between 21 and 30 then 'X' else NULL end as [21-30 days]
from items
```

• Marked as answer by Monday, January 13, 2014 8:18 AM
Monday, January 13, 2014 6:56 AM