Solved

!!! Calendar / Date / Array / Query problem

Posted on 2004-10-07
13
321 Views
Last Modified: 2013-12-24
Here's the app def:  
Its an approval site for time. User enters how much hours they worked overtime.
Manager logs on and can approve or reject.

Current Situation:

I need to be able to add the number of hours for each day and print it out for that day for each employee

I have the following query:

<!--- grabs all approved users info --->
<CFQUERY DATASOURCE="lieu" NAME="lookup">
      SELECT to_char (zdate, 'mm/DD/yyyy hh24:MI') as zdate,
                  to_char (time, 'mm/DD/yyyy hh24:MI') as zTime,
                  title, id, comments, status, type, user_id
      FROM lieu_events
      WHERE zdate LIKE '%#UCase(DateFormat(ThisCalMonth, "mmm-yy"))#%'
      AND status='APPROVED'
</CFQUERY>



How do I alter the SQL statement so that it sums up the number of hours entered (zdate - time) per day for each user_id??
0
Comment
Question by:kkhipple
  • 7
  • 2
  • 2
  • +1
13 Comments
 
LVL 35

Expert Comment

by:mrichmon
ID: 12253050
<CFQUERY DATASOURCE="lieu" NAME="lookup">
     SELECT SUM(Datediff(h, zdate, time)) AS hoursum, user_id
     FROM lieu_events
     WHERE zdate LIKE '%#UCase(DateFormat(ThisCalMonth, "mmm-yy"))#%'
     AND status='APPROVED'
     GROUP BY user_id
</CFQUERY>

That should do it.

If you still need other columns just add them to both the select and group by....
0
 
LVL 9

Assisted Solution

by:CFDevHead
CFDevHead earned 100 total points
ID: 12253095
you might want to try this
SELECT to_char (zdate, 'mm/DD/yyyy hh24:MI') as zdate,
               to_char (time, 'mm/DD/yyyy hh24:MI') as zTime,
               (datediff(minute ,zdate,time) )* 60 as totalHours
               title, id, comments, status, type, user_id
     FROM lieu_events
     WHERE zdate LIKE '%#UCase(DateFormat(ThisCalMonth, "mmm-yy"))#%'
     AND status='APPROVED'
0
 
LVL 5

Author Comment

by:kkhipple
ID: 12253113
Thx for responding mrichmon.
Btw, there is also a type table field.
This indicates what kind of request. Overtime or Taking time off.

How would that change, like so?

<CFQUERY DATASOURCE="lieu" NAME="lookup">
     SELECT SUM(Datediff(h, zdate, time)) AS hoursum, user_id, type
     FROM lieu_events
     WHERE zdate LIKE '%#UCase(DateFormat(ThisCalMonth, "mmm-yy"))#%'
     AND status='APPROVED'
     GROUP BY user_id, type
</CFQUERY>
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 9

Expert Comment

by:CFDevHead
ID: 12253156
How would that change, like so?
what do you mean?
0
 
LVL 35

Accepted Solution

by:
mrichmon earned 100 total points
ID: 12253246
<CFQUERY DATASOURCE="lieu" NAME="lookup">
     SELECT SUM(Datediff(h, zdate, time)) AS hoursum, user_id, type
     FROM lieu_events
     WHERE zdate LIKE '%#UCase(DateFormat(ThisCalMonth, "mmm-yy"))#%'
     AND status='APPROVED'
     GROUP BY user_id, type
</CFQUERY>

Would give results like:
user_id          hoursum          type
123643            10                 overtime
123643            7                   taking time off
658354            5                  taking time off
967562           8                   sick
993422             17               overtime
999231            24               taking time off
etc...
0
 
LVL 5

Author Comment

by:kkhipple
ID: 12253254
I mean, if on one day within the month .... the user - for whatever reason - decides to input two requests for time taken off or overtime then i'd like to sum up the hours of time taken off separately and overtime worked separately within one particular day for each user.


is that too cryptic?  i hope someone can figure out what im trying to say
0
 
LVL 5

Author Comment

by:kkhipple
ID: 12253388
Here's the table definition:

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 VARCHAR2(8)
 USER_ID                                            VARCHAR2(6)
 ZDATE                                              DATE
 TIME                                               DATE
 TITLE                                              VARCHAR2(75)
 COMMENTS                                           VARCHAR2(200)
 STATUS                                             VARCHAR2(50)
 TYPE                                               VARCHAR2(25)


Im' getting the following error:

ORA-00904: invalid column name

SQL = "SELECT SUM(Datediff(h, zdate, time)) AS hoursum, user_id FROM lieu_events WHERE zdate LIKE '%OCT-04%' AND status='APPROVED' GROUP BY user_id"
0
 
LVL 5

Author Comment

by:kkhipple
ID: 12253404
mind you, I also would like it so that it sums up the hours for each day.  

is the query doing that?

<CFQUERY DATASOURCE="lieu" NAME="lookup">
     SELECT SUM(Datediff(h, zdate, time)) AS hoursum, user_id, type
     FROM lieu_events
     WHERE zdate LIKE '%#UCase(DateFormat(ThisCalMonth, "mmm-yy"))#%'
     AND status='APPROVED'
     GROUP BY user_id, type
</CFQUERY>


from what it seems, its summing up the hours for whatever zdate im passing it. which in this case, is the first of the month.
0
 
LVL 5

Author Comment

by:kkhipple
ID: 12253476
if that's too difficult... once the query is working.. should i itterate through the query and input the values into an array?  if so, how would i do that?


sorry guys.. but i need to get this done ASAP  

KDK
0
 
LVL 6

Assisted Solution

by:campbelc
campbelc earned 50 total points
ID: 12261824
Can u put together an excel speadsheet showing the layout that you are looking for with some sample data. Much easier to code then.

email it to: badtemper@gmail.com

Thanks.
0
 
LVL 5

Author Comment

by:kkhipple
ID: 12263115
haha.... nice email addy campbelc / badtemper ...  

well i got it figured out ... and although none of you were able to guide me in the right direciton. possibly because i didn't explain properly... i will give a few of you that did attempt to help me out  points....


KDK
p.s.  i couldn't get the group by thing going for my particular problem... so used an array to store unqiue user_id ... and then looped thru and added the hrs ...  if anyone wants to see the code... let me know
0
 
LVL 5

Author Comment

by:kkhipple
ID: 12263161
These points were distributed for effort.

campbelc you get points for having a badtemper
0
 
LVL 6

Expert Comment

by:campbelc
ID: 12264812
lol, thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
how can I temporarily cancel my monthly membership with Hostgator.com? 11 137
ColdFusion Rereplace 3 78
CFFILE upload help 98 158
.dwt files not viewable in browser - why? 2 89
Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question