VBScript and SQL Calendars
by John Donovan Lambert

Example 1: 

Month   DayOfMonth  WkDay   UnitsSold
11         27         6       869
11         28         7       344
11         29         1       238
11         30         2       618
12          1         3       589

Example 2: 

(a)
<% set rs1=conn.Execute("SELECT DATEPART(mm, RecDate) AS Month, "&_
    "DATEPART(dd, RecDate) AS DayOfMonth, DATEPART(dw, "&_
    "RecDate) AS WkDay, COUNT(OrderID) AS UnitsSold FROM UnitsSold "&_
    "WHERE DATEPART(yyyy, RecDate) = " & wYear & " GROUP BY "&_
    "DATEPART(mm, RecDate), " & _
    "DATEPART(dd, RecDate), DATEPART(dw, RecDate) "&_
    "ORDER BY DATEPART(mm, RecDate), DATEPART(dd, RecDate)") %>

(b) 
<% szSQL = "SELECT DATEPART(mm, RecDate) AS Month, "&_
    "DATEPART(dd, RecDate) AS DayOfMonth, DATEPART(dw, "&_
    "RecDate) AS WkDay, COUNT(OrderID) AS UnitsSold FROM UnitsSold "&_
    "WHERE DATEPART(yyyy, RecDate) = " & wYear & " GROUP BY "&_
    "DATEPART(mm, RecDate), " & _
    "DATEPART(dd, RecDate), DATEPART(dw, RecDate) "&_
    "ORDER BY DATEPART(mm, RecDate), DATEPART(dd, RecDate)" %>
<% =szSQL %><P>
<% set rs1=conn.Execute(szSQL) %>


Example 3: 

DayOfMonth  WkDay   DaysTopCustomer
27           6        General Electric
28           7        General Dynamics
29           1        General Motors
30           2        General Mills
1            3        General Schwarzkopf

Example 4:

<%IF wMonth = 2 THEN
IF ldom = 29 AND DayOfWeekCounter = 
       [?day] AND DayCounter > 22 THEN%>Last ?Day In Feb.<%END IF
IF ldom = 28 AND DayOfWeekCounter = 
       [?day] AND DayCounter > 21 THEN%>Last ?Day In Feb.<%END IF
END IF %>


Listing One

(a)
Parameter     Result Type    Digits Returned
yyyy           year           1753-9999
mm             month          1-12
dd             day of month   1-31
dw             day of week    1-7 (Sun.-Sat.)

(b)
Parameter   Result Type     Digits Returned
YYYY          Year             1-9999
MM            month            01-12
DD            day of month     01-31
D             day of week      01-07

(c)
YEAR(date) for year as 1-9999
MONTH(date) for month as 1-12
DAY(date) for day of month as 1-31
DAYOFWEEK(date) for day of week as 1-7

Listing Two
CREATE PROCEDURE sp_UnitsSold @Year int AS
SELECT DATEPART(mm, RecDate) AS Month, DATEPART(dd, RecDate) AS DayOfMonth,
       DATEPART(dw, RecDate) AS WkDay, COUNT(OrderID) AS UnitsSold
FROM UnitsSold 
WHERE DATEPART(yyyy, RecDate) = @Year
GROUP BY DATEPART(mm, RecDate), DATEPART(dd, RecDate), DATEPART(dw, RecDate) 
ORDER BY DATEPART(mm, RecDate), DATEPART(dd, RecDate)

Listing Three 
<%@ LANGUAGE=VBScript %>
<%wYear=Request.QueryString("wYear")
IF wYear = "" THEN wYear=Request.Form("wYear")
IF wYear = "" OR wYear < 1753 OR wYear > 9999 THEN wYear = 
                                                DATEPART("yyyy",now()) %>
<html><HEAD><TITLE>Calendar Report of Units Sold</TITLE></head>
<BODY><CENTER>
<form action="calendarunitsales.asp" method="post">
    Enter a year for report:<br>
    <input size=6 maxlength=4 name=wYear><br>
    <input type="submit" name="Change" VALUE="Change">
</form><BR>
<H2><%=wYear%> Unit Sales</H2><P>
<%
dim D(31) 
Set conn=server.createobject("ADODB.connection")
conn.Open "DATABASE=[database name];DSN=[DSN Name];
                                   UID=[login];Password=[password];"
%>
<TABLE ALIGN=center WIDTH=30% BORDER=1 CELLSPACING=1 CELLPADDING=2>
    <TR>
        <TD ALIGN=middle></TD>
        <TD ALIGN=middle><STRONG>Sun.</STRONG></TD>
        <TD ALIGN=middle><STRONG>Mon.</STRONG></TD>
        <TD ALIGN=middle><STRONG>Tue.</STRONG></TD>
        <TD ALIGN=middle><STRONG>Wed.</STRONG></TD>
        <TD ALIGN=middle><STRONG>Thu.</STRONG></TD>
        <TD ALIGN=middle><STRONG>Fri.</STRONG></TD>
        <TD ALIGN=middle><STRONG>Sat.</STRONG></TD>
    </TR>
