Selection Query - 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

General SQL Server Forums

New to SQL Server Programming

Selection Query

Author

Topic

HaydenN
Starting Member

4 Posts

Posted - 2006-09-21 : 09:16:38

Hi All

Being new to this forum and to SQL, I would to apologise upfront for my clumsy code and explanations as I try and get to grips with this subject.

I have created this query which does work:

SELECT T1.U_Serial,
case
when (U_RDate < GETDATE() AND U_ASDate Is NULL)
then 'NA'
when (U_RDate Is NULL)
then 'No Request'
when (U_ASDate Is Not NULL And U_AEDate Is NULL)
then 'NA'
when (U_ASDate Is Not NULL And U_AEDate Is Not NULL)
then 'A'
when (U_RDate > GETDATE() AND U_ASDate Is NULL)
then 'NA'
else 'DN'
end As Available,
case
when (U_RDate < GETDATE() AND U_ASDate Is NULL)
then 'Late'
when (U_RDate Is NULL)
then 'No Request'
when (U_ASDate Is Not NULL And U_AEDate Is NULL)
then 'Busy'
when (U_ASDate Is Not NULL And U_AEDate Is Not NULL)
then 'Done'
when (U_RDate > GETDATE() AND U_ASDate Is NULL)
then 'Waiting'
else 'DN'
end As Progress,

T1.U_Status,T1.U_JobTp, T1.U_JobNr,T1.Code, T0.U_CardNM, T0.U_ZpCd, GETDATE() as Today, T1.U_RDate, T1.U_ASDate, T1.U_AEDate, T1.U_ItemCd, T1.U_ItemDsc

FROM [dbo].[@IDH_JOBENTR] T0 , [dbo].[@IDH_JOBSHD] T1

WHERE T0.Code = T1.U_JobNr AND T1.U_Serial not like ''

ORDER BY T1.U_Serial, T1.U_AEDate DESC, T1.U_JobNr DESC ,T1.Code DESC,Today, T1.U_RDate, Available, Progress, T1.U_Status,T1.U_JobTp,T0.U_CardNM, T0.U_ZpCd, T1.U_ASDate, T1.U_ItemCd, T1.U_ItemDsc

The tables I’m using are IDH_JOBENTR which is the header records for my jobs and IDH_JOBSHD, which holds the row details for the jobs.

I’m trying to extract from the IDH_JOBSHD table the last (row) detail record for the (header) job.

The way the query works now, the first row for each unique serial number is the record I need, but this is all the info I would like to display I don’t need the other lines, and I don't know how to strip out the rest of the data

I hope this make sense.

Kind regards
Hayden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-21 : 09:53:17

