January 9, 2008 at 6:48 am
The following is the table structure and sample data. I am looking to return the most recent date (just date) and session that does not contain a message of 'error'. So with the following data I would want to return the date 01/09/2008 and session 1. Session is reset daily.
if object_id('tempdb..#rr') is not null
drop table #rr
CREATE TABLE #rr(
[id] [int] IDENTITY(1,1) NOT NULL,
[date] [smalldatetime] NOT NULL CONSTRAINT [DF_rr_date] DEFAULT (getdate()),
[session] [tinyint] NOT NULL CONSTRAINT [DF_rr_session] DEFAULT ((0)),
[message] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_rr_message] DEFAULT (' '),
CONSTRAINT [PK_rr] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO #rr VALUES ('01/08/2008 07:01:00',1,'started')
INSERT INTO #rr VALUES ('01/08/2008 07:02:00',1,'proccessing')
INSERT INTO #rr VALUES ('01/08/2008 07:03:00',1,'complete')
INSERT INTO #rr VALUES ('01/08/2008 08:01:00',2,'started')
INSERT INTO #rr VALUES ('01/08/2008 08:02:00',2,'error')
INSERT INTO #rr VALUES ('01/08/2008 08:03:00',2,'complete')
INSERT INTO #rr VALUES ('01/09/2008 08:01:00',1,'started')
INSERT INTO #rr VALUES ('01/09/2008 08:02:00',1,'processing')
INSERT INTO #rr VALUES ('01/09/2008 08:03:00',1,'complete')
INSERT INTO #rr VALUES ('01/09/2008 09:01:00',2,'started')
INSERT INTO #rr VALUES ('01/09/2008 09:02:00',2,'error')
INSERT INTO #rr VALUES ('01/09/2008 09:03:00',2,'complete')
Thanks for any help. 😀
January 9, 2008 at 6:52 am
Nice bit of your code in your post, certainly speeds things up! 🙂
select top 1
date, session
from #rr
where message <> 'error'
order by date desc
HTH
(whoops, missed the "desc" out)
January 9, 2008 at 7:04 am
Thanks for your reply. This is not quite what I need. A little more information. Session is reset daily and increases over time (so as the day goes on the session increases). I am looking for the largest (most recent) date and session where that session does not contain an entry of 'error'. So for this data I would want a date of 01/09/2008 and a session of 1 returned since session 2 has an 'error' entry.
Please let me know if this is unclear.
Thanks!
January 9, 2008 at 7:09 am
select top 1
date, session
from #rr
where session not in (select session from #rr where message = 'error')
order by date desc
January 9, 2008 at 7:09 am
try using an CTE to assist .. see example
with mysession(date, session) as
(
select top 1 date, session from #rr where message <> 'error' order by date
)
select top 1 convert(varchar,date,2),session from mysession
group by convert(varchar,date,2),session
having count(session) = 3
kgunnarsson
Mcitp Database Developer.
January 9, 2008 at 7:12 am
Exactly what I was looking for.
Many thanks for the quick replies.
Cheers! 😀
January 9, 2008 at 7:19 am
kgunnarsson,
That doesn't return anything for me!
January 9, 2008 at 7:25 am
sorry... incorrect code.
with mysession(date, session) as
(
select date, session from #rr where message <> 'error'
)
select top 1 convert(varchar,date,2),session from mysession
group by convert(varchar,date,2),session
having count(session) = 3
kgunnarsson
Mcitp Database Developer.
January 9, 2008 at 7:26 am
Samuel Vella (1/9/2008)
select top 1date, session
from #rr
where session not in (select session from #rr where message = 'error')
order by date desc
Turns out I was too quick to jump the gun and say it worked. With a revised set of data it does not return any results. With this new data I would want the 01/08/2008 session 1 record, but that session is not being returned because of the "select session from #rr where message = 'error'" subquery.
Also I did not get any results from the CTE.
New data:
if object_id('tempdb..#rr') is not null
drop table #rr
CREATE TABLE #rr(
[id] [int] IDENTITY(1,1) NOT NULL,
[date] [smalldatetime] NOT NULL CONSTRAINT [DF_rr_date] DEFAULT (getdate()),
[session] [tinyint] NOT NULL CONSTRAINT [DF_rr_session] DEFAULT ((0)),
[message] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL CONSTRAINT [DF_rr_message] DEFAULT (' '),
CONSTRAINT [PK_rr] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO #rr VALUES ('01/08/2008 07:01:00',1,'started')
INSERT INTO #rr VALUES ('01/08/2008 07:02:00',1,'proccessing')
INSERT INTO #rr VALUES ('01/08/2008 07:03:00',1,'complete')
INSERT INTO #rr VALUES ('01/08/2008 08:01:00',2,'started')
INSERT INTO #rr VALUES ('01/08/2008 08:02:00',2,'error')
INSERT INTO #rr VALUES ('01/08/2008 08:03:00',2,'complete')
INSERT INTO #rr VALUES ('01/09/2008 08:01:00',1,'started')
INSERT INTO #rr VALUES ('01/09/2008 08:02:00',1,'error')
INSERT INTO #rr VALUES ('01/09/2008 08:03:00',1,'complete')
INSERT INTO #rr VALUES ('01/09/2008 09:01:00',2,'started')
INSERT INTO #rr VALUES ('01/09/2008 09:02:00',2,'error')
INSERT INTO #rr VALUES ('01/09/2008 09:03:00',2,'complete')
January 9, 2008 at 7:34 am
It was correct, its your specifications that were wrong 😛
select top 1
date, session
from #rr
where convert(varchar, date, 103) + '-'+ cast(session as varchar) not in (select convert(varchar, date, 103) + '-'+ cast(session as varchar) from #rr where message = 'error')
order by date desc
January 9, 2008 at 8:04 am
With sesslist(dateshort,datelong,session, [message],rn) as
(
select
cast(date as int) as dateshort,
date as datelong,
session,
[message],
ROW_NUMBER() over (PARTITION by cast(date as int) order by date desc) RN
from #RR s
where not exists
(
select *
from #RR s1
where
s1.[message] = 'error' and
s1.session=s.session and
cast(s1.date as int) =cast(s.date as int)
)
)
select dateshort,datelong,session, [message],rn
from sesslist
where
rn=1
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 9, 2008 at 8:08 am
Samuel Vella (1/9/2008)
It was correct, its your specifications that were wrong 😛
select top 1date, session
from #rr
where convert(varchar, date, 103) + '-'+ cast(session as varchar) not in (select convert(varchar, date, 103) + '-'+ cast(session as varchar) from #rr where message = 'error')
order by date desc
Perfect, thanks.
January 9, 2008 at 8:12 am
Modify the temp table definition by adding:
DateDay as cast(convert(varchar(25), [date], 101) as datetime),
Then run your inserts, and try:
select top 1 #rr.date, #rr.session
from #rr
inner join
(select dateday, session
from #rr
except
select dateday, session
from #rr
where [message] = 'error') SubRR
on #rr.dateday = subrr.dateday
and #rr.session = subrr.session
order by date
Does that give you what you need? (This will only work in SQL 2005. If you need an SQL 2000 version, I can do that by converting the "except" to an outer join.)
If you are adding a lot of values to the temp table, this version will allow you to add an index to the temp table on the DateDay column, which will make it run faster than querying against the cast and convert functions directly. If it's only a small number of rows, it won't matter which version you use.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply