Retrieve the days of a month which are not populated in a column

The following query retrieves the days of a particular month which are not used in a particular column.
Please change the column name and tablename alongwith the month and year to give expected results. Also this can be used to create a function or procedure by replacing the variables by parameters.


Create table #t1
(
checkdate datetime
)

DECLARE @checkdate AS DATETIME;
DECLARE @month AS varchar(2);
DECLARE @year AS varchar(4);
DECLARE @tablename AS varchar(100);
DECLARE @columnname AS varchar(100);

set @month = '06'
set @year = '2008'
set @tablename = 'tablename'
set @columnname = 'columndate'
select @checkdate = Convert(DATETIME,(select @year + '-' + @month + '-01'))


--select @checkdate

WHILE MONTH(@checkdate) = @month
BEGIN
insert into #t1
select @checkdate ;
select @checkdate = dateadd(dd,1,@checkdate) ;
END

execute ('select DAY(checkdate) , checkdate from #t1' +
' LEFT JOIN ('+
'select distinct DAY('+ @columnname+') as [d1] from '+ @tablename +'
where YEAR('+ @columnname+') = '+ @year +' and MONTH('+ @columnname+') = '+ @month +
') split '+
'ON ( day(#t1.checkdate) = split.[d1] ) '+
'where split.[d1] is NULL '+
'order by checkdate' )


/*
select DAY(checkdate), checkdate from #t1
where DAY(checkdate) not in (
select distinct DAY(columnname) from tblsplitmthis
where YEAR(columnname) = @year and MONTH(columnname) = @month
) from #t1
*/

drop table #t1

Finding and listing columns with default values in a database

This one is a handy script I found on a blog.


SELECT SO.NAME AS "Table Name", SC.NAME AS "Column Name", SM.TEXT AS "Default Value"
FROM dbo.sysobjects SO INNER JOIN dbo.syscolumns SC ON SO.id = SC.id
INNER JOIN dbo.syscomments SM ON SC.cdefault = SM.id
WHERE SO.xtype = 'U'
ORDER BY SO.[name], SC.colid


Read more at mssqltips.com

The power of Cross (Cartesian) Joins

This is a very good example of where the Cartesian (Cross) Join may be very helpful. Though proper understanding and enough experimentation should be done to try it out.
Jeff @ SQLTeam.com

It explains an example where a Join may be required to return some value ['0'] in this case when there are no matching records found. for e.g. return 0 for sales of all combination of products and stores which dont have a value i.e. null.


SELECT S.Store, P.Product, ISNULL(C.TotalSales,0) as TotalSales
FROM Stores S
CROSS JOIN Products P
LEFT OUTER JOIN
(SELECT Store, Product, SUM(Sales) as TotalSales
FROM Sales
GROUP BY Store, Product) C
ON
S.Store = C.Store AND
P.Product = C.Product

Also Refer:
Join Clause in SQl

Image representation of Joins in SQL

Found a similar blog with name
Visual Explanation of SQL JOINS

Deleting duplicate records.

Its a nice post on deleting duplicate records. She has mentioned 3 different methods with their advantages and disadvantages.

SQLteam.com

Object Oriented approach for SQL

Implementing Table inheritance in SQL Server.
SQlTeam.com

Implementing Table Interfaces
SQLTeam.com

The Golden Rule of Data Manipulation

This is a very good post about data design.
SQLTeam Golden Rule Of Data manipulation

It is suggested that Concatenating is easier than parsing scripts. Thus wherever possible one must segregate the data. e.g Full name phone numbers etc.

Links Helpful for SQL Join

Article on Join Clause In SQL


Refered sites :
http:/en.wikipedia.org

MSDN library (Transact SQL JOINS)

Craig Freedman's SQL SERVER BLOG (Introduction & details Quite good)

Ward Pond's SQL SERVER BLOG (Quite good for Introduction & details )

Some more pages of interests:
Joins Visual Explanation
MSDN search (SQL JOINS)
Advanced Query tuning Concepts

JOIN clause in SQL

Referenced Links

A JOIN clause in SQL combines records from two tables in a relational database and results in a new (temporary) table, also called a "joined table". SQL specifies two types of joins: inner and outer.

A predicate, i.e. a condition used to join two tables, is used to create a join between two tables.
SQL:2003 specifies two different syntactical ways to express joins.
The ' EXPLICIT JOIN notation' uses the keyword join.
The 'IMPLICITJOIN notation' lists the tables for joining in the FROM clause of a SELECT statement, using commas to separate them.

A> INNER JOIN : Represents the default join type, and occurs most commonly in applications.
It returns the all the records from the tables which satisfy the predicate.
Inner joins are fully commutative. “A inner join B” and “B inner join A” are equivalent.
One can further classify inner joins as equi-joins, as natural joins, or as cross-joins.
eg : Explicit Inner join :

SELECT
table1.*, table2.*
FROM table1
INNER JOIN table2
ON ( table2.table1ID = table1.ID ) -- predicate
-- Here table2.table1ID refers to (FK) table1.ID column.

Thus this query would return all the rows in table 2 whose table1ID matches the values of table1.ID, alongwith the corresponding table1 rows.

Implicit Inner Join:

SELECT table1.*, table2.*
FROM table1, table2
where ( table2.table1ID = table1.ID ) -- predicate

This query would return the same results as the one above.


Types Of Inner JOIN :
1> EQUI-JOIN [equijoin, theta join] :
is specific type of comparator-based join which uses only equality based join-predicates.
eg :
SELECT
table1.*, table2.*
FROM table1
INNER JOIN table2
ON ( table2.table1ID = table1.ID ) -- predicate
-- Here only the equality operator is used.


2) Natural join :
A natural join offers a further specialization of equi-joins. The join predicate arises implicitly by comparing all columns in both tables that have the same column-name in the joined tables. The resulting joined table contains only one column for each pair of equally-named columns.

eg: consider both tables have only one column with same name say com_col , then.

SELECT *
FROM table1
NATURAL JOIN table2

will return the following structure:

table1.cols, com_col , table2.cols

3) CROSS JOIN [cartesian join] : Provides the foundation upon which all types of inner joins operate. A cross join returns the cartesian product of the sets of records from the two joined tables. Thus it equates to an inner join where the join-condition always evaluates to True. i.e. it returns all the rows in one table for every row in the another.
eg:
SELECT * FROM table1 CROSS JOIN table2 -- Explicit

SELECT * FROM table1,table2

Both the queries would return rows equal to the product of rows in both tables.





B> OUTER JOIN :

An outer join does not require each record in the two joined tables to have a matching record in the other table. The joined table retains each record — even if no other matching record exists. Outer joins subdivide further into left outer joins, right outer joins, and full outer joins, depending on which table(s) one retains the rows from (left, right, or both).

Full outer joins are commutative. In addition, “A left outer join B” and “B right outer join A” are equivalent.

1> LEFT OUTER JOIN :
Its result contains all the rows satisfying the predicate and also all the remaining rows from the left table for which there are no matching records in the right table

eg:
SELECT
table1.*, table2.*
FROM table1
LEFT OUTER JOIN table2
ON ( table2.table1ID = table1.ID )
-- Here table2.table1ID refers to (FK) table1.ID column.

This query would return all the rows in table1 but only matching rows from table2. For all the rows in table1, that do not have corresponding rows in table2, the values for table2 columns will be NULL.

2> RIGHT OUTER JOIN :
This is similar to the LEFT OUTER JOIN, except that it will return all the rows from the table on right, but only the rows from left table which have matching values in the right table.

eg:
SELECT
table1.*, table2.*
FROM table1
RIGHT OUTER JOIN table2
ON ( table2.table1ID = table1.ID )
-- Here table2.table1ID refers to (FK) table1.ID column.

Now in this case all the rows from table2 two will be returned, whereas from table1 only those rows will be populated which will have matching values in table2.


3> FULL OUTER JOIN :
The result in this case combines the results of both left and right outer joins. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.

eg:
SELECT
table1.*, table2.*
FROM table1
FULL OUTER JOIN table2
ON ( table2.table1ID = table1.ID )
-- Here table2.table1ID refers to (FK) table1.ID column.

In this case all the rows from table1 as well as from table2 are returned. Also the rows which dont have any matching values in the other table will be populated with null values.
i.e. for the rows from table1 which do not have corresponding values in table2, will have the columns from table2 poulated as NULL.



Referenced Links