• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

Query Help.........

Hey I'm working on this query... What I need is it to only display the info related to the EmployeesID
URL.M is so the info is also displayed by month. As of now It's displaying all the values in the field multiple times for each employee...

<CFQUERY name="listhours"
     datasource="#Request.MainDSN#">
SELECT Timesheets.EmployeesID, Timesheets.HSPENT, Timesheets.CLientID, Timesheets.ddate, Clients.CompanyN, Clients.ClientID, Employees.firstname, Employees.lastname, Employees.EmployeesID FROM Timesheets, Clients, Employees WHERE Timesheets.ClientID = Clients.ClientID and Timesheets.EmployeesID = #URL.EmployeesID#
<CFIF isdefined("url.m")>
  and month(ddate) = #url.m#
</CFIF>
</CFQUERY>


<TD WIDTH="838" COLSPAN="8" ALIGN="RIGHT" CLASS="error" ><CFOUTPUT><A HREF="EmployeeTimeSheet2.cfm?EmployeesID=#URL.EmployeesID#&m=1"></CFOUTPUT>
                  <CFIF URL.M is 1>
                    <SPAN CLASS="linkcolor">January</SPAN>
                    <CFELSE>
                    January
                  </CFIF>
                  </A> | <CFOUTPUT><A HREF="EmployeeTimeSheet2.cfm?EmployeesID=#URL.EmployeesID#&m=2"></CFOUTPUT>
                  <CFIF URL.M is 2>
                    <SPAN CLASS="linkcolor">Feburary</SPAN>
                    <CFELSE>
                    Feburary
                  </CFIF>
                  </A> | <CFOUTPUT><A HREF="EmployeeTimeSheet2.cfm?EmployeesID=#URL.EmployeesID#&m=3"></CFOUTPUT>
                  <CFIF URL.M is 3>
                    <SPAN CLASS="linkcolor">March</SPAN>
                    <CFELSE>
                    March
                  </CFIF>
                  </A> | <CFOUTPUT><A HREF="EmployeeTimeSheet2.cfm?EmployeesID=#URL.EmployeesID#&m=4"></CFOUTPUT>
                  <CFIF URL.M is 4>
                    <SPAN CLASS="linkcolor">April</SPAN>
                    <CFELSE>
                    April
                  </CFIF>
                  </A> | <CFOUTPUT><A HREF="EmployeeTimeSheet2.cfm?EmployeesID=#URL.EmployeesID#&m=5"></CFOUTPUT>
                  <CFIF URL.M is 5>
                    <SPAN CLASS="linkcolor">May</SPAN>
                    <CFELSE>
                    May
                  </CFIF>
                  </A> | <CFOUTPUT><A HREF="EmployeeTimeSheet2.cfm?EmployeesID=#URL.EmployeesID#&m=6"></CFOUTPUT>
                  <CFIF URL.M is 6>
                    <SPAN CLASS="linkcolor">June</SPAN>
                    <CFELSE>
                    June
                  </CFIF>
                  </A> | <CFOUTPUT><A HREF="EmployeeTimeSheet2.cfm?EmployeesID=#URL.EmployeesID#&m=7"></CFOUTPUT>
                  <CFIF URL.M is 7>
                    <SPAN CLASS="linkcolor">July</SPAN>
                    <CFELSE>
                    July
                  </CFIF>
                  </A> | <CFOUTPUT><A HREF="EmployeeTimeSheet2.cfm?EmployeesID=#URL.EmployeesID#&m=8"></CFOUTPUT>
                  <CFIF URL.M is 8>
                    <SPAN CLASS="linkcolor">August</SPAN>
                    <CFELSE>
                    August
                  </CFIF>
                  </A> | <CFOUTPUT><A HREF="EmployeeTimeSheet2.cfm?EmployeesID=#URL.EmployeesID#&m=9"></CFOUTPUT>
                  <CFIF URL.M is 9>
                    <SPAN CLASS="linkcolor">September</SPAN>
                    <CFELSE>
                    September
                  </CFIF>
                  </A> | <CFOUTPUT><A HREF="EmployeeTimeSheet2.cfm?EmployeesID=#URL.EmployeesID#&m=10"></CFOUTPUT>
                  <CFIF URL.M is 10>
                    <SPAN CLASS="linkcolor">October</SPAN>
                    <CFELSE>
                    October
                  </CFIF>
                  </A> | <CFOUTPUT><A HREF="EmployeeTimeSheet2.cfm?EmployeesID=#URL.EmployeesID#&m=11"></CFOUTPUT>
                  <CFIF URL.M is 11>
                    <SPAN CLASS="linkcolor">November</SPAN>
                    <CFELSE>
                    November
                  </CFIF>
                  </A> | <CFOUTPUT><A HREF="EmployeeTimeSheet2.cfm?EmployeesID=#URL.EmployeesID#&m=12"></CFOUTPUT>
                  <CFIF URL.M is 12>
                    <SPAN CLASS="linkcolor">December</SPAN>
                    <CFELSE>
                    December
                  </CFIF>
                  </A></TD>
0
rurth24
Asked:
rurth24
  • 16
  • 13
1 Solution
 
SidFishesCommented:
umm..i don't see a reference to your query in your ouput...

try something like...

<cfoutput query = "listhours">
<TD WIDTH="838" COLSPAN="8" ALIGN="RIGHT" CLASS="error" >

<A HREF="EmployeeTimeSheet2.cfm?EmployeesID=#URL.EmployeesID#&m=1">

                  <CFIF URL.M is 1>
                    <SPAN CLASS="linkcolor">January</SPAN>
                    <CFELSE>
                    January
                  </CFIF>
                  </A> | <A HREF="EmployeeTimeSheet2.cfm?EmployeesID=#listhours.EmployeesID#&m=2">
                  <CFIF URL.M is 2>
                    <SPAN CLASS="linkcolor">Feburary</SPAN>
                    <CFELSE>
                    Feburary
                  </CFIF>
                  </A> | <A HREF="EmployeeTimeSheet2.cfm?EmployeesID=#listhours.EmployeesID#&m=3">
                  <CFIF URL.M is 3>
                    <SPAN CLASS="linkcolor">March</SPAN>
                    <CFELSE>
                    March
                  </CFIF>
                  </A> | <A HREF="EmployeeTimeSheet2.cfm?EmployeesID=#listhours.EmployeesID#&m=4">
                  <CFIF URL.M is 4>
                    <SPAN CLASS="linkcolor">April</SPAN>
                    <CFELSE>
                    April
                  </CFIF>
                  </A> | <A HREF="EmployeeTimeSheet2.cfm?EmployeesID=#listhours.EmployeesID#&m=5">
                  <CFIF URL.M is 5>
                    <SPAN CLASS="linkcolor">May</SPAN>
                    <CFELSE>
                    May
                  </CFIF>
                  </A> | <A HREF="EmployeeTimeSheet2.cfm?EmployeesID=#listhours.EmployeesID#&m=6">
                  <CFIF URL.M is 6>
                    <SPAN CLASS="linkcolor">June</SPAN>
                    <CFELSE>
                    June
                  </CFIF>
                  </A> | <A HREF="EmployeeTimeSheet2.cfm?EmployeesID=#listhours.EmployeesID#&m=7">
                  <CFIF URL.M is 7>
                    <SPAN CLASS="linkcolor">July</SPAN>
                    <CFELSE>
                    July
                  </CFIF>
                  </A> | <A HREF="EmployeeTimeSheet2.cfm?EmployeesID=#listhours.EmployeesID#&m=8">
                  <CFIF URL.M is 8>
                    <SPAN CLASS="linkcolor">August</SPAN>
                    <CFELSE>
                    August
                  </CFIF>
                  </A> | <A HREF="EmployeeTimeSheet2.cfm?EmployeesID=#listhours.EmployeesID#&m=9">
                  <CFIF URL.M is 9>
                    <SPAN CLASS="linkcolor">September</SPAN>
                    <CFELSE>
                    September
                  </CFIF>
                  </A> | <A HREF="EmployeeTimeSheet2.cfm?EmployeesID=#listhours.EmployeesID#&m=10">
                  <CFIF URL.M is 10>
                    <SPAN CLASS="linkcolor">October</SPAN>
                    <CFELSE>
                    October
                  </CFIF>
                  </A> | <A HREF="EmployeeTimeSheet2.cfm?EmployeesID=#listhours.EmployeesID#&m=11">
                  <CFIF URL.M is 11>
                    <SPAN CLASS="linkcolor">November</SPAN>
                    <CFELSE>
                    November
                  </CFIF>
                  </A> | <A HREF="EmployeeTimeSheet2.cfm?EmployeesID=#listhours.EmployeesID#&m=12">
                  <CFIF URL.M is 12>
                    <SPAN CLASS="linkcolor">December</SPAN>
                    <CFELSE>
                    December
                  </CFIF>
                  </A></TD>
</cfouput>

0
 
trailblazzyr55Commented:
this is the reference to the query...

<cfoutput query = "listhours">

 isn't it?

You can do...

<cfoutput query="listhours">
#var1#,#var2#,#var3#,#var4#...
</cfoutput>

or

<cfoutput query="listhours.">
#listhours.var1#,#listhours.var2#,#listhours.var3#,#listhours.var4#...
</cfoutput>

but this is repetitive...

<cfoutput query="listhours">
#listhours.var1#,#listhours.var2#,#listhours.var3#,#listhours.var4#...
</cfoutput>
0
 
trailblazzyr55Commented:
oops meant...

You can do...

<cfoutput query="listhours">
#var1#,#var2#,#var3#,#var4#...
</cfoutput>

or

<cfoutput>    <--- NOTICE THE CHANGE
#listhours.var1#,#listhours.var2#,#listhours.var3#,#listhours.var4#...
</cfoutput>

but this is repetitive...

<cfoutput query="listhours">
#listhours.var1#,#listhours.var2#,#listhours.var3#,#listhours.var4#...
</cfoutput>
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
rurth24Author Commented:
I actually figured it out on my own.. after I posted it...
I changed my query around to this.

<CFQUERY name="listhours"
     datasource="#Request.MainDSN#">
SELECT * FROM (Timesheets LEFT JOIN Clients ON Clients.ClientID = Timesheets.ClientID) LEFT JOIN Employees ON Employees.EmployeesID = Timesheets.EmployeesID WHERE Timesheets.EmployeesID =
<CFQUERYPARAM cfsqltype="CF_SQL_INTEGER" value="#URL.EmployeesId#">
<CFIF isdefined("url.m")>
  and month(ddate) = #url.m#
</CFIF>
</CFQUERY>
0
 
trailblazzyr55Commented:
you may want to switch all those if statements around this could be a lot easier for you??

<TD WIDTH="838" COLSPAN="8" ALIGN="RIGHT" CLASS="error" >
<cfset months = "January,February,March,April,May,June,July,August,September,October,November,December">
<CFOUTPUT><A HREF="EmployeeTimeSheet2.cfm?EmployeesID=#URL.EmployeesID#&m=1"></CFOUTPUT>
<cfswitch expression="#URL.M#">
      <cfloop From = "1" To = "#ListLen(months)#" index = "i">
            <cfcase value="#i#">
                  <cfoutput>
                     <CFIF URL.M is #i#>
                                    <SPAN CLASS="linkcolor">#ListGetAt(months, i)#</SPAN>
                              <cfelse>
                                    #ListGetAt(months, i)#
                        </cfif>
                        </A> | <A HREF="EmployeeTimeSheet2.cfm?EmployeesID=#URL.EmployeesID#&m=#i#">
                  </cfoutput>
            </cfcase>
      </cfloop>
</cfswitch>
</TD>
0
 
trailblazzyr55Commented:
just a loop thorugh a case statement, faster and shorter than listing every month out in an <cfif> statement

regards,
~trail
0
 
trailblazzyr55Commented:


made a mistake, you'd need to move the output statements like this

<cfoutput>
<cfswitch expression="#URL.M#">
     <cfloop From = "1" To = "#ListLen(months)#" index = "i">
          <cfcase value="#i#">
                  <CFIF URL.M is #i#>
                              <SPAN CLASS="linkcolor">#ListGetAt(months, i)#</SPAN>
                         <cfelse>
                              #ListGetAt(months, i)#
                    </cfif>
                    </A> | <A HREF="EmployeeTimeSheet2.cfm?EmployeesID=#URL.EmployeesID#&m=#i#">
          </cfcase>
     </cfloop>
</cfswitch>
</cfoutput>
0
 
rurth24Author Commented:
Trailblazzyr55,

I tried you comment
and I got an error

Only <CFCASE> or <CFDEFAULTCASE> tags may be nested within a <CFSWITCH> tag.  
 
 
The Error Occurred in C:\\EmployeeTimeSheet2.cfm: line 111
 
109 :                   <CFOUTPUT><A HREF="EmployeeTimeSheet2.cfm?EmployeesID=#URL.EmployeesID#&m=1"></CFOUTPUT>
110 :                   <CFSWITCH expression="#URL.M#">
111 :                     <CFLOOP From = "1" To = "#ListLen(months)#" index = "i">
112 :                       <CFCASE value="#i#">
113 :                       <CFOUTPUT>

 
0
 
trailblazzyr55Commented:
Actually I don't really think you need the case statements, was going to do it in cfscript, but I think this'll work for you, try this

<cfset months = "January,February,March,April,May,June,July,August,September,October,November,December">
<cfoutput>
   <A HREF="EmployeeTimeSheet2.cfm?EmployeesID=#URL.EmployeesID#&m=1">
     <cfloop From = "1" To = "#ListLen(months)#" index = "i">
            <CFIF URL.M is #i#>
                  <SPAN CLASS="linkcolor">#ListGetAt(months, i)#</SPAN>
             <cfelse>
                  #ListGetAt(months, i)#
             </cfif>
                  </A> | <A HREF="EmployeeTimeSheet2.cfm?EmployeesID=#URL.EmployeesID#&m=#i#">
     </cfloop>
</cfoutput>
0
 
trailblazzyr55Commented:
I was thinking why use so many if's, just use case's, then I was like hey throw them in a loop shorter it up, so I did both... oops :o)
Don't even need the case's then

regards,
~trail
0
 
rurth24Author Commented:
Okay It seems to be working but the Months are alittle off..

When I click on Feburary, Janurary Highlights
When I click on March, February Highlights.

It seems to be highlighting the month to the left...
0
 
trailblazzyr55Commented:
Ok here you go, have to move the URL up and take out that extra one you have, it was throwing off the count, you'll have to add your url properties back in where you need them like the emplyeesid, for testing they were messing things up, you should find this works :o)

<cfset months = "January,February,March,April,May,June,July,August,September,October,November,December">
<cfoutput>
     <cfloop From = "1" To = "#ListLen(months)#" index = "i">
          <A HREF="EmployeeTimeSheet2.cfm?m=#i#">
                  <CFIF URL.M is #i#>
                  <SPAN CLASS="linkcolor">#ListGetAt(months, i)#</SPAN>
             <cfelse>
                  #ListGetAt(months, i)#
             </cfif>
                  </A>
                     <cfif i LT '#ListLen(months)#'>|<cfelse></cfif>         
     </cfloop>
</cfoutput>
0
 
rurth24Author Commented:
Thanks Bro.. Worked great.

ONE TO GROW ON.... :)
0
 
trailblazzyr55Commented:
Hehe, glad that worked for ya! just had to work out the kinks! :o)

Thanks for the points!
Best Regards,
~trail
0
 
rurth24Author Commented:
If you have some time can you kind of explain what's going on?

I'm alittle blurry on the logic?
0
 
trailblazzyr55Commented:
Sure, not a problem...
I'll coment through each section so you see what's going on each step...

<!---Here we need to list the months to loop through, this way you don't have to assign each of them an "if" statement --->

<cfset months = "January,February,March,April,May,June,July,August,September,October,November,December">

<!---Next we create the loop, starting at 1 to the total number of items in your list which is #ListLen(months)#. ListLen is a function that will count the number of items in a list, so in this case ListLen = 12 cause there's twelve months in the list (months). Index is the the count when the loop runs so if there's 12 items the loop will run until it hits number 12. this value can be shown by "i". I is the counter basically. So if you want to reference the count anywhere along the loop, you use "i". --->

<cfoutput>
     <cfloop From = "1" To = "#ListLen(months)#" index = "i">

<!--- next you want to creat your link at the beginning of the loop so we don't run into the problem with how the months links were off. this will use the counter to assign "m" the value of the count. So like below you see m=#i# this means if it loops twice or is on month february m=2... Then we check to see what month you have by saying "if url.m IS what the count is or #i# then, Apply the span, or else don't apply the span. You don't actually need the second part, I'm not sure why you had the if statement deciding to add a span or not, but I left it in there. Anyway, then you see the function #ListGetAt(months, i)# which uses the ListGetAT() function to get the name of the month depending on the count of the loop.

here's how it works...

let say you have a list which is dogs, cats, birds, pigs, goats and this list is called Animals
So.. <cfset animals = "dogs, cats, horses, pigs, goats">

ListLen(animals) = "5"
Now you want one of these items to use right? so you get it by saying...

ListGetAt(animals, 4) = "pigs" which means Get in this list "animals" item number 4 which would be "pigs"

So below you can see I used "i" the index of the loop to retrieve the months based on where in the loop it was. --->


          <A HREF="EmployeeTimeSheet2.cfm?m=#i#">
               <CFIF URL.M is #i#>
                  <SPAN CLASS="linkcolor">#ListGetAt(months, i)#</SPAN>
             <cfelse>
                  #ListGetAt(months, i)#
             </cfif>

<!--- then you have the last statement which will add a "|" pipe after each record except for the last one. Again I use the ListLen(months) to get the total number of items in the list, but I say if "i" which is the loops index is less than "LT" 12 or ListLen(months), then add the "|" otherwise nothing which is why there is nothing after the <efelse>. So basically for items 1-11 add the "|" after otherwise leave it blank. And I ended the link on the other side of all the other values except for the "|" so they have thier links applied to them, except for the "|" because you don't want that to have a link.
                  </A>
                  <cfif i LT '#ListLen(months)#'>|<cfelse></cfif>    

<!--- Then just wrap it up and you're done, not too hard :o)--->
     
     </cfloop>
</cfoutput>

If you have any questions I'll be more than happy to answer them! I sometimes don't explain things the best so if you're still a bit blurry on how it works keep asking till you are clear, I'll be more than happy to answer every question.

~trail
0
 
rurth24Author Commented:
Thanks Alot....
0
 
trailblazzyr55Commented:
Yep anytime!

Was that description able to make things a bit easier to understand? Hope that help! :o)

~trail
0
 
rurth24Author Commented:
Hell yeah... More than I could ask for...
Thanks again..
0
 
rurth24Author Commented:
Hey Trail,, I have a quick question for ya..

How do you do a CFIF statement that is Comparison?

<CFIF URL.M is "Greater That is 1 or greater">
0
 
trailblazzyr55Commented:
Glad I could help ;^)
0
 
rurth24Author Commented:
I mean Equal to 1 or greater...

I want this to display only if the URL.M is 1 or greater...
<CFOUTPUT QUERY="mtotal">
                <TR>
                  <TD WIDTH="116">Total #CompanyN# Hours </TD>
                  <TD WIDTH="250">#DollarFormat(monthsum)#</TD>
                </TR>
</CFOUTPUT>
0
 
rurth24Author Commented:
I have this as of right now

<CFOUTPUT QUERY="mtotal">
              <TABLE WIDTH="387" BORDER="0" CELLPADDING="3" CELLSPACING="3" CLASS="error">
                <CFIF URL.M GTE 1>
                  <TR>
                    <TD WIDTH="116">Total #CompanyN# Hours </TD>
                    <TD WIDTH="250">#DollarFormat(monthsum)#</TD>
                  </TR>
                  <CFELSE>
                  <TR>
                    <TD WIDTH="116">Total Hours </TD>
                  </TR>
                </CFIF>
              </TABLE>
            </CFOUTPUT>
0
 
trailblazzyr55Commented:

<CFIF URL.M is "Greater That is 1 or greater">

You have to use GTE ;greater than or equal to
                        LTE ; less than or equal to
                        LT ; Less tan
                        GT ; greater than
                        EQ ; equals
                        IS ; example <cfif var1 IS 1>... used to compare
                        NEQ ; does not equal
                        Not IS ; basically if this isn't that.. used to compare
                        isdefined() checking for existance
                        not isdefined() checking for non existance

You can't use =, >, <, ... in cfif's

<CFIF URL.M = 1>   <--- this would throw an error



A couple examples are:

<CFIF URL.M GTE "1"> which means if url.m is greater than or equal to 1...
<CFIF URL.M NEQ "1"> which means if url.m is anything, but 1...
<CFIF URL.M LTE "10"> means less than or equal to ten...
.... and so on

You could also do...

<CFIF URL.M GTE 1> which means if url.m is greater than or equal to 1...
<CFIF URL.M NEQ 1> which means if url.m is anything, but 1...
<CFIF URL.M LTE 10> means less than or equal to ten...

or using isdefined

<cfif not isdefined("url.m")>
     <cfset url.m = "1">
</cfif>


This is about the same as saying

<cfparam name"url.m" default="1">    <---- much easier!!

Same, just slightly different syntax

Hope than helps ;o)

~trail
0
 
trailblazzyr55Commented:
Your code looks correct...

<CFIF URL.M GTE 1>

this will show a record if it is greater than or equal to one.

~trail
0
 
rurth24Author Commented:
Thanks Dude...
0
 
trailblazzyr55Commented:
Yep nodda problem! ;o) ..
0
 
rurth24Author Commented:
I'm going to post a query question in a few... Check it out since you helped me on this one and it's kind of related...
0
 
trailblazzyr55Commented:
yeah will do, glad to help..
0
 
rurth24Author Commented:
Just posted it...
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 16
  • 13
Tackle projects and never again get stuck behind a technical roadblock.
Join Now