J
joefonseca79
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;
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;