Nested Query

Hi All,

I am stuck to a senario in which i need the help of u all.

well the present senaio is i have two table xx and yy.

the table conatins data as below:

table xx

EmployeeID Departments

10014 A

10015 A

10002 A

10013 B

10019 B

10056 B

table yy

Empoyee ID ActuaDate Start time EndTime

10014 03/30/2007 0600 1445

10015 03/30/2007 0600 1445

10002 03/30/2007 0600 1445

10013 03/31/2007 1130 1300

10019 03/31/2007 0300 1300

10056 03/31/2007 0300 1100

--

conditions :

Actdate ='03/30/2007' and starttime>='600'

or actdate ='03/31/2007' and starttime<='0300' -for pick

Result should be as:

Department EmployeeCount Pick Drop

A 3 3 3

B 3 3 3

please help me in solving the problem.

Regards

sufian

Your requirements are a bit 'unclear'. Perhaps this is in the right direction:

Code Snippet

SET NOCOUNT ON

DECLARE @.xx table
( EmployeeID int,
Department char(1)
)

INSERT INTO @.xx VALUES ( 10014, 'A' )
INSERT INTO @.xx VALUES ( 10015, 'A' )
INSERT INTO @.xx VALUES ( 10002, 'A' )
INSERT INTO @.xx VALUES ( 10013, 'B' )
INSERT INTO @.xx VALUES ( 10019, 'B' )
INSERT INTO @.xx VALUES ( 10056, 'B' )

DECLARE @.yy table
( EmployeeID int,
ActuaDate datetime,
StartTime int,
EndTime int
)

INSERT INTO @.yy VALUES ( 10014, '03/30/2007', 0600, 1445 )
INSERT INTO @.yy VALUES ( 10015, '03/30/2007', 0600, 1445 )
INSERT INTO @.yy VALUES ( 10002, '03/30/2007', 0600, 1445 )
INSERT INTO @.yy VALUES ( 10013, '03/31/2007', 1130, 1300 )
INSERT INTO @.yy VALUES ( 10019, '03/31/2007', 0300, 1300 )
INSERT INTO @.yy VALUES ( 10056, '03/31/2007', 0300, 1100 )
INSERT INTO @.yy VALUES ( 10013, '03/31/2007', NULL, NULL )
INSERT INTO @.yy VALUES ( 10056, '03/31/2007', 0300, NULL )

SELECT
x.Department,
EmployeeCount = count( DISTINCT y.EmployeeID ),
Picked = count( y.StartTime ),
Dropped = count( y.EndTime )
FROM @.XX x
JOIN @.YY y
ON x.EmployeeID = y.EmployeeID
GROUP BY x.Department

Department EmployeeCount Picked Dropped
- - -- --
A 3 3 3
B 3 4 3

|||

While I can't completely follow your requirements, I think this will give you the idea for what you want. The idea is to do a select with a group by and manufacture values using SUM

select department, count(distinct xx.employeeId) as EmployeeCount,
sum(case when actuadate ='03/31/2007' and starttime<='0300' then 1 else 0 end) as Pick
from xx
join yy
on xx.employeeId = yy.employeeId
group by department

The full code follows, and if you could provide this kind of structure (and change mine if it is wrong) it is a lot easier. Plus, if your results are what you expected to receive, it seems wrong. If it is just a basice feel for the results, I understand.

I would also consider making the start and end values a datetime. Use a constraint to make sure they are in the same day perhaps, but it will be easier to work with with date and time in the same column.

Code Snippet

create table xx
(
EmployeeId int primary key,
Department char(1)
)
insert into xx
select 10014,'A'
union all
select 10015,'A'
union all
select 10002,'A'
union all
select 10013,'B'
union all
select 10019,'B'
union all
select 10056,'B'

create table yy
(
employeeId int,
actuaDate datetime,
startTime int,
endTime int
)
insert into yy
select 10014,'03/30/2007',0600,1445
union all
select 10015,'03/30/2007',0600,1445
union all
select 10002,'03/30/2007',0600,1445
union all
select 10013,'03/31/2007',1130,1300
union all
select 10019,'03/31/2007',0300,1300
union all
select 10056,'03/31/2007',0300,1100
go

select department, count(distinct xx.employeeId) as EmployeeCount,
sum(case when actuadate ='03/31/2007' and starttime<='0300' then 1 else 0 end) as Pick
from xx
join yy
on xx.employeeId = yy.employeeId
group by department

Nested queries difficulty

Hi!
I'm a SQL beginner struggling with some SQL textbook examples, and i'm stuck at one exercise. :eek:
Can anyone give me a hint??

Ok, here it goes:
I'm trying to simulate a airport booking system, where CUSTOMER makes a RESERVATION to a FLIGHT. The FLIGHT have a ROUTE, a AIRCRAFT, and some CABINSTAFF. Plus some other minor attribures..

The question i'm trying to answer now is "how many seats are there left on all flights having a specific route?". I can get how many seats the airplanes have with this:

SELECt SEATS
FROM FLIGHT FL, AIRCRAFT AC, ROUTE R
WHERE FL.FNUMBER = R.FLIGHTNUMBER
AND FL.AIRCRAFT = AC.NAME
AND DEPARTURECITY = 'Paris' AND ARRIVALCITY = 'London'
AND "DATE" = '2005-03-01';

It will return:
100
200
(there are two flights that match the selected route and date, one airplane have 100 seats, the other one 200).

I then find out how many customers that are booked on those flights:

SELECT COUNT(RESERVATION_NO)
FROM FLIGHT F, AIRCRAFT A, ROUTE R, RESERVATION RN, RESERVES RS
WHERE RN.RESERVATION_NO = RS.RESERVATION_NO
AND RN.FNUMBER = R.FLIGHTNUMBER
AND F.FNUMBER = RN.FNUMBER
AND F.AIRCRAFT = A.NAME
AND DEPARTURECITY = 'Paris' AND ARRIVALCITY = 'London'
AND FDATE = '2005-03-01'
GROUP BY RN.RESERVATION_NO

It will correctly return:
1
2

Now i want to substract the second query from the first to get the number of seats left in those flights:

SELECT FLIGHTNUMBER, SEATS - (SELECT COUNT(RESERVATION_NO)
FROM FLIGHT F, AIRCRAFT A, ROUTE R, RESERVATION RN, RESERVES RS
WHERE RN.RESERVATION_NO = RS.RESERVATION_NO
AND RN.FNUMBER = R.FLIGHTNUMBER
AND F.FNUMBER = RN.FNUMBER
AND F.AIRCRAFT = A.NAME
AND DEPARTURECITY = 'Paris' AND ARRIVALCITY = 'London'
AND FDATE = '2005-03-01'
GROUP BY RN.RESERVATION_NO)
FROM FLIGHT FL, AIRCRAFT AC, ROUTE R
WHERE FL.FNUMBER = R.FLIGHTNUMBER
AND FL.AIRCRAFT = AC.NAME
AND DEPARTURECITY = 'Paris' AND ARRIVALCITY = 'London'
AND "DATE" = '2005-03-01';

But that does'nt work since you can't substract a set, only a single row, right?? So how do i fix this??
The answer should be:
219
98

Thanks!Without getting into the details of your query, you need to correlate the subquery to the main query something like this:

SELECT FL.FLIGHTNUMBER, SEATS - (SELECT COUNT(RESERVATION_NO)
FROM FLIGHT F, ...
WHERE ...
AND F.FLIGHTNUMBER = FL.FLIGHTNUMBER)
FROM FLIGHT FL, ...;

need to write complex query without cursor

Hi all,
I am trying to do the following and am getting stuck.Your help wil be higly
appreciated.
I am matching two tables.If i get a single matching row then get that single
row
but if i get >1 matching rows i should select only the one with the latest
date.
for example
Table1
--
id value
1 10
2 20
3 30
Table 2
--
id param updateDate
1 10 day1
1 20 day2
1 25 day3
2 20 day2
2 40 day 3
3 30 day 2
(day1 <day2<day3 etc)
So i should get results as
Result
--
1 25 day3
2 40 day3
3 30 day2
how can I do this without a cursor?
Thanks for your help.on SQL 2005,
select id, param, updateDate
from table2
where row_number() over(partition by id order by updateDate desc) = 1
*untested*|||Try this, if you don't have SQL Server 2005:
select Table2.id, Table2.param, Table2.updateDate
from Table2
join Table1 on Table1.id = Table2.id
where updateDate = (
select max(updateDate) from Table2 as T_copy
where T_copy = Table2.id
)
or
select id, param, updateDate
from Table2
where updateDate = (
select max(updateDate) from Table2 as T_copy
where T_copy = Table2.id
) and exists (
select * from Table1
where Table1.id = Table2.id
)
This can yield multiple rows per id if (id, updateDate) is not unique
and there are two or more updates for an id on most recent update date.
For SQL Server 2005, Alexander has the right idea, but windowed
functions cannot appear in the WHERE clause, and you'll have to do this:
select id, param, updateDate
from (
select
id, param, updateDate,
row_number() over (partition by id order by updateDate desc) as rn
from Table2
) as T
where rn = 1
Steve Kass
Drew University
tech77 wrote:

>Hi all,
>I am trying to do the following and am getting stuck.Your help wil be higly
>appreciated.
>I am matching two tables.If i get a single matching row then get that singl
e
>row
> but if i get >1 matching rows i should select only the one with the latest
>date.
>for example
>Table1
>--
>id value
>1 10
>2 20
>3 30
>Table 2
>--
>id param updateDate
>1 10 day1
>1 20 day2
>1 25 day3
>2 20 day2
>2 40 day 3
>3 30 day 2
>(day1 <day2<day3 etc)
>So i should get results as
>Result
>--
>1 25 day3
>2 40 day3
>3 30 day2
>how can I do this without a cursor?
>Thanks for your help.
>|||Here's one method, assuming that each id will only have one row per
day. If you have multiple rows per id per day, you have to have some
rule to determine which row you want returned.
Stu
DECLARE @.Table1 TABLE (id int, value int)
INSERT INTO @.TABLE1
SELECT 1, 10
UNION ALL
SELECT 2, 20
UNION ALL
SELECT 3, 30
DECLARE @.Table2 TABLE (id int, param int, UpdateDate smalldatetime)
INSERT INTO @.TABLE2
SELECT 1, 10, '20060301'
UNION ALL
SELECT 1, 20, '20060302'
UNION ALL
SELECT 1, 25, '20060303'
UNION ALL
SELECT 2, 20, '20060302'
UNION ALL
SELECT 2, 40, '20060303'
UNION ALL
SELECT 3, 30, '20060302'
SELECT t2.id, t2.param, t2.UpdateDate
FROM @.table1 t1 JOIN @.Table2 t2 ON t1.id=t2.id
JOIN (SELECT id, MAX(updateDate) AS upDateDate
FROM @.Table2 t2
GROUP BY id) t2_2 ON t2.ID = t2_2.ID
AND t2.UpDateDate = t2_2.UpdateDate