SQL Querys...

To retrive last one month or last few days ..means u can use this query

if u have table ( Id , Name , date , City ) means ...
The query is ..

SELECT Id, Name, Date, City FROM tblTest
WHERE (Date > DATEADD(day, - 30, GETDATE()))

------------------------------------------------------------

To retrive recently Updated row's data means u can use this qry

SELECT userID, username FROM Tbl_Test
WHERE userID = ( SELECT MAX(userid) FROM Tbl_Test )

i used this qry in my application like below one ..

SELECT Id, Name, Phone, Email, DonorIp, DateofRegFROM Tbl_DonorWHERE
(Id = (SELECT MAX(Id) AS Expr1 FROM Tbl_Donor AS Tbl_Donor_1))

----------------------------------------------------------------

To Delete More then one Row using Primary Key ....


delid &= id.Text & ","
delid &= "0"
obj.SaiExcuteNonQry("DELETE FROM Tbl_Appeal where Appealid in
(" & delid &")")

-------------------------------------------------------------

to make bit fields as other name ...in page .... for example the bit fiels or 0 ,1 if u want to show 1 = male and 0 = female then u can use this query using CASE


SELECT CASE isApproved WHEN 1 THEN 'male' WHEN 0 THEN 'female'

END AS Expr1, Tbl_Donor.* FROM Tbl_Donor

------------------------------------------------------------

this qry shows how to use CASE with SQL query Fucntion Like DATEDIFF,DATEADD...

SELECT CASE isApproved WHEN 1 THEN 'Approved' WHEN 0 THEN 'Pending' END AS Expr1, CASE isApproved WHEN 1 THEN (DATEDIFF(dd, Postedon,
GETDATE())) WHEN 0 THEN '0' END AS FinishedDAYS, Appealid, Name, Address, City, State, Contactno, Email, Pincode, Refperson1, Refperson2,
Appeal, Filepath, Postedon, Userip, isResolved, isApproved
FROM Tbl_Appeal
WHERE (isApproved = 0)

----------------------------------------------------------------

if u want copy a record or row from a old table to new table u can use this Query

if u want copy all the data including ID ( primary key ) then u should set
auntoincrement to NO else u should retrieve all data ( except ID of old table ) and insert into new table .....

i am following the first case ( auto increment = no ie identity = no )

example

INSERT INTO Tbl_AppealBackup
SELECT Tbl_Appeal.*
FROM Tbl_Appeal
WHERE (Appealid = 135)

---------------------------------------------------------------

If u want to Check NULL values in SQL Queries here is the Example

ISNULL(expression,ValueifNULL)as expression

SELECT ISNULL(addID, '') AS addID,ISNULL(addType, '') AS addType, ISNULL(addPracticeName, '') AS addPracticeName, ISNULL(addAddress, '') AS addAddress, ISNULL(addStreet, '') AS addStreet, ISNULL(addCity, '') AS addCity," & _
"ISNULL(addState, '') AS addState, ISNULL(addZip, '') AS addZip, ISNULL(addCountry, '') AS addCountry, ISNULL(addMailTo, '') AS addMailTo FROM tblAddress WHERE(addMemID = " & mainID &

---------------------------------------------------------------------------------

all DATE FORMAT SQL query pls visit the below link

the DATE format query link

http://www.sql-server-helper.com/tips/date-formats.aspx ( data format link )
a example :

SELECT CASE Frequency WHEN 0 THEN 'Zero' WHEN 1 THEN 'one' WHEN 10 THEN 'Ten' END AS Frequency, CONVERT(char(10),tblInteractions.NextChargeDate, 105) AS NextChargeDate, CONVERT(char(10), tblInteractions.LastChargeDate, 105) AS LastChargeDate," & WHERE (tblInteractions.Vnum <> '') AND (tblInteractions.CreditCard <> '')


Fetching N th row from a table

SELECT * FROM tblMembers AS a
WHERE (@num =
(SELECT COUNT(*) AS Expr1
FROM tblMembers AS b
WHERE (a.mainID >= b.mainID)))


Finding and Deleting Duplicate rows from a TABALE


Here tblAdderss ---> table which have duplicate rows
tmpKey,tmpData ----> temp tables used for this process



Findind duplicate rows in a table
----------------------------------

SELECT addId, count(*)
FROM tblAddress
GROUP BY addId
HAVING count(*) > 1 /*** here addID is identity ro Primary key ****/



Deleting duplicate rows steps
-----------------------------

STEP 1 : Copy the duplicate IDs to temKey table
------------------------------------------------

SELECT addID,count(*) as count
INTO tmpKey
FROM tblAddress
GROUP BY addID
HAVING count(*) > 1


STEP 2: Copy the Distinct data from tblAddress to tmpData table with help of tmpKey
-----------------------------------------------------------------------------------

SELECT DISTINCT tblAddress.*
INTO tmpData
FROM tblAddress, tmpKey
WHERE tblAddress.addID= tmpKey.addID


STEP 3: Check tmpData table have any duplicate Rows
---------------------------------------------------

SELECT addID, count(*)
FROM tmpData
GROUP BY addID


STEP 4: Delete all duplicate rows in tblAddress
-----------------------------------------------

DELETE tblAddress
FROM tblAddress, tmpKey
WHERE tblAddress.addID= tmpKey.addID


STEP 5: Copy the non-dupicate rows to tblAddress from tmpData
--------------------------------------------------------------

SET IDENTITY_INSERT [tblAddress] ON

INSERT tblAddress([addID],[addMemID],[addType],[addPracticeName],[addAddress],[addStreet],[addCity],[addState],[addZip],[addCountry],[addMailTo]) SELECT [addID],[addMemID],[addType],[addPracticeName],[addAddress],[addStreet],[addCity],[addState],[addZip],[addCountry],[addMailTo] FROM tmpData

SET IDENTITY_INSERT [tblAddress] OFF



STEP 6: Drop the temp tables we used
-------------------------------------
DROP TABLE tmpKey,tmpData


SQL Functions

http://doc.ddart.net/mssql/sql70/fa-fz_15.htm


Super SQL server LINK

http://blog.sqlauthority.com/


to know about JOIN concept pls go to below link

link 1

link 2


http://en.wikipedia.org/wiki/Join_(SQL)#Sample_tables


how to improve the performance of the Query

http://technet.microsoft.com/en-us/library/ms172984.aspx


SQL TUTO LINK

http://www.1keydata.com/sql/sql.html

SQL FAQ

http://spaces.msn.com/ziauldotnet/

SQL Indexing

http://www.mssqlcity.com/Articles/Tuning/IndexOptimTips.htm
http://www.odetocode.com/Articles/70.aspx


SQL server Versions or excute this Query to know - " SELECT @@VERSION"

@@Version	SQL Server Version	Released
6.50.201	SQL Server 6.5 RTM
6.50.213	SQL Server 6.5 with Service Pack 1
6.50.240	SQL Server 6.5 with Service Pack 2
6.50.258	SQL Server 6.5 with Service Pack 3
6.50.281	SQL Server 6.5 with Service Pack 4
6.50.415	SQL Server 6.5 with Service Pack 5
6.50.416	SQL Server 6.5 with Service Pack 5a
7.00.623	SQL Server 7.0 / MSDE 1.0 RTM
7.00.699	SQL Server 7.0 SP1	July 1999
7.00.842	SQL Server 7.0 SP2	March 20th, 2000
7.00.961	SQL Server 7.0 SP3	December 15th, 2000
7.00.1063	SQL Server 7.0 SP4
8.00.194	SQL Server 2000 RTM
8.00.384	SQL Server 2000 SP1
8.00.534	SQL Server 2000 SP2	November 30th, 2001
8.00.760	SQL Server 2000 SP3
8.00.2039	SQL Server 2000 SP4
2005.90.1399	SQL Server 2005 RTM	November 2005
2005.90.2047	SQL Server 2005 SP1
2005.90.3042.00	SQL Server 2005 SP2	February 2007
2005.90.3042.01	SQL Server 2005 "SP2a"	5 Mar 2007
2005.90.3054.00	KB934458 - Fix to check database in maintenance plans. Also read Bob Ward's post on SP2 for a great description of what to install.	5 Apr 2007

To know the Rows count of all tables in a database


SELECT
[TableName] = so.name,
[RowCount] = MAX(si.rows)
FROM
sysobjects so,
sysindexes si
WHERE
so.xtype = 'U'
AND
si.id = OBJECT_ID(so.name)
GROUP BY
so.name
ORDER BY
2 DESC


To know the Columns count, Row size of all tables in a database

SELECT sysobjects.name, sysobjects.id, COUNT(syscolumns.name) AS ColumnCount, SUM(syscolumns.length) AS MaxLength
FROM sysobjects INNER JOIN
syscolumns ON sysobjects.id = syscolumns.id
WHERE (sysobjects.type = 'U')
GROUP BY sysobjects.name, sysobjects.id


SQL Injection Cheat Sheet

http://ferruh.mavituna.com/sql-injection-cheatsheet-oku/

Case Sensitive or Insensitive SQL Query

Suppose you need to perform a SQL query and you need for it to be case sensitive or case insensitive, and either your database is set up the opposite way or you're smart and you're trying to write your query so that it will work regardless of how the database may or may not be configured. For instance, consider this query:

SELECT UserId, email
FROM aspnet_membership
WHERE email = 'billg@microsoft.com'

If your database contains an email for Bill like this one: BillG@microsoft.com then whether or not your query will return any rows will depend on the COLLATION for the database. If you want to ensure that you DO get results, you can force it to use a CASE INSENSITIVE collation like so:

SELECT UserId, email
FROM aspnet_membership
WHERE email = 'biilg@microsoft.com' COLLATE SQL_Latin1_General_CP1_CI_AS

Similarly, if you don't want it to return any rows unless you specify the correct case for the email, you would change the collation to replace _CI_ with _CS_ where CI is short for Case Insensitive and CS is short for Case Sensitive. The following query would not match BillG@microsoft.com because it is using a case sensitive collation.

SELECT UserId, email
FROM aspnet_membership
WHERE email = 'billg@microsoft.com' COLLATE SQL_Latin1_General_CP1_CS_AS

To reset the identity field after testing in a TABLE

DBCC CHECKIDENT ('[dbo].[tblCalendar_Payment]' , RESEED, 0)



Listing / Dropping ALL stored procedures from a database in SQL Server 2005


I) List all Stored procedures in a database:

Select * from sys.procedures where [type] = 'P' and is_ms_shipped = 0 and [name] not like 'sp[_]%diagram%'

OR

Select * from sys.objects where type='p' and is_ms_shipped=0 and [name] not like 'sp[_]%diagram%'

Please note that I have added the 'NOT LIKE' part to get rid of stored procedures created during database installation.

II) Delete all [user created] Stored procedures in a database:

Select 'Drop Procedure ' + name from sys.procedures Where [type] = 'P' and is_ms_shipped = 0 and [name] not like 'sp[_]%diagram%'

This would list down all the user defined Stored procedure as 'Drop Procedure procedureName' in the result pane. Just copy-paste it into the query window and execute it once.

Select by Date part only ( not time )

WHERE (REGDATE = CONVERT(varchar(8), GETDATE(), 112))