Solved

SQL Server Looping Help

Posted on 2013-01-31
17
149 Views
Last Modified: 2013-02-06
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!
0
Comment
Question by:kmc10314
  • 8
  • 5
  • 4
17 Comments
 
LVL 6

Expert Comment

by:esolve
Comment Utility
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

by:kmc10314
Comment Utility
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

by:appari
Comment Utility
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
 
LVL 6

Expert Comment

by:esolve
Comment Utility
I cant do it better than appari
0
 

Author Comment

by:kmc10314
Comment Utility
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

by:esolve
Comment Utility
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

by:kmc10314
Comment Utility
@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

by:esolve
Comment Utility
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 

Author Comment

by:kmc10314
Comment Utility
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
 
LVL 6

Expert Comment

by:esolve
Comment Utility
Looks correct yes. It will obviously depend on your requirement. You might want to change the proc to suit your needs.
0
 

Author Comment

by:kmc10314
Comment Utility
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

by:appari
Comment Utility
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

by:kmc10314
Comment Utility
OK. Appari.
I'll look forward seeing your posting.
Thanks
0
 
LVL 39

Accepted Solution

by:
appari earned 500 total points
Comment Utility
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
 

Author Comment

by:kmc10314
Comment Utility
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

by:appari
Comment Utility
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

by:kmc10314
Comment Utility
Appari, nevermind, it works fine.
I had some syntax error.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now