Tuesday, April 24, 2012

SQL report error on where help please

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