SQL Server Looping Help

Hello, Experts
I'm trying to create the chart below

 Available Date         Sales Started         Interval
    01-01-13         |           N/A           |          1
    01-04-13         |       01-01-13       |          1
    01-15-13         |       01-01-13       |          1
    01-27-13         |           N/A           |          2
    02-04-13         |       01-27-13       |          2
    02-05-13         |       01-27-13       |          2
    02-07-13         |       01-27-13       |          2
    02-10-13         |       01-27-13       |          2
    02-17-13         |           N/A           |          3
    03-01-13         |       02-17-13       |          3
    04-04-13         |           N/A           |          4
    04-15-13         |           N/A           |          5
    05-10-13         |           N/A           |          6
    05-20-13         |       05-10-13       |          6
 
Right now all I have is the first column.
The from the very initial point the dates will be effective 15 days.
If the 15 days passes, when the new dates starts, the grouping start again.

For example, if a promotion started on 01-01-2013, it will be effective until 01-16-2013.
Any dates recorded between then will fall under 01-01-2013 promotion
When the new sales promotion starts after that, that day becomes a starting point, and 15 days clock starts again.
In this case, 01-27-13 and the clock will be effective until 02-11-13.

The dates between promotions are random; only rule will be a promotion cannot start until the 15 days clock is over for current promotion.

Please help me write this promotion loop.

Thank you in advance!
kmc10314Asked:
Who is Participating?
 
appariConnect With a Mentor Commented:
try this
declare @tab table(itemNo varchar(3), Available_Date datetime,Sales_Started datetime,Interval int )

insert into @tab(itemNo,Available_Date)
Select '123', '01-01-2013'
union Select '123','01-04-2013'         
union Select '123','01-15-2013'        
union Select '123','01-27-2013'         
union Select '123','02-04-2013'         
union Select '123','02-05-2013'         
union Select '123','02-07-2013'         
union Select '123','02-10-2013'         
union Select '123','02-17-2013'         
union Select '123','03-01-2013'         
union Select '123','04-04-2013'         
union Select '123','04-20-2013'         
union Select '123','05-10-2013'         
union Select '123','05-20-2013'

union Select '124','01-04-2013'         
union Select '124','01-12-2013'        
union Select '124','01-27-2013'         
union Select '124','02-03-2013'         
union Select '124','02-06-2013'         
union Select '124','02-09-2013'         
union Select '124','02-14-2013'         
union Select '125','02-21-2013'         
union Select '125','03-01-2013'         
union Select '125','04-04-2013'         
union Select '125','04-20-2013'         
union Select '125','05-10-2013'         
union Select '125','05-20-2013'

update top(1) A1
set Sales_Started=Available_Date, interval=1
from @tab A1
where Available_Date = (Select min(Available_Date) from @tab)


while exists(Select 1 from @tab where Sales_Started is null)
begin
	Update top(1) A1
		Set Sales_Started = isnull((Select min(A2.Sales_Started) from @tab A2
			Where A1.itemno= A2.itemNo and A2.Sales_Started is not null 
			and abs(datediff(dd,A2.Sales_Started, A1.Available_Date))<=15), A1.Available_Date)
	from @tab A1 where A1.Sales_Started is null
end

--select * from @tab

;with A as (Select itemno, Sales_Started, row_number() over(partition by itemno order by Sales_Started) rowID
from (Select distinct itemno, Sales_Started from @tab) A1)
update A1
set interval = rowID
from @tab A1 join A on A.itemno = A1.itemNo and 
A.Sales_Started = A1.Sales_Started

Select itemno, Available_Date, 
case when Available_Date = Sales_Started then 'N/A' else convert(varchar,Sales_Started ) end, interval
 from @tab

Open in new window

0
 
esolveCommented:
Your logic is not clear. When does the 15 days interval start? Will this be in sequence of 15 days (ongoing without breaks in between)? eg. How do you determine when the next 15 days start?

When is N/A? on the 1st day of the 15 day syclus?

You can use the same loop as my suggestion here but I need more info on your logic.

http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL_Server_2008/Q_28010710.html

DECLARE @TmpTable TABLE (ID INT IDENTITY(1,1), Name VARCHAR(50))
INSERT INTO @TmpTable (Name) values ('James')
INSERT INTO @TmpTable (Name) values ('Pete')
INSERT INTO @TmpTable (Name) values ('Sarah')
INSERT INTO @TmpTable (Name) values ('Carina')
INSERT INTO @TmpTable (Name) values ('Shane')

