find last 30th working date.

declare @i int
set @i =1
Declare @dt smalldatetime
Set @dt=GETDATE()
While @i<=30
Begin
IF (datepart(dw,@dt) Between 2 and 6)
BEGIN
SET @i = @i + 1
END
SET @dt = @dt - 1
End

select @dt

Primay & FK settings with scripts

--alter table T_Tracking alter column ENTERPRISE_NBR varchar(10) not null
ALTER TABLE T_PersonnelInformation
ADD CONSTRAINT pk_PERSONNEL_id PRIMARY KEY (PERSONNEL_id)

alter table T_PersonnelInformation alter column PERSONNEL_id varchar(10) not null

ALTER TABLE T_PersonnelInformation
ADD CONSTRAINT fk_ENTERPRISE_NBR
FOREIGN KEY (ENTERPRISE_NBR)
REFERENCES T_CompanyInformation (ENTERPRISE_NBR) ON DELETE CASCADE
GO


ALTER TABLE T_Tracking
ADD CONSTRAINT fk_ENTERPRISE_NBR_TR
FOREIGN KEY (ENTERPRISE_NBR)
REFERENCES T_CompanyInformation (ENTERPRISE_NBR) ON DELETE CASCADE
GO

Nearest Time in sqlserver

/*CREATE FUNCTION RoundToNearest15
(
@Date SMALLDATETIME
)

RETURNS SMALLDATETIME
AS
*/
DECLARE @Date SMALLDATETIME=GETDATE()
BEGIN

DECLARE @Miniute TINYINT
DECLARE @Mult SMALLINT
DECLARE @Value TINYINT

SET @Miniute = DATEPART(MI,@Date)
SET @Value = 0
SET @Mult = 1

IF @Miniute < 8 BEGIN SET @Mult = -1 SET @Value = @Miniute END IF @Miniute BETWEEN 16 AND 22 BEGIN SET @Mult = -1 SET @Value = @Miniute - 15 END IF @Miniute BETWEEN 31 AND 37 BEGIN SET @Mult = -1 SET @Value = @Miniute - 30 END IF @Miniute BETWEEN 46 AND 52 BEGIN SET @Mult = -1 SET @Value = @Miniute - 45 END IF @Miniute BETWEEN 8 AND 14 SET @Value = 15 - @Miniute IF @Miniute BETWEEN 23 AND 29 SET @Value = 30 - @Miniute IF @Miniute BETWEEN 38 AND 44 SET @Value = 45 - @Miniute IF @Miniute BETWEEN 53 AND 59 SET @Value = 60 - @Miniute --SELECT DATEADD(MI,-15,DATEADD(MI,@Value * @Mult,@Date)) --SELECT DATEADD(MI,@Value * @Mult,@Date) --SELECT DATEPART(MINUTE,@Date ) END --select convert(varchar(10),getdate(),108) --SELECT Convert( VarChar( 10 ), GETDATE(), 114 ) --SELECT substring(convert(varchar(10), GETDATE(), 108),1,2) SELECT cast( case when cast(substring(convert(varchar(10),DATEADD(MI,-15,DATEADD(MI,@Value * @Mult,@Date)), 108),1,2) as integer) > 12 then
CAST(cast(substring(convert(varchar(10),DATEADD(MI,-15,DATEADD(MI,@Value * @Mult,@Date)), 108),1,2) as integer)-12 AS VARCHAR)+
substring(convert(varchar(10),DATEADD(MI,-15,DATEADD(MI,@Value * @Mult,@Date)), 108),3,3)
when
substring(convert(varchar(10),DATEADD(MI,-15,DATEADD(MI,@Value * @Mult,@Date)), 108),1,2)='00' then
CAST(12 AS VARCHAR)+substring(convert(varchar(10),DATEADD(MI,-15,DATEADD(MI,@Value * @Mult,@Date)), 108),3,3)
else
substring(convert(varchar(10),DATEADD(MI,-15,DATEADD(MI,@Value * @Mult,@Date)), 108),1,5)
end
as varchar(10))+
case
when
cast(substring(convert(varchar(10),DATEADD(MI,-15,DATEADD(MI,@Value * @Mult,@Date)), 108),1,2) as integer) > 12 then
' pm'
else
' am'
end
as time1,
cast(
case
when
cast(substring(convert(varchar(10),DATEADD(MI,@Value * @Mult,@Date), 108),1,2) as integer) > 12 then
CAST(cast(substring(convert(varchar(10),DATEADD(MI,@Value * @Mult,@Date), 108),1,2) as integer)-12 AS VARCHAR)+
substring(convert(varchar(10),DATEADD(MI,@Value * @Mult,@Date), 108),3,3)
when
substring(convert(varchar(10),DATEADD(MI,@Value * @Mult,@Date), 108),1,2)='00' then
CAST(12 AS VARCHAR)+substring(convert(varchar(10),DATEADD(MI,@Value * @Mult,@Date), 108),3,3)
else
substring(convert(varchar(10),DATEADD(MI,@Value * @Mult,@Date), 108),1,5)
end
as varchar(10))+
case
when
cast(substring(convert(varchar(10),DATEADD(MI,@Value * @Mult,@Date), 108),1,2) as integer) > 12 then
' pm'
else
' am'
end
as time2

Dynamic field name and data type selection in sqlserver 2005/2008

DECLARE @ListXML VARCHAR(MAX),@TableName VARCHAR(50)
SET @ListXML=''
SET @TableName='T_SAMPLE'-- MENTION UR TABLE NAME HERE

SELECT @ListXML = (SELECT CASE WHEN DATA_TYPE IN('datetime','smalldatetime') THEN 'ISNULL(CONVERT(VARCHAR(10),'+CONVERT(VARCHAR,COLUMN_NAME)+',101),''''),'
WHEN DATA_TYPE IN('bit','int') THEN 'ISNULL(CAST('+CONVERT(VARCHAR,COLUMN_NAME)+' AS VARCHAR),''''),'
ELSE 'ISNULL(CAST('+CONVERT(VARCHAR,COLUMN_NAME) +' AS VARCHAR('+CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')),''''),' END
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME IN(@TableName) FOR XML PATH(''))

SELECT @ListXML

Find PRIMARY KEY column name and table name in sqlsever

select a.Table_Name, a.Column_Name from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE a,
INFORMATION_SCHEMA.TABLE_CONSTRAINTS b
where b.constraint_type='PRIMARY KEY' and a.constraint_name = b.constraint_name
--and a.table_name='T_8KCompany'
order by a.table_name, a.column_name

Pivot sample Query in sqlserver

create table #temp
(CustomerID int ,[date] datetime ,
SubTotal varchar(20))

select * from #temp
--insert into #temp values(4,getdate(),34)
SELECT * FROM

(SELECT CustomerID, DATEPART(m, date) OrderMonth, SubTotal

FROM #temp

WHERE date between '20030101' and '20101231'

and CustomerID IN (2,4,6,7,8,9)) src

PIVOT (SUM(SubTotal) FOR OrderMonth

IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) AS pvt

GO

month of week of first day and last date using sqlserver

declare @end_week datetime,@week_number int
declare @start_week datetime,@today datetime
set @today=GETDATE()
set @week_number=2
set @today = dateadd(wk, @week_number- datepart(wk,@today), @today)
set @start_week = dateadd(d, 1 - DATEPART(dw, @today), @today)
set @end_week = dateadd(d,6,@start_week )
select @start_week,@end_week

First and Last Date of Last,Current and Next month using sqlserver 2008

DECLARE @Date DATETIME
SELECT @Date = GETDATE()
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@Date)),@Date),101) ,
'LastDayof PreviousMonth'
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@Date)-1),@Date),101) AS Date_Value,
'FirstDayofCurrentMonth' AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),@Date,101) AS Date_Value,
'Today' AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@Date))),DATEADD(mm,1,@Date)),101) ,
'LastDayofCurrentMonth'
UNION
SELECT CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@Date))-1),DATEADD(mm,1,@Date)),101) ,
'FirstDayofNextMonth'
GO

Select values with monthly based and order by month with case method

SELECT CAST(DATENAME(MONTH, TimeOut)AS VARCHAR(50))+'-'+CAST(DATENAME(YEAR, TimeOut)AS VARCHAR(50))[Time],
R.[Researcher] ,
'10k' [Project],
CONVERT(VARCHAR(10),B.[Filingdate],101) [FilingDate],
'10k'[Form],
COUNT(*)[NormalFiles],
COUNT(CASE RED_FLAG WHEN 'type1' THEN 1 END)[Type1],
COUNT(CASE RED_FLAG WHEN 'type2' THEN 1 END)[Type2],
COUNT(CASE RED_FLAG WHEN 'type3' THEN 1 END)[Type3],
COUNT(CASE RED_FLAG WHEN 'type4' THEN 1 END)[Type4],
COUNT(CASE RED_FLAG WHEN 'type5' THEN 1 END)[Type5],
COUNT(CASE RED_FLAG WHEN 'type6' THEN 1 END)[Type6],
COUNT(CASE A.Status WHEN 'RecordNotFound' THEN 1 END)[NewCompanies],

COUNT(*)+
COUNT(CASE RED_FLAG WHEN 'type1' THEN 1 END)+
COUNT(CASE RED_FLAG WHEN 'type2' THEN 1 END)+
COUNT(CASE RED_FLAG WHEN 'type3' THEN 1 END)+
COUNT(CASE RED_FLAG WHEN 'type4' THEN 1 END)+
COUNT(CASE RED_FLAG WHEN 'type5' THEN 1 END)+
COUNT(CASE RED_FLAG WHEN 'type6' THEN 1 END)+
COUNT(CASE A.Status WHEN 'RecordNotFound' THEN 1 END)[Total]

FROM [10K2K9] A JOIN T_Allocation B
ON A.Record_ID = B.Record_ID
LEFT JOIN T_Tracking E on E.Enterprise_number = B.Enterprise_Number
join T_Accounts R on R.EmployeeID= A.Researcher
WHERE TimeOut IS NOT NULL
GROUP BY DATENAME(MONTH, TimeOut),R.[Researcher],CONVERT(VARCHAR(10),B.[Filingdate],101) ,DATENAME(YEAR, TimeOut)
ORDER BY CASE DATENAME(MONTH, TimeOut) WHEN 'January' THEN'01'
WHEN 'February'THEN'02'
WHEN 'March'THEN'03'
WHEN 'April'THEN'04'
WHEN 'May'THEN'05'
WHEN 'June'THEN'06'
WHEN 'July'THEN'07'
WHEN 'August'THEN'08'
WHEN 'September'THEN'09'
WHEN 'October'THEN'10'
WHEN 'November'THEN'11'
WHEN 'December'THEN '12'
ELSE '1' END

Finding week of the month in sqlserver 2005

DECLARE @DATE VARCHAR(50)
SET @DATE ='04/03/2009'
select cast(datename(week,@DATE) as int)- cast( datename(week,dateadd(dd,1-day(@DATE),@DATE)) as int)+1
---------------------------


SELECT DATEPART(DAY, GETDATE()-10) / 7 AS WeekofMonth

Select Date between two different date values

DECLARE @FromDate VARCHAR(50)
DECLARE @ToDate VARCHAR(50)
SET @FromDate='2009/02/20'
SET @ToDate ='2009/03/25'

DECLARE @TEMPDATE VARCHAR(50)
--DROP TABLE #TEMP
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[#TEMP]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[#TEMP]
CREATE TABLE #TEMP(TEMPEDATE datetime)

IF @FromDate IS NOT NULL
BEGIN
WHILE CAST(CONVERT(VARCHAR(10),@FromDate,101) AS DATETIME)
<=CAST(CONVERT(VARCHAR(10),@ToDate,101)AS DATETIME)
BEGIN
INSERT INTO #TEMP (TEMPEDATE)
SELECT CONVERT(VARCHAR(10),@FromDate,101)
SET @FromDate=CONVERT(VARCHAR(10),CAST(@FromDate AS DATETIME)+1,101)
END
END
select CONVERT(VARCHAR(10),TEMPEDATE,101) from #TEMP
select CONVERT(VARCHAR(10),TEMPEDATE,102) from #TEMP
select CONVERT(VARCHAR(10),TEMPEDATE,103) from #TEMP
SELECT * FROM #TEMP

select column names and table name in sqlserver

SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS


SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='T_Tracking'

Select count values between two date in sqlserver 2005

DECLARE @FromDate varchar(50)
DECLARE @Todate varchar(50)

SET @FromDate='10-1-2008'
SET @Todate='2-1-2009'


SELECT
CASE MONTH(ValidFrom)
WHEN 1 THEN 'JAN-'+CAST(year(ValidFrom)AS VARCHAR)
WHEN 2 THEN 'FEB-'+CAST(year(ValidFrom)AS VARCHAR)
WHEN 3 THEN 'MAR-'+CAST(year(ValidFrom)AS VARCHAR)
WHEN 4 THEN 'APR-'+CAST(year(ValidFrom)AS VARCHAR)
WHEN 5 THEN 'MAY-'+CAST(year(ValidFrom)AS VARCHAR)
WHEN 6 THEN 'JUN-'+CAST(year(ValidFrom)AS VARCHAR)
WHEN 7 THEN 'JUL-'+CAST(year(ValidFrom)AS VARCHAR)
WHEN 8 THEN 'AUG-'+CAST(year(ValidFrom)AS VARCHAR)
WHEN 9 THEN 'SEP-'+CAST(year(ValidFrom)AS VARCHAR)
WHEN 10 THEN 'OCT-'+CAST(year(ValidFrom)AS VARCHAR)
WHEN 11 THEN 'NOV-'+CAST(year(ValidFrom)AS VARCHAR)
WHEN 12 THEN 'DEC-'+CAST(year(ValidFrom)AS VARCHAR)
END MonthName,COUNT(*)AS Total,
COUNT( CASE Level WHEN 1 THEN Level END )AS Platinum,
COUNT( CASE Level WHEN 2 THEN Level END )AS Gold,
COUNT( CASE Level WHEN 7 THEN Level END )AS Trail

FROM SubscriberLevel
WHERE
((YEAR(ValidFrom) >= YEAR(@FromDate)
AND Month(ValidFrom) >= Month(@FromDate)
AND (Year(ValidFrom) <= YEAR(@Todate))--AND
-- AND MONTH(ValidFrom) <= 12 OR
OR Month(ValidFrom) <= Month(@Todate)))

GROUP BY YEAR(ValidFrom),MONTH(ValidFrom)
ORDER BY YEAR(ValidFrom),MONTH(ValidFrom)

Differences between sqlserver 2000,2005 and 2008

selecting two different table values in multiple column unmatching in sqlserver

create a temp table to view unmatched fields from first table

create table #zip(
City varchar(100)collate Latin1_General_CI_AS,
State varchar(100)collate Latin1_General_CI_AS,
County varchar(100)collate Latin1_General_CI_AS)


declare @City varchar(100)
declare @State varchar(100)
declare @County varchar(100)

declare @City1 varchar(100)
-- declare @State1 varchar(100)
-- declare @County1 varchar(100)
DECLARE
c3 CURSOR FOR
select distinct City,
State,
County from T_Companies
OPEN c3
FETCH next FROM c3 INTO @City,@State,@County
WHILE @@FETCH_status=0
BEGIN
SET @City1=''
--insert into #zip
select @City1='1'
from T_Zipcodes where City=@City
and State=@State and County=@County
IF @City1 !='1'
BEGIN

insert into #zip
SELECT @City,@State,@County
END


FETCH next FROM c3 INTO @City,@State,@County
END
CLOSE c3
DEALLOCATE c3

select * from #zip

drop table #zip

Select All table names in a database in sqlsever 2005

SELECT * FROM INFORMATION_SCHEMA.TABLES

Dropdown list values get from DB in ASP.net

in .cs file code
------------------
public DataSet LoadValues(string procName, string valueField, string nameField)
{
string retVal = string.Empty;
DataSet ds = new DataSet();
if (procName == "GetList" || procName == "GetStateName")
{
retVal = "--Select--";
}
ds = GetStateValues(procName);
// now construct the option list
if (ds != null)
{
if (ds.Tables[0].Rows.Count > 0)
{
for (int x = 0; x < ds.Tables[0].Rows.Count; x++) { retVal += "" + ds.Tables[0].Rows[x][nameField].ToString() + "";
}
}
}

return ds;
}
===========
code hehind section add the below code:
--------------------------------------
SessionDB sessionDB = new SessionDB();

DataSet ds = sessionDB.LoadValues("GetList", "UserID", "UserName");

if (ds.Tables[0].Rows.Count > 0)
{
dduserid.DataSource = ds.Tables[0];
dduserid.DataTextField = "UserName";
dduserid.DataValueField = "UserName";
dduserid.DataBind();
dduserid.Items.Insert(0, "----Select----");
}

select columns from three different tables using join in sqlserver 2005

--Select values from 3 diff tables
---------------------------------
SELECT DISTINCT A.NAICS,B.NAICSS,C.SIC

FROM #NAICS A JOIN NAIC_SIC..NAICS B ON

A.[NAICS]=SUBSTRING(CAST(B.NAICS AS VARCHAR),1,5)

JOIN [LE-NAICS] C ON

A.[NAICS]=C.NAICS

Hide menus in master page using asp.net

if (Request.QueryString["hid"] != null)
{
if (Request.QueryString["hid"] == "hidelink")
{
_master.ShowHideLinks("hidelinkedarea");
}
}

send this QueryString whenever you need to hide
sample : Response.Redirect("SearchDetails.aspx?cid=" + compid + "&hid=hidelink");

Update(add) values with current values in same column in sqlserver 2005

Create procedure to get XML output in sqlserver 2005

CREATE PROCEDURE [dbo].[GetUsersXML]

AS
BEGIN
SELECT 1 AS TAG,
NULL AS PARENT,
UserID AS [User!1!ID],
UserName AS [User!1!Name]
FROM T_Users
FOR XML EXPLICIT
END

Replace special characters in sqlserver 2005