Solved

SP is broken :(        SP is pulling data from 2008 - Shoud be current and previous year- Max Points

Posted on 2010-11-19
11
361 Views
Last Modified: 2012-05-10
Hi Experts!

The query portion works to return this and previous year data however, as SP it is returning records from 2008.  So I am assuming there is something wrong with the Declaration and or Set portion but, I am sorta lost
What I need is , when the SP is fired it will return a month end report on the 5th of every month with the current and prior year data.
ALTER PROCEDURE EnergyProductLine
    @energyOrg nvarchar(1000)
    
    

AS
BEGIN
SET NOCOUNT OFF

DECLARE @Start datetime
DECLARE @end datetime
DECLARE @date datetime
--DECLARE @energyOrg nvarchar


SET @date = dateadd (mm, -1, getdate())
SET @start= dateadd(m, datediff(m, 0, @date)-15, 0)
SET @end = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@date),0))

select distinct 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 OR
                      vReportdataCurrentPrior.transDate BETWEEN DATEADD(yy, - 1, @start) AND DATEADD(yy, - 1, @end)) AND (EnergySites.EnergyOrg = @energyorg) AND
                       (vReportdataCurrentPrior.BusinessEntity = 'energy')


END

Open in new window

0
Comment
Question by:SPLady
  • 5
  • 5
11 Comments
 
LVL 1

Expert Comment

by:MartinChadderton
ID: 34173662
The Initial sets give:
Date
2010-10-19 15:30:49.890      
Start
2009-07-01 00:00:00.000
End
2010-09-30 23:59:59.000

So the line in the where clause of:
BETWEEN DATEADD(yy, - 1, @start) AND DATEADD(yy, - 1, @end))
will deduct a year off the start giving 2008.

i.e. in this example 2008-07-01 00:00:00.000

If you want to go back only 1 year then only apply the deduction once i.e. the where clause could be:

between @start and @end
0
 
LVL 1

Author Comment

by:SPLady
ID: 34173984
Thanky you @MartinChadderton what about the month part, why is it starting at July (7)?
0
 
LVL 34

Expert Comment

by:James0628
ID: 34178785
July comes from the -15 in the SET @start line.  It takes the difference in months between date 0 (01/01/1900) and @date, subtracts 15 from that, and adds the result back to date 0.  In effect, that subtracts 15 months from @date, so October, 2010 gives you July, 2009.  In December, @date will be in November and @start will be 08/01/2009.

 FWIW, it would be much simpler to just subtract 15 months from @date.  I assume that it's using date 0 as a way to get @start to reference the first day of the month.  I would have handled it differently, but the real question is, if you don't want @start to be 15 months before @date, what do you want it to be?

 James
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
LVL 1

Expert Comment

by:MartinChadderton
ID: 34181109
Initial sets give you:
The Initial sets give:
Date
2010-10-19 15:30:49.890      
Start
2009-07-01 00:00:00.000
End
2010-09-30 23:59:59.000

So line SET @start= dateadd(m, datediff(m, 0, @date)-15, 0) will deduct 15 months off the original date:
since date is
Date
2010-10-19 15:30:49.890      
deducting 15 months will take you back to July 2009.

If you want a simple solution to give you current month and prior year data then as mentioned, change where clause to between @start and @end
and original sets to

DECLARE @Start datetime
DECLARE @end datetime

SET @start= getdate()
SET @end = DATEADD(yy,-1,@start)

a little simplistic i know and if not exactly what is required let me know but hope this is a pointer in right direction.

0
 
LVL 1

Author Comment

by:SPLady
ID: 34187786
Thanks @ James and @Martin, @Martin not it is not returning data, did I make the correct changes?
ALTER PROCEDURE EnergyProductLine
    @energyOrg nvarchar(1000)
    
    

AS
BEGIN
SET NOCOUNT OFF

DECLARE @Start datetime
DECLARE @end datetime

SET @start= getdate()
SET @end = DATEADD(yy,-1,@start)
select distinct 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 OR
                      vReportdataCurrentPrior.transDate BETWEEN @start AND @end )AND (EnergySites.EnergyOrg = @energyorg) AND
                       (vReportdataCurrentPrior.BusinessEntity = 'energy')


END

Open in new window

0
 
LVL 1

Expert Comment

by:MartinChadderton
ID: 34188089
Apologies, the @start and @end are wrong way round:
should be:
SET @end= getdate()
SET @start = DATEADD(yy,-1,@end)


Assuming that you want to return the last years data for all records where the EnergySites.EnergyOrg = @energyorg
vReportdataCurrentPrior.BusinessEntity = 'energy'
and the date range between @start and @end

