asked on

Weeknumber and year in reports

Dear experts,

I have an asp-script generating the present weeknumber for a formular.
Into the database I write 2012-34 which is the year and week we are in now.

My script is this:

<%
Dim wk, intStart, intEnd

wk = DatePart("ww", date())-1
intStart = (wk * 7) +2
intEnd = intStart + 6
%>
<input type="hidden" name="weeknumber" id="weeknumber" value="<%= Year(Now) & "-" & DatePart("ww", Now()) %>" />

Open in new window

But I also have an archieve with reports from the last half year.
I thought the easiest way to make this would be to select them in the database, but my problem is that the weeknumber would be zero if I say:

DatePart("ww", Now()) - 36

Open in new window

So if the week number is above the present weeknumber, my output would be 2011-0, and not 2011-50. Is there a way to make sure I can track the reports from the last 26 weeks correctly?

If there is a better way to write the script, it is ok!

Thanks in advance!
DanishCoder

Scott Fell

See if this helps with your problem. If you use the other options for date part you don't have to do the subtraction. In the code below I am making sure the first day of the week is a sunday ",1" and starting on the first week with 4 days ",2". This way there will always be weeks 1 - 52. If you choose to start the week 1 where there is a full week, then you will end up with some years using week53.

Where I have "NewDate" you can simply use your past date.

startdate= #12/20/2011#
for x = 1 to 800
	NewDate=dateadd("d",x,startdate)
	response.write NewDate&" = "& DatePart("ww", NewDate,1,2)&"<br>"
next

Open in new window

DanishCoder

ASKER

Padas,

These are reports sent in every week from a person with a login - there is no need for all days and which week they belong to. Therefore, 201x-xx is the ideal solution, but maybe I can't make an archive that way?

DanishCoder

Scott Fell

The other dates were just for show. I guess I do not understand what you are doing or what you mean by an archive?

If the data in your database has a valid date, you can simply use replace the "newdate" with a field from your db.

DatePart("ww", rs("date"),1,2)

It sounds like I am missing something though.

Into the database I write 2012-34 which is the year and week

Are you storing the actual date or just the year-week?

But I also have an archieve with reports from the last half year.
I thought the easiest way to make this would be to select them in the database, but my problem is that the weeknumber would be zero if I say:

This is where I am lost with your question. What is the archive? Is this a table in your database? Does it have the regular date? or just the date-week?

If you are using date week, I do think it would be a good idea to use the method where you look for the week with the first 4 days and that way you can always compare week 1 with week 1 although week 1 or 52 may not always be a full week. Or if you prefer to use full weeks, then some years you will have 53 weeks.

DanishCoder

ASKER

Padas,

I have a formular that will be sent by email to the main leader and stored in the database for later review. Now I need an archive to make sure that they have something to review.

Today, I store it as 2012-34 (year-week), but sure, I can store it by date, I just don't do that, because dates in ASP is not always easy to work with.

The formular stored in the database could be sent in on 2011-23, 2011-45, 2012-15, 2012-28...you name it. But I need it to go back 6 months from today's week, so if it is sent in at 2011-45, I should be able to see 7 reports from 2011 and 19 reports from 2012 (or all reports from week 45 to week 2012-19, as their could be some reports not filled out (the system automatically find today's week and dates)):

End Week    Today's Week (could be)
2011-31	     2012-5
2011-32	     2012-6
2011-33	     2012-7
2011-34	     2012-8
2011-35	     2012-9
2011-36	     2012-10
2011-37	     2012-11
2011-38	     2012-12
2011-39	     2012-13
2011-40	     2012-14
2011-41	     2012-15
2011-42	     2012-16
2011-43	     2012-17
2011-44	     2012-18
2011-45	     2012-19
2011-46	     2012-20
2011-47	     2012-21
2011-48	     2012-22
2011-49	     2012-23
2011-50	     2012-24
2011-51	     2012-25
2011-52	     2012-26
2012-01	     2012-27
2012-02	     2012-28
2012-03	     2012-29
2012-04	     2012-30
2012-05	     2012-31
2012-06	     2012-32
2012-07	     2012-33
2012-08	     2012-34

Open in new window

I hope this makes more sense.
There are 26 weeks among "Today's week" and "End week".

/DC

ASKER CERTIFIED SOLUTION

Scott Fell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DanishCoder

ASKER

Sorry for the delay (and confusion) I made up.
Anyway, that was exactly what I was looking for.

So now I have made it a little more dynamic:

<%
ReportWeek1	= Year(Now) & "-" & DatePart("ww", Now())

'function 1
'function 2
%>

Open in new window

so my SQL looks like this:

<%
"SELECT [DateSent], [ReportWeek], [ReportWeekDates] FROM reports WHERE UserID = " & session("id") & " AND ReportWeek BETWEEN " & reportDate("" & ReportWeek1 & "",-26) & " AND " & ReportWeek1 & " ORDER BY ReportID DESC"
%>

Open in new window

Which will give:

SELECT [DateSent], [ReportWeek], [ReportWeekDates] FROM reports WHERE UserID = 71 AND ReportWeek BETWEEN 2012-11 AND 2012-36 ORDER BY ReportID DESC

Open in new window

Thanks again, and have a great weekend...

/DC