TECHNOLOGIES
FORUMS
JOBS
BOOKS
EVENTS
INTERVIEWS
Live
MORE
LEARN
Training
CAREER
MEMBERS
VIDEOS
NEWS
BLOGS
Sign Up
Login
No unread comment.
View All Comments
No unread message.
View All Messages
No unread notification.
View All Notifications
Answers
Post
An Article
A Blog
A News
A Video
An EBook
An Interview Question
Ask Question
Forums
Monthly Leaders
Forum guidelines
TAN WhoAMI
NA
291
0
SQL Distinct, Count data into appropriate time slot
Aug 5 2013 10:34 PM
SELECT COUNT(DISTINCT MAINSERNO) AS TOTAL
FROM PPL_PRODUCT_TESTID2
WHERE ((MONO = '5750333') AND (ITNO = '132961') AND (SYSTEMTYPE = '0036') AND (TESTFLAG = '1'))
Order by TIMESTAMP ASC
==> above gives me a TOTAL of 150
SELECT DISTINCT MAINSERNO, to_char(TIMESTAMP, 'MM/DD/YYYY HH24:MI:SS') as TIMESTAMP
FROM PPL_PRODUCT_TESTID2
WHERE ((MONO = '5750333') AND (ITNO = '132961') AND (SYSTEMTYPE = '0036') AND (TESTFLAG = '1'))
Order by TIMESTAMP ASC
==> above gives me a TOTAL of 165, of which 15 are MAINSERNO are duplicates. I have used the DISTINCT, why does it still produces duplicates?
Also, based on its TIMESTAMP, I would want to extract the count, and put them into its appropriate hourly time slot as shown in the printscreen, is that possible?
That is e.g from TIMESTAMP data of HH:MM:SS, those data that are after 00:30:00 and before 01:30:00 would be counted and place into the 01:30 Hours(18) timeslot.
How do I loop through the 24-hour time slot, instead of repeating each one as below:
also possible not to include the date, and only the time in the comparison condition?
Reply
Answers (
23
)
dynamic menu database connection in asp.net c#
open and edit word document through form application