Jump to content

My query is a ticking time bomb!


Moron

Recommended Posts

[quote]
$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 [b]LH.[LYR] >= '$last'[/b]  and [b]LH.[Lda] >= '01'[/b] and [b]LH.[Lmo] >= '07'[/b]  ORDER BY LH.[LYR] desc, LH.[Lmo] desc, LH.[Lda] desc");
$RESULT=mssql_fetch_assoc($RESULTDS);[/quote]

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 [b]$last[/b] 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:

[code]
if ($last == $curr_year) {
$monlast = 07;
}else{
$monlast = 12;
}
[/code]

I can echo it and it works fine, BUT, if I put [b]LH.[Lmo] >= '$monlast'[/b] 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?

Link to comment
Share on other sites

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.

[code]$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);[/code]

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

[code]$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);[/code]

See if it is returning what you want.

Ray
Link to comment
Share on other sites

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

[code]
//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;
}
[/code]

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!
Link to comment
Share on other sites

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
Link to comment
Share on other sites

[quote author=craygo link=topic=106085.msg424051#msg424051 date=1156864364]
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
[/quote]

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

[code]
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');
[/code]

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).

Link to comment
Share on other sites

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:

[code]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[/code]
Link to comment
Share on other sites

Hitman6003,

Thanks a ton, bit it keeps giving me this:

[quote][b]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
[/b][/quote]
Link to comment
Share on other sites

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

[code]$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);[/code]
Link to comment
Share on other sites

I also tried replacing just this part:

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

No go. It gives four major sql errors.

Thanks for the help, though. My syntax is probably just hosed somewhere.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.