[Cross Post] - Filter dd/mm/yyyy to show most popular day of week

Hi Guys and Gals,

I designed a website for a client of mine a while back that deals with
applications for Insurance policies.

When someone applies and pays for a policy the Application Date is stored by
sending <%=Now()%> to a date field in an access database formatted to
DD/MM/YYYY.

Now... this website has been up and running for the past 18 months or so and
my client now wishes to be able to see which days of the week are most
popular and has asked me to devise a dynamic bar chart that will display the
most popular days of the week...

E.g., "The number of policies that have been purchased on a Monday is:" and
so on...

If this had been part of the original brief then I would have simply been
submitting the day of the week (name) to the database and then filter it out
accordingly... my problem now of course is that I've got 18 months of
DD/MM/YYYY's

Is there any way of filtering the database using ASP/VBscript that will have
it automatically work out and allocate records to its applicable day of the
week?

Any help would be greatly appreciated.

Kind regards

David

Group by day of the week:
SELECT DATEPART("dw",BoughtOn), COUNT(PolicyID)
FROM Table
WHERE BoughtOn BETWEEN startdate AND enddate
GROUP BY DATEPART("dw",BoughtOn)
ORDER BY DATEPART("dw",BoughtOn)

You'll have 1 for Sunday, 2 for Monday, etc.
For SQL Server, that's a server setting. You can tell it to call whatever
day of the week you want 1. I don't know if Access changes those results
for regional settings or not.


"David Burns-Keane" <daveb (AT) dtbinter (DOT) net> wrote

Hi Guys and Gals,

I designed a website for a client of mine a while back that deals with
applications for Insurance policies.

When someone applies and pays for a policy the Application Date is stored
by sending <%=Now()%> to a date field in an access database formatted to
DD/MM/YYYY.

Now... this website has been up and running for the past 18 months or so
and my client now wishes to be able to see which days of the week are most
popular and has asked me to devise a dynamic bar chart that will display
the most popular days of the week...

E.g., "The number of policies that have been purchased on a Monday is:"
and so on...

If this had been part of the original brief then I would have simply been
submitting the day of the week (name) to the database and then filter it
out accordingly... my problem now of course is that I've got 18 months of
DD/MM/YYYY's

Is there any way of filtering the database using ASP/VBscript that will
have it automatically work out and allocate records to its applicable day
of the week?

Any help would be greatly appreciated.

Kind regards

David

Gazing into my crystal ball I observed "David Burns-Keane"
<daveb (AT) dtbinter (DOT) net> writing in news:d5t1bd$jvt$1 (AT) forums (DOT) macromedia.com:

Hi Guys and Gals,

I designed a website for a client of mine a while back that deals with
applications for Insurance policies.

When someone applies and pays for a policy the Application Date is
stored by sending <%=Now()%> to a date field in an access database
formatted to DD/MM/YYYY.

Now... this website has been up and running for the past 18 months or
so and my client now wishes to be able to see which days of the week
are most popular and has asked me to devise a dynamic bar chart that
will display the most popular days of the week...

E.g., "The number of policies that have been purchased on a Monday is:"
and so on...

If this had been part of the original brief then I would have simply
been submitting the day of the week (name) to the database and then
filter it out accordingly... my problem now of course is that I've got
18 months of DD/MM/YYYY's

Is there any way of filtering the database using ASP/VBscript that will
have it automatically work out and allocate records to its applicable
day of the week?

Any help would be greatly appreciated.

Kind regards

David

You need the datepart function, eg:

SELECT datepart('w',field) AS weekday, COUNT(id) AS count
FROM table
GROUP BY datepart('w',field)
ORDER BY datepart('w',field) DESC

Then:

total = rs.RecordCount
while not rs.EOF

select case trim(rs("weekday"))
case "1"
weekday = "Sunday"
case "2"
weekday = "Monday" ...
end select

count = trim(rs("count"))
width = count*100/total
if (i mod 2) = 0 then
color = "blue"
else
color = "red"
end if
%>

<div style="width:<%=width%>%; height:1.25em; border:1px solid black;
background-color: <%=color%>; color:white;margin-bottom:1px; float:left">
</div>&nbsp;<%=weekday%>&nbsp;<%=count%><br style="clear:both">
<% rs.Movenext
i = i + 1
wend
rs.Close
set rs = nothing
%>

This will give you a nice horizontal bar chart without the use of images.
Play around with the colors to suit.

HTH
--
Adrienne Boswell
http://www.cavalcade-of-coding.info
Please respond to the group so others can share

Hi Adrienne,

I tried your code - which was very useful thanks, but I seem to be
getting an error in translating the weekday numbers to weekday names.

Using your code I get the following error message:

Microsoft VBScript runtime error '800a01f5'
Illegal assignment: 'weekday'

/test.asp, line 45

Where line 45 is the following:

43. select case trim(rs_count("weekday"))
44. case "1"
45. weekday = "Sunday"

I've tried a few things but don't seem to be able to fix this.

Would you happen to have any suggestions?

Kind regards

David


Adrienne wrote:

Gazing into my crystal ball I observed "David Burns-Keane"
daveb (AT) dtbinter (DOT) net> writing in
news:d5t1bd$jvt$1 (AT) forums (DOT) macromedia.com:

Hi Guys and Gals,

I designed a website for a client of mine a while back that deals
with
applications for Insurance policies.

When someone applies and pays for a policy the Application Date is
stored by sending <%=Now()%> to a date field in an access database
formatted to DD/MM/YYYY.

Now... this website has been up and running for the past 18 months
or
so and my client now wishes to be able to see which days of the
week
are most popular and has asked me to devise a dynamic bar chart
that
will display the most popular days of the week...

E.g., "The number of policies that have been purchased on a Monday
is:"
and so on...

If this had been part of the original brief then I would have
simply
been submitting the day of the week (name) to the database and then
filter it out accordingly... my problem now of course is that I've
got
18 months of DD/MM/YYYY's

Is there any way of filtering the database using ASP/VBscript that
will
have it automatically work out and allocate records to its
applicable
day of the week?

Any help would be greatly appreciated.

Kind regards

David




You need the datepart function, eg:

SELECT datepart('w',field) AS weekday, COUNT(id) AS count
FROM table
GROUP BY datepart('w',field)
ORDER BY datepart('w',field) DESC

Then:

total = rs.RecordCount
while not rs.EOF

select case trim(rs("weekday"))
case "1"
weekday = "Sunday"
case "2"
weekday = "Monday" ...
end select

count = trim(rs("count"))
width = count*100/total
if (i mod 2) = 0 then
color = "blue"
else
color = "red"
end if
%

div style="width:<%=width%>%; height:1.25em; border:1px solid black;

background-color: <%=color%>; color:white;margin-bottom:1px;
float:left"
/div>&nbsp;<%=weekday%>&nbsp;<%=count%><br style="clear:both"
% rs.Movenext
i = i + 1
wend
rs.Close
set rs = nothing
%

This will give you a nice horizontal bar chart without the use of
images.
Play around with the colors to suit.

HTH
--
Adrienne Boswell
http://www.cavalcade-of-coding.info
Please respond to the group so others can share

Gazing into my crystal ball I observed "burnskeane" <d.burns (AT) dtbinter (DOT) net>
writing in news:1115888847.779127.80860 (AT) o13g2000cwo (DOT) googlegroups.com:

Adrienne wrote:
Gazing into my crystal ball I observed "David Burns-Keane"
daveb (AT) dtbinter (DOT) net> writing in
news:d5t1bd$jvt$1 (AT) forums (DOT) macromedia.com:

Hi Guys and Gals,

I designed a website for a client of mine a while back that deals
with applications for Insurance policies.

When someone applies and pays for a policy the Application Date is
stored by sending <%=Now()%> to a date field in an access database
formatted to DD/MM/YYYY.

Now... this website has been up and running for the past 18 months
or so and my client now wishes to be able to see which days of the
week are most popular and has asked me to devise a dynamic bar chart
that will display the most popular days of the week...

E.g., "The number of policies that have been purchased on a Monday
is:" and so on...

If this had been part of the original brief then I would have simply
been submitting the day of the week (name) to the database and then
filter it out accordingly... my problem now of course is that I've
got 18 months of DD/MM/YYYY's

Is there any way of filtering the database using ASP/VBscript that
will have it automatically work out and allocate records to its
applicable day of the week?

Any help would be greatly appreciated.

Kind regards

David




You need the datepart function, eg:

SELECT datepart('w',field) AS weekday, COUNT(id) AS count
FROM table
GROUP BY datepart('w',field)
ORDER BY datepart('w',field) DESC

Then:

total = rs.RecordCount
while not rs.EOF

select case trim(rs("weekday"))
case "1"
weekday = "Sunday"
case "2"
weekday = "Monday" ...
end select

count = trim(rs("count"))
width = count*100/total
if (i mod 2) = 0 then
color = "blue" else
color = "red"
end if
%

div style="width:<%=width%>%; height:1.25em; border:1px solid black;

background-color: <%=color%>; color:white;margin-bottom:1px;
float:left"> </div>&nbsp;<%=weekday%>&nbsp;<%=count%><br
style="clear:both"> <% rs.Movenext
i = i + 1
wend
rs.Close
set rs = nothing %

This will give you a nice horizontal bar chart without the use of
images. Play around with the colors to suit.

HTH
Hi Adrienne,

I tried your code - which was very useful thanks, but I seem to be
getting an error in translating the weekday numbers to weekday names.

Using your code I get the following error message:

Microsoft VBScript runtime error '800a01f5'
Illegal assignment: 'weekday'

/test.asp, line 45

Where line 45 is the following:

43. select case trim(rs_count("weekday"))
44. case "1"
45. weekday = "Sunday"

I've tried a few things but don't seem to be able to fix this.

Would you happen to have any suggestions?

Kind regards

David

What is the value of trim(rs_count("weekday"))?

--
Adrienne Boswell
http://www.cavalcade-of-coding.info
Please respond to the group so others can share