hi ... i need a bit of help here ....

ive the foll.. tables

   FIN_Transactions
    - TransactGID
    - TransactTime
    - PayModeFID
    - Amt

   FIN_PayMode
    - PayModeGID
    - PayMode
    - CC       [bit]
    - Voucher  [bit]
    - Cash     [bit]   


FID's refer to foreign keys & GID's are Pk's

Now what i want to do is prepare a monthly report that
has all the days of the month, sum of all transactions per day as per
each column of CC,Voucher & Cash & All Total


ie.. the report shud look like this

TransactTime | Cash  | CC    | Voucher| Total
-----------------------------------------------
01/12/2005   | 143.00| 124.00|    0.00|  534.00
02/12/2005   |   0.00|  12.00|   10.00|   22.00
03/12/2005   |  55.00|  65.00|   75.00|  195.00

Instead i cud only only manage to get this result:

TransactTime | Cash  | CC    | Voucher| Total
-----------------------------------------------
01/12/2005   | 143.00|       |        |  267.00
01/12/2005   |       | 124.00|        |  267.00
02/12/2005   |       |  12.00|        |   22.00
02/12/2005   |       |       |   10.00|   22.00
03/12/2005   |  55.00|       |        |  195.00
03/12/2005   |       |  65.00|        |  195.00
03/12/2005   |       |       |   75.00|  195.00

i know its simple but it dint click for me yet ...

Renjith Chembakarayil wrote:

Show quoteHide quoteThis should do it. 

select TransactTime,
case when PayMode CC = 1 then Amt else 0 end as CC,
case when PayMode Voucher = 1 then Amt else 0 end as Voucher,
case when PayMode Cash  = 1 then Amt else 0 end as Cash
from FIN_Transactions inner join FIN_PayMode on FIN_PayMode.PayModeGID
= FIN_Transactions.PayModeGID

BTW If it's a one on one relation why not keep the fields CC, Voucher,
etc  in the FIN_Transactions table?

Stijn Verrept wrote:

Woops, small mistake:

select TransactTime,
case when CC = 1 then Amt else 0 end as CC,
case when Voucher = 1 then Amt else 0 end as Voucher,
case when Cash  = 1 then Amt else 0 end as Cash
from FIN_Transactions inner join FIN_PayMode on FIN_PayMode.PayModeGID
= FIN_Transactions.PayModeGID

thnx stijin for the quick reply ...

ur soln works fine  if i want to list single transactions .. i forgot to
mention the Amt values were summed up per day for CC,Voucher & Cash .. &
Grouped per day ....

only then the result will be as required ...

I cant put CC,Voucher or Cash into GuestPayments as PayModeGID contins
different type of CC's & Vouchers 

Show quoteHide quote

Renjith Chembakarayil wrote:

Do I understand correctly that you want to have the sums of each
category per day?

Then you just need to group per day and add sum before every result
column.

Hi There,
I think this will solve the problem.

Select TDate ,Sum(Cash),Sum(CC) ,Sum(Voucher),Sum(Total) From (
Select '01/12/2005' TDate ,143.00 Cash ,0 CC ,0 Voucher ,267.00 Total
Union All
Select '01/12/2005',0,124.00,0,267.00
Union All
Select '02/12/2005',0,12.00,0,22.00
Union All
Select '02/12/2005',0,0,10.00,22.00
Union All
Select '03/12/2005',55.00,0,0,195.00
Union All
Select '03/12/2005',0,65.00,0,195.00
Union All
Select '03/12/2005',0,0,75.00,195.00
) ABC Group By TDate

With Warm regards
Jatinder Singh

On Fri, 9 Dec 2005 05:47:03 -0800, Renjith Chembakarayil wrote:

Hi Renjith,

Not sure if I understand you correctly. Try if this works:

select TransactTime,
SUM(case when CC = 1 then Amt else 0 end) as CC,
SUM(case when Voucher = 1 then Amt else 0 end) as Voucher,
SUM(case when Cash  = 1 then Amt else 0 end) as Cash
from FIN_Transactions inner join FIN_PayMode on FIN_PayMode.PayModeGID
= FIN_Transactions.PayModeGID
GROUP BY TransactTime

If that's not what you want, then please check www.aspfaq.com/5006 and
follow the advise found there to post unambiguous specs and test data.

Best, Hugo

convert flat file to native file format for bcp
Query Help: Counts & Sums
Disappearing data (happens rarely)
Year To Date?
trying to do some weird math in an update statement