pls help me query base crystal report .... when i run below query in SQl i get in problem like conversion failed when converting date and/or time from character string in sql server 2008 useing/via crystal report Add command ..........................Thanks in advance for your co-operation.................
SELECT
ACCOUNTNAME,
SUM(TOTALDEPOSITEDAMOUNT) TOTALDEPOSITEDAMOUNT,
SUM(TOTALLOANPAIDAMOUNT) TOTALLOANPAIDAMOUNT,
SUM(TOTALPROFITPAIDAMOUNT) TOTALPROFITPAIDAMOUNT,
SUM( TOTALEXPENSESAMOUNT) TOTALEXPENSESAMOUNT,
SUM( TOTALOTHERSAMOUNT) TOTALOTHERSAMOUNT,
SUM(TOTALADMISSIONAMOUNT) TOTALADMISSIONAMOUNT,
SUM(TOTALSERVICECHARGE) TOTALSERVICECHARGE,
SUM(TOTALRISKABLESERVICECHARGE) TOTALRISKABLESERVICECHARGE,
SUM(TOTALSERVICECHARGE) TOTALSERVICECHARGEs,
SUM(TOTALDONEAMOUNT) TOTALDONEAMOUNT
FROM
(SELECT
Deposit.AcountName AS ACCOUNTNAME,
SUM(Deposit.AmountDeposit) AS TOTALDEPOSITEDAMOUNT,
0 AS TOTALLOANPAIDAMOUNT,
0 AS TOTALPROFITPAIDAMOUNT,
0 AS TOTALEXPENSESAMOUNT,
0 AS TOTALOTHERSAMOUNT,
0 AS TOTALADMISSIONAMOUNT,
0 AS TOTALSERVICECHARGE,
0 AS TOTALRISKABLESERVICECHARGE,
0 AS TOTALSERVICECHARGEs,
0 AS TOTALDONEAMOUNT,
Deposit.Ddate AS TRANDATE
FROM Deposit GROUP BY Deposit.AcountName, Deposit.Ddate
UNION ALL
SELECT
LoanPaid.AcountName AS ACCOUNTNAME,
0 AS TOTALDEPOSITEDAMOUNT,
SUM(LoanPaid.LPA)AS TOTALLOANPAIDAMOUNT,
SUM(LoanPaid.PPA)AS TOTALPROFITPAIDAMOUNT,
0 AS TOTALEXPENSESAMOUNT,
0 AS TOTALOTHERSAMOUNT,
0 AS TOTALADMISSIONAMOUNT,
0 AS TOTALSERVICECHARGE,
0 AS TOTALRISKABLESERVICECHARGE,
0 AS TOTALSERVICECHARGEs,
0 AS TOTALDONEAMOUNT,
LoanPaid.Ddate AS TRANDATE
FROM LoanPaid GROUP BY LoanPaid.AcountName, LoanPaid.Ddate
UNION ALL
SELECT
'NA' AS ACCOUNTNAME,
0 AS TOTALDEPOSITEDAMOUNT,
0 AS TOTALLOANPAIDAMOUNT,
0 AS TOTALPROFITPAIDAMOUNT,
SUM(Expenses.Amount)AS TOTALEXPENSESAMOUNT,
0 AS TOTALOTHERSAMOUNT,
0 AS TOTALADMISSIONAMOUNT,
0 AS TOTALSERVICECHARGE,
0 AS TOTALRISKABLESERVICECHARGE,
0 AS TOTALSERVICECHARGEs,
0 AS TOTALDONEAMOUNT,
Expenses.Ddate AS TRANDATE
FROM Expenses GROUP BY Expenses.Ddate
UNION ALL
SELECT
'NA' AS ACCOUNTNAME,
0 AS TOTALDEPOSITEDAMOUNT,
0 AS TOTALLOANPAIDAMOUNT,
0 AS TOTALPROFITPAIDAMOUNT,
0 AS TOTALEXPENSESAMOUNT,
SUM(Others.Amount) AS TOTALOTHERSAMOUNT,
0 AS TOTALADMISSIONAMOUNT,
0 AS TOTALSERVICECHARGE,
0 AS TOTALRISKABLESERVICECHARGE,
0 AS TOTALSERVICECHARGEs,
0 AS TOTALDONEAMOUNT,
Others.Ddate AS TRANDATE FROM Others
GROUP BY Others.Ddate
UNION ALL
SELECT
AdminFee.AccountName AS ACCOUNTNAME,
0 AS TOTALDEPOSITEDAMOUNT,
0 AS TOTALLOANPAIDAMOUNT,
0 AS TOTALPROFITPAIDAMOUNT,
0 AS TOTALEXPENSESAMOUNT,
0 AS TOTALOTHERSAMOUNT,
SUM(AdminFee.AdminFee)AS TOTALADMISSIONAMOUNT,
0 AS TOTALSERVICECHARGE,
0 AS TOTALRISKABLESERVICECHARGE,
0 AS TOTALSERVICECHARGEs,
0 AS TOTALDONEAMOUNT,
AdminFee.AdminDate AS TRANDATE
FROM AdminFee GROUP BY AdminFee.AccountName, AdminFee.AdminDate
UNION ALL
SELECT
LPB.AcountName AS ACCOUNTNAME,
0 AS TOTALDEPOSITEDAMOUNT,
0 AS TOTALLOANPAIDAMOUNT,
0 AS TOTALPROFITPAIDAMOUNT,
0 AS TOTALEXPENSESAMOUNT,
0 AS TOTALOTHERSAMOUNT,
0 AS TOTALADMISSIONAMOUNT,
SUM(LPB.ServiceCharge)AS TOTALSERVICECHARGE,
SUM (LPB.RiskAbleServiceCharge)AS TOTALRISKABLESERVICECHARGE,
0 AS TOTALSERVICECHARGEs,
0 AS TOTALDONEAMOUNT,
LPB.Ddate AS TRANDATE
FROM LPB GROUP BY LPB.AcountName, LPB.Ddate
UNION ALL
SELECT
OPB.AcountName AS ACCOUNTNAME,
0 AS TOTALDEPOSITEDAMOUNT,
0 AS TOTALLOANPAIDAMOUNT,
0 AS TOTALPROFITPAIDAMOUNT,
0 AS TOTALEXPENSESAMOUNT,
0 AS TOTALOTHERSAMOUNT,
0 AS TOTALADMISSIONAMOUNT,
0 AS TOTALSERVICECHARGE,
0 AS TOTALRISKABLESERVICECHARGE,
SUM(OPB.ServiceCharge)AS TOTALSERVICECHARGEs,
0 AS TOTALDONEAMOUNT,
OPB.Ddate AS TRANDATE
FROM OPB GROUP BY OPB.AcountName, OPB.Ddate
UNION ALL
SELECT
Loan.AcountName AS ACCOUNTNAME,
0 AS TOTALDEPOSITEDAMOUNT,
0 AS TOTALLOANPAIDAMOUNT,
0 AS TOTALPROFITPAIDAMOUNT,
0 AS TOTALEXPENSESAMOUNT,
0 AS TOTALOTHERSAMOUNT,
0 AS TOTALADMISSIONAMOUNT,
0 AS TOTALSERVICECHARGE,
0 AS TOTALRISKABLESERVICECHARGE,
0 AS TOTALSERVICECHARGEs,
SUM(Loan.LoanAmount)AS TOTALDONEAMOUNT,
Loan.Ddate AS TRANDATE
FROM Loan GROUP BY Loan.AcountName, Loan.Ddate) LONGQUERY
WHERE TRANDATE BETWEEN convert(smalldatetime,'" & dtpBegDate.Text & "' ,103) AND convert(smalldatetime,'" & dtpEndDate.text &"',103)
GROUP BY ACCOUNTNAME