DECLARE @tblScratch TABLE
(
card int,
Name varchar(50),
Address varchar(50),
DateArrived datetime
)
Insert into @tblScratch
(
card,
Name,
Address,
DateArrived
)
values (1,’John’,’Coit RD’,getdate())
Select * from @tblScratch
CREATE TABLE #tblScratch
(
card int,
Name varchar(50),
Address varchar(50),
DateArrived datetime
)
Insert into #tblScratch
(
card,
Name,
Address,
DateArrived
)
values (1,’John’,’Coit RD’,getdate())
Select * from #tblScratch
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
USE YourDatabaseName
SELECT COUNT(*) from information_schema.tables
WHERE table_type = ‘base table’
Create Table BusinessCalendar
(BDate smalldatetime Primary Key, BType tinyint)
Go
–Load dates into table:
–This will handle business dates and weekends but not holidays. Change date range as needed.
Declare @dt smalldatetime
Set @dt=’02/28/2010′
While @dt<=’03/13/2010′
Begin
Insert BusinessCalendar Values
(@dt,
Case
When datepart(dw,@dt) Between 2 and 6
Then 1 Else 0
End)
SET @dt = @dt + 1
End
Declare @startdate smalldatetime, @enddate smalldatetime
Set @startdate=’02/28/2010′
Set @enddate=’03/13/2010′
Select BDays=Count(*)
From BusinessCalendar
Where BType=1
And BDate Between @startdate And @enddate
drop table BusinessCalendar
SELECT t1.run
,ISNULL((SELECT COUNT(t2.errors) FROM table2 t2 WHERE t2.run = t1.run ), 0) as errors
FROM table1 t1
/// <summary>
/// Calulates Business Days within the given range of days.
/// Start date and End date inclusive.
/// </summary>
/// <param name="startDate">Datetime object
/// containing Starting Date</param>
/// <param name="EndDate">Datetime object containing
/// End Date</param>
/// <param name="NoOfDayWeek">integer denoting No of Business
/// Day in a week</param>
/// <param name="DayType"> DayType=0 for Business Day and
/// DayType=1 for WeekEnds </param>
/// <returns></returns>
public static double CalculateBDay(
DateTime startDate,
DateTime EndDate,
int NoOfDayWeek, /* No of Working Day per week*/
int DayType
)
{
//Get the difference in terms of TimeSpan
TimeSpan T;
T = endDate - startDate;
double iWeek, iDays, isDays, ieDays;
//* Find the number of weeks between the dates. Subtract 1 */
// since we do not want to count the current week. * /
iWeek =DateDiff("ww",startDate,EndDate)-1 ;
iDays = iWeek * NoOfDayWeek;
//
if( NoOfDayWeek == 5)
{
//-- If Saturday, Sunday is holiday
if ( startDate.DayOfWeek == DayOfWeek.Saturday )
isDays = 7 -(int) startDate.DayOfWeek;
else
isDays = 7 - (int)startDate.DayOfWeek - 1;
}
else
{
//-- If Sunday is only <st1:place>Holiday</st1:place>
isDays = 7 - (int)startDate.DayOfWeek;
}
//-- Calculate the days in the last week. These are not included in the
//-- week calculation. Since we are starting with the end date, we only
//-- remove the Sunday (datepart=1) from the number of days. If the end
//-- date is Saturday, correct for this.
if( NoOfDayWeek == 5)
{
if( EndDate.DayOfWeek == DayOfWeek.Saturday )
ieDays = (int)EndDate.DayOfWeek - 2;
else
ieDays = (int)EndDate.DayOfWeek - 1;
}
else
{
ieDays = (int)EndDate.DayOfWeek - 1 ;
}
//-- Sum everything together.
iDays = iDays + isDays + ieDays;
if(DayType ==0)
return iDays;
else
return T.Days - iDays;
}
/// <summary>
/// Calculate weeks between starting date and ending date
/// </summary>
/// <param name="stdate"></param>
/// <param name="eddate"></param>
/// <returns></returns>
public static int GetWeeks(DateTime stdate, DateTime eddate )
{
TimeSpan t= eddate - stdate;
int iDays;
if( t.Days < 7)
{
if(stdate.DayOfWeek > eddate.DayOfWeek)
return 1; //It is accross the week
else
return 0; // same week
}
else
{
iDays = t.Days -7 +(int) stdate.DayOfWeek ;
int i=0;
int k=0;
for(i=1;k<iDays ;i++)
{
k+=7;
}
if(i>1 && eddate.DayOfWeek != DayOfWeek.Sunday ) i-=1;
return i;
}
}
/// e.g. if you say starting date is 29/01/2005
/// and 01/02/2005 the year will be 0,month will be 1.
///
/// </summary>
/// <param name="datePart">specifies on which part
/// of the date to calculate the difference </param>
/// <param name="startDate">Datetime object containing
/// the beginning date for the calculation</param>
/// <param name="endDate">Datetime object containing
/// the ending date for the calculation</param>
/// <returns></returns>
public static double DateDiff(string datePart,
DateTime startDate, DateTime endDate)
{
//Get the difference in terms of TimeSpan
TimeSpan T;
T = endDate - startDate;
//Get the difference in terms of Month and Year.
int sMonth, eMonth, sYear, eYear;
sMonth = startDate.Month;
eMonth = endDate.Month;
sYear = startDate.Year;
eYear = endDate.Year;
double Months,Years=0;
Months = eMonth - sMonth;
Years = eYear - sYear;
Months = Months + ( Years*12);
switch(datePart.ToUpper())
{
case "WW":
case "DW":
return (double)GetWeeks(startDate,endDate);
case "MM":
return Months;
case "YY":
case "YYYY":
return Years;
case "QQ":
case "QQQQ":
//Difference in Terms of Quater
return Math.Ceiling((double)T.Days/90.0);
case "MI":
case "N":
return T.TotalMinutes ;
case "HH":
return T.TotalHours ;
case "SS":
return T.TotalSeconds;
case "MS":
return T.TotalMilliseconds;
case "DD":
default:
return T.Days;
}
}
Aspx Page
<script type=”text/javascript” language=”javascript”>
function ShowConfirmation(){
if (confirm(“Are you want to show the value?”) == true) {
document.getElementById(‘<%= btnAlelrt.ClientID%>’).click();
}
}
</script>
<tr>
<asp:Button ID=”btnDetails” runat=”server” Text=”GetValue” />
<asp:Button ID=”btnAlelrt” runat=”server” Text=”GetDetails”/>
</tr>
Aspx.cs file
private void CustomInitilize()
{
this.btnDetails.Click += new EventHandler(btnDetails_Click);
this.btnAlelrt.Click += new EventHandler(btnAlelrt_Click);
}
protected void btnDetails_Click(object sender, EventArgs e)
{
//Write your code which you wish to execute before the Confirmation from the User
ScriptManager.RegisterClientScriptBlock(this, typeof(Page),
Guid.NewGuid().ToString(), “ShowConfirmation();”, true);
}
protected void btnAlelrt_Click(object sender, EventArgs e)
{
//write your code which you wish to execute after the confirmation from the user….
Page.ClientScript.RegisterStartupScript(this.GetType(), “showVal”, “alert(‘alert2’);”, true);
}
CSharpWiki 