--- In [email protected], "Joe" <joefonsec...@...> wrote: > > I have a senerio where I have a Fact table that has the following fields > FACT_ID, Emp_ID, Point, StartDate, Date_Plus30Days, Comments > > The purpose of the app is to track every time a person misses a day of work > they get some Point # assigned to them. > If they don't call in and don't show they get 5 points. > If they don't show but call they get 2 points. > > The issue that I'm having is that if someone misses work on 1/15/2009 and > they get 5 points. > Then the next day they miss is on 2/28/2009 and they get 2 points. > Lets say they have one more missed day on 4/5/09 for 1 point. > > The records in the table would look like the following. > FACT_ID, Emp_ID, Point, StartDate, Date_Plus30Days, Comments > 1 1 5 1/15/09 2/15/09 Test 1 > 2 1 2 2/28/09 3/28/09 Test 2 > 3 1 1 4/5/09 5/5/09 Test 3 > > **I have an update query that goes in and populates the Date_Plus30Days using > the logic DateAdd("d",30,StartDate). > > The time between 1/15/09 and 2/28/09 is more than 30 days. > I need a way to Insert a record for -1 every time their is a gap of more than > 30 days between entries. > > If you could help me out that would be great or at least give me some ideas > on how to go about this. > > Current Table Structure is: > > Fact Table > FACT_ID, Emp_ID, Point, StartDate, Date_Plus30Days, Comments > > Employee Table > Emp_ID, Employee, Employee_NBR > > Calendar Table > Calendar_ID, Date, Month, Year > > I have a current Append Query that inserts the -1 for every 30 days based on > a table that I created that has 1 date for each month. But this isn't > correct for the requirement. > They need it to insert for every instance where there is a 30 day span > between records. > > INSERT INTO FACT ( POINT, EMP_ID, REASON, DATE_ENTERED ) > SELECT -1 AS POINT, [(-1_LKUP) Query_ALL_CUST_MONTHS].EMP_ID, 'ADMIN ENTERED' > AS REASON, [(-1_LKUP) Query_ALL_CUST_MONTHS].DATE_ENTERED > FROM [(-1_LKUP) Query_ALL_CUST_MONTHS] LEFT JOIN FACT ON ([(-1_LKUP) > Query_ALL_CUST_MONTHS].EMP_ID=FACT.EMP_ID) AND (FORMAT([(-1_LKUP) > Query_ALL_CUST_MONTHS].DATE_ENTERED,"YYYYMM")=Format(FACT.DATE_ENTERED,"YYYYMM")) > WHERE FACT.EMP_ID IS NULL; >
ÇáÓáÇã Úáíßã >if you want to record date exactly in table >make two record start_date and last date >in anew record calculate the diffrence between them and give the points respect to only one month ------>Note : >>your calculation applied to only one month
