SPLady
asked on
SP is broken :( SP is pulling data from 2008 - Shoud be current and previous year- Max Points
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.
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
ASKER
Thanky you @MartinChadderton what about the month part, why is it starting at July (7)?
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
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
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.
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.
ASKER
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
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.Bu
and the date range between @start and @end
then i would change the where clause to:
WHERE (vReportdataCurrentPrior.t
AND EnergySites.EnergyOrg = @energyorg
AND vReportdataCurrentPrior.Bu
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.
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.Bu
and the date range between @start and @end
then i would change the where clause to:
WHERE (vReportdataCurrentPrior.t
AND EnergySites.EnergyOrg = @energyorg
AND vReportdataCurrentPrior.Bu
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.
ASKER
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
DECLARE @month INT,
@endyear INT,
@startyear INT
SET @month= datepart(month,getdate())
SET @endyear= datepart(year,getdate())
SET @startyear= @endyear -1
WHERE Month(vReportdataCurrentPr
AND (Month(vReportdataCurrentP
OR Month(vReportdataCurrentPr
AND EnergySites.EnergyOrg = @energyorg
AND vReportdataCurrentPrior.Bu
@endyear INT,
@startyear INT
SET @month= datepart(month,getdate())
SET @endyear= datepart(year,getdate())
SET @startyear= @endyear -1
WHERE Month(vReportdataCurrentPr
AND (Month(vReportdataCurrentP
OR Month(vReportdataCurrentPr
AND EnergySites.EnergyOrg = @energyorg
AND vReportdataCurrentPrior.Bu
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank You!
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