Get Working Day Number/Days Left in Month

wembleybear · August 30, 2019, 2:45pm

I have a calendar table which has columns for the date and whether or not it is a working day as defined in our ERP system. What I need help with is working out the working day numbers for the dates. I need to get:

WRKDAY - The number of the working day in the month, so excluding non-work days.
REMAIN - The number of working days remaining in the month, again ignoring non-work days
WRKTOTAL - The number of working days in the week in total, ignoring non-work days

This is what I need to get to using January 2016 as an example:

+---------+------------+---------+-----------+--------+--------+----------+
| CALCODE |  CALDATE   | CALWORK |  CALNAME  | WRKDAY | REMAIN | WRKTOTAL |
+---------+------------+---------+-----------+--------+--------+----------+
| BH      | 01/01/2016 |       0 | Friday    |        |        |          |
| BH      | 02/01/2016 |       0 | Saturday  |        |        |          |
| BH      | 03/01/2016 |       0 | Sunday    |        |        |          |
| BH      | 04/01/2016 |       1 | Monday    |      1 |     20 |        5 |
| BH      | 05/01/2016 |       1 | Tuesday   |      2 |     19 |        5 |
| BH      | 06/01/2016 |       1 | Wednesday |      3 |     18 |        5 |
| BH      | 07/01/2016 |       1 | Thursday  |      4 |     17 |        5 |
| BH      | 08/01/2016 |       1 | Friday    |      5 |     16 |        5 |
| BH      | 09/01/2016 |       0 | Saturday  |        |        |          |
| BH      | 10/01/2016 |       0 | Sunday    |        |        |          |
| BH      | 11/01/2016 |       1 | Monday    |      6 |     15 |        5 |
| BH      | 12/01/2016 |       1 | Tuesday   |      7 |     14 |        5 |
| BH      | 13/01/2016 |       1 | Wednesday |      8 |     13 |        5 |
| BH      | 14/01/2016 |       1 | Thursday  |      9 |     12 |        5 |
| BH      | 15/01/2016 |       1 | Friday    |     10 |     11 |        5 |
| BH      | 16/01/2016 |       0 | Saturday  |        |        |          |
| BH      | 17/01/2016 |       0 | Sunday    |        |        |          |
| BH      | 18/01/2016 |       1 | Monday    |     11 |     10 |        5 |
| BH      | 19/01/2016 |       1 | Tuesday   |     12 |      9 |        5 |
| BH      | 20/01/2016 |       1 | Wednesday |     13 |      8 |        5 |
| BH      | 21/01/2016 |       1 | Thursday  |     14 |      7 |        5 |
| BH      | 22/01/2016 |       1 | Friday    |     15 |      6 |        5 |
| BH      | 23/01/2016 |       0 | Saturday  |        |        |          |
| BH      | 24/01/2016 |       0 | Sunday    |        |        |          |
| BH      | 25/01/2016 |       1 | Monday    |     16 |      5 |        5 |
| BH      | 26/01/2016 |       1 | Tuesday   |     17 |      4 |        5 |
| BH      | 27/01/2016 |       1 | Wednesday |     18 |      3 |        5 |
| BH      | 28/01/2016 |       1 | Thursday  |     19 |      2 |        5 |
| BH      | 29/01/2016 |       1 | Friday    |     20 |      1 |        5 |
| BH      | 30/01/2016 |       0 | Saturday  |        |        |          |
| BH      | 31/01/2016 |       0 | Sunday    |        |        |          |
+---------+------------+---------+-----------+--------+--------+----------+

I've tried using DATEPART, but can't get to the numbers in the places I need them, so any help would be appreciated.

yosiasz · August 30, 2019, 3:32pm

please post this data in consumable format?

create table #calendar(CALCODE char(2), CALDATE DATE, CALWORK bit, 
CALNAME varchar(50) ) --etc

insert into #calendar
select 'BH', '01/01/2016', 0, 'Friday' union  --and the rest of the data.

wembleybear · August 30, 2019, 4:50pm

Please find below. I forgot also to mention that for the 3 new fields, if it is not a working day, these should be set to 0.

CREATE TABLE [dbo].[CALENDARS](
	[CALCODE] [varchar](2) NULL,
	[CALDATE] [date] NULL,
	[CALWORK] [int] NULL,
	[CALNAME] [varchar](10) NULL
)

INSERT [dbo].[CALENDARS] ([CALCODE], [CALDATE], [CALWORK], [CALNAME]) VALUES (N'BH', CAST(N'2016-01-01' AS Date), 0, N'Friday')
GO
INSERT [dbo].[CALENDARS] ([CALCODE], [CALDATE], [CALWORK], [CALNAME]) VALUES (N'BH', CAST(N'2016-01-02' AS Date), 0, N'Saturday')
GO
INSERT [dbo].[CALENDARS] ([CALCODE], [CALDATE], [CALWORK], [CALNAME]) VALUES (N'BH', CAST(N'2016-01-03' AS Date), 0, N'Sunday')
GO
INSERT [dbo].[CALENDARS] ([CALCODE], [CALDATE], [CALWORK], [CALNAME]) VALUES (N'BH', CAST(N'2016-01-04' AS Date), 1, N'Monday')
GO
INSERT [dbo].[CALENDARS] ([CALCODE], [CALDATE], [CALWORK], [CALNAME]) VALUES (N'BH', CAST(N'2016-01-05' AS Date), 1, N'Tuesday')
GO
INSERT [dbo].[CALENDARS] ([CALCODE], [CALDATE], [CALWORK], [CALNAME]) VALUES (N'BH', CAST(N'2016-01-06' AS Date), 1, N'Wednesday')
GO
INSERT [dbo].[CALENDARS] ([CALCODE], [CALDATE], [CALWORK], [CALNAME]) VALUES (N'BH', CAST(N'2016-01-07' AS Date), 1, N'Thursday')
GO
INSERT [dbo].[CALENDARS] ([CALCODE], [CALDATE], [CALWORK], [CALNAME]) VALUES (N'BH', CAST(N'2016-01-08' AS Date), 1, N'Friday')
GO
INSERT [dbo].[CALENDARS] ([CALCODE], [CALDATE], [CALWORK], [CALNAME]) VALUES (N'BH', CAST(N'2016-01-09' AS Date), 0, N'Saturday')
GO
INSERT [dbo].[CALENDARS] ([CALCODE], [CALDATE], [CALWORK], [CALNAME]) VALUES (N'BH', CAST(N'2016-01-10' AS Date), 0, N'Sunday')
GO
INSERT [dbo].[CALENDARS] ([CALCODE], [CALDATE], [CALWORK], [CALNAME]) VALUES (N'BH', CAST(N'2016-01-11' AS Date), 1, N'Monday')
GO
INSERT [dbo].[CALENDARS] ([CALCODE], [CALDATE], [CALWORK], [CALNAME]) VALUES (N'BH', CAST(N'2016-01-12' AS Date), 1, N'Tuesday')
GO
INSERT [dbo].[CALENDARS] ([CALCODE], [CALDATE], [CALWORK], [CALNAME]) VALUES (N'BH', CAST(N'2016-01-13' AS Date), 1, N'Wednesday')
GO
INSERT [dbo].[CALENDARS] ([CALCODE], [CALDATE], [CALWORK], [CALNAME]) VALUES (N'BH', CAST(N'2016-01-14' AS Date), 1, N'Thursday')
GO
INSERT [dbo].[CALENDARS] ([CALCODE], [CALDATE], [CALWORK], [CALNAME]) VALUES (N'BH', CAST(N'2016-01-15' AS Date), 1, N'Friday')
GO
INSERT [dbo].[CALENDARS] ([CALCODE], [CALDATE], [CALWORK], [CALNAME]) VALUES (N'BH', CAST(N'2016-01-16' AS Date), 0, N'Saturday')
GO
INSERT [dbo].[CALENDARS] ([CALCODE], [CALDATE], [CALWORK], [CALNAME]) VALUES (N'BH', CAST(N'2016-01-17' AS Date), 0, N'Sunday')
GO
INSERT [dbo].[CALENDARS] ([CALCODE], [CALDATE], [CALWORK], [CALNAME]) VALUES (N'BH', CAST(N'2016-01-18' AS Date), 1, N'Monday')
GO
INSERT [dbo].[CALENDARS] ([CALCODE], [CALDATE], [CALWORK], [CALNAME]) VALUES (N'BH', CAST(N'2016-01-19' AS Date), 1, N'Tuesday')
GO
INSERT [dbo].[CALENDARS] ([CALCODE], [CALDATE], [CALWORK], [CALNAME]) VALUES (N'BH', CAST(N'2016-01-20' AS Date), 1, N'Wednesday')
GO
INSERT [dbo].[CALENDARS] ([CALCODE], [CALDATE], [CALWORK], [CALNAME]) VALUES (N'BH', CAST(N'2016-01-21' AS Date), 1, N'Thursday')
GO
INSERT [dbo].[CALENDARS] ([CALCODE], [CALDATE], [CALWORK], [CALNAME]) VALUES (N'BH', CAST(N'2016-01-22' AS Date), 1, N'Friday')
GO
INSERT [dbo].[CALENDARS] ([CALCODE], [CALDATE], [CALWORK], [CALNAME]) VALUES (N'BH', CAST(N'2016-01-23' AS Date), 0, N'Saturday')
GO
INSERT [dbo].[CALENDARS] ([CALCODE], [CALDATE], [CALWORK], [CALNAME]) VALUES (N'BH', CAST(N'2016-01-24' AS Date), 0, N'Sunday')
GO
INSERT [dbo].[CALENDARS] ([CALCODE], [CALDATE], [CALWORK], [CALNAME]) VALUES (N'BH', CAST(N'2016-01-25' AS Date), 1, N'Monday')
GO
INSERT [dbo].[CALENDARS] ([CALCODE], [CALDATE], [CALWORK], [CALNAME]) VALUES (N'BH', CAST(N'2016-01-26' AS Date), 1, N'Tuesday')
GO
INSERT [dbo].[CALENDARS] ([CALCODE], [CALDATE], [CALWORK], [CALNAME]) VALUES (N'BH', CAST(N'2016-01-27' AS Date), 1, N'Wednesday')
GO
INSERT [dbo].[CALENDARS] ([CALCODE], [CALDATE], [CALWORK], [CALNAME]) VALUES (N'BH', CAST(N'2016-01-28' AS Date), 1, N'Thursday')
GO
INSERT [dbo].[CALENDARS] ([CALCODE], [CALDATE], [CALWORK], [CALNAME]) VALUES (N'BH', CAST(N'2016-01-29' AS Date), 1, N'Friday')
GO
INSERT [dbo].[CALENDARS] ([CALCODE], [CALDATE], [CALWORK], [CALNAME]) VALUES (N'BH', CAST(N'2016-01-30' AS Date), 0, N'Saturday')
GO
INSERT [dbo].[CALENDARS] ([CALCODE], [CALDATE], [CALWORK], [CALNAME]) VALUES (N'BH', CAST(N'2016-01-31' AS Date), 0, N'Sunday')
GO

