Solved

Timeserial on sql statement question

Posted on 2009-07-14
3
324 Views
Last Modified: 2013-11-27
Good Day Gurus,
I'm basically stuck on a query involving a timeserial operation on an sql statement:

"SELECT DISTINCT FORMAT(Main_Records.Participation_Date, 'MMMM'), " & _
           "FORMAT(Main_Records.Participation_Date, 'YYYY'),  timeserial(0, count3, 0) ,  (count1 * 60) + count2 AS count3,  " & _
           "(SELECT SUM(M.Hours) FROM Main_Records M WHERE M.User = '" & fOSUserName() & "' AND " & _
           "FORMAT(M.Participation_Date, 'MMMM') = FORMAT(Main_Records.Participation_Date, 'MMMM') AND " & _
           "FORMAT(M.Participation_Date, 'YYYY') = FORMAT(Main_Records.Participation_Date, 'YYYY')) as count1, " & _
           "(SELECT SUM(M.Minutes) FROM Main_Records M WHERE M.User = '" & fOSUserName() & "' AND " & _
           "FORMAT(M.Participation_Date, 'MMMM') = FORMAT(Main_Records.Participation_Date, 'MMMM') AND " & _
           "FORMAT(M.Participation_Date, 'YYYY') = FORMAT(Main_Records.Participation_Date, 'YYYY')) as count2 FROM " & _
           "Main_Records  group by FORMAT(Main_Records.Participation_Date, 'MMMM'), FORMAT(Main_Records.Participation_Date, 'YYYY')  order BY 1 ASC"

Now I already isolated the issue with this statement which is the 'timeserial(0, count3, 0)' field.  Now when doing a straight mathematical operation on the alias 'count3', like count3 + 1,  the statement works.  But if I do something like a timeserial, or a dateadd operation using the alias, the statement fails.  Now, I'm assuming both timeserial and dateadd don't work with aliases? Any Ideas?  Thanks!
0
Comment
Question by:jad0083
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 24856630
>timeserial(0, count3, 0) ,  (count1 * 60) + count2 AS count3
so, you reuse the same column name as alias.

note: when you use count3 anywhere in the query, it remains the value of the table column, and not the result of the expression above.

apart from that: what are the values of count3?
timeserial might not take any value >= 60 ...
0
 

Author Comment

by:jad0083
ID: 24856932
@angelll
Well, in the end, after seeing how messy the actual statement is, I just rewrote the whole damn thing, eliminating the nested selects in the process and from your advise, just minimized the alias usage.  And it worked!  Below is the final statement that I came up with that worked, thanks!


"SELECT FORMAT(Main_Records.Participation_Date, 'MMMM') AS D1a, FORMAT(Main_Records.Participation_Date, 'MM') AS D1b, " & _
           "FORMAT(Main_Records.Participation_Date, 'YYYY') AS D2, IIF(CStr(countb) = '00', counta & ' hour(s)', " & _
           "counta & ' hour(s) ' & countb & ' minute(s)'), LEFT(countx, INStr(county, '.') -1) AS counta, " & _
           "RIGHT(countx, IIF(LEN(countx) - INStr(county, '.') < 0, 0, LEN(countx) - " & _
           "INStr(county, '.'))) AS countb, IIF(INStr(countx, '.') = 0, countx & '.', countx) AS county, " & _
           "CStr(FORMAT(TIMESERIAL(0, count1, 0), 'HH.MM')) AS countx, ((SUM(Main_Records.Hours) * 60) + " & _
           "SUM(Main_Records.Minutes)) AS count1 FROM Main_Records WHERE Main_Records.User = '" & fOSUserName & "' " & _
           "GROUP BY FORMAT(Main_Records.Participation_Date, 'MMMM'), FORMAT(Main_Records.Participation_Date, 'MM'), " & _
           "FORMAT(Main_Records.Participation_Date, 'YYYY') ORDER BY 3 DESC, 2 DESC"

Open in new window

0
 

Author Closing Comment

by:jad0083
ID: 31603590
Thanks!
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

730 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