CODEWITHSUNDEEP
sqlsql-serversql-server-2005
Penguen
Penguen

Reputation: 17298

How to generate this sql query?

"0" is VisitingCount ---Numeric Value Datepart, For example: 00:00--1, 00:30--2, 01:00--2, 01:30--3


CREATE TABLE #Temp (VisitingCount int, [Time] int )
DECLARE @DateNow DATETIME,@i int,@Time int
set @DateNow='00:00'  
set @i=1;  
while(@i<48)  
    begin  
        set @DateNow = DATEADD(minute, 30, @DateNow)
        set @Time = (datepart(hour,@DateNow)*60+datepart(minute,@DateNow))/30 
        insert into #Temp(VisitingCount,[Time]) values(0,@Time )
        set @i=@i+1
    end


select Sum(VisitingCount), [Time]
    from #Temp group by [Time]
    Union All
         select count(page) as VisitingCount, 
       (datepart(hour,Date)*60+datepart(minute,Date))/30 as [Time]
       from scr_SecuristLog
       where Date between '2009-05-04 10:30' and '2009-05-04 12:30'
       GROUP BY (datepart(hour,Date)*60+datepart(minute,Date))/30--scr_SecuristLog.Date

My query return below Table


0   1

0   2

..(removed repeating)..

0   45

0   46

0   47

825 23

526 21

1064    24

885 22


This is my dream Table. i need this:



0   1

0   2

..(removed repeating)..

0   19

0   20

526 21

885 22

825 23

1064    24

0   25

0   26

..(removed repeating)..

0   46

0   47

Upvotes: 0

Views: 116

Answers (1)

Dead account
Dead account

Reputation: 19960

add Order By 2 desc to your select

  select Sum(VisitingCount), [Time]
  from #Temp group by [Time]
Union All
  select count(page) as VisitingCount, 
  (datepart(hour,Date)*60+datepart(minute,Date))/30 as [Time]
  from scr_SecuristLog
  where Date between '2009-05-04 10:30' and '2009-05-04 12:30'
  GROUP BY (datepart(hour,Date)*60+datepart(minute,Date))/30--scr
order by 2 desc

See example A in from UNION (Transact-SQL)

Upvotes: 3

Related Questions