Solved

Not returning records

Posted on 2010-11-30
3
286 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
Comment Utility
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
Comment Utility
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
Comment Utility
THANK YOU  @hyphenpipe no it doesnt work woithout them
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

763 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

15 Experts available now in Live!

Get 1:1 Help Now