$strSQL="SELECT COUNT(VN) as pretotal =
(SELECT COUNT(VN) as total
FROM DNHOSPITAL_TEST.dbo.HNOPD_MASTER
WHERE VisitDate BETWEEN '".$nowYear."-".$month."-1' AND '".$nowYear."-".$month."-".$dmonth."' AND Cxl='0')
FROM DNHOSPITAL_TEST.dbo.HNOPD_MASTER
WHERE VisitDate BETWEEN '".(($nowYear)-1)."-".$month."-1' AND '".(($nowYear)-1)."-".$month."-".$dmonth."' AND Cxl='0'";
$objExec = odbc_exec($conn,$strSQL) or die ("Error SQL [".$strSQL."]");
Code (PHP)
SELECT COUNT(VN) as pretotal =
(SELECT COUNT(VN) as total
FROM DNHOSPITAL_TEST.dbo.HNOPD_MASTER
WHERE VisitDate BETWEEN '2018-1-1' AND '2018-1-31' AND Cxl='0')
FROM DNHOSPITAL_TEST.dbo.HNOPD_MASTER
WHERE VisitDate BETWEEN '2017-1-1' AND '2017-1-31' AND Cxl='0'
Code (PHP)
พอลอง query ใน SQL SERVER มันฟ้องว่า
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '='.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'FROM'.
SELECT
(SELECT COUNT(VN) FROM DNHOSPITAL_TEST.dbo.HNOPD_MASTER
WHERE VisitDate BETWEEN '2017-1-1' AND '2017-1-31' AND Cxl='0') as pretotal ,
(SELECT COUNT(VN) FROM DNHOSPITAL_TEST.dbo.HNOPD_MASTER
WHERE VisitDate BETWEEN '2018-1-1' AND '2018-1-31' AND Cxl='0') as total
function days_in_month($month, $year)
{
// calculate number of days in a month
return $month == 2 ? ($year % 4 ? 28 : ($year % 100 ? 29 : ($year % 400 ? 28 : 29))) :(($month - 1) % 7 % 2 ? 30 : 31);
}
$nYear = date('Y');
$sYear = 2549-543;
for($yy=$nYear;$yy>=$sYear;$yy--)
{
echo "<tr>";
echo "<td><b>".($yy+543)."</b></td>";
for($mm=1;$mm<=12;$mm++)
{
$dd =cal_days_in_month(CAL_GREGORIAN, $mm, $yy);
//echo "There was".$dd."days Month".$mm."year".$yy."<br>";
$strSQL="SELECT COUNT(VN) as pretotal,
(SELECT COUNT(VN)
FROM DNHOSPITAL_TEST.dbo.HNOPD_MASTER
WHERE VisitDate BETWEEN '".$yy."-".$mm."-1' AND '".$yy."-".$mm."-".$dd."' AND Cxl='0') as total
FROM DNHOSPITAL_TEST.dbo.HNOPD_MASTER
WHERE VisitDate BETWEEN '".(($yy)-1)."-".$mm."-1' AND '".(($yy)-1)."-".$mm."-".$dd."' AND Cxl='0'";
$objExec = odbc_exec($conn,$strSQL) or die ("Error SQL [".$strSQL."]");
while($objResult = odbc_fetch_object($objExec))
{
$pretotal=$objResult->pretotal;
$total=$objResult->total;
//var_dump($pretotal);
//var_dump($total);
$nets= (($total-$pretotal)*100)/$pretotal;
//echo $nets."<br>";
echo "<td span class=border>".(number_format($total, 0))."<br>".(number_format($nets, 2))."</td>";
}
//echo $strSQL;
}
echo "</tr>";
$yy--;
}
Code (PHP)
Warning: odbc_exec(): SQL error: [Microsoft][SQL Server Native Client 11.0][SQL Server]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value., SQL state 22008 in SQLExecDirect in D:\Apache24\htdocs\Bootstrap\ThainakarinStatistics\test\subquery.php on line 34
Error SQL [SELECT COUNT(VN) as pretotal, (SELECT COUNT(VN) FROM DNHOSPITAL_TEST.dbo.HNOPD_MASTER WHERE VisitDate BETWEEN '2016-2-1' AND '2016-2-29' AND Cxl='0') as total FROM DNHOSPITAL_TEST.dbo.HNOPD_MASTER WHERE VisitDate BETWEEN '2015-2-1' AND '2015-2-29' AND Cxl='0']
SELECT
(SELECT COUNT(VN) FROM DNHOSPITAL_TEST.dbo.HNOPD_MASTER
WHERE Year(VisitDate) = 2017 and month(VisitDate)=1 AND Cxl='0') as pretotal ,
(SELECT COUNT(VN) FROM DNHOSPITAL_TEST.dbo.HNOPD_MASTER
WHERE Year(VisitDate) = 2018 and month(VisitDate)=1 AND Cxl='0') as total
Load balance : Server 04
