Solved

Not returning records

Posted on 2010-11-30
3
289 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
3 Comments
 
LVL 6

Assisted Solution

by:hyphenpipe
hyphenpipe earned 150 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 350 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

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.

Question has a verified solution.

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

Suggested Solutions

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

840 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