[code]SELECT T1.U_Serial,
case
when U_RDate < GETDATE() AND U_ASDate Is NULL then 'NA'
when U_RDate Is NULL then 'No Request'
when U_ASDate Is Not NULL And U_AEDate Is NULL then 'NA'
when U_ASDate Is Not NULL And U_AEDate Is Not NULL then 'A'
when U_RDate > GETDATE() AND U_ASDate Is NULL then 'NA'
else 'DN'
end As Available,
case
when U_RDate < GETDATE() AND U_ASDate Is NULL then 'Late'
when U_RDate Is NULL then 'No Request'
when U_ASDate Is Not NULL And U_AEDate Is NULL then 'Busy'
when U_ASDate Is Not NULL And U_AEDate Is Not NULL then 'Done'
when U_RDate > GETDATE() AND U_ASDate Is NULL then 'Waiting'
else 'DN'
end As Progress,
T1.U_Status,
T1.U_JobTp,
T1.U_JobNr,
T1.Code,
T0.U_CardNM,
T0.U_ZpCd,
GETDATE() as Today,
T1.U_RDate,
T1.U_ASDate,
T1.U_AEDate,
T1.U_ItemCd,
T1.U_ItemDsc
FROM [dbo].[@IDH_JOBENTR] T0
INNER JOIN [dbo].[@IDH_JOBSHD] T1 ON T1.U_JobNr = T0.Code
WHERE T1.U_Serial <> ''
ORDER BY T1.U_Serial,
T1.U_AEDate DESC,
T1.U_JobNr DESC,
T1.Code DESC,
10,
T1.U_RDate,
2,
3,
T1.U_Status,
T1.U_JobTp,
T0.U_CardNM,
T0.U_ZpCd,
T1.U_ASDate,
T1.U_ItemCd,
T1.U_ItemDsc[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-21 : 09:55:51

Why don't you post us same sample data? Table layouts? And the expected output from the provied sample data?

Help us help you.

Peter Larsson
Helsingborg, Sweden

HaydenN
Starting Member

4 Posts

Posted - 2006-09-21 : 10:41:37

Hi Peter, thanks for the prompt reply

Here is the current result of the query:

What I would like to extract from the data is records like the following for example (ie: for each unique serial number where the 'code' is effectively the biggest number:

Hope this helps

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-21 : 10:54:39

We can't see locally stored images...

Peter Larsson
Helsingborg, Sweden

HaydenN
Starting Member

4 Posts

Posted - 2006-09-22 : 06:40:13

Hi Peter, sorry hopefully this is better now

Here is the current result of the query:

#  U_Serial  Available Progress	U_Status U_JobTp	U_JobNr	Code U_CardNM			U_ZpCd	 Today	   U_RDate  U_ASDate U_AEDate	U_ItemCd U_ItemDsc
1         4	NA	Busy	Open	 Delivery	63	84   Mr John Smith		SE6 4JU	 22.09.06  01.06.06 02.06.06		SKI008	 Skip - 8 Yard
2  11119999	A	Done	Ordered	 Delivery	50	75   London Borough of Lewisham	SE6 4RU	 22.09.06  01.03.06 03.03.06 04.03.06	ROH030	 Rolonof - 30 Yard
3  1212121212	A	Done	Ordered	 Delivery	22	39   Mr Evan Green		SE13 7TG 22.09.06  01.03.06 01.03.06 05.03.06	SKI006	 Skip - 6 Yard
4  123456777	NA	Late	Created	 Collection	24	48   ASDA Main Office		E14 3BT	 22.09.06  10.03.06		        COH014	 Compactor - 14 Yard
5  123456777	NA	Late	Created	 Empty		24	46   ASDA Main Office		E14 3BT	 22.09.06  07.03.06			COH014	 Compactor - 14 Yard
6  123456777	NA	Late	Created	 Delivery	24	45   ASDA Main Office		E14 3BT	 22.09.06  05.03.06			COH014	 Compactor - 14 Yard
7  123456788	A	Done	Ordered	 Delivery	48	72   Mr G Peace			SE13	 22.09.06  06.03.06 06.03.06 15.03.06	SKI006	 Skip - 6 Yard
8  123456788	A	Done	Ordered	 Delivery	10	24   Tesco Head Office Ltd	SE13	 22.09.06  02.03.06 05.03.06 05.03.06	SKI008	 Skip - 8 Yard
9  123456788	NA	Late	Open	 Exchange	53	78   Tesco Head Office Ltd	SE7	 22.09.06  08.03.06			SKI006	 Skip - 6 Yard
10 123456788	NA	Late	Open	 Collection	48	74   Mr G Peace			SE13	 22.09.06  20.03.06			SKI006	 Skip - 6 Yard
11 123456788	NA	Late	Open	 Exchange	48	73   Mr G Peace			SE13	 22.09.06  10.03.06			SKI006	 Skip - 6 Yard
12 123456788	NA	Busy		 Delivery	4	1    Mr Evan Green		SE13 7TG 22.09.06  24.02.06 03.03.06		SKI006	 Skip - 6 Yard
13 123456799	A	Done	Ordered	 Delivery	57	81   Mr G Peace			SE3	 22.09.06  16.05.06 16.05.06 24.05.06	SKI006	 Skip - 6 Yard
14 123456799	NA	Late	Ordered	 Delivery	53	77   Tesco Head Office Ltd	SE7	 22.09.06  07.03.06			SKI006	 Skip - 6 Yard
15 123456799	NA	Late	Created	 Collection	10	26   Tesco Head Office Ltd	SE13	 22.09.06  05.03.06			SKI008	 Skip - 8 Yard
16 123456799	NA	Late	Created	 Exchange	10	25   Tesco Head Office Ltd	SE13	 22.09.06  04.03.06			SKI008	 Skip - 8 Yard

What I would like to extract from the data is records like the following for example (ie: for each unique serial number where the 'code' is effectively the biggest number:

#  U_Serial  Available Progress	U_Status U_JobTp	U_JobNr	Code U_CardNM			U_ZpCd	 Today	   U_RDate  U_ASDate U_AEDate	U_ItemCd U_ItemDsc
1         4	NA	Busy	Open	 Delivery	63	84   Mr John Smith		SE6 4JU	 22.09.06  01.06.06 02.06.06		SKI008	 Skip - 8 Yard
2  11119999	A	Done	Ordered	 Delivery	50	75   London Borough of Lewisham	SE6 4RU	 22.09.06  01.03.06 03.03.06 04.03.06	ROH030	 Rolonof - 30 Yard3  1212121212	A	Done	Ordered	 Delivery	22	39   Mr Evan Green		SE13 7TG 22.09.06  01.03.06 01.03.06 05.03.06	SKI006	 Skip - 6 Yard
4  123456777	NA	Late	Created	 Collection	24	48   ASDA Main Office		E14 3BT	 22.09.06  10.03.06		        COH014	 Compactor - 14 Yard5  123456777	NA	Late	Created	 Empty		24	46   ASDA Main Office		E14 3BT	 22.09.06  07.03.06			COH014	 Compactor - 14 Yard
6  123456777	NA	Late	Created	 Delivery	24	45   ASDA Main Office		E14 3BT	 22.09.06  05.03.06			COH014	 Compactor - 14 Yard
7  123456788	A	Done	Ordered	 Delivery	48	72   Mr G Peace			SE13	 22.09.06  06.03.06 06.03.06 15.03.06	SKI006	 Skip - 6 Yard8  123456788	A	Done	Ordered	 Delivery	10	24   Tesco Head Office Ltd	SE13	 22.09.06  02.03.06 05.03.06 05.03.06	SKI008	 Skip - 8 Yard
9  123456788	NA	Late	Open	 Exchange	53	78   Tesco Head Office Ltd	SE7	 22.09.06  08.03.06			SKI006	 Skip - 6 Yard
10 123456788	NA	Late	Open	 Collection	48	74   Mr G Peace			SE13	 22.09.06  20.03.06			SKI006	 Skip - 6 Yard
11 123456788	NA	Late	Open	 Exchange	48	73   Mr G Peace			SE13	 22.09.06  10.03.06			SKI006	 Skip - 6 Yard
12 123456788	NA	Busy		 Delivery	4	1    Mr Evan Green		SE13 7TG 22.09.06  24.02.06 03.03.06		SKI006	 Skip - 6 Yard
13 123456799	A	Done	Ordered	 Delivery	57	81   Mr G Peace			SE3	 22.09.06  16.05.06 16.05.06 24.05.06	SKI006	 Skip - 6 Yard
14 123456799	NA	Late	Ordered	 Delivery	53	77   Tesco Head Office Ltd	SE7	 22.09.06  07.03.06			SKI006	 Skip - 6 Yard
15 123456799	NA	Late	Created	 Collection	10	26   Tesco Head Office Ltd	SE13	 22.09.06  05.03.06			SKI008	 Skip - 8 Yard
16 123456799	NA	Late	Created	 Exchange	10	25   Tesco Head Office Ltd	SE13	 22.09.06  04.03.06			SKI008	 Skip - 8 Yard

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-22 : 07:31:06

select t1.*
from mytable t1
inner join (select u_serial, max(code) mc from mytable group by u_serial) t2 on t2.u_serial = t1.u_serial and t2.mc = t1.code

Peter Larsson
Helsingborg, Sweden

HaydenN
Starting Member

4 Posts

Posted - 2006-09-26 : 02:36:03

Thanks very much for your help, Peter

Regards
Hayden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources