CODEWITHSUNDEEP
sqlsql-server
R_Avery_17
R_Avery_17

Reputation: 305

Query executes successfully but returns no results

The following query is used for a report, the report is still showing live data when accesssed, yet when running the query in Management Studio I am not getting any results despite the message 'query successfully completed'

I have had to declare the @Date parameter which I think I have done correctly at the top of the query. 

DECLARE @Date datetime;

BEGIN
    SET @Date = 27/07/2017;
END

SELECT     CAST(CASE WHEN (SOTD_STWH_CODE = 'HPP SHEF') THEN DATE - (CASE 
DATEPART(dw, DATE) WHEN 2 THEN 3 ELSE 1 END) ELSE DATE END AS date) AS 
ShipDate, 
                      DeFactoUser.F_SO_Transaction.SOTR_CUST_CODE, 
DeFactoUser.F_SO_Transaction_Details.SOTD_HEAD_NO, 
DeFactoUser.F_SO_Transaction.SOTR_DLSC_CODE, 
                      DeFactoUser.F_SO_Transaction_Details.SOTD_STWH_CODE, 
DeFactoUser.F_SO_Transaction_Details.SOTD_STRC_CODE, 
DeFactoUser.F_SO_Transaction_Details.SOTD_QTY_UNITS_ORDERED, 

DeFactoUser.F_SO_Transaction_Details.SOTD_QTY_UNITS_OUTSTANDING, 
DeFactoUser.F_SO_Transaction_Details.SOTD_QTY_UNITS_PICKED, 

DeFactoUser.F_BM_Transactions_Details.BMTD_BMTR_SYS_NO, 
DeFactoUser.F_BM_Transactions_Details.BMTD_QTY_OUTSTANDING, 
ISNULL(CAST(BaseOn.baseon AS varchar), '') AS BaseOn, 
                      CASE BaseOn.baseonstat WHEN '99' THEN 'Complete' WHEN 
'98' THEN 'Outstanding' WHEN '1' THEN 'open' ELSE '' END AS BaseOnStatus, 
DeFactoUser.F_SL_Customers.CUST_NAME
FROM         DeFactoUser.F_SL_Customers INNER JOIN
                  DeFactoUser.F_SO_Transaction_Details WITH (NOLOCK) INNER 
JOIN
                      DeFactoUser.F_ST_Products WITH (NOLOCK) ON 
DeFactoUser.F_SO_Transaction_Details.SOTD_STRC_CODE = 
DeFactoUser.F_ST_Products.STRC_CODE INNER JOIN
                      DeFactoUser.F_SO_Transaction WITH (NOLOCK) ON 
DeFactoUser.F_SO_Transaction_Details.SOTD_HEAD_NO = 
DeFactoUser.F_SO_Transaction.SOTR_SYS_NO INNER JOIN
                      tbl_DFBI_Date ON 
DeFactoUser.F_SO_Transaction.SOTR_PROMISED_DATE = tbl_DFBI_Date.Date ON 
                      DeFactoUser.F_SL_Customers.CUST_CODE = 
DeFactoUser.F_SO_Transaction_Details.SOTD_CUST_CODE LEFT OUTER JOIN
                      DeFactoUser.F_BM_Transactions INNER JOIN
                      DeFactoUser.F_BM_Transactions_Details ON 
DeFactoUser.F_BM_Transactions.BMTR_SYS_NO = 
DeFactoUser.F_BM_Transactions_Details.BMTD_BMTR_SYS_NO ON 
                      DeFactoUser.F_SO_Transaction_Details.SOTD_SYS_NO = 
DeFactoUser.F_BM_Transactions_Details.BMTD_ORDER_LINK_NUMBER LEFT OUTER JOIN
                          (SELECT     RIGHT(SOTR_BASED_ON_REF, 7) AS link, 
SOTR_STATUS AS baseonstat, SOTR_SYS_NO AS baseon
                            FROM          DeFactoUser.F_SO_Transaction AS 
F_SO_Transaction_1
                            WHERE      (SOTR_CUST_CODE = 'h075') AND 
(SOTR_BASED_ON_REF > '0')) AS BaseOn ON 
CAST(DeFactoUser.F_SO_Transaction_Details.SOTD_HEAD_NO AS varchar) = 
BaseOn.link
WHERE     (DeFactoUser.F_ST_Products.STRC_NI_CODE = 'panelcut') AND 
(DeFactoUser.F_ST_Products.STRC_ANAL1 = '1033') AND 
(DeFactoUser.F_SO_Transaction_Details.SOTD_SOTR_TYPE = 10) AND 
                  (DeFactoUser.F_SO_Transaction.SOTR_CUST_CODE <> 'h075') 
AND (DeFactoUser.F_SO_Transaction.SOTR_STATUS < '99') AND (CASE WHEN 
(SOTD_STWH_CODE = 'HPP SHEF') 
                  THEN DATE - (CASE DATEPART(dw, DATE) WHEN 2 THEN 3 ELSE 1 
END) ELSE DATE END <= @Date)

Any suggestions as to how i can view the results?

Upvotes: 0

Views: 431

Answers (1)

TT.
TT.

Reputation: 16144

You need the following to initialize the date:

SET @Date = '20170727'; -- Format as YYYYMMDD which is locale neutral

What you currently have is, 27 divided by 7 divided by 2017. These are integers, so the result is 0. This number is then converted to datetime, which will not result in the date you intended to have.

You best stick to the ISO 8601 formatting of dates, or date/time values. You can read more about that in the DATETIME documentation.

Upvotes: 1

Related Questions