Date Parameter Query (Current and Prior Year)

Try something like this in your where clause.

1. Use nullif(@paramName,0) to turn the parameter val null if it is 0
2. Use isnull() function to return a default if the parameter name is null
3. Use function to get Jan 1st of last year for your default floor parameter
4. Use function to get Dec 31st of current year for your default ceiling parameter

ex. (hopefully i got my parenthesis correct)

WHERE     (vReportdataCurrentPrior.transdate BETWEEN isnull(nullif(@start,0),cast('1/1/' + cast(YEAR(dateadd(year, -1, getdate())) as CHAR(4)) as DATE))  AND isnull(nullif(@end,0),cast('12/31/' + cast(YEAR(getdate()) as CHAR(4)) as DATE)) AND (EnergySites.EnergyOrg = @energyorg) AND
                      (vReportdataCurrentPrior.BusinessEntity = 'energy')

Thank you soooo much @gohard.. I have a feeling It is so close! I got an error :( not sure what is wrong.. time crunch

SELECT     vReportdataCurrentPrior.transdate, vReportdataCurrentPrior.extendedstandardCost, vReportdataCurrentPrior.BusinessEntity, 
                      vReportdataCurrentPrior.product, vReportdataCurrentPrior.site, vReportdataCurrentPrior.subproduct, vReportdataCurrentPrior.transYear, 
                      vReportdataCurrentPrior.OrderAmount_LastYear, vReportdataCurrentPrior.InvoiceAmount_LastYear, 
                      vReportdataCurrentPrior.OrderAmount_CurrentYear, vReportdataCurrentPrior.InvoiceAmount_CurrentYear, ExchangeRates.ExchangeRate, 
                      (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(MONTH, 1, GETDATE())), DATEADD(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(MONTH, 1, 
                      GETDATE())), DATEADD(MONTH, 1, GETDATE())) AS DOUBLE PRECISION)) AS DATETIME)) + @@DATEFIRST - 2) % 7), CAST(FLOOR(CAST(DATEADD(DD, 
                      - DATEPART(DD, DATEADD(MONTH, 1, GETDATE())), DATEADD(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, EnergySites.EnergyOrg, 
                      vReportdataCurrentPrior.QTY
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
WHERE     (vReportdataCurrentPrior.transdate BETWEEN isnull(nullif(@start,0),cast('1/1/' + cast(YEAR(dateadd(year, -1, getdate())) as CHAR(4)) as DATE))  AND isnull(nullif(@end,0),cast('12/31/' + cast(YEAR(getdate()) as CHAR(4)) as DATE)) AND (EnergySites.EnergyOrg = @energyorg) AND 
                      (vReportdataCurrentPrior.BusinessEntity = 'energy')

Select allOpen in new window

Error.doc

Is this part of a stored procedure ?  Is @start and @end parameters?  What are they defined as date?  varchar?

thanks!

@gohord Not a stored proceedure, it is a query

They are defined as DATETIME

"When I enter date ranges prior year = 0."  

Can you clarify this?  Sorry.  Not sure if I got it.  Are you trying to enter a date value of "0" as a value for a date parameter?

Thanks

Thank you soo much for your time @gohard.. I am on a huge time crunch for these report. The report shows the sales and order trends which is what the parameters are use for (As an example- Today is the 8th business day of the month and their are 22 business days in this month = 2.75 * sales is the trend for today and the user needs to be able to enter dates so that the report can trend correctly) However that parameter is causing a problem with the prior year data.  The report also compares the current and previous year’s sales and orders so, when the dates are chosen last year or "previous year"  is "negated" thus the zeros. I don’t know how to write code to handle that.

So there are two date parameters on your report that represent a start and end date for analysis correct?  If I were to select a start date and end date within the current year, I'd expect to retrieve all results from your table that fall within that current given date range?  

Is this correct so far?

No this is where I'm still lost..  We now run the report with a start date in 6/7/2009 for example.  What data do we need to pull from your table?  Are we just ignoring the data parameter and pulling everything (all history?  Are we pulling all dates starting in 1/1/2009?

thanks and sorry i'm still missing the objective on this one :|

correct... for the second half  suppose  I select between 10/01/2010  and 10/31/2010 .. I need pull that time period for october 2010 and 2009 for comparison the code that I am using for that is attached it pulls current order and sales and previous year order and sales. I saved it as a veiw and created the previous query above.

SELECT     *
FROM         (SELECT     QTY, transdate, extendedstandardCost, currencyCode, BusinessEntity, product, site, lineValue AS 'OrderAmount', Cast(0 AS NUMERIC(30, 
                                              2)) AS 'InvoiceAmount', subproduct, transYear, CASE WHEN TransYear = Year(getdate()) - 1 THEN LineValue ELSE Cast(0 AS Numeric(30, 2)) 
                                              END AS 'OrderAmount_LastYear', Cast(0 AS Numeric(30, 2)) 'InvoiceAmount_LastYear', CASE WHEN TransYear = Year(getdate()) 
                                              THEN LineValue ELSE Cast(0 AS Numeric(30, 2)) END AS 'OrderAmount_CurrentYear', Cast(0 AS Numeric(30, 2)) 
                                              'InvoiceAmount_CurrentYear'
                       FROM          vReportData
                       WHERE      (recType = 'order') AND (businessEntity = 'Energy')
                       UNION
                       SELECT     QTY, transdate, extendedstandardCost, currencyCode, BusinessEntity, product, site, 0 AS 'OrderAmount', lineValue AS 'InvoiceAmount', 
                                             subproduct, transYear, Cast(0 AS Numeric(30, 2)) 'OrderAmount_LastYear', CASE WHEN TransYear = Year(getdate()) 
                                             - 1 THEN LineValue ELSE Cast(0 AS Numeric(30, 2)) END AS 'InvoiceAmount_LastYear', Cast(0 AS Numeric(30, 2)) 
                                             'OrderAmount_CurrentYear', CASE WHEN TransYear = Year(getdate()) THEN LineValue ELSE Cast(0 AS Numeric(30, 2)) 
                                             END AS 'InvoiceAmount_CurrentYear'
                       FROM         vReportData AS ReportData1
                       WHERE     (recType = 'invoice') AND (businessEntity = 'Energy')) A,
                          (SELECT     20 + COUNT(*) WorkDayCount
                            FROM          (SELECT     DATEADD(MONTH, DATEDIFF(MONTH, 0, getDate()), 28) AS theDate
                                                    UNION
                                                    SELECT     DATEADD(MONTH, DATEDIFF(MONTH, 0, getDate()), 29)
                                                    UNION
                                                    SELECT     DATEADD(MONTH, DATEDIFF(MONTH, 0, getDate()), 30)) AS d
                            WHERE      DATEPART(DAY, theDate) > 28 AND DATEDIFF(DAY, 0, theDate) % 7 < 5) B

Select allOpen in new window

Can you provide with the table structure for the following tables:

vReportdataCurrentPrior
ExchangeRates
EnergySites

(If possible generate script with the help of Script Wizard)

And some dummy data (not ur actual data).

Also if u can provide ur expected output template in excel.

This will help in quick resolution to ur problem.

reportdata- I am using this as a view

recordID      uniqueidentifier      Unchecked
recType      varchar(50)      Checked
site      int      Checked
salesOrder      varchar(50)      Checked
salesOrderLine      varchar(50)      Checked
invoice      varchar(50)      Checked
invoiceLine      varchar(50)      Checked
customerNo      varchar(50)      Checked
customerSuffix      varchar(50)      Checked
partNumber      varchar(50)      Checked
productCode      varchar(100)      Checked
extendedStandardCost      float      Checked
qty      float      Checked
lineValue      float      Checked
currencyCode      varchar(50)      Checked
transDate      datetime      Checked
salesAgent      varchar(50)      Checked
orderType      varchar(50)      Checked
endUserCountry      varchar(50)      Checked
sourcesystem      varchar(50)      Checked
updateState      int      Checked

exchange rates

XRateYear      int      Unchecked
XRateMonth                             int      Unchecked
Currency                           nvarchar(10)      Unchecked
ExchangeRate      float      Checked

EnergySite
BusEnitityID                             int      Unchecked
SiteNumber                            int      Unchecked
SiteName                            varchar(50)      Unchecked
EnergyOrg                           varchar(50)      Unchecked
Energy_MS_SM      varchar(50)      Unchecked
CurrencyCode      varchar(50)      Unchecked
            Unchecked




TestData.xls

I wasn't able to follow prev example.  I tried to generate an example though of how you might do it.  Note alot of the code in the attachement is just used to generate sample data so you can just run this in SSMS.

Here is the main part of the code...

select            SampleData.Date                                    as      Date_Current_Year
            ,      SampleData.MyValue                              as      Amount_Current_Year
            ,      SampleDataPreviousYear.Date                  as      Date_Last_Year
            ,      SampleDataPreviousYear.myValue            as      Amount_Previous_Year
from      SampleData

left outer join
(
      --get data from previous year based on start and end dates
      select      *
      from      SampleData
      where      SampleData.Date between dateadd(year, -1, @startDate) and dateadd(year, -1, @endDate)
      
) SampleDataPreviousYear on
      
      --sneaky join on date so current year matches to previous year
      SampleData.Date = DATEADD(year, 1, SampleDataPreviousYear.Date)
      

            --get data from current year based on start and end dates
where      SampleData.Date between @startDate and @endDate


Your basically joining your current year month data and previous year month data based on date.  I hope this makes sense.

I wasn't able to follow prev example.  I tried to generate an example though of how you might do it.  Note alot of the code in the attachement is just used to generate sample data so you can just run this in SSMS.

Here is the main part of the code...

select            SampleData.Date                                    as      Date_Current_Year
            ,      SampleData.MyValue                              as      Amount_Current_Year
            ,      SampleDataPreviousYear.Date                  as      Date_Last_Year
            ,      SampleDataPreviousYear.myValue            as      Amount_Previous_Year
from      SampleData

left outer join
(
      --get data from previous year based on start and end dates
      select      *
      from      SampleData
      where      SampleData.Date between dateadd(year, -1, @startDate) and dateadd(year, -1, @endDate)
      
) SampleDataPreviousYear on
      
      --sneaky join on date so current year matches to previous year
      SampleData.Date = DATEADD(year, 1, SampleDataPreviousYear.Date)
      

            --get data from current year based on start and end dates
where      SampleData.Date between @startDate and @endDate


Your basically joining your current year month data and previous year month data based on date.  I hope this makes sense.
sql-stuff.sql

@gohord  first of all Thank you!.....I am confused..lol I understand the logic but, dont know how to translate... getting error from sql-stuff
Error.doc

Can you try running the sample in SQL Server Management Studio?

I have Visual Studio 2005 and BI Development Studio 2005

Just Awesome.. Thank you sooo much.. I might have another question but this is the solution for this question :)

Do I need to replace date sequence with  a table? Do I replace DataSample with vReportdataCurrentPrior and what table do I replace TestData with (which table do I join DataSample or vReportdataCurrentPrior with)?