Handling working days in a month 1

[collierd]

Hello

Using SQL 2000

If I have a table called tblValidWorkingDays which comprises of a single field called workingDay (sample as shown)

workingDay
2006-11-01 00:00:00
2006-11-02 00:00:00
2006-11-03 00:00:00
2006-11-06 00:00:00
2006-11-07 00:00:00
2006-11-08 00:00:00
2006-11-09 00:00:00
2006-11-10 00:00:00
2006-11-13 00:00:00
2006-11-14 00:00:00
2006-11-15 00:00:00
2006-11-16 00:00:00
2006-11-17 00:00:00
2006-11-20 00:00:00
2006-11-21 00:00:00
2006-11-22 00:00:00
2006-11-23 00:00:00
2006-11-24 00:00:00
2006-11-27 00:00:00
2006-11-28 00:00:00
2006-11-29 00:00:00
2006-11-30 00:00:00
2006-12-01 00:00:00
2006-12-04 00:00:00
2006-12-05 00:00:00
2006-12-06 00:00:00
2006-12-07 00:00:00
2006-12-08 00:00:00
2006-12-11 00:00:00
2006-12-12 00:00:00
2006-12-13 00:00:00
2006-12-14 00:00:00
2006-12-15 00:00:00
2006-12-18 00:00:00
2006-12-19 00:00:00
2006-12-20 00:00:00
2006-12-21 00:00:00
2006-12-22 00:00:00

How do I turn this into table tblWorkingDays such that there are 2 fields workingDay and dayNumber
Where dayNumber is the working day for that date as shown

workingDay             dayNumber
2006-11-01 00:00:00     1
2006-11-02 00:00:00     2
2006-11-03 00:00:00     3
2006-11-06 00:00:00     4
2006-11-07 00:00:00     5
2006-11-08 00:00:00     6
2006-11-09 00:00:00     7
2006-11-10 00:00:00     8
2006-11-13 00:00:00     9
2006-11-14 00:00:00     etc..
2006-11-15 00:00:00
2006-11-16 00:00:00
2006-11-17 00:00:00
2006-11-20 00:00:00
2006-11-21 00:00:00
2006-11-22 00:00:00
2006-11-23 00:00:00
2006-11-24 00:00:00
2006-11-27 00:00:00
2006-11-28 00:00:00
2006-11-29 00:00:00
2006-11-30 00:00:00
2006-12-01 00:00:00
2006-12-04 00:00:00
2006-12-05 00:00:00
2006-12-06 00:00:00
2006-12-07 00:00:00
2006-12-08 00:00:00
2006-12-11 00:00:00
2006-12-12 00:00:00
2006-12-13 00:00:00
2006-12-14 00:00:00
2006-12-15 00:00:00
2006-12-18 00:00:00
2006-12-19 00:00:00
2006-12-20 00:00:00
2006-12-21 00:00:00
2006-12-22 00:00:00

Thanks

Damian.

 

[bborissov]

--- Preparing test data, you don't need this
DECLARE @Test TABLE (workingDay DateTime, dayNumber int DEFAULT 0)
INSERT INTO @Test (workingDay) VALUES ('2006-11-01 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-11-02 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-11-03 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-11-06 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-11-07 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-11-08 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-11-09 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-11-10 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-11-13 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-11-14 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-11-15 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-11-16 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-11-17 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-11-20 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-11-21 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-11-22 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-11-23 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-11-24 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-11-27 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-11-28 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-11-29 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-11-30 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-12-01 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-12-04 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-12-05 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-12-06 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-12-07 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-12-08 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-12-11 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-12-12 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-12-13 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-12-14 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-12-15 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-12-18 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-12-19 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-12-20 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-12-21 00:00:00')
INSERT INTO @Test (workingDay) VALUES ('2006-12-22 00:00:00')
--- End preparing test data, you don't need this


DECLARE @Test2 Table (workingDay datetime, dayNumber int IDENTITY(1,1))
INSERT INTO @Test2 (workingDay)
SELECT workingDay FROM @Test ORDER BY workingDay


UPDATE @Test SET dayNumber = TestMe.dayNumber
FROM @Test Test
INNER JOIN (SELECT workingDay, (Test.dayNumber - Tbl1.dayNumber + 1) AS dayNumber
                   FROM @Test2 Test
                   INNER JOIN (SELECT YEAR(workingDay) Yr, MONTH(workingDay) Mnt, MIN(dayNumber) AS dayNumber
                                      FROM @Test2
                                      GROUP BY YEAR(workingDay), MONTH(workingDay)) Tbl1
                   ON YEAR(Test.workingDay)  = Tbl1.Yr AND
                      MONTH(Test.workingDay) = Tbl1.Mnt) TestMe
ON Test.workingDay = TestMe.workingDay

SELECT * from @Test

If that works as you want, make a good backup, and change
@Test with your actual table name


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[collierd]

Thanks

 

[collierd]

If I wanted to take this a step further and insert records that are currently missing from this list and allocate them the nearest previous date

e.g. 2006-11-04 would be day 3

Is this possible?

 

[bborissov]

You must insert these dates before running this. How do you know which date is missing, If you keep only working days?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[ESquared]

You know, there's a whole FAQ in this forum about this.