Time, Count(Hits)

• SwePeso

  SSC-Dedicated

  Points: 39757

  More actions

  August 29, 2007 at 11:43 am

  #730212

  Can you post the 12 records here?

   

  ---

  N 56°04'39.16"
  E 12°55'05.25"

• Shamshad Ali

  SSChasing Mays

  Points: 604

  More actions

  August 29, 2007 at 11:57 pm

  #730323

  Please see post 10. following are 12 hits logged in to LoginTrack table for 01-Jan-2007 with the help of following query:

  Declare

   @todate datetime, 

   @fromdate datetime, 

   @channelid numeric(9)

   

  set @todate = '01/01/2007'

  set @fromdate = '01/01/2007'

  select LoginDateTime from LoginTrack

  where convert (varchar, LoginDateTime,101) between @fromdate and @todate

  order by LoginDateTime

  No.	Login_datetime
  1	2007-01-01 04:00:00.000
  2	2007-01-01 08:00:00.000
  3	2007-01-01 09:00:00.000
  4	2007-01-01 10:26:18.503
  5	2007-01-01 11:00:00.000
  6	2007-01-01 11:00:00.000
  7	2007-01-01 11:00:00.000
  8	2007-01-01 14:00:00.000
  9	2007-01-01 14:52:27.817
  10	2007-01-01 16:00:00.000
  11	2007-01-01 22:00:00.000
  12	2007-01-01 23:00:00.000

  there are total 12 hits on 01-Jan-2007, but the result is giving me 1 more hit for 22:00 to 23:00, see below: also the percent becomes above 100.

  <TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num x:fmla="=SUM(D224)">13

  No.	Hit Duration	Percent	Hits
  1	00:00 to 01:00	0	0
  2	01:00 to 02:00	0	0
  3	02:00 to 03:00	0	0
  4	03:00 to 04:00	0	0
  5	04:00 to 05:00	8.33	1
  6	05:00 to 06:00	0	0
  7	06:00 to 07:00	0	0
  8	07:00 to 08:00	0	0
  9	08:00 to 09:00	8.33	1
  10	09:00 to 10:00	8.33	1
  11	10:00 to 11:00	8.33	1
  12	11:00 to 12:00	25	3
  13	12:00 to 13:00	0	0
  14	13:00 to 14:00	0	0
  15	14:00 to 15:00	16.67	2
  16	15:00 to 16:00	0	0
  17	16:00 to 17:00	8.33	1
  18	17:00 to 18:00	0	0
  19	18:00 to 19:00	0	0
  20	19:00 to 20:00	0	0
  22	21:00 to 22:00	0	0
  23	22:00 to 23:00	16.67	2	should be 1
  24	23:00 to 00:00	8.33	1
  	your query results:	108.32
  	it should be	100	12

  DECLARE     @ToDate DATETIME,

              @FromDate DATETIME,

              @ChannelID NUMERIC(9)

   

  SELECT      @ToDate = '01/01/2007',

              @FromDate = '01/01/2007'

   

  SELECT      v.Number + 1 AS Hit,

              CONVERT(CHAR(5), DATEADD(HOUR, v.Number, 0), 108) + ' to ' + CONVERT(CHAR(5), DATEADD(HOUR, v.Number + 1, 0), 108) AS [Hit Duration],

              CONVERT(DECIMAL(5, 2), 100.0 * COUNT(lt.LoginDateTime) / t.h) AS [Percent],

              COUNT(lt.LoginDateTime) AS Hits

  FROM        (

                           SELECT 0 AS Number UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL

                          SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL

                          SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL

                          SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL

                          SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL

                          SELECT 22 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23

              ) AS v

  CROSS JOIN  (

                          SELECT      COUNT(*) AS h

                          FROM        LoginTrack

                          WHERE       LoginDateTime >= @FromDate

                                       AND LoginDateTime < DATEADD(DAY, 1, @ToDate)

              ) AS t

  LEFT JOIN   LoginTrack AS lt ON DATEPART(HOUR, lt.LoginDateTime) = v.Number

                          AND lt.LoginDateTime >= @FromDate

                          AND lt.LoginDateTime < DATEADD(DAY, 1, @ToDate)

  GROUP BY    v.Number,

              t.h

  ORDER BY    v.Number

   Shamshad Ali.

• Shamshad Ali

  SSChasing Mays

  Points: 604

  More actions

  August 30, 2007 at 12:22 am

  #730327

  Peter, I found out the clue, here was a little mistake making V table

  the last row of populating v table was missing 20 and it was 22 twice.

  Old:

  From

  (

                          SELECT 0 AS Number UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL

                          SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL

                          SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL

                          SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL

                          SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL

                          SELECT 22 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23

              ) AS v

  New:

  From

  (

                          SELECT 0 AS Number UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL

                          SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL

                          SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL

                          SELECT 12 UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL

                          SELECT 16 UNION ALL SELECT 17 UNION ALL SELECT 18 UNION ALL SELECT 19 UNION ALL

                          SELECT 20 UNION ALL SELECT 21 UNION ALL SELECT 22 UNION ALL SELECT 23

              ) AS v

   

  Thanks for such GREAT!!! support, its now working perfect.

   

  Shamshad Ali.