How to generate a specific query in SQL Server 2012

Question

I need to generate a report that contains the number of sells for a specific product. (Clothing)

I have 4 tables:

• Orders contains the ID and the date
• Product_Model contains the model description and gender
• Product_Type contains the Product Model ID, price, size, color
• Product_Order contains a reference of "Orders" and a reference of "Product_Type"

What I need is generate a report showing, for example, the number of Male Blue T-Shirts in all months.

In this order: Product Model / Gender / Color

For examples:

Also check my simple representation in SQLFiddle

Thanks very much for this support! =)

Answer 1

Try something like this:

SELECT MODEL, 
       GENDER, 
       COLOR, 
       SUM(CASE 
             WHEN MONTH(ORDER_DATE) = 1 THEN 1 
             ELSE 0 
           END) Jan, 
       SUM(CASE 
             WHEN MONTH(ORDER_DATE) = 2 THEN 1 
             ELSE 0 
           END) Feb, 
       SUM(CASE 
             WHEN MONTH(ORDER_DATE) = 3 THEN 1 
             ELSE 0 
           END) Mar, 
       SUM(CASE 
             WHEN MONTH(ORDER_DATE) = 4 THEN 1 
             ELSE 0 
           END) Apr 
FROM   PRODUCT_MODELS t1 
       INNER JOIN PRODUCT_TYPES t2 
               ON t1.PRODUCT_MODEL_ID = t2.PRODUCT_MODEL_ID 
       INNER JOIN PRODUCT_ORDERS T3 
               ON t2.PRODUCT_TYPE_ID = t3.PRODUCT_TYPE_ID 
       INNER JOIN ORDERS T4 
               ON T3.ORDER_ID = T4.ORDER_ID 
GROUP  BY MODEL, 
          GENDER, 
          COLOR 

I used your SQL fiddle to cook up my own example.