September 18, 2013 at 9:42 am
Hello all, First let me say thank you for the tips/tricks/best practices that I have gleaned by reading the forums. I have learned so much!!!
My issue is I need to determine a week number. Hopefully I'll explain it succintly. I have a jobtask table. Each job has a schedule start date. Using the schedule start date I need to find the week nbr (tnbr). Jobs scheduled in current week are T00, next week T01, etc. Weeks run from Sunday 00:00 to Saturday 23:59. I am doing this for a different report in a stored procedure using a cursor. After having been reading the forums it occurred to me that maybe I don't need to use a cursor. I've included pared-down table and query for brevity sake.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#jobtask','U') IS NOT NULL
DROP TABLE #jobtask
--===== Create the test table with
CREATE TABLE #jobtask
(
jobnbr varchar(8),
tasknbr varchar(3),
schstrdt datetime
)
INSERT into #jobtask (jobnbr, tasknbr, schstrdt)
SELECT '12500173','500','2013-09-18 07:30:00.000' UNION ALL
SELECT '13502889','500','2013-09-25 08:00:00.000' UNION ALL
SELECT '13506023','0','2013-09-20 07:30:00.000' UNION ALL
SELECT '13506779','0','2013-09-26 07:30:00.000' UNION ALL
SELECT '13506780','0','2013-09-29 07:30:00.000' UNION ALL
SELECT '13510071','500','2013-09-18 07:30:00.000' UNION ALL
SELECT '13510263','500','2013-09-23 07:30:00.000' UNION ALL
SELECT '13510321','500','2013-09-25 07:30:00.000' UNION ALL
SELECT '13510331','500','2013-09-25 07:30:00.000' UNION ALL
SELECT '13510527','500','2013-09-30 00:00:00.000' UNION ALL
SELECT '13510578','500','2013-10-02 00:00:00.000' UNION ALL
SELECT '13510733','500','2013-09-18 07:30:00.000' UNION ALL
SELECT '13510887','500','2013-09-18 07:30:00.000' UNION ALL
SELECT '13510945','500','2013-09-18 07:30:00.000' UNION ALL
SELECT '13510983','500','2013-09-23 00:00:00.000'
Here is what the data should look like.
jobnbr taskschstrdt tnbr
1250017350009/18/2013 07:30T00
1350288950009/25/2013 08:00T01
13506023009/20/2013 07:30T00
13506779009/26/2013 07:30T01
13506780009/29/2013 07:30T02
1351007150009/18/2013 07:30T00
1351026350009/23/2013 07:30T01
1351032150009/25/2013 07:30T01
1351033150009/25/2013 07:30T01
1351052750009/30/2013 00:00T02
1351057850010/02/2013 00:00T02
1351073350009/18/2013 07:30T00
1351088750009/18/2013 07:30T00
1351094550009/18/2013 07:30T00
1351098350009/23/2013 00:00T01
Thanks in advance and please let me know if something isn't clear.
cmw
September 18, 2013 at 9:49 am
is this based on a set Fiscal year?
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
September 18, 2013 at 9:53 am
No. Depending on the week the tnbr will change. If stored procedure run on 9/18/2013 and job schedule start date is 9/26/2013 then the tnbr is T01. If stored procedure run on 9/24/2013 and job schedule start date is 9/26/2013 then the tnbr is T00.
September 18, 2013 at 10:07 am
If you add something like this you should get what you need
select datepart(week,'09/26/13')-datepart(week,'09/18/13')
This would give you the difference between the two dates and you could simply add the "T" to the front of it.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
September 18, 2013 at 10:29 am
Thanks Dan. That works!!
September 18, 2013 at 10:47 am
On second thought that works fine as long as the schedule start date is the current year. But when i run this on a job that has a schedule start date of 01-13-2014 I get -35 as tnbr instead of 16. The range of schedule start dates will be current week out to 18 weeks.
September 18, 2013 at 10:51 am
Correction if schedule start date is 2014-01-12 the tnbr should be T17.
September 18, 2013 at 11:15 am
Sorry should have thought of that. Try DATEDIFF ( week, startdate , enddate )
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
September 18, 2013 at 11:25 am
Gosh it's so simple:-D! Thanks!!!
September 18, 2013 at 12:04 pm
As a former supervisor of mine said "the devil is in the details". I forgot about something. The stored procedure may skip weeks but the Tnbr should continue consecutively.
Using previous data, if the stored procedure skips schedule start dates in week of 09-22-2013 the next week should be T01 not T02. Will I have to do a cursor afterall?
jobnbr task schstrdt tnbr
12500173 500 09/18/2013 07:30 T00
13506023 0 09/20/2013 07:30 T00
13506780 0 09/29/2013 07:30 T01
13510071 500 09/18/2013 07:30 T00
13510527 500 09/30/2013 00:00 T01
13510578 500 10/02/2013 00:00 T01
13510733 500 09/18/2013 07:30 T00
13510887 500 09/18/2013 07:30 T00
13510945 500 09/18/2013 07:30 T00
September 18, 2013 at 12:49 pm
Sorry not following can you give an example of that. I personally would remove the cursor function from your tool box of options and pretend you never learned about something called a cursor.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
September 18, 2013 at 1:09 pm
Initially this is what I indicated the data should look like.
jobnbr task schstrdt tnbr
12500173 500 09/18/2013 07:30 T00
13502889 500 09/25/2013 08:00 T01
13506023 0 09/20/2013 07:30 T00
13506779 0 09/26/2013 07:30 T01
13506780 0 09/29/2013 07:30 T02
13510071 500 09/18/2013 07:30 T00
13510263 500 09/23/2013 07:30 T01
13510321 500 09/25/2013 07:30 T01
13510331 500 09/25/2013 07:30 T01
13510527 500 09/30/2013 00:00 T02
13510578 500 10/02/2013 00:00 T02
13510733 500 09/18/2013 07:30 T00
13510887 500 09/18/2013 07:30 T00
13510945 500 09/18/2013 07:30 T00
13510983 500 09/23/2013 00:00 T01
But there are weeks that I do not want to include in my selection criteria yet the Tnbr should still be consecutive for the following weeks. Say I want to skip jobs that are scheduled between 09/22/2013 and 09/28/2013 23:59
jobnbr task schstrdt tnbr
12500173 500 09/18/2013 07:30 T00
13502889 500 09/25/2013 08:00 T01would not be in list
13506023 0 09/20/2013 07:30 T00
13506779 0 09/26/2013 07:30 T01 would not be in list
13506780 0 09/29/2013 07:30 T02instead of T02 this would be T01
13510071 500 09/18/2013 07:30 T00
13510263 500 09/23/2013 07:30 T01 would not be in list
13510321 500 09/25/2013 07:30 T01 would not be in list
13510331 500 09/25/2013 07:30 T01 would not be in list
13510527 500 09/30/2013 00:00 T02 instead of T02 this would be T01
13510578 500 10/02/2013 00:00 T02 instead of T02 this would be T01
13510733 500 09/18/2013 07:30 T00
13510887 500 09/18/2013 07:30 T00
13510945 500 09/18/2013 07:30 T00
13510983 500 09/23/2013 00:00 T01 would not be in list
September 18, 2013 at 1:18 pm
Hum, instead of a cursor I used a while loop. Was thinking it would be so much simpler if I could do everything in one select statement!
September 18, 2013 at 1:25 pm
try this
RANK ( ) OVER (PARTITION BY Datepart(week,schstrdt) ORDER BY Datepart(week,schstrdt))
Sorry I can't test this were I am currently but I beleive this would work.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply