How/What is the correct way to create a function for MS SQL Queries.

[kat35601]

I have three Queries that show Production totals for Day, Week and Month. How would you do this.

 

Day code looks like this:

<?php
$grandTotal = 0;
$connect =odbc_connect("removed");
if(!$connect) {
exit("Connection Failed: " . $connect);
}
 
$sql="	SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, tdate)) AS DATE
	,ISNULL(trans, 'NON') AS trans
	,ISNULL(Transactions.item, Snumbers.item) AS item
	,count(serial) AS qty
	,tuser
	,sum(M1_KF.dbo.PartUnitSalePrices.imhUnitSalePrice) as TotalPrice
	FROM Orbedata.dbo.SNumbers
  	LEFT OUTER JOIN OrbeData.dbo.Transactions ON snum = serial 
	INNER JOIN M1_KF.dbo.PartUnitSalePrices ON Orbedata.dbo.transactions.item = M1_KF.dbo.PartUnitSalePrices.imhPartID
	WHERE CONVERT(DATE, tdate) = CONVERT(DATE, Getdate())
		AND trans = 'fpr'
	GROUP BY DATEADD(dd, 0, DATEDIFF(dd, 0, tdate))
	,ISNULL(trans, 'NON')
	,ISNULL(Transactions.item, Snumbers.item)
	,tuser
	,Orbedata.dbo.transactions.qty
	order by tuser,item";

 
$result =odbc_exec($connect,$sql);
if(!$result){
exit("Error in SQL");
}
 
 
while (odbc_fetch_row($result)) {
 
 $Price=odbc_result($result,"TotalPrice");
 
$num = number_format($Price, 2);
 $grandTotal += $Price;
 $num3 = number_format( $grandTotal, 2);
 
}
odbc_close($connect);
echo "$num3";
?>

Week only the where clause changes like this:

WHERE  tdate>=DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) and   tdate<=DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6)

Month only the where clause changes like this:

	WHERE  tdate>=DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) and   tdate<=DATEADD (dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0))

[ginerjm]

You are creating a PHP function, correct?

 

Code up the function like this:

function DoQuery($where)
{
// the $where parameter is a complete where= clause
$q = (your query statement here with the where clause being replaced by $where)
(the rest of your process to produce the result
return $result;
}

 

// build the where clause

if ($runDay)

$where = (your where clause for Day);

elseif($runWeek)

$where = (your where clause for Week);

elseif($runMonth)

$where = (your where clause for Month);

// run the query

$answer = DoQuery($where);

(use the value of $answer where you want it to show up)

[Psycho]

For records in a particular month, I would use something like

 

YEAR(datefield) = targetYear AND MONTH(datefield) = targetMonth

 

For records within a particular week, you can do something similar if your weeks are aligned with ISO_WEEKS

[Barand]

Translating the above to sqlsrv dialect

WHERE DATEPART(yyyy, datefield) = targetyear AND DATEPART(mm, datefield) = targetmonth

[kat35601]

With regards to the date " year, month, week, day" thank you for but mine seem to work fine and I don't have to put in a target.

 

I really am trying to figure out how to create a function that will give me the totals for day week month. 

 

Thanks

[ginerjm]

And post #2 didn't help you see it?

[Psycho]

With regards to the date " year, month, week, day" thank you for but mine seem to work fine and I don't have to put in a target.

 

I really am trying to figure out how to create a function that will give me the totals for day week month. 

 

Yes, you do have a target. You are creating it with a more complicated logic using datediff() and getdate(). I just gave a high level example, if you wanted records for the current month it would look like

WHERE DATEPART(yyyy, tdate) = DATEPART(yyyy, getdate()) AND DATEPART(mm, tdate) = DATEPART(mm, getdate())

But, per your original request you asked

 

 

I have three Queries that show Production totals for Day, Week and Month. How would you do this

 

So, I provided how I would do it. You never asked about getting totals for the periods. If you want totals for particular periods you would use GROUP BY using similar logic to what I provided. As stated before, the ISO_WEEK will work if your weeks align with the start/end of ISO_WEEK. Else, you are going to have to create some logic to appropriately group the weeks as you want them.

Edited August 9, 2016 by Psycho

[kat35601]

#2 did help me kinda but i was hoping I could open a connection to the sql server run all three queries and return the totals for each query and close the connection.

and I not sure if thats the best way. Right now I have the pages one for day week and month and I include those on my page that show the totals for each and it works fine but I am trying to learn the correct/better way of doing this so I don't have so many pages and I do thank all of you for the instructions.

[ginerjm]

If I am thinking correctly you said you had multiple servers involved. For each connection type (ie, server) you should create a module and function that you include in order to call that function to open the connection and return the handle to it. Then pass that handle to the query function that you create from my post. You don't really have to worry about closing it (but you can) since when the script dies it will self-close.

 

If you only have the one db server and one set of credentials, then you'll only need one connection function. The beauty of having a connection module/function is that you can then use this in any of your future scripts and should the need arise to alter the creds, you just have one place to modify. Be sure to store this module OUTSIDE of the root folder tree. (not in a sub-folder of it!).

[kat35601]

Function

 

how do you make a function out of this? I can't find any examples 

<?php
$connect =odbc_connect("removed");
if(!$connect) {
	exit("Connection Failed: " . $connect);
}

/**********************************************************/

$result =odbc_exec($connect,$sql);
if(!$result){
exit("Error in SQL");
}
include ("redtag_results.php");

Edited August 9, 2016 by kat35601

[ginerjm]

Write the lines of code needed to make a connection. Then when it works properly wrap it in a function header and a return of the resulting "handle" var. Store that whole thing in a file above or next to the html root folder and you have it. Then in a script that needs a connection you include that file and call that function.

 

Personally, I have added a parm to the function to allow me to select the desired database name so you can do the connect and select all at one time. I also added a function to the module to retrieve the last sql error message (I use PDO) so that if my calling script wants to display the message during development I can call that function.