Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Not returning records

Posted on 2010-11-30
3
Medium Priority
?
293 Views
Last Modified: 2012-05-10
Need a second pair of eyes, this query is not retunrning data and I am not sure why, I am not getting an error message.
SELECT     vReportdataCurrentPrior.extendedStandardCost, vReportdataCurrentPrior.businessEntity, vReportdataCurrentPrior.product, 
                      vReportdataCurrentPrior.site, vReportdataCurrentPrior.subProduct, vReportdataCurrentPrior.OrderAmount_LastYear AS [Prior Bookings], 
                      vReportdataCurrentPrior.InvoiceAmount_LastYear AS [Prior Sales], vReportdataCurrentPrior.OrderAmount_CurrentYear AS [Current Bookings], 
                      vReportdataCurrentPrior.InvoiceAmount_CurrentYear AS [Current Sales], ExchangeRates.ExchangeRate, EnergySites.EnergyOrg, 
                      vReportdataCurrentPrior.qty, EnergySites.SiteNumber, MWD.days AS monthworkingdays, WD.days AS workingdays, 
                      vReportdataCurrentPrior.transyear
FROM         vReportdataCurrentPrior CROSS JOIN
                          (SELECT     COUNT(*) AS days
                            FROM          master.dbo.spt_values
                            WHERE      (type = 'P') AND (number BETWEEN 0 AND 60) AND (MONTH(number + GETDATE() - 30) = MONTH(GETDATE())) AND ((DATEPART(dw, 
                                                   GETDATE() - 30 + number) + @@DATEFIRST - 2) % 7 + 1 < 6)) AS MWD CROSS JOIN
                          (SELECT     COUNT(*) AS days
                            FROM          master.dbo.spt_values AS spt_values_1
                            WHERE      (type = 'P') AND (number BETWEEN 0 AND @end - @start) AND ((DATEPART(dw, @start + number) + @@DATEFIRST - 2) % 7 + 1 < 6)) 
                      AS WD 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 INNER JOIN
                      PlanJoin ON vReportdataCurrentPrior.subProduct = PlanJoin.SubProduct
GROUP BY vReportdataCurrentPrior.extendedStandardCost, vReportdataCurrentPrior.businessEntity, vReportdataCurrentPrior.product, 
                      vReportdataCurrentPrior.site, vReportdataCurrentPrior.subProduct, vReportdataCurrentPrior.OrderAmount_LastYear, 
                      vReportdataCurrentPrior.InvoiceAmount_LastYear, vReportdataCurrentPrior.OrderAmount_CurrentYear, 
                      vReportdataCurrentPrior.InvoiceAmount_CurrentYear, ExchangeRates.ExchangeRate, EnergySites.EnergyOrg, vReportdataCurrentPrior.qty, 
                      EnergySites.SiteNumber, MWD.days, WD.days, vReportdataCurrentPrior.transyear, vReportdataCurrentPrior.transDate
HAVING      (EnergySites.EnergyOrg = @energyorg) AND (vReportdataCurrentPrior.transDate BETWEEN @start AND @end OR
                      vReportdataCurrentPrior.transDate BETWEEN DATEADD(yy, - 1, @start) AND DATEADD(yy, - 1, @end)) AND 
                      (vReportdataCurrentPrior.businessEntity = 'energy')
ORDER BY vReportdataCurrentPrior.transDate DESC

Open in new window

0
Comment
Question by:SPLady
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 6

Assisted Solution

by:hyphenpipe
hyphenpipe earned 600 total points
ID: 34239475
What are the variable values?

@energyorg, @start AND @end

If you rem out these clauses does it return anything?
0
 
LVL 22

Accepted Solution

by:
8080_Diver earned 1400 total points
ID: 34239972
Generally, in debugging a complex SQL statement, I start by selecting the inner most queries (e.g. the SELECT COUNT(*) AS days query) and make sure that each of them provides "reasonable" results.  

Once I have made sure that I am getting reasonable results from the inner most queries, I move out a level to see if that level is providing reasonable results.  

Of course, I also force variables to specific "known good" values (e.g. replacing the @start with a "known good" date) that I know occur in the table..
0
 
LVL 1

Author Comment

by:SPLady
ID: 34240005
THANK YOU  @hyphenpipe no it doesnt work woithout them
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

688 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