No of consecutive days -worked by employee

Question

Hello to all I would like to calculate in DAX -no of consecutive days worked by employee. The last column in below table. SITEID Vendor_name Contractor Name Laborcode WORKDATE LaborHrs No of consecutive days Horizon ABC LTD AHMED A30011442 10/27/2022 12 1 Horizon ABC LTD AHMED A30011442 10/28/2022 12 2 Horizon ABC LTD AHMED A30011442 10/29/2022 12 3 Horizon ABC LTD AHMED A30011442 10/30/2022 6 4 Horizon ABC LTD AHMED A30011442 10/30/2022 6 4 Horizon ABC LTD AHMED A30011442 10/31/2022 12 5 Horizon ABC LTD AHMED A30011442 11/1/2022 8 6 Horizon ABC LTD David A30021329 11/30/2022 12 1 Horizon ABC LTD David A30021329 12/1/2022 12 2 Horizon ABC LTD David A30021329 12/2/2022 12 3 Horizon ABC LTD David A30021329 12/3/2022 12 4 Horizon ABC LTD David A30021329 12/4/2022 6 5 Horizon ABC LTD David A30021329 12/4/2022 -6 5 Horizon ABC LTD David A30021329 12/4/2022 8 5 Horizon ABC LTD David A30021329 12/5/2022 12 6 Horizon ABC LTD David A30021329 12/6/2022 8 7 Horizon ABC LTD David A30021329 12/14/2022 12 1 Horizon ABC LTD David A30021329 12/15/2022 12 2 Horizon ABC LTD David A30021329 12/16/2022 8 3 Horizon ABC LTD David A30021329 12/16/2022 4 3 Horizon ABC LTD David A30021329 12/17/2022 12 4 Horizon ABC LTD David A30021329 12/18/2022 12 5 Horizon ABC LTD David A30021329 12/19/2022 12 6 Horizon ABC LTD David A30021329 12/20/2022 8 7 Horizon ABC LTD David A30021329 12/28/2022 9 1 Horizon ABC LTD David A30021329 12/29/2022 12 2 Horizon ABC LTD David A30021329 12/30/2022 13 3 Horizon ABC LTD David A30021329 12/31/2022 12 4 Horizon ABC LTD David A30021329 1/1/2023 12 5 Horizon ABC LTD David A30021329 1/2/2023 12 6 Horizon ABC LTD David A30021329 1/3/2023 8 7 Horizon ABC LTD David A30021329 1/13/2023 12 1 Horizon ABC LTD David A30021329 1/14/2023 12 2 Albian XYZ Sony A30001729 10/1/2022 5.5 1 Albian XYZ Sony A30001729 10/2/2022 5.5 2 Albian XYZ Sony A30001729 10/3/2022 5.5 3 Albian XYZ Sony A30001729 10/4/2022 7 4 Albian XYZ Jager A30001600 10/6/2022 11.5 1 Albian XYZ Jager A30001600 10/7/2022 11.5 2 Albian XYZ Jager A30001600 10/8/2022 11.5 3 Albian XYZ Jager A30001600 10/9/2022 11.5 4 Albian XYZ Jager A30001600 10/10/2022 11.5 5 Albian XYZ Jager A30001600 10/11/2022 11.5 6 Albian XYZ Jager A30001600 10/12/2022 11 7 Albian XYZ Lee A30001665 11/25/2022 12 1 Albian XYZ Bear A30021315 12/15/2022 12 1 Albian XYZ Bear A30021315 12/16/2022 12 2 Albian XYZ Bear A30021315 12/17/2022 12 3 Albian XYZ Bear A30021315 12/18/2022 12 4 Albian XYZ Bear A30021315 12/19/2022 12 0 Albian XYZ Bear A30021315 12/19/2022 -12 0 Albian XYZ Bear A30021315 12/20/2022 12 1 Albian XYZ Bear A30021315 12/21/2022 10 2 Albian XYZ Bear A30021315 12/29/2022 12 1 Albian XYZ Bear A30021315 12/30/2022 12 2 Albian XYZ Bear A30021315 12/31/2022 12 3 Albian XYZ Bear A30021315 1/1/2023 12 4 Albian XYZ Bear A30021315 1/2/2023 12 5 Albian XYZ Bear A30021315 1/3/2023 12 6 Albian XYZ Bear A30021315 1/4/2023 10 7 Albian XYZ Bear A30021315 1/12/2023 12 1 Albian XYZ Bear A30021315 1/13/2023 12 2 Albian XYZ Bear A30021315 1/14/2023 12 3 Thanks

