Jump to content

pass date variable to SQL Server pivot query correctly


kat35601
 Share

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>

 

Link to comment
Share on other sites

This thread is more than a year old.

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.

 Share

×
×
  • 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.