lost on forming this sql

[havey]

Hi, I'm totally stumped as to what to do, you see each record has a day of the week and a day, like
column heading--> Week_Day..... Day
value---------------> Tuesday ....... 13

How to pull all the records information, via sql statement, by order of Week_Day (sunday, monday, tuesday, wednesday, thursday, friday, saturday, and sunday , monday.. again...) and also pull the records by sorting order of Day, so the week of the 4th preceeds the week of 11th) There may be circumstances when there is no (for example: tuesday the 5th or/and wednesday the 6th.

In the database the record use the field id as an autonumber, and I know that a monday record preceeds a tuesday record and a tuesday preceeds a wednesday... and so on back to sunday again like so,

id.......Week_Day.......Day
25.......Sunday..............4
26 ....... Monday...........5
27 ....... Tuesday...........6
28 ....... Wednesday........7
29 ....... Thursday..........8
30 ....... Friday..............9
31 ....... Saturday..........10
32 ....... Sunday............11
33.......Monday..............12
34....and so on

this is what i though may be the solution to my problem, but it seems to have a syntax error so i can't test it, and i'm wondering if an error may occure if there is no Tuesday with the AND statement in the sql?

SELECT * FROM Results WHERE UserName = '" & Session("UserName") & "' AND Week_Day='Sunday' AND Week_Day='Monday' AND Week_Day='Tuesday' AND Week_Day='Wednesday' AND Week_Day='Thursday' AND Week_Day='Friday' AND Week_Day='Saturday' BY id ASC

[raf]

your statement
SELECT * FROM Results WHERE UserName = '" & Session("UserName") & "' AND Week_Day='Sunday' AND Week_Day='Monday' AND Week_Day='Tuesday' AND Week_Day='Wednesday' AND Week_Day='Thursday' AND Week_Day='Friday' AND Week_Day='Saturday' BY id ASC
can't work. Since the condition (where clause) is impossible + you forgot 'ORDER' + 'Day' is a reserverd word.
The were clause is impossible since a variable can only have one value for that variable.

I don't think it is possible to do what you want, with one sql statement. You'll need one statement for each week. (first find the first day of the week, use a between clause on that day and day+7 and order the records by day.

But you're lucky ! Just save the dates. Select the records and order them by date. ASP has a function to display the day --> weekday(date)

[havey]

i'm sorry, you lost me.. do you mean by that? something like this:

week 1:
SELECT * FROM Results WHERE UserName = '" & Session("UserName") & "' AND Day
BETWEEN 18 AND +7 days

week2:
SELECT * FROM Results WHERE UserName = '" & Session("UserName") & "' AND Day
BETWEEN +7more days AND ++7 more days

to pull out the records by day number then what?

I'm trying to create a time sheet starting from sunday, each employee fills in the timesheet daily, but emaployees do have some days off and the days are never the same, teh time sheet works in intervals of weeks, and i need to pull out a week at a time for each UserName session ID.

Please explain to pedy me.

the first day of the week will be the sunday the 18th.

or did you mean something like:

SELECT * FROM Results WHERE UserName = '" & Session("UserName") & "' BY Date ASC

?????

[raf]

Ok. I meant; store the datevalues in the db. Like 05/15/2003 . ASP (well, VBscript) has a build in function to return the day of the week -->
Weekday(#05/15/2003#) will return 5
WeekdayName(5) will return thursday.
FormatDateTime(#05/05/2003#, 1) will return 15 may 2003 or something

so

sql = "select * FROM Results WHERE UserName = '" & Session("UserName") & "' AND Dayvalue
BETWEEN #start# AND #end#"
sql=replace(sql,"start", "18/05/2003")
sql=replace(sql,"start", "25/05/2003")

will give you all the records for that user in that period. Once you have them, you can use the functions to display the day names etc.
NOTE : day is a reserved word and can not be used as a variablename!

Of coarse, it would be better to have it completely dynamical, like
(here it comes)

dim currentday, startdate, enddate, jump
currentday = weekday(Now) 'will return 5 today
if currentday = 1 then 'if today is sunday
startdate = Date 'startdate sselection is today
enddate = DateAdd("d",7,Date) 'enddate selection is next week
else
jump = 8 - currentday 'returns te number of day to add before it's sunday.
startdate = DateAdd("d", jump, Date) 'startdate sselection is today
enddate = DateAdd("d",jump + 7,Date) 'enddate selection is next week
end if
sql = "select * FROM Results WHERE UserName = '" & Session("UserName") & "' AND Dayvalue
BETWEEN #start# AND #end#"
sql=replace(sql,"start", startdate)
sql=replace(sql,"start", enddate)

[havey]

WOW !!... I'll have to try it....hmm, i guess i need to change the db field to Dayvalue from Day, that messes up a few pages, will have to go back and fix things them, then try it. Thank you Raf! so lets say the values are pulled from the database with you magic script, would i run thru the record set to print each one like so ?


<%While Not rs.EOF%>
<%=rs("field1")%>
<%=rs("field2")%>
<%=rs("Dayvalue")%>
<%=rs("field4")%>

<%
rs.MoveNext
Wend
%>

[raf]

Well. depends on what you're after. I presume the user need to put some info in. In textfields or by selecting values from a dropdownlist or whatever. So you'll have to dynamically build a form, in such a way that the formfields can be identified when you process the form.
For instance, say you have a primary key (autonum variable). If field1 is the occupation ('analysing', 'welding', 'drawing'), then you could have a dropdown formfield with name="10field1" for the record with primary key value 10. The values from the dropdown could be pulled from a db (if they are the foreign key in this table).
If you then post the form, you can use a 'for each formelement in form" loop, to go through the recordset collection and dynamically build the sql updatestatements. With Left(formelement, Len(formelement - 6)) you get the id of the record that needs to be updated, and right(formelement, 6) gets the name of the field that needs to be updated, request.form("formelement") gets you the new value.
You see? By looping through the formcollection, you can dynamically build the updatestatements

rs("Dayvalue") will give you 15/05/2003. If you need the dayname, you need to use something like
WeekdayName(Weekday(rsRecordsetname.Fields("Dayvalue") ))

--> i always use rsRecordsetname.Fields("variablename") form (in case you have more then one recordset open)

I might look complicated, but dynamically building these forms and dynamically and fully parametrised formprocessing is something you realy need to master. I'd see, give it a go and if you run into problems, just let us know.