February 5, 2017 at 12:04 am
one employee take leave from
1) start leave No.1 from 1/12/2014 - 31/01/2015
2) start leave No.2 from 15/12/2015 - 15/01/2016
how many days between 1/1/2015 - 31/12/2015
select emp_code, startday,returnday,actualday from tb1
emp_code startday returnday actualday
101 null null 0
102 1/12/2014 31/01/2015 31
102 15/12/2015 15/01/2016 31
103 null null 0
the answer should become 48 days
emp_code days2015
101 0
102 48
103 0
I hope to find a solution here in this great Forum
February 5, 2017 at 3:38 am
Here is a quick suggestion towards a solution that should get you passed this hurdle
😎
USE TEEST;
GO
SET NOCOUNT ON;
--https://www.sqlservercentral.com/Forums/1856286/Defferance-Count-days-within-one-year
IF OBJECT_ID(N'dbo.TBL_SAMPLE_DATE') IS NOT NULL DROP TABLE dbo.TBL_SAMPLE_DATE;
CREATE TABLE dbo.TBL_SAMPLE_DATE
(
SD_ID INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_DBO_TBL_SAMPLE_DATE_SD_ID PRIMARY KEY CLUSTERED
,emp_code INT NOT NULL
,startday DATE NULL
,returnday DATE NULL
,actualday INT NULL
);
INSERT INTO dbo.TBL_SAMPLE_DATE (emp_code,startday,returnday,actualday)
VALUES (101,null ,null ,0 )
,(102,'20141201','20150131',31)
,(102,'20151215','20160115',31)
,(103,null ,null ,0 )
;
;WITH T(N) AS (SELECT N FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) AS X(N))
,BASE_CALENDAR_CONFIG AS
(
SELECT
MIN(T.startday) AS FIRST_DATE
,DATEDIFF(DAY,MIN(T.startday),MAX(returnday)) AS NUM_DAYS
FROM dbo.TBL_SAMPLE_DATE T
)
,NUMS(N) AS (SELECT TOP((SELECT BCC.NUM_DAYS FROM BASE_CALENDAR_CONFIG BCC) + 1) ROW_NUMBER() OVER (ORDER BY (@@VERSION)) - 1 AS N
FROM T T1,T T2,T T3,T T4,T T5,T T6,T T7,T T8,T T9)
,INLINE_CALENDAR AS
(
SELECT
NM.N AS DATE_NO
,DATEADD(DAY,NM.N,BC.FIRST_DATE) AS DATE_VAL
FROM BASE_CALENDAR_CONFIG BC
CROSS APPLY NUMS NM
)
,EMP_LIST AS
(
SELECT
SD.emp_code
FROM dbo.TBL_SAMPLE_DATE SD
GROUP BY SD.emp_code
)
,YEAR_LIST AS
(
SELECT
YEAR(IC.DATE_VAL) AS [YEAR]
FROM INLINE_CALENDAR IC
GROUP BY YEAR(IC.DATE_VAL)
)
,DATES_IN_YEAR AS
(
SELECT
T.emp_code
,YEAR(INC.DATE_VAL) AS [YEAR]
,SUM(CONVERT(INT,1 - SIGN(1 + SIGN((DATEDIFF(DAY,0,INC.DATE_VAL) % 7) - 5)),0)) AS NO_WORKDAY
,COUNT(*) AS NO_DAY
FROM dbo.TBL_SAMPLE_DATE T
OUTER APPLY INLINE_CALENDAR INC
WHERE INC.DATE_VAL BETWEEN T.startday AND T.returnday
GROUP BY T.emp_code
,YEAR(INC.DATE_VAL)
)
SELECT
EL.emp_code
,YL.YEAR
,ISNULL(DIY.NO_DAY ,0) AS NO_DAY
,ISNULL(DIY.NO_WORKDAY,0) AS NO_WORKDAY
FROM EMP_LIST EL
CROSS JOIN YEAR_LIST YL
LEFT OUTER JOIN DATES_IN_YEAR DIY
ON EL.emp_code = DIY.emp_code
AND YL.[YEAR] = DIY.[YEAR]
ORDER BY EL.emp_code ASC
,YL.YEAR ASC
;
Output
emp_code YEAR NO_DAY NO_WORKDAY
----------- ----------- ----------- -----------
101 2014 0 0
101 2015 0 0
101 2016 0 0
102 2014 31 23
102 2015 48 35
102 2016 15 11
103 2014 0 0
103 2015 0 0
103 2016 0 0
February 5, 2017 at 8:24 am
Thanks a lot Mr Eirikur Eiriksson
The table and columns oready i have
No need creat any table or columns
Only query
It's possible or no
Thanks in advance
February 5, 2017 at 12:08 pm
ALDHEEB - Sunday, February 5, 2017 8:24 AMThanks a lot Mr Eirikur EirikssonThe table and columns oready i haveNo need creat any table or columns Only query It's possible or noThanks in advance
The table creation was simply to generate test data. Go back and look at the code with that in mind and see how to apply it to your situation without creating a test table.
For future articles, please provide such readily consumable test data. See the first link under "Helpful Links" in my signature line for how to do that properly.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply