Pulling Time value from Date/Time in SQL in VB 6.

The below code counts the number of records within the given date range.  Please let me know how I can modify this so that it counts the number of records every hour within the given date range.

So from May 1 to May 3, I want it to list the number of records each hour at the hours 7 AM, 8AM, 9AM, 10AM..............to 6PM.  So for 7AM, it should list the number of records that occured from 7AM to 7:59AM from May 1 to May 3.
strSQL = "SELECT COUNT(*) AS numCase, Int(Time_Open) AS numDate FROM tbl_Closed Where Time_Open >= #" & txtDateStart & "# AND Time_Open <= #" & DateAdd("d", 1, txtDateEnd) & "# GROUP BY Int(Time_Open)"

Open in new window

Tony789Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior DBACommented:
Hi,

Try

select
       count( * )
       , dateadd( hour, datediff( hour, 0, Time_Open ), 0 )
from tbl_Close
where ...
group by
      dateadd( hour, datediff( hour, 0, Time_Open ), 0 )
order by
      dateadd( hour, datediff( hour, 0, Time_Open ), 0 )

this will group all datetimes within the hour as of the start of the hour.

HTH
  David
0
Mike EghtebasDatabase and Application DeveloperCommented:

 strSQL = "SELECT Format(Time_Open,"hh") As DayTime, Sum(IIF(Format(Time_Open,"mm/dd/yyyy") ='05/01/2008',1,0) As MayFirst, Sum(IIF(Format(Time_Open,"mm/dd/yyyy") ='05/02/2008',1,0) As May2nd, Sum(IIF(Format(Time_Open,"mm/dd/yyyy") ='05/03/2008',1,0) As MayThird FROM tbl_Closed"

I understand we have to read from the form. Lets try to see if it works so far.

Mike
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Not real sure I follow, but i'll give it a shot

<SQL Server>

SELECT
      Count(CASE WHEN DATEPART(hour, Time_Open) = 1  THEN ac_comm_amount ELSE 0 END) as '1am',
      Count(CASE WHEN DATEPART(hour, Time_Open) = 2  THEN ac_comm_amount ELSE 0 END) as '2am',
      Count(CASE WHEN DATEPART(hour, Time_Open) = 3  THEN ac_comm_amount ELSE 0 END) as '3am',
                -- You get the idea
FROM tbl_Close
WHERE Time_Open BETWEEN '05-01-2007 23:59:59.999' AND '05-03-2007 23:59:59.999'
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

LowfatspreadCommented:
use the hour function ...

please confirm which database you are using (Access?)
sqlstr = "SELECT COUNT(*) AS numCase
       ,Int(Time_Open) AS numDate
       ,count(iif(hour(time_open)=7,1,0))  as [ 7am]
       ,count(iif(hour(time_open)=8,1,0))  as [ 8am]
       ,count(iif(hour(time_open)=9,1,0))  as [ 9am]
       ,count(iif(hour(time_open)=10,1,0))  as [10am]
       ,count(iif(hour(time_open)=11,1,0))  as [11am]
       ,count(iif(hour(time_open)=12,1,0))  as [12am]
       ,count(iif(hour(time_open)=13,1,0))  as [ 1pm]
       ,count(iif(hour(time_open)=14,1,0))  as [ 2pm]
       ,count(iif(hour(time_open)=15,1,0))  as [ 3pm]
       ,count(iif(hour(time_open)=16,1,0))  as [ 4pm]
       ,count(iif(hour(time_open)=17,1,0))  as [ 5pm]
       ,count(iif(hour(time_open)=18,1,0))  as [ 6pm]
   FROM tbl_Closed   Where Time_Open >= #"
   & txtDateStart & "#AND Time_Open <= #" 
   & DateAdd("d", 1, txtDateEnd) & 
   "# and hour(time_open) between 7 and 19 GROUP BY Int(Time_Open)"
 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mike EghtebasDatabase and Application DeveloperCommented:
And I am not sure if IIF() is available in VB environment. If not Switch() could be used alse provided this on is available in VB.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
{slight correction}

Not real sure I follow, but i'll give it a shot

<SQL Server>

SELECT
      Count(CASE WHEN DATEPART(hour, Time_Open) = 1  THEN Time_Open ELSE 0 END) as '1am',
      Count(CASE WHEN DATEPART(hour, Time_Open) = 2  THEN Time_Open ELSE 0 END) as '2am',
      Count(CASE WHEN DATEPART(hour, Time_Open) = 3  THEN Time_Open ELSE 0 END) as '3am',
                -- You get the idea
FROM tbl_Close
WHERE Time_Open BETWEEN '05-01-2007 23:59:59.999' AND '05-03-2007 23:59:59.999'
0
Mike EghtebasDatabase and Application DeveloperCommented:
2nd try:

strSQL = "SELECT Format(Time_Open,"hh") As DayTime, Sum(Case Format(Time_Open,"mm/dd/yyyy") ='05/01/2008' Then 1 Else 0) As MayFirst, Sum(Case Format(Time_Open,"mm/dd/yyyy") ='05/02/2008' Then 1 Else 0) As May2nd, Sum(Case Format(Time_Open,"mm/dd/yyyy") ='05/03/2008' Then 1 Else 0) As MayThird FROM tbl_Closed Where clng(Format(Time_Open,"hh")) Between 7 and 18"

This should give you:


DayTime               MayFirst                 May2nd                MayThird
------------            --------------            -----------------        --------------
07                             2                            5                          3
.
.
.
18                           33                          4                           0

Mike
0
Mike EghtebasDatabase and Application DeveloperCommented:
I used jimhorn's Case ... Then .... Else approach.
0
ee_rleeCommented:
try this
strsql= "SELECT Format(Time_Open,'hh am/pm') AS Open_Hour , Count(*) AS numCase FROM tbl_Closed WHERE Time_Open >= #" & txtDateStart & "# AND Time_Open <= #" & DateAdd("d", 1, txtDateEnd) & "# GROUP BY Format(Time_Open,'hh am/pm')"

Open in new window

0
Tony789Author Commented:
Lowfatspread,

Your code compiled but I am not sure if I am using the correct syntax below to display the count at each hour.

Please let me know.
Set X = Me.MSChart4.Object
Set grid = X.DataGrid
  
Dim i As Long
i = 0
 
Do While Not ars.EOF
    i = i + 1
    grid.SetSize 1, 1, i, 1
    'grid.ColumnLabel(1, 1) = "numCase"
    grid.RowLabel(i, 1) = "7 AM"
    grid.SetData i, 1, ars.Fields("7am"), 0
ars.MoveNext
Loop
X.ShowLegend = True

Open in new window

0
Tony789Author Commented:
Anybody else have any suggestions?
0
Tony789Author Commented:
Below is the code I am using.  It compiles but it does not print out the correct values.  It always prints the value as being 2.
strSQL = "SELECT COUNT(*) AS numCase, Int(Time_Open) AS numDate, count(iif(hour(Time_Open)=7,1,0)) as [7am], count(iif(hour(Time_Open)=8,1,0)) as [8am],count(iif(hour(Time_Open)=9,1,0)) as [9am],count(iif(hour(Time_Open)=10,1,0)) as [10am],count(iif(hour(Time_Open)=11,1,0)) as [11am],count(iif(hour(Time_Open)=12,1,0)) as [12pm],count(iif(hour(Time_Open)=13,1,0)) as [1pm],count(iif(hour(Time_Open)=14,1,0)) as [2pm],count(iif(hour(Time_Open)=15,1,0)) as [3pm],count(iif(hour(Time_Open)=16,1,0)) as [4pm],count(iif(hour(Time_Open)=17,1,0)) as [5pm],count(iif(hour(Time_Open)=18,1,0)) as [6pm] FROM tbl_Closed Where Time_Open >= #" & txtDateStart & "# AND Time_Open <= #" & DateAdd("d", 1, txtDateEnd) & "# and hour(Time_Open) between 7 and 19 GROUP BY Int(Time_Open)"
ars.Open strSQL, aconn, adOpenForwardOnly, adLockReadOnly
 
