Solved

Date Parameter Query (Current and Prior Year)

Posted on 2010-11-09
19
736 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
 
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
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

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

13 Experts available now in Live!

Get 1:1 Help Now