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


  
Click to buy this book from Amazon

Section Menu 

News

Pindaro is now working on a comprehensive book on SQL Server 2014.  For any comments you can email the author at alphapress@hotmail.com.

  

 

 

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: