pass date variable to SQL Server pivot query correctly

kat35601 · September 1, 2020

How do I pass date variable to SQL Server pivot query correctly it works in the where clause but not the Pivot IN statement I think it's because the date there should be in Brackets. I also need help once that is working to display the qty data for each date column. Thanks

<html>
<head>
</head>
<body>
<style>


table {
	border: 1px solid #B0CBEF;
	border-width: 1px 0px 0px 1px;
	font-size: 14pt;
	font-family: Calibri;
	font-weight: 100;
	border-spacing: 0px;
	border-collapse: collapse;
}

 TH {
	background-image: url(excel-2007-header-bg.gif);
	background-repeat: repeat-x;
	font-weight: normal;
	font-size: 17px;
	border: 1px solid #9EB6CE;
	border-width: 0px 1px 1px 0px;
	height: 17px;
}

 TD {
	border: 0px;
	padding: 0px 4px 0px 2px;
	border: 1px solid #D0D7E5;
	border-width: 0px 1px 1px 0px;
}

 TD B {
	border: 0px;
	background-color: white;
	font-weight: bold;
}

 TD.heading {
	background-color: #E4ECF7;
	text-align: center;
	border: 1px solid #9EB6CE;
	border-width: 0px 1px 1px 0px;
}



</style>
<?php
#$date=$_POST['date'];

$date1=date('m/d/Y');
$date2= Date('m/d/Y', strtotime('+2 days'));
$date3=Date('m/d/Y', strtotime('+3 days'));
$date4= Date('m/d/Y', strtotime('+4 days'));
$date5= Date('m/d/Y', strtotime('+5 days'));
$date6= Date('m/d/Y', strtotime('+6 days'));
$date7= Date('m/d/Y', strtotime('+7 days'));
$date8= Date('m/d/Y', strtotime('+8 days'));
$date9= Date('m/d/Y', strtotime('+9 days'));
$date10= Date('m/d/Y', strtotime('+10 days'));
$date11= Date('m/d/Y', strtotime('+11 days'));
$date12= Date('m/d/Y', strtotime('+12 days'));
$date13= Date('m/d/Y', strtotime('+13 days'));
$date14= Date('m/d/Y', strtotime('+14 days'));

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

$sql="SELECT * FROM (
    select  
    omlPartID
    , ISNULL(omlOrderQuantity,0) AS qty
    , CONVERT(VARCHAR, ompRequestedShipDate , 101) AS shipdate
   
    FROM   M1_KF.dbo.salesorders
    LEFT OUTER JOIN M1_KF.dbo.salesorderlines
    on omlSalesOrderID=ompSalesOrderID
    LEFT OUTER JOIN  M1_KF.dbo.PartRevisions
    on imrPartID=omlPartID
    LEFT OUTER JOIN  M1_KF.dbo.Parts
    on impPartID=omlPartID
   
    WHERE  ompclosed !=- 1 and UOMPTRUCKNUMBER !=''
    AND ompshippingmethodid != 'DC' and impPartClassID='FGM'
    and ompRequestedShipDate>=$date1 and ompRequestedShipDate<=$date14
    ) as test
   
    pivot(
     sum(qty)
     for shipdate IN ($date1,$date2,$date3,$date4,$date5,$date6,$date7,$date8,$date9,$date10,$date11,$date12,$date13,$date14) ) as pvt
      ";
        echo "<table><tr>";
        echo "<th>ITEM</th>";
        echo "<th>".$date1."</th>";
        echo "<th>".$date2."</th>";
        echo "<th>".$date3."</th>";
        echo "<th>".$date4."</th>";
        echo "<th>".$date5."</th>";
        echo "<th>".$date6."</th>";
        echo "<th>".$date7."</th>";
        echo "<th>".$date8."</th>";
        echo "<th>".$date9."</th>";
        echo "<th>".$date10."</th>";
        echo "<th>".$date11."</th>";
        echo "<th>".$date12."</th>";
        echo "<th>".$date13."</th>";
        echo "<th>".$date14."</th>";
       

$result =odbc_exec($connect,$sql);
if(!$result){
exit("Error in SQL");
}
 while ($row = odbc_fetch_array($result))
{
    echo "<tr><td>" .  $row['omlPartID']."</td>";

    # how to output the qty data for each date column
    echo "<td>" .$row['qty']."</td></tr>"; 
 }

odbc_close($connect);

?>
</body>
</html>

Barand · September 1, 2020

One problem you have is that you cannot correctly compare date values in m/d/Y format. To compare reliably they need to be Y-m-d.

08/01/2020 > 02/01/2021

whereas

2021-01-02 > 2020-01-08 - correct

Sign In

pass date variable to SQL Server pivot query correctly

Recommended Posts

kat35601

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information