Join with Period table for Period (Trimaster /Monthly ) Summary of Transaction.

For the Monthly/Trimaster/Annual Summary of transaction report, Join with the period table is a very easy alternative.

for example:

tblPeriod

PeriodID int primary,
PeriodName nvarchar(100),
StartDate date,
EndDate Date

tblTransaction

TransactionID int,
TransactionDate Date,
Ledger int,
DrAmount float,
CrAmount float,
Narration

the Period report can be obtained by Joining These table on date between

syntax:

select
  PeridName ,
  sum(isnull(DrAmount,0)) as DrAmount,
  sum(isnull(CrAmount,0)) as CrAmount,
  sum(isnull(DrAmount,0))- sum(isnull(CrAmount,0)) as TotalAmount

from
   tblPeriod left join tblTransaction
on
   tblTransaction. TransactionDate between tblPeriod.StartDate and tblPeriod.EndDate


result will be like:

FirstTrimaster       15000    10000   5000
SecondTrimaster   14500       4500   10000