help with job extraction query

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 2005 Forums

Transact-SQL (2005)

help with job extraction query

Author

Topic

TheMatrixDuck
Starting Member

3 Posts

Posted - 2007-08-08 : 15:00:09

HI, I am wayyy over my head with this query requirement. We have a table of mfg jobs that contains (among others) columns for job_number, schedule_position and mfg_start_day. The query needs to extract the jobs for different mfg days. I had initially created a dynamic column called strMfgDay and populated it with a string labeled "incomplete jobs", "todays jobs", and "tomorrows jobs" and used UNION to group the jobs in that fashion. Of four manufacturing departments, each has a different "day one" start day. For instance, on day one of a 4 day mfg cycle, the door dept completes. On day two of the cycle, the job is transferred to the milling department.

I have been able to break out the requirement based on the manufacturing start day and assigning a value based on the datediff of today and the date mfg started for that job. However, the mfg department would like to see jobs from yesterday totaling to 125' (or a minimum of one job). So, the milling dept (day two) would contain not only day ones jobs for that dept, but also at least one job from the previous day (or more totaling to 125' worth).

can anyone help with a query for this?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-09 : 00:21:48

please provide your table DDL, sample data and your expected result.

KH
[spoiler]Time is always against us[/spoiler]

TheMatrixDuck
Starting Member

3 Posts

Posted - 2007-08-09 : 10:56:21

The result will populate an application grid grouped at runtime by the generated alias column. Three groups representing jobs for various manufacturing departments broken into todays jobs, tomorrows jobs and incomplete jobs.

table structure is:
ID int, not null autoinc
Jobnum, varchar(10), not null (job number)
dt_mfg_started, datetime, null (date manufacturing started for this job
job_footage, int, null (linear feet of job)
schedule_position int, null (determines order jobs enter production)

Sample data
Job# mfg_dt FT Sched_pos
2500, 8/6/07, 125, 200
2530, 8/6/07, 68, 201
1587, 8/6/07, 197, 202
4321, 8/7/07, 189, 203
9899, 8/7/07, 54, 204
7832, 8/7/07, 123, 205
7921, 8/8/07, 145, 206
5342, 8/9/07, 43, 207
6354, 8/9/07, 75, 208
3124, 8/9/07, 165, 209
4523, 8/9/07, 220, 210

The goal is to produce a result set that includes an alias for the day of a four day production cycle: value -1 thru -4 based on the jobs mfg start day and the current day. If the difference in days is 2 then that job is in day two of the cycle. The alias value would be:
"Todays Jobs" - DATEDIFF -1
"Tomorrows Jobs" - DATEFIFF +1
"Incomplete Jobs" - DATEDIFF < -1 (all others)

Todays jobs are defined by dt_mfg_started (DATEDIFF -1...-3), but include 120' (+20% fudge factor... 132') of jobs from the previous day - or at least one job if it is over 132'.

Tomorrows jobs are defined by dt_mfg_stared only (DATEDIFF +1)

An ideal result set for the above would be:
job# mfg_dt FT sched_pos iMfgDay
1324, 8/5/07, 115, 196 -3
1243, 8/5/07, 123, 197 -3
1423, 8/5/07, 121, 198 -3
1132, 8/5/07, 108, 199 -3
2500, 8/6/07, 125, 200 -2
2530, 8/6/07, 68, 201 -2
1587, 8/6/07, 197, 202 -2
4321, 8/7/07, 189, 203 -1
9899, 8/7/07, 54, 204 -1
7832, 8/7/07, 123, 205 -1
7921, 8/8/07, 145, 206 -1
5342, 8/9/07, 43, 207 0
6354, 8/9/07, 75, 208 0
3124, 8/9/07, 165, 209 0
4523, 8/9/07, 220, 210 0
6879, 8/10/07, 118, 211 +1
7689, 8/10/07, 123, 212 +1
7564, 8/10/07, 178, 213 +1
5641, 8/10/07, 65, 214 +1

Hope that helps.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources