hi everyone,, i need to know how can i remove or avoid displaying double on my details section in my crystal report. im using a stored procedure for my crystal report.this is my code for my report:
''''''
SELECT transactiondetails.foilionum
,actualguesting.numhours
,actualguesting.folionumber
,transactionType =
case transactiondetails.transactionType
when 'CREDIT CARD' then 'CC'
when 'DEBIT CARD' then 'DC'
when 'CASH' then 'CASH'
when 'GIFT CHECK' then 'GC'
when 'CHECK' then 'CHECK'
when 'MULTIPLE PAYMENT' then 'MP'
when 'FOC' then 'FOC'
when 'A/R SEND BILL' then 'SEND BILL'
when 'A/R CHARGE TO' then 'CHARGE TO'
when 'HOUSE USE' then 'HOUSE USE'
when 'DD' then 'DD'
when 'None' then 'None'
end
,actualguesting.roomnum
,actualguesting.roomrate
,actualguesting.roomtype
,disctype=
case actualguesting.disctype
when 'EUROMILES' then 'EURO'
when 'Cash Discount' then 'CD'
when 'Senior Citizen' then 'SCD'
when 'PPC' then 'PPC'
end
,actualguesting.discountedby
,actualguesting.checkinDate
,actualguesting.datecheckout
,actualguesting.extrapax
,actualguesting.company
,actualguesting.AmountRefunded
,actualguesting.earlyCheckinAmount
,roomtype =
case actualguesting.roomtype
when 'Suite 1' then 'Suite 1'
when 'Suite 2' then 'Suite 2'
when 'Premium' then 'Premium'
when 'Deluxe' then 'Deluxe'
END
,sum(transactiondetails.discount) as [discount]
,industry=
case actualguesting.industry
when 'CORPORATE ACCOUNT' then 'CORP'
when 'TRAVEL AGENCY' then 'TA'
when 'INDIVIDUAL WALK IN' then 'WI'
when 'ONLINE BOOKING' then 'OB'
when 'GOVERNMENT' then 'GOVT'
when 'OTA' then 'OTA'
end
,actualguesting.NightLeft
,actualguesting.BalanceDeposit
,actualguesting.remainingbal
,transactiondetails.username
,transactiondetails.supervisor
,transactiondetails.checkInStat
,transactiondetails.checkOutStat
,transactiondetails.avail
,(SELECT SUM(total) FROM foodordertbl where folioid = transactiondetails.foilionum and date BETWEEN @dtStartDate AND @dtEndDate) as [foodOrdered]
,(select count(foilionum) from actualguesting where checkinDate between @dtStartDate and @dtEndDate) as PickUp
,(select count(avail) from transactiondetails where transactiondatetime between @dtStartDate and @dtEndDate and avail='*') as countAvail
,(select sum(roomamount) from transactiondetails where transactiondatetime between @dtStartDate and @dtEndDate and transactiondetails.avail='*') as sumRoomAvailed
,(select sum(breakfast) from transactiondetails where transactiondatetime between @dtStartDate and @dtEndDate and transactiondetails.avail='*') as sumBRFAvailed
,(select sum(amount) from SafeKeeping where dateTime between @dtStartDate AND @dtEndDate) as SafeKeepAmount
,sum(transactiondetails.cashReceived) as CashOnHand
,sum(transactiondetails.breakfast)as [Breakfast]
,sum(transactiondetails.roomamount) as roomSales
,SUM(transactiondetails.overtime) AS [total_overtime]
,sum(transactiondetails.misc) as [total_misc]
,sum(transactiondetails.extend) as [total_extend]
,sum(transactiondetails.roomamount)+sum(transactiondetails.breakfast) as [total_room]
,sum(transactiondetails.totalamount) +SUM(transactiondetails.overtime) + sum(transactiondetails.misc) +sum(transactiondetails.breakfast)+ sum(transactiondetails.roomamount) AS [Grand_total]
--SUM(transactiondetails.overtime) + + sum(transactiondetails.extend)+SUM(transactiondetails.overtime)
FROM actualguesting FULL OUTER JOIN
transactiondetails ON actualguesting.foilionum = transactiondetails.foilionum-- cross join ListOfCheckout
WHERE (transactiondetails.transactiondatetime BETWEEN @dtStartDate AND @dtEndDate)
GROUP BY actualguesting.roomnum,
actualguesting.roomtype,
actualguesting.numhours,
actualguesting.industry,
actualguesting.remainingbal,
actualguesting.disctype,
actualguesting.roomrate,
actualguesting.extrapax,
actualguesting.checkinDate,
actualguesting.datecheckout,
actualguesting.company,
actualguesting.discountedby,
transactiondetails.checkInStat,
transactiondetails.checkOutStat,
transactiondetails.transactionType,
transactiondetails.username,
transactiondetails.supervisor,
actualguesting.AmountRefunded,
actualguesting.folionumber,
actualguesting.NightLeft,
actualguesting.BalanceDeposit,
actualguesting.earlyCheckinAmount,
transactiondetails.foilionum,
transactiondetails.avail
order by actualguesting.roomnum
''''''''''
i tried using distinct on my foilionum but nothing happens.
any help would be appreciated.
thank you.