Full text Search

Posted in Labels: | at 10:14 AM

-----------------------------------------------------------
-- Total Training for SQL Server 2005
--
-- Part 2 - Selecting Data
-- Lesson 5 - Using Full-Text Search
--
-- Paul Nielsen
-----------------------------------------------------------


-----------------------------------------------------------
-- Creating a Full-Text Index

USE AESOP

-- SQL Where Like
SELECT Title
FROM Fable
WHERE Fabletext LIKE '%lion%'
AND Fabletext LIKE '%bold%'

CREATE FULLTEXT CATALOG AesopFT

CREATE FULLTEXT INDEX ON dbo.Fable(Title, Moral, Fabletext)
KEY INDEX FablePK ON AesopFT
WITH CHANGE_TRACKING AUTO
GO


-----------------------------------------------------
-- Searching for Words

USE AESOP

-- Contains
SELECT Title
FROM Fable
WHERE CONTAINS (Fable.*,'Lion')

-- Joining with Full-Text Search
SELECT *
FROM CONTAINSTABLE (Fable, *, 'Lion')

SELECT Fable.Title, Rank
FROM Fable
JOIN CONTAINSTABLE (Fable, *, 'Lion') FTS
ON Fable.FableID = FTS.[KEY]
ORDER BY FTS.Rank DESC

SELECT Fable.Title, Rank
FROM Fable
JOIN CONTAINSTABLE (Fable, *, 'Lion', 2) FTS
ON Fable.FableID = FTS.[KEY]
ORDER BY FTS.Rank

-- Advanced Options

-- Phrases
SELECT Title
FROM Fable
WHERE CONTAINS (*,' "Wolf! Wolf!" ')

-- Or, And
SELECT Title
FROM Fable
WHERE CONTAINS (FableText,'Tortoise AND Hare')

SELECT Title
FROM Fable
WHERE CONTAINS (*,' "Thrifty AND supperless" ')

SELECT Title
FROM Fable
WHERE CONTAINS (*,'Thrifty')
AND CONTAINS(*,'supperless')

-- Wildcards *
SELECT Title
FROM Fable
WHERE CONTAINS (*,' "Hunt*" ')

-- Near
SELECT Title
FROM Fable
WHERE CONTAINS (*,'pardoned NEAR forest')

SELECT Title
FROM Fable
WHERE CONTAINS (*,'lion NEAR paw NEAR bleeding')

SELECT Fable.Title, Rank
FROM Fable
JOIN CONTAINSTABLE (Fable, *,'life NEAR death') FTS
ON Fable.FableID = FTS.[KEY]
ORDER BY FTS.Rank DESC

-- Weighting
SELECT Fable.Title, FTS.Rank
FROM Fable
JOIN CONTAINSTABLE (Fable, fabletext, 'ISABOUT (Lion weight (.5), Brave weight (.5), Eagle weight (.5))',20) FTS
ON Fable.FableID = FTS.[KEY]
ORDER BY Rank DESC

SELECT Fable.Title, FTS.Rank
FROM Fable
JOIN CONTAINSTABLE (Fable, fabletext, 'ISABOUT (Lion weight (.2), Brave weight (.2), Eagle weight (.8))',20) FTS
ON Fable.FableID = FTS.[KEY]
ORDER BY Rank DESC

SELECT Fable.Title, FTS.Rank
FROM Fable
JOIN CONTAINSTABLE (Fable, *, 'ISABOUT (Lion weight (.2), Brave weight (.2), Eagle weight (.8))',20) FTS
ON Fable.FableID = FTS.[KEY]
ORDER BY Rank DESC

-- Inflection
SELECT Title
FROM Fable
WHERE CONTAINS (*,'FORMSOF(INFLECTIONAL,pity)')

SELECT Title
FROM Fable
WHERE CONTAINS (*,'FORMSOF(INFLECTIONAL,geese)')

SELECT Title
FROM Fable
WHERE CONTAINS (*,'FORMSOF(INFLECTIONAL,carry)')

SELECT Title
FROM Fable
WHERE CONTAINS (*,'FORMSOF(INFLECTIONAL,fly)')


----------------------------------------------------
-- Using Fuzzy Searches

-- FreeText

SELECT Title
FROM Fable
WHERE FREETEXT (*,'The Tortoise beat the Hare in the big race')

SELECT Title
FROM Fable
WHERE FREETEXT (*,'The eagle was shot by an arrow')

SELECT Title
FROM Fable
WHERE FREETEXT (*,'The brave hunter kills the lion')

-- FreeText Table

SELECT Fable.Title, FTS.Rank
FROM Fable
JOIN FREETEXTTABLE (Fable, *, 'The brave hunter kills the lion',20) FTS
ON Fable.FableID = FTS.[KEY]
ORDER BY Rank DESC

SELECT Fable.Title, FTS.Rank
FROM Fable
JOIN FREETEXTTABLE (Fable, *, 'The eagle was shot by an arrow',20) FTS
ON Fable.FableID = FTS.[KEY]
ORDER BY Rank DESC

Read Users' Comments (0)

Aggregation Functions

Posted in Labels: | at 10:14 AM

-----------------------------------------------------------
-- Total Training for SQL Server 2005
--
-- Part 2 - Selecting Data
-- Lesson 4 - Aggregating Data
--
-- Paul Nielsen
-----------------------------------------------------------

-----------------------------------------------------------
-- Using the new Windowing Functions

Use AdventureWorks;

SELECT ProductID, TransactionDate
FROM Production.TransactionHistory
ORDER BY TransactionDate;

SELECT ProductID, TransactionDate,
Row_Number() OVER(ORDER BY TransactionDate) as RowNumDate,
Row_Number() OVER(ORDER BY ProductID) + 5000 as RowNumiD
FROM Production.TransactionHistory
-- not permitted
Where Row_Number() OVER(ORDER BY TransactionDate) Between 10001 AND 20000
ORDER BY TransactionDate;

-- Select mid-range of rownumbers & every 5th row
SELECT RowNum, ProductID, TransactionDate
FROM (SELECT ProductID, TransactionDate,
Row_Number() OVER(ORDER BY TransactionDate) as RowNum
FROM Production.TransactionHistory) sq
WHERE RowNum Between 10001 AND 20000
AND RowNum%5 = 0
ORDER BY RowNum ;

-- Ranking and Windowing
SELECT SalesOrderId, CustomerId, TerritoryID,
ROW_NUMBER() OVER(ORDER BY CustomerId) AS RowNum,
RANK() OVER(ORDER BY CustomerId) AS [Rank],
DENSE_RANK() OVER(ORDER BY CustomerId) AS [DenseRank],
NTILE(5) OVER(ORDER BY CustomerId) AS NTile5,
RANK() OVER(PARTITION BY TerritoryID ORDER BY CustomerId) AS [Partition]
FROM Sales.SalesOrderHeader;

