June 8, 2016 at 5:51 am
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"
June 8, 2016 at 6:14 am
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 8, 2016 at 6:55 am
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)
June 8, 2016 at 7:43 am
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply