SQL Server Sum Multiple Date Ranges

zvms9eto 于 2023-08-02 发布在其他

关注(0)|答案(1)|浏览(89)

requirement to look forward 6 months and sum the demand for each product per month for each manufacture looking 6 months ahead. I have done a little work to make tables and column names more understandable.

I need to be able to sum some quantities across multiple date ranges. below is where I got myself to but has the massive issue of sum-ing the whole lot in every month it see's a product, rather than just the months demand.

@Manufacturer VARCHAR(10)

AS
BEGIN

    SET NOCOUNT ON

            SELECT 
                CASE
                WHEN o.CLOSEDATE Between GETDATE() And DateAdd(MONTH,+6,GETDATE()) AND m.CODE = @Manufacturer THEN p.PRODUCTCODE
            END AS 'Product',   
                CASE
                WHEN o.CLOSEDATE Between GETDATE() And DateAdd(MONTH,+1,GETDATE()) AND m.CODE = @Manufacturer THEN SUM(ql.QUANTITY)
            END AS 'Month 1 Quantity',
                CASE
                WHEN o.CLOSEDATE Between DateAdd(MONTH,+1,GETDATE()) And DateAdd(MONTH,+2,GETDATE()) AND m.CODE = @Manufacturer THEN SUM(ql.QUANTITY)
            END AS 'Month 2 Quantity',
                CASE
                WHEN o.CLOSEDATE Between DateAdd(MONTH,+2,GETDATE()) And DateAdd(MONTH,+3,GETDATE()) AND m.CODE = @Manufacturer THEN SUM(ql.QUANTITY)
            END AS 'Month 3 Quantity',
                CASE
                WHEN o.CLOSEDATE Between DateAdd(MONTH,+3,GETDATE()) And DateAdd(MONTH,+4,GETDATE()) AND m.CODE = @Manufacturer THEN SUM(ql.QUANTITY)
            END AS 'Month 4 Quantity',
                CASE
                WHEN o.CLOSEDATE Between DateAdd(MONTH,+4,GETDATE()) And DateAdd(MONTH,+5,GETDATE()) AND m.CODE = @Manufacturer THEN SUM(ql.QUANTITY)
            END AS 'Month 5 Quantity',
                CASE
                WHEN o.CLOSEDATE Between DateAdd(MONTH,+5,GETDATE()) And DateAdd(MONTH,+6,GETDATE()) AND m.CODE = @Manufacturer THEN SUM(ql.QUANTITY)
            END AS 'Month 6 Quantity'
            
            FROM OPPORTUNITY AS o
            JOIN CUSTOMEROPPORTUNITY AS co (NOLOCK)
            on co.ID = o.ID
            JOIN CUSTOMER AS c (NOLOCK)
            on co.CUSTOMER = c.ID
            JOIN ENTITYRELATIONSHIP AS er (NOLOCK)
            on er.C_ENTITY1 = o.ID
            JOIN QUOTATION AS sq (NOLOCK)
            on er.ENTITY2 = sq.ID
            JOIN SALESQUOTATIONLINE AS ql (NOLOCK)
            on ql.OWNER = sq.ID
            JOIN PRODUCT AS p (NOLOCK)
            on p.ID = ql.PRODUCT
            JOIN MANUFACTURER AS m (NOLOCK)
            on m.ID = p.MANUFACTURER
            JOIN OPPORTUNITYSOURCE AS os (NOLOCK)
            on o.OPPORTUNITYSOURCE = os.ID
            JOIN OPPORTUNITYSTATUS AS wf (NOLOCK)
            on o.WORKFLOWSTATUS = wf.ID
            WHERE m.CODE = @Manufacturer AND wf.CODE != 'LOST' AND o.CLOSEDATE Between GETDATE() And DateAdd(MONTH,+12,GETDATE())
            GROUP BY p.PRODUCTCODE, o.CLOSEDATE, m.CODE
