?
Solved

Help with changing this query.

Posted on 2003-03-24
45
Medium Priority
?
228 Views
Last Modified: 2013-12-24
Currently the cf query structure below allocates the number of days requested by the employee.  
I want to change the query structure to limit the maximum amount of days given for each project_id to 4 days.
How can I change my query structure to do this?
Thanks.

<!--  here is my code -->
<cfquery name="Query_1" datasource="#dsn#">
select building,floor,cube,emp_id,project_id(total_days-project_days) as days
From project_table
order by project_month
</cfquery>
<cfloop query="Query_1">
<cfif form.requested_days GT days>
<cfquery  datasource="#dsn#">
INSERT INTO emp_table (days,building,floor,Curdate,emp_name,emp_id,project_id)
VALUES (#int(days)#,'#building#','#floor#','#dateformat(Now(),"DD-MMM-YYYY")#','#form.employee#','#emp_id#','#project_id#')
</cfquery>
<cfset form.requested_days = form.requested_days - days>
<cfelse>
<cfquery  datasource="#dsn#">
INSERT INTO emp_table (days,building,floor,Curdate,emp_name,emp_id,project_id)
VALUES (#form.requested_days#,'#building#','#floor#','#dateformat(Now(),"DD-MMM-YYYY")#','#form.employee#','#emp_id#','#project_id#')
</cfquery>
</cfif>
</cfloop>
0
Comment
Question by:g118481
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 21
  • 13
  • 7
  • +1
45 Comments
 
LVL 9

Expert Comment

by:HamdyHassan
ID: 8196091
The answer will be based on the meaning of
project_id(total_days-project_days) ???


You need to post the source code of this function project_id?

And what the meaning of two parameters total_days and project_days ?

Where do you get these values from ?


The answer could be simple as hardconding "4" as parameter to this function

It could be

<cfquery name="Query_1" datasource="#dsn#">
select building,floor,cube,emp_id,4 as days
From project_table
order by project_month
</cfquery>

or it could be
<cfquery name="Query_1" datasource="#dsn#">
select building,floor,cube,emp_id,project_id(4-project_days) as days
From project_table
order by project_month
</cfquery>


or could be
<cfquery name="Query_1" datasource="#dsn#">
select building,floor,cube,emp_id,project_id(total_days-4) as days
From project_table
order by project_month
</cfquery>

0
 
LVL 1

Author Comment

by:g118481
ID: 8196606
HamdyHassan,

There should be a comma between project_id and total_days-project_days) as days.  Sorry for the error.

In the project_table there are projects with different amounts of project days left.

The employee uses the form to request a certain number of days from this table.  What the employee gets back is sometimes one project_id, or many, based upon how many days the project_id has verses how many days the employees requested.  

What I want the code to change to do is, not give more than 4 days per each project_id to the employee.  

Example:  If the employee request 14 days in the form, the code needs to go to the Project_table and allocate to the employee however many of project_ids equal 14 days, with each project_id days allocated <= 4 days.  
Now in the table, the project_id may have more than 4 days available, but the code should not give more than 4 days of that to the employee.

Example:
Project_id: 7506      Days:  4
Project_id: 7507      Days:  4
Project_id: 7508      Days:  4
                Total Days:  12

0
 
LVL 9

Expert Comment

by:HamdyHassan
ID: 8197198
Now it's better however I don't understand your question, please give a complete example

by the way, you have a lot of Coldfusion open questions , one is back to March 2002, my advice to take a time to close/clean/reward them, otherwise no one here will have a good reason to answer new questions by you.
0
Flexible connectivity for any environment

The KE6900 series can extend and deploy computers with high definition displays across multiple stations in a variety of applications that suit any environment. Expand computer use to stations across multiple rooms with dynamic access.

 
LVL 1

Author Comment

by:g118481
ID: 8197359
HamdyHassan,

Thanks for alerting me to the other opened questions.  The were on the hidden page and I wasn't paying much attention to them.  I have closed out all but my current 3 questions, and one of them will be closed as soon as I can test the suggestions.

What is it about my question you don't understand, and what can I provide to help?
0
 
LVL 9

Expert Comment

by:HamdyHassan
ID: 8197720
I guess you need to limit result by max 4 days, so the employee will get more than one project but at the same time he will have max 4 days on each.

If this true, then you need to make this changes

<cfquery name="Query_1" datasource="#dsn#">
select building,floor,cube,emp_id,project_id,4 as days
From project_table
where (total_days - project_days) >= 4
order by project_month
</cfquery>



this query will return 4 days as days for projects that still have more than 4 days, then in your result
0
 
LVL 9

Expert Comment

by:HamdyHassan
ID: 8197736
and if you want to include projects with less than 4 days left,  you need a union

<cfquery name="Query_1" datasource="#dsn#">

select building,floor,cube,emp_id,project_id,4 as days
From project_table
where (total_days - project_days) >= 4

union

select building,floor,cube,emp_id,project_id, (total_days - project_days) as days
From project_table
where (total_days - project_days) < 4
order by project_month

</cfquery>




about the result, I can see you did a good job at your cfloop


0
 
LVL 1

Author Comment

by:g118481
ID: 8197868
HamdyHassan,

I think you missunderstood my question.  I am not trying to find project_days that are equal to or less than 4; most project_ids are almost always greater than 4 days.

I want to give the employee just 4 days of any project.
I want the code to give several projects to the employee.

When the employee ask for projects and a total of days in the form, the code needs to give a maximum of 4 hours for each project_id.  I the employee asked for 20 days of projects, then he/she should receive at least 5 projects with a maxium of 4 days each.  That equals to 20 days.
0
 
LVL 9

Expert Comment

by:HamdyHassan
ID: 8199570
I understand your question and your last post confirm what I already understand.

Now let me tell you my idea,

at table project_table, you have two type of records

(1) (total_days - project_days) could be >= 4
     This group contains all projects with more 4 days left

(2) (total_days - project_days) could be < 4
     This group contains all projects with less 4 days left

Are you agree so far?

Now,
for group (1) , we need to assign only 4 days
for group (2) , we need to assign what we have , could be 3 or 2 or 1

Are you agree so far?

So, at our query
for group (1) , we will select "4" as days
for group (2) , we will have any difference we have


then at CFLOOP, you will have a mix of records of both groups, and using
<cfset form.requested_days = form.requested_days - days>
you will insert records


Give it a try and let me know what you get


0
 
LVL 6

Expert Comment

by:dash420
ID: 8200185
if you don't mind are u Brad.
brad h <cgttsp01@yahoo.com>
0
 
LVL 1

Author Comment

by:g118481
ID: 8202447
HamdyHassan,

Thanks for the reply.  I believe I understand your suggestion.  However, I am not sure how to code it.

Would you mind giving me an example?  

Thanks,
Stephen
0
 
LVL 1

Author Comment

by:g118481
ID: 8202506
dash420,

Don't know any brad, I am Stephen.  Can you answer my question?
0
 
LVL 6

Expert Comment

by:dash420
ID: 8202631
why i asked u are brad or not. Because "cgttsp01" cheated me and he have the same requirement as yours. I am feeling bad to write he just used me.
leave it.

I think this will work for u. reply me if not.
 
<cfquery name="Query_1" datasource="#dsn#">
select building,floor,cube,emp_id,project_id(total_days-project_days) as days
From project_table
order by project_month
</cfquery>
<cfloop query="Query_1">
<cfif form.requested_days GT 4>
<cfquery  datasource="#dsn#">
INSERT INTO emp_table (days,building,floor,Curdate,emp_name,emp_id,project_id)
VALUES (4,'#building#','#floor#','#dateformat(Now(),"DD-MMM-YYYY")#','#form.employee#','#emp_id#','#project_id#')
</cfquery>
<cfset form.requested_days = form.requested_days - 4>
</cfif>
</cfloop>
0
 
LVL 6

Expert Comment

by:dash420
ID: 8202645
why i asked u are brad or not. Because "cgttsp01" cheated me and he have the same requirement as yours. I am feeling bad to write he just used me.
leave it.

I think this will work for u. reply me if not.
 
<cfquery name="Query_1" datasource="#dsn#">
select building,floor,cube,emp_id,project_id(total_days-project_days) as days
From project_table
order by project_month
</cfquery>
<cfloop query="Query_1">
<cfif form.requested_days GT 4>
<cfquery  datasource="#dsn#">
INSERT INTO emp_table (days,building,floor,Curdate,emp_name,emp_id,project_id)
VALUES (4,'#building#','#floor#','#dateformat(Now(),"DD-MMM-YYYY")#','#form.employee#','#emp_id#','#project_id#')
</cfquery>
<cfset form.requested_days = form.requested_days - 4>
</cfif>
</cfloop>
0
 
LVL 1

Author Comment

by:g118481
ID: 8202932
dash420,

sorry to hear of that. It is not unusual for requirements to be similar.  In fact, I have often used the code from questions here in EE for my own use.

There are good people and then there are not so good people.  

Is the code you did for the other person something you think I could use?

I see you have given an example for me to test.  I will do that and get back to you.

Stephen
0
 
LVL 6

Expert Comment

by:dash420
ID: 8202999
ok i am leaving now. If u get any problem jsut write it to me.
0
 
LVL 9

Expert Comment

by:HamdyHassan
ID: 8203661
Here is the code

<cfquery name="Query_1" datasource="#dsn#">

select building,floor,cube,emp_id,project_id,4 as days
From project_table
where (total_days - project_days) >= 4

union

select building,floor,cube,emp_id,project_id, (total_days - project_days) as days
From project_table
where (total_days - project_days) < 4
order by project_month

</cfquery>

<cfloop query="Query_1">
<cfif form.requested_days GT days>
<cfquery  datasource="#dsn#">
INSERT INTO emp_table (days,building,floor,Curdate,emp_name,emp_id,project_id)
VALUES (#int(days)#,'#building#','#floor#','#dateformat(Now(),"DD-MMM-YYYY")#','#form.employee#','#emp_id#','#project_id#')
</cfquery>
<cfset form.requested_days = form.requested_days - days>
<cfelse>
<cfquery  datasource="#dsn#">
INSERT INTO emp_table (days,building,floor,Curdate,emp_name,emp_id,project_id)
VALUES (#form.requested_days#,'#building#','#floor#','#dateformat(Now(),"DD-MMM-YYYY")#','#form.employee#','#emp_id#','#project_id#')
</cfquery>
</cfif>
</cfloop>



0
 
LVL 6

Expert Comment

by:dash420
ID: 8210672
hi Stephen,
it was work or not? only condition part you need to change what i understood from your problem. try with above post comment and only change this line

<cfif form.requested_days GT 4> to
<cfif form.requested_days GTE 4>

0
 
LVL 10

Expert Comment

by:substand
ID: 8216072
change your last query which is

<cfquery  datasource="#dsn#">
INSERT INTO emp_table (days,building,floor,Curdate,emp_name,emp_id,project_id)
VALUES (#form.requested_days#,'#building#','#floor#','#dateformat(Now(),"DD-MMM-YYYY")#','#form.employee#','#emp_id#','#project_id#')
</cfquery>


to this:

<cfquery  datasource="#dsn#">
INSERT INTO emp_table (days,building,floor,Curdate,emp_name,emp_id,project_id)
VALUES (#min(form.requested_days,4)#,'#building#','#floor#','#dateformat(Now(),"DD-MMM-YYYY")#','#form.employee#','#emp_id#','#project_id#')
</cfquery>
0
 
LVL 1

Author Comment

by:g118481
ID: 8218382
dash420,

Your code suggestion does not work correctly for me.
When I use the form to request the days, this is passed to your code, and it does not give the number of days requested.

Any suggestions?
0
 
LVL 6

Expert Comment

by:dash420
ID: 8222543
i want clarification.

user submit form to request the days i.e. form.requested_days if he input 14, u wrote it should be
like
then what about 2 remaining days.

Project_id: 7506      Days:  4
Project_id: 7507      Days:  4
Project_id: 7508      Days:  4

This example is data of the "emp_table" after insertion. This leads to another doubt.

suppose table data fetchded through select clause like this
this data  of project_table

Project_id   Days

7506         2
7507         10
7508         4

Then what you want in the emp_table. whether project_id 7506 will enter into emp_table with request days 2 or what?

Describe it so that we can solved your problem.

Regards,
Dash
0
 
LVL 1

Author Comment

by:g118481
ID: 8239010
Dash420,

Thanks for your response.  

When the user requests 14 days, then the code should give the following, and write it to the table:

Project_id: 7506      Days:  4
Project_id: 7507      Days:  4
Project_id: 7508      Days:  4
Project_id: 7509      Days:  2

Sorry I haven't got back to you till now.  But I don't have a way to test code at home on the weekend.

stephen
0
 
LVL 1

Author Comment

by:g118481
ID: 8239322
I have increased the points for this question, as the solution may be a little more difficult than first thought.

stephen
0
 
LVL 1

Author Comment

by:g118481
ID: 8240433
I believe what is left to be answered for this question, is how can I code the following:

1. query the 'Project_table' to find the days available.(this is done by this query:)
<cfquery name="Query_1" datasource="#dsn#">
select building,floor,cube,emp_id,project_id,(total_days-project_days) as days
From project_table
</cfquery>

2.  this is the tricky part, which I don't have yet:
loop through the recordset for the above query, and give the employee a maximum of '4 days' for each Project_id.

3.  the same insert query needs to give the employee the total amount of days from the "form.requested_days".
(in other words, the amount of days requested has to be divided up into multiple project_ids that are a maximum of 4 hours each that total the days requested.)

For example:
If the employee requested 16 days in the form.
The code needs to write this to the emp_table, along with the other field of course:

Project_id: 7506      Days:  4
Project_id: 7507      Days:  4
Project_id: 7508      Days:  4
Project_id: 7509      Days:  4
0
 
LVL 10

Expert Comment

by:substand
ID: 8240539
your first query is fine.
here's part 2 and 3:

<cfset tdays=form.days>
<cfloop condition="tdays gte 4">

<cfquery name="ins" datasource="#datasrc#">
insert into yourtable (your_fields, days)
values (all_your_values,4)
</cfquery>
<cfset tdays=tdays-4>
</cfloop>

<cfif tdays gt 0 and tdays lt 4>

<cfquery name="ins" datasource="#datasrc#">
insert into yourtable (your_fields, days)
values (all_your_values,#tdays#
</cfquery>

</cfif>
0
 
LVL 10

Expert Comment

by:substand
ID: 8240543
i missed putting in my last ")" on the last query. my whole post should read:

your first query is fine.
here's part 2 and 3:

<cfset tdays=form.days>
<cfloop condition="tdays gte 4">

<cfquery name="ins" datasource="#datasrc#">
insert into yourtable (your_fields, days)
values (all_your_values,4)
</cfquery>
<cfset tdays=tdays-4>
</cfloop>

<cfif tdays gt 0 and tdays lt 4>

<cfquery name="ins" datasource="#datasrc#">
insert into yourtable (your_fields, days)
values (all_your_values,#tdays#)
</cfquery>

</cfif>

0
 
LVL 1

Author Comment

by:g118481
ID: 8241168
substand,

I tested your suggestion, and it does not work the way I need.  Your suggestion simply gives 4 hours for each project_id it finds from the initial select query.  
0
 
LVL 10

Expert Comment

by:substand
ID: 8241213
i thought if they have 14 hours then you want:

4
4
4
2

or 16 would be
4
4
4
4

or 17 would be
4
4
4
4
1

right?

maybe i'm not clear on what you're asking, as thats what i thought it was and my code did????


0
 
LVL 6

Expert Comment

by:dash420
ID: 8243112
Stephen

Try this


<cfloop query="Query_1">
  <cfif form.requested_days GTE 4>
    <cfif form.requested_days GT days>
      <cfquery  datasource="#dsn#">
      INSERT INTO emp_table (days,building,floor,Curdate,emp_name,emp_id,project_id)
VALUES( 4,'#building#','#floor#','#dateformat(Now(),"DD-MMM-YYYY")#','#form.employee#','#emp_id#','#project_id#')
    </cfquery>
    <cfset form.requested_days = form.requested_days - 4>
    <cfelse>
       <cfquery  datasource="#dsn#">
      INSERT INTO emp_table (days,building,floor,Curdate,emp_name,emp_id,project_id)
VALUES( #days#,'#building#','#floor#','#dateformat(Now(),"DD-MMM-YYYY")#','#form.employee#','#emp_id#','#project_id#')
    </cfquery>
     <cfset form.requested_days = form.requested_days - days>
    </cfif>
     
  <cfelse>
     <cfquery  datasource="#dsn#">
      INSERT INTO emp_table (days,building,floor,Curdate,emp_name,emp_id,project_id)
VALUES( #form.requested_days#,'#building#','#floor#','#dateformat(Now(),"DD-MMM-YYYY")#','#form.employee#','#emp_id#','#project_id#')               </cfquery>
   <cfbreak/>
</cfif>
</cfloop>

Regards,
Dash
0
 
LVL 1

Author Comment

by:g118481
ID: 8245769
Dash,

Your newest changes are working better, but there are still problems.  Here is what its doing that needs to change.

1.  If a project_id in the project_table has less than 4 days available, then your code gives 4 days.  It needs to give the correct value.

2.  Your last query seems to be giving more than 4 days.  After testing it this morning it gave 8 days.

I believe we are really close.  Thanks for your time.
I look forward to hearing from you soon.

Stephen
0
 
LVL 9

Expert Comment

by:HamdyHassan
ID: 8245803
Did you try my post


<cfquery name="Query_1" datasource="#dsn#">

select building,floor,cube,emp_id,project_id,4 as days
From project_table
where (total_days - project_days) >= 4

union

select building,floor,cube,emp_id,project_id, (total_days - project_days) as days
From project_table
where (total_days - project_days) < 4
order by project_month

</cfquery>

<cfloop query="Query_1">
<cfif form.requested_days GT days>
<cfquery  datasource="#dsn#">
INSERT INTO emp_table (days,building,floor,Curdate,emp_name,emp_id,project_id)
VALUES (#int(days)#,'#building#','#floor#','#dateformat(Now(),"DD-MMM-YYYY")#','#form.employee#','#emp_id#','#project_id#')
</cfquery>
<cfset form.requested_days = form.requested_days - days>
<cfelse>
<cfquery  datasource="#dsn#">
INSERT INTO emp_table (days,building,floor,Curdate,emp_name,emp_id,project_id)
VALUES (#form.requested_days#,'#building#','#floor#','#dateformat(Now(),"DD-MMM-YYYY")#','#form.employee#','#emp_id#','#project_id#')
</cfquery>
</cfif>
</cfloop>



0
 
LVL 6

Accepted Solution

by:
dash420 earned 600 total points
ID: 8245887
Hi Stephen,

just changes code like this


cfloop query="Query_1">
 <cfif form.requested_days GTE 4>
   <cfif form.requested_days GT days and days GTE 4>
     <cfquery  datasource="#dsn#">
     INSERT INTO emp_table (days,building,floor,Curdate,emp_name,emp_id,project_id)
VALUES( 4,'#building#','#floor#','#dateformat(Now(),"DD-MMM-YYYY")#','#form.employee#','#emp_id#','#project_id#')
   </cfquery>
   <cfset form.requested_days = form.requested_days - 4>
   <cfelse>
      <cfquery  datasource="#dsn#">
     INSERT INTO emp_table (days,building,floor,Curdate,emp_name,emp_id,project_id)
VALUES( #days#,'#building#','#floor#','#dateformat(Now(),"DD-MMM-YYYY")#','#form.employee#','#emp_id#','#project_id#')
   </cfquery>
    <cfset form.requested_days = form.requested_days - days>
   </cfif>
     
 <cfelse>
    <cfquery  datasource="#dsn#">
     INSERT INTO emp_table (days,building,floor,Curdate,emp_name,emp_id,project_id)
VALUES( #form.requested_days#,'#building#','#floor#','#dateformat(Now(),"DD-MMM-YYYY")#','#form.employee#','#emp_id#','#project_id#')               </cfquery>
  <cfbreak/>
</cfif>
</cfloop>
0
 
LVL 1

Author Comment

by:g118481
ID: 8245960
HamdyHassan,

I did try your suggestion, however, it does not work for me.  Sorry, but I do appreciate your time and effort.  From my test, it looks like dash420 will have the answer to this question for me.  But I am going to compensate you for your time by opening another question with a title in your name so you can receive some points for your effort.

Thanks again,
Stephen
0
 
LVL 1

Author Comment

by:g118481
ID: 8245975
Dash420,

Your suggestion works great.  Thanks you for your time on this one.  And thanks for sticking with the task.  Others might have lost interest, but not you.  Thanks again!

Stephen
0
 
LVL 6

Expert Comment

by:dash420
ID: 8246022
Thanks Stephen,

You most welcome.

Regards,
Dash
0
 
LVL 1

Author Comment

by:g118481
ID: 8249001
Dash,

I'm doing more testing on the code you suggested, and I have found a problem.

If a 'product_id' in the 'product_table' has more days available than the days requested by the employee, the code gives all the days for that record.

Here is an example of what the code write to the emp_table:
days     building floor     curdate         emp_id     project_id
0     SP-1     E4     01-Apr-2003 TED345     7500
0     SP-1     E4     01-Apr-2003 TED345     7501
0     SP-1     E4     01-Apr-2003 TED345     7502
4     SP-1     E4     01-Apr-2003 TED345     7503
4     SP-1     E4     01-Apr-2003 TED345     7504
30     SP-1     E4     01-Apr-2003 TED345     7505
-18     SP-1     E4     01-Apr-2003 TED345     7506

How can I edit the code to correct this issue?

Stephen
0
 
LVL 6

Expert Comment

by:dash420
ID: 8251426
Could u please give me what is the "form.requested_days" and the result of this query.

select building,floor,cube,emp_id,project_id(total_days-project_days) as days
From project_table
order by project_month

particularly "days" column.

0
 
LVL 1

Author Comment

by:g118481
ID: 8253060
Dash,

I am not sure what you are asking for here?
Do you want the code for the form?

There is no 'days' column. 'Days' is the result of subtracting the days worked on the project (project_days) from the total days estimated for the project (total_days).
//// (total_days-project_days) as days ///

The above query gets the project records from the project_table that have 'days' greater than 0.

Thanks again for your time.  If there is any way I can further explain, please let me know.
0
 
LVL 6

Expert Comment

by:dash420
ID: 8258629
No Stephen,
I want the value to test my code. I know there is no column for days as it is subtraction of two column.

i want what user input that is requested days (form.requested_days)

query result of this query

select building,floor,cube,emp_id,project_id(total_days-project_days) as days
From project_table
order by project_month

you just run this query in your database and provide me result.

hope this clear to you.

Thanks,
Dash
 
0
 
LVL 1

Author Comment

by:g118481
ID: 8260591
Dash,
Ok, I understand.  
The user can enter up to as many days as there are available, but normally the amount of days requested is between 20 and 60.

Here is the query results you requested.
Also, my work is giving me a laptop with all the software I need to use for testing at home.  So, I can work on this over the weekend, if you're available.


Building Floor Cube Project Lead ID Project ID Days AVAILABLE
TC B3 R-07 000425 000425 0
TC B3 R-07 000425 000425 0
TC B3 R-07 000425 000425 0
TC B3 R-07 000425 000425 22
TC B3 R-07 000425 000425 7
TC B3 R-07 000425 000425 7
TC B3 R-07 000425 000425 30
TC B3 R-07 000425 000425 100
TC B3 R-07 000425 000425 6
SABLE N/A N/A 000398 000398 39
TC B3 R-07 000425 000425 10
SABLE N/A N/A 000398 000398 735
SABLE N/A N/A 000398 000398 68
TC B3 R-07 000425 000425 212
SABLE N/A N/A 000398 000398 306
SABLE N/A N/A 000398 000398 72
TC B3 R-07 000425 000425 85
TC B3 R-07 000425 000425 65
TC B3 R-07 000425 000425 55
SABLE N/A N/A 000398 000398 625
TC B3 R-07 000425 000425 6
          Total:   2450.0  
0
 
LVL 1

Author Comment

by:g118481
ID: 8260606
Dash,

Should I open another question so you can get points for what you are doing for this question, or does the problem still fall under the original question.

I just want to be fair. You are spending some time on this issue.  I do not mind opening another question for you on this pressing issue.  

Thanks again for your time.

Stephen
0
 
LVL 6

Expert Comment

by:dash420
ID: 8260736
Stephen,
it is upto u to decide whether this new problem or old problem. points no matter.

you wrote my code inserting the record like this.

days     building floor     curdate         emp_id     project_id
0     SP-1     E4     01-Apr-2003 TED345     7500
0     SP-1     E4     01-Apr-2003 TED345     7501
0     SP-1     E4     01-Apr-2003 TED345     7502
4     SP-1     E4     01-Apr-2003 TED345     7503
4     SP-1     E4     01-Apr-2003 TED345     7504
30     SP-1     E4     01-Apr-2003 TED345     7505
-18     SP-1     E4     01-Apr-2003 TED345     7506

as building and floor are SP-1 and E4 respectively but result of the query showing as

TC B3 R-07 000425 000425 0
TC B3 R-07 000425 000425 0
TC B3 R-07 000425 000425 0
TC B3 R-07 000425 000425 22
TC B3 R-07 000425 000425 7
TC B3 R-07 000425 000425 7
TC B3 R-07 000425 000425 30
TC B3 R-07 000425 000425 100
TC B3 R-07 000425 000425 6
SABLE N/A N/A 000398 000398 39
TC B3 R-07 000425 000425 10
SABLE N/A N/A 000398 000398 735
SABLE N/A N/A 000398 000398 68
TC B3 R-07 000425 000425 212
SABLE N/A N/A 000398 000398 306
SABLE N/A N/A 000398 000398 72
TC B3 R-07 000425 000425 85
TC B3 R-07 000425 000425 65
TC B3 R-07 000425 000425 55
SABLE N/A N/A 000398 000398 625
TC B3 R-07 000425 000425 6

where building and floor TC and B3.

and also no project_id is 7500 as first insert element but here project_id is 000425.

I think we are looping through the result of the above query and chcking the value of days. not inserting more than 4. but record inserted into table different.

I think it will understand to you.

Regards,
Dash
0
 
LVL 1

Author Comment

by:g118481
ID: 8261180
Dash,

I had reloaded the table since the first testing, that is why some of the records look different.  But I haven't changed the design of the table, all the fields are the same.  I just put more up-to-date data in the table.

It shouldn't matter what the data is, should it.  The main thing is that days are available, right?

Stephen
0
 
LVL 6

Expert Comment

by:dash420
ID: 8261239
stephen. i want which sequence of input user requests and days it is wrong. as u mention earlier.

"If a 'product_id' in the 'product_table' has more days available than the days requested by the employee, the code gives all the days for that record."

i also wonder how it was fails. I need to put extra conditions for that.

u can test again with the table data and if it is fails just gave me info where exactly it is fails.




0
 
LVL 1

Author Comment

by:g118481
ID: 8261323
Dash,

Ok, let me now run a new test.
I am going to open a new question for you on this issue.
Give me just a few minutes to do so.

Thank you again so much for your help on this issue.

Stephen
0
 
LVL 1

Author Comment

by:g118481
ID: 8261528
Dash,

I have opened a new question for this issue, so you can receive points for your time.  Here is the url: http://www.experts-exchange.com/Web/WebDevSoftware/ColdFusion/Q_20572989.html
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question