-- Percentile Ranking
SELECT SalesOrderId, CustomerId,
NTILE(100) OVER(ORDER BY CustomerId) AS Percentile
FROM Sales.SalesOrderHeader
ORDER BY CustomerId

-- Sample Data
SELECT
SalesOrderId, CustomerId
FROM Sales.SalesOrderHeader TABLESAMPLE (10000 rows)

SELECT
SalesOrderId, CustomerId
FROM Sales.SalesOrderHeader TABLESAMPLE (10 percent)
ORDER BY CustomerId


--------------------------------------------------------------------
-- Using Aggregate Functions
go

-- Build the sample data
USE tempdb

IF EXISTS(SELECT * FROM sysobjects WHERE Name = 'RawData')
DROP TABLE RawData
go

CREATE TABLE RawData (
Region VARCHAR(10),
Category CHAR(1),
Amount INT,
SalesDate DateTime
)

go

INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'South', 'Y', 12, '11/1/2005')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'South', 'Y', 24, '11/1/2005')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'South', 'Y', 15, '12/1/2005')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'NorthEast', 'Y', 28, '12/1/2005')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'South', 'X', 11, '1/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'MidWest', 'X', 24, '1/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'West', 'X', 36, '2/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'South', 'Y', 47, '2/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'MidWest', 'Y', 38, '3/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'NorthEast', 'Y', 62, '3/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'South', 'Z', 33, '4/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'MidWest', 'Z', 83, '4/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'West', 'Z', 44, '5/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'NorthEast', 'Z', 55, '5/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'South', 'X', 68, '6/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'South', 'X', 86, '6/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'South', 'Y', 54, '7/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'South', 'Y', 63, '7/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'South', 'Y', 72, '8/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'NorthEast', 'Y', 91, '8/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'NorthEast', 'Y', null, '8/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'NorthEast', 'Y', null, '8/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'NorthEast', 'Y', null, '8/1/2006')
INSERT RawData (Region, Category, Amount, SalesDate)
VALUES( 'NorthEast', 'Y', null, '8/1/2006')

-- check the Amount
SELECT * FROM RawData

-- Simple Aggregations
SELECT
Count(*) as Count,
Sum(Amount) as [Sum],
Avg(Amount) as [Avg],
Min(Amount) as [Min],
Max(Amount) as [Max]
FROM RawData

-- How Nulls affect Averages
SELECT Avg(Cast((Amount)as Numeric(9,5))) as [Numeric Avg],
Avg(Amount) as [Int Avg],
Sum(Amount) / Count(*) as [Manual Avg]
FROM RawData



-- Statistical Functions
SELECT
Count(*) as Count,
StDevP(Amount) as [StDevP],
VarP(Amount) as [VarP]
FROM RawData

SELECT
Count(*) as Count,
StDev(Amount) as [StDev],
Var(Amount) as [Var]
FROM RawData
WHERE Year(SalesDate) = 2006


---------------------------------------------
-- Grouping and Aggregating

SELECT Category,
Count(*) as Count,
Sum(Amount) as [Sum],
Avg(Amount) as [Avg],
Min(Amount) as [Min],
Max(Amount) as [Max]
FROM RawData
GROUP BY Category

-- Group by occurs after the where clause
SELECT Category,
Count(*) as Count,
Sum(Amount) as [Sum],
Avg(Amount) as [Avg],
Min(Amount) as [Min],
Max(Amount) as [Max]
FROM RawData
WHERE Year(SalesDate) = 2006
GROUP BY Category

-- Grouping By multiple columns
SELECT Year(SalesDate) as [Year], DatePart(q,SalesDate) as [Quarter],
Count(*) as Count,
Sum(Amount) as [Sum],
Avg(Amount) as [Avg],
Min(Amount) as [Min],
Max(Amount) as [Max]
FROM RawData
GROUP BY Year(SalesDate), DatePart(q,SalesDate)

---------------------------------------------
-- Including Descriptions

/*
-- This example SHOULD generate an error...
SELECT Region, Count(*)
FROM RawData
*/

IF EXISTS(SELECT * FROM sysobjects WHERE Name = 'RawCategory')
DROP TABLE RawCategory

CREATE TABLE RawCategory (
RawCategoryID CHAR(1),
CategoryName VARCHAR(25)
)

INSERT RawCategory (RawCategoryID, CategoryName)
VALUES ('X', 'Sci-Fi')
INSERT RawCategory (RawCategoryID, CategoryName)
VALUES ('Y', 'Philosophy')
INSERT RawCategory (RawCategoryID, CategoryName)
VALUES ('Z', 'Zoology')

-- including Amount outside the aggregate function or group by will cause an error
/*
SELECT Category, CategoryName,
Sum(Amount) as [Sum],
Avg(Amount) as [Avg],
Min(Amount) as [Min],
Max(Amount) as [Max]
FROM RawData R
JOIN RawCategory C
ON R.Category = C.RawCategoryID
GROUP BY Category
*/


-- Solution 1: include all Amount in the Group By
SELECT Category, CategoryName,
Sum(Amount) as [Sum],
Avg(Amount) as [Avg],
Min(Amount) as [Min],
Max(Amount) as [Max]
FROM RawData R
JOIN RawCategory C
ON R.Category = C.RawCategoryID
GROUP BY Category, CategoryName
ORDER BY Category, CategoryName

-- Solution 2: Aggregate in Subquery, description in outer query
SELECT sq.Category, CategoryName, sq.[Sum], sq.[Avg], sq.[Min], sq.[Max]
FROM (SELECT Category,
Sum(Amount) as [Sum],
Avg(Amount) as [Avg],
Min(Amount) as [Min],
Max(Amount) as [Max]
FROM RawData
GROUP BY Category ) sq
JOIN RawCategory C
ON sq.Category = C.RawCategoryID
ORDER BY Category, CategoryName


--------------------------------------------------------------
-- Including all Group By Values

-- Left Outer Join Group Bys
SELECT Year(SalesDate) AS [Year],
Count(*) as Count,
Sum(Amount) as [Sum],
Avg(Amount) as [Avg],
Min(Amount) as [Min],
Max(Amount) as [Max]
FROM RawData
WHERE Year(SalesDate) = 2006 -- (There are a few 2005 rows in the RawData table)
GROUP BY ALL Year(SalesDate)



------------------------------------------------------------------
-- Nesting Aggregations

-- Which Category sold the most in each quarter?

-- Can't nest aggregate function - error:
/*
Select Y,Q, Max(Sum) as MaxSum
FROM ( -- Calculate Sums
SELECT Category, Year(SalesDate) as Y, DatePart(q,SalesDate) as Q, max(Sum(Amount)) as Sum
FROM RawData
GROUP BY Category, Year(SalesDate), DatePart(q,SalesDate)
) sq
GROUP BY Y,Q
ORDER BY Y,Q
*/

-- Solution: Including Detail description

SELECT MaxQuery.Y, MaxQuery.Q, AllQuery.Category, MaxQuery.MaxSum as sales
FROM (-- Find Max Sum Per Year/Quarter
Select Y,Q, Max(Sum) as MaxSum
FROM ( -- Calculate Sums
SELECT Category, Year(SalesDate) as Y, DatePart(q,SalesDate) as Q, Sum(Amount) as Sum
FROM RawData
GROUP BY Category, Year(SalesDate), DatePart(q,SalesDate)
) sq
GROUP BY Y,Q
) MaxQuery
JOIN (-- All Amount Query
SELECT Category, Year(SalesDate) as Y, DatePart(q,SalesDate) as Q, Sum(Amount) as Sum
FROM RawData
GROUP BY Category, Year(SalesDate), DatePart(q,SalesDate)
)AllQuery
ON MaxQuery.Y = AllQuery.Y
AND MaxQuery.Q = AllQuery.Q
AND MaxQuery.MaxSum = AllQuery.Sum
ORDER BY MaxQuery.Y, MaxQuery.Q


---------------------------------------------
-- Filtering Group By Results

SELECT Year(SalesDate) as [Year],
DatePart(q,SalesDate) as [Quarter],
Count(*) as Count,
Sum(Amount) as [Sum],
Avg(Amount) as [Avg]
FROM RawData
GROUP BY Year(SalesDate), DatePart(q,SalesDate)
HAVING Avg(Amount) > 25
ORDER BY [Year], [Quarter]


--------------------------------------------------------
-- Adding Grand Totals

-- Sum of single column Group By (Roll-Up)
SELECT Grouping(Category), Category,
CASE Grouping(Category)
WHEN 0 THEN Category
WHEN 1 THEN 'All Categories'
END AS Category,
Count(*) as Count
FROM RawData
GROUP BY Category
WITH ROLLUP

-- Sum of Mulitple column Group By (Roll-Up)
-- Second Group by Column w/ subtotals

SELECT
CASE Grouping(Category)
WHEN 0 THEN Category
WHEN 1 THEN 'All Categories'
END AS Category,
CASE Grouping(Year(SalesDate))
WHEN 0 THEN Cast(Year(SalesDate) as CHAR(8))
WHEN 1 THEN 'All Years'
END AS Year,
Count(*) as Count
FROM RawData
GROUP BY Category, Year(SalesDate)
WITH ROLLUP

-- Multidimensional column Group By (Cube)
SELECT
CASE Grouping(Category)
WHEN 0 THEN Category
WHEN 1 THEN 'All Categories'
END AS Category,
CASE Grouping(Year(SalesDate))
WHEN 0 THEN Cast(Year(SalesDate) as CHAR(8))
WHEN 1 THEN 'All Years'
END AS Year, Count(*) as Count
FROM RawData
GROUP BY Category, Year(SalesDate)
WITH CUBE

-- Compute
SELECT Category, SalesDate, Amount
FROM RawData
WHERE Year(SalesDate) = '2006'
COMPUTE Avg(Amount), sum(Amount)

SELECT Category, SalesDate, Amount
FROM RawData
WHERE Year(SalesDate) = '2006'
ORDER BY Category
COMPUTE Avg(Amount), sum(Amount)
BY Category

SELECT Category, SalesDate, Amount
FROM RawData
WHERE Year(SalesDate) = '2006'
ORDER BY Category
COMPUTE avg(Amount), sum(Amount)
COMPUTE sum(Amount)
BY Category


-------------------------------------------------------------------
-- Generating Crosstab(Pivot) Tables

-- Fixed Column CrossTab with Category Subtotal- CASE Method
SELECT Category,
SUM(Case Region WHEN 'South' THEN Amount ELSE 0 END) AS South,
SUM(Case Region WHEN 'NorthEast' THEN Amount ELSE 0 END) AS NorthEast,
SUM(Case Region WHEN 'MidWest' THEN Amount ELSE 0 END) AS MidWest,
SUM(Case Region WHEN 'West' THEN Amount ELSE 0 END) AS West,
SUM(Amount) as Total
FROM RawData
GROUP BY Category
ORDER BY Category


-- Fixed Column Crosstab - PIVOT Method
SELECT Category, SalesDate, South, NorthEast, MidWest, West
FROM RawData
PIVOT
(Sum (Amount)
FOR Region IN (South, NorthEast, MidWest, West)
) AS pt

SELECT Category, South, NorthEast, MidWest, West
FROM (Select Category, Region, Amount from RawData) sq
PIVOT
(Sum (Amount)
FOR Region IN (South, NorthEast, MidWest, West)
) AS pt


-- Fixed Column Crosstab with Category Subtotal - PIVOT Method
SELECT Category, South, NorthEast, MidWest, West,
IsNull(South,0) + IsNull(NorthEast,0) + IsNull(MidWest,0) + IsNull(West,0) as Total
FROM (Select Category, Region, Amount from RawData) sq
PIVOT
(Sum (Amount)
FOR Region IN (South, NorthEast, MidWest, West)
) AS pt

-- Fixed Column Crosstab with Filter - PIVOT Method
-- Must filter within the FROM clause (using subquery) prior to Pivot operation
SELECT Category, South, NorthEast, MidWest, West,
IsNull(South,0) + IsNull(NorthEast,0) + IsNull(MidWest,0) + IsNull(West,0) as Total
FROM (Select Region, Category, Amount
From RawData
Where Category = 'Z') sq
PIVOT
(Sum (Amount)
FOR Region IN (South, NorthEast, MidWest, West)
) AS pt

-- Dynamic CrossTabs with Multiple Assignment Variable and Pivot Method
-- Appending to a variable within a query to dynamically generate the column names

DECLARE @SQLStr NVARCHAR(1024)
SET @SQLStr = ''
SELECT @SQLStr = @SQLStr + [a].[Column] + ', '
FROM
(SELECT DISTINCT Region as [Column]
FROM RawData ) as a

SET @SQLStr = Left(@SQLStr, Len(@SQLStr) - 1)

SET @SQLStr = 'SELECT Category, '
+ @SQLStr
+ ' FROM (Select Category, Region, Amount from RawData) sq PIVOT (Sum (Amount) FOR Region IN ('
+ @SQLStr
+ ')) AS pt'
PRINT @SQLStr

EXEC sp_executesql @SQLStr

---------------------------------------------------------------
-- Calculating Cummulative Sums (Running Totals)

-- Simple Correlated Subquery Method
-- Groups same DateTime data together
SELECT SalesDate, Amount,
(SELECT SUM(Amount)
FROM RawData
WHERE SalesDate <= R.SalesDate ) as Balance
FROM RawData R
ORDER BY SalesDate


-- Cursor based - Improved Solution
ALTER TABLE RawData
ADD Balance INT
ALTER TABLE RawData
ADD ID INT IDENTITY NOT NULL PRIMARY KEY

-- Post some balances
UPDATE RawData
SET Balance =
(SELECT SUM(Amount)
FROM RawData
WHERE SalesDate <= R.SalesDate )
FROM RawData R
WHERE SalesDate <= '4/1/2006'


-- Cursor - 5 steps
DECLARE
@BeginningBalanceDate DateTime,
@BeginningBalanceAmount INT,
@CurrentBalance INT,
@CurrentID INT,
@CurrentAmount INT

SELECT
@BeginningBalanceDate = SalesDate,
@CurrentBalance = Balance
FROM RawData
WHERE SalesDate =
(SELECT Max(SalesDate) -- Identify the last good balance date
FROM RawData
WHERE SalesDate < -- row prior to earliest row with null balance
(SELECT Min(SalesDate)
FROM RawData
WHERE Balance IS NULL))

-- 1 Declare the cursor
-- Select all rows following last row with a good balance
-- (this will handle gaps in the balance)
DECLARE cBalance CURSOR FAST_FORWARD READ_ONLY
FOR SELECT ID, Amount
FROM RawData
WHERE SalesDate > @BeginningBalanceDate
ORDER BY SalesDate
-- 2 Open the Cursor
OPEN cBalance
-- 3 Prime the Cursor, then loop
FETCH cBalance INTO @CurrentID, @CurrentAmount -- prime the cursor
WHILE @@Fetch_Status = 0
BEGIN
SET @CurrentBalance = @CurrentBalance + IsNull(@CurrentAmount, 0)
UPDATE RawData
SET Balance = @CurrentBalance
WHERE ID = @CurrentID
PRINT @CurrentBalance
-- 3
FETCH cBalance INTO @CurrentID, @CurrentAmount -- fetch next
END
-- 4 Close
CLOSE cBalance
-- 5 Clean up Memory
DEALLOCATE cBalance

-- view the result
SELECT * FROM RawData ORDER BY SalesDate







Read Users' Comments (0)

sub queries

Posted in Labels: | at 10:13 AM

-----------------------------------------------------------
-- Total Training for SQL Server 2005
--
-- Part 2 - Selecting Data
-- Lesson 3 - Working with Subqueries
--
-- Paul Nielsen
-----------------------------------------------------------

-----------------------------------------------------------
-- Simple Subqueries
-- Coorelated Subqueries

-----------------------------------------------------------
-- Using Subqueries as Expressions

SELECT (SELECT 'Paul') AS SubqueryValue

USE CHA2

SELECT BaseCampID
FROM dbo.BaseCamp
WHERE Name = 'Ashville'

-- Find all Tours that depart from Ashville

-- Join Version
SELECT Tour.Name
FROM dbo.Tour
JOIN dbo.BaseCamp
ON Tour.BaseCampID = BaseCamp.BaseCampID
WHERE BaseCamp.Name = 'Ashville';

-- Where = (Scalar Subquery) Version
SELECT Tour.Name
FROM dbo.Tour
WHERE BaseCampID =
(SELECT BaseCampID
FROM dbo.BaseCamp
WHERE Name = 'Ashville');

-- Common Table Expression Version
WITH BC (BaseCampID)
AS (SELECT BaseCampID
FROM dbo.BaseCamp
WHERE Name = 'Ashville')
SELECT Tour.Name
FROM dbo.Tour
WHERE BaseCampID =
(SELECT BaseCampID FROM BC)


-----------------------------------------------------------
-- Including Subqueries as Lists

-- Where IN (List Subquery) Version
SELECT Tour.Name
FROM dbo.Tour
WHERE BaseCampID IN
(SELECT BaseCampID
FROM dbo.BaseCamp
WHERE Region = 'NC');

-- Where NOT in (List Subquery)
USE OBXKites

SELECT ProductName, ProductID
FROM dbo.Product
WHERE ProductID NOT IN
(SELECT TOP 10 ProductID
FROM dbo.Product
ORDER BY ProductID)


----------------------------------------------
-- Adding Subqueries as Derived Tables

-- How many children has each mother born?
USE Family

SELECT PersonID, FirstName, LastName, Children
FROM dbo.Person
JOIN (SELECT MotherID, COUNT(*) AS Children
FROM dbo.Person
WHERE MotherID IS NOT NULL
GROUP BY MotherID) ChildCount
ON Person.PersonID = ChildCount.MotherID
ORDER BY Children DESC;


-----------------------------------------------------------
-- Working with Correlated Subqueries

USE CHA2

-- Who has gone on an event outside thier state?
SELECT * FROM dbo.BaseCamp

-- this code assumes the data has been converted only once and the base campes are ID 1-4

UPDATE dbo.BaseCamp SET Region = 'NC' Where BaseCampID = 1
UPDATE dbo.BaseCamp SET Region = 'NC' Where BaseCampID = 2
UPDATE dbo.BaseCamp SET Region = 'BA' Where BaseCampID = 3
UPDATE dbo.BaseCamp SET Region = 'FL' Where BaseCampID = 4
UPDATE dbo.BaseCamp SET Region = 'WV' Where BaseCampID = 5

UPDATE dbo.Customer SET Region = 'ND' WHERE CustomerID = 1
UPDATE dbo.Customer SET Region = 'NC' WHERE CustomerID = 2
UPDATE dbo.Customer SET Region = 'NJ' WHERE CustomerID = 3
UPDATE dbo.Customer SET Region = 'NE' WHERE CustomerID = 4
UPDATE dbo.Customer SET Region = 'ND' WHERE CustomerID = 5
UPDATE dbo.Customer SET Region = 'NC' WHERE CustomerID = 6
UPDATE dbo.Customer SET Region = 'NC' WHERE CustomerID = 7
UPDATE dbo.Customer SET Region = 'BA' WHERE CustomerID = 8
UPDATE dbo.Customer SET Region = 'NC' WHERE CustomerID = 9
UPDATE dbo. Customer SET Region = 'FL' WHERE CustomerID = 10

-- location matrix
SELECT DISTINCT Customer.Region, BaseCamp.Region
FROM dbo.Customer
JOIN dbo.Event_mm_Customer
ON Customer.CustomerID = Event_mm_Customer.CustomerID
JOIN dbo.Event
ON Event_mm_Customer.EventID = Event.EventID
JOIN dbo.Tour
ON Event.TourID = Tour.TourID
JOIN dbo.BaseCamp
ON Tour.BaseCampID = BaseCamp.BaseCampID
WHERE Customer.Region IS NOT NULL
GROUP BY Customer.Region, BaseCamp.Region
ORDER BY Customer.Region, BaseCamp.Region

-- who lives near a base camp?
USE CHA2
SELECT C.FirstName, C.LastName, C.Region
FROM dbo.Customer C
WHERE EXISTS
(SELECT *
FROM dbo.BaseCamp B
WHERE B.Region = C.Region)
ORDER BY LastName, FirstName

-- same query as a join
SELECT DISTINCT C.FirstName, C.LastName, C.Region, B.Region
FROM dbo.Customer C
JOIN dbo.BaseCamp B
ON C.Region = B.Region
ORDER BY LastName, FirstName

-- Who attended an event in their home region?
USE CHA2
SELECT DISTINCT C.FirstName, C.LastName, C.Region AS Home
FROM dbo.Customer C
JOIN dbo.Event_mm_Customer E
ON C.CustomerID = E.CustomerID
WHERE C.Region IS NOT NULL
AND EXISTS
(SELECT *
FROM dbo.Event
JOIN dbo.Tour
ON Event.TourID = Tour.TourID
JOIN dbo.BaseCamp
ON Tour.BaseCampID = BaseCamp.BaseCampID
WHERE BaseCamp.Region = C.Region
AND Event.EventID = E.EventID)

-- Same query as a join
SELECT Distinct C.FirstName, C.LastName, C.Region AS Home,
Tour.Name, BaseCamp.Region
FROM dbo.Customer C
JOIN dbo.Event_mm_Customer
ON C.CustomerID = Event_mm_Customer.CustomerID
JOIN dbo.Event
ON Event_mm_Customer.EventID = Event.EventID
JOIN dbo.Tour
ON Event.TourID = Tour.TourID
JOIN dbo.BaseCamp
ON Tour.BaseCampID = BaseCamp.BaseCampID
AND C.Region = BaseCamp.Region
AND C.Region IS NOT NULL
ORDER BY C.LastName

Go


-----------------------------------------------------------
-- Solving for X with Relational Division

-- Exact Relational Division
go
USE OBXKites
go
DECLARE @OrderNumber INT

-- First Person orders exactly all toys
EXEC pOrder_AddNew
@ContactCode = '110',
@EmployeeCode = '120',
@LocationCode = 'CH',
@OrderDate='6/1/2002',
@OrderNumber = @OrderNumber output

EXEC pOrder_AddItem
@OrderNumber = @OrderNumber, -- must be a valid, open order. Get OrderNumber from pOrder_AddNew
@Code = '1049', -- if NULL then non-stock Product text description
@NonStockProduct = NULL,
@Quantity = 1, -- required
@UnitPrice = NULL, -- if Null then the sproc will lookup the correct current price for the customer
@ShipRequestDate = NULL, -- if NULL then today
@ShipComment = NULL

EXEC pOrder_AddItem @OrderNumber, '1050', NULL, 1, NULL, NULL, NULL

-- Second Person - has ordered exactly all toys - 1050 twice
EXEC pOrder_AddNew '111', '119', 'JR', '6/1/2002', @OrderNumber output
EXEC pOrder_AddItem @OrderNumber, '1049', NULL, 1, NULL, NULL, NULL
EXEC pOrder_AddItem @OrderNumber, '1050', NULL, 1, NULL, NULL, NULL

EXEC pOrder_AddNew '111', '119', 'JR', '6/1/2002', @OrderNumber output
EXEC pOrder_AddItem @OrderNumber, '1050', NULL, 1, NULL, NULL, NULL

-- Third Person - has order all toys plus some others
EXEC pOrder_AddNew '112', '119', 'JR', '6/1/2002', @OrderNumber output
EXEC pOrder_AddItem @OrderNumber, '1049', NULL, 1, NULL, NULL, NULL
EXEC pOrder_AddItem @OrderNumber, '1050', NULL, 1, NULL, NULL, NULL
EXEC pOrder_AddItem @OrderNumber, '1001', NULL, 1, NULL, NULL, NULL
EXEC pOrder_AddItem @OrderNumber, '1002', NULL, 1, NULL, NULL, NULL

-- Fourth Person - has order one toy
EXEC pOrder_AddNew '113', '119', 'JR', '6/1/2002', @OrderNumber output
EXEC pOrder_AddItem @OrderNumber, '1049', NULL, 1, NULL, NULL, NULL

SELECT *
FROM dbo.[order]
WHERE OrderDate = '6/1/2002'

--Relational Division with remainder
-- Is number of toys ordered...
SELECT Contact.ContactCode
FROM dbo.Contact
JOIN dbo.[Order]
ON Contact.ContactID = [Order].ContactID
JOIN dbo.OrderDetail
ON [Order].OrderID = OrderDetail.OrderID
JOIN dbo.Product
ON OrderDetail.ProductID = Product.ProductID
JOIN dbo.ProductCategory
ON Product.ProductCategoryID = ProductCategory.ProductCategoryID
WHERE ProductCategory.ProductCategoryName = 'Toy'
GROUP BY Contact.ContactCode
HAVING COUNT(DISTINCT Product.Code) =
-- equal to number of toys available?
(SELECT Count(Code)
FROM dbo.Product
JOIN dbo.ProductCategory
ON Product.ProductCategoryID
= ProductCategory.ProductCategoryID
WHERE ProductCategory.ProductCategoryName = 'Toy')

