YogendraRai.blogspot.com: HTC Wildfire: A Review by a First Time User

YogendraRai.blogspot.com: HTC Wildfire: A Review by a First Time User: "(After reading Sandeep Sir's Blog i also decided to post similar blog about my new handset. So this blog inspired from above blog.) After ..."

Increasing value as Serial number in sql

select ROW_NUMBER () OVER (ORDER BY min([Date])) AS rowNum, FiscalMonthName from bom_calendar where FiscalYear=2010 Group by FiscalMonthName Order by min([Date])

Incremental column in select statement.

SELECT ROW_NUMBER() OVER (ORDER BY [Date] ASC) AS ROWID

,month([Date]) as Dt

FROM [TestChartDB].[dbo].[bom_Calendar]

Where FiscalYear=2010

Order by [Date]

find working hours between two date

CREATE function getWorkingHrsInMin(@datetime1 datetime,@datetime2datetime, @DailyWorkingHrs int)

returns int

as

begin

declare @time int

set @time=0

declare @tmpTbl table

(

Col_Date datetime,

Col_Day nvarchar(50),

WorkingHrs int

)

WHILE (@datetime1 <= @datetime2)

BEGIN

insert into @tmpTbl(Col_Date,Col_Day,WorkingHrs)

values (@datetime1, CASE DATEPART(weekday,@datetime1)

WHEN 1 THEN 'Sun'

WHEN 2 THEN 'Mon'

WHEN 3 THEN 'Tue'

WHEN 4 THEN 'Wed'

WHEN 5 THEN 'Thu'

WHEN 6 THEN 'Fri'

WHEN 7 THEN 'Sat'

END,

@DailyWorkingHrs)

SET @datetime1 = @datetime1 + 1

END

set @time=(select sum(WorkingHrs)*60 from @tmpTbl whereCol_Day not in('Sat','Sun'))

return @time

end

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

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

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

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

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

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

Call this function like this:

1st Parameter: StartDate

2nd Parameter: EndDate

3rd Parameter: Daily working Hours.

select dbo.getWorkingHrsInMin('7-Mar-2010','13-Mar-2010',8)

Result:

2400

Getting the Number of Tables in MS Access:

Getting the Number of Tables in MS Access:

Select count(*) from MSysObjects where Type = 1 and Flags = 0

Fetching Records from Closed DB.

Fetching Records from Closed DB.
SELECT * FROM Employees IN "C:\northwind.mdb";