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-3

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

Please post sample data + desired result. Always state what version you are using...

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

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        

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

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		

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