Compare Values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Compare Values

Author

Topic

babai74
Starting Member

3 Posts

Posted - 2010-12-02 : 04:51:32

Hi all,

I am not a SQL 2005 developer, I have a query

select convert(char(10), CreationDate , 120) as 'Date (yyyy-mm-dd)' ,convert(varchar(5),creationdate,14) as 'Time (GMT)',count(error) as 'Total errors'
from errortracker with (nolock) where creationdate > '29-NOV-2010 19:00' and creationdate < '30-NOV-2010 06:00'
group by convert(char(10), CreationDate , 120), convert(varchar(5),creationdate,14) order by 1,2

i use the same query but different dates (for example previous week which is 22)

select convert(char(10), CreationDate , 120) as 'Date (yyyy-mm-dd)' ,convert(varchar(5),creationdate,14) as 'Time (GMT)',count(error) as 'Total errors'
from errortracker with (nolock) where creationdate > '22-NOV-2010 19:00' and creationdate < '23-NOV-2010 06:00'
group by convert(char(10), CreationDate , 120), convert(varchar(5),creationdate,14) order by 1,2

Now what i do is i check each and every minute is there are same amount or errors manually.
My question is is there any way to combine this two queries in a way that output of both queries is displayed side by side (especially total(errors) column) .

Thanks for your help
Babai

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-02 : 04:57:42

Try this -


select convert(char(10), CreationDate , 120) as 'Date (yyyy-mm-dd)' 
,convert(varchar(5),creationdate,14) as 'Time (GMT)'
,count( CASE WHEN  creationdate > '29-NOV-2010 19:00' and creationdate < '30-NOV-2010 06:00' THEN error ELSE NULL ) as 'Total errors 29-30'
,count( CASE WHEN  creationdate > '22-NOV-2010 19:00' and creationdate < '23-NOV-2010 06:00' THEN error ELSE NULL ) as 'Total errors 22-23'
from errortracker with (nolock)
group by convert(char(10), CreationDate , 120), convert(varchar(5),creationdate,14) order by 1,2

Vaibhav T

If I cant go back, I want to go fast...

babai74
Starting Member

3 Posts

Posted - 2010-12-02 : 05:44:25

when i tried this , i get following msg

Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near ')'.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-02 : 05:49:53

Put an end before the close brackets
select convert(char(10), CreationDate , 120) as 'Date (yyyy-mm-dd)'
,convert(varchar(5),creationdate,14) as 'Time (GMT)'
,count( CASE WHEN creationdate > '29-NOV-2010 19:00' and creationdate < '30-NOV-2010 06:00' THEN error ELSE NULL end) as 'Total errors 29-30'
,count( CASE WHEN creationdate > '22-NOV-2010 19:00' and creationdate < '23-NOV-2010 06:00' THEN error ELSE NULL end) as 'Total errors 22-23'
from errortracker with (nolock)
group by convert(char(10), CreationDate , 120), convert(varchar(5),creationdate,14) order by 1,2

I would code it as
select convert(char(10), CreationDate , 120) as 'Date (yyyy-mm-dd)'
,convert(varchar(5),creationdate,14) as 'Time (GMT)'
,sum( CASE WHEN creationdate > '29-NOV-2010 19:00' and creationdate < '30-NOV-2010 06:00' THEN 1 ELSE 0 end) as 'Total errors 29-30'
,sum( CASE WHEN creationdate > '22-NOV-2010 19:00' and creationdate < '23-NOV-2010 06:00' THEN 1 ELSE 0 end) as 'Total errors 22-23'
from errortracker with (nolock)
group by convert(char(10), CreationDate , 120), convert(varchar(5),creationdate,14) order by 1,2

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

babai74
Starting Member

3 Posts

Posted - 2010-12-02 : 06:16:42

cool, what ever i query i execute from the above i get the result of the whole data(right from 2003 till 2010) in DB, i dont get for the specified dates

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-02 : 07:09:46

How could you say that Number of rows from both the queries will be same.
If its not then how could you put it side by side.

Vaibhav T

If I cant go back, I want to go fast...

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-02 : 07:17:43

select convert(varchar(5),creationdate,14) as 'Time (GMT)'
,sum( CASE WHEN creationdate > '29-NOV-2010 19:00' and creationdate < '30-NOV-2010 06:00' THEN 1 ELSE 0 end) as 'Total errors 29-30'
,sum( CASE WHEN creationdate > '22-NOV-2010 19:00' and creationdate < '23-NOV-2010 06:00' THEN 1 ELSE 0 end) as 'Total errors 22-23'
from errortracker with (nolock)
where (creationdate > '29-NOV-2010 19:00' and creationdate < '30-NOV-2010 06:00')
or (creationdate > '22-NOV-2010 19:00' and creationdate < '23-NOV-2010 06:00')
group by convert(varchar(5),creationdate,14)
order by case when convert(varchar(5),creationdate,14) > '06:00' then 1 else 2 end, convert(varchar(5),creationdate,14)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-02 : 07:23:20

quote:
Originally posted by nigelrivett

select convert(varchar(5),creationdate,14) as 'Time (GMT)'
,sum( CASE WHEN creationdate > '29-NOV-2010 19:00' and creationdate < '30-NOV-2010 06:00' THEN 1 ELSE 0 end) as 'Total errors 29-30'
,sum( CASE WHEN creationdate > '22-NOV-2010 19:00' and creationdate < '23-NOV-2010 06:00' THEN 1 ELSE 0 end) as 'Total errors 22-23'
from errortracker with (nolock)
where (creationdate > '29-NOV-2010 19:00' and creationdate < '30-NOV-2010 06:00')
or (creationdate > '22-NOV-2010 19:00' and creationdate < '23-NOV-2010 06:00')
group by convert(varchar(5),creationdate,14)
order by case when convert(varchar(5),creationdate,14) > '06:00' then 1 else 2 end, convert(varchar(5),creationdate,14)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

What you have done in this ?
Can you please explain ?

Vaibhav T

If I cant go back, I want to go fast...

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-02 : 07:31:52

You can't return the date as it only applies to the error counts - they are on different dates so I've returned the minute and ordered by the minute depending on whether it's before or after midnight.

I don't thik this sort of thing is what you want though. Not very configurable and no result if no entries for a minute.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-12-02 : 07:45:20

I haven't tried to run this - just typed it in but maybe you want something more like

declare @d1strt datetime
declare @d2strt datetime
declare @strttime datetime
declare @numhours int

select @d1strt = '20101122'
select @d2strt = '20101129'
select @strttime = '19:00'
select @numhours = 11

;with cte as
(
select mi = DATEPART(MI,@strttime) dte = @strttime
union all
select mi = mi+1, dte = DATEADD(mi,1,cte.dte)
from cte
where dte < DATEADD(hh,@numhours,@strttime)
)
select cte.mi ,
dte1 = @d1strt ,
dte1ErrCnt = coalesce(a.num,0) ,
dte2 = @d2strt ,
dteErrCnt = coalesce(b.num,0) ,
from cte
left join ( select datepart(mi,creationdate), num = count(*) from errortracker
where creationdate >= @d1strt+@strttime and creationdate < DATEADD(hh,@numhours,@d1strt+@strttime)
group by datepart(mi,creationdate)
) a
on cte.mi = a.mi
left join ( select datepart(mi,creationdate), num = count(*) from errortracker
where creationdate >= @d2strt and creationdate < DATEADD(hh,@numhours,@d2strt+@strttime)
group by datepart(mi,creationdate)
) b
on cte.mi = b.mi
order by cte.dte

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources