Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help in Date query

Author  Topic 

GirishKumarSharma
Starting Member

10 Posts
Posted - 2011-11-09 : 08:55:55
Hi,


USE [ScheduleTask]
GO
/****** Object: Table [dbo].[DBFILE] Script Date: 11/09/2011 16:54:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DBFILE](
[USER_CODE] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DATE_OF_ALLOTED] [datetime] NULL,
[From_Date] [datetime] NULL,
[TYPE] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DESCRIPTION] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[PRIORITY] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

insert into dbfile values('A01',getdate()-2,getdate()-2,'Week-Wednesday-0-1-0-1-0','Export Database','*')
insert into dbfile values('A01',getdate()+3,getdate()+3,'Week-Sunday-1-0-0-1-0','Full Backup','***')


Now Today is 09-11-2011. If i am running the query then :
For Row 1: Row will be selected because Today is 2nd Wednesday so if i was running -2 days i.e. from 7-11-2011 to 11-11-2011
this row will be part of query output.

Now suppose today is >=12-11-2011 it will not part of query. When this row will be part of query? It will now work for 4th
Wednesday i.e. if i run 21-11-2011 to 25-11-2011 this rows will be part of query.


Now for 2nd Row :
First Sunday has gone of this month, so this row will be now reflected in 01-12-2011 to 05-12-2011.

SQL Server Version info :

Microsoft SQL Server Management Studio 9.00.1399.00

I think this is bad table design issue. I am ready to change the design, but after viewing the running select query, because otherwise it may down the whole application which is running fine, no complaint.


I am working with my friend on his vb project regarding schedule task assignment. Managers will assign some different types of scheduled task to different team members, so that they can have track and get report. In our database we are storing all types of assignments which can be given, but stuck on a task type "Week Day". Here week day task type means, that person has to perform that task on that particular week day. We are storing data in following manner :

User_Code Date_of_alloted Type
A01 2011-11-09 00:00:00.000 Week-Wednesday-0-1-0-1-0
S23 2011-10-24 00:00:00.000 Week-Sunday-1-0-0-1-0
K15 2011-11-10 00:00:00.000 Week-Sunday-0-0-0-1-0

Means, user A01 will perform given task on every 2nd and 4th wednesday.
User S23 will perform the given task on every 1st Sunday.

Ok. Now question is when should these tasks should be reflected in the report (Select Query) ? When i asked my Manager, they said, we will tell you, but first you think how it should be reflected and work with :
1.Date_of_alloted 2.Date_of_Running_Query

For Example:A01 User. If we compare with Date_Of_Alloted then query will work differently and if we compare with current date then differently.

Now suppose, if 2nd Wednesday has gone from Date_of_Alloted / current date then it will reflected from 4th Wednesday -2 Upto 4th Wednesday +2 days every month. I means between 4th Wednesday-2 and 4th Wednesday+2.

I think its confusing. Let me explain again.

If 2nd Wednesday (because he has not got any task for 1st Wednesday) has not yet gone by either date_of_alloted or current date then:
This row will be in query output from 2 days before when we run query upto 2nd Wednesday + 2 days.

Ok, if 2nd wednesday has gone then loop for 4th Wednesday.

I am really not any idea, how do i achieve this by select query's where part by simultaneously looking into type (varchar) column.

Please help me.

Thanks and Regards
Girish Sharma

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2011-11-09 : 11:01:45
You might want to do some research on how others have implemented a scheduler. Possibly, even look at how SQL Sever does it. I've done this sort of thing in the past and I think jamming all that into into a "Type" column is NOT the way to go. One suggestion I'll make is that this type of task lends itself very well to bit masking..
Go to Top of Page

GirishKumarSharma
Starting Member

10 Posts
Posted - 2011-11-09 : 11:51:46
Then ?
I can make changes in table for required output, but only after view the code or some guideline in this regard please.

Thanks & Regards
Girish Sharma
Go to Top of Page

GirishKumarSharma
Starting Member

10 Posts
Posted - 2011-11-09 : 23:24:14
Any update/help please?
I will store all "TYPE" columns info in another table for TYPE="Week Day". But question is which SQL/Function/SP will answer my question please.
Go to Top of Page

GirishKumarSharma
Starting Member

10 Posts
Posted - 2011-11-10 : 00:31:58
Here it is new table and sample data :

create table dbf1(user_code varchar(10),date_of_alloted datetime,from_date datetime,type varchar(20),description varchar(200),priority varchar(3),dayname varchar(9),w1 int,w2 int,w3 int,w4 int,w5 int)

insert into dbf1 values('A01',getdate(),getdate(),'Week Day','Full DB Backup','***','Sunday',0,1,0,1,0)

insert into dbf1 values('S07',getdate()-4,getdate()-4,'Week Day','User Privileges','*','Saturday',1,0,1,1,0)

insert into dbf1 values('B05',getdate()+3,getdate()+3,'Week Day','Export Site1 Schema','**','Wednesday',0,0,0,0,1)

Now let me explain. First Row :
User A01 has got a task 2nd and 4th Sunday of every month on (date_of_alloted) i.e. 13 and 20th Nov. 2011. So,


If date_of_running_query sees that 13 Nov has been gone Then 
-- Because it got first non-zero value in W2 i.e. 2nd Sunday=13-11-2011
   If date_of_running_query is between 18 Nov to 22 Nov Then -- Because user has one more non-zero value in next W<n> column.
     The Row will be part of query output.
   Else
     The Row will be Not be part of query output.
   Endif
Else
   If date_of_running_query is between 11 Nov to 15 Nov Then
     The Row will be part of query output.
   Else
     The Row will not be part of query output.
Endif


Thanks & Regards
Girish Sharma
Go to Top of Page

Lamprey
Master Smack Fu Yak Hacker

4614 Posts
Posted - 2011-11-10 : 11:29:54
I'm not sure if this helps, but here is a little insight into how SLQ Server strucutes it's sysschedules table:
http://msdn.microsoft.com/en-us/library/ms178644.aspx

The thing they do that I was pointing out before is to use bit masking. That makes it much easier to determine if a schudle should be run today. Here is a simple sample, obviously things get more complicated if you want to schedule things on a different period basis like weekly or monthly:
DECLARE @Sunday INT = 1;
DECLARE @Monday INT = 2;
DECLARE @Tuesday INT = 4;
DECLARE @Wednesday INT = 8;
DECLARE @Thursday INT = 16;
DECLARE @Friday INT = 32;
DECLARE @Saturday INT = 64;

-- If you want to run on Sunday and Tuesday you just OR the values together
DECLARE @Days INT = @Sunday | @Tuesday; 

-- To check to see if the day is a day you want to run, you AND the values
SELECT 
    @Days & @Monday AS NoRun, -- equals 0
    @Days & @Tuesday AS Run -- not equal to zero
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site