Today’s Query

Today’s Query

const tsumTodaysQuery = `
    select SUBSTRING (cast(tsum_tot_travel_time as varchar(8)),1,2) + ' H :'+SUBSTRING (cast(tsum_tot_travel_time as varchar(8)),4,2)
    + ' M :'+SUBSTRING (cast(tsum_tot_travel_time as varchar(8)),7,2) + ' S ' as tsum_tot_travel_time ,
    SUBSTRING (cast(tsum_tot_task_time as varchar(8)),1,2) + ' H :'+ SUBSTRING (cast(tsum_tot_task_time as varchar(8)),4,2)
    + ' M :'+ SUBSTRING (cast(tsum_tot_task_time as varchar(8)),7,2) + ' S ' as tsum_tot_task_time ,
    SUBSTRING (cast(tsum_tot_wrk_time as varchar(8)),1,2) + ' H :'+ SUBSTRING (cast(tsum_tot_wrk_time as varchar(8)),4,2)
    + ' M :'+ SUBSTRING (cast(tsum_tot_wrk_time as varchar(8)),7,2) + ' S ' as tsum_tot_wrk_time ,
    SUBSTRING (cast(tsum_tot_unprod_wrk_time as varchar(8)),1,2) + ' H :'+ SUBSTRING (cast(tsum_tot_unprod_wrk_time as varchar(8)),4,2)
    + ' M :'+ SUBSTRING (cast(tsum_tot_unprod_wrk_time as varchar(8)),7,2) + ' S ' as tsum_tot_unprod_wrk_time ,
    SUBSTRING (cast(tsum_tot_task_time as varchar(8)),1,2) + ' H :' + SUBSTRING (cast(tsum_tot_task_time as varchar(8)),4,2)
    + ' M :'+ SUBSTRING (cast(tsum_tot_task_time as varchar(8)),7,2) + ' S ' as tsum_tot_task_time
    FROM task_summary where tsum_date = CAST(getdate() AS date)
    and tsum_owner = '${ReqData.user.usr_id}' and tsum_isActive = 1 and tsum_domian = '${ReqData.user.usr_domain}'
    `;

Monthly Query

const tsumMonthlyQuery = `
    SELECT tsum_owner,
    CAST(FORMAT((SUM((DATEPART("ss",tsum_tot_task_time) + DATEPART("mi",tsum_tot_task_time) * 60 + DATEPART("hh",tsum_tot_task_time) * 3600)) / 3600),'00') as varchar(max)) + ' H : ' +
    CAST(FORMAT((SUM((DATEPART("ss",tsum_tot_task_time) + DATEPART("mi",tsum_tot_task_time) * 60 + DATEPART("hh",tsum_tot_task_time) * 3600)) % 3600 / 60),'00') as varchar(max)) + ' M : ' +
    CAST(FORMAT((SUM((DATEPART("ss",tsum_tot_task_time) + DATEPART("mi",tsum_tot_task_time) * 60 + DATEPART("hh",tsum_tot_task_time) * 3600)) % 3600 % 60),'00') as varchar(max)) + ' S' as tsum_tot_task_time
   ,CAST(FORMAT((SUM((DATEPART("ss",tsum_tot_wrk_time) + DATEPART("mi",tsum_tot_wrk_time) * 60 + DATEPART("hh",tsum_tot_wrk_time) * 3600)) / 3600),'00') as varchar(max)) + ' H :' +
    CAST(FORMAT((SUM((DATEPART("ss",tsum_tot_wrk_time) + DATEPART("mi",tsum_tot_wrk_time) * 60 + DATEPART("hh",tsum_tot_wrk_time) * 3600)) % 3600 / 60),'00') as varchar(max)) + ' M :' +
    CAST(FORMAT((SUM((DATEPART("ss",tsum_tot_wrk_time) + DATEPART("mi",tsum_tot_wrk_time) * 60 + DATEPART("hh",tsum_tot_wrk_time) * 3600)) % 3600 % 60),'00') as varchar(max)) + ' S' as tsum_tot_wrk_time
   ,CAST(FORMAT((SUM((DATEPART("ss",tsum_tot_travel_time) + DATEPART("mi",tsum_tot_travel_time) * 60 + DATEPART("hh",tsum_tot_travel_time) * 3600)) / 3600),'00') as varchar(max)) + ' H :' +
    CAST(FORMAT((SUM((DATEPART("ss",tsum_tot_travel_time) + DATEPART("mi",tsum_tot_travel_time) * 60 + DATEPART("hh",tsum_tot_travel_time) * 3600)) % 3600 / 60),'00') as varchar(max)) + ' M :' +
    CAST(FORMAT((SUM((DATEPART("ss",tsum_tot_travel_time) + DATEPART("mi",tsum_tot_travel_time) * 60 + DATEPART("hh",tsum_tot_travel_time) * 3600)) % 3600 % 60),'00') as varchar(max)) + ' S' as tsum_tot_travel_time
    ,CAST(FORMAT((SUM((DATEPART("ss",tsum_tot_unprod_wrk_time) + DATEPART("mi",tsum_tot_unprod_wrk_time) * 60 + DATEPART("hh",tsum_tot_unprod_wrk_time) * 3600)) / 3600),'00') as varchar(max)) + ' H :' +
    CAST(FORMAT((SUM((DATEPART("ss",tsum_tot_unprod_wrk_time) + DATEPART("mi",tsum_tot_unprod_wrk_time) * 60 + DATEPART("hh",tsum_tot_unprod_wrk_time) * 3600)) % 3600 / 60),'00') as varchar(max)) + ' M :' +
    CAST(FORMAT((SUM((DATEPART("ss",tsum_tot_unprod_wrk_time) + DATEPART("mi",tsum_tot_unprod_wrk_time) * 60 + DATEPART("hh",tsum_tot_unprod_wrk_time) * 3600)) % 3600 % 60),'00') as varchar(max)) + ' S' as tsum_tot_unprod_wrk_time
    FROM task_summary where tsum_date between CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(getdate())-1),getdate()),101) and CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,getdate()))), DATEADD(mm,1,getdate())),101)
    and tsum_owner = '${ReqData.user.usr_id}' and tsum_isActive =1 and tsum_domian='${ReqData.user.usr_domain}' group by tsum_owner
    `;

