Select Oldest Record

[Fletch12]

I only need to select the oldest of the 2 records in bold, based on the date. The dates and cells change so I can't "hardcode" those fields in the query. I need to come up with some group by clause I think and select the oldest one based on that date.

[U]Cell      Shift        Date           Units   Pct   [/U]
123-AB	1st Shift	2006-05-03     2752    128.71
123-AB	2nd Shift	2006-05-03 	2328	108.88
[b]123-AB	3rd Shift	2006-05-03 	2121	106.58
123-AB	3rd Shift	2006-05-04 	1809	121.21[/b]
456-AB	1st Shift	2006-05-03 	2328	108.88
456-AB	2nd Shift	2006-05-03 	2121	106.58
456-AB	3rd Shift	2006-05-04 	1809	121.21

The result of the query should be:

[U]Cell      Shift        Date           Units   Pct   [/U]
123-AB	1st Shift	2006-05-03     2752    128.71
123-AB	2nd Shift	2006-05-03 	2328	108.88
123-AB	3rd Shift	2006-05-03 	2121	106.58
456-AB	1st Shift	2006-05-03 	2328	108.88
456-AB	2nd Shift	2006-05-03 	2121	106.58
456-AB	3rd Shift	2006-05-04 	1809	121.21

 

[vongrunt]

And problem is... because 3rd (night?) shift spans across two dates?

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]

 

[bborissov]

Create table #Temp (Cell char(6), Shift varchar(20), Date datetime, Units int, Pct int)
INSERT INTO #temp VALUES('123-AB','1st Shift','2006-05-03',2752,128.71)
INSERT INTO #temp VALUES('123-AB','2nd Shift','2006-05-03',2328,108.88)
INSERT INTO #temp VALUES('123-AB','3rd Shift','2006-05-03',2121,106.58)
INSERT INTO #temp VALUES('123-AB','3rd Shift','2006-05-04',1809,121.21)
INSERT INTO #temp VALUES('456-AB','1st Shift','2006-05-03',2328,108.88)
INSERT INTO #temp VALUES('456-AB','2nd Shift','2006-05-03',2121,106.58)
INSERT INTO #temp VALUES('456-AB','3rd Shift','2006-05-04',1809,121.21)

SELECT #Temp.Cell, #Temp.Shift, #Temp.Date , #Temp.Units , #Temp.Pct
FROM #Temp
INNER JOIN (SELECT Cell, MIN(Date) AS Date FROM #Temp GROUP BY Cell) Tbl1
ON #Temp.Cell = Tbl1.Cell AND #Temp.Date = Tbl1.Date
drop table #temp

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[Fletch12]

vongrunt,

That's correct...

bborissov,

I may be misunderstanding what you're trying to do with the temp table, but my values in these fields will be changing all the time. Should I still try this?

 

[bborissov]

Yes, You could change any values of this tabl. I just add your data into #Temp just o be sure I get the correct result.
No metter what values you have in Cell and Date you always cill get these records whith smallest Date for Cell.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[Fletch12]

I'm still not quite following you. Assuming all I'm using is Query Analyzer to build this query, are you saying I'll need to key in the values for this temp table each time?

 

[bborissov]

NO,
No need of Tem table at all, But I didn't have your table
This is just an example how to build this query. Assuming all your data is stored in table with name ShiftTableData then the query must looks like this:

SELECT ShiftTableData.Cell,
       ShiftTableData.Shift,
       ShiftTableData.Date ,
       ShiftTableData.Units,
       ShiftTableData.Pct
FROM ShiftTableData
INNER JOIN
(SELECT Cell, MIN(Date) AS Date
        FROM ShiftTableData GROUP BY Cell) Tbl1
ON ShiftTableData.Cell = Tbl1.Cell AND
   ShiftTableData.Date = Tbl1.Date

Just replace ShiftTableData with actual table name.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[SQLBill]

Fletch,

When a responder includes the INSERT INTO lines and uses temp tables, that is just to show you how they tested their query. We don't have your table set up or your data, so we take the information you provide, create a temp table (or two) and insert your data into the tables. Then we test our query against it. All you need to be concerned with is the actual query itself. However, if it doesn't work for you...the temp table set up lets you double check that we are testing the correct data as you can see what datatypes, lengths, etc that we used.

-SQLBill

Posting advice: FAQ481-4875

 

[Fletch12]

Okay, that works great. Sorry for the misunderstanding there with the temp tables. Thanks for the help.

 

[bborissov]

You are welcome, One other thing If you have TWO records for one Cell and the Bothe dates there are identical you will get two records in result set. I mean if you have data like this:

Cell      Shift        Date           Units   Pct   
123-AB    1st Shift    2006-05-03     2752    128.71
123-AB    2nd Shift    2006-05-03     2328    108.88
123-AB    3rd Shift    2006-05-03     2121    106.58
123-AB    3rd Shift    2006-05-03     1809    121.21
456-AB    1st Shift    2006-05-03     2328    108.88
456-AB    2nd Shift    2006-05-03     2121    106.58
456-AB    3rd Shift    2006-05-04     1809    121.21

You will get the following:

Cell      Shift        Date           Units   Pct   
123-AB    1st Shift    2006-05-03     2752    128.71
123-AB    2nd Shift    2006-05-03     2328    108.88
123-AB    3rd Shift    2006-05-03     2121    106.58
123-AB    3rd Shift    2006-05-03     1809    121.06
456-AB    1st Shift    2006-05-03     2328    108.88
456-AB    2nd Shift    2006-05-03     2121    106.58
456-AB    3rd Shift    2006-05-04     1809    121.21

Also add Shift to derived table and Join condition to be sure you didn't miss some shifts:

SELECT ShiftTableData.Cell,
       ShiftTableData.Shift,
       ShiftTableData.Date ,
       ShiftTableData.Units,
       ShiftTableData.Pct
FROM ShiftTableData
INNER JOIN
(SELECT Cell, Shift, MIN(Date) AS Date
        FROM ShiftTableData GROUP BY Cell, Shift) Tbl1
ON ShiftTableData.Cell  = Tbl1.Cell AND
   ShiftTableData.Date  = Tbl1.Date AND
   ShiftTableData.Shift = Tbl1.Shift

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.