I need to get Quantity of items in orders the next 12 months, like this:

I can do like this for one month and repeat, but think there is a better way to do it.

 SELECT I.ItemNumber, ThisMonth.Quantity AS This, NextMonth.Quantity AS Next * 
 FROM Items I
 LEFT JOIN (
    SELECT Quantity,Itemno 
    FROM [Orders] 
    WHERE MONTH([Shipment Date]) = MONTH(GETDATE()) AND YEAR([Shipment Date]) = YEAR(GETDATE())
 ) AS ThisMonth ON ThisMonth.Itemno = I.Itemnumber
 LEFT JOIN (
    SELECT Quantity,Itemno  
    FROM [Orders] 
    WHERE MONTH([Shipment Date]) = MONTH(DATEADD(month, 1,GETDATE())) AND YEAR([Shipment Date]) = YEAR(GETDATE())
 ) AS NextMonth ON NextMonth.Itemno = I.Itemnumber

Can you help? :)

UPDATED WITH MY TABLE DATA BELOW

ITEMS:

ORDERS:

CodePudding user response：

You haven't supplied sample data, but it looks like you need conditional aggregation (aka a pivot). It's generally easier, if a bit more verbose, to do this with SUM(CASE rather than using the PIVOT operator.

SELECT
  i.ItemNumber,
  SUM(CASE WHEN EOMONTH(i.[Shipment Date]) = EOMONTH(GETDATE()) THEN i.Quantity END) AS CurrentMonth,
  SUM(CASE WHEN EOMONTH(i.[Shipment Date]) = EOMONTH(GETDATE(), 1) THEN i.Quantity END) AS Month1,
  SUM(CASE WHEN EOMONTH(i.[Shipment Date]) = EOMONTH(GETDATE(), 2) THEN i.Quantity END) AS Month2,
  SUM(CASE WHEN EOMONTH(i.[Shipment Date]) = EOMONTH(GETDATE(), 3) THEN i.Quantity END) AS Month3,
  SUM(CASE WHEN EOMONTH(i.[Shipment Date]) = EOMONTH(GETDATE(), 4) THEN i.Quantity END) AS Month4,
  SUM(CASE WHEN EOMONTH(i.[Shipment Date]) = EOMONTH(GETDATE(), 5) THEN i.Quantity END) AS Month5,
  SUM(CASE WHEN EOMONTH(i.[Shipment Date]) = EOMONTH(GETDATE(), 6) THEN i.Quantity END) AS Month6,
  SUM(CASE WHEN EOMONTH(i.[Shipment Date]) = EOMONTH(GETDATE(), 7) THEN i.Quantity END) AS Month7,
  SUM(CASE WHEN EOMONTH(i.[Shipment Date]) = EOMONTH(GETDATE(), 8) THEN i.Quantity END) AS Month8,
  SUM(CASE WHEN EOMONTH(i.[Shipment Date]) = EOMONTH(GETDATE(), 9) THEN i.Quantity END) AS Month9,
  SUM(CASE WHEN EOMONTH(i.[Shipment Date]) = EOMONTH(GETDATE(), 10) THEN i.Quantity END) AS Month10,
  SUM(CASE WHEN EOMONTH(i.[Shipment Date]) = EOMONTH(GETDATE(), 11) THEN i.Quantity END) AS Month11,
FROM Items i
GROUP BY
  i.ItemNumber;

CodePudding user response：

A possible option, using the PIVOT relational operator:

Sample data:

SELECT *
INTO Items
FROM (VALUES
   ('item1', CONVERT(date, '20220802'), 10),
   ('item1', CONVERT(date, '20220902'), 13),
   ('item1', CONVERT(date, '20221002'), 12),
   ('item1', CONVERT(date, '20221102'), 130),
   ('item2', CONVERT(date, '20220802'), 20),
   ('item3', CONVERT(date, '20220902'), 23),
   ('item4', CONVERT(date, '20221002'), 22),
   ('item5', CONVERT(date, '20221102'), 230)
) t (ItemNo, ShipmentDate, Quantity)

Statement:

SELECT *
FROM (
   SELECT ItemNo, Quantity, DATEDIFF(month, EOMONTH(GETDATE()), EOMONTH(ShipmentDate)) AS NextMonth
   FROM Items
) t
PIVOT (
   SUM(Quantity)
   FOR NextMonth IN ([0], [1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11])
) p

Result: