asked on

Excel ODBC QUERY with Declared @EOMONTH

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,
Sd1.StmnDetail,m1.matters,m1.clientid,p1.professionals,SD1.PaidDate,S1.StmnDate,P1.ProfName,m1.Matterid,m1.areaoflaw,
			(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)

Open in new window

JimFive

Your parameters for DateAdd are in the wrong order it is: DateAdd(interval, number, date), Also DateDiff requires two date parameters it looks like you want end of the previous month and beginning of the current year, so:

Set @EOM = DateAdd(day, -Day(GetDate()), GetDate())
Set @BOY = DateAdd(day, -DatePart(dy, GetDate())-1, GetDate())

Open in new window

The -1 is so that you get the beginning of the year instead of the end of last year.

yo_bee

ASKER

So standard SQL functions will not pass in Excel ODBC ?

Zberteoc

Try to run this:

Select  Row_Number() over (Partition by StmnDetail order by stmndetail) as RowNum,
Sd1.StmnDetail,m1.matters,m1.clientid,p1.professionals,SD1.PaidDate,S1.StmnDate,P1.ProfName,m1.Matterid,m1.areaoflaw,
			(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 >= (DATEADD(Year, DATEDIFF(year, 0, GETDATE()), 0)) and SD1.PaidDate <= (DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), -1)))

Open in new window

yo_bee

ASKER

This SQL Statement I posted is only a snippet of the whole statement.
This Statement consist of 6 CTE to get to the final results.
The part I posted is the first of the 6 CTE. When I change the Declare variable to have the DateAdd function within the where statement the query runs for an extremely long time.

So that is not an option.

ASKER CERTIFIED SOLUTION

Zberteoc

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

yo_bee

ASKER

That was perfect. Thank you for all your help and patience.