-- Exact Relational Division
-- Is number of all products ordered...
SELECT Contact.ContactCode
FROM dbo.Contact
JOIN dbo.[Order]
ON Contact.ContactID = [Order].ContactID
JOIN dbo.OrderDetail
ON [Order].OrderID = OrderDetail.OrderID
JOIN dbo.Product
ON OrderDetail.ProductID = Product.ProductID
JOIN dbo.ProductCategory P1
ON Product.ProductCategoryID = P1.ProductCategoryID
JOIN
-- and number of toys ordered
(SELECT Contact.ContactCode, Product.Code
FROM dbo.Contact
JOIN dbo.[Order]
ON Contact.ContactID = [Order].ContactID
JOIN dbo.OrderDetail
ON [Order].OrderID = OrderDetail.OrderID
JOIN dbo.Product
ON OrderDetail.ProductID = Product.ProductID
JOIN dbo.ProductCategory
ON Product.ProductCategoryID = ProductCategory.ProductCategoryID
WHERE ProductCategory.ProductCategoryName = 'Toy') ToysOrdered
ON Contact.ContactCode = ToysOrdered.ContactCode
GROUP BY Contact.ContactCode
HAVING COUNT(DISTINCT Product.Code) =
-- equal to number of toys available?
(SELECT Count(Code)
FROM dbo.Product
JOIN dbo.ProductCategory
ON Product.ProductCategoryID
= ProductCategory.ProductCategoryID
WHERE ProductCategory.ProductCategoryName = 'Toy')
-- AND equal to the total number of any product ordered?
AND COUNT(DISTINCT ToysOrdered.Code) =
(SELECT Count(Code)
FROM dbo.Product
JOIN dbo.ProductCategory
ON Product.ProductCategoryID
= ProductCategory.ProductCategoryID
WHERE ProductCategory.ProductCategoryName = 'Toy')


Read Users' Comments (0)

Source Codes - SQL

Posted in Labels: | at 10:11 AM

-----------------------------------------------------------
-- Total Training for SQL Server 2005
--
-- Part 2 - Selecting Data
-- Lesson 2 - Merging Data with Joins and Unions
--
-- Paul Nielsen
-----------------------------------------------------------

USE CHA2

-----------------------------------------------------------
-- Working with Inner Joins

-- Two Table Join
SELECT Tour.Name, Tour.BaseCampID,
BaseCamp.BaseCampID, BaseCamp.Name
FROM dbo.Tour
JOIN dbo.BaseCamp
ON Tour.BaseCampID = BaseCamp.BaseCampID


-- Legacy Style Join
SELECT Tour.Name, Tour.BaseCampID,
BaseCamp.BaseCampID, BaseCamp.Name
FROM dbo.Tour, dbo.BaseCamp
WHERE Tour.BaseCampID = BaseCamp.BaseCampID

-- Multiple Table Join
USE OBXKites

SELECT LastName, FirstName, ProductName
FROM dbo.Contact
JOIN dbo.[Order]
ON Contact.ContactID = [Order].ContactID
JOIN dbo.OrderDetail
ON [Order].OrderID = OrderDetail.OrderID
JOIN dbo.Product
ON OrderDetail.ProductID = Product.ProductID
JOIN dbo.ProductCategory
ON Product.ProductCategoryID = ProductCategory.ProductCategoryID
WHERE ProductCategoryName = 'Kite'
ORDER BY LastName, FirstName


---------------------------------------------------------------
-- Using Left Outer Joins

USE OBXKites

-- All Primary table - some secondary table
SELECT ContactCode, OrderNumber
FROM dbo.Contact
LEFT OUTER JOIN dbo.[Order]
ON [Order].ContactID = Contact.ContactID
ORDER BY ContactCode

-- Optional FK to look-up
SELECT OrderNumber, OrderPriorityName
FROM dbo.[Order]
Left Outer Join dbo.OrderPriority
ON [Order].OrderPriorityID = OrderPriority.OrderPriorityID

---------------------------------------------------------------
-- Multiplying Data with Cross Joins

USE CHA2

SELECT GuideID, CustomerID
FROM dbo.Guide
CROSS JOIN dbo.Customer

---------------------------------------------------------------
-- Referencing Self-Joins

USE Family

-- Check the PersonID of Audry
SELECT PersonID
FROM dbo.Person
WHERE LastName = 'Halloway'
AND FirstName = 'Audry'

-- Select all of Audry's children
SELECT Person.PersonID, Person.FirstName,
Person.MotherID
FROM dbo.Person
JOIN dbo.Person AS Mother -- second reference with Alias
ON Person.MotherID = Mother.PersonID
WHERE Mother.LastName = 'Halloway'
AND Mother.FirstName = 'Audry'

-- Select all birth w/ child, mother, & father
SELECT CONVERT(NVARCHAR(15),Person.DateofBirth,1) AS Date,
Person.FirstName AS Name, Person.Gender AS G,
ISNULL(F.FirstName + ' ' + F.LastName, ' * unknown *')
as Father,
M.FirstName + ' ' + M.LastName as Mother
FROM dbo.Person -- Person Reference
Left Outer JOIN dbo.Person F -- Father Reference
ON Person.FatherID = F.PersonID
INNER JOIN dbo.Person M -- Mother Reference
ON Person.MotherID = M.PersonID
ORDER BY Person.DateOfBirth

---------------------------------------------------------------
-- Working with Complex Joins

USE Family

SELECT Person.FirstName + ' ' + Person.LastName,
Twin.FirstName + ' ' + Twin.LastName as Twin,
Person.DateOfBirth
FROM dbo.Person
JOIN dbo.Person Twin
ON Person.PersonID <> Twin.PersonID
AND Person.MotherID = Twin.MotherID
AND Person.DateOfBirth = Twin.DateOfBirth
WHERE Person.DateOfBirth IS NOT NULL

---------------------------------------------------------------
-- Finding the Set-Difference

-- Find all Customers w/o orders
USE OBXKites

SELECT ContactCode, FirstName, LastName, OrderNumber
FROM dbo.Contact
LEFT OUTER JOIN dbo.[Order]
ON [Order].ContactID = Contact.ContactID
WHERE [Order].OrderID IS NULL
ORDER BY ContactCode

-- Find all products w/o orders
SELECT ProductName
FROM dbo.Product P
LEFT OUTER JOIN dbo.OrderDetail OD
ON P.ProductID = OD.ProductID
WHERE OD.ProductID IS NULL


---------------------------------------------------------------
-- Stacking Data with Unions

SELECT FirstName as [First], LastName as [Last], 'CHA2' as Source
FROM CHA2.dbo.Customer
UNION
SELECT FirstName, LastName, 'OBXKites'
FROM OBXKites.dbo.Contact
ORDER BY LastName, FirstName


-- Union ALL / Distinct
SELECT LastName
FROM CHA2.dbo.Customer
UNION --DISTINCT by default
SELECT LastName
FROM OBXKites.dbo.Contact
ORDER BY LastName

SELECT LastName
FROM CHA2.dbo.Customer
UNION ALL
SELECT LastName
FROM OBXKites.dbo.Contact
ORDER BY LastName

-- Union Intersect
SELECT LastName
FROM CHA2.dbo.Customer
INTERSECT
SELECT LastName
FROM OBXKites.dbo.Contact
ORDER BY LastName

-- Union Except (Difference Union)
SELECT LastName
FROM CHA2.dbo.Customer
EXCEPT
SELECT LastName
FROM OBXKites.dbo.Contact
ORDER BY LastName

Read Users' Comments (0)

Source Codes

Posted in | at 10:08 AM

-----------------------------------------------------------
-- Total Training for SQL Server 2005
--
-- Part 2 - Selecting Data
-- Lesson 1 - Working with Single Table Queries
--
-- Paul
-----------------------------------------------------------


-----------------------------------------------------------
-- Simple SQL statement
SELECT 'SQL Rocks';

-- Toggle Result Pane - ctrl R

-- Logical Flow (PowerPoint)
-- Syntax Flow (BOL)
-- Physical Flow (Query Execution Plan)

-- Switch Default Database
USE CHA2;

SELECT *
FROM Tour;

-- Statement Terminator ;
-- Batch Terminator GO

-----------------------------------------------------------
-- Logical Flow - FROM Clause
/*
SELECT *
FROM
tables(s)
subqueries (as derived tables) AS NamedRangeAlias
common table expressions (CTE)
views
table-valued user-defined functions
distributed data sources (OpenQuery)
XML data sources (XQuery)
*/

go

-- Fully Qualified - Four Part Name

-- SELECT *
-- FROM Server.Database.Schema.Object;

SELECT *
FROM dbo.Tour;
SELECT *
FROM dbo.Tour;


-----------------------------------------------------------
--- Where Clause

/*
SELECT *
FROM data source(s)
WHERE condition
*/

-- a boolean condition
SELECT *
FROM dbo.Tour
WHERE 1 = 1;

-- Comparison Operator
SELECT *
FROM dbo.Tour
WHERE TourID <= 3;

-- Between
SELECT *
FROM dbo.Tour
WHERE TourID BETWEEN 2 AND 4;

SELECT *
FROM dbo.Tour
WHERE TourID >= 2
AND TourID <= 4;

-- In Condition
SELECT *
FROM dbo.Tour
WHERE TourID IN (1, 3); -- (Subquery)

UPDATE dbo.BaseCamp SET Region = 'NC' WHERE Name = 'Ashville';
UPDATE dbo.BaseCamp SET Region = 'NC' WHERE Name = 'Cape Hatteras';
UPDATE dbo.BaseCamp SET Region = 'BA' WHERE Name = 'Freeport';
UPDATE dbo.BaseCamp SET Region = 'FL' WHERE Name = 'Ft Lauderdale';
UPDATE dbo.BaseCamp SET Region = 'WV' WHERE Name = 'West Virginia';

SELECT *
FROM dbo.BaseCamp
WHERE Region IN ('NC', 'WV');

-- Like Condition
/*
multiple characters %
single character _
match in range [ ]
match not in range [^ ]
*/

SELECT *
FROM dbo.BaseCamp
WHERE Name LIKE 'F%';

SELECT *
FROM dbo.Customer
WHERE LastName LIKE '%son';

SELECT *
FROM dbo.Customer
WHERE FirstName LIKE '[ABC]%';

SELECT *
FROM dbo.Customer
WHERE FirstName LIKE '[^ABC]%';

-- Multiple Conditions
SELECT *
FROM dbo.Customer
WHERE
CustomerTypeID = 1
OR
FirstName LIKE 'Dav%'
AND
CustomerID = 14;

SELECT *
FROM dbo.Customer
WHERE
(CustomerTypeID = 1
OR
FirstName LIKE 'Dav%')
AND
CustomerID = 14;

-----------------------------------------------------------
-- Columns, Expressions Scalar Functions

SELECT 'Expression' as Alias, 123 as [Order]

/* Expressions
Numeric constants 1, 2, 3
String literals 'LastName', 'Employee: ', 'Life''s Great!'
Dates '1/6/80', 'Jan 6, 1980', '19800106'
Mathematical operators *, /, % (remainder), +, -(in order of precedence)
String operator (concatenation) +
Bitwise operators and &, or |, exclusive or ^, not ~
Columns LastName, PrimaryKeyID
Case Expressions CASE Column1
WHEN 1 THEN 'on'
ELSE 'off'
END AS Status
Subqueries (Select 3)
User-defined variables @MyVariable
Global variables @@Error
Scalar functions GetDate(), SysUser()
User-defined functions dbo.MyUDF()
*/

-- Addition vs. Concatenation
SELECT 123 + 456 as Addition, '123' + 'abc' as Concatenation


-----------------------------------------------------------
-- CASE Expressions

-- Simple CASE Expression

USE OBXKites

SELECT CustomerTypeName, [Default],
CASE [Default]
WHEN 1 THEN 'default type'
WHEN 0 THEN 'alternate'
ELSE '-'
End as AssignStatus
From dbo.CustomerType

-- Boolean Case Expression
/*

CASE
WHEN BooleanCondition THEN Expression
WHEN BooleanCondition THEN Expression
...
ELSE Expression
END AS Alias
-- from KillTheCursor!
UPDATE dbo.Detail
SET AdjAmount = DWM.Multiplier * ClientType.Multiplier *
CASE
WHEN Formula = 1 AND ExecOverRide = 0
THEN BaseRate * Amount * BaseMultiplier
WHEN (Formula = 1 OR Formula = 3 )AND ExecOverRide = 1
THEN BaseRate * Amount
WHEN Formula = 2
THEN BaseRate * Amount * (SELECT Value
FROM dbo.Variable
WHERE Name = 'AccRate')
WHEN (Formula = 3 AND ExecOverRide = 0)
THEN Amount * BaseMultiplier
END
FROM Detail
JOIN ActionCode
ON Detail.ActionCode = ActionCode.ActionCode
JOIN [Order]
ON [Order].OrderID = Detail.OrderID
JOIN Client
ON [Order].ClientID = Client.ClientID
Join ClientType
ON Client.ClientTypeID = ClientType.ClientTypeID
JOIN DayOfWeekMultiplier DWM
ON CAST(DatePart(dw,[Order].TransDate) as SMALLINT) = DWM.DayOfWeek
WHERE AdjAmount IS NULL

*/

-----------------------------------------------------------
-- Working with Nulls

SELECT 1 + NULL

-- IS NULL

-- WHERE Expression IS NULL

SELECT CASE
WHEN Null = NULL THEN 'True'
ELSE 'False'
END as '=null'

SELECT CASE
WHEN Null IS NULL THEN 'True'
ELSE 'False'
END as ' IS null'

-- Is Null
USE CHA2
SELECT FirstName, LastName, Nickname
FROM Customer
WHERE NickName IS NULL
ORDER BY LastName, FirstName

SELECT FirstName, LastName, Nickname
FROM Customer
WHERE NickName IS NOT NULL
ORDER BY LastName, FirstName

-- Coalesce()
SELECT COALESCE(NULL, 1+NULL, 1+2, 'abc')

