Help with a query

  • sam bryant

    Right there with Babe

    Points: 786

    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. 😀

  • Samuel Vella

    SSCrazy Eights

    Points: 8055

    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)

  • sam bryant

    Right there with Babe

    Points: 786

    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!

  • Samuel Vella

    SSCrazy Eights

    Points: 8055

    select top 1

    date, session

    from #rr

    where session not in (select session from #rr where message = 'error')

    order by date desc

  • kgunnarsson

    Ten Centuries

    Points: 1108

    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.

  • sam bryant

    Right there with Babe

    Points: 786

    Exactly what I was looking for.

    Many thanks for the quick replies.

    Cheers! 😀

  • Samuel Vella

    SSCrazy Eights

    Points: 8055

    kgunnarsson,

    That doesn't return anything for me!

  • kgunnarsson

    Ten Centuries

    Points: 1108

    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.

  • sam bryant

    Right there with Babe

    Points: 786

    Samuel Vella (1/9/2008)


    select top 1

    date, 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')

  • Samuel Vella

    SSCrazy Eights

    Points: 8055

    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

  • Matt Miller (4)

    SSC Guru

    Points: 124208

    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?

  • sam bryant

    Right there with Babe

    Points: 786

    Samuel Vella (1/9/2008)


    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

    Perfect, thanks.

  • GSquared

    SSC Guru

    Points: 260824

    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 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply