Solved

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

Posted on 2010-11-19
11
357 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
 
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
overlapping data labels 1 446
MDX in SSRS v SSAS 1 1,956
sql query to reportserver  table error 3 39
Report Builder 9 44
Time Corrections for Reports Working with a report, we made some interesting discoveries about the time corrections/updates We are using the following Parameters: Starting Entered Date (Date) formatted as Data type: "Date/Time" Ending Entered …
Hi All, I am here to write a simple article to move SSRS (SQL Server Reporting Services) reports from one server to another. When I have faced the same issue to move reports those were developed by developer on development server and now need to …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

911 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

19 Experts available now in Live!

Get 1:1 Help Now