AlphaPress Publishing
P.O. Box 5942 Albany, NY 12205
Tel: 518-250-3890
Email: alphapress@hotmail.com 


  

Section Menu 

Are you a University or College bookstore and want to order multiple books?  Email us at alphapress@hotmail.com or call us at 518-250-3890.  We will be happy to talk to you.  

Are you a professor and you need a sample copy?  We will be happy to send you a pdf or printed copy!  Email us at alphapress@hotmail.com.

Are you outside the United States?  We can ship multiple books worldwide in most countries.

  

 

 

How to use the datepart() or format() functions to extract the week from a date field

To contact the author click here

This article is from the book "Access 2007 Pure SQL

To download the sample database click here


 

Use the datepart() or format() functions to extract the week from a date field

Create a crosstab report showing product sales by week for a six-month period

Discussion (with datepart):

This time, management wants a report listing product sales by week within a six-month period and within a specific year.  To create this crosstab query, we need to use three date functions in combination.  First, we will use the month() function to extract the month number from the orderdate field and use this as a criterion in the WHERE clause.  Notice how the IN operator is used with the WHERE clause to get the first six months of the year.  Since, however, we have multiple years of sales in the database, we need to include a criterion in the WHERE clause to isolate the year we want.  We can use the year function on the orderdate field to achieve this task.  Finally, in the PIVOT part of the crosstab query, we can use the datepart() function with the "ww" argument to produce a column for each week.  (For a detailed overview of crosstab queries, check chapter 18).  As you can see from the result set, we can now examine detailed total product sales by week for a period of 27 weeks within the six-month period that we defined in our WHERE clause. 

Code:

TRANSFORM Sum([unitprice]*[quantity]) AS Ordertotal

SELECT ProductName

FROM Qry_Invoices

WHERE month(orderdate) IN (1,2,3,4,5,6) AND year(orderdate) = 2010

GROUP BY ProductName

PIVOT datePart("ww",[OrderDate])  

Result:


Discussion (with format):

We could use the format function to achieve the same task, but notice in the result set that the weeks are not in order this time. 

Code:

TRANSFORM Sum([unitprice]*[quantity]) AS Ordertotal

SELECT ProductName

FROM Qry_Invoices

WHERE month(orderdate) IN (1,2,3,4,5,6) AND year(orderdate) = 2010

GROUP BY ProductName

PIVOT format([OrderDate],  "ww")

 

Result: