mohamed faisal mohamed faisal - 13 days ago 4
SQL Question

How to make this query for this scenario in SQL SERVER?

I have two table called

TransactionItem, ItemRelation


Result of my query of two tables
Result of my query of two tables
As you can see the result on image, now I just want to divide all the values of quantity from first result by size from second result. For example

QUANTITY
6/48
64/48
167/48
31/48
2367/48
1664/48
8/48


How to do it in a single query?.

Answer

How about this:

SELECT QUANTITY / SIZE QUANTITY FROM (
    select sum(dqty) as QUANTITY
    FROM [HQMatajer].[dbo].[TransactionItem]
    where ItemLookupCode='306519P0001'
    group by Tyear, Tmonth
) A
CROSS JOIN (
    select ItemLookupCode, convert(INT,SUBSTRING(ItemLookupCode,8,4)) as SIZE
    from [HQMatajer].[dbo].[ItemRelation]
    where
        ItemLookupCode='319348P0001'
        or ChildItemLookupCode1='319348P0001'
        or ChildItemLookupCode2='319348P0001'
        or ChildItemLookupCode='319348P0001'
) B
Comments