Unique SQL question

[vilrbn]

I have a unique problem I have come across and was hoping someone could help me out. I have changed the data for security reasons but the logic still holds true.

I have a table with the following setup...

ID Category Value ProcessDate
-----------------------------------
1 1 12 1900-01-01 00:00:00.000
2 1 13 1900-01-01 00:00:00.000
3 1 14 1900-01-01 00:00:00.000
4 1 15 1900-01-01 00:00:00.000
5 1 16 1900-01-01 00:00:00.000
6 1 23 1900-01-01 00:00:00.000
7 1 24 1900-01-01 00:00:00.000
8 1 25 1900-01-01 00:00:00.000
9 1 26 1900-01-01 00:00:00.000
10 1 34 1900-01-01 00:00:00.000
11 1 35 1900-01-01 00:00:00.000

etc.... note....value increases in a pattern where the 10's value (here 2 and 3 ) start being paired with a number 1 higher than itself and continues to 6 and starts again with another order of 10. This goes until ID value goes to 15.

Now, what I am trying to do is write a looping function with SQL that would build off itself, meaning after every pass, the SQL query would change to accomodate the rule I am looking for.



a) The rule: the values within an order of 10 (the teen values, the twenty values, the thirty values) must have a process date
such that a value as 2 in [ ID 1 value 12 week1] cannot be represented in the same process date week, it can however be chosen for a following week [ ID 6 value 23 week2].

Thus no single digit in a value can have a processdate less than a week's difference.

Example.... I have manually determined the 1st two process weeks.

ID Category Value ProcessDate
-----------------------------------
1 1 12 2002-11-03 00:00:00.000
2 1 13 1900-01-01 00:00:00.000
3 1 14 1900-01-01 00:00:00.000
4 1 15 1900-01-01 00:00:00.000
5 1 16 2002-10-27 00:00:00.000
6 1 23 1900-01-01 00:00:00.000
7 1 24 2002-10-27 00:00:00.000
8 1 25 1900-01-01 00:00:00.000
9 1 26 1900-01-01 00:00:00.000
10 1 34 1900-01-01 00:00:00.000
11 1 35 2002-10-27 00:00:00.000
12 1 36 1900-01-01 00:00:00.000
13 1 45 2002-11-03 00:00:00.000
14 1 46 1900-01-01 00:00:00.000
15 1 56 2002-11-03 00:00:00.000

So far I am stuck with the following...
set rst4 = Server.CreateObject("ADODB.Recordset&quot
sql4="select * from rb1 where value like '%"&n&"%'"

For n=1 to 15 ' the max value of ID
thisstringarray2=thisstring
response.write thisstringarray2 & &quot;<BR>&quot;
thisarray2=split(thisstring,&quot;,&quot
If IsArray(thisarray2) then
'response.write UBound(thisarray2)
For p=0 to UBound(thisarray2)
If len(trim(thisarray2(p)))=0 then
'do nothing
Else
' sql4=sql4 & &quot; and value not like '%&quot;&thisarray2(p)&&quot;%' &quot;
sql4=sql4 & &quot;and value not like '%&quot;&thisarray2(p)&&quot;%'&quot;
End IF
Next
End IF
sql4=sql4&&quot; and processdate !='&quot;&theprocessdate&&quot;' &quot;
sql4=sql4&&quot; order by ID DESC&quot;

...

thisstring=thisstring&myarray(0)&&quot;,&quot;&myarray(1)&&quot;,&quot;
myarray=split(rst4(&quot;value&quot,&quot;-&quot

Next


-------------------------------------------------
Does anyone know how to accomplish this...?

japes

 

[krisbrixon]

I'm lost:

Let me clear up some assumpions first:

Is the first character in value field equal to the week?
i.e. 34 = week 3
65 = week 6

Is 1900-01-01 00:00:00.000 considered a process date?

Is 0 a vaild second value? or is it only 1-6?

Is there a maximum value for the column &quot;value&quot;
i.e. 96 or 99 or 99999999

If there are two (let's say) 34, do we care which of the two records we pull from the database? or do we just need the value 34 to be represented?


With out knowing the answers to these questions here are two things to look at:

1. Try doing subSelects to work the data into a cleaner format.
Select *
From (Select *
From rb1
Where ...)
Where value IN (Select DISTINCT value
From rb1
Where value ...)

2. See if the keyword DISTINCT can help.

Kris

 

[japes89]

Answers to questions.....
the first character is irrelevant to the week, therefore
value=34 does not denote week 3


1900 0101 000.000.000 is simply a place holder for a process date, waiting to be updated with the correct solution.

The maximum value for column &quot;value&quot; is 98 as value cannot have the same digit in it, no 55, no 66, etc.

34 will only be represented once in the database, as there should never be more than one record for any given &quot;value&quot;.

Currently trying to work the suggested sql's offered......
_______________________________________________
OutsideIntranets.com
Stop wasting time, get to work

http://www.outsideintranets.com

 

[japes89]

I am starting to wonder if this is possible at all.... _______________________________________________
OutsideIntranets.com
Stop wasting time, get to work

http://www.outsideintranets.com