customer wise sales register-ALL

[Former Member]

hi experts,

i want a report to show 'item' wise, 'service' wise n both item n service combine whn user select 'I' , 'S' or null

and date range thn it shud pic up data from below query

Select t.cardcode, cardname, sum(Quantity) as Quantity, sum(Value) as Value, sum(Tax) as Tax,
 sum(Total) as Total, sum(materialvalue) as 'Material Value',sum(CM) as CM, (sum(CM)/SUM(VALUE)*100) as 'CM%' from
(SELECT (T0.CardCode) as cardcode,t0.cardname as 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 materialvalue ,
isnull ((sum( T1.[LineTotal] + T1.[VatSum]) - sum(T1.Quantity * t2.u_cmmc)- sum(T1.[VatSum])) ,
sum(T1.[LineTotal])) as CM ,
ISNULL((((sum( T1.[LineTotal] + T1.[VatSum]) - sum(T1.Quantity * t2.u_cmmc)- sum(T1.[VatSum])) )/(sum(T1.[LineTotal]))*100),'100') as cm_perc
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.CardName,T0.CardCode
union 
SELECT (T0.[CardCode]) as 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 materialvalue,
isnull((-sum( T1.[LineTotal] + T1.[VatSum]) - sum(T1.Quantity * t2.u_cmmc)- sum(T1.[VatSum])) ,
-sum(T1.[LineTotal])) as CM ,
ISNULL((((sum( T1.[LineTotal] + T1.[VatSum]) - sum(T1.Quantity * t2.u_cmmc)- sum(T1.[VatSum]))) / -(-sum(T1.[LineTotal]))*100),'100') as cm_perc
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.CardName,T0.CardCode) as t
group by  t.cardname,t.cardcode

it get fails whn i giv where T0.[DocType] = [%0] AND t0.docdate >= [%1] and t0.docdate <= [%2]

for AND it takes 'I' and 'S' but null codition fails

& for OR null codition wrkng rest are not

ketan.....

[Former Member · Accepted solution]

Hi!

Try this

Select t.cardcode, cardname, sum(Quantity) as Quantity, sum(Value) as Value, sum(Tax) as Tax,
 sum(Total) as Total, sum(materialvalue) as 'Material Value',sum(CM) as CM, (sum(CM)/SUM(VALUE)*100) as 'CM%' from
(SELECT (T0.CardCode) as cardcode,t0.cardname as 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 materialvalue ,
isnull ((sum( T1.[LineTotal] + T1.[VatSum]) - sum(T1.Quantity * t2.u_cmmc)- sum(T1.[VatSum])) ,
sum(T1.[LineTotal])) as CM ,
ISNULL((((sum( T1.[LineTotal] + T1.[VatSum]) - sum(T1.Quantity * t2.u_cmmc)- sum(T1.[VatSum])) )/(sum(T1.[LineTotal]))*100),'100') as cm_perc
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.CardName,T0.CardCode
union 
SELECT (T0.[CardCode]) as 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 materialvalue,
isnull((-sum( T1.[LineTotal] + T1.[VatSum]) - sum(T1.Quantity * t2.u_cmmc)- sum(T1.[VatSum])) ,
-sum(T1.[LineTotal])) as CM ,
ISNULL((((sum( T1.[LineTotal] + T1.[VatSum]) - sum(T1.Quantity * t2.u_cmmc)- sum(T1.[VatSum]))) / -(-sum(T1.[LineTotal]))*100),'100') as cm_perc
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.[DocType] = '' ) and t0.docdate >= [%1] and t0.docdate <= [%2]
group by T0.CardName,T0.CardCode) as t
group by  t.cardname,t.cardcode

[Former Member · Accepted solution]

HI

thnx for reply but it not wrkng for all three cases i tried sum changes also

pls help me

ketan...

[Former Member · Accepted solution]

Hi!

Try this

Select t.cardcode, cardname, sum(Quantity) as Quantity, sum(Value) as Value, sum(Tax) as Tax,
 sum(Total) as Total, sum(materialvalue) as 'Material Value',sum(CM) as CM, (sum(CM)/SUM(VALUE)*100) as 'CM%' from
(SELECT (T0.CardCode) as cardcode,t0.cardname as 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 materialvalue ,
isnull ((sum( T1.[LineTotal] + T1.[VatSum]) - sum(T1.Quantity * t2.u_cmmc)- sum(T1.[VatSum])) ,
sum(T1.[LineTotal])) as CM ,
ISNULL((((sum( T1.[LineTotal] + T1.[VatSum]) - sum(T1.Quantity * t2.u_cmmc)- sum(T1.[VatSum])) )/(sum(T1.[LineTotal]))*100),'100') as cm_perc
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 [%0] = '' ) and t0.docdate >= [%1] and t0.docdate <= [%2]
group by T0.CardName,T0.CardCode
union 
SELECT (T0.[CardCode]) as 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 materialvalue,
isnull((-sum( T1.[LineTotal] + T1.[VatSum]) - sum(T1.Quantity * t2.u_cmmc)- sum(T1.[VatSum])) ,
-sum(T1.[LineTotal])) as CM ,
ISNULL((((sum( T1.[LineTotal] + T1.[VatSum]) - sum(T1.Quantity * t2.u_cmmc)- sum(T1.[VatSum]))) / -(-sum(T1.[LineTotal]))*100),'100') as cm_perc
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 [%0] = '' ) and t0.docdate >= [%1] and t0.docdate <= [%2]
group by T0.CardName,T0.CardCode) as t
group by  t.cardname,t.cardcode

[Former Member]

Hi Ketan,

Try this,

SELECT  K.[CardCode], K.[CardCode], K.[DocType], K.[DocDate], SUM(K.Quantity), SUM(K.Value), SUM(K.Tax), 
SUM(K.Total), SUM(K.Material Value), SUM(K.CM), SUM(K.cm_perc)
FROM 
(SELECT T0.[CardCode], T0.[CardCode], T0.[DocType], T0.[DocDate],
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_perc' 
FROM OINV T0  INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry left outer join oitm t2 on t1.itemcode = t2.itemcode
 
UNION ALL
 
SELECT T3.[CardCode], T3.[CardName], T3.[DocType], T3.[DocDate],
-sum(T4.[Quantity]) as Quantity, 
-sum(T4.[LineTotal]) as Value, 
-sum(T4.[VatSum]) as Tax, 
-sum( T4.[LineTotal] + T4.[VatSum]) as Total, 
isnull(-sum(T4.Quantity * t5.u_cmmc),'00')  as 'Material Value' ,
isnull(-sum( T4.[LineTotal] + T4.[VatSum]) - sum(T4.Quantity * t5.u_cmmc) ,-sum(T4.[LineTotal]))as CM ,
isnull(((sum( T4.[LineTotal] + T4.[VatSum]) - sum(T4.Quantity * t5.u_cmmc)) / (sum(T4.[LineTotal]))*100),'100') as 'cm_perc' 
FROM ORIN T3  INNER JOIN RIN1 T4 ON T3.DocEntry = T4.DocEntry left outer join oitm t5 on t5.itemcode = t4.itemcode)K
WHERE 
K.[DocType]='[%0]'
AND
K.DocDate >= '[%1]' AND K.DocDate <= '[%2]'
GROUP BY
 K.[CardCode], K.[CardCode], K.[DocType], K.[DocDate]
ORDER BY
 K.[CardCode], K.[CardCode], K.[DocType], K.[DocDate]

Regards,

Madhan.