END

sql-server

来源：https://stackoverflow.com/questions/76711317/sum-multiple-date-ranges

1条答案

按热度按时间

b0zn9rqh1#

You have a number of issues here:

Put the CASEinside the SUM , so that you are conditionally aggregating.
Remove CLOSEDATE from the GROUP BY
You probably want to truncate to the beginning of the day, you can do this using CAST as date
A check on m.CODE = @Manufacturer is not necessary, you are doing it in the WHERE anyway.
Use date ranges >= AND < rather than BETWEEN . See this article .
Use [] to quote column names, not '' .
Do NOT use NOLOCK , it has serious data integrity implications, and probably doesn't do what you think it does. If you need to avoid blocking then use SNAPSHOT .

SELECT 
  p.PRODUCTCODE AS Product,
  SUM(CASE WHEN o.CLOSEDATE >=                   CAST(GETDATE() AS date)  AND o.CLOSEDATE < CAST(DATEADD(month, 1, GETDATE()) AS date) THEN ql.QUANTITY END) AS [Month 1 Quantity],
  SUM(CASE WHEN o.CLOSEDATE >= DATEADD(month, 1, CAST(GETDATE() AS date)) AND o.CLOSEDATE < CAST(DATEADD(month, 2, GETDATE()) AS date) THEN ql.QUANTITY END) AS [Month 2 Quantity],
  SUM(CASE WHEN o.CLOSEDATE >= DATEADD(month, 2, CAST(GETDATE() AS date)) AND o.CLOSEDATE < CAST(DATEADD(month, 3, GETDATE()) AS date) THEN ql.QUANTITY END) AS [Month 3 Quantity],
  SUM(CASE WHEN o.CLOSEDATE >= DATEADD(month, 3, CAST(GETDATE() AS date)) AND o.CLOSEDATE < CAST(DATEADD(month, 4, GETDATE()) AS date) THEN ql.QUANTITY END) AS [Month 4 Quantity],
  SUM(CASE WHEN o.CLOSEDATE >= DATEADD(month, 4, CAST(GETDATE() AS date)) AND o.CLOSEDATE < CAST(DATEADD(month, 5, GETDATE()) AS date) THEN ql.QUANTITY END) AS [Month 5 Quantity],
  SUM(CASE WHEN o.CLOSEDATE >= DATEADD(month, 5, CAST(GETDATE() AS date)) AND o.CLOSEDATE < CAST(DATEADD(month, 6, GETDATE()) AS date) THEN ql.QUANTITY END) AS [Month 6 Quantity]
FROM OPPORTUNITY AS o
JOIN CUSTOMEROPPORTUNITY AS co ON co.ID = o.ID
JOIN CUSTOMER AS c ON co.CUSTOMER = c.ID
JOIN ENTITYRELATIONSHIP AS er ON er.C_ENTITY1 = o.ID
JOIN QUOTATION AS sq ON er.ENTITY2 = sq.ID
JOIN SALESQUOTATIONLINE AS ql ON ql.OWNER = sq.ID
JOIN PRODUCT AS p ON p.ID = ql.PRODUCT
JOIN MANUFACTURER AS m ON m.ID = p.MANUFACTURER
JOIN OPPORTUNITYSOURCE AS os ON o.OPPORTUNITYSOURCE = os.ID
JOIN OPPORTUNITYSTATUS AS wf ON o.WORKFLOWSTATUS = wf.ID
WHERE m.CODE = @Manufacturer
  AND wf.CODE != 'LOST'
  AND o.CLOSEDATE >= CAST(GETDATE() AS date)
  AND o.CLOSEDATE < CAST(DATEADD(month, 12, GETDATE()) AS date)
GROUP BY
  p.PRODUCTCODE;

赞(0）回复(0）举报 2023-08-02

我来回答

SQL Server Sum Multiple Date Ranges

1条答案

相关问题

热门标签

最新问答