Solved

Report Manager- Stored Procedures- Configuring Subscriptions -Max Points

Posted on 2010-11-18
7
321 Views
Last Modified: 2012-05-10

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

Open in new window

0
Comment
Question by:SPLady
  • 4
  • 3
7 Comments
 
LVL 32

Expert Comment

by:Erick37
ID: 34167910
If @energyOrg is NULL uou will not get any records.
How do you populate @energyOrg without sending it into the procedure?
0
 
LVL 1

Author Comment

by:SPLady
ID: 34167979
Thank you @erick37, I am a newbie of sorts how do I "send it in to the procedure"
0
 
LVL 32

Expert Comment

by:Erick37
ID: 34168076
Pass it as a parameter.

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

Open in new window

0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 1

Author Comment

by:SPLady
ID: 34168251
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

Open in new window

0
 
LVL 32

Accepted Solution

by:
Erick37 earned 500 total points
ID: 34168396
Pass in everything you need:

CREATE PROCEDURE EnergyProductLine
    @energyOrg nvarchar(1000)
    ,@start datetime
    ,@end datetime


AS
BEGIN
SET NOCOUNT OFF

--DECLARE @Start datetime
--DECLARE @end datetime
--DECLARE @date datetime
--DECLARE @energyOrg nvarchar
 
0
 
LVL 1

Author Closing Comment

by:SPLady
ID: 34168411
Thank you! Erick37 you rock :)
0
 
LVL 32

Expert Comment

by:Erick37
ID: 34168451
Glad to help!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Suggested Solutions

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 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