Jump to content
kat35601

pass date variable to SQL Server pivot query correctly

Recommended Posts

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>

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.