November 9, 2011 at 3:34 am
Hi,
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
November 9, 2011 at 4:22 am
We would be able to help you better if you provide following.
•DDL for the table.
•Sample Data
•Expected report output and report parameters
•Existing scripts, if any
November 9, 2011 at 4:41 am
Hi,
First thank you for your reply and interest in my query. Here it is required informations :
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 :
Kindly let me know, if i am still unclear. I don't know how do i use formatting in this forum.
Microsoft SQL Server Management Studio9.00.1399.00
Microsoft Analysis Services Client Tools2005.090.1399.00
Microsoft Data Access Components (MDAC)2000.085.1132.00 (xpsp.080413-0852)
Microsoft MSXML2.6 3.0 4.0 5.0 6.0
Microsoft Internet Explorer6.0.2900.5512
Microsoft .NET Framework2.0.50727.42
Operating System5.1.2600
Regards
Girish Sharma
November 9, 2011 at 4:59 am
It's pretty bad table design. Do you have control on DB Design? Are you open for redesigning the table? It will solve many problems else query would be complex (and also slow).
November 9, 2011 at 5:16 am
Hi Dev,
>It's pretty bad table design.
Not really, because till non-incorporation of this new week day type task, application is running fine, no complaint. But yes, now it seems bit bad i.e. incorporating "Week Day" type task.
>Do you have control on DB Design?
Yes, no issue. But the thing is if there is drastic changes in table then i think i will have to look in vb code as well; but if it is really require then there is no second thought though.
>It will solve many problems else query would be complex (and also slow).
Kindly post your suggestion to :
1.Solve the query and eradication of slowness (if any)
Thank you Dev.
Regards
Girish Sharma
November 9, 2011 at 5:45 am
insert into dbfile values('A01',getdate()-2,getdate()-2,'Week-Wednesday-0-1-0-1-0','Export Database','*')
As I have already mentioned I am against Spoon Feeding so I am letting you design the table. However few recommendations based on insert statements above:
A01 -- ID
Date -- Effective Start Date
Date -- Effective End Date
Week -- Recursion type
Wed -- Rec_Week_Day
0 -- Week1_Bit
1 -- Week2_Bit
0 -- Week3_Bit
1 -- Week4_Bit
0 -- Week5_Bit
Export -- Task
It could be better but for your requirements it’s just sufficient.
The advantage you would get with this is you don’t need to do String operations (substring etc.) in query.
November 9, 2011 at 6:45 am
What i understood from your suggestion that i should break the "TYPE" column into different parts. Ok, suppose i do like this then ? I mean, now i am storing the 0 and 1s in different columns and name of day in another column. Now how do i query which will give me the business output please. Its not a big issue for me to store the type columns's information in more than column(s). Kindly show me your query which :
1.Give me that current date is 2nd Wednesday of the month : True/False
2.If it is true then it will come in query output.
3.If it is false then Week<n>_Bit is having 1 or not.
3(i) If no then next Week<n>_Bit.....
...
I am not getting how do i proceed to get the query output. I think i will require UDF and/or procedure which running the query, but ...
Thanks and Regards
Girish Sharma
November 9, 2011 at 9:28 pm
Any Update please?
November 10, 2011 at 6:54 am
I am not getting how do i proceed to get the query output. I think i will require UDF and/or procedure which running the query, but ...
SQL query syntaxes are same in Oracle & SQL Server (if ANSI SQL followed). I believe you are familiar with Oracle. Please write a query to generate required resultset and we will help you to bundle it up in SQL Server stored procedure (because T-SQL & PL/SQL syntaxes are different).
November 10, 2011 at 7:41 am
gksharmaajmer (11/9/2011)
Hi,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
I'm having a problem with this statement: Starting 2011-11-09, person A01 should perform this task every 2nd and 4th Wednesday? What does that even mean? Of each month? You have 5 options in some months, 4 in others... It is too arbitrary, but I suppose if that is your business rules then go for it. I just have a problem with someone being set as 1 for the 5th, because not all months have that 5th. Seems like the business rules need changing for you to build a proper application.
Jared
Jared
CE - Microsoft
November 10, 2011 at 7:46 am
Hi Dev,
Yes you are right that its very tough for a peer to do the programming in a new tech (without having architecture knowledge). I just thought on the problem and write the question in vb forum in the different direction. Means, Visual basic 6 solved the 90% problem, now in the table user will store 5 dates for given task and now SQL with date between.... will solve the problem like a charm.
Frankly speaking, i am not that much expert in PL/SQL to write such a very tough code(for me, for sure!!!). If my friend were using Oracle, then probably i could give my little try.
Here in Our Oracle Technologies (in many other too), people says "Problem Definition is the answer of Problem".
With the help of VB expert now issue seems got resolved upto 90%. [ http://www.vbforums.com/showthread.php?t=664699%5D. If still, we gets any issue, i will try another chance to come here.
Regards
Girish Sharma
November 10, 2011 at 7:51 am
Hi Dev,
Yes you are right that its very tough for a peer to do the programming in a new tech (without having architecture knowledge). I just thought on the problem and write the question in vb forum in the different direction. Means, Visual basic 6 solved the 90% problem, now in the table user will store 5 dates for given task and now SQL with date between.... will solve the problem like a charm.
Frankly speaking, i am not that much expert in PL/SQL to write such a very tough code(for me, for sure!!!). If my friend were using Oracle, then probably i could give my little try.
Here in Our Oracle Technologies (in many other too), people says "Problem Definition is the answer of Problem".
With the help of VB experts now issue seems got resolved upto 90%. [ http://www.vbforums.com/showthread.php?t=664699%5D. If still, we gets any issue, i will try another chance to come here.
Regards
Girish Sharma
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply