Solved

TSQL ERROR- is not a recognized dateadd option.

Posted on 2010-11-23
6
782 Views
Last Modified: 2012-05-10
Hi Experts! How do I correct this error? I want to join the PlanJoin to my table however I am getting the is not a recognized dateadd option error and I am not sure why. I am not attempting to use the dateadd option, just using the month to create a relationship.
SELECT     vReportdataCurrentPrior.extendedStandardCost, vReportdataCurrentPrior.businessEntity, vReportdataCurrentPrior.product, 

                      vReportdataCurrentPrior.site, vReportdataCurrentPrior.subProduct, SUM(vReportdataCurrentPrior.OrderAmount_LastYear) AS [Prior Bookings], 

                      SUM(vReportdataCurrentPrior.InvoiceAmount_LastYear) AS [Prior Sales]]], SUM(vReportdataCurrentPrior.OrderAmount_CurrentYear) 

                      AS [Current Bookings], SUM(vReportdataCurrentPrior.InvoiceAmount_CurrentYear) AS [Current Sales], ExchangeRates.ExchangeRate, 

                      EnergySites.EnergyOrg, vReportdataCurrentPrior.qty, EnergySites.SiteNumber, (DATEDIFF(DD, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, 

                      GETDATE()) + 1, GETDATE()) AS DOUBLE PRECISION)) AS DATETIME), CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, DATEADD(PlanJoin.Month, 1, 

                      GETDATE())), DATEADD(PlanJoin.Month, 1, GETDATE())) AS DOUBLE PRECISION)) AS DATETIME)) + 1) - (DATEDIFF(DD, DATEADD(DD, 

                      6 - (DATEPART(DW, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, GETDATE()) + 1, GETDATE()) AS DOUBLE PRECISION)) AS DATETIME)) 

                      + @@DATEFIRST - 2) % 7, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, GETDATE()) + 1, GETDATE()) AS DOUBLE PRECISION)) AS DATETIME)), 

                      DATEADD(DD, - ((DATEPART(DW, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, DATEADD(PlanJoin.Month, 1, GETDATE())), 

                      DATEADD(PlanJoin.Month, 1, GETDATE())) AS DOUBLE PRECISION)) AS DATETIME)) + @@DATEFIRST - 2) % 7), CAST(FLOOR(CAST(DATEADD(DD, 

                      - DATEPART(DD, DATEADD(PlanJoin.Month, 1, GETDATE())), DATEADD(PlanJoin.Month, 1, GETDATE())) AS DOUBLE PRECISION)) AS DATETIME))) - 1) 

                      / 7 * 2 - CASE (DATEPART(DW, CAST(FLOOR(CAST(DATEADD(DD, - DATEPART(DD, GETDATE()) + 1, GETDATE()) AS DOUBLE PRECISION)) AS DATETIME)) 

                      + @@DATEFIRST - 2) % 7 + 1 WHEN 6 THEN 2 WHEN 7 THEN 1 ELSE 2 END - CASE (DATEPART(DW, CAST(FLOOR(CAST(DATEADD(DD, 

                      - DATEPART(DD, DATEADD(MONTH, 1, GETDATE())), DATEADD(MONTH, 1, GETDATE())) AS DOUBLE PRECISION)) AS DATETIME)) + @@DATEFIRST - 2) 

                      % 7 + 1 WHEN 6 THEN 1 WHEN 7 THEN 2 ELSE 0 END AS MonthWorkingDays, (DATEDIFF(DD, @start, @end) + 1) - (DATEDIFF(DD, DATEADD(DD, 

                      6 - (DATEPART(DW, @start) + @@DATEFIRST - 2) % 7, @start), DATEADD(DD, - ((DATEPART(DW, @end) + @@DATEFIRST - 2) % 7), @end)) - 1) 

                      / 7 * 2 - CASE (DATEPART(DW, @start) + @@DATEFIRST - 2) % 7 + 1 WHEN 6 THEN 2 WHEN 7 THEN 1 ELSE 2 END - CASE (DATEPART(DW, @end) 

                      + @@DATEFIRST - 2) % 7 + 1 WHEN 6 THEN 1 WHEN 7 THEN 2 ELSE 0 END AS WorkingDays, vReportdataCurrentPrior.transyear

FROM         vReportdataCurrentPrior INNER JOIN

                      ExchangeRates ON MONTH(vReportdataCurrentPrior.transDate) = ExchangeRates.XRateMonth AND 

                      vReportdataCurrentPrior.transyear = ExchangeRates.XRateYear AND vReportdataCurrentPrior.currencyCode = ExchangeRates.Currency INNER JOIN

                      EnergySites ON vReportdataCurrentPrior.site = EnergySites.SiteNumber INNER JOIN

                      PlanJoin ON vReportdataCurrentPrior.subProduct = PlanJoin.SubProduct

WHERE     (EnergySites.EnergyOrg = @energyorg)

GROUP BY vReportdataCurrentPrior.extendedStandardCost, vReportdataCurrentPrior.businessEntity, vReportdataCurrentPrior.product, 

                      vReportdataCurrentPrior.site, vReportdataCurrentPrior.subProduct, vReportdataCurrentPrior.transyear, ExchangeRates.ExchangeRate, 

                      EnergySites.EnergyOrg, vReportdataCurrentPrior.qty, EnergySites.SiteNumber, vReportdataCurrentPrior.transDate

HAVING      (vReportdataCurrentPrior.transDate BETWEEN @start AND @end OR

                      vReportdataCurrentPrior.transDate BETWEEN DATEADD(yy, - 1, @start) AND DATEADD(yy, - 1, @end)) AND 

                      (vReportdataCurrentPrior.businessEntity = 'energy')

ORDER BY vReportdataCurrentPrior.transDate DESC

Open in new window

Error.doc
0
Comment
Question by:SPLady
  • 3
  • 3
6 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34200398
You have
DATEADD(PlanJoin.Month
about 4 times in there.
The first part of DateAdd only allows things like month, m, mm, year, y,yy etc, not a column
0
 
LVL 1

Author Comment

by:SPLady
ID: 34200462
Thank you @ cyberkiwi


Umm am I missing something......I havn't used dateadd(PlanJoin.Month)..  I dont want to use dateadd for planjoin, I just want to use that column to develop a relationship.
0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 500 total points
ID: 34200569
The query is horrible to parse, so I rewrote and reformatted it..
I also moved your HAVING clause to WHERE since it will perform better.
The HAVING clause is used for AGGREGATES, you are testing against the fields in the table, which fits in plain WHERE clause.
--declare @start datetime

--declare @end datetime

--declare @energyorg int



SELECT vreportdatacurrentprior.extendedstandardcost,

       vreportdatacurrentprior.businessentity,

       vreportdatacurrentprior.product,

       vreportdatacurrentprior.site,

       vreportdatacurrentprior.subproduct,

       SUM(vreportdatacurrentprior.orderamount_lastyear)      AS [Prior Bookings],

       SUM(vreportdatacurrentprior.invoiceamount_lastyear)    AS [Prior Sales] ,

       SUM(vreportdatacurrentprior.orderamount_currentyear)   AS [Current Bookings],

       SUM(vreportdatacurrentprior.invoiceamount_currentyear) AS [Current Sales],

       exchangerates.exchangerate,

       energysites.energyorg,

       vreportdatacurrentprior.qty,

       energysites.sitenumber,

       MWD.Days AS monthworkingdays,

       WD.Days AS workingdays,

       vreportdatacurrentprior.transyear

FROM   vreportdatacurrentprior

CROSS JOIN (select count(*) days

                       from master..spt_values

                       where type='P'  -- series from 0-1024

                        and number between 0 and 60  -- max of 30 days from start

                        and month(number+GETDATE()-30) = month(GETDATE())  -- within start month

                        and (datepart(dw,GETDATE()-30 + number)+@@datefirst-2)%7+1 < 6) MWD

CROSS JOIN (select count(*) days

                       from master..spt_values

                       where type='P'  -- series from 0-1024

                        and number between 0 and @end-@start  -- range from start to end

                        and (datepart(dw,@start + number)+@@datefirst-2)%7+1 < 6) WD

INNER JOIN exchangerates ON MONTH(vreportdatacurrentprior.transdate) = exchangerates.xratemonth

    AND vreportdatacurrentprior.transyear = exchangerates.xrateyear

    AND vreportdatacurrentprior.currencycode = exchangerates.currency

INNER JOIN energysites ON vreportdatacurrentprior.site = energysites.sitenumber

INNER JOIN planjoin ON vreportdatacurrentprior.subproduct = planjoin.subproduct

WHERE  ( energysites.energyorg = @energyorg )

AND ( vreportdatacurrentprior.transdate BETWEEN @start AND @end

          OR vreportdatacurrentprior.transdate BETWEEN

             Dateadd(yy, -1, @start) AND Dateadd(yy, -1, @end) )

       AND ( vreportdatacurrentprior.businessentity = 'energy' )

GROUP  BY vreportdatacurrentprior.extendedstandardcost,

          vreportdatacurrentprior.businessentity,

          vreportdatacurrentprior.product,

          vreportdatacurrentprior.site,

          vreportdatacurrentprior.subproduct,

          vreportdatacurrentprior.transyear,

          exchangerates.exchangerate,

          energysites.energyorg,

          vreportdatacurrentprior.qty,

          energysites.sitenumber,

          vreportdatacurrentprior.transdate

ORDER  BY vreportdatacurrentprior.transdate DESC

Open in new window

0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34200577
Well if you look at your query in the question, you have 6 cases of dateadd(planjoin.month ...
I think you mean this relationship

INNER JOIN planjoin ON vreportdatacurrentprior.subproduct = planjoin.subproduct

How does planjoin.month fit in there?
You will see that the new query is easier to understand and work with.
0
 
LVL 1

Author Comment

by:SPLady
ID: 34200702
Thank you soo much @cyberkiwi how did those change lol.... I guess when I added the table... thats crazy --6 cases of dateadd(planjoin.month ...
0
 
LVL 1

Author Closing Comment

by:SPLady
ID: 34200792
Wow that is BEAUTIFUL code !
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now