I have the following (summarized) sales invoice table from a customer ERP's SQL database. I've filtered out a single invoice for this example.

In this table, "Fixed" and "Flex" kits are discrete bundles of items (lintyp 2 and 6 respectively), and components are of course the component items within those kits. My problem is that, as you can see above, my costs per kit are always 0 as the ERP does not natively sum up and store costs. The challenge is that the SQL tables also do not contain any sort of parent-child relationship between kits and components.

My desired output would be to have only kit-type item codes with the sum of their components' costs, as well as any "normal" type items left as-is. Visually, it would look like the following...

I imagine I would need a cursor to iterate through the table, or some sort of usage of the LEAD/LAG window function, however I'm having a difficult time fleshing out the logic.

It is worth noting that the ERP always organizes items within an invoice such that, if there is a kit, the corresponding component items will follow, so use of the LEAD/LAG function is a reliable option. (edit: lets assume I have a field that ensures the order is reliable)

How can I accomplish this output?

CodePudding user response：

Can you try

DECLARE @T TABLE (
LINTYP_DESC VARCHAR(100)
,Cost INT
,ItemCode VARCHAR(10)
)
INSERT INTO @T
VALUES('Fixed Kit',1000,'FIN051')
,('Fixed Kit Component',1000,'BMS003')
,('Normal',1000,'BMS057')
,('Flex Kit',1000,'FIN009')
,('Flex Kit Component',1000,'FIN010')

SELECT T.LINTYP_DESC,T.ItemCode,
C.TOTAL FROM @T T
LEFT JOIN (
SELECT REPLACE(LINTYP_DESC,'Component','')LINTYP_DESC, SUM(Cost)TOTAL FROM @T
GROUP BY REPLACE(LINTYP_DESC,'Component',''))C ON C.LINTYP_DESC = T.LINTYP_DESC
WHERE C.LINTYP_DESC IS NOT NULL

CodePudding user response：

select   min(LINTYP_0)                                                        as LINTYP_0
        ,min(LINTYP_DESC)                                                     as LINTYP_DESC
        ,min(InvoiceNumber)                                                   as InvoiceNumber
        ,min(case when LINTYP_DESC not like('%Component%') then ItemCode end) as ItemCode
        ,sum(Cost)                                                            as cost
       
from     (
         select  *
                 ,case when LINTYP_DESC like('%Fixed Kit%') then 1
                       when LINTYP_DESC like('%Flex Kit%')  then 2
                       else 3 end as grp
         from    t
         ) t
group by grp
    
          

Fiddle