--select * from @TmpTable

DECLARE @IteratorID INT
SET @IteratorID = 0

WHILE @IteratorID IS NOT NULL
BEGIN
      --SELECT ROW TO WORK WITH
      SET @IteratorID = (SELECT TOP 1 ID FROM @TmpTable)
     
      IF(@IteratorID IS NOT NULL)
      BEGIN
      ------------
            --DO WHAT YOU WANT WITH THIS ROW
            DECLARE @Name NVARCHAR(50)
            SET @Name = (SELECT Name FROM @TmpTable WHERE ID = @IteratorID)
            SELECT @Name
      ------------
      END
     
      --IMPORTANT: DELETE ROW TO ENSURE YOU'RE DONE WITH IT AND PREVENT STACK OVERFLOW
      DELETE FROM @TmpTable WHERE ID = @IteratorID
END
0
 
kmc10314Author Commented:
Sorry for not making it clear.

When does the 15 days interval start?
- There is only one initial point and everything else will be depending on previous output
For example, if it started on 1-01-2013, then the first interval will start end on 01-16-2013.

When the next record shows up after end of the first interval (it can be random; any date after the first interval's ending date) that's when new interval starts  


In the example, above because 2nd interval started on 1-27-13 because it was out of 15 days clock of the 1st interval.

The 3rd interval will start at the record, if the record is greater than 2-12-13 because 2nd interval started on 1-27-13 and ended on 2-11-13.

I hope it makes sense..
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
appariCommented:
try this code
declare @tab table(Available_Date datetime,Sales_Started datetime,Interval int )

insert into @tab(Available_Date)
Select '01-01-2013'
union Select '01-04-2013'         
union Select '01-15-2013'        
union Select '01-27-2013'         
union Select '02-04-2013'         
union Select '02-05-2013'         
union Select '02-07-2013'         
union Select '02-10-2013'         
union Select '02-17-2013'         
union Select '03-01-2013'         
union Select '04-04-2013'         
union Select '04-20-2013'         
union Select '05-10-2013'         
union Select '05-20-2013'

update A1
set Sales_Started=Available_Date, interval=1
from @tab A1
where Available_Date = (Select min(Available_Date) from @tab)


while exists(Select 1 from @tab where Sales_Started is null)
begin
	Update top(1) A1
		Set Sales_Started = isnull((Select min(A2.Sales_Started) from @tab A2
			Where A2.Sales_Started is not null and abs(datediff(dd,A2.Sales_Started, A1.Available_Date))<=15), A1.Available_Date)
	from @tab A1 where A1.Sales_Started is null
end


;with A as (Select Sales_Started, row_number() over(order by Sales_Started) rowID
from (Select distinct Sales_Started from @tab) A1)
update A1
set interval = rowID
from @tab A1 join A on A.Sales_Started = A1.Sales_Started

Select Available_Date, case when Available_Date = Sales_Started then 'N/A' else convert(varchar,Sales_Started ) end, interval
 from @tab

Open in new window


in your sample data, i think there is a mistake. 04-04 and 04-15 are within 15 days block so they belong to same interval, isn't it?
04-04-13         |           N/A           |          4
 04-15-13         |           N/A           |          5
0
 
esolveCommented:
I cant do it better than appari
0
 
kmc10314Author Commented:
Appari, it is really good.
One more question though.
In my database, I have many sets like the set above.

The set above was only for item1.
If I want that to be repeated across all the item, how do I do it?
0
 
esolveCommented:
The easiest would be to put the above in a stored procedure which can be called by passing in a parameter like this.

create procedure p_select_Functionality
(
  @DateTimeString NVARCHAR(MAX) --values in this format '01-04-2013,01-15-2013'
)
AS

DECLARE @TmpDateTime TABLE (Avalable_Date nvarchar(50))
INSERT INTO  @TmpDateTime (Avalable_Date )
SELECT * FROM dbo.fn_strlist_to_table(@DateTimeString )

..proc content (remember to convert the datetime string to actual datetime)

GO

You can use the following type function to convert from string to table:


CREATE FUNCTION [dbo].[fn_strlist_to_table] (@list text)
      RETURNS @tbl TABLE (value nvarchar(50) NOT NULL) AS
   BEGIN
      DECLARE @pos      int,
              @textpos  int,
              @chunklen smallint,
              @str      varchar(50),
              @tmpstr   varchar(8000),
              @leftover varchar(50)

      SET @textpos = 0
      SET @leftover = ''
      WHILE @textpos <= datalength(@list) / 2
      BEGIN
         SET @chunklen = 4000 - datalength(@leftover) / 2
         SET @tmpstr = ltrim(@leftover + substring(@list, @textpos, @chunklen))
         SET @textpos = @textpos + @chunklen

         SET @pos = charindex(',', @tmpstr)
         WHILE @pos > 0
         BEGIN
            SET @str = substring(@tmpstr, 1, @pos - 1)
            INSERT @tbl (value) VALUES(@str)
            SET @tmpstr = ltrim(substring(@tmpstr, @pos + 1, len(@tmpstr)))
            SET @pos = charindex(',', @tmpstr)
         END

         SET @leftover = @tmpstr
      END

      IF ltrim(rtrim(@leftover)) <> ''
         INSERT @tbl (value) VALUES(@leftover)

      RETURN
   END
0
 
kmc10314Author Commented:
@esolve
I'm somewhat confused,
So your query is used so that I can do the query above (written by appari) in groups?
0
 
esolveCommented:
Your question:
In my database, I have many sets like the set above.
The set above was only for item1.
If I want that to be repeated across all the item, how do I do it?

You can put @appari's code in a stored procedure and send through a list of datetimes like I have explained above. This allow for reusing the same logic for a set of datetimes, if this is your requirement.

Probably a better option for your scenario is to have one proc for each "set" you are talking about.

Regards
0
 
kmc10314Author Commented:
So... is this what you are talking about?

ALTER PROCEDURE [dbo].[sp_Sales_Started_Loop] 
(
	@DateTimeString VARCHAR(MAX)	
) 

AS

SELECT [Item#], [Available_Date]
INTO #Sales
FROM [vw_MasterSales]
WHERE SalesMonth BETWEEN '2012-01' AND '2012-06' 
--DROP TABLE #Sales

SET @DateTimeString = (SELECT Available_Date FROM #Sales)

DECLARE @TmpDateTime TABLE (Sales_Started VARCHAR(50))
INSERT INTO @TmpDateTime (Sales_Started)
SELECT * FROM dbo.fn_ListToTable (@DateTimeString)

DECLARE @Sales TABLE (Available_Date VARCHAR(10), Sales_Started VARCHAR(15), Interval INT)
INSERT INTO @Sales (Available_Date)
SELECT Available_Date
FROM #Sales

UPDATE A
SET Sales_Started = Available_Date, Interval = 1
FROM @Sales A
WHERE Available_Date = (SELECT MIN(Available_Date) FROM @Sales)

WHILE EXISTS (SELECT 1 FROM @Sales WHERE Available_Date IS NULL)
BEGIN
	UPDATE TOP(1) A
		SET Available_Date = ISNULL((SELECT MIN(B.Sales_Started) FROM @Sales B
			WHERE B.Available_Date IS NOT NULL AND ABS(DATEDIFF(DD, B.Sales_Started, A.Available_Date)) <= 15), A.Available_Date)
	FROM @Sales A WHERE A.Sales_Started IS NULL
END

;WITH B AS (SELECT Sales_Started, ROW_NUMBER() OVER(ORDER BY Sales_Started) Row_ID
FROM (SELECT DISTINCT Sales_Started FROM @Sales) A)
UPDATE A
SET Interval = Row_ID
FROM @Sales A
JOIN B on A.Sales_Started = B.Sales_Started

Open in new window

0
 
esolveCommented:
Looks correct yes. It will obviously depend on your requirement. You might want to change the proc to suit your needs.
0
 
kmc10314Author Commented:
Please bear with me, but I'm still somewhat confused.
I'm getting the date from the table and where I do feed that into?
In other word, where does @DateTimeString VARCHAR(MAX) come from?
0
 
appariCommented:
No need of any procedure, just include itemno in the joins and it should work fine. Right now i am outside unable to post modified code. Will post as soon as possible.
0
 
kmc10314Author Commented:
OK. Appari.
I'll look forward seeing your posting.
Thanks
0
 
kmc10314Author Commented:
Appari, I tried your query and the logic seems to be great.
However, at certain point of query, it seems like its breaking, giving an error like this
Msg 208, Level 16, State 1, Line 20
Invalid object name 'A1'.

Can you think of the reason why the break is happening?
0
 
appariCommented:
The sql block i posted is working fine. are you executing the same sql i posted or did you change something to use your table names? if possible post the sql you are executing.
0
 
kmc10314Author Commented:
Appari, nevermind, it works fine.
I had some syntax error.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.