Amit Mohanty · Answer

Check this SELECT * DENSE_RANK() OVER (PARTITION BY ContractorName ORDER BY WorkDate) [No of consecutive days] FROM your_table_name

Vishal Joshi · Answer

Hello Rupal Please try below SQL query to get the expected result Select * ROW_NUMBER() over(partition by Contractor_Name order by Contractor_Name ) as [No of consecutive days] from Employe Please let me know if any query Thanks

Rupal Patel · Answer

Hi Vishal Here is my full query SELECT VENDOR_NAMEVENDOR_IDCNRCMSNUM CTR#LF.CONTRACTNUMLEMNUMLINENUMLF.ORIGLEMNUM PONUMVENDREFLF.STATUSPOSTATUSLINESTATUSLINETYPEWORKDATELF.ENTERDATEBUSINESSUNITOBJSUB BU.DESCRIPTION BU_DESCRIPTIONVALUESTREAMREFWOWODESCLOCATIONLF.LABORCODE CONTRACT_NO DENSE_RANK() OVER (PARTITION BY LF.LABORCODE ORDER BY LF.WORKDATE ASC) CONSECUTIVE_DAYS EMP.LASTNAME||' '||EMP.FIRSTNAME AS CONTRACTOR NAME LABORPOSITIONDESC LABORPAYCODE PA.DESCRIPTION PAYCODEDESC C.DESCRIPTION CRAFT_DESC ISLEM SKILLLEVEL LABORRATE LF.LABORISNIGHTSHIFT LABORISOFFSITE EMP.SWIPECARDNUM CNRLEMSHIFT CNRLEM2SSTARTDATE SHIFTSTARTDATE PRIMARYSHIFTNUM LABORHRS LABORLINECOST LF.LINECOST LF.VENDMEMO LF.MEMO TA.CLASS_STRCTR_DESC TA_DESC AREA LF.SITEID Q4.EAFTYPE PR.EMPLY_TYPE LF.CRAFT CASE LF.CRAFT WHEN 'BM' THEN 'DIRECT' WHEN 'OPS' THEN 'DIRECT' WHEN 'OP' THEN 'DIRECT' WHEN 'PF' THEN 'DIRECT' WHEN 'MW' THEN 'DIRECT' WHEN 'WLD' THEN 'DIRECT' WHEN 'SW' THEN 'DIRECT' WHEN 'WH' THEN 'DIRECT' WHEN 'LOSM' THEN 'DIRECT' WHEN 'MACH' THEN 'DIRECT' WHEN 'PIPL' THEN 'DIRECT' WHEN 'LH' THEN 'DIRECT' WHEN 'COM' THEN 'DIRECT' WHEN 'OPCR' THEN 'DIRECT' WHEN 'FM' THEN 'DIRECT' WHEN 'NDT' THEN 'DIRECT' WHEN 'CRP' THEN 'DIRECT' WHEN 'SUB' THEN 'DIRECT' WHEN 'DRV' THEN 'DIRECT' WHEN 'FP' THEN 'DIRECT' WHEN 'MH' THEN 'DIRECT' WHEN 'SPF' THEN 'DIRECT' WHEN 'AR' THEN 'DIRECT' WHEN 'PSE' THEN 'DIRECT' WHEN 'HTO' THEN 'DIRECT' WHEN 'PT' THEN 'DIRECT' WHEN 'CH' THEN 'DIRECT' WHEN 'PG' THEN 'DIRECT' WHEN 'LS' THEN 'DIRECT' WHEN 'HV' THEN 'DIRECT' WHEN 'INM' THEN 'DIRECT' WHEN 'FWT' THEN 'DIRECT' WHEN 'SCF' THEN 'DIRECT' WHEN 'LINE' THEN 'DIRECT' WHEN 'LB' THEN 'DIRECT' WHEN 'MGSP' THEN 'DIRECT' WHEN 'CM' THEN 'DIRECT' WHEN 'SBPT' THEN 'DIRECT' WHEN 'PLB' THEN 'DIRECT' WHEN 'PLS' THEN 'DIRECT' WHEN 'SB' THEN 'DIRECT' WHEN 'LD' THEN 'DIRECT' WHEN 'IRT' THEN 'DIRECT' WHEN 'WB' THEN 'DIRECT' WHEN 'FUM' THEN 'DIRECT' WHEN 'IWW' THEN 'DIRECT' WHEN 'GEN' THEN 'DIRECT' WHEN 'MECH' THEN 'DIRECT' WHEN 'CT' THEN 'DIRECT' WHEN 'INS' THEN 'DIRECT' WHEN 'AUTO' THEN 'DIRECT' WHEN 'EL' THEN 'DIRECT' WHEN 'AWO' THEN 'DIRECT' WHEN 'SHM' THEN 'DIRECT' WHEN 'TECH' THEN 'DIRECT' WHEN 'GAS' THEN 'DIRECT' WHEN 'RB' THEN 'DIRECT' WHEN 'PP' THEN 'DIRECT' WHEN 'INT' THEN 'DIRECT' WHEN 'SV' THEN 'DIRECT' WHEN 'BLDG' THEN 'DIRECT' WHEN 'TMJS' THEN 'DIRECT' WHEN 'PFW' THEN 'DIRECT' WHEN 'SF' THEN 'DIRECT' WHEN 'STEA' THEN 'DIRECT' WHEN 'WLDB' THEN 'DIRECT' WHEN 'NDE' THEN 'DIRECT' WHEN 'INEL' THEN 'DIRECT' WHEN 'FAB' THEN 'DIRECT' WHEN 'LBCO' THEN 'DIRECT' WHEN 'OPCO' THEN 'DIRECT' WHEN 'TIR' THEN 'DIRECT' WHEN 'CBT' THEN 'DIRECT' WHEN 'FO' THEN 'DIRECT' WHEN 'LBR' THEN 'DIRECT' WHEN 'IW' THEN 'DIRECT' WHEN 'SER' THEN 'DIRECT' WHEN 'BL' THEN 'DIRECT' WHEN 'ITG' THEN 'DIRECT' WHEN 'INMT' THEN 'DIRECT' WHEN 'MCL' THEN 'DIRECT' WHEN 'TEC' THEN 'DIRECT' WHEN 'MCH' THEN 'DIRECT' WHEN 'PRO' THEN 'DIRECT' WHEN 'EHT' THEN 'DIRECT' WHEN 'DIR' THEN 'DIRECT' WHEN 'DUAL' THEN 'DIRECT' WHEN 'SHOP' THEN 'DIRECT' WHEN 'DRP' THEN 'DIRECT' WHEN 'TC' THEN 'DIRECT' WHEN 'EQP' THEN 'DIRECT' WHEN 'PLT' THEN 'DIRECT' WHEN 'TCLR' THEN 'DIRECT' WHEN 'EST' THEN 'INDIRECT' WHEN 'INSP' THEN 'INDIRECT' WHEN 'SUPT' THEN 'INDIRECT' WHEN 'ST' THEN 'INDIRECT' WHEN 'DIS' THEN 'INDIRECT' WHEN 'PROC' THEN 'INDIRECT' WHEN 'QAQC' THEN 'INDIRECT' WHEN 'ADM' THEN 'INDIRECT' WHEN 'SCH' THEN 'INDIRECT' WHEN 'ERW' THEN 'INDIRECT' WHEN 'ENG' THEN 'INDIRECT' WHEN 'DC' THEN 'INDIRECT' WHEN 'DCP' THEN 'INDIRECT' WHEN 'PLN' THEN 'INDIRECT' WHEN 'PC' THEN 'INDIRECT' WHEN 'GF' THEN 'INDIRECT' WHEN 'ART' THEN 'INDIRECT' WHEN 'DM' THEN 'INDIRECT' WHEN 'DES' THEN 'INDIRECT' WHEN 'QEH' THEN 'INDIRECT' WHEN 'SUVCO' THEN 'INDIRECT' WHEN 'DT' THEN 'INDIRECT' WHEN 'INH' THEN 'INDIRECT' WHEN 'ANA' THEN 'INDIRECT' WHEN 'SYS' THEN 'INDIRECT' WHEN 'BUY' THEN 'INDIRECT' WHEN 'ADV' THEN 'INDIRECT' WHEN 'EIT' THEN 'INDIRECT' WHEN 'MG' THEN 'INDIRECT' WHEN 'IS' THEN 'INDIRECT' WHEN 'TRN' THEN 'INDIRECT' WHEN 'SPE' THEN 'INDIRECT' WHEN 'DSP' THEN 'INDIRECT' WHEN 'ACC' THEN 'INDIRECT' WHEN 'HP' THEN 'INDIRECT' WHEN 'CO' THEN 'INDIRECT' WHEN 'COT' THEN 'INDIRECT' WHEN 'TBA' THEN 'INDIRECT' WHEN 'PAF' THEN 'INDIRECT' WHEN 'PRG' THEN 'INDIRECT' WHEN 'SAP' THEN 'INDIRECT' WHEN 'AD' THEN 'INDIRECT' WHEN 'OPDT' THEN 'INDIRECT' WHEN 'HSE' THEN 'INDIRECT' WHEN 'SAF' THEN 'INDIRECT' WHEN 'TL' THEN 'INDIRECT' WHEN 'ENGL' THEN 'INDIRECT' WHEN 'CON' THEN 'INDIRECT' WHEN 'DE' THEN 'INDIRECT' WHEN 'DST' THEN 'INDIRECT' WHEN 'EAF' THEN 'INDIRECT' WHEN 'CLN' THEN 'INDIRECT' WHEN 'CST' THEN 'INDIRECT' WHEN 'JAN' THEN 'INDIRECT' WHEN 'DV' THEN 'INDIRECT' WHEN 'PUR' THEN 'INDIRECT' WHEN 'SUV' THEN 'INDIRECT' WHEN 'CRT' THEN 'INDIRECT' END AS DIRECT_INDIRECT FROM BI_HZ_ETL.LEM_FACTS LF LEFT JOIN BI_HZ_ETL.LEM_BU BU ON (BUSINESSUNIT=BU.BU) LEFT JOIN BI_HZ_ETL.LEM_EMP EMP ON (LF.LABORCODE = EMP.PERSONID) LEFT JOIN BI_HZ_ETL.MXM_CONTRACT CMS ON (LF.CONTRACTNUM=CMS.CONTRACTNUM) LEFT JOIN BI_HZ_ETL.LEM_CRAFT C ON (LF.CRAFT=C.CRAFT) LEFT JOIN BI_HZ_ETL.LEM_VENDOR V ON (LF.VENDOR=V.VENDOR_ID) LEFT JOIN BI_HZ_ETL.LEM_PAYCODE PA ON (LF.LABORPAYCODE=PA.PAYCODE) LEFT JOIN BI_HZ_ETL.MXM_CLASSSTRUCTURE TA ON (CLASSSTRUCTUREID= TA.CLASS_STRCTR_ID) LEFT JOIN BI_HZ_ETL.MXM_PERSON PR ON (LF.CONTRACTNUM=PR.EMPLY_NUM) LEFT JOIN (SELECT CONTRACTNUM LABORCODE EAFTYPE FROM BI_HZ_ETL.LEM_CNRLEMEAFRATE) Q4 ON LF.CONTRACTNUM=Q4.CONTRACTNUM AND LF.LABORCODE=Q4.LABORCODE WHERE LF.SITEID IN ('HORIZON''ALBIAN') AND LINETYPE IN('L') AND LF.STATUS 'DELETED' AND WORKDATE>CURRENT_DATE-50 Thanks for your time

