Solved

Date Parameter Query (Current and Prior Year)

Posted on 2010-11-09
19
739 Views
Last Modified: 2012-05-10
Hi Experts! How would I rewrite this query so that the prior year will be pulled with the current year. When I enter date ranges prior year = 0.
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 @start AND @end) AND (EnergySites.EnergyOrg = @energyorg) AND 
                      (vReportdataCurrentPrior.BusinessEntity = 'energy')

Open in new window

0
Comment
Question by:SPLady
  • 10
  • 8
19 Comments
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 34097520
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')
0
 
LVL 1

Author Comment

by:SPLady
ID: 34097613
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')

Open in new window

Error.doc
0
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 34098164
Is this part of a stored procedure ?  Is @start and @end parameters?  What are they defined as date?  varchar?

thanks!
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 1

Author Comment

by:SPLady
ID: 34101707
@gohord Not a stored proceedure, it is a query
0
 
LVL 1

Author Comment

by:SPLady
ID: 34101750
They are defined as DATETIME
0
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 34102053
"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
0
 
LVL 1

Author Comment

by:SPLady
ID: 34102225
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.
0
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 34102397
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 :|

0
 
LVL 1

Author Comment

by:SPLady
ID: 34102560
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

Open in new window

0
 
LVL 3

Expert Comment

by:expert_dharam
ID: 34102702
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.
0
 
LVL 1

Author Comment

by:SPLady
ID: 34102887
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
0
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 34103040
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.
0
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 34103041
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
0
 
LVL 1

Author Comment

by:SPLady
ID: 34103490
@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
0
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 34103549
Can you try running the sample in SQL Server Management Studio?
0
 
LVL 1

Author Comment

by:SPLady
ID: 34103621
I have Visual Studio 2005 and BI Development Studio 2005
0
 
LVL 14

Accepted Solution

by:
Christopher Gordon earned 500 total points
ID: 34103719
I wonder if I used SQL 2008 only syntax.  Try this.
sql-stuff.sql
0
 
LVL 1

Author Closing Comment

by:SPLady
ID: 34103870
Just Awesome.. Thank you sooo much.. I might have another question but this is the solution for this question :)
0
 
LVL 1

Author Comment

by:SPLady
ID: 34105141
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)?
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Need some help wiht :CAST AS Double 11 47
Query - which index being used? 2 60
Get row count of current SQL query 8 58
Two tables - Sum of values - What is the difference 31 54
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …

791 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