No Result on the Query

Question

No Result on the Query

gazy007

Default port

Points: 1491
More actions
June 8, 2016 at 5:51 am

#100223

Hi,
I have been working on a query and failed to get it working after a full day.
I am not sure where I am going wrong and why am I not getting any data?
could please someone help as I am very new to SQL.
Thanks,
"
SELECT CustomerAccount.AccountNumber, view_SalesForecast.CompanyName, SUM(view_SalesForecastLine.ForecastValue) AS Invoiced,
Sum( view_salesorderline.SellingPrice / view_Salesorder.ExchangeRate * (view_Salesorderline.OrderQuantity - view_SalesOrderLine .InvoicedQuantity)) as OnOrder,
SUM(view_SalesForecastLine.ForecastValue) AS Forecast2016
FROM view_SalesForecast INNER JOIN
view_SalesForecastLine ON view_SalesForecast.SalesForecastID = view_SalesForecastLine.SalesForecastID
INNER JOIN CustomerAccount ON view_SalesForecast.CustomerAccountID = CustomerAccount.CustomerAccountID
INNER JOIN view_SalesStatistics ON CustomerAccount.CustomerAccountID = view_SalesStatistics.CustomerAccountID INNER JOIN view_SalesOrder ON CustomerAccount.CustomerAccountID = view_SalesOrder.CustomerAccountID
Inner join view_SalesOrderLine On view_SalesOrder.salesorderid = view_SalesOrderLine.SalesOrderID
WHERE
(view_SalesForecast.Closed = '0' AND view_salesorder.Closed = '0')
AND (view_SalesForecast.PeriodStart >= '1/1/2016') AND (view_SalesForecast.PeriodEnd <= '12/31/2016') AND (view_SalesStatistics.TransactionDate = '1/1/2016')
GROUP BY view_SalesForecast.CompanyName, view_SalesForecastLine.ForecastValue, CustomerAccount.AccountNumber"

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246686 More actions · Answer 1

This is difficult, because we don't know what your data looks like. But first of all, here is your query again, reformatted for readability:

SELECT CustomerAccount.AccountNumber

, view_SalesForecast.CompanyName

, SUM(view_SalesForecastLine.ForecastValue) AS Invoiced

, SUM(view_salesorderline.SellingPrice / view_Salesorder.ExchangeRate * (view_salesorderline.OrderQuantity

- view_salesorderline.InvoicedQuantity)) AS OnOrder

, SUM(view_SalesForecastLine.ForecastValue) AS Forecast2016

FROM view_SalesForecast

INNER JOIN view_SalesForecastLine ON view_SalesForecast.SalesForecastID = view_SalesForecastLine.SalesForecastID

INNER JOIN CustomerAccount ON view_SalesForecast.CustomerAccountID = CustomerAccount.CustomerAccountID

INNER JOIN view_SalesStatistics ON CustomerAccount.CustomerAccountID = view_SalesStatistics.CustomerAccountID

INNER JOIN view_SalesOrder ON CustomerAccount.CustomerAccountID = view_Salesorder.CustomerAccountID

INNER JOIN view_SalesOrderLine ON view_Salesorder.salesorderid = view_salesorderline.SalesOrderID

WHERE (

view_SalesForecast.Closed = '0'

AND view_Salesorder.Closed = '0'

)

AND (view_SalesForecast.PeriodStart >= '1/1/2016')

AND (view_SalesForecast.PeriodEnd <= '12/31/2016')

AND (view_SalesStatistics.TransactionDate = '1/1/2016')

GROUP BY view_SalesForecast.CompanyName

, view_SalesForecastLine.ForecastValue

, CustomerAccount.AccountNumber;

The first thing I would check is the data type of column 'view_SalesStatistics.TransactionDate'. If it is a datetime rather than a date, try this instead:

AND (cast(view_SalesStatistics.TransactionDate as date) = '20160101')

Note that I have used the universal 'YYYYMMDD' date format to reduce any possible ambiguity.

I would also check whether 'view_SalesForecast.PeriodEnd' is a datetime or a date. If it's a datetime, you need to refine your selection so that dates on 31 Dec 2016, after midnight, are included.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

gazy007 Default port Points: 1491 More actions · Answer 2

Periodend and period start are datetime as well and I have changed as you suggested earlier

AND (cast(view_SalesForecast.PeriodStart as date) >= '20160101')

AND (cast(view_SalesForecast.PeriodEnd as date ) <= '20161231')

AND (cast(view_SalesStatistics.TransactionDate as date) = '20160101')

Query run but (0 row(s) affected)

Phil Parkin SSC Guru Points: 246686 More actions · Answer 3

OK. Next step is to remove the WHERE conditions one by one and rerun. Hopefully, removing one of the conditions will make rows appear ... then we can focus troubleshooting on that.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.