then i would change the where clause to:

WHERE            (vReportdataCurrentPrior.transDate BETWEEN @start AND @end)
AND                  EnergySites.EnergyOrg = @energyorg
AND                  vReportdataCurrentPrior.BusinessEntity = 'energy'


so long as you have some records that fall between
start 2009-11-22
end 2010-11-22
and businessentity = 'energy' and energyorg = @energyorg

you shoudl be ok.      
0
 
LVL 1

Author Comment

by:SPLady
ID: 34188235
Thank you @Martin  Hmm it is pulling 2009 and 2010 but, all months instead of just the current month.. it needs to run like the 5th of every month for the previous month ie 10/01/2010-10/31/2010 and 10/01/2009 -10/31/2009... so, now the previous and prior years are working but not the month part...
ALTER PROCEDURE EnergyProductLine
   
 @energyOrg nvarchar(1000)
   

AS
BEGIN
SET NOCOUNT OFF

DECLARE @Start datetime
DECLARE @end datetime

--DECLARE @energyOrg nvarchar
SET @end= getdate()
SET @start = DATEADD(yy,-1,@end)





select distinct 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'


END

Open in new window

0
 
LVL 1

Expert Comment

by:MartinChadderton
ID: 34189260
DECLARE @month INT,
            @endyear INT,
            @startyear INT

SET @month= datepart(month,getdate())
SET @endyear= datepart(year,getdate())
SET @startyear= @endyear -1

WHERE           Month(vReportdataCurrentPrior.transDate) = @month
AND                        (Month(vReportdataCurrentPrior.transDate) =@startyear
                              OR Month(vReportdataCurrentPrior.transDate) =@endyear)
AND                  EnergySites.EnergyOrg = @energyorg
AND                  vReportdataCurrentPrior.BusinessEntity = 'energy'
0
 
LVL 1

Author Comment

by:SPLady
ID: 34189482
Thankk you...I think I am being to literal..lol It is not returning data..lol
LTER PROCEDURE EnergyProductLine
   
 @energyOrg nvarchar(1000)

   

AS
BEGIN
SET NOCOUNT OFF

DECLARE @Start datetime
DECLARE @end datetime
DECLARE @month INT,
            @endyear INT,
            @startyear INT



--DECLARE @energyOrg nvarchar
SET @end= getdate()
SET @start = DATEADD(yy,-1,@end)
SET @month= datepart(month,getdate())
SET @endyear= datepart(year,getdate())
SET @startyear= @endyear -1





select distinct 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           Month(vReportdataCurrentPrior.transDate) = @month
AND                        (Month(vReportdataCurrentPrior.transDate) =@startyear
                              OR Month(vReportdataCurrentPrior.transDate) =@endyear)
AND                  EnergySites.EnergyOrg = @energyorg
AND                  vReportdataCurrentPrior.BusinessEntity = 'energy'


END

Open in new window

0
 
LVL 1

Accepted Solution

by:
MartinChadderton earned 500 total points
ID: 34192001
Change where clause to :
WHERE           Month(vReportdataCurrentPrior.transDate) = @month
AND                        (Year(vReportdataCurrentPrior.transDate) =@startyear
                              OR Year(vReportdataCurrentPrior.transDate) =@endyear)
AND                  EnergySites.EnergyOrg = @energyorg
AND                  vReportdataCurrentPrior.BusinessEntity = 'energy'

this should bring back this november and last november.
If it doesn't can you check usinf following query:

select count(*)
FROM vReportdataCurrentPrior
WHERE           Month(vReportdataCurrentPrior.transDate) = @month
AND                        (Year(vReportdataCurrentPrior.transDate) =@startyear
                              OR Year(vReportdataCurrentPrior.transDate) =@endyear)

 you should get some rows coming back before any extra filters are applied.
0
 
LVL 1

Author Closing Comment

by:SPLady
ID: 34192154
Thank You!
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SSRS report 5 63
Date Differences SSRS 3 75
SSRS 2008 Multi Value Parameter Filter using OR 3 48
SSRS 2012 r2 - Parm Drop Down has Date/Time 12 41
Introduction: This article is aimed at report developers who are used to developing reports using relational databases and have gotten a first-time assignment to develop reports on OLAP cubes. It demonstrates how to build a report using SQL Ser…
This code started out as a fix for a customer that had incoming data that was hunderds of numbers and words long that was to fit in one column. The problem was that the customer did not want to split words or numbers when wrapping in the column. …
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

860 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