[RESOLVED] Eliminating multiple entrances in SQL query

[josephine]

ello all

in a table i have multiple entrances.
can i, via the SQL statement, make sure i have just 1 entry per B.serial?
and that the above entry will have the latest B.date?
SQL statement now:

"select B.serial, B.version, CONVERT(char(20), B.date,113) 'Date Started', C.name 'Status' from BLCodes C, Bootloader B WHERE B.status = C.code"

tnx in advance for help on this.


regards,

[szlamany]

SQL has aggregate functions - they work in unison with the GROUP BY clause.

SELECT B.serial, B.version, CONVERT(char(20), MAX(B.date),113) 'Latest Date Started'
FROM ...
GROUP BY B.Serial, B.Version

Issue is that everything NOT in an AGGREGATE function must be in the GROUP BY clause - so you will have issues getting the STATUS. If you want the status of the latest entry then that's a different query all together...

[josephine]

1st, tnx 4 a quick reply szalmany. howzit..
unfortunately i get an error as i run the script.

not sure i fully get the issue of
"everything NOT in an AGGREGATE function must be in the GROUP BY clause "

[szlamany]

Yes - that's exactly the error I was talking about in my post.

You either put a field in an AGGREGATE function - such as MAX(), MIN() or SUM()...

or you put it in the GROUP BY clause.

You will need to give a more clear example of your data and what you are trying to accomplish before we can give specific help.

[josephine]

You will need to give a more clear example of your data and what you are trying to accomplish before we can give specific help.

enviorment is MS SQL;

2 tables:

B=> B.id,B.Serial, b.version, B.state, B.date
C=>C.codeId, C.name, c.code

B.state is related to C.code. and shows in it value of C.name

at present my result is a table with headers:
Serial; Version; Date; Status;
table has multiple entrances (on same serial) for status "started" "finished".

need a query to display one row per serial,
in it:

the latest date entry for "Started" (i.e., ignore earlier dates).
the latest date entry for "Finished"
the status of the above entry (started/finished)

hope its clearer now, if any info missin, pls advise..

tnx a lot 4 the help.
regards,

[szlamany]

So that seems more to me like you want to "select" only a specific row for a serial no - and that row happens to have the "latest" date for that serial number.

Also note that I've put a more conventional FROM/JOIN clause on that SELECT. Since the "primary" table you are attacking is the Bootloader - it should be the FROM table. JOIN to it any "related" tables - such as the description from the BLCodes table.

select B.serial
	, B.version
	, CONVERT(char(20), B.date,113) 'Date Started'
	, C.name 'Status'
	from Bootloader B
	Left join BLCodes C on C.Code=B.Status
	Where B.Date=(Select Max(B1.Date) From Bootloader B1
			Where B1.Serial=B.Serial)

So the sub-query "brings up" the latest date for a serial number and the WHERE clause only selects that row. Does version or state play into this? If so add them to the WHERE clause of the sub-query.

[josephine]

So the sub-query "brings up" the latest date for a serial number and the WHERE clause only selects that row. Does version or state play into this? If so add them to the WHERE clause of the sub-query.

state does play into it, and i added it as you suggested.
its works perfectly!!!

tnx so much again for your help szlamany!! i appreciate it, (as rated).

[szlamany]

You are very welcome! Glad to help

[josephine]

Since the "primary" table you are attacking is the Bootloader - it should be the FROM table. JOIN to it any "related" tables - such as the description from the BLCodes table.

sorry to re open this.. but one more question if i may please, as i'm very confused by it.

in "primary" bootloader table entries are added once with B.status=0 (start)
and the second time with b.status=1 (finish). (multiple entries for same serial)

with your help i managed to "ignore" the entries with older dates, and just check the latest date and its status.

now client wants to display date started and date finished, for every Bootload;

i.e.,
if latest date has status code 0 (started), file is in progress. and there is no date finished.
if latest date has status code 1 (finished), bootload proccess is done, and i must show : date started (one date smaller than MAX(date))
and date finished MAX(date)


hope this is clear... as its not so clear for me.

[szlamany]

Are there only ever 2 rows for a serial number?

How does VERSION and STATE play into this?

Showing me some sample data would be really helpful in making this a one or two post answer

[josephine]

Are there only ever 2 rows for a serial number?
How does VERSION and STATE play into this?
Showing me some sample data would be really helpful in making this a one or two post answer

as requested, attached are data samples.

"Source Table" uses the following SQL query:

select B.serial, B.version, CONVERT(char(20), B.date,113) 'Date', C.name 'Status' from BLCodes C, Bootloader B WHERE B.status = C.code

as you can see, there are multiple entries for each serial.
an entry is created whenever a Serial reports either, "Start (value=in progress)" or "Finish (value=finished)".

