Jump to content


Photo

My query is a ticking time bomb!


  • Please log in to reply
11 replies to this topic

#1 Moron

Moron
  • Members
  • PipPipPip
  • Advanced Member
  • 368 posts

Posted 29 August 2006 - 01:54 PM

$RESULTDS=mssql_query("SELECT DISTINCT LH.[Employee Number], LH.[Lmo], LH.[Lda], LH.[LYR], LH.[Hours], LH.[Leave Code], M2.[HRYRAT], M2.[EMPNO], M2.[MANLAP], M2.[MANLAC], M2.[MANLTC], M2.[MSKLAB], M2.[MSKLTC], M2.[MSKLAB], M2.[MSKLTC], M2.[NAMEMI], M2.[NAMEL], M2.[NAMEF] FROM LEAVHST LH INNER JOIN MASTERL2 M2 ON LH.[Employee Number]=M2.EMPNO WHERE M2.[EMPNO] = '".$_POST['employeenumber']."' and LH.[LYR] >= '$last'  and LH.[Lda] >= '01' and LH.[Lmo] >= '07'  ORDER BY LH.[LYR] desc, LH.[Lmo] desc, LH.[Lda] desc");
$RESULT=mssql_fetch_assoc($RESULTDS);


Okay, as of right now, this is pulling exactly what I want it to pull, records on or after July 1, 2006, the start of our fiscal year (note that $last determines the last year in which July 1 occurred, but I didn't see the need to include the code here).

Anyway, it works now, but come January, the month will NOT be ">= 07" anymore.

I tried this:

if ($last == $curr_year) { 
$monlast = 07;
}else{
$monlast = 12;
}

I can echo it and it works fine, BUT, if I put LH.[Lmo] >= '$monlast' in the query, the entire query crashes.

Can anyone tell me a better way of going about this -OR- tell me why $monlast crashes the code?



#2 radar

radar
  • Members
  • PipPipPip
  • Advanced Member
  • 645 posts
  • LocationSLC

Posted 29 August 2006 - 02:26 PM

How your actual query and everything...  like yes this is your mysql part of the query but where is the php.. and give like 3 lines before and 3 lines after -- might help a little bit more ...

#3 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 29 August 2006 - 02:30 PM

you should seperate the query statement from your sql. That way you can echo out the sql statement and see if it is returning what you want.

$sql = "SELECT DISTINCT LH.[Employee Number], LH.[Lmo], LH.[Lda], LH.[LYR], LH.[Hours], LH.[Leave Code], M2.[HRYRAT], M2.[EMPNO], M2.[MANLAP], M2.[MANLAC], M2.[MANLTC], M2.[MSKLAB], M2.[MSKLTC], M2.[MSKLAB], M2.[MSKLTC], M2.[NAMEMI], M2.[NAMEL], M2.[NAMEF] FROM LEAVHST LH INNER JOIN MASTERL2 M2 ON LH.[Employee Number]=M2.EMPNO WHERE M2.[EMPNO] = '".$_POST['employeenumber']."' and LH.[LYR] >= '$last'  and LH.[Lda] >= '01' and LH.[Lmo] >= '07'  ORDER BY LH.[LYR] desc, LH.[Lmo] desc, LH.[Lda] desc";

$RESULTDS=mssql_query($sql) or die ('There is an error with your query'); 
   $RESULT=mssql_fetch_assoc($RESULTDS);

Now you can comment out your query with // then add an echo to give you your sql

$sql = "SELECT DISTINCT LH.[Employee Number], LH.[Lmo], LH.[Lda], LH.[LYR], LH.[Hours], LH.[Leave Code], M2.[HRYRAT], M2.[EMPNO], M2.[MANLAP], M2.[MANLAC], M2.[MANLTC], M2.[MSKLAB], M2.[MSKLTC], M2.[MSKLAB], M2.[MSKLTC], M2.[NAMEMI], M2.[NAMEL], M2.[NAMEF] FROM LEAVHST LH INNER JOIN MASTERL2 M2 ON LH.[Employee Number]=M2.EMPNO WHERE M2.[EMPNO] = '".$_POST['employeenumber']."' and LH.[LYR] >= '$last'  and LH.[Lda] >= '01' and LH.[Lmo] >= '07'  ORDER BY LH.[LYR] desc, LH.[Lmo] desc, LH.[Lda] desc";

echo $sql;

// $RESULTDS=mssql_query($sql) or die ('There is an error with your query'); 
//   $RESULT=mssql_fetch_assoc($RESULTDS);

See if it is returning what you want.

Ray


#4 Moron

Moron
  • Members
  • PipPipPip
  • Advanced Member
  • 368 posts

Posted 29 August 2006 - 02:46 PM

Thanks, guys. Before the query (it's MS SQL, by the way), is...

//Define year of current fiscal year

if(($curr_month ==6 && $curr_day >= 1) || $curr_month >6){
$last = $curr_year;
}else{
$last = $curr_year - 1;
}

//Define month to start with

if ($last == $curr_year) { 
$monlast = 07;
}else{
$monlast = 12;
}

//Define day to break on

if ($last >= $curr_year){
$daypoint = 01;
}else{
$daypoint = 31;
}

The query works perfectly, BUT... it won't when January comes around with the month hardcoded as ">= 07" because January, being month 01, won't fit that criteria. I basically need a way to make the query fiscal year-sensitive.

Any help is greatly appreciated!

#5 craygo

craygo
  • Staff Alumni
  • Advanced Member
  • 1,973 posts
  • LocationRhode Island

Posted 29 August 2006 - 03:12 PM

I work with this also.

Does your fiscal year start july 1st??
If your date is august 3, 2006 will it be fiscal year 2007 or 2006??
Where is $curr_year, $curr_month, $curr_day coming from?? Is it just todays date??
Is what you are trying to do is find out if a particular date is in the current fiscal year??

Ray

#6 Moron

Moron
  • Members
  • PipPipPip
  • Advanced Member
  • 368 posts

Posted 29 August 2006 - 04:22 PM

I work with this also.

Does your fiscal year start july 1st??
If your date is august 3, 2006 will it be fiscal year 2007 or 2006??
Where is $curr_year, $curr_month, $curr_day coming from?? Is it just todays date??
Is what you are trying to do is find out if a particular date is in the current fiscal year??

Ray


Yes, our fiscal year starts July 1. We're in fiscal year 2007 right now.

if(($curr_month ==6 && $curr_day >= 1) || $curr_month >6){
$last = $curr_year;
}else{
$last = $curr_year - 1;
}

$curr_month = date('m');
$curr_day = date('d');
$curr_year = date('Y');

On the last question, no, I want to pull records from the previous July 1 and forward. The query is doing this now, but it isn't "fiscal year sensitive." It's saying that if [Lmo] (Leave Month) is >= 07, etc... This hard wiring won't work after we hit January (01).



#7 hitman6003

hitman6003
  • Members
  • PipPipPip
  • Advanced Member
  • 1,807 posts

Posted 29 August 2006 - 04:44 PM

So, just to be sure...you are storing the date for leave in three seperate columns...Lya, Lda, and Lmo?

If so, then you should be able to do something like:

SELECT 
	DISTINCT LH.[Employee Number], 
	LH.[Lmo], 
	LH.[Lda], 
	LH.[LYR], 
	LH.[Hours], 
	LH.[Leave Code], 
	M2.[HRYRAT], 
	M2.[EMPNO], 
	M2.[MANLAP], 
	M2.[MANLAC], 
	M2.[MANLTC], 
	M2.[MSKLAB], 
	M2.[MSKLTC], 
	M2.[MSKLAB], 
	M2.[MSKLTC], 
	M2.[NAMEMI], 
	M2.[NAMEL], 
	M2.[NAMEF] 
FROM LEAVHST LH 
	INNER JOIN MASTERL2 M2 ON LH.[Employee Number] = M2.EMPNO 
WHERE 
	M2.[EMPNO] = '" . $_POST['employeenumber'] . "' AND 
	((LH.[LYR] = '$last' AND	LH.[Lmo] >= '07') OR (LH.[LYR] = '" . $last + 1 . "' AND LH.[Lmo] < '07'))
ORDER BY LH.[LYR] desc, LH.[Lmo] desc, LH.[Lda] desc


#8 Moron

Moron
  • Members
  • PipPipPip
  • Advanced Member
  • 368 posts

Posted 29 August 2006 - 06:54 PM

Hitman6003,

Thanks a ton, bit it keeps giving me this:

Warning: mssql_query() [function.mssql-query]: message: Line 1: Incorrect syntax near '1'. (severity 15) in E:\User\Inetpub\wwwroot\PHPLeaveTime\leaveprocess.php on line 285

Warning: mssql_query() [function.mssql-query]: message: Unclosed quotation mark before the character string ')) ORDER BY LH.[LYR] desc, LH.[Lmo] desc, LH.[Lda] desc'. (severity 15) in E:\User\Inetpub\wwwroot\PHPLeaveTime\leaveprocess.php on line 285

Warning: mssql_query() [function.mssql-query]: Query failed in E:\User\Inetpub\wwwroot\PHPLeaveTime\leaveprocess.php on line 285

Warning: mssql_fetch_assoc(): supplied argument is not a valid MS SQL-result resource in E:\User\Inetpub\wwwroot\PHPLeaveTime\leaveprocess.php on line 286



#9 hitman6003

hitman6003
  • Members
  • PipPipPip
  • Advanced Member
  • 1,807 posts

Posted 29 August 2006 - 07:13 PM

Seems to not escape the quotes correctly to add the 1 to $last.

$query = "SELECT 
	DISTINCT LH.[Employee Number], 
	LH.[Lmo], 
	LH.[Lda], 
	LH.[LYR], 
	LH.[Hours], 
	LH.[Leave Code], 
	M2.[HRYRAT], 
	M2.[EMPNO], 
	M2.[MANLAP], 
	M2.[MANLAC], 
	M2.[MANLTC], 
	M2.[MSKLAB], 
	M2.[MSKLTC], 
	M2.[MSKLAB], 
	M2.[MSKLTC], 
	M2.[NAMEMI], 
	M2.[NAMEL], 
	M2.[NAMEF] 
FROM LEAVHST LH 
	INNER JOIN MASTERL2 M2 ON LH.[Employee Number] = M2.EMPNO 
WHERE 
	M2.[EMPNO] = '" . $_POST['employeenumber'] . "' AND 
	((LH.[LYR] = '$last' AND	LH.[Lmo] >= '07') OR (LH.[LYR] = '" . $last + 1 . "' AND LH.[Lmo] < '07'))
ORDER BY LH.[LYR] desc, LH.[Lmo] desc, LH.[Lda] desc";

$result = mssql_query($query);


#10 Moron

Moron
  • Members
  • PipPipPip
  • Advanced Member
  • 368 posts

Posted 29 August 2006 - 07:53 PM

I also tried replacing just this part:

((LH.[LYR] = '$last' AND	LH.[Lmo] >= '07') OR (LH.[LYR] = '" . $last + 1 . $last + 1 . "' AND LH.[Lmo] < '07'))

No go. It gives four major sql errors.

Thanks for the help, though. My syntax is probably just hosed somewhere.


#11 Moron

Moron
  • Members
  • PipPipPip
  • Advanced Member
  • 368 posts

Posted 29 August 2006 - 08:08 PM

Did you mean to put this twice in a row?

 $last + 1 . $last + 1 .


#12 Moron

Moron
  • Members
  • PipPipPip
  • Advanced Member
  • 368 posts

Posted 29 August 2006 - 08:11 PM

Hey, check it out. This returns the right results:

(LH.[LYR] = '$last' AND LH.[Lmo] >= '07') OR (LH.[LYR] = '$last' + 1  AND LH.[Lmo] < '07') 

But does anyone see a problem with it?






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users