No of consecutive days -worked by employee

Hi Vishal,

Here is my full query

SELECT
VENDOR_NAME,VENDOR_ID,CNRCMSNUM CTR#,LF.CONTRACTNUM,LEMNUM,LINENUM,LF.ORIGLEMNUM,
PONUM,VENDREF,LF.STATUS,POSTATUS,LINESTATUS,LINETYPE,WORKDATE,LF.ENTERDATE,BUSINESSUNIT,OBJ,SUB,
BU.DESCRIPTION BU_DESCRIPTION,VALUESTREAM,REFWO,WODESC,LOCATION,LF.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

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

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

Hello Amit and Vishal

I am really sorry for late reply. 

I will try and let you know

Thanks lots

Rupal

Check this

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

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