Many thanks
Martyn

JamesK · August 30, 2019, 7:06pm

Assuming your week starts on a Sunday:

SELECT 
	*,
	CASE 
		WHEN CalWork = 1 THEN SUM(CASE WHEN CalWork=1 THEN 1 ELSE 0 END) OVER(ORDER BY CalDate) 
	END AS WrkDay,
	1 + SUM(CASE WHEN CalWork=1 THEN 1 ELSE 0 END) OVER() 
	-
	CASE 
		WHEN CalWork = 1 THEN SUM(CASE WHEN CalWork=1 THEN 1 ELSE 0 END) OVER(ORDER BY CalDate) 
	END AS Remain,
	CASE 
		WHEN CalWork = 1 THEN SUM(CASE WHEN CalWork=1 THEN 1 ELSE 0 END) OVER(PARTITION BY DATEDIFF(DAY,-1,CALDATE)/7) 
	END AS Wrktotal
FROM 
	dbo.Calendars
ORDER BY 
	caldate;

wembleybear · September 2, 2019, 1:43pm

I've just looked at this and it's almost there but not quite right. The first day of our working week is Monday. And when I look at the figures as I run the code taking Monday 22nd February 2016 as an example,

WRKDAY gives 36, when it should be 16 as 22/02/2016 is working day 16 in the month. Similarly with REMAIN, the code gives 216, when it should be 6 as there were 6 working days left in the month.

WRKTOTAL is correct however as this is for the week

I apprecate all help with this.

Many thanks
Martyn

JamesK · September 2, 2019, 9:45pm

For WrkDay and Remain calculations, change the
(ORDER BY CalDate)
to
(PARTITION BY YEAR(CalDate),MONTH(CalDate) ORDER BY CalDate)

For WrkTotal, if your week begins on Monday (rather than Sunday), change the
OVER(PARTITION BY DATEDIFF(DAY,-1,CALDATE)/7)
to
OVER(PARTITION BY DATEDIFF(DAY,0,CALDATE)/7)

wembleybear · September 4, 2019, 3:23pm

Thanks James, this works for me. I've managed to extend this to some other columns that I needed to get the figures for as well, so much appreciated.