Solved

Date Parameter Query (Current and Prior Year)

Posted on 2010-11-09
19
738 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

810 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