Maximum date with a twist

Please help.
I have this query which works fine to get the maximum create date between 6
tables:
create table #temp1 (create_date datetime,call_stat char(30))
insert into #temp1 (create_date,call_stat)
(SELECT create_dt,'Disposition' as call_stat FROM tblotherdisposition where
scp_seqno = @.scpseqno
union all
SELECT create_dt,'Pending' as call_stat FROM Tblpendings where scp_seqno =
@.scpseqno
union all
SELECT create_dt,'Referred to CompanyA' as call_stat FROM
tblreferredtoCompanyA where scp_seqno = @.scpseqno
union all
SELECT create_dt,'Referred to CompanyB' as call_stat FROM
TblReferredToCompanyB where scp_seqno = @.scpseqno
union all
SELECT create_dt,'Appointment' as call_stat FROM t_appointment where
scp_seqno = @.scpseqno
union all
SELECT create_dt,'Unknown' as call_stat FROM tblsurvey where scp_seqno =
@.scpseqno)
select call_stat as status from #temp1
where create_date = (select max(create_date) from #temp1)
**Recent testing shows that the Appointment create date is always less than
the
create date of tblsurvey by a few minutes. When this happens, I still want
call status = Appointment rather than "Unknown". How can I change my query
to reflect this discovery.
TIA,
--
LynPlease post table structures with sample data along with expected results
for others to better understand you requirements. For details refer to:
www.aspfaq.com/5006
Do you just want to display call_stat as 'Appointment'? Then you can use a
CASE directly in your SELECT list. If you are concerned about the create_dt
value, then you can write something along the lines of:
SELECT TOP 1 call_stat
FROM ( < your union-ed query > ) D
ORDER BY create_date DESC
If you are using the #temp table, use your subquery like:
WHERE create_date = ( SELECT MAX( create_date )
FROM #temp1 WHERE call_stat <> 'Unknown' ) ;
If this has to be done in the inner query, use a CASE expression like:
MAX( CASE WHEN call_stat <> 'unknown' THEN create_date )
Anith|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
There is a common newbiew design flaw called attribute splitting. You
take an attribute and make each value into a separate table. It is
like using a card sorter back when we had punch card (aka "unit
record") data systems.
CREATE TABLE Survey
(create_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, -- screw the
SOX audit rule!
call_status CHAR(30) NOT NULL -- code is too long!
CHECK (call_stat IN ('Pending', 'Referred to Company A', 'Referred
to Company B', 'Appointment', 'Unknown')),
..);
Now the query is simple:
SELECT create_dt, call_stat
FROM Survey
WHERE scp_seq = @.my_scpseq;
But the code implies that this is really screwed up. What you probably
want is a history under the contorl a transition table. Go to
DBAzine.com to learn what a transition constraint table is; I have a
short article there. Google aroudn here for history table.
You are at the point in your education where you still put those
stupid, redudant "tbl-" prefixes on table names. Do not expect
learning how to do it right is going to be quick or easy. And you all
you will get on Newsgroup is stinking dirty kludges.|||I am sorry, I forget to mention that yoru call_status needs to be put
into a well-designed encoding scheme. Get a copy of SQL PROGRAMMING
STYLE for details.|||Please share some details. Heck, if you share some of the details here it
might make a few more people buy your book. Shoot, I might even buy a copy
or two. :) As it stands now, with the title in all caps I halfway expect
that the book will yell at me for 272 pages. If I want yelled at for a few
hours, I will call my wife stupid and it won't cost 30 buck:)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1139275655.490414.101310@.f14g2000cwb.googlegroups.com...
>I am sorry, I forget to mention that yoru call_status needs to be put
> into a well-designed encoding scheme. Get a copy of SQL PROGRAMMING
> STYLE for details.
>|||> CREATE TABLE Survey
> (create_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, -- screw the
> SOX audit rule!
> call_status CHAR(30) NOT NULL -- code is too long!
> CHECK (call_stat IN ('Pending', 'Referred to Company A', 'Referred
> to Company B', 'Appointment', 'Unknown')),
> ..);
Where is the PRIMARY KEY?
Again, another fundemental mistake.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1139275517.755904.323520@.o13g2000cwo.googlegroups.com...
> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> There is a common newbiew design flaw called attribute splitting. You
> take an attribute and make each value into a separate table. It is
> like using a card sorter back when we had punch card (aka "unit
> record") data systems.
> CREATE TABLE Survey
> (create_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, -- screw the
> SOX audit rule!
> call_status CHAR(30) NOT NULL -- code is too long!
> CHECK (call_stat IN ('Pending', 'Referred to Company A', 'Referred
> to Company B', 'Appointment', 'Unknown')),
> ..);
> Now the query is simple:
> SELECT create_dt, call_stat
> FROM Survey
> WHERE scp_seq = @.my_scpseq;
> But the code implies that this is really screwed up. What you probably
> want is a history under the contorl a transition table. Go to
> DBAzine.com to learn what a transition constraint table is; I have a
> short article there. Google aroudn here for history table.
> You are at the point in your education where you still put those
> stupid, redudant "tbl-" prefixes on table names. Do not expect
> learning how to do it right is going to be quick or easy. And you all
> you will get on Newsgroup is stinking dirty kludges.
>|||"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:uOb4zp5KGHA.3396@.TK2MSFTNGP10.phx.gbl...
> If I want yelled at for a few hours, I will call my wife stupid and it
> won't cost 30 buck:)
How much does a dozen roses go for these days...|||Thank you for the input. I would have appreciated your help without
sabotaging
my coding and throwing insults at me. I posted here to understand where I
am doing wrong. You might be an excellent SQL programmer but your character
needs a lot of fine tuning.
--
lyn
"--CELKO--" wrote:

> Please post DDL, so that people do not have to guess what the keys,
> constraints, Declarative Referential Integrity, data types, etc. in
> your schema are. Sample data is also a good idea, along with clear
> specifications. It is very hard to debug code when you do not let us
> see it.
> There is a common newbiew design flaw called attribute splitting. You
> take an attribute and make each value into a separate table. It is
> like using a card sorter back when we had punch card (aka "unit
> record") data systems.
> CREATE TABLE Survey
> (create_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL, -- screw the
> SOX audit rule!
> call_status CHAR(30) NOT NULL -- code is too long!
> CHECK (call_stat IN ('Pending', 'Referred to Company A', 'Referred
> to Company B', 'Appointment', 'Unknown')),
> ...);
> Now the query is simple:
> SELECT create_dt, call_stat
> FROM Survey
> WHERE scp_seq = @.my_scpseq;
> But the code implies that this is really screwed up. What you probably
> want is a history under the contorl a transition table. Go to
> DBAzine.com to learn what a transition constraint table is; I have a
> short article there. Google aroudn here for history table.
> You are at the point in your education where you still put those
> stupid, redudant "tbl-" prefixes on table names. Do not expect
> learning how to do it right is going to be quick or easy. And you all
> you will get on Newsgroup is stinking dirty kludges.
>|||Lyn,
Don't take it personally, Celko attacks everyone like that. If you can wade
through the insults you can often find some useful suggestions. Truth is,
this post was actually quite civil, I truely think he was trying to be nice.
The "newbie design flaw" and "stupid, redudant "tbl-" prefixes" comments are
just part of how he talks here.
"stinking dirty kludges" refers to the solutions that you will get from
others on the newsgroup. I may be able to post one myself, if I can quite
figure out what you need, although I suspect it will involve a couple
subqueries with exists or not exists.
One simple solution is to select each date as a seperate column (outer joins
on scp_seqno), then you will be able to compare all the columns at once to
determine which is the maximum based on whether one column or another is
populated.
He is correct that the database design could probably use some changes.
Whatever help you get here will solve the problem for now, but at the risk
of leaving more complicated code to work with later.
"Lyn" <pablomb@.optonline.net> wrote in message
news:B7C59EFA-7B3B-43BB-BEB6-207A7966F326@.microsoft.com...
> Thank you for the input. I would have appreciated your help without
> sabotaging
> my coding and throwing insults at me. I posted here to understand where I
> am doing wrong. You might be an excellent SQL programmer but your
character
> needs a lot of fine tuning.
> --
> lyn
>
> "--CELKO--" wrote:
>|||Thanks Jim but if someone wanted to teach instead of insult -they
are more likely to learn and be inspired to change rather than kick them
when they are already down.
Anyway, what I'm trying to do is here:
I wanted to get the call status from the 6 tables where create date is the
latest.
Parent/Main table is - tblSurvey
The rest of the tables are just child/related tables.
In Access, I have the main form bound to tblSurvey
and the rest of the subforms are either - Appointment,Pending,
Other Disposition, Referred to CompanyA or Referred to CompanyB.
When a dept. gets a call they either select from one of the subforms
and then I write the call status in tblSurvey.
If the subforms are not filled in - it will be "Unknown".
In the case of Appointments, tblAppointments gets committed first before
tblsurvey by a few minutes or even seconds. We want the status to be as
"Appointment" instead of "Unknown".
tblSurvey:
PK - scp_seqno
create_dt
call_status
other fields
tblAppointments
Pk-Appt_seqno
FK-scp_seqno
create_dt
other fields
tlbPendings
pk-Pending_seqno
fK - scp_seqno
create_dt
other fields
tblReferredtoCompanyA
Pk-companyA_seqno
fk- scp_seqno
create_dt
other fields
and the same goes for the rest of the child tables.
Hopefully this is clear. Thanks for any ideas.
"Jim Underwood" wrote:

> Lyn,
> Don't take it personally, Celko attacks everyone like that. If you can wa
de
> through the insults you can often find some useful suggestions. Truth is,
> this post was actually quite civil, I truely think he was trying to be nic
e.
> The "newbie design flaw" and "stupid, redudant "tbl-" prefixes" comments a
re
> just part of how he talks here.
> "stinking dirty kludges" refers to the solutions that you will get from
> others on the newsgroup. I may be able to post one myself, if I can quite
> figure out what you need, although I suspect it will involve a couple
> subqueries with exists or not exists.
> One simple solution is to select each date as a seperate column (outer joi
ns
> on scp_seqno), then you will be able to compare all the columns at once to
> determine which is the maximum based on whether one column or another is
> populated.
> He is correct that the database design could probably use some changes.
> Whatever help you get here will solve the problem for now, but at the risk
> of leaving more complicated code to work with later.
> "Lyn" <pablomb@.optonline.net> wrote in message
> news:B7C59EFA-7B3B-43BB-BEB6-207A7966F326@.microsoft.com...
> character
>
>