Simplifying SQL SELECT statements?

Question

I have a query that pulls a rolling 36 month unit sales history. It works as-is, but I wondered if there was a way to make it less lengthy and cumbersome.

There are two parts to the query as it stands - the headers and the data.

Here is what I have for the headers:

SELECT  
DATEADD(m, -36, GETDATE()-day(GETDATE()-1)) AS  "MM36"
, DATEADD(m, -35, GETDATE()-day(GETDATE()-1)) AS  "MM35"
, DATEADD(m, -34, GETDATE()-day(GETDATE()-1)) AS  "MM34"

As of today, this is Sep - Nov 2011. It goes on to...

, DATEADD(m, -03, GETDATE()-day(GETDATE()-1)) AS  "MM03"
, DATEADD(m, -02, GETDATE()-day(GETDATE()-1)) AS  "MM02"
, DATEADD(m, -01, GETDATE()-day(GETDATE()-1)) AS  "MM01"

June - Aug 2014. 36 lines of code.

The statements to pull the unit sales data are as follows:

, SUM (CASE WHEN "SalesTable"."SalesDate" BETWEEN DATEADD(m, -36, GETDATE()-day(GETDATE()-1)) AND DATEADD(m, -35, GETDATE()-day(GETDATE()-1))-1 THEN "SalesTable"."SalesQty" - "SalesTable"."ReturnsQty"  ELSE NULL END) AS "NetQty36"
 , SUM (CASE WHEN "SalesTable"."SalesDate" BETWEEN DATEADD(m, -35, GETDATE()-day(GETDATE()-1)) AND DATEADD(m, -34, GETDATE()-day(GETDATE()-1))-1 THEN "SalesTable"."SalesQty" - "SalesTable"."ReturnsQty" ELSE NULL END) AS "NetQty35"
 , SUM (CASE WHEN "SalesTable"."SalesDate" BETWEEN DATEADD(m, -34, GETDATE()-day(GETDATE()-1)) AND DATEADD(m, -33, GETDATE()-day(GETDATE()-1))-1 THEN "SalesTable"."SalesQty" - "SalesTable"."ReturnsQty" ELSE NULL END) AS "NetQty34"

As of today, this is Sep - Nov 2011. It goes on to...

 , SUM (CASE WHEN "SalesTable"."SalesDate" BETWEEN DATEADD(m, -03, GETDATE()-day(GETDATE()-1)) AND DATEADD(m, -02, GETDATE()-day(GETDATE()-1))-1 THEN "SalesTable"."SalesQty" - "SalesTable"."ReturnsQty" ELSE NULL END) AS "NetQty03"
 , SUM (CASE WHEN "SalesTable"."SalesDate" BETWEEN DATEADD(m, -02, GETDATE()-day(GETDATE()-1)) AND DATEADD(m, -01, GETDATE()-day(GETDATE()-1))-1 THEN "SalesTable"."SalesQty" - "SalesTable"."ReturnsQty" ELSE NULL END) AS "NetQty02"
 , SUM (CASE WHEN "SalesTable"."SalesDate" BETWEEN DATEADD(m, -01, GETDATE()-day(GETDATE()-1)) AND DATEADD(m, -00, GETDATE()-day(GETDATE()-1))-1 THEN "SalesTable"."SalesQty" - "SalesTable"."ReturnsQty" ELSE NULL END) AS "NetQty01"

June - Aug 2014.

I'm going to have a lot more to add before this report is done so I want to keep things as tidy as possible.

Thanks,

EDIT

The example below shows that with the above code I am able to get what I want (don't have the rep to post images so I had to do a link). It also shows that with the pivot table I am missing my item numbers and the rolling months. How do I add those two things?

Example

I tried dropping the "ItemTable"."ItemNum" field in as follows, but it doesn't show up in my Crystal Report. Anywhere else I tried to put it, I get "could not be bound" error.

select 
[0] MM00
, [1] MM01
, [2] MM02
, ...
from (
select 
  "ItemTable"."ItemNum",DateDiff(m, "SalesTable"."SalesDate", GetDate()) months_ago
, "SalesTable"."SalesQty" - "SalesTable"."ReturnsQty" NetQty
from your_table
) as source
pivot 
(
 sum(NetQty) For months_ago in ([0], [1], [2], [3], ...)
) as PivotTable

Thanks again.

Answer 1

This situation seems ripe for a Pivot table.

Something along the lines of

select 
    [0] MM00
  , [1] MM01
  , [2] MM02
  , ...
from (
  select 
      DateDiff(m, "SalesTable"."SalesDate", GetDate()) months_ago
    , "SalesTable"."SalesQty" - "SalesTable"."ReturnsQty" NetQty
  from your_table
) as source
pivot 
(
  sum(NetQty) For months_ago in ([0], [1], [2], [3], ...)
) as PivotTable

Edit

Refining the example: Including the item number is fairly straight forward; just include it in the select statement.

select 
    "ItemTable"."ItemNum"
  , [0] MM00
  , [1] MM01
  , [2] MM02
  , ...
from (
  select
      "ItemTable"."ItemNum" 
    , DateDiff(m, "SalesTable"."SalesDate", GetDate()) months_ago
    , "SalesTable"."SalesQty" - "SalesTable"."ReturnsQty" NetQty
  from your_table
) as source
pivot 
(
  sum(NetQty) For months_ago in ([0], [1], [2], [3], ...)
) as PivotTable

Having dynamic names for your pivot columns is rather more difficult. The general idea is that you have to build the SQL as a string and execute it dynamically. A Google search yields a number of results (such as a SQL Hints blog). Some incomplete code to give you the general idea would be.

SET @DynamicPivotQuery = 
 N'select 
    "ItemTable"."ItemNum"
  , [' + @Name1 + ']
  , [' + @Name2 + ']
  , [' + @NameN + ']
from (
  select
      "ItemTable"."ItemNum" 
      /* search MSDN for the best date formatting algorithm */
    , CONVERT_TO_STRING("SalesTable"."SalesDate") month 
    , "SalesTable"."SalesQty" - "SalesTable"."ReturnsQty" NetQty
  from your_table
) as source
pivot 
(
  sum(NetQty) For month in (
      [' + @Name1 + ']
    , [' + @Name2 + ']
    , [' + @NameN + ']
  )
) as PivotTable';

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery