Need help with query - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Need help with query

Author

Topic

Shem
Yak Posting Veteran

62 Posts

Posted - 2007-01-09 : 09:48:33

I have this query that must display Weekly tasks assigned to the user, now if the task has been
captured then it must not until the following start date, which would be 7 days from the start
date.

I have come quite far in the query, it pulls up Weekly assigned tasks, it aslo checks whether its
been captured or not, I've also got a WHEN loop to loop through start dates, but i'm struggling with
having it display again once the next start date comes.

here is my query so far:

USE aoms
DECLARE @setDate datetime
DECLARE @compDate int
DECLARE @setCompDate datetime

/* Get the start date of the task */
SET @setDate = (SELECT DISTINCT recurring FROM tblTaskForm WHERE (assignValue = '1' OR assignValue IN (select fldUsrsPosition from tblUsers where fldUsrsID = '1') OR assignValue = 'all')
AND recurringType = 'weekly')

/* Get the amount of days to complete the task */
SET @compDate = (SELECT DISTINCT compDays FROM tblTaskForm WHERE (assignValue = '1' OR assignValue IN (select fldUsrsPosition from tblUsers where fldUsrsID = '1') OR assignValue = 'all')
AND recurringType = 'weekly')

SET @setCompDate = DATEADD(d, @compDate, @setDate)

/*if current date smaller than completion date then display else..*/
IF GetDate() < @setCompDate

BEGIN
SELECT tblTaskForm.*
FROM tblTaskForm
WHERE (assignValue = '1' OR assignValue IN (select fldUsrsPosition from tblUsers where fldUsrsID = '1') OR assignValue = 'all')
AND recurringType = 'weekly'
AND DateDiff(w, recurring, GetDate()) >= '0'
/* this part of the query checks wether the task has been captured*/
AND taskFormID NOT IN(SELECT DISTINCT taskFormID FROM tblTask WHERE taskAgent = '1')
ORDER BY recurring ASC

PRINT 'Start date: ' + CAST(@setDate AS varchar(11))
PRINT 'Due Date: ' + CAST(DATEADD(d, @compDate, @setDate) AS varchar(11))
END

ELSE

BEGIN

/*loop through the start dates*/
WHILE DATEDIFF(d, @setDate, GetDate()) >= 0
BEGIN
SET @setDate = DATEADD(d, 7, @setDate)
END

/*this should bring up the task by the follwing start date*/
IF DATEADD(d, @compDate, @setDate) >= GetDate()
BEGIN
SELECT tblTaskForm.*
FROM tblTaskForm
WHERE (assignValue = '1' OR assignValue IN (select fldUsrsPosition from tblUsers where fldUsrsID = '1') OR assignValue = 'all')
AND recurringType = 'weekly'
AND DATEADD(d, @compDate, @setDate) >= GetDate()
/* this part of the query checks wether the task has been captured*/
AND taskFormID NOT IN(SELECT DISTINCT taskFormID FROM tblTask WHERE taskAgent = '1')
ORDER BY recurring ASC
PRINT 'Start date: ' + CAST(@setDate AS varchar(11))
PRINT 'Due Date: ' + CAST(DATEADD(d, @compDate, @setDate) AS varchar(11))
END

END

I have commented the code, if you need me to explain in more detail just shout:)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-09 : 10:00:10

Why do you keep posting same question all over again?
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77237

Peter Larsson
Helsingborg, Sweden

Shem
Yak Posting Veteran

62 Posts

Posted - 2007-01-09 : 10:06:17

I don't know?

sorry about that, my code has changed alot so I figured it was
easier to start fresh.

Shem

Shem
Yak Posting Veteran

62 Posts

Posted - 2007-01-10 : 03:20:18

ok this part checks if the task has been captured:
AND taskFormID NOT IN(SELECT DISTINCT taskFormID FROM tblTask WHERE taskAgent = '1')

If i change to this:
AND taskFormID NOT IN(SELECT DISTINCT taskFormID FROM tblTask WHERE taskAgent = '1' AND DATEDIFF(ww, taskDate, GetDate()) < 1)

then it will bring up the task every Sunday, beginning of new week,
but i'm still trying to rather get it to show on the start date.

have tried alot of variations but no luck

can anyone see the solution to this problem

Thanks
Shem

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-10 : 03:35:11

Let's try this from a different angle.

Post relevant sample data here. Also post your expected output based on the provided sample data.
Explain why the expected output looks like that (the business rules).

Peter Larsson
Helsingborg, Sweden

Shem
Yak Posting Veteran

62 Posts

Posted - 2007-01-10 : 04:20:01

only relevant info for this query
tblTaskForm - this table holds the forms to be captured.

Field Name - Field Value
taskFormID - 31
taskName - Weekly All
taskType - recurring
recurringType - weekly
recurring - 2007/01/05 (start date of task)
assignValue - all
compDays - 4 (the amount of days to complete task)

tblTask - this holds the captured task and which user captured the task

Field Name - Field Value
taskID - 36
taskFormID - 31
taskName - Weekly All
taskAgent - 1 (the user that captured the task)
taskDate - 2007/01/09 (this is the date the task was captured)

the task 'Weekly All' in tblTaskForm needs to display on the 2007/01/05 (start date) then if it is captured it must not display.
now as the task is weekly, it must re-appear in 7 days time, which would be 2007/01/12, then if it is captured it must not display.

and this must repeat every 7 days.

Also compDays is the amount of days in which the task must be completed so if we look at the start date: 2007/01/05, the
completion date would be 2007/01/09, once the current date gets passed 2007/01/09, the task must no longer display
until the 2007/01/12 and so and so on.

I hope I have been clear enough here.
That is all that needs to happen.

Thanks
Shem

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-10 : 04:24:47

CompDays is 4 days, right? Recurring is 2007/01/05, right?
Do you not include the Recurring date?

See here for 4 days:

2007/01/05
2007/01/06
2007/01/07
2007/01/08

There are your four days. Why is 2007/01/09 included as valid date?

Peter Larsson
Helsingborg, Sweden

Shem
Yak Posting Veteran

62 Posts

Posted - 2007-01-10 : 04:32:10

I just added compDays to recurring to get a completion date.

but i see what you mean, the 5th is still a valid day.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-10 : 04:38:57

[code]-- prepare sample data
declare @tbltaskform table (taskformid int, taskname varchar(20), tasktype varchar(20), recurringtype varchar(20), recurring datetime, assignvalue varchar(10), compdays int)

insert @tbltaskform
select 31,
'Weekly All',
'recurring',
'weekly',
'20070105',
'all',
4

declare @tbltask table (taskid int, taskformid int, taskname varchar(20), taskagent int, taskdate datetime)

insert @tbltask
select 36,
31,
'Weekly All',
1,
'20070109'

-- is this what you want?
select tf.taskformid,
tf.taskname,
dateadd(day, sv.number * 7, tf.recurring) as fromdate,
dateadd(day, tf.compdays + sv.number * 7, tf.recurring) as todate,
max(case when t.taskagent is null then 0 else 1 end) as done
from @tbltaskform as tf
cross join master..spt_values as sv
left join @tbltask as t on t.taskformid = tf.taskformid
and t.taskname = tf.taskname
and t.taskdate >= dateadd(day, sv.number * 7, tf.recurring)
and t.taskdate <= dateadd(day, tf.compdays + sv.number * 7, tf.recurring)
where sv.name is null
and dateadd(day, sv.number * 7, tf.recurring) <= '20091231'
group by tf.taskformid,
tf.taskname,
dateadd(day, sv.number * 7, tf.recurring),
dateadd(day, tf.compdays + sv.number * 7, tf.recurring)[/code]

Peter Larsson
Helsingborg, Sweden

Shem
Yak Posting Veteran

62 Posts

Posted - 2007-01-10 : 04:43:02

will try it out now, and let you know if thats what i need, I can't
really make out what you've done, my tsql knowledge is'nt that good:)

Shem
Yak Posting Veteran

62 Posts

Posted - 2007-01-10 : 04:53:47

ok, I see what you have done, you have listed all the start and completion dates, and which have been completed.

'2007-01-05 00:00:00.000' to '2007-01-09 00:00:00.000' is done.
is it possible to not display it as its done, and rather only display the next following task which would be:
'2007-01-12 00:00:00.000' to '2007-01-16 00:00:00.000'

and if current date > completion date then dont display anything
until current date = '2007-01-19 00:00:00.000' which is the following start date after '2007-01-16 00:00:00.000'

does that make sense?
Shem

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-10 : 05:17:27

[code]-- prepare sample data
declare @tbltaskform table (taskformid int, taskname varchar(20), tasktype varchar(20), recurringtype varchar(20), recurring datetime, assignvalue varchar(10), compdays int)

insert @tbltaskform
select 31,
'Weekly All',
'recurring',
'weekly',
'20061113',
'all',
4

declare @tbltask table (taskid int, taskformid int, taskname varchar(20), taskagent int, taskdate datetime)

insert @tbltask
select 36,
31,
'Weekly All',
1,
'20070109'

-- is this what you want?
select tf.taskformid,
tf.taskname,
dateadd(day, sv.number * 7, tf.recurring) as fromdate,
dateadd(day, tf.compdays + sv.number * 7, tf.recurring) as todate,
dateadd(day, (1 + sv.number) * 7, tf.recurring) as nextfromdate,
dateadd(day, tf.compdays + (1 + sv.number) * 7, tf.recurring) as nexttodate
from @tbltaskform as tf
cross join master..spt_values as sv
where sv.name is null
and dateadd(day, tf.compdays + sv.number * 7, tf.recurring) <= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)[/code]

Peter Larsson
Helsingborg, Sweden

Shem
Yak Posting Veteran

62 Posts

Posted - 2007-01-10 : 05:38:51

thats just about what i need...

though if i changed taskDate to '20070116' the task does not dissappear

EG:

currently i get this:
taskFormID • taskName • fromdate • todate • nextfromdate • nexttodate
31 • Weekly All • 2007-01-05 • 2007-01-09 • 2007-01-12 • 2007-01-16

there is a captured task with the taskDate 2007-01-09
and the current date is 2007-01-10

so i should just have:
taskFormID • taskName • fromdate • todate

now if the date is 2007-01-12 i should get:
taskFormID • taskName • fromdate • todate
31 • Weekly All • 2007-01-12 • 2007-01-16

and if there is a taskDate between 2007-01-12 and 2007-01-16 then again it should show:
taskFormID • taskName • fromdate • todate

until the next start date arrives.

sorry, i obviously did not explain what i needed properly, i hope you understand what i'm trying to do now
Shem

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-10 : 05:41:08

Ok, you want the list to proceed to the latest existing value in the tbltask table, not longer?

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-10 : 05:42:24

Shem
Yak Posting Veteran

62 Posts

Posted - 2007-01-10 : 05:53:51

i tried that bit of code, it doesn't change at all, whether i change my pc date or the date of taskDate.

Basically, if the task has been captured then no results, Until next start date then display task.

so the task 2007-01-05 / 2007-01-09 has been captured as taskDate holds a date between 2007-01-05 / 2007-01-09
therefore the task 'Weekly All' must not show. Until GetDate() >= 2007-01-12

Thanks for all your patience by the way:)
Shem

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-10 : 06:00:10

[code]select tf.taskformid,
tf.taskname,
dateadd(day, sv.number * 7, tf.recurring) as fromdate,
dateadd(day, tf.compdays + sv.number * 7, tf.recurring) as todate,
dateadd(day, (1 + sv.number) * 7, tf.recurring) as nextfromdate,
dateadd(day, tf.compdays + (1 + sv.number) * 7, tf.recurring) as nexttodate
from @tbltaskform as tf
cross join master..spt_values as sv
left join @tbltask as t on t.taskformid = tf.taskformid
and t.taskname = tf.taskname
and t.taskdate >= dateadd(day, sv.number * 7, tf.recurring)
and t.taskdate <= dateadd(day, tf.compdays + sv.number * 7, tf.recurring)
where sv.name is null
and dateadd(day, tf.compdays + sv.number * 7, tf.recurring) <= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
group by tf.taskformid,
tf.taskname,
dateadd(day, sv.number * 7, tf.recurring),
dateadd(day, tf.compdays + sv.number * 7, tf.recurring),
dateadd(day, (1 + sv.number) * 7, tf.recurring),
dateadd(day, tf.compdays + (1 + sv.number) * 7, tf.recurring)
having max(case when t.taskagent is null then 0 else 1 end) = 0[/code]

Peter Larsson
Helsingborg, Sweden

Shem
Yak Posting Veteran

62 Posts

Posted - 2007-01-10 : 06:12:14

currently it shows no results, which is right as the current date is the 10th,
so on the 12th Weekly All should show again, but it does not, even if i change current date to 13th.

Thanks
Shem

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-10 : 06:20:40

If you change the date to next month then?

I think it is about time you provide some more expected output.
Post a list of what you expect from the query IN REAL FORMAT.

31	Weekly All	2006-11-13 	2006-11-17 	2006-11-20 	2006-11-24 
31	Weekly All	2006-11-20 	2006-11-24 	2006-11-27 	2006-12-01 
31	Weekly All	2006-11-27 	2006-12-01 	2006-12-04 	2006-12-08 
31	Weekly All	2006-12-04 	2006-12-08 	2006-12-11 	2006-12-15 
31	Weekly All	2006-12-11 	2006-12-15 	2006-12-18 	2006-12-22 
31	Weekly All	2006-12-18 	2006-12-22 	2006-12-25 	2006-12-29 
31	Weekly All	2006-12-25 	2006-12-29 	2007-01-01 	2007-01-05 
31	Weekly All	2007-01-01 	2007-01-05 	2007-01-08 	2007-01-12

Peter Larsson
Helsingborg, Sweden

Shem
Yak Posting Veteran

62 Posts

Posted - 2007-01-10 : 06:44:10

This query is for web application i'm busy with, the application is a task management app
so the app shows weekly tasks, which the user then clicks on and fills in all the data,
if the task is captured for that week it must not show until next week when it has to be captured again.

so i don't need to display records, rather just a record, i just need to display 'Weekly all' once a week
at the right date every week.

ok lets start off with Weekly task not being captured in tblTask

Current Date: 2007-01-05
so output would be:
31 Weekly All 2007-01-05 2007-01-09

Now the user captures Weekly task, now we have taskDate holding the date of 2007-01-05
Current Date: 2007-01-05
so output would be:
(nothing)

Current Date: 2007-01-12
so output would be:
31 Weekly All 2007-01-12 2007-01-16

Does this make better sense?
Thanks
Shem

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-10 : 06:56:56

No, I mean row by row!
What you just posted is the same thing as before.

What happens to all the other future weekly rows?

Peter Larsson
Helsingborg, Sweden

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources