sales register

[Former Member]

hi all,

how to consolidate two line items in this query

for item base and service base it show two seperate line item or rows in output

but i want single row for same customer code

SELECT MAX (T0.[CardCode]), T0.[CardName], sum(T1.[Quantity]) as Quantity, sum(T1.[LineTotal]) as Value, sum(T1.[VatSum]) as Tax, sum( T1.[LineTotal] + T1.[VatSum]) as Total,
isnull(sum(T1.Quantity * t2.u_cmmc),'00')as 'Material Value' ,
isnull (sum( T1.[LineTotal] + T1.[VatSum]) - sum(T1.Quantity * t2.u_cmmc) ,sum(T1.[LineTotal])) as CM ,
isnull(((sum( T1.[LineTotal] + T1.[VatSum]) - sum(T1.Quantity * t2.u_cmmc) )/(sum(T1.[LineTotal]))*100),'100') as 'CM%' 
FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry left outer join oitm t2 on t1.itemcode = t2.itemcode
where T0.[DocType] = [%0] or t0.docdate >= [%1] and t0.docdate <= [%2]
group by T0.CardCode,T0.CardName

UNION

SELECT MAX (T0.[CardCode]), T0.[CardName], -sum(T1.[Quantity]) as Quantity, -sum(T1.[LineTotal]) as Value, -sum(T1.[VatSum]) as Tax, -sum( T1.[LineTotal] + T1.[VatSum]) as Total, isnull(-sum(T1.Quantity * t2.u_cmmc),'00')  as 'Material Value' ,
isnull(-sum( T1.[LineTotal] + T1.[VatSum]) - sum(T1.Quantity * t2.u_cmmc) ,-sum(T1.[LineTotal]))as CM ,
isnull(((sum( T1.[LineTotal] + T1.[VatSum]) - sum(T1.Quantity * t2.u_cmmc)) / (sum(T1.[LineTotal]))*100),'100') as 'CM%' 
FROM ORIN T0  INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry left outer join oitm t2 on t1.itemcode = t2.itemcode
where T0.[DocType] = [%0] or t0.docdate >= [%1] and t0.docdate <= [%2]
group by T0.CardCode,T0.CardName

pls help me in this regard

ketan......

[former_member187989]

Check this threads,it will give u idea

Jeyakanthan

[Former Member]

Hi Kehan,

If you want to show only one row for one BP, you have to use OJDT and JDT1 tables to make your query. Start your query first. When you have problem to create, let us know.

Thanks,

Gordon

[Former Member]

Hi Ketan,

Check the thread, you will be get any idea or solution.

Regards,

Madhan.