Jump to content

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



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;


$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 (
    , 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
     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);
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>"; 




Link to comment
Share on other sites

This thread is more than a year old. Are you sure you have something important to add to it?

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

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.