Solved

Not returning records

Posted on 2010-11-30
3
288 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
CONVERT date time to a different time zone. 2 53
INSERT DATE FROM STRING COLUMN 18 56
My Query is not giving correct result. Please help 5 40
Sql query 107 63
If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

772 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