Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 393
  • Last Modified:

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

0
yo_bee
Asked:
yo_bee
  • 3
  • 2
1 Solution
 
JimFiveCommented:
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.
0
 
yo_beeDirector of ITAuthor Commented:
So standard SQL functions will not pass in Excel ODBC ?
0
 
ZberteocCommented:
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

0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
yo_beeDirector of ITAuthor Commented:
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.
0
 
ZberteocCommented:
Put everything into a stored procedure, with the declare and variable use, and execute that from excel.
0
 
yo_beeDirector of ITAuthor Commented:
That was perfect.  Thank you for all your help and patience.
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now