none
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 gs.7 Monday, January 13, 2014 5:52 AM
    Monday, January 13, 2014 5:51 AM

Answers

  • Hi,

    Do you think following SQL SELECT can help you?

    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
    


    SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

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

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
    Answerer

  • 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 gs.7 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 gs.7 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


    • Edited by RSingh() Monday, January 13, 2014 6:46 AM
    Monday, January 13, 2014 6:33 AM
  • Hi,

    Do you think following SQL SELECT can help you?

    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
    


    SQL Server, SQL Server 2012 Denali and T-SQL Tutorials

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