--BASIC SELECT *
SELECT *
FROM dbo.SV00585
--SPECIFY COLUMNS
SELECT Contract_Number,WSCONTSQ,Equipment_ID
FROM dbo.SV00585
WHERE WSCONTSQ=1
--USING AN INDEX TO GET RID OF THE TABLE SCAN
SELECT T.Contract_Number,T.WSCONTSQ,T.Equipment_ID
FROM dbo.SV00585 T
INNER JOIN dbo.SV00501 H
ON T.Contract_Number=H.Contract_Number
AND T.WSCONTSQ=H.WSCONTSQ
AND T.ADRSCODE=H.ADRSCODE
AND T.CUSTNMBR=H.CUSTNMBR
WHERE T.WSCONTSQ=1
--EXAMPLE OF ELIMINATING TABLE SCAN ON DATE FUNCTION
SELECT T.Contract_Number,T.WSCONTSQ,T.Equipment_ID
FROM dbo.SV00585 T
INNER JOIN dbo.SV00501 H
ON T.Contract_Number=H.Contract_Number
AND T.WSCONTSQ=H.WSCONTSQ
AND T.ADRSCODE=H.ADRSCODE
AND T.CUSTNMBR=H.CUSTNMBR
WHERE DATEADD(day, 15, T.Schedule_Date) = '01/16/2017'
SELECT T.Contract_Number,T.WSCONTSQ,T.Equipment_ID
FROM dbo.SV00585 T
INNER JOIN dbo.SV00501 H
ON T.Contract_Number=H.Contract_Number
AND T.WSCONTSQ=H.WSCONTSQ
AND T.ADRSCODE=H.ADRSCODE
AND T.CUSTNMBR=H.CUSTNMBR
WHERE T.Schedule_Date = DATEADD(day, -15, '01/16/2017')
--EXAMPLE OF LIKE OPERATOR
SELECT A.Equipment_ID
FROM dbo.SV00400 A
WHERE A.Equipment_ID LIKE '%A%'
--IMPLICIT CONVERSIONS
DECLARE @NCHAR NCHAR(15),
@CHAR CHAR(15)
SELECT @NCHAR='MAIN OFFICE',
@CHAR='MAIN OFFICE'
SELECT T.Contract_Number,T.WSCONTSQ,T.Equipment_ID,T.CUSTNMBR,T.ADRSCODE,H.*
FROM dbo.SV00585 T
INNER JOIN dbo.SV00501 H
ON T.Contract_Number=H.Contract_Number
AND T.WSCONTSQ=H.WSCONTSQ
AND T.ADRSCODE=H.ADRSCODE
AND T.CUSTNMBR=H.CUSTNMBR
WHERE H.ADRSCODE=@NCHAR
SELECT T.Contract_Number,T.WSCONTSQ,T.Equipment_ID,T.CUSTNMBR,T.ADRSCODE,H.*
FROM dbo.SV00585 T
INNER JOIN dbo.SV00501 H
ON T.Contract_Number=H.Contract_Number
AND T.WSCONTSQ=H.WSCONTSQ
AND T.ADRSCODE=H.ADRSCODE
AND T.CUSTNMBR=H.CUSTNMBR
WHERE H.ADRSCODE=@CHAR
Did this help you? If so, please leave a comment!
David Jedziniak maintains this blog and posts helpful tips and tricks on SQL Server and Dynamics GP development. The opinions expressed here are those of the author and do not represent the views, express or implied, of any past or present employer. The information here is provided without warranty of fitness for any particular purpose.
Subscribe to:
Post Comments (Atom)
SQL 2022 TSQL snapshot backups!
SQL 2022 now actually supports snapshot backups! More specifically, T-SQL snapshot backups. Of course this is hardware-dependent. Here ...
-
If you are reading this, you are probably aware of the fact that you cannot deploy SSRS 2008 reports on an SSRS2005 server. SSRS reports ar...
-
SQL Job to refresh TEST from PRODUCTION Last Updated: 2018.11.12 I like to include each of these steps as a separate job step. If you ...
-
I ran into an issue today where I had a report parameter default that I couldn't seem to get rid of. In BIDS, I deleted the defaults f...
No comments:
Post a Comment