---

SELECT
    CAST(FORMAT((SUM((DATEPART("ss",tsum_tot_task_time) + DATEPART("mi",tsum_tot_task_time) * 60 + DATEPART("hh",tsum_tot_task_time) * 3600)) / 3600),'00') as varchar(max)) + ' H : ' +
    CAST(FORMAT((SUM((DATEPART("ss",tsum_tot_task_time) + DATEPART("mi",tsum_tot_task_time) * 60 + DATEPART("hh",tsum_tot_task_time) * 3600)) % 3600 / 60),'00') as varchar(max)) + ' M : ' +
    CAST(FORMAT((SUM((DATEPART("ss",tsum_tot_task_time) + DATEPART("mi",tsum_tot_task_time) * 60 + DATEPART("hh",tsum_tot_task_time) * 3600)) % 3600 % 60),'00') as varchar(max)) + ' S' as tsum_tot_task_time,
    CAST(FORMAT((SUM((DATEPART("ss",tsum_tot_wrk_time) + DATEPART("mi",tsum_tot_wrk_time) * 60 + DATEPART("hh",tsum_tot_wrk_time) * 3600)) / 3600),'00') as varchar(max)) + ' H :' +
    CAST(FORMAT((SUM((DATEPART("ss",tsum_tot_wrk_time) + DATEPART("mi",tsum_tot_wrk_time) * 60 + DATEPART("hh",tsum_tot_wrk_time) * 3600)) % 3600 / 60),'00') as varchar(max)) + ' M :' +
    CAST(FORMAT((SUM((DATEPART("ss",tsum_tot_wrk_time) + DATEPART("mi",tsum_tot_wrk_time) * 60 + DATEPART("hh",tsum_tot_wrk_time) * 3600)) % 3600 % 60),'00') as varchar(max)) + ' S' as tsum_tot_wrk_time,
    CAST(FORMAT((SUM((DATEPART("ss",tsum_tot_travel_time) + DATEPART("mi",tsum_tot_travel_time) * 60 + DATEPART("hh",tsum_tot_travel_time) * 3600)) / 3600),'00') as varchar(max)) + ' H :' +
    CAST(FORMAT((SUM((DATEPART("ss",tsum_tot_travel_time) + DATEPART("mi",tsum_tot_travel_time) * 60 + DATEPART("hh",tsum_tot_travel_time) * 3600)) % 3600 / 60),'00') as varchar(max)) + ' M :' +
    CAST(FORMAT((SUM((DATEPART("ss",tsum_tot_travel_time) + DATEPART("mi",tsum_tot_travel_time) * 60 + DATEPART("hh",tsum_tot_travel_time) * 3600)) % 3600 % 60),'00') as varchar(max)) + ' S' as tsum_tot_travel_time,
    CAST(FORMAT((SUM((DATEPART("ss",tsum_tot_unprod_wrk_time) + DATEPART("mi",tsum_tot_unprod_wrk_time) * 60 + DATEPART("hh",tsum_tot_unprod_wrk_time) * 3600)) / 3600),'00') as varchar(max)) + ' H :' +
    CAST(FORMAT((SUM((DATEPART("ss",tsum_tot_unprod_wrk_time) + DATEPART("mi",tsum_tot_unprod_wrk_time) * 60 + DATEPART("hh",tsum_tot_unprod_wrk_time) * 3600)) % 3600 / 60),'00') as varchar(max)) + ' M :' +
    CAST(FORMAT((SUM((DATEPART("ss",tsum_tot_unprod_wrk_time) + DATEPART("mi",tsum_tot_unprod_wrk_time) * 60 + DATEPART("hh",tsum_tot_unprod_wrk_time) * 3600)) % 3600 % 60),'00') as varchar(max)) + ' S' as tsum_tot_unprod_wrk_time
    FROM task_summary
    join User_mstr  on usr_id=tsum_owner  and usr_manager ='0050I000008apzsQAA'
    where tsum_date between '2020-11-18' and '2020-11-18'
    and   tsum_isActive =1 and tsum_domian='D001'

---

select TODATETIMEOFFSET(GETDATE(),'+02:00')