/*
//-------------------------------------------------------------------
/// File       : queries.sql
/// Version    : 1.0
/// Created    : 2008-01-08
/// Modified   : 2009-03-13
///
/// Author     : William Chang
/// Email      : william@babybluebox.com
/// Website    : http://www.babybluebox.com
///
/// Compatible : Microsoft SQL Server 8+
//-------------------------------------------------------------------
/// References:
/// http://www.sql-tutorial.net/
/// http://www.1keydata.com/sql/
///
/// Paging (Range):
/// http://vorg.ca/626-the-MS-SQL-equivalent-to-MySQLs-limit-command
/// http://codesnippets.joyent.com/posts/show/1225
/// http://itcave.net/sql/extracting-ranges-using-top
/// http://www.select-sql.com/mssql/how-to-make-limit-from-to-in-mssql-2005.html
//-------------------------------------------------------------------
*/

/* DateTime
//-------------------------------------------------------------------*/
'19561030'
'561030'
'10/30/1956'
'10/30/56'
'30 OCT 1956'
'30 OCT 56'
'OCT 30 1956'
'OCT 30, 1956'
'OCT 30, 56'
'OCTOBER 10, 1956'

/* TOP (MySQL: LIMIT)
//-------------------------------------------------------------------*/
SELECT TOP(10) * FROM users;

/*
Paging (MySQL: SELECT * FROM users LIMIT 3, 2;)
Start at 3 and return 2 rows.
Outer TOP equation: return(2) = 2
Inner TOP equation: startat(3) - 1 = 2
*/
SELECT TOP(2) *
FROM users
WHERE user_id NOT IN(
    SELECT TOP(2) user_id FROM users
);

/*
Paging (MySQL: SELECT * FROM users LIMIT 3, 2;)
Start at 3 and return 2 rows.
Outer TOP equation: return(2) = 2
Inner TOP equation: startat(3) + return(2) - 1 = 5
*/
SELECT * FROM(
    SELECT TOP(2) * FROM(
        SELECT TOP(5) *
        FROM users
        ORDER BY user_id ASC
    )
    AS table1
    ORDER BY user_id DESC
)
AS table2
ORDER BY user_id ASC;

/* EXEC
//-------------------------------------------------------------------*/

/* Stored Procedure */
EXEC dbo.storedprocedure_name
    @title = 'foo',
    @content = 'goo'

/* SELECT
//-------------------------------------------------------------------*/
SELECT *
FROM locations
WHERE location_id NOT IN(
    SELECT user_organization_location_id FROM user_profiles
);

SELECT
    user_id,
    user_name_first + ' ' + user_name_last AS user_name
FROM users

/* WITH
//-------------------------------------------------------------------*/

/*
Hierarchy:
http://www.webinade.com/web-development/creating-recursive-sql-calls-for-tables-with-parent-child-relationships
*/
WITH tbl_hierarchy(
    content_header,
    content_id,
    content_parent_id,
    iteration
) AS(
    SELECT content_header, content_id, content_parent_id, 0
    FROM contents
    WHERE content_parent_id = 0
    UNION ALL
    SELECT t1.content_id, t2.content_parent_id, t1.iteration + 1
    FROM tbl_hierarchy AS t1, tbl_hierarchy AS t2
    WHERE t1.content_parent_id = t2.content_id
)
SELECT child_header FROM tbl_hierarchy

/*
Hierarchy:
http://www.webinade.com/web-development/creating-recursive-sql-calls-for-tables-with-parent-child-relationships
*/
WITH tbl_hierarchy(
    content_header,
    content_id,
    content_parent_id,
) AS(
    SELECT content_header, content_id, content_parent_id
    FROM contents
    WHERE content_parent_id = 0
    UNION ALL
    SELECT t1.content_header, t1.content_id, t1.content_parent_id
    FROM
        contents AS t1
        INNER JOIN @tbl_hierarchy AS t2 ON t2.content_id = t1.content_parent_id
)
SELECT content_id FROM tbl_hierarchy

/*
XML Hierarchy:
http://msdn.microsoft.com/en-us/library/aa175807(SQL.80).aspx
*/
SELECT
    parent.content_header AS header,
    child.content_header AS header
FROM
    contents AS parent
    INNER JOIN contents AS child ON parent.content_id = child.content_parent_id
WHERE
    child.content_type IN (1, 2)
    AND parent.content_status = 1
    AND child.content_status = 1
    AND parent.content_deleted = 0
    AND child.content_deleted = 0
ORDER BY parent.content_id
FOR XML AUTO

/* DELETE FROM
//-------------------------------------------------------------------*/
DELETE FROM user_profiles
DBCC CHECKIDENT(user_profiles, RESEED, 0) WITH NO_INFOMSGS

DELETE FROM locations
WHERE location_id NOT IN(
    SELECT user_organization_location_id FROM user_profiles
);

/* TRUNCATE (without foreign keys)
//-------------------------------------------------------------------*/
TRUNCATE TABLE user_profiles
DBCC CHECKIDENT(user_profiles, RESEED, 0) WITH NO_INFOMSGS