Jump to content

Archived

This topic is now archived and is closed to further replies.

Moron

My query is a ticking time bomb!

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?

Share this post


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

Share this post


Link to post
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

Share this post


Link to post
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!

Share this post


Link to post
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

Share this post


Link to post
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).

Share this post


Link to post
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]

Share this post


Link to post
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]

Share this post


Link to post
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]

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites
Did you mean to put this twice in a row?

[code]
$last + 1 . $last + 1 .
[/code]

Share this post


Link to post
Share on other sites
Hey, check it out. This returns the right results:

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

But does anyone see a problem with it?

Share this post


Link to post
Share on other sites

×

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.