Link to home
Start Free TrialLog in
Avatar of ammartahir1978
ammartahir1978Flag for United Kingdom of Great Britain and Northern Ireland

asked on

how to add the attributes of the report

hi everyone,
i have a report which give me details about payment types.

i want to add some fields and show one attribute against those fields.

for example:
BRANCH          TENDER_TYPE            VALUE              DATE
london                 amex                          80                      28/02/2008
london                 DELTA                          80                      28/02/2008
london                 MASTER CARD                          80                      28/02/2008
london                 CASH                          80                      28/02/2008
london                 CHEQUE                          80                      28/02/2008

what i want is
amex
DELTA+MASTER CARD = CREDIT_CARDS
CASH+CHEQUES = CASH_CHEQUE

please help
CREATE procedure tender_report_NEW   
@datetime1 datetime,    
@datetime2 datetime,    
@branch_code varchar(30)  = ''  
    
As    
Begin  
   
select b.branch_desc,    
(CASE WHEN pod.description IN ('Cash','Cash Change') THEN 'CASH' ELSE pod.description END) as 'DESC',    
sum(pttd.value) as TotalValue,    
convert(varchar(10), pth.txn_date_time, 101) AS 'WeekdayDate'  
     
from pm_trans_tender_details pttd,    
     pos_definitions pod,    
     pm_trans_header pth,    
     branch b    
     
where pth.txn_date_time between @datetime1 and @datetime2    
and pttd.tender_type_id = pod.id    
and pttd.receipt_id = pth.receipt_id    
and pth.branch_id = b.branch_id    
and pth.flag = 1    
and pttd.validated_payment = 1    
and pth.transaction_void = 0    
and b.branch_code = CASE WHEN @branch_code = '' THEN b.branch_code ELSE @branch_code END  
group by convert(varchar(10), pth.txn_date_time, 101),b.branch_desc,    
         (CASE WHEN pod.description IN ('Cash','Cash Change') THEN 'CASH' ELSE pod.description END)    
     
order by convert(varchar(10), pth.txn_date_time, 101),  
         (CASE WHEN pod.description IN ('Cash','Cash Change') THEN 'CASH' ELSE pod.description END)  
   
end

Open in new window

Avatar of ee_rlee
ee_rlee
Flag of Philippines image

hi, try this
CREATE procedure tender_report_NEW   
@datetime1 datetime,    
@datetime2 datetime,    
@branch_code varchar(30)  = ''  
    
As    
Begin  
   
select b.branch_desc,    
(CASE WHEN pod.description IN ('Cash','Cash Change') THEN 'CASH' ELSE pod.description END) as 'DESC',    
SUM(CASE WHEN tender_type IN ('DELTA','MASTER CARD') THEN pttd.value ELSE 0 END) as 'CREDIT_CARDS',    
SUM(CASE WHEN tender_type IN ('CASH','CHEQUE') THEN pttd.value ELSE 0 END) as 'CASH_CHEQUE'
 
 
sum(pttd.value) as TotalValue,    
convert(varchar(10), pth.txn_date_time, 101) AS 'WeekdayDate'  
     
from pm_trans_tender_details pttd,    
     pos_definitions pod,    
     pm_trans_header pth,    
     branch b    
     
where pth.txn_date_time between @datetime1 and @datetime2    
and pttd.tender_type_id = pod.id    
and pttd.receipt_id = pth.receipt_id    
and pth.branch_id = b.branch_id    
and pth.flag = 1    
and pttd.validated_payment = 1    
and pth.transaction_void = 0    
and b.branch_code = CASE WHEN @branch_code = '' THEN b.branch_code ELSE @branch_code END  
group by convert(varchar(10), pth.txn_date_time, 101),b.branch_desc,    
         (CASE WHEN pod.description IN ('Cash','Cash Change') THEN 'CASH' ELSE pod.description END)    
     
order by convert(varchar(10), pth.txn_date_time, 101),  
         (CASE WHEN pod.description IN ('Cash','Cash Change') THEN 'CASH' ELSE pod.description END)  
   
end

Open in new window

slight correction. missed a comma
CREATE procedure tender_report_NEW   
@datetime1 datetime,    
@datetime2 datetime,    
@branch_code varchar(30)  = ''  
    
As    
Begin  
   
select b.branch_desc,    
(CASE WHEN pod.description IN ('Cash','Cash Change') THEN 'CASH' ELSE pod.description END) as 'DESC',    
SUM(CASE WHEN tender_type IN ('DELTA','MASTER CARD') THEN pttd.value ELSE 0 END) as 'CREDIT_CARDS',    
SUM(CASE WHEN tender_type IN ('CASH','CHEQUE') THEN pttd.value ELSE 0 END) as 'CASH_CHEQUE',
sum(pttd.value) as TotalValue,    
convert(varchar(10), pth.txn_date_time, 101) AS 'WeekdayDate'  
     
from pm_trans_tender_details pttd,    
     pos_definitions pod,    
     pm_trans_header pth,    
     branch b    
     
where pth.txn_date_time between @datetime1 and @datetime2    
and pttd.tender_type_id = pod.id    
and pttd.receipt_id = pth.receipt_id    
and pth.branch_id = b.branch_id    
and pth.flag = 1    
and pttd.validated_payment = 1    
and pth.transaction_void = 0    
and b.branch_code = CASE WHEN @branch_code = '' THEN b.branch_code ELSE @branch_code END  
group by convert(varchar(10), pth.txn_date_time, 101),b.branch_desc,    
         (CASE WHEN pod.description IN ('Cash','Cash Change') THEN 'CASH' ELSE pod.description END)    
     
order by convert(varchar(10), pth.txn_date_time, 101),  
         (CASE WHEN pod.description IN ('Cash','Cash Change') THEN 'CASH' ELSE pod.description END)  
   
end

Open in new window

Avatar of ammartahir1978

ASKER

hi ee_rlee its not adding the values, its showing me everything
can you try this?
CREATE procedure tender_report_NEW   
@datetime1 datetime,    
@datetime2 datetime,    
@branch_code varchar(30)  = ''  
    
As    
Begin  
   
select b.branch_desc,    
SUM(CASE WHEN tender_type IN ('DELTA','MASTER CARD') THEN pttd.value ELSE 0 END) as 'CREDIT_CARDS',    
SUM(CASE WHEN tender_type IN ('CASH','CHEQUE') THEN pttd.value ELSE 0 END) as 'CASH_CHEQUE',
sum(pttd.value) as TotalValue,    
convert(varchar(10), pth.txn_date_time, 101) AS 'WeekdayDate'  
     
from pm_trans_tender_details pttd,    
     pos_definitions pod,    
     pm_trans_header pth,    
     branch b    
     
where pth.txn_date_time between @datetime1 and @datetime2    
and pttd.tender_type_id = pod.id    
and pttd.receipt_id = pth.receipt_id    
and pth.branch_id = b.branch_id    
and pth.flag = 1    
and pttd.validated_payment = 1    
and pth.transaction_void = 0    
and b.branch_code = CASE WHEN @branch_code = '' THEN b.branch_code ELSE @branch_code END  
group by convert(varchar(10), pth.txn_date_time, 101),b.branch_desc,    
order by convert(varchar(10), pth.txn_date_time, 101),  
   
end

Open in new window

error 156 incorrect syntax near keyword 'order'
sorry, remove the ,  before end
CREATE procedure tender_report_NEW   
@datetime1 datetime,    
@datetime2 datetime,    
@branch_code varchar(30)  = ''  
    
As    
Begin  
   
select b.branch_desc,    
SUM(CASE WHEN tender_type IN ('DELTA','MASTER CARD') THEN pttd.value ELSE 0 END) as 'CREDIT_CARDS',    
SUM(CASE WHEN tender_type IN ('CASH','CHEQUE') THEN pttd.value ELSE 0 END) as 'CASH_CHEQUE',
sum(pttd.value) as TotalValue,    
convert(varchar(10), pth.txn_date_time, 101) AS 'WeekdayDate'  
     
from pm_trans_tender_details pttd,    
     pos_definitions pod,    
     pm_trans_header pth,    
     branch b    
     
where pth.txn_date_time between @datetime1 and @datetime2    
and pttd.tender_type_id = pod.id    
and pttd.receipt_id = pth.receipt_id    
and pth.branch_id = b.branch_id    
and pth.flag = 1    
and pttd.validated_payment = 1    
and pth.transaction_void = 0    
and b.branch_code = CASE WHEN @branch_code = '' THEN b.branch_code ELSE @branch_code END  
group by convert(varchar(10), pth.txn_date_time, 101),b.branch_desc,    
order by convert(varchar(10), pth.txn_date_time, 101)
   
end

Open in new window

ERROR 257 implicit conversion from data type varchar to money is not allowed, use Convert function to run this query
i changed it a little you script but only text not the functions
CREATE procedure tender_report_NEW   
@datetime1 datetime,    
@datetime2 datetime,    
@branch_code varchar(30)  = ''  
    
As    
Begin  
   
select b.branch_desc,    
SUM(CASE WHEN pod.description IN ('DELTA','MASTERCARD','visa','maestro','VISA DELTA','DELTA','UNKNOWn CARD','maestro intl') THEN pttd.value ELSE pod.description END) as 'CREDIT_CARDS',    
SUM(CASE WHEN pod.description IN ('CASH','CHEQUE') THEN pttd.value ELSE pod.description END) as 'CASH_CHEQUE',
sum(pttd.value) as TotalValue,    
convert(varchar(10), pth.txn_date_time, 101) AS 'WeekdayDate'  
     
from pm_trans_tender_details pttd,    
     pos_definitions pod,    
     pm_trans_header pth,    
     branch b    
     
where pth.txn_date_time between @datetime1 and @datetime2    
and pttd.tender_type_id = pod.id    
and pttd.receipt_id = pth.receipt_id    
and pth.branch_id = b.branch_id    
and pth.flag = 1    
and pttd.validated_payment = 1    
and pth.transaction_void = 0    
and b.branch_code = CASE WHEN @branch_code = '' THEN b.branch_code ELSE @branch_code END  
group by convert(varchar(10), pth.txn_date_time, 101),b.branch_desc    
order by convert(varchar(10), pth.txn_date_time, 101)
   
end
 
 

Open in new window

> THEN pttd.value ELSE pod.description END

since we are getting the sum/total, we cannot have description in it.

change it to

THEN pttd.value ELSE 0 END
let me send you a file probably then it will explain more
okay now if you see this report the current format is like the file attached.
what i want is where where in the same date there are credit cards

like VISA,VISA DELTA, MAESTRO,MAESTRO INTL, UNKNOWN CARD,MASTERCARD they all should add together and display as CREDIT CARD

AMex will come as it is

SGV as it is
CASH AND CHEQUES  will come as CASH_CHEQUES
tender-report.xls
ASKER CERTIFIED SOLUTION
Avatar of ee_rlee
ee_rlee
Flag of Philippines image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial