Solved

Timeserial on sql statement question

Posted on 2009-07-14
3
321 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
  • 2
3 Comments
 
LVL 142

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access Runtime 2010 Error 17 29
Unable to save view in SSMS 21 53
xpath sql query 2008 8 41
Format a Field AFTER UPDATE 5 18
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

929 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now