How to restrict date value in one table based on dates in another table

全部回复

• 2012年7月5日 21:39

   

   
  

  0

  Hi,

  You can implement this using Referential Integrity Constraints ( Foreign keys) in SQL Server - http://msdn.microsoft.com/en-us/library/ms175464(v=sql.105).aspx

  In your scenario, the WEEKSTARTDATE in  TBLTIMECARD is the parent and the DAY column in TBLTIMECARDDETAIL  is the child. A Simple Foreign Key relationship will work, if your constraint you wish to implement is as simple as an exact match if values between the two. However, if you need to implement a complex constraint that is more than an exact match of values, you should explore CHECK constraints - http://msdn.microsoft.com/en-us/library/ms188258(v=sql.105).aspx

  Hope this helps.

  ---

  Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

  
  

  • 已编辑 Sanil Mhatre 2012年7月5日 21:45 added links
  •  
  • 回复
  • 引用

   
• 2012年7月6日 4:52

   

   
  

  0
  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. If you know how, follow ISO-11179 data element naming conventions and formatting rules (you do not). Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 
  
  This is minimal polite behavior on SQL forums. 
  
  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. 
  
  >> What I have is a table (TBLTIMECARD) that contains columns for TIMECARDID as well as a WEEKSTARTDATE (my access form computes a WEEKENDDATE but it is not stored in the table). << 
  
  Notice that table names never have that silly “tbl-” affix on them. That meta data flag was needed by 1960's BASIC and a few simple one-pass compilers. Since a table models a set, its name is a collective or plural noun. Oh, we have lowercase letters now, so quit writing code as if you used punch cards. 
  
  CREATE TABLE Timecards
  (emp_id CHAR(9) NOT NULL,
   work_date DATE NOT NULL,
   PRIMARY KEY(emp_id CHAR, work_date),
   work_start_time TIME NOT NULL,
   work_end_time TIME, – null is still working
   ..);
  
  You have a natural key, but failed to use it. A useful idiom is a report period calendar. It gives a name to a range of dates. In your casem the ISO-08601 week names 
  
  CREATE TABLE Report_Periods
  (week_name CHAR(10) NOT NULL 
   CHECK (week_name LIKE '[12][0-9][0-9][0-9]W[0-5][0-9]'),
   week_start_date DATE NOT NULL PRIMARY KEY,
   week_end_date DATE NOT NULL,
   CONSTRAINT date_ordering
   CHECK (week_start_date <= week_end_date),
   etc);
  
  >> I have another table (timecard_details) that uses the timecard_id from the first table and then allows entries for the days of the week including time in and out and some calculation. What I need to do is restrict the day column entries in timecard_details to fall on the week_start_date from the first table or on one of the 6 days following that date on rows where the timecard_id is the same in both. <<
  
  This is inside out! The facts to catch in the data model are the daily hours worked. Then summarize them in a VIEW that is always correct. 
  
  CREATE VIEW Weekly_Timecards 
  AS
  SELECT T.emp_id, R.week_name,
         SUM(DATEDIFF(MM, work_start_time, work_end_time))
           work_time_tot
    FROM Timecards AS T, Report_Periods AS R
   WHERE T.work_date BETWEEN R.week_start_date AND R.week_end_date 
   GROUP BY T.emp_id, R.week_name;
  
  Note that unfinished days become NULLs

  ---

  --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

  • 回复
  • 引用

   
• 2012年7月6日 16:06

   

   
  

  0

  >>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. If you know how, follow ISO-11179 data element naming conventions and formatting rules (you do not). Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 
  This is minimal polite behavior on SQL forums. <<

  I have no idea what you are on about in this first paragraph.  If I already knew everything I wouldn’t have to ask questions and you seem like the last person who should be giving advice about politeness based on the massive condescension in the rest of your post.  Please go spread your arrogance elsewhere I have no use for it.  I am more interested in helpful responses.  And not that I owe you any explanation, this database was created using SSMA from an Oracle database so yes it probably was originally created by someone who was familiar with punchcards.

  @Sanil, from what I have read the check constraint cannot reference another table.  It looks like the foreign key constraint might work but I am not sure how.  I’ll do some more reading on it.  The TIMECARDID is PK on TBLTIMECARD and FK on TBLTIMECARDDETAIL.

  
  

  • 已编辑 MnM Show 2012年7月6日 18:10
  •  
  • 回复
  • 引用

   
• 2012年7月6日 20:52

   

   
  

  1

  @MnM_Show - I agree that your situation does not allow you to easily implement Foreign keys. Do you have the liberty of re-designing some of the table structures bringing them up to database normalization standards (3N form) ? If that is possible, the table structures suggested by (@CELKO) is a feasible and good solution (Try to see past some of the unploiteness in that response)

  An interesting way to bypass the check constraint limitation of referencing another table, is by creating a user defined function (UDF) . The UDF would be added as the check constraint on the required column as demonstrated in these examples - http://msdn.microsoft.com/en-us/library/ms188258(v=sql.105).aspx , http://connectsql.blogspot.com/2011/01/sql-server-function-based-check.html . The check constraint will take the column as an input parameter and the logic within the constraint would perform the validation against another table. The end result of the validation can be return back as 0 or 1 (success or failure). Here is another example - http://snipplr.com/view/17476/ . The logic can be extended to query another table.

  Hope this helps.

  ---

  Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

  
  

  • 已编辑 Sanil Mhatre 2012年7月6日 21:12
  • 已标记为答案 MnM Show 2012年7月6日 21:35
  •  
  • 回复
  • 引用

   
• 2012年7月6日 21:35

   

   
  

  0

  Thank you Sanil.  I had actually found the solution of using a check constraint and a UDF elsewhere http://stackoverflow.com/questions/3200795/sql-server-check-constraint-on-a-column-where-values-come-from-another-table and I was going down that road.  I believe that I can get this method to work.  Thanks for the links, I am finding this one helpful as well http://msdn.microsoft.com/en-us/magazine/cc164062.aspx

  In terms of do I have the latitude and do I have the organizational permission, then yes I can redesign the database.  However this is not a mission critical database, it is simply a tool to allow people to calculate their time for the week since we have people who work non-traditional hours and times.  I have already gotten it to duplicate what it did with the Oracle backend now that it is on the SQL Server, this constraint on data entry is just an improvement I want to make.  I am not a DBA I am a sysadmin, so I am not looking to reinvent the wheel on this project.  Thanks for your help.

  • 回复
  • 引用

   
• 2012年7月6日 21:38

   

   
  

  0
  Thats great. Glad i could help.

  ---

  Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

  • 回复
  • 引用

   
• 2012年7月6日 21:46

   

   
  

  0

  You could probably create an updateable view that emulates a join by putting a subquery in the where clause. Presumably your time cards are good for one week only, and cross referenced to an employee to which the card was assigned.

  With the updateable view you can select from the detail table with a check constraint that looks up the week start date by the time card id. You have to have the settings correct to allow updateable views.

  http://msdn.microsoft.com/en-us/library/k625abw5(v=VS.80).aspx

  • 回复
  • 引用