Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

SQL Server Looping Help

Posted on 2013-01-31
Medium Priority
165 Views
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.

0
Question by:kmc10314
• 8
• 5
• 4

LVL 6

Expert Comment

ID: 38841348
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

Author Comment

ID: 38841404
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

LVL 39

Expert Comment

ID: 38842373
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

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

LVL 6

Expert Comment

ID: 38842708
I cant do it better than appari
0

Author Comment

ID: 38843711
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

LVL 6

Expert Comment

ID: 38844015
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

Author Comment

ID: 38844075
@esolve
I'm somewhat confused,
So your query is used so that I can do the query above (written by appari) in groups?
0

LVL 6

Expert Comment

ID: 38844111
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

Author Comment

ID: 38844250
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
0

LVL 6

Expert Comment

ID: 38844453
Looks correct yes. It will obviously depend on your requirement. You might want to change the proc to suit your needs.
0

Author Comment

ID: 38844552
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

LVL 39

Expert Comment

ID: 38845214
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

Author Comment

ID: 38852810
OK. Appari.
I'll look forward seeing your posting.
Thanks
0

LVL 39

Accepted Solution

appari earned 2000 total points
ID: 38853532
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
0

Author Comment

ID: 38855775
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

LVL 39

Expert Comment

ID: 38857700
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

Author Comment

ID: 38859668
Appari, nevermind, it works fine.
0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we waâ€¦
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.