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.

Basics and some tips on writing joins in T-SQL

Link to article on Joins in T-SQl : Basics

sqlteam.com

Points
1) when using aggregates with joins take care to include the column, as aggregating on '*' i.e. the row, might return false result specially for columns with null values.
2) when executing multiple joins using the left outer joins may be useful as using inner join might not return some rows with NULL values in the intermediate table.