Declare @sqlstr varchar(4000)
Declare @FromDate varchar(4000),
@ToDate varchar(4000),
@FranchiseCode varchar(20)
set @sqlstr=''
set @FromDate='03/02/2013'
set @ToDate='06/03/2013'
set @FranchiseCode='HY'
set @sqlstr='select row_number() over (order by (SELECT (1))) AS SNo,
convert(varchar(15),ReceiptDate,103) as ReceiptDate,
ISNULL((select SUM(CurrentPaid) from FeeReceipt where PaymentMode=''cash''
and ReceiptDate between CONVERT(VARCHAR(15),'''+@FromDate+''',103) AND CONVERT(VARCHAR(15),'''+@ToDate+''',103)
and ReceiptDate=FR.ReceiptDate),0) as Cash,
ISNULL((select SUM(CurrentPaid) from FeeReceipt where PaymentMode=''Cheque''
and ReceiptDate between CONVERT(VARCHAR(15),'''+@FromDate+''',103) AND CONVERT(VARCHAR(15),'''+@ToDate+''',103)
and ReceiptDate=FR.ReceiptDate),0) as Cheque ,
ISNULL((select SUM(CurrentPaid) from FeeReceipt where PaymentMode=''CREDIT CARD''
and ReceiptDate between CONVERT(VARCHAR(15),'''+@FromDate+''',103) AND CONVERT(VARCHAR(15),'''+@ToDate+''',103)
and ReceiptDate=FR.ReceiptDate),0) as ''Credit_Card'',
ISNULL((select SUM(Fine) from FeeReceipt where ReceiptDate between CONVERT(VARCHAR(15),'''+@FromDate+''',103)
AND CONVERT(VARCHAR(15),'''+@ToDate+''',103)
and ReceiptDate=FR.ReceiptDate),0) as Fine ,
ISNULL((select SUM(CurrentPaid) from FeeReceipt where PaymentMode=''cash''
and ReceiptDate between CONVERT(VARCHAR(15),'''+@FromDate+''',103) AND CONVERT(VARCHAR(15),'''+@ToDate+''',103)
and ReceiptDate=FR.ReceiptDate),0)+ISNULL((select SUM(CurrentPaid) from FeeReceipt where PaymentMode=''Cheque''
and ReceiptDate between CONVERT(VARCHAR(15),'''+@FromDate+''',103) AND CONVERT(VARCHAR(15),'''+@ToDate+''',103)
and ReceiptDate=FR.ReceiptDate),0)+ISNULL((select SUM(CurrentPaid) from FeeReceipt where PaymentMode=''CREDIT CARD''
and ReceiptDate between CONVERT(VARCHAR(15),'''+@FromDate+''',103) AND CONVERT(VARCHAR(15),'''+@ToDate+''',103)
and ReceiptDate=FR.ReceiptDate),0)
as ''Total_Collection'' ,
ISNULL(CR.Amount,0) AS ''Chq_Return''
from FeeReceipt FR
left join
(select FranchiseCode,vDATE,ISNULL(SUM(AMOUNT),0) as "Amount" FROM CHEQUERETURN
WHERE vDATE between CONVERT(VARCHAR(15),'''+@FromDate+''',103) AND CONVERT(VARCHAR(15),'''+@ToDate+''',103)
group by vDATE,FranchiseCode) CR
ON FR.ReceiptDate=CR.vDATE
AND FR.FranchiseCode=CR.FranchiseCode
WHERE
FR.ReceiptDate between CONVERT(VARCHAR(15),'''+@FromDate+''',103) AND CONVERT(VARCHAR(15),'''+@ToDate+''',103)
AND FR.FranchiseCode='''+@FranchiseCode+'''group by FR.ReceiptDate,CR.Amount,CR.FranchiseCode'
exec(@sqlstr)
Declare @FromDate varchar(4000),
@ToDate varchar(4000),
@FranchiseCode varchar(20)
set @sqlstr=''
set @FromDate='03/02/2013'
set @ToDate='06/03/2013'
set @FranchiseCode='HY'
set @sqlstr='select row_number() over (order by (SELECT (1))) AS SNo,
convert(varchar(15),ReceiptDate,103) as ReceiptDate,
ISNULL((select SUM(CurrentPaid) from FeeReceipt where PaymentMode=''cash''
and ReceiptDate between CONVERT(VARCHAR(15),'''+@FromDate+''',103) AND CONVERT(VARCHAR(15),'''+@ToDate+''',103)
and ReceiptDate=FR.ReceiptDate),0) as Cash,
ISNULL((select SUM(CurrentPaid) from FeeReceipt where PaymentMode=''Cheque''
and ReceiptDate between CONVERT(VARCHAR(15),'''+@FromDate+''',103) AND CONVERT(VARCHAR(15),'''+@ToDate+''',103)
and ReceiptDate=FR.ReceiptDate),0) as Cheque ,
ISNULL((select SUM(CurrentPaid) from FeeReceipt where PaymentMode=''CREDIT CARD''
and ReceiptDate between CONVERT(VARCHAR(15),'''+@FromDate+''',103) AND CONVERT(VARCHAR(15),'''+@ToDate+''',103)
and ReceiptDate=FR.ReceiptDate),0) as ''Credit_Card'',
ISNULL((select SUM(Fine) from FeeReceipt where ReceiptDate between CONVERT(VARCHAR(15),'''+@FromDate+''',103)
AND CONVERT(VARCHAR(15),'''+@ToDate+''',103)
and ReceiptDate=FR.ReceiptDate),0) as Fine ,
ISNULL((select SUM(CurrentPaid) from FeeReceipt where PaymentMode=''cash''
and ReceiptDate between CONVERT(VARCHAR(15),'''+@FromDate+''',103) AND CONVERT(VARCHAR(15),'''+@ToDate+''',103)
and ReceiptDate=FR.ReceiptDate),0)+ISNULL((select SUM(CurrentPaid) from FeeReceipt where PaymentMode=''Cheque''
and ReceiptDate between CONVERT(VARCHAR(15),'''+@FromDate+''',103) AND CONVERT(VARCHAR(15),'''+@ToDate+''',103)
and ReceiptDate=FR.ReceiptDate),0)+ISNULL((select SUM(CurrentPaid) from FeeReceipt where PaymentMode=''CREDIT CARD''
and ReceiptDate between CONVERT(VARCHAR(15),'''+@FromDate+''',103) AND CONVERT(VARCHAR(15),'''+@ToDate+''',103)
and ReceiptDate=FR.ReceiptDate),0)
as ''Total_Collection'' ,
ISNULL(CR.Amount,0) AS ''Chq_Return''
from FeeReceipt FR
left join
(select FranchiseCode,vDATE,ISNULL(SUM(AMOUNT),0) as "Amount" FROM CHEQUERETURN
WHERE vDATE between CONVERT(VARCHAR(15),'''+@FromDate+''',103) AND CONVERT(VARCHAR(15),'''+@ToDate+''',103)
group by vDATE,FranchiseCode) CR
ON FR.ReceiptDate=CR.vDATE
AND FR.FranchiseCode=CR.FranchiseCode
WHERE
FR.ReceiptDate between CONVERT(VARCHAR(15),'''+@FromDate+''',103) AND CONVERT(VARCHAR(15),'''+@ToDate+''',103)
AND FR.FranchiseCode='''+@FranchiseCode+'''group by FR.ReceiptDate,CR.Amount,CR.FranchiseCode'
exec(@sqlstr)
No comments:
Post a Comment