Solved

Report Manager- Stored Procedures- Configuring Subscriptions -Max Points

Posted on 2010-11-18
7
320 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Select2 jquery help 9 74
Remove Time Stamp from a Date field 4 41
Help writing a query 6 70
subtract 1 in Access 2003 query 7 36
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.
Need to grow your business through quality cloud solutions? With everything required to build a cloud platform and solution, you may feel like the distance between you and the cloud is quite long. Help is here. Spend some time learning about the Con…

932 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

14 Experts available now in Live!

Get 1:1 Help Now