SPLady
asked on
Report Manager- Stored Procedures- Configuring Subscriptions -Max Points
Hello Experts,
My SP is broken
1. Why isnt this SP returning any records?
2. Why aren't the parameters populating?
CREATE PROCEDURE EnergyProductLine
AS
BEGIN
SET NOCOUNT OFF
DECLARE @Start datetime
DECLARE @end datetime
DECLARE @date datetime
DECLARE @energyOrg nvarchar
SET @date = dateadd (mm, -1, getdate()+13)
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
Thank you @erick37, I am a newbie of sorts how do I "send it in to the procedure"
Pass it as a parameter.
EXEC EnergyProductLine @energyOrg = 'foo'
EXEC EnergyProductLine @energyOrg = 'foo'
CREATE PROCEDURE EnergyProductLine
@energyOrg nvarchar(1000)
AS
BEGIN
SET NOCOUNT OFF
DECLARE @Start datetime
DECLARE @end datetime
DECLARE @date datetime
--DECLARE @energyOrg nvarchar
ASKER
Thank you! ok, so If i am calling this procedure from another do I need the date and energy parameters?
ALTER PROCEDURE runEnergyProductLine
@energyOrg nvarchar(1000)
AS
BEGIN
SET NOCOUNT OFF
DECLARE @start datetime
DECLARE @end datetime
DECLARE @date datetime
SET @date = dateadd (mm, -1, getdate()-13)
SET @start = dateadd(m, datediff(m, 0, @date-15), 0)
SET @end = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@date),0))
PRINT @date
PRINT @start
PRINT @end
--then you will use them in a select or even execute the other SP with parameters passing the two dates
select* from energyproductline where transDate between @start and @end
exec EnergyProductLine @start,@end
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! Erick37 you rock :)
Glad to help!
How do you populate @energyOrg without sending it into the procedure?