rickyProgrammer rickyProgrammer - 4 months ago 22
SQL Question

SQL Query using Cross Apply to get Sum Conditionally

Output to be produced

enter image description here

using this as reference but now with different scenario SQL Server query : get the sum conditionally

explanation:

Item, Sales, and remarks columns are given column from a database, New Sales column is a formulated column where in, it is getting the sum of items with the same keyword remarks except the default n/a remarks.

(regardless the remarks is not fully identical, at least there's a common similarity like what is on the image above - item 5 has "new" in it, still it sums up with item 6 because of their similar keyword found "small")

code used

FIRST OPTION- using partition - This doesn't work because when the remarks is not identical to each other it will not get the sum properly (for item5 and item6)

CASE
WHEN ([remarks] not like '%big%') AND ([remarks] not like '%PAENGS%')


THEN sales
ELSE SUM(sales) OVER(PARTITION BY [remarks])
END as 'New Sales'


SECOND OPTION -using Cross Apply - So it leave me to this, but I was lost as it is not getting the desired output.

CROSS APPLY
SELECT
d.*,
NewSales =
CASE
WHEN ([remarks] not like '%big%') or ([remarks] not like '%small%')
THEN Sales
ELSE x.NewSales
END


FROM #MSRSuperFinal3 d
CROSS APPLY(SELECT NewSales = SUM(Sales)
FROM #MSRSuperFinal3
WHERE ([remarks] like '%big%') or ([remarks] like '%small%')
)x


Any help will be highly appreciated

Answer

Using CROSS APPLY

SELECT *
FROM temp t
CROSS APPLY(
    SELECT SUM(sales)
    FROM temp
    WHERE
        remarks LIKE '%' + t.remarks + '%'
        OR t.remarks LIKE '%' + remarks + '%'
)x(NewSales)
WHERE remarks <> 'n/a'

UNION ALL

SELECT *, 
    NewSales = sales
FROM temp
WHERE remarks = 'n/a'
ORDER BY item

Based on your comment, this should be your final query:

SELECT * 
FROM #MSRSuperFinal3 t
CROSS APPLY( 
    SELECT 
        SUM(CurrentMonth)
    FROM #MSRSuperFinal3 
    WHERE 
        t.misc LIKE '%' + misc + '%'
        OR misc LIKE '%' + t.misc + '%'
)x(NewSales) 
WHERE 
    ([misc] LIKE '%BIGKAHUNA%') 
    or ([misc] LIKE '%PAENGS%') 

UNION ALL 
SELECT *, 
    NewSales = CurrentMonth 
FROM #MSRSuperFinal3 
WHERE 
    ([misc] not like '%BIGKAHUNA%')
    AND ([misc] not like '%PAENGS%')
    AND ([misc] not like '%ROBINSONS%')
ORDER BY location, name 
Comments