Hello,
I have got four tables.
PURCHASE
PurchaseID Date ProductID CustomerID
1 2012-02-01 1 1
2 2012-02-02 2 2
3 2012-02-03 3 3
PRODUCT
ProductID ProductName ProductPrice ProductTypeID
1 Michelin X4t 2000 150.00 1
2 Craftsman 6000T 97.00 2
3 Duracell AAA 6 Pack 12.00 3
PRODUCT TYPE
ProductTypeID ProductTypeName
1 Automobile Tires
2 Drills
3 Batteries
4 Motor Oil
CUSTOMER
CustomerID FirstName LastName PhoneNumber EmailAddress
1 Steve Smith 6045554567 ssmith@hotmail.com
2 John McLean 7675679876 johnm@gmail.com
3 Peter Harrington 6045762934 petedog@yahoo.ca
Using the sample data as a guide, how would you write a TSQL statement that lists the number of each product type purchased by each customer for the month of February 2012. Here is what the resulting data set would look like:
CustomerID FirstName LastName NumAutoTires NumDrills NumBatteries NumMotorOil
1 Steve Smith 4 0 10 1
2 John McLean 2 2 0 18
3 Peter Harrington 1 1 2 4
4 Wendy Foster 4 0 8 0