I have a query that runs fine when I set the dates to static value it works fine, but when I set values to a declared variable it does not work. This query is run within Excel via an ODBC.
Is this even possible?
Here is my sql statement.
Declare @eom as Datetime
Declare @boy as DateTime
set @eom = (DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -1))
Set @boy = (DATEADD(Year, DATEDIFF(year, 0, GETDATE()), 0))
Select Row_Number() over (Partition by StmnDetail order by stmndetail) as RowNum,
(CASE WHEN LedgerType = 'S'
THEN Paid - WO
ELSE - (Billed - Paid)
END ) AS [Cash Received]--, [Firm Origination]
FROM dbo.StmnDetail AS SD1 LEFT OUTER JOIN
dbo.Professionals AS P1 ON SD1.Professionals = P1.Professionals INNER JOIN
dbo.StmnLedger AS S1 ON SD1.StmnLedger = S1.StmnLedger INNER JOIN
dbo.Matters AS M1 ON S1.matters = M1.Matters LEFT OUTER JOIN
MatterTypes as MT1 ON M1.MatterType = MT1.MatterTypesDesc
--left outer join CV_temp_Tbl CV1 on cv1.matters = m1.matters
--left outer join MattersProfessionals as MP1 on m1.matters = mp1.matters
--Inner join Professionals as p2 on MP1.Professionals = p2.professionals
WHERE (SD1.CompType = 'F') AND (NOT (M1.MatterType LIKE '%Admin%')) AND (SD1.PaidDate >= @boy and SD1.PaidDate <= @eom)