-----------------------------------------------------------
-- Scalar Functions

-- Server Environment Functions
SELECT GETDATE()

SELECT GETDATE() AS 'Date',
DB_NAME() AS 'Database'

SELECT @@SERVERNAME

SELECT
SERVERPROPERTY ('ServerName') AS ServerName,
SERVERPROPERTY ('Edition') AS Edition,
SERVERPROPERTY ('EngineEdition') AS EngineEdition,
SERVERPROPERTY ('ProductLevel') AS ProductLevel

-- User Information Functions
SELECT
USER_NAME() AS 'User',
SUSER_SNAME() AS 'Login',
HOST_NAME() AS 'Workstation',
APP_NAME() AS 'Application'

-- Date/Time Functions

SELECT DATENAME(YEAR, GETDATE()) AS YEAR

UPDATE Guide
SET DateOfBirth = '9/4/58'
WHERE lastName = 'Frank'

SELECT LastName,
DATENAME(yy,DateOfBirth) AS [Year],
DATENAME(mm,DateOfBirth) AS [Month],
DATENAME(dd,DateOfBirth) AS [Day],
DATENAME(WEEKDAY, DateOfBirth) AS BirthDay
FROM Guide
WHERE DateOfBirth IS NOT NULL

SELECT DATEPART(DayofYear, GETDATE()) AS DayCount
SELECT DATEPART(dw, GETDATE()) AS DayWeek

-- Date Math

SELECT DATEDIFF(yy,'2005/10/22', GETDATE()) AS MarriedYears,
DATEDIFF(dd,'2005/10/22', GETDATE()) AS MarriedDays

SELECT DATEADD(hh,100, GETDATE()) AS [100HoursFromNow]

USE Family

SELECT Person.FirstName + ' ' + Person.LastName AS Mother,
DATEDIFF(yy, Person.DateOfBirth,
Child.DateOfBirth) AS Age,Child.FirstName
FROM Person
JOIN Person Child
ON Person.PersonID = Child.MotherID
ORDER By Age DESC

-- Paul's Recommended Date/time code:
-- return datetime - date only / midnight time
SELECT DATEADD(day, DATEDIFF(day, '20040101', CURRENT_TIMESTAMP), '20040101')

-- extract date only as string
SELECT Convert(CHAR(10), CURRENT_TIMESTAMP, 101 )

-- extract time only as string (with seconds)
SELECT substring(Convert(CHAR(19), CURRENT_TIMESTAMP, 120 ),12,8)

-- extract time only as string (without seconds)
SELECT substring(Convert(CHAR(19), CURRENT_TIMESTAMP, 120 ),12,5)

-- String Functions

SELECT SUBSTRING('abcdefg', 3, 2)

SELECT STUFF('abcdefg', 3, 2, '123')

SELECT STUFF('123456789', 4, 0, '-')
SELECT STUFF(STUFF('123456789', 4, 0, '-'), 7, 0, '-')

SELECT CHARINDEX('c', 'abcdefg', 1)

SELECT PATINDEX('%[cd]%', 'abdcdefg')

SELECT RIGHT('Nielsen',2) AS [Right], LEFT('Nielsen',2) AS 'Left'

SELECT LEN('Supercalifragilisticexpialidocious') AS LEN

SELECT RTRIM(' middle earth ') AS [RTrim], LTRIM(' middle earth ') AS [LTrim]

Select UPPER('one TWO tHrEe') AS [UpperCase], LOWER('one TWO tHrEe') AS [LowerCase]

-- Data Type Conversions

SELECT CAST('Away' AS NVARCHAR(5)) AS 'Tom Hanks'

SELECT CAST(123 AS NVARCHAR(15)) AS Int2String

SELECT GETDATE() AS RawDate,
CONVERT (NVARCHAR(25), GETDATE(), 100) AS Date100,
CONVERT (NVARCHAR(25), GETDATE(), 1) AS Date1

SELECT STR(123,5,2) AS [Str]


-----------------------------------------------------------
-- Order By

-- Column name or alias
SELECT FirstName, LastName as Last
FROM dbo.Customer
ORDER BY Last, FirstName;

-- Column Expression
SELECT LastName + ', ' + FirstName as name
FROM dbo.Customer
ORDER BY LastName + ', ' + FirstName;

-- Ordinal Position
SELECT *
FROM dbo.Customer
ORDER BY 3, 2;

SELECT *
FROM dbo.Event
ORDER BY DateBegin DESC;

-----------------------------------------------------------
-- Predicates

-- [All] Distinct
SELECT ALL FirstName
FROM dbo.Customer
WHERE FIRSTNAME IN ('David', 'Gary', 'Melissa')
ORDER BY FirstName

SELECT DISTINCT FirstName
FROM dbo.Customer
WHERE FIRSTNAME IN ('David', 'Gary', 'Melissa')
ORDER BY FirstName

-- Top
SELECT TOP 5 *
FROM dbo.Customer
WHERE CustomerTypeID IS NOT NULL
ORDER BY CustomerTypeID
SELECT TOP 5 WITH TIES *
FROM dbo.Customer
WHERE CustomerTypeID IS NOT NULL
ORDER BY CustomerTypeID

Read Users' Comments (0)

sql command

Posted in | at 10:02 AM

SELECT RecordID, CASE businessunit WHEN 'xont' THEN 'kasun' ELSE businessunit END AS Expr1,
CASE CreatedOn WHEN '10/1/2009 12:00:00 AM' THEN NULL ELSE createdon END AS createddate
FROM RD.Route

Read Users' Comments (0)

Warehouse Maintain...

Posted in | at 9:44 AM

Sp Update...


Add exectution type as well...

BEGIN
INSERT INTO [RD].[ProductWarehouse]
([BusinessUnit]
,[TerritoryCode]
,[WarehouseCode]
,[ProductCode]
,[ROL]
,[MinStock]
,[MaxStock]
,[EOQ]
,[ReorderMultiple]
,[LastStockTakeDate]
,[NoOfStockTakesPerYear]
,[DefaultLocationCode]
,[PrefSupplierCode]
,[CreatedBy]
,[CreatedOn]
,[ModifiedBy]
,[ModifiedOn])
VALUES
(@BusinessUnit
,@TerritoryCode
,@WarehouseCode
,@ProductCode
,@ROL
,@MinStock
,@MaxStock
,@EOQ
,@ReorderMultiple
,@LastStockTakeDate
,@NoOfStockTakesPerYear
,@DefaultLocationCode
,@PrefSupplierCode
,@CreatedBy
,getdate()
,@ModifiedBy
,getdate())
END

Read Users' Comments (1)comments

Subscribe to: Posts (Atom)