Values of All Dates In Between

When you select a "from month" and "to month", the values of all the months in between are available to you as the programmer. These are useful in Pivot tables in Access / VB or the Cross-Tab function in SQL Server

Access / Visual Basic

Sql = Sql + "Select ORDER_DETAIL.PROD_ID, PROD_MASTER.PROD_DESCR " ' Always leave a space bar at the end Sql = Sql + "From ORDER_DETAIL "
Sql = Sql + "Left Outer Join PROD_MASTER on "
Sql = Sql + "ORDER_DETAIL.PROD_ID=PROD_MASTER.PROD_ID "
Sql = Sql + "Where INVOICE_DATE between #" ' Don't forget the # here
Sql = Sql + Format(MonthEnd1.FromDateAsDate, "mm/dd/yy") + "# and #"
Sql = Sql + Format(MonthEnd1.ToDateAsDate, "mm/dd/yy") + "# "
Sql = Sql + "Group by ORDER_DETAIL.PROD_ID,PROD_MASTER.PROD_DESCR "
Sql = Sql + "PIVOT format(INVOICE_DATE,""mmm/yy"") "   

' SQL=SQL+'in ("
'For i = MonthEnd1.FromDateAsColumn To MonthEnd1.ToDateAsColumn
'Sql = Sql + """" + Format(MonthEnd1.MonthBegin(i), "mmm/yy") + ""","
'Next i
'Sql = Left(Sql, Len(Sql) - 1) + ")"

' THE LAST PART of ( in ...) IS NOT USUALLY NECESSARY, BUT PROVIDED
' IN CASE YOU NEED IT FOR OTHER PURPOSES.
' IT MORE OR LESS ACTS AS ANOTHER "WHERE" STATEMENT
' FOR ACCESS PIVOT TABLES

Access Result:

TRANSFORM sum(QTY) Select ORDER_DETAIL.PROD_ID, PROD_MASTER.PROD_DESCR From ORDER_DETAIL Left Outer Join PROD_MASTER on ORDER_DETAIL.PROD_ID=PROD_MASTER.PROD_ID Where INVOICE_DATE between #10/01/00# and #03/31/01# Group by ORDER_DETAIL.PROD_ID,PROD_MASTER.PROD_DESCR PIVOT format(INVOICE_DATE,"mmm/yy")

SQL Server

' THIS IS HOW YOU DO A PIVOT-TABLE, OR CROSS TAB in SQL SERVER

Sql = "Select BILL_ID,ORDERS.PROD_ID, " ' Always leave a space bar at the end
For i = MonthEnd1.FromDateAsColumn To MonthEnd1.ToDateAsColumn
Sql = Sql + "SUM(CASE WHEN DATE_INV BETWEEN """
Sql = Sql + Format(MonthEnd1.MonthBegin(i), "mm/dd/yy") + """ and """
Sql = Sql + Format(MonthEnd1.MonthEnd(i), "mm/dd/yy") + """ "
Sql = Sql + "Then QTY_SHP ELSE 0 END ) "
Sql = Sql + "As "
Sql = Sql + Format(MonthEnd1.MonthBegin(i), "mmmyy") + ", "
Next i
Sql = Left(Sql, Len(Sql) - 2) + " " ' We added a space bar here, so strip it off and the comma
Sql = Sql + "From ORDERS "
Sql = Sql + "Left Outer Join PROD_MASTER on "
Sql = Sql + "ORDERS.PROD_ID=PROD_MASTER.PROD_ID "
Sql = Sql + "Where INVOICE_DATE between """
Sql = Sql + Format(MonthEnd1.FromDateAsDate, "mm/dd/yy") + """ and """
Sql = Sql + Format(MonthEnd1.ToDateAsDate, "mm/dd/yy") + """ "
Sql = Sql + "Group by BILL_ID,ORDERS.PROD_ID "

NOTE: In SQL Server, you DO want the last 'WHERE' statement to limit your search to the least number of records

SQL Server Result:

Select BILL_ID,ORDERS.PROD_ID, SUM(CASE WHEN DATE_INV BETWEEN "10/01/00" and "10/31/00" Then QTY_SHP ELSE 0 END ) As Oct00, SUM(CASE WHEN DATE_INV BETWEEN "11/01/00" and "11/30/00" Then QTY_SHP ELSE 0 END ) As Nov00, SUM(CASE WHEN DATE_INV BETWEEN "12/01/00" and "12/31/00" Then QTY_SHP ELSE 0 END ) As Dec00, SUM(CASE WHEN DATE_INV BETWEEN "01/01/01" and "01/31/01" Then QTY_SHP ELSE 0 END ) As Jan01, SUM(CASE WHEN DATE_INV BETWEEN "02/01/01" and "02/28/01" Then QTY_SHP ELSE 0 END ) As Feb01, SUM(CASE WHEN DATE_INV BETWEEN "03/01/01" and "03/31/01" Then QTY_SHP ELSE 0 END ) As Mar01 From ORDERS Left Outer Join PROD_MASTER on ORDERS.PROD_ID=PROD_MASTER.PROD_ID Where INVOICE_DATE between "10/01/00" and "03/31/01" Group by BILL_ID,ORDERS.PROD_ID