Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Not returning records

Posted on 2010-11-30
3
Medium Priority
?
294 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 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…

885 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