Can anyone tell me if Microsoft has set a release date for MDAC 2.7 SP1 yet?
Please email me at joeydba@.yahoo.com - thanks.
Opps - I meant MDAC 2.8.
Sorry
|||I am assuming you mean MDAC 2.8 SP1. If so, I do not believe there is an
official release date yet.
Rand
This posting is provided "as is" with no warranties and confers no rights.
sql
Showing posts with label date. Show all posts
Showing posts with label date. Show all posts
Monday, March 26, 2012
Wednesday, March 7, 2012
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
>
>
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
>
>
Maximum Date in a group of records
I have a table that I need to find the maximum date per Doc_No per Parent_Doccategory. I can get the maximum date per Doc_No but I can't get the information for the next level. The following script is getting the maximum date per airplane but I should get a maximum date each time the parent_doccategory changes. Can anyone help? This is the script so far:
Try adding another argument in the first where clause saying which one you want like example add
That will work but then it eliminates the other two lines that I also need. I basinally need two group levels, one for the Parent_doc_No and the other for the Parent_DocCategory so that it first looks at the Parent_Doc_No and then at the Parent_Doc_Category and gets the most up to date for each category.
CREATE PROCEDURE [dbo].[sp_Maximum_Change_Date] AS
select Effective_Updated , parent_doc_no, parent_doccategory from gdb_01_4.dbo.aircraft_changes As S
where effective_updated = (select max(effective_updated)
from gdb_01_4_test.dbo.aircraft_changes where
parent_doc_no = S.parent_doc_no) group by parent_doc_no,parent_doccategory, effective_updated, order by parent_doc_no
GO
PARENT_DOC_NOPARENT_DOCCATEGORYEFFECTIVE_UPDATED
129 AC 3/24/2004 1:54:29 PM
129 AC 4/2/2004 1:44:39 PM
129 AE 6/24/2004 8:49:13 AM
129 AU 9/28/2004 12:16:30 PM
I want to get the one record for AC with the 4/2 date, the one for AE and the one for AU. My script is only returning the AU record because it has the maximum date for Parent_Doc_No 129.
Quote:
Originally Posted by crackerbox
I have a table that I need to find the maximum date per Doc_No per Parent_Doccategory. I can get the maximum date per Doc_No but I can't get the information for the next level. The following script is getting the maximum date per airplane but I should get a maximum date each time the parent_doccategory changes. Can anyone help? This is the script so far:
CREATE PROCEDURE [dbo].[sp_Maximum_Change_Date] AS
select Effective_Updated , parent_doc_no, parent_doccategory from gdb_01_4.dbo.aircraft_changes As S
where effective_updated = (select max(effective_updated)
from gdb_01_4_test.dbo.aircraft_changes where
parent_doc_no = S.parent_doc_no) group by parent_doc_no,parent_doccategory, effective_updated, order by parent_doc_no
GO
PARENT_DOC_NOPARENT_DOCCATEGORYEFFECTIVE_UPDATED
129 AC 3/24/2004 1:54:29 PM
129 AC 4/2/2004 1:44:39 PM
129 AE 6/24/2004 8:49:13 AM
129 AU 9/28/2004 12:16:30 PM
I want to get the one record for AC with the 4/2 date, the one for AE and the one for AU. My script is only returning the AU record because it has the maximum date for Parent_Doc_No 129.
Try adding another argument in the first where clause saying which one you want like example add
and parent_doccategory = 'AE'
before the group by clause|||
Quote:
Originally Posted by Taftheman
Try adding another argument in the first where clause saying which one you want like example add
and parent_doccategory = 'AE'
before the group by clause
That will work but then it eliminates the other two lines that I also need. I basinally need two group levels, one for the Parent_doc_No and the other for the Parent_DocCategory so that it first looks at the Parent_Doc_No and then at the Parent_Doc_Category and gets the most up to date for each category.
Maximum Date in a group of records
hi all,
i got is table:
Id StartDate EndDate
a 19/03/2001 18/03/2002
a 19/03/2002 18/04/2002*
b 13/08/2000 12/08/2001
b 13/08/2001 12/08/2002
b 13/08/2002 10/07/2002*
i got is table:
Id StartDate EndDate
a 19/03/2001 18/03/2002
a 19/03/2002 18/04/2002*
b 13/08/2000 12/08/2001
b 13/08/2001 12/08/2002
b 13/08/2002 10/07/2002*
Sort command and groupins i am ok but i need to select only the records that
has the latest enddate. (See *)
any ideas? thanks in advance
rashidThis query returns row(s) with the latest Enddate for each ID but the row
for ID='B' isn't the one you highlighted.
SELECT id, startdate, enddate
FROM SomeTable AS S
WHERE enddate =
(SELECT MAX(enddate)
FROM SomeTable
WHERE id = S.id)
Hope this helps.
--
David Portas
SQL Server MVP
--
Maximum date calculation for all records
For Crystal Reports version 8.5, I need a report for last year's amounts and this year's amounts. My raw data looks like: fund-type, fund-id, customer-id, check amount, check date, last year's beginning date, last year's ending date, this year's beginning date, and this year's ending date. The beginning and ending dates are subject to change. Also, out of the 2,000 records in the raw data, about 5 of the records have the beginning and ending dates attached. I have used the summary calculation of Maximum to find the beginning and ending dates. My problem is that I cannot use the Maximum function in the formula fields to determine if a check is for last year or this year. I also need totals for a customer's checks (last year and this year), fund-id amounts (this year and last year), and fund-type (last year and this year). When I try to create running-totals, I receive the error message that the summary/running totals cannot be created. Is there a way to create this report?
Thank you for any and all help and advice,
TracySHCCreate a formula having the code
If year(datecol)<year(currentdate) then
1
else
2
Place it in details section
Now group the report by this formula and use summary accordingly
Thank you for any and all help and advice,
TracySHCCreate a formula having the code
If year(datecol)<year(currentdate) then
1
else
2
Place it in details section
Now group the report by this formula and use summary accordingly
Saturday, February 25, 2012
Maxdate in table data (date range)
Hi all, First post on THIS forum...so be kind (I have my nomex suite on anyway, and am flameproof, but STILL!)
I have a table that consists of a bunch-o-gibberish, but essentially looks like this (for the sake of discussion):
PK RecID int
PK StartDate smalldatetime
PK EndDate smalldatetime
Gibberish_1 varchar(zillions and zillions)
Gibberish_2 int
OK, my query (get it? *LOL* I kill me) is this:
My PK date range is essentially a range indicating a "validity date range" or in other words - "this record applys to dates from StartDate to EndDate".
If I insert a new row, that I want to be "valid" from today on into the forseeable future. I'm thinking I would enter it with StartDate = today, and would like to figure out a good "MAXDATE" type value to put in the EndDate column so that:
1) I can find a record using a single date (i.e., the current date) and find the validation record (as defined above) - - - I'm not concerned about this part of the question, as I can build the simple query to do so).
2) NOT have to keep adjusting the EndDate to keep it ahead of the current date so that the query into the range will work.
Essentially, my question is this: What is a good value to put in the EndDate to mean "infinity" - - that will still work when I query using a single date designed to find the validation record with a range that includes it?
My boss suggested something like "January 1, 2040", but this bothers me, since my kids may be working here then :lol: and I don't want to have to force them into a Y2K-like issue where they now have to go adjust all the "maxdate" values. Anythoughts or conventions that I am unaware of here?
Thanks!
PaulSince your end date column is small datetime the max value you can store is June 6, 2079. If you change your column to datetime, max date you can put is December 31, 9999.|||Thanks...I suppose that buys enough time that it would be the problem of my GRANDkids :D so that just may do...I was just hoping for something magical like MAXDATE, so it would never have to be changed.
Oh well, it's just a typically @.nal developer concern...typical for me...it just somehow seems WRONG on so many levels to put an essentially "random" date in as the high end of an open range.
As if the database OR application will still be around in 2079 anyway *LOL*
Thanks again for confirming my worst fears (well, besides the fear of opening that container of green stuff down in the back corner of my refridgerator, that is ;) )|||I am leaving the EndDate field empty if it needs to be an open date, and in my queries still use BETWEEN:
...where @.DateParameter between DateStart and isnull(DateEnd, @.DateParameter)|||But the problem is .. his app is not gonna work after June 6, 2079 coz the max date he can put into his start date is also smalldatetime|||Hmmm...I'll check that out...I wonder if it will work though if some of the records have a valid end date?
My application will "never" have overlapping dates...but may have sequential ranges...such as:
rec1 StartDate = 01/01/2002 EndDate = 02/01/2002
rec2 StartDate = 02/02/2002 EndDate = 01/01/2004
rec3 StartDate = 01/02/2004 EndDate = NULL
will your query still work to find, say...12/31/2003 even though your query's top end is "isnull"? I guess I'll give it a test and find out.
Thanks!|||Originally posted by Enigma
But the problem is .. his app is not gonna work after June 6, 2079 coz the max date he can put into his start date is also smalldatetime
*LOL* :smacking self on the forehead: There's always THAT, if you wanna get picky ;)
Problem solved...
Though, I suppose (of Y2K taught us anything) that about May 31st, 2079 - they will change the algorithm to allow later dates...and I guess that's the brunt of my angst...I just HATE putting a "hard" date in a field that doesn't represent a hard date.
Anyway, thanks for helping with the mental exercises this morning!
Paul|||Originally posted by rdjabarov
I am leaving the EndDate field empty if it needs to be an open date, and in my queries still use BETWEEN:
...where @.DateParameter between DateStart and isnull(DateEnd, @.DateParameter)
Oops...sorry...misunderstood the function of the isnull function.|||I'd leave the end-date null, like rdjabarov suggested.|||Yeah, I seriously thought about it, but the trouble with that is my primary key includes the EndDate (and so can't be NULL).
However, that leads me to another question...does the EndDate NEED to be part of the key? Hmmmm...if the selects on this table are going to be exclusively by a single date...i.e., each day I will build a child table row by first looking to this "validation" table by using the current date...am I losing performance by not including the EndDate in the key?
My concern is that my BETWEEN clause will be less efficient if the enddate is not in the primary key.
Also keep in mind that I will also be performing selects where the BeginDate and EndDate will actually have (historical - aka past...) dates in them (as noted in an earlier post).|||You can exclude DateEnd from the PK, change the PK to non-clustered (unless you absolutely need it to be clustered), and create a clustered index with all three fields, which allows for a nullable column.
I have a table that consists of a bunch-o-gibberish, but essentially looks like this (for the sake of discussion):
PK RecID int
PK StartDate smalldatetime
PK EndDate smalldatetime
Gibberish_1 varchar(zillions and zillions)
Gibberish_2 int
OK, my query (get it? *LOL* I kill me) is this:
My PK date range is essentially a range indicating a "validity date range" or in other words - "this record applys to dates from StartDate to EndDate".
If I insert a new row, that I want to be "valid" from today on into the forseeable future. I'm thinking I would enter it with StartDate = today, and would like to figure out a good "MAXDATE" type value to put in the EndDate column so that:
1) I can find a record using a single date (i.e., the current date) and find the validation record (as defined above) - - - I'm not concerned about this part of the question, as I can build the simple query to do so).
2) NOT have to keep adjusting the EndDate to keep it ahead of the current date so that the query into the range will work.
Essentially, my question is this: What is a good value to put in the EndDate to mean "infinity" - - that will still work when I query using a single date designed to find the validation record with a range that includes it?
My boss suggested something like "January 1, 2040", but this bothers me, since my kids may be working here then :lol: and I don't want to have to force them into a Y2K-like issue where they now have to go adjust all the "maxdate" values. Anythoughts or conventions that I am unaware of here?
Thanks!
PaulSince your end date column is small datetime the max value you can store is June 6, 2079. If you change your column to datetime, max date you can put is December 31, 9999.|||Thanks...I suppose that buys enough time that it would be the problem of my GRANDkids :D so that just may do...I was just hoping for something magical like MAXDATE, so it would never have to be changed.
Oh well, it's just a typically @.nal developer concern...typical for me...it just somehow seems WRONG on so many levels to put an essentially "random" date in as the high end of an open range.
As if the database OR application will still be around in 2079 anyway *LOL*
Thanks again for confirming my worst fears (well, besides the fear of opening that container of green stuff down in the back corner of my refridgerator, that is ;) )|||I am leaving the EndDate field empty if it needs to be an open date, and in my queries still use BETWEEN:
...where @.DateParameter between DateStart and isnull(DateEnd, @.DateParameter)|||But the problem is .. his app is not gonna work after June 6, 2079 coz the max date he can put into his start date is also smalldatetime|||Hmmm...I'll check that out...I wonder if it will work though if some of the records have a valid end date?
My application will "never" have overlapping dates...but may have sequential ranges...such as:
rec1 StartDate = 01/01/2002 EndDate = 02/01/2002
rec2 StartDate = 02/02/2002 EndDate = 01/01/2004
rec3 StartDate = 01/02/2004 EndDate = NULL
will your query still work to find, say...12/31/2003 even though your query's top end is "isnull"? I guess I'll give it a test and find out.
Thanks!|||Originally posted by Enigma
But the problem is .. his app is not gonna work after June 6, 2079 coz the max date he can put into his start date is also smalldatetime
*LOL* :smacking self on the forehead: There's always THAT, if you wanna get picky ;)
Problem solved...
Though, I suppose (of Y2K taught us anything) that about May 31st, 2079 - they will change the algorithm to allow later dates...and I guess that's the brunt of my angst...I just HATE putting a "hard" date in a field that doesn't represent a hard date.
Anyway, thanks for helping with the mental exercises this morning!
Paul|||Originally posted by rdjabarov
I am leaving the EndDate field empty if it needs to be an open date, and in my queries still use BETWEEN:
...where @.DateParameter between DateStart and isnull(DateEnd, @.DateParameter)
Oops...sorry...misunderstood the function of the isnull function.|||I'd leave the end-date null, like rdjabarov suggested.|||Yeah, I seriously thought about it, but the trouble with that is my primary key includes the EndDate (and so can't be NULL).
However, that leads me to another question...does the EndDate NEED to be part of the key? Hmmmm...if the selects on this table are going to be exclusively by a single date...i.e., each day I will build a child table row by first looking to this "validation" table by using the current date...am I losing performance by not including the EndDate in the key?
My concern is that my BETWEEN clause will be less efficient if the enddate is not in the primary key.
Also keep in mind that I will also be performing selects where the BeginDate and EndDate will actually have (historical - aka past...) dates in them (as noted in an earlier post).|||You can exclude DateEnd from the PK, change the PK to non-clustered (unless you absolutely need it to be clustered), and create a clustered index with all three fields, which allows for a nullable column.
Monday, February 20, 2012
MAX(DATE) - help
Hello everyone:
i am trying to compare a MAX(DATE) from one table that would be greater
than a date in another.
first_table
compname MYDATE
abc comp 2006-09-26 09:19:43.250
123inc 2006-10-20 01:3:00.250
SELECT MAX(MYDATE) FROM first_table
log_table
logdate
2006-01-15 05:12:41.250
I would like to get the MAX(MYDATE) which is greater than the logdate.
thx
f.Freddie ,
If I understand correctly ,
What you want all the dates in log_table having date less than
max(date) in first table
SELECT * FROM log_table WHERE log_date < (SELECT MAX(DATE) FROM
first_table)
M A Srinivas
Freddie wrote:
Quote:
Originally Posted by
Hello everyone:
>
i am trying to compare a MAX(DATE) from one table that would be greater
than a date in another.
>
first_table
>
compname MYDATE
>
abc comp 2006-09-26 09:19:43.250
123inc 2006-10-20 01:3:00.250
>
SELECT MAX(MYDATE) FROM first_table
>
log_table
>
logdate
>
2006-01-15 05:12:41.250
>
I would like to get the MAX(MYDATE) which is greater than the logdate.
>
>
thx
>
f.
Max with Datepart
I am wanting to set any given @.Date parameter to the most current recorded Monday in a table (tblMarketPricing). Would this work:
@.Date = Max(Datepart(dw,MktDate) IN 2
I am unsure if you can use Max with the Datepart function.
***If this is not allowed, can anyone suggest anything different that I might try?
To get what you are asking for you need:
Select @.date = Max(MktDate) from tblMarketPricing where DatePart(dw, MktDate) = 2
assuming that 2 corresponds to Monday on your system.
You could consider using the DateName() function in place of DatePart().
Since it will otherwise be forthcoming, I will suggest that a Calendar table could also be used to provide these sorts of answers.
|||That worked! Thank you.MAX vs COUNT - subselect
I have a table where is recorded each employee carreer in my enterprise.
This is my table:
employee_id | date | function
The last date I have for each employee corresponds to the present function of that employee in my enterprise.
How can I know how many employees I have in each function at the present time?
I tried this one without success :confused: :
select id_function, count(id_function) from
(select id_employee, max(id_function) from carreer group by id_employee) group by id_function;
Thanks in advance.I wish I worked for you - I would love to only have one function in my enterprise :)
You would be looking to find the last date for each employee in one query and then match this last date to your table to find the last function your employees have been essigned.
HTH|||match? like with a join? we don' need no steenkin joins!
select function
, count(*) as employees
from (
select function
, employee_id
from carreer as T
where date
= ( select max(date)
from carreer
where employee_id = T.employee_id )
) as latest
group
by function|||match? like with a join? Or match to corrolated subQ result :)
Is that your latest NZDF mission - eliminate joins from RFH responses?|||Maybe I didn't explain myself quite well. What I'd like to know is:
How many employees I have in each level of my organization chart (function) at the present date.
So what I need is to count how many employees I have in each "function" of this select -
Select employee_id, max(funtion) from carreer group by employee_id|||did you at least try the query i suggested? what was the result?
This is my table:
employee_id | date | function
The last date I have for each employee corresponds to the present function of that employee in my enterprise.
How can I know how many employees I have in each function at the present time?
I tried this one without success :confused: :
select id_function, count(id_function) from
(select id_employee, max(id_function) from carreer group by id_employee) group by id_function;
Thanks in advance.I wish I worked for you - I would love to only have one function in my enterprise :)
You would be looking to find the last date for each employee in one query and then match this last date to your table to find the last function your employees have been essigned.
HTH|||match? like with a join? we don' need no steenkin joins!
select function
, count(*) as employees
from (
select function
, employee_id
from carreer as T
where date
= ( select max(date)
from carreer
where employee_id = T.employee_id )
) as latest
group
by function|||match? like with a join? Or match to corrolated subQ result :)
Is that your latest NZDF mission - eliminate joins from RFH responses?|||Maybe I didn't explain myself quite well. What I'd like to know is:
How many employees I have in each level of my organization chart (function) at the present date.
So what I need is to count how many employees I have in each "function" of this select -
Select employee_id, max(funtion) from carreer group by employee_id|||did you at least try the query i suggested? what was the result?
Subscribe to:
Posts (Atom)