required entries from Source Table are:

if Serial reported "Finish" then show date in 'date finished' and show one date prior to latest date is 'Date Started'.
if Serial reported "Started" and did not report "Finish", i.e., it is "In Progress" then show date in 'date started' and status = "in progress" no 'date finished'

format of data presentation should be somethin like "Desired Table", i.e., one row per Serial.

pls lemme know if any other info is needed...
and again, much tnx for your help, i really appreciate it.

regards,

[szlamany]

CH0000000129 has a IN PROGRESS, FINISHED and another IN PROGRESS...

That's possible?

But you only want to show the CH0000000129 row once - with the latest IN PROGRESS and the potential FINISHED that follows that - right?

[josephine]

absolutely!
look in current table att. that is you suggestion of yesterday.

[szlamany]

Ok - so this modification to yesterdays query will only select the LATEST IN-PROGRESS entry - right?

select B.serial
	, B.version
	, CONVERT(char(20), B.date,113) 'Date Started'
	, C.name 'Status'
	from Bootloader B
	Left join BLCodes C on C.Code=B.Status
	Where B.Date=(Select Max(B1.Date) From Bootloader B1
			Where B1.Serial=B.Serial and B1.Status='In Progress')

Then this JOIN should add to that the possible FINISHED entry that might follow it.

select B.serial
	, B.version
	, CONVERT(char(20), B.date,113) 'Date Started'
	, CONVERT(char(20), BF.date,113) 'Date Finished'
	, C.name 'Status'
	from Bootloader B
	Left join BLCodes C on C.Code=B.Status
	Left Join Bootloader BF on BF.Serial=B.Serial and BF.Date>=B.Date
					and BF.Status='Finished'
	Where B.Date=(Select Max(B1.Date) From Bootloader B1
			Where B1.Serial=B.Serial and B1.Status='In Progress')

If you need to add version to these joins make sure to put them in the WHERE Sub-query and the new BF JOIN.

If you want to process one date or the other do this:

Case When BF.Serial is null Then B.Date Else BF.Date End

This checks if the BF row exists and optionally returns either the B.Date or BF.Date...

[josephine]

---

wow!
took me 10 minutes just to read the statemnet. and im very happy to say i understand it!
have changed only 2 things:
the text 'in progress' to a numeric code 0.
the text 'finished' to numeric code 1.
statement now is

select B.serial, B.version, CONVERT(char(20), B.date,113) 'Date Started', CONVERT(char(20), BF.date,113) 'Date Finished', C.name 'Status' from Bootloader B Left join BLCodes C on C.Code=B.Status Left Join Bootloader BF on BF.Serial=B.Serial and BF.Date>=B.Date and BF.Status=1 Where B.Date=(Select Max(B1.Date) From Bootloader B1 Where B1.Serial=B.Serial and B1.Status=0)

Please check result of statement is in attachment below; (last.jpg)

only problem is they all say "IN PROGRESS" when only CH1000000129 is actually in progress. (it has no "Date Finish"....)
if can work around it=> PERFECT!
if not, => PERFECT!

Sir, you are a star!!!

[szlamany]

I'm glad it's working for you!

This should do the last step you need:

select B.serial
	, B.version
	, CONVERT(char(20), B.date,113) 'Date Started'
	, CONVERT(char(20), BF.date,113) 'Date Finished'
	, Isnull(CF.name,C.Name) 'Status'
	from Bootloader B
	Left join BLCodes C on C.Code=B.Status
	Left Join Bootloader BF on BF.Serial=B.Serial 
		and BF.Date>=B.Date and BF.Status=1
	Left join BLCodes CF on C.Code=BF.Status
	Where B.Date=(Select Max(B1.Date) 
			From Bootloader B1
			Where B1.Serial=B.Serial and B1.Status=0)

by JOIN'ing the Code table on the BF code you get that data only if the serial # is completed - and ISNULL() will return the proper one.

You could also use the CASE...IS NULL... type statement I posted back a bit - but ISNULL() function should do it.

I'm off to an all afternoon meeting - I'll check back later.

[szlamany]

I edited that last post - the JOIN was in a bad spot - hope you see the correct version!

[josephine]

hope you have a good meeting,
my ems says:
"The column prefix 'BF' does not match with a table name or alias name used in the query."
i must leave too.
have a good evening.see ya 2moro.

[szlamany]

That should be fixed by the EDIT I made - the position of that JOIN was important - it needed to be after the BF join.

[josephine]

Thread Epilogue.

much thanks for all your help on this... esp the code comments, through which i learned a lot.

(best result i got is captured in post #15.)

happy friday!

regards,