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.
| Author |
Topic |
|
hrsepla
Starting Member
1 Post |
 Posted - 2011-08-09 : 12:09:57
|
| I am querying a table that when a record is (M)odified another record is placed in the table noting the status and effective date of the change. When there are duplicate ID's I need to get ONLY the NULL record before the effective date and ONLY the modified record after the effective date. Records without a change will appear only once in the table and have a status and eff_date of NULL.Please help, I have been pulling my hair out for days on what I thought would be a simple query. Note the table structures cannot be changed.id status eff_date200 NULL NULL201 NULL NULL202 NULL NULL203 NULL NULL203 M 2011-09-11 00:00:00 204 NULL NULL204 M 2011-08-07 00:00:00205 NULL NULL205 M 2011-08-05 00:00:00-hrsepla |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-08-09 : 12:14:32
|
| what are values you use as an input to filter the result from above?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-08-09 : 16:12:32
|
| Every SQL forum or newsgroup expects that you will post DDL, sample data and clear specifications which might might include output. This is explained in the FAQ section. We can not read minds; what would we need to know to do your job for you? Tables must have keys and should have DRI, constraints, and all the basic features of a schema. You should know use ISO-8601 Standards for temporal data, avoid needless dialect, basic data modeling and use ISO-11179 Standards for data element names. Please tell us if you can change the DDL. >> I am querying a table that when a record [sic: record [sic] are not records] is (M)odified another record [sic] is placed in the table noting the status and effective date of the change. <<You failed to follow basic Netiquette, you got the basics for SQL wrong AND you wan to do something stupid. Not a good start. Audits are always done by software external to the schema. This is a matter of law and common sense. What you want to do it like putting the back up log file on the same disk as the schema, so you can lose them both with a single crash. Do you know the difference between an audit versus a history? >> When there are duplicate ID's I need to get ONLY the NULL record [sic] before the effective date and ONLY the modified record [sic] after the effective date. records [sic] without a change will appear only once in the table and have a status and eff_date of NULL. <<Yep, a vague silly generic “id” to mimic a magnetic tape record number. >> Please help, I have been pulling my hair out for days on what I thought would be a simple query. Note the table structures cannot be changed. <<WHAT TABLE STRUCTURES? You never posted DDL! So why don't you follow basic Netiquette? Oh, we have a DATE data type now so you can get rid of the '00:00:00' in the DATETIME you used. --CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
|
|
|
|
|
query correct status record with null dates