num7am = nvl(ars("7am"), "")
num8am = nvl(ars("8am"), "")
num9am = nvl(ars("9am"), "")
num10am = nvl(ars("10am"), "")
num11am = nvl(ars("11am"), "")
num12pm = nvl(ars("12pm"), "")
num1pm = nvl(ars("1pm"), "")
num2pm = nvl(ars("2pm"), "")
num3pm = nvl(ars("3pm"), "")
num4pm = nvl(ars("4pm"), "")
num5pm = nvl(ars("5pm"), "")
num6pm = nvl(ars("6pm"), "")
 
With MSChart4
 
    .Column = 1
    .ColumnLabel = "7 AM"
    .Row = 1
    .Data = IIf(IsNull(num7am), 0, num7am)
   
    .Column = 2
    .ColumnLabel = "8 AM"
    .Row = 1
    .Data = IIf(IsNull(num8am), 0, num8am)
    
    .Column = 3
    .ColumnLabel = "9 AM"
    .Row = 1
    .Data = IIf(IsNull(num9am), 0, num9am)
    
    .Column = 4
    .ColumnLabel = "10 AM"
    .Row = 1
    .Data = IIf(IsNull(num10am), 0, num10am)
    
    .Column = 5
    .ColumnLabel = "11 AM"
    .Row = 1
    .Data = IIf(IsNull(num11am), 0, num11am)
    
    .Column = 6
    .ColumnLabel = "12 PM"
    .Row = 1
    .Data = IIf(IsNull(num12pm), 0, num12pm)
    
    .Column = 7
    .ColumnLabel = "1 PM"
    .Row = 1
    .Data = IIf(IsNull(num1pm), 0, num1pm)
    
    .Column = 8
    .ColumnLabel = "2 PM"
    .Row = 1
    .Data = IIf(IsNull(num2pm), 0, num2pm)
    
    .Column = 9
    .ColumnLabel = "3 PM"
    .Row = 1
    .Data = IIf(IsNull(num3pm), 0, num3pm)
    
    .Column = 10
    .ColumnLabel = "4 PM"
    .Row = 1
    .Data = IIf(IsNull(num4pm), 0, num4pm)
    
    .Column = 11
    .ColumnLabel = "5 PM"
    .Row = 1
    .Data = IIf(IsNull(num5pm), 0, num5pm)
    
    .Column = 12
    .ColumnLabel = "6 PM"
    .Row = 1
    .Data = IIf(IsNull(num6pm), 0, num6pm)
    
End With

Open in new window

0
ee_rleeCommented:
try to use sum instead of count

strSQL = "SELECT COUNT(*) AS numCase, Int(Time_Open) AS numDate, sum(iif(hour(Time_Open)=7,1,0)) as [7am], sum(iif(hour(Time_Open)=8,1,0)) as [8am],sum(iif(hour(Time_Open)=9,1,0)) as [9am],sum(iif(hour(Time_Open)=10,1,0)) as [10am],sum(iif(hour(Time_Open)=11,1,0)) as [11am],sum(iif(hour(Time_Open)=12,1,0)) as [12pm],sum(iif(hour(Time_Open)=13,1,0)) as [1pm],sum(iif(hour(Time_Open)=14,1,0)) as [2pm],sum(iif(hour(Time_Open)=15,1,0)) as [3pm],sum(iif(hour(Time_Open)=16,1,0)) as [4pm],sum(iif(hour(Time_Open)=17,1,0)) as [5pm],sum(iif(hour(Time_Open)=18,1,0)) as [6pm] FROM tbl_Closed Where Time_Open >= #" & txtDateStart & "# AND Time_Open <= #" & DateAdd("d", 1, txtDateEnd) & "# and hour(Time_Open) between 7 and 19 GROUP BY Int(Time_Open)"
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.