declare @startDate datetime
declare @endDate datetime
set @startDate = '2009-01-01 00:00:00.000'
set @endDate = '2009-02-01 00:00:00.000'

select
n.name,
n.namecode,
dh.openitemno,
convert(varchar, dh.transdate, 103) as t_date,
dh.localvalue,
isnull(dh.foreigntranvalue, 0) as foreigntranvalue,
c.irn,
cn.referenceno, row_number() over (partition by n.namecode order by n.namecode asc) as row
from debtorhistory dh
inner join name n on n.nameno = dh.acctdebtorno
inner join (
select distinct(caseid), reftransno
from workhistory
) as ci
on ci.reftransno = dh.itemtransno
inner join cases c on c.caseid = ci.caseid
inner join casename cn on cn.caseid = c.caseid and cn.nametype = 'D' and cn.sequence = 0
where dh.transtype in (510, 511, 512, 515, 516)
and dh.transdate between @startDate and @endDate
and row = 1
group by dh.openitemno, n.name, n.namecode, dh.transdate, dh.localvalue, dh.foreigntranvalue, dh.referencetext, c.irn, cn.referenceno