score:1

Accepted answer

Ok here we go, I built out the table structure to validate the SQL. I did not load any data but it does execute.

SELECT * FROM
(
Select  
   catalogid, numitems, allitems - numitems ignoreditems 
from 
    (
        select  
            i.catalogid, 
            case 
                when ocardtype in ('PayPal','Sofort') then sum(i.numitems)
                when ocardtype in ('mastercard','visa') and odate is not null then sum(i.numitems)
            else 0 end numitems, 
            sum(numitems) allitems 
        from 
            orders o
        inner join
            oitems i 
        on 
            i.orderid=o.orderid
        inner join 
            products T1 
        on 
            T1.catalogid = i.catalogid
        group by 
            i.catalogid, ocardtype, odate
    ) A
) B
INNER JOIN
(
    SELECT 
        catalogId, 
        ProcessedSucssessfully = 
           STUFF((SELECT ', ' + CAST( b.orderid as varchar(10)) 
                  FROM oitems b JOIN orders o ON b.orderid = o.orderid 
                  WHERE b.catalogId = a.catalogId 
                  AND (o.ocardtype in ('PayPal','Sofort') OR o.ocardtype in  ('mastercard','visa') and o.odate is not null) 
                  FOR XML PATH('')), 1, 2, ''), 
                      NotProcessed = 
            STUFF((SELECT ', ' + CAST( c.orderid as varchar(10)) 
                   FROM oitems c JOIN orders o ON c.orderId = o.orderid 
                   WHERE c.catalogid = a.catalogid 
                   AND (o.ocardtype in ('mastercard') OR o.ocardtype is null) and o.odate is null 
                   FOR XML PATH('')), 1, 2, '') 
    FROM 
        oitems a 
    GROUP BY 
        a.CatalogId 
)C
    ON 
        B.CatalogId = C.CatalogId

Read More

score:1

SQL server view would look something like this

SELECT * FROM
(
    Select  
       catalogid, numitems, allitems - numitems ignoreditems 
    from 
        (
        select * from
        (
            select  
                i.catalogid, 
                sum(case when (ocardtype in ('PayPal','Sofort') 
                OR 
                ocardtype in ('mastercard','visa') and odate is not null) then numitems 
                else 0 end) numitems, 
                sum(numitems) allitems 
            from 
                orders
         ) o
        inner join
            items i 
        on 
            i.orderid=o.orderid
        inner join 
            products T1 
        on 
            T1.catalogid = i.catalogid
        group by 
            i.catalogid
        ) A
    ) X 
) B
INNER JOIN
SELECT * FROM
(
    SELECT 
        catalogId, 
        ProcessedSucssessfully = 
           STUFF((SELECT ', ' + CAST( b.orderid as varchar(10)) 
                  FROM oitems b JOIN orders o ON b.orderid = o.orderid 
                  WHERE b.catalogId = a.catalogId 
                  AND (o.ocardtype in ('PayPal','Sofort') OR o.ocardtype in  ('mastercard','visa') and o.odate is not null) 
                  FOR XML PATH('')), 1, 2, ''), 
                      NotProcessed = 
            STUFF((SELECT ', ' + CAST( c.orderid as varchar(10)) 
                   FROM oitems c JOIN orders o ON c.orderId = o.orderid 
                   WHERE c.catalogid = a.catalogid 
                   AND (o.ocardtype in ('mastercard') OR o.ocardtype is null) and o.odate is null 
                   FOR XML PATH('')), 1, 2, '') 
    FROM 
        oitems a 
    GROUP BY 
        a.CatalogId 
) B
ON A.CatalogId = B.CatalogId

score:2

You could create a view on the sql box and then just Linq to that view

select * from table_a inner join table_b on table_a.field_a = table_b.field_b

EDIT:

or

Select * from (select * from table_a inner join table_b on table_a.field_a = table_b.field_b) AB
inner join
select * from (select * from table_c inner join table_d on table_c.field_c = table_d.field_d) CD
ON
AB.id_column = CD.id_column

More questions

More questions with similar tag