<%
SET rs1=conn.Execute("EXECUTE sp_UnitsSold " & wYear)
DO UNTIL rs1.EOF 'CONDITIONAL MONTH LOOP
mc = rs1("Month")%>
    <TR>
        <TD ALIGN=middle><STRONG><%=MonthName(lc)%></STRONG></TD>
        <% 'Determine day of the week the month begins on
        tempdate = mc & "/1/" & wYear 
        bwdom = datepart("w", tempdate)
        DayOfWeekCounter = 0 'This "week" has 8 "days" to 
                             '       include the Name of the Month column.
        'PRINT LEADING BLANK DAYS
        SELECT CASE bwdom
            CASE 1 
                DayOfWeekCounter = 1
            CASE 2 %>
                <TD></TD><%DayOfWeekCounter = 2
            CASE 3 %>
                <TD></TD><TD></TD><%DayOfWeekCounter = 3
            CASE 4 %>
                <TD></TD><TD></TD><TD></TD><%DayOfWeekCounter = 4
            CASE 5 %>
                <TD></TD><TD></TD><TD></TD><TD></TD><%DayOfWeekCounter = 5
            CASE 6 %>
                <TD></TD><TD></TD><TD></TD><TD></TD>
                                            <TD></TD><%DayOfWeekCounter = 6
            CASE 7 %>
                <TD></TD><TD></TD><TD></TD><TD></TD><TD></TD>
                                            <TD></TD><%DayOfWeekCounter = 7
            CASE ELSE %>
                <TD>Beginning Day of Week Error</TD><%
        END SELECT
        'Determine last day of month & number of weeks
        ldom = Day(DateAdd("d", -1, mc + 1 & "/1/" & tempyear))
        NumWeeks = 5
        IF (bwdom = 6 AND ldom = 31) OR 
                               (bwdom = 7 AND ldom > 29) THEN NumWeeks = 6
        'INITIALIZE DAY ARRAY
        lc =  0
        FOR lc = 1 to 31
            D(lc) = "&nbsp;"
        NEXT 
        'loop through records for the month & assign to D array.
        DO WHILE rs1("Month") = mc 'DAY ASSIGNMENT LOOP
            TempD = rs1("DayOfMonth")
            D(TempD) = rs1("UnitsSold")
            IF NOT rs1.eof THEN rs1.movenext
            IF rs1.eof then exit do 
        LOOP
        DayCounter = 1
        WeekCounter = 1
        DO WHILE WeekCounter < NumWeeks + 1
            DO WHILE DayOfWeekCounter < 8   
                IF DayCounter < ldom + 1 THEN %>
                    <TD ALIGN=middle><SUP><FONT size=-2><%=DayCounter%>
                    </FONT></SUP>&nbsp;<FONT color="ff0000">
                    <STRONG><%=D(DayCounter)%></STRONG></FONT></TD>
                <%ELSE%>
                    <TD ALIGN=middle></TD>
                <%END IF
                DayOfWeekCounter = DayOfWeekCounter + 1 
                DayCounter = DayCounter + 1 
            LOOP
            DayOfWeekCounter = 1
            WeekCounter = WeekCounter + 1 %>
            </TR><TR><TD></TD>
       <%LOOP
LOOP
rs1.Close
conn.Close%>
</TABLE><P>
</CENTER></BODY></HTML>


Listing Four 
CREATE PROCEDURE sp_SalesRecords @Year int, @Month int AS
SELECT DATEPART(dd, RecDate) AS DayOfMonth, DATEPART(dw, RecDate) AS WkDay,
                                                             DaysTopCustomer 
FROM SalesRecords 
WHERE DATEPART(yyyy, RecDate) = @Year
    AND DATEPART(mm, RecDate) = @Month
GROUP BY DATEPART(dd, RecDate), DATEPART(dw, RecDate) 
ORDER BY DATEPART(dd, RecDate)


Listing Five
<%  IF wMonth = 3 OR wMonth = 4 THEN 'Begin Easter calculation
        eMonth = 0: eDay = 0: v1 = 0: v2 = 0: v3 = 0: v4 = 0: 
                                                v5 = 0: v6 = 0: v7 = 0 
        IF wYear > 1699 AND wYear < 1800 THEN v6 = 23: v7=3

       IF wYear > 1799 AND wYear < 1900 THEN v6 = 23: v7=4
        IF wYear > 1899 AND wYear < 2100 THEN v6 = 24: v7=5
        IF wYear > 2099 AND wYear < 2200 THEN v6 = 24: v7=6
        v1 = wYear MOD 19: v2 = wYear MOD 4: v3 = wYear MOD 7
        v4 = ((19*v1)+v6) MOD 30
        v5 = ((2*v2)+(4*v3)+(6*v4)+v7) MOD 7
        eDay = (22+v4+v5): eMonth = 3
        IF eDay > 31 THEN
            eDay = (v4+v5-9): eMonth = 4
            IF eDay > 24 THEN
                IF eDay = 26 THEN eDay = 19
                IF eDay = 25 AND v4 = 28 AND v1 > 10 THEN eDay = 18
            END IF
        END IF
    END IF 'End Easter calculation %>

1


