General SQL Stuff

Order of execution

When you execute a SQL Query, it will be executed in the following sequence.

FROM

ON

JOIN

WHERE

GROUP BY

HAVING

SELECT

DISTINCT

ORDER BY

TOP

Pivot
select * from DailyIncome  -- Colums to pivot
pivot (
   max (IncomeAmount)                                                    -- Pivot on this column
   for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN]))         -- Make colum where IncomeDay is in one of these.
   as MaxIncomePerDay                                                     -- Pivot table alias
where VendorId in ('SPIKE')                               -- Select only for this vendor
Date Time format for Microsoft SQL
ANSI Warnings

http://technet.microsoft.com/en-us/library/aa259213%28v=sql.80%29.aspx

SET ARITHABORT OFF SET ANSI_WARNINGS OFF SET NOCOUNT ON 
NO LOCK
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
CONVERT
Convert DateTime to Just Date
CONVERT(Datetime,(CONVERT(CHAR(10),[DateTime],102)),102)
Convert DateTime to Date + Hour
DATE = DATEADD(HOUR, DATEDIFF(HOUR, 0, DateTime), 0)
Datepart

MAXDOP

Limit SQL query to a single thread - for intensive SQL queries without impacting system performance for other queries

SELECT * FROM BLAH WHERE A = B
OPTION (MAXDOP 1)

http://sqlmag.com/blog/common-misconception-about-maxdop

LAST HOUR

WHERE DateTime > DateAdd(hh,-1,GetDate())
Checking Seed value
USE ContactCenterManager
GO
DBCC CHECKIDENT ('Queue',NORESEED);
GO
Setting Seed value
USE ContactCenterManager
GO
DBCC CHECKIDENT ('Queue',RESEED,1001);
GO
Setting constraint on a database table for two fields which must be unique
  • Enter Design View of Table
  • Right Click and select Indexs/Keys
  • Add an index, add your two (or more) colums and select unique - see screen shot below

Select Records JUST for yesterday, between 00 - 23:59

select * from Route_Call_Detail
WHERE DateTime >= cast(GETDATE()-1 as date) AND DateTime < cast(GETDATE() as date)
EXEC sp_configure 'show advanced options', 1
GO

RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
SELECT        SQLTEXT.text, STATS.last_execution_time
FROM          sys.dm_exec_query_stats STATS
CROSS APPLY   sys.dm_exec_sql_text(STATS.sql_handle) AS SQLTEXT
WHERE         STATS.last_execution_time > GETDATE()-1
ORDER BY      STATS.last_execution_time DESC

Example 1

SELECT * FROM
(
SELECT
TCDRecoveryKey = TCD.TCDRecoveryKey
,ECCID = TCD.ExpandedCallVariableID
,ECCValue = TCD.ECCValue
FROM [ucce_awdb].[dbo].[Termination_Call_Variable] TCD
WHERE DateTime > GetDate()-1 AND ExpandedCallVariableID IN (5039,5040,5041)
)  
AS S
PIVOT (MAX(ECCValue) FOR [ECCID] IN ([5039],[5040],[5041])
) AS PVT

Example 2

SELECT * FROM
(
SELECT
 TCDRecoveryKey = TCD.TCDRecoveryKey
--,ECCID = TCD.ExpandedCallVariableID
,ECCName = ECC.EnterpriseName
,ECCValue = TCD.ECCValue
 
FROM 
[ucce_awdb].[dbo].[Termination_Call_Variable] TCD
,[ucce_awdb].[dbo].[Expanded_Call_Variable] ECC
 
WHERE 
DateTime > GetDate()-1 
AND TCD.ExpandedCallVariableID IN (5030,5036)
AND TCD.ExpandedCallVariableID = ECC.ExpandedCallVariableID
)  
AS S
PIVOT (MAX(ECCValue) FOR [ECCName] IN ([USER.cust.blah1],[USER.cust.blah2])
) AS PVT
This website uses cookies. By using the website, you agree with storing cookies on your computer. Also you acknowledge that you have read and understand our Privacy Policy. If you do not agree leave the website.More information about cookies