title: B1 query | Monthly sales consolidated for different time periods (i.e. 1 / 3 / 6 / 12 months)
body: <p>really struggling with this one. Looking for a way to query sale volumes for different time spans all put into one table.</p>
  <p>Let's assume it is Aug 13th. I then want summed up sales volumes for:</p>
  <ul>
  <li>Jul 1st 00:00:00 - July 31st 11:59:59 (last 1 month)</li>
  <li>May-1st 00:00:00 - July 31st 11:59:59 (last 3 months)</li>
  <li>Feb 1st 00:00:00 - July 31st 11:59:59 (last 6 months)</li>
  <li>Aug 1st 00:00:00 - July 31st 11:59:59 (last 12 months)</li>
  </ul>
  <p>I tried to reuse snippets from the pivot query examples that can be found related to sales volumes but could not succeed.
  I started over from scratch by first trying to create datetimes which I can use for the time spans but that's already quite fiddly and somehow feels wrong.</p>
  <p>I thought that I need to end up with 4 single queries which then need to be joined to get all the columns into one single table:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ItemName</th>
  <th>ItemCode</th>
  <th>Sal.vol 1 mth</th>
  <th>Sal.vol 3 mths</th>
  <th>Sal.vol 6 mths</th>
  <th>Sal.vol 12 mths</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Item 1</td>
  <td>10102251</td>
  <td>10</td>
  <td>30</td>
  <td>60</td>
  <td>120</td>
  </tr>
  <tr>
  <td>Item 2</td>
  <td>10120101</td>
  <td>14</td>
  <td>35</td>
  <td>78</td>
  <td>181</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>My progress so far:</p>
  <pre><code>SELECT
    T1.itemcode,  month(T1.[docDate]) as month, T3.ItemName,
    SUM(t1.quantity )
  FROM INV1 T1 INNER JOIN OINV T2 ON T1.docentry = T2.docentry INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode
  WHERE T1.[docDate] between DATEADD(month, -4, GETDATE()) and DATEADD(month, -1, GETDATE()) AND T1.ItemCode LIKE '1%' AND T3.ItmsGrpCod = 100
  Group By  T1.itemcode, T3.ItemName, month(T1.[docDate])
  </code></pre>
  <p>For my datetimes I wanted to build them as follows:</p>
  <pre><code>DECLARE @date date = month(DATEADD(month, -1, GETDATE())) + '-01-' + year(DATEADD(month, -1, GETDATE()));
  DECLARE @datetime datetime = @date;     
  SELECT @datetime AS '@datetime'
  </code></pre>
  <p>I However did not succeed yet as I am having trouble with int to date conversion and I think the month() for Jan-Sep will not work as it return only 1 digit...</p>
  <p>Could anyone help pointing me into the right direction?</p>
  <p>Thanks in advance!</p>
  <hr />
  <p>Update 1:</p>
  <p>@imerd pointed me into the right direction. I was able to build my query as desired. Sharing my intermediate result as this works for now. Query needs some cleaning and optimisation and the recurring main part sourced out into a 2nd function.</p>
  <pre><code>create function MonthFirst (@period integer ) returns date
  as begin
  declare @returnperiod date 
  set @returnperiod = cast(cast(year(DATEADD(month, @period, GETDATE())) as varchar(4)) + '-' +
      cast(month(DATEADD(month, @period, GETDATE())) as varchar(2)) + '-01' as date)
   return @returnperiod
  end
  -- drop function dbo.MonthFirst
  
  SELECT S12.ItemCode, o1.ItemName, S12.[last 12 months], S6.[last 6 months], S3.[last 3 months], S1.[last month] FROM 
  (SELECT
     T1.itemcode,  CAST(SUM(t1.quantity) AS int) AS 'last 12 months'
  FROM DLN1 T1 INNER JOIN ODLN T2 ON T1.docentry = T2.docentry INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode
  WHERE T1.[docDate] between CAST(dbo.MonthFirst(-12) AS datetime) AND  DATEADD(second, -1, CAST(dbo.MonthFirst(0) AS datetime)) 
        AND T3.validFor = 'Y' AND T3.ItmsGrpCod = 100 --products / 102 raw materials
        AND T2.CANCELED = 'N'
  Group By T1.itemcode) S12
  
  FULL JOIN 
  
  (SELECT
     T1.itemcode,  CAST(SUM(t1.quantity) AS int) AS 'last 6 months'
  FROM DLN1 T1 INNER JOIN ODLN T2 ON T1.docentry = T2.docentry INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode
  WHERE T1.[docDate] between CAST(dbo.MonthFirst(-6) AS datetime) AND  DATEADD(second, -1, CAST(dbo.MonthFirst(0) AS datetime)) 
        AND T3.validFor = 'Y' AND T3.ItmsGrpCod = 100 --products / 102 raw materials
        AND T2.CANCELED = 'N'
  Group By T1.itemcode) S6 on S12.ItemCode = S6.ItemCode
  
  FULL JOIN 
  
  (SELECT
     T1.itemcode,  CAST(SUM(t1.quantity) AS int) AS 'last 3 months'
  FROM DLN1 T1 INNER JOIN ODLN T2 ON T1.docentry = T2.docentry INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode
  WHERE T1.[docDate] between CAST(dbo.MonthFirst(-3) AS datetime) AND  DATEADD(second, -1, CAST(dbo.MonthFirst(0) AS datetime)) 
        AND T3.validFor = 'Y' AND T3.ItmsGrpCod = 100 --products / 102 raw materials
        AND T2.CANCELED = 'N'
  Group By T1.itemcode) S3 on S12.ItemCode = S3.ItemCode
  
  FULL JOIN 
  
  (SELECT
     T1.itemcode,  CAST(SUM(t1.quantity) AS int) AS 'last month'
  FROM DLN1 T1 INNER JOIN ODLN T2 ON T1.docentry = T2.docentry INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode
  WHERE T1.[docDate] between CAST(dbo.MonthFirst(-1) AS datetime) AND  DATEADD(second, -1, CAST(dbo.MonthFirst(0) AS datetime)) 
        AND T3.validFor = 'Y' AND T3.ItmsGrpCod = 100 --products / 102 raw materials
        AND T2.CANCELED = 'N'
  Group By T1.itemcode) S1 on S12.ItemCode = S1.ItemCode
  
  INNER JOIN OITM o1 on S12.ItemCode = o1.ItemCode
  
  ORDER BY o1.ItemName
  </code></pre>
  <hr />
  <p>Update 2:</p>
  <p>I now combined both suggestions/hints from @imerd and @Gordon Linoff and got this little nifty query which pretty much exactly does want I wanted.</p>
  <p>Thank you very much!</p>
  <p>Here's my solution:</p>
  <pre><code>DECLARE @period integer = -12
  DECLARE @startperiod date
  DECLARE @endperiod date 
  SET @startperiod = CAST(cast(year(DATEADD(month, @period, GETDATE())) AS varchar(4)) + '-' +
      cast(month(DATEADD(month, @period, GETDATE())) AS varchar(2)) + '-01' AS date)
  SET @endperiod = CAST(cast(year(DATEADD(month, 0, GETDATE())) AS varchar(4)) + '-' +
      cast(month(DATEADD(month, 0, GETDATE())) AS varchar(2)) + '-01' AS datetime)
  
  SELECT T1.ItemCode, T3.ItemName, 
         sum(case when datediff(month, T1.[docDate], GETDATE()) &lt;= 12 then t1.quantity else 0 end) as month_12,
         sum(case when datediff(month, T1.[docDate], GETDATE()) &lt;= 6 then t1.quantity else 0 end) as month_6,
         sum(case when datediff(month, T1.[docDate], GETDATE()) &lt;= 3 then t1.quantity else 0 end) as month_3,
         sum(case when datediff(month, T1.[docDate], GETDATE()) = 1 then t1.quantity else 0 end) as month_1
  FROM DLN1 T1 INNER JOIN ODLN T2 ON T1.docentry = T2.docentry INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode
  WHERE T1.[docDate] between CAST(@startperiod AS datetime) AND  DATEADD(second, -1, CAST(@endperiod AS datetime)) 
        AND T3.validFor = 'Y' AND T3.ItmsGrpCod = 100 --products / 102 raw materials
        AND T2.CANCELED = 'N'
  GROUP BY T1.ItemCode, T3.ItemName
  ORDER BY T3.ItemName ASC
  </code></pre>
  <p>@Gordon Linoff: '=' needed to be '&lt;=' because I want sales volume from e.g. all of the last 12 months and not the volume from just the single month one year ago.</p>
  <p>Thanks again for the very quick help!</p>