urgent help! - how to get the column selection?

Dev Shed Forums Sponsor:

October 4th, 2004, 02:07 PM

shivanjali

Registered User

Join Date: Jun 2004

Posts: 11

Time spent in forums: < 1 sec
Reputation Power: 0

urgent help! - how to get the column selection?

suppose i have a table penalties with
ticket_number, ticket_start_date datetime, ticket_end_date datetime

There are many records of same ticket_number.example:
ticket_number ticket_start_date ticket_end_date
75 2/1/03 3/2/03
75 2/4/03 5/4/03

34 2/1/03 3/3/04
34 3/4/04 3/3/05

I want to write a query which will:
1. select the ticket_number which is not active as of today
in case of ticket_number 75 it is closed so I want to print it in report

in case of 34 it was closed but now opened, so i don't want in my report.

Could anybody suggest a query how to do it?

I was trying with stored procedure and cursor :

-------------------------------------
CREATE PROCEDURE simpleTest

AS

declare @Today datetime
set @Today = '10/04/2004'
declare @colA varchar(38)
declare @colB datetime
declare @colC datetime
declare @myCursor CURSOR

set @myCursor = CURSOR FAST_FORWARD
for
select distinct ticket_number, ticket_start_date, ticket_end_date
from penalties
where ticket_end_date <= @Today

open @myCursor
fetch next from @myCursor
into @colA, @colB, @colC

while @@fetch_status=0
begin
print @colA
print @colB
print @colC
fetch next from @myCursor
into @colA, @colB, @colC
end

close @myCursor
deallocate @myCursor

GO

-- =============================================
-- example to execute the store procedure
-- =============================================
EXECUTE simpleTest
GO
-------------

however this gives closed tickets but multiple records. I want only the last record details of the ticket_number.
I tried last(ticket_number) in my query, but it doesn't affect.
Please suggest! thanks.
I am using MS SQL server.

October 4th, 2004, 03:10 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,982

Time spent in forums: 1 Month 4 Weeks 18 h 20 m 26 sec
Reputation Power: 1028

Code:

select ticket_number
     , ticket_start_date 
     , ticket_end_date     
  from penalties as X
 where ticket_end_date =
     ( select max(ticket_end_date)
         from penalties
        where ticket_number 
            = X.ticket_number )
   and not exists
     ( select 1
         from penalties
        where ticket_number 
            = X.ticket_number
          and ticket_start_date <= getdate()
          and ticket_end_date >= getdate()    )

__________________
r937.com | rudy.ca

pre-order my book Simply SQL from Amazon

October 4th, 2004, 04:51 PM

shivanjali

Registered User

Join Date: Jun 2004

Posts: 11

Time spent in forums: < 1 sec
Reputation Power: 0

solution-

I can't understand the solution. I tried to execute but it's not successful. could you please help me understand what you do with 'ant not exists' clause here?

-------------

Quote:

Originally Posted by r937

Code:

select ticket_number
     , ticket_start_date 
     , ticket_end_date     
  from penalties as X
 where ticket_end_date =
     ( select max(ticket_end_date)
         from penalties
        where ticket_number 
            = X.ticket_number )
   and not exists
     ( select 1
         from penalties
        where ticket_number 
            = X.ticket_number
          and ticket_start_date <= getdate()
          and ticket_end_date >= getdate()    )

October 4th, 2004, 05:02 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,982

Time spent in forums: 1 Month 4 Weeks 18 h 20 m 26 sec
Reputation Power: 1028

the AND NOT EXISTS subquery attempts to find rows with the same ticket number where the start date is less than or equal to today and the end date is greater than or equal to today, and if such a row doesn't exist, then the ticket number is not curent and therefore should be returned by the query

and yes, the query works, i tested it on your sample data

October 4th, 2004, 05:51 PM

shivanjali

Registered User

Join Date: Jun 2004

Posts: 11

Time spent in forums: < 1 sec
Reputation Power: 0

cols and tablename renamed

1.I was trying to now replace ticket_number, ticket_start_date, ticket_end_date, penalties with renames columns and tables:
production.lk_penalties.pen_sched_id, production.lk_penalties.range_begin, production.lk_penalties.range_end, production.lk_penalties respectively.
how could i rearrange the code?

2. I have another table which has 2 columns production.lk_pen_sched_id.pen_sched_id and production.lk_pen_sched_id.pen_sched_name in table called production.lk_pen_sched_id
I want to add production.lk_pen_sched_id.pen_sched_name in the query also joining 2 tables.
how will the final query be?

October 4th, 2004, 06:13 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,982

Time spent in forums: 1 Month 4 Weeks 18 h 20 m 26 sec
Reputation Power: 1028

Quote:

Originally Posted by shivanjali

Code:

select pen_sched_id
     , range_begin 
     , range_end     
  from production.lk_penalties as X
 where range_end =
     ( select max(range_end)
         from production.lk_penalties
        where pen_sched_id 
            = X.pen_sched_id )
   and not exists
     ( select 1
         from production.lk_penalties
        where pen_sched_id 
            = X.pen_sched_id
          and range_begin <= getdate()
          and range_end >= getdate()    )

Quote:

2. I have another table which has 2 columns production.lk_pen_sched_id.pen_sched_id and production.lk_pen_sched_id.pen_sched_name in table called production.lk_pen_sched_id
I want to add production.lk_pen_sched_id.pen_sched_name in the query also joining 2 tables.
how will the final query be?

sorry, i have no idea what you're asking here

Last edited by r937 : October 4th, 2004 at 06:15 PM.

October 5th, 2004, 03:31 PM

shivanjali

Registered User

Join Date: Jun 2004

Posts: 11

Time spent in forums: < 1 sec
Reputation Power: 0

what i meant to say is this...i try your query here , it doesn't work!
--------check out.
select production.lk_penalties.pen_sched_id,
production.lk_penalties.range_begin,
production.lk_penalties.range_end
from production.lk_penalties as X
where production.lk_penalties.range_end =
(select max(production.lk_penalties.range_end)
from production.lk_penalties
where production.lk_penalties.pen_sched_id = X.pen_sched_id)
and not exists
(select 1
from production.lk_penalties
where production.lk_penalties.pen_sched_id = X.pen_sched_id
and production.lk_penalties.range_begin <= getdate()
and production.lk_penalties.range_end >= getdate()
)

October 5th, 2004, 03:41 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,982

Time spent in forums: 1 Month 4 Weeks 18 h 20 m 26 sec
Reputation Power: 1028

try it without the database.tablename qualifiers the way i posted in post #6

October 5th, 2004, 04:43 PM

shivanjali

Registered User

Join Date: Jun 2004

Posts: 11

Time spent in forums: < 1 sec
Reputation Power: 0

it doesn't work either way. can you tell me again what this query does exactly.

#10

October 5th, 2004, 04:55 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 17,982

Time spent in forums: 1 Month 4 Weeks 18 h 20 m 26 sec
Reputation Power: 1028

exactly? what it does is select the latest ticket for every ticket number which does not have an open ticket

to satisfy yourself that it actually does work, run the following in Query Analyzer:

Code:

create table penalties
( ticket_number tinyint not null 
, ticket_start_date datetime
, ticket_end_date datetime
)

insert into penalties values (75, '2003-02-01', '2003-03-02')
insert into penalties values (75, '2003-02-04', '2003-05-04')
insert into penalties values (34, '2003-02-01', '2004-03-03')
insert into penalties values (34, '2004-03-04', '2005-03-03')

select ticket_number
     , ticket_start_date 
     , ticket_end_date     
  from penalties as X
 where ticket_end_date =
     ( select max(ticket_end_date)
         from penalties
        where ticket_number 
            = X.ticket_number )
 and not exists
     ( select 1
         from penalties
        where ticket_number 
            = X.ticket_number
          and ticket_start_date <= getdate()
          and ticket_end_date >= getdate()    )

Thread Tools	Search this Thread
Email this Page	Search this Thread: Advanced Search
Display Modes	Rate This Thread
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode	Rate This Thread: