SQL Help - Tough One!?!

**jtanner** · 01-09-2007, 08:50 AM

Ok, im looking at some problems with coldfusion / SQL statements. The code was NOT written by me but ive been asked to look at the problem. (Code is at end of post)

There are two table in question - tbl_delivery_report & tbl_client_clip.

The tables cant be linked eg there is no feasable join. the code uses DISTINCT but it means that we are unable to run the delivery reports unless a client clip has been uploaded. I understand the point of the distinct because the drop down lost doesnt want to show (example) 11/03 23 times. Id like to combine a field (Month_NR - contains date in a mm/yy format) from BOTH tables, then use the distinct statement. that way, it would read in the dates from the delivery table as well, the major problem at the moment is the drop down doesnt show a delivery date option unless there was a clip (hope im making sense)

I dont know why the code was written this way, maybe someone could shed some logical reason lol

Code is as folows:

<cf_mod_login>
<cfparam name="form.Report_Type" default="delivery">

<cfparam name="form.Month_NR" default="#dateformat(dateadd("m", 0, now()), "mm/yy")#">

<cfif isdefined("url.Month_NR")>
<cfset form.Month_NR = url.Month_NR>
</cfif>

<cfquery name="rs_client_name" datasource="#request.dsn#" username="#request.uid#" password="#request.pwd#">
SELECT Client_Name FROM Tbl_Client WHERE Client_ID = #session.sv_client#
</cfquery>

<cfquery name="rs_report" datasource="#request.dsn#" username="#request.uid#" password="#request.pwd#">
SELECT * FROM Tbl_Delivery_Report
WHERE Client_Name LIKE '#rs_client_name.Client_Name#%' AND Month_NR LIKE '#form.Month_NR#%'
</cfquery>


<cfquery name="rs_filter" datasource="#request.dsn#" username="#request.uid#" password="#request.pwd#">
SELECT distinct Month_NR
FROM Tbl_Client_Clip
WHERE Client_Name LIKE '#rs_client_name.Client_Name#%'
</cfquery>


<cfquery name="rs_min" datasource="#request.dsn#" username="#request.uid#" password="#request.pwd#">
SELECT MIN(Client_Clip_ID) AS varMin FROM Tbl_Client_Clip WHERE Client_Name LIKE
'#rs_client_name.Client_Name#'<cfif isdefined("form.month_nr") AND #form.month_nr# gt "0"> AND Month_NR = '#form.Month_NR#'</cfif>
</cfquery>

<cfif form.Report_Type EQ "placement">
<cflocation url="report.cfm?Client_Clip_ID=#rs_min.varMin#&Mon th_NR=#form.Month_NR#&new=1" addtoken="no">
</cfif>

<cfif form.Report_Type EQ "valuation">
<cflocation url="report_valuation.cfm?Month_NR=#form.Month_NR# " addtoken="no">
</cfif>
<cfquery name="rs_user" datasource="#request.dsn#" username="#request.uid#" password="#request.pwd#">
SELECT #request.uid#.Tbl_Client_Contact.Client_Contact_ID , #request.uid#.Tbl_Client_Contact.First_Name, #request.uid#.Tbl_Client_Contact.Last_Name, #request.uid#.Tbl_Client.Client_Name, #request.uid#.Tbl_Client.Client_Logo
FROM #request.uid#.Tbl_Client_Contact INNER JOIN #request.uid#.Tbl_Client ON #request.uid#.Tbl_Client_Contact.Client_ID = #request.uid#.Tbl_Client.Client_ID
WHERE #request.uid#.Tbl_Client_Contact.Client_Contact_ID = #session.sv_login#
</cfquery>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Product Placement - 1st Place - Premium Product Placement</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="stylesheets/client_main.css" rel="stylesheet" type="text/css">
<script language="javascript" src="scripts/pop.js"></script>
</head>

<body>



<div id="global">
<div id="container">





<div class="pageheader">
<cfinclude template="navbar_clients.cfm">
<div class="breadtext">
delivery report
</div>
</div>



<div class="maincontentarea">
<cfoutput>
<div class="clipuserimage2">
<img src="images/company_logos/#rs_user.Client_Logo#" alt="#rs_user.Client_Name#" style="padding-bottom: 20px;"><br />
</div>
</cfoutput>
<a name="top"></a>
<div class="reportbox">
<table width="669" border="0" cellpadding="3" cellspacing="1">
<form name="frm_filter" action="report_delivery.cfm" method="post">
<tr class="textbold">
<td colspan="12">
<table width="100%" border="0" cellspacing="0" cellpadding="3">
<tr class="bodytextsmallbold">
<td>Filter by Month (mm/yy)</td>
<td>
<select name="Month_NR" class="bodytextsmall">
<option value="0">Select All...</option>
<cfoutput query="rs_filter">
<option <cfif Month_NR EQ form.Month_NR>selected</cfif>>#Month_NR#</option>
</cfoutput> </select>
</td>
<td>
Report Type
</td>
<td>
<select name="Report_Type" class="bodytextsmall">
<option value="valuation" <cfif form.Report_Type EQ "valuation">selected</cfif>>Valuation Report</option>
<option value="placement" <cfif form.Report_Type EQ "placement">selected</cfif>>Placement Report</option>
<option value="delivery" <cfif form.Report_Type EQ "delivery">selected</cfif>>Delivery Report</option>
</select>
<input name="Submit" type="submit" class="text" value="Filter">
</td>
</tr>
</table>
</td>
</tr>
<tr class="textbold">
<td height="5" colspan="12">
<img src="nav/shim.gif" width="10" height="5"><br />
</td>
</tr>
</form>
<tr>
<td align="center" bgcolor="#C9CDD3" class="bodytextwhitebold" style="padding-top: 10px; padding-bottom: 10px;">
Title
</td>
<td align="center" bgcolor="#C9CDD3" class="bodytextwhitebold">
Entry No
</td>
<td align="center" bgcolor="#C9CDD3" class="bodytextwhitebold">
Channel
</td>
<td align="center" bgcolor="#C9CDD3" class="bodytextwhitebold">
Programme Type
</td>
<td align="center" bgcolor="#C9CDD3" class="bodytextwhitebold">
Delivery
</td>
<td align="center" bgcolor="#C9CDD3" class="bodytextwhitebold">
Transmission Date
</td>
<td align="center" bgcolor="#C9CDD3" class="bodytextwhitebold">
Product
</td>
</tr>
<cfoutput query="rs_report">
<tr class="bodytextbold">
<td align="center" style="padding-top: 20px;">
#Programme#<br />
</td>
<td align="center" style="padding-top: 20px;">
#Delivery_No#<br />
</td>
<td align="center" style="padding-top: 20px;">
#Channel#<br />
</td>
<td align="center" style="padding-top: 20px;">
#Programme_Type#<br />
</td>
<td align="center" style="padding-top: 20px;">
#Delivery_Date#<br />
</td>
<td align="center" style="padding-top: 20px;">
#Bullshit#<br />
</td>
<td align="center" style="padding-top: 20px;">
#Branded_Prop#<br />
</td>
</tr>
<tr>
<td height="24" colspan="7" class="bodytext">
<p>#paragraphformat(Description_1)#</p>
<p>#paragraphformat(Description_2)#</p>
</td>
</tr>
<tr>
<td align="right" colspan="7" class="bodytext" style="margin-bottom: 10px; padding-bottom: 10px; border-bottom: 1px dashed ##C9CDD3;">
<a href="##top">back to top</a><br />
</td>
</tr>
</cfoutput>
</table>
</div>
</div>



<cfinclude template="footerbar.cfm">





</div>
</div>



<script src="http://www.google-analytics.com/urchin.js" type="text/javascript">
</script>
<script type="text/javascript">
_uacct = "UA-377381-1";
urchinTracker();
</script>

</body>
</html>

Thread: SQL Help - Tough One!?!

Thread Tools

Display

SQL Help - Tough One!?!

Posting Permissions