What Query?

Question

What Query?

aselcukc

SSC Journeyman

Points: 75
More actions
February 4, 2008 at 3:57 am

#182452

Hello
This table arranges me to a year's holidays (every sunday and some holidays manually arranged by myself). I wish to access holidays with a query, but what query? If you can post the requird codes, I'd be most thankful. Regards.
create table WorkCalendar
(
dt smalldatetime Primary Key Clustered,
isWeekDay as convert(bit, case when datepart(dw, dt) in (1) then 0 else 1 end),
isWorkDay bit default 1
)
GO
declare @dt smalldatetime;
set @dt = '20080101';
while @dt <= '20081231'
begin
insert WorkCalendar(dt) select @dt;
set @dt = @dt +1
end
update WorkCalendar
set isWorkDay = 0 where isWeekDay = 0;
--enter some holidays
update WorkCalendar
Set isWorkDay = 0 where isWorkDay = 1
and dt in
(
'20080101',
'20080423',
'20080519'
)

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Could you give the expected output please?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

aselcukc SSC Journeyman Points: 75 More actions · Answer 2

I wish to arrange holidays from my asp 2.0 website. I want to do this; a query that returns me to the all holidays in a year, and if a holiday's next day is a holiday again, then query adds one more day's time to the my site's remaining time calculator. I guess a select query can help me, but I confused :crazy:. Thanks.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

With the table structure you've given, there's no way to tell 100% which days are holidays. It's easy to tell which days are not work days (SELECT dt FROM Calender WHERE isWorkDay = 0)

It's also easy to tell which days are not weekdays (SELECT dt FROM Calender WHERE isWeekDay = 0)

Most holidays can be returned by the query (SELECT dt FROM Calender WHERE isWorkDay = 0 AND isWeekDay = 1), however, that won't pick up holidays that fall on days that are not week days

What do you mean by "site's remaining time calender"?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

aselcukc SSC Journeyman Points: 75 More actions · Answer 4

Very thanks, GilaMonster.

In my website, I used a remaining time calculator that calculates the shipment's remaining time, like this amazon.com examples:

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

http://www.amazon.com/Fisher-Price-M2688-eXtra-Special-Edition/dp/B000UU7LOS/ref=xs_gb_20_right-3_1.1_17866?%5Fencoding=UTF8&m=ATVPDKIKX0DER&%5Fencoding=UTF8&pf_rd_m=ATVPDKIKX0DER&pf_rd_s=center-2&pf_rd_r=1M6GZPE7RYP43PNDRYHV&pf_rd_t=701&pf_rd_p=305207201&pf_rd_i=20

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

Want it delivered Tuesday, February 5? Order it in the next 10 hours and 34 minutes, and choose One-Day Shipping at checkout.

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

I wish to this time calculator adds one more day's time to the remaining time if next day is a holiday again.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 5

Could you post the code that you're using for the time calculation please. Along with any tables that it uses, sample data and expected output.

See http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

aselcukc SSC Journeyman Points: 75 More actions · Answer 6

I write this code and I used Ajax for dynamic time view. They're C# codes, so... sorry :blush:

protected void Button1_Click(object sender, EventArgs e)

{

Timer1.Enabled = true;

DateTime sonTarih = DateTime.Today.AddDays(1).AddHours(13);

TimeSpan aradakiFark = sonTarih - DateTime.Now;

StringBuilder sb = new StringBuilder();

sb.Append("Remaining time is ");

if (aradakiFark.Days > 0)

{

sb.Append(aradakiFark.Days.ToString());

sb.Append(" days ");

}

if (aradakiFark.Hours > 0)

{

sb.Append(aradakiFark.Hours.ToString());

sb.Append(" hours ");

}

if (aradakiFark.Minutes > 0)

{

sb.Append(aradakiFark.Minutes.ToString());

sb.Append(" minutes");

}

lblSure.Text = sb.ToString();

}

protected void Timer1_Tick(object sender, EventArgs e)

{

DateTime sonTarih = DateTime.Today.AddDays(1).AddHours(13);

TimeSpan aradakiFark = sonTarih - DateTime.Now;

StringBuilder sb = new StringBuilder();

sb.Append("Remaining time is ");

if (aradakiFark.Days > 0)

{

sb.Append(aradakiFark.Days.ToString());

sb.Append(" days ");

}

if (aradakiFark.Hours > 0)

{

sb.Append(aradakiFark.Hours.ToString());

sb.Append(" hours ");

}

if (aradakiFark.Minutes > 0)

{

sb.Append(aradakiFark.Minutes.ToString());

sb.Append(" minutes");

}

lblSure.Text = sb.ToString();

}