Vishal Joshi · Answer

Hello Rupal Based on the primary question both queries should work. also checked locally. It would be helpful if you elaborate more and send the data with all columns so we can write the query accordingly to get it to work. Thanks

Rupal Patel · Answer

Hi Amit and Vishal I try both way but not working for me ROW_NUMBER() OVER (PARTITION BY lf.laborcode ORDER BY lf.WorkDate ASC) CONSECUTIVE_Days DENSE_RANK() OVER (PARTITION BY lf.laborcode ORDER BY lf.WorkDate ASC) CONSECUTIVE_Days on 25-Jan -is suppose to be start with 1 Please have a look for me Thanks for your time Rupal

Rupal Patel · Answer

Hello Amit and Vishal I am really sorry for late reply. I will try and let you know Thanks lots Rupal

SITEID	Vendor_name	Contractor Name	Laborcode	WORKDATE	LaborHrs	No of consecutive days
Horizon	ABC LTD	AHMED	A30011442	10/27/2022	12	1
Horizon	ABC LTD	AHMED	A30011442	10/28/2022	12	2
Horizon	ABC LTD	AHMED	A30011442	10/29/2022	12	3
Horizon	ABC LTD	AHMED	A30011442	10/30/2022	6	4
Horizon	ABC LTD	AHMED	A30011442	10/30/2022	6	4
Horizon	ABC LTD	AHMED	A30011442	10/31/2022	12	5
Horizon	ABC LTD	AHMED	A30011442	11/1/2022	8	6
Horizon	ABC LTD	David	A30021329	11/30/2022	12	1
Horizon	ABC LTD	David	A30021329	12/1/2022	12	2
Horizon	ABC LTD	David	A30021329	12/2/2022	12	3
Horizon	ABC LTD	David	A30021329	12/3/2022	12	4
Horizon	ABC LTD	David	A30021329	12/4/2022	6	5
Horizon	ABC LTD	David	A30021329	12/4/2022	-6	5
Horizon	ABC LTD	David	A30021329	12/4/2022	8	5
Horizon	ABC LTD	David	A30021329	12/5/2022	12	6
Horizon	ABC LTD	David	A30021329	12/6/2022	8	7
Horizon	ABC LTD	David	A30021329	12/14/2022	12	1
Horizon	ABC LTD	David	A30021329	12/15/2022	12	2
Horizon	ABC LTD	David	A30021329	12/16/2022	8	3
Horizon	ABC LTD	David	A30021329	12/16/2022	4	3
Horizon	ABC LTD	David	A30021329	12/17/2022	12	4
Horizon	ABC LTD	David	A30021329	12/18/2022	12	5
Horizon	ABC LTD	David	A30021329	12/19/2022	12	6
Horizon	ABC LTD	David	A30021329	12/20/2022	8	7
Horizon	ABC LTD	David	A30021329	12/28/2022	9	1
Horizon	ABC LTD	David	A30021329	12/29/2022	12	2
Horizon	ABC LTD	David	A30021329	12/30/2022	13	3
Horizon	ABC LTD	David	A30021329	12/31/2022	12	4
Horizon	ABC LTD	David	A30021329	1/1/2023	12	5
Horizon	ABC LTD	David	A30021329	1/2/2023	12	6
Horizon	ABC LTD	David	A30021329	1/3/2023	8	7
Horizon	ABC LTD	David	A30021329	1/13/2023	12	1
Horizon	ABC LTD	David	A30021329	1/14/2023	12	2
Albian	XYZ	Sony	A30001729	10/1/2022	5.5	1
Albian	XYZ	Sony	A30001729	10/2/2022	5.5	2
Albian	XYZ	Sony	A30001729	10/3/2022	5.5	3
Albian	XYZ	Sony	A30001729	10/4/2022	7	4
Albian	XYZ	Jager	A30001600	10/6/2022	11.5	1
Albian	XYZ	Jager	A30001600	10/7/2022	11.5	2
Albian	XYZ	Jager	A30001600	10/8/2022	11.5	3
Albian	XYZ	Jager	A30001600	10/9/2022	11.5	4
Albian	XYZ	Jager	A30001600	10/10/2022	11.5	5
Albian	XYZ	Jager	A30001600	10/11/2022	11.5	6
Albian	XYZ	Jager	A30001600	10/12/2022	11	7
Albian	XYZ	Lee	A30001665	11/25/2022	12	1
Albian	XYZ	Bear	A30021315	12/15/2022	12	1
Albian	XYZ	Bear	A30021315	12/16/2022	12	2
Albian	XYZ	Bear	A30021315	12/17/2022	12	3
Albian	XYZ	Bear	A30021315	12/18/2022	12	4
Albian	XYZ	Bear	A30021315	12/19/2022	12	0
Albian	XYZ	Bear	A30021315	12/19/2022	-12	0
Albian	XYZ	Bear	A30021315	12/20/2022	12	1
Albian	XYZ	Bear	A30021315	12/21/2022	10	2
Albian	XYZ	Bear	A30021315	12/29/2022	12	1
Albian	XYZ	Bear	A30021315	12/30/2022	12	2
Albian	XYZ	Bear	A30021315	12/31/2022	12	3
Albian	XYZ	Bear	A30021315	1/1/2023	12	4
Albian	XYZ	Bear	A30021315	1/2/2023	12	5
Albian	XYZ	Bear	A30021315	1/3/2023	12	6
Albian	XYZ	Bear	A30021315	1/4/2023	10	7
Albian	XYZ	Bear	A30021315	1/12/2023	12	1
Albian	XYZ	Bear	A30021315	1/13/2023	12	2
Albian	XYZ	Bear	A30021315	1/14/2023	12	3