I have a report to check for duplicate "vouchers" but have got a little lost and getting an error. Have a look!
Declare @StartDate as Datetime
Declare @EndDate as Datetime
set @StartDate = GetDate()-14
set @EndDate = GetDate()
SELECT *
FROM (SELECT dupl.TerminalID, dupl.Location, dupl.Country, dupl.VoucherNumber,
MIN(realdupl.vsTransDate)
AS FirstVoucher, MAX(realdupl.vsTransDate) AS LastVoucher
FROM
(SELECT vs.vsTermID AS TerminalID, cfg.cfgLocation AS Location,
cfg.cfgLocationCountry AS Country, vs.vsVoucherNum AS VoucherNumber, COUNT(*) as
NoOfVouchers
FROM
(SELECT vsTermID, vsVoucherNum, vsTransDate, vsReversalIndicator FROM
ProBatchHostDbSec.dbo.vatVouchers (NOLOCK)
)
AS vs
INNER JOIN TermConfig AS cfg WITH (NOLOCK)
ON vs.vsTermID = cfg.cfgTerminalID
WHERE vs.vsReversalIndicator = 0
AND cfg.cfgProductionTerminal = 'Y'
AND cfg.cfgLocationCountry = 'SG'
GROUP BY vs.vsTermID, cfg.cfgLocation,
cfg.cfgLocationCountry, vs.vsVoucherNum
HAVING COUNT(*) > 1
) AS dupl
INNER JOIN
(SELECT vsTermID, vsVoucherNum, vsTransDate, vsReversalIndicator FROM
ProBatchHostDbSec.dbo.vatVouchers
AND dupl.VoucherNumber = realdupl.vsVoucherNum
GROUP BY dupl.TerminalID, dupl.Location, dupl.Country, dupl.VoucherNumber
) AS temp
WHERE temp.FirstVoucher != temp.LastVoucher
AND temp.LastVoucher BETWEEN @StartDate AND @EndDate
ORDER BY temp.TerminalID, temp.LastVoucherdbo.vatVouchers --WITH (NOLOCK)
GROUP BY dupl.TerminalID, dupl.Location, dupl.Country, dupl.VoucherNumber
) AS temp
When I run this I get an error "Incorrect syntax near the keyword 'WHERE'."
Wheres the incorrect syntax??
Thanks in advance!
No comments:
Post a Comment