Sql Server Holidays Tables join

Last Reply 3 days ago By pandeyism

Posted 18 days ago

Hi everyone,

At least I'd appreciate it if you could give me some ideas.

this job will work every Monday at first. He's gonna check up last week. conditions below, for example

x users - at week 23 - 8 hours of data entered - TotalWatch 40 - 8 - 8 = 24 hours of data if that week coincides with April 23(holiday table).

If x user - also in the 23rd week - no data entered - If that week is equalizing April 23 Total Time 40 - 8 = 32 hours need to enter data.

If x user - also in week 23 - no data entered - that week no holidays, ie Total Time 40 = 40 hours.

 

declare @basla datetime;
declare @bitis datetime;
declare @ToplamSaat int;

set @basla = (SELECT DATEADD(wk, 0, DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0,GETDATE()))))-7
set @bitis = (SELECT DATEADD(DAY,0,(DATEADD(wk, 1, DATEADD(DAY, 0-DATEPART(WEEKDAY, GETDATE()), DATEDIFF(dd, 0, GETDATE()))))))-7
set @ToplamSaat = 40;

with FMTablo(cbEmployee_TEXT,cbEmployee) as
(
Select
PC.cbEmployee_TEXT, PC.cbEmployee FROM E_DT_EforGiris_F_PortfoyCalisan PC

INNER JOIN OSUSERS O ON (PC.cbEmployee = O.ID)

INNER JOIN OSMANAGERS OS ON (OS.USERID = O.ID)

INNER JOIN OSUSERS OS2 ON (OS.MANAGERUSERID = OS2.ID)


group by PC.cbEmployee_TEXT, PC.cbEmployee
)

SELECT

FM.txtEffortOwner_TEXT [Kaynak],

Sum(FM.txtHours) [Saat],

datepart(wk,FM.txtEffortDate) Hafta,


datepart(wk, @basla) [HaftaBasi],

datepart(wk, @bitis) [HaftaSonu]

FROM


E_DT_EforGiris_F_EforGiris F

INNER JOIN E_DT_EforGiris_F_EforGiris_dtyEffort D ON (F.ID=D.FORMID)

INNER JOIN E_DT_EforGiris_F_EforModal FM ON (D.DOCUMENTID=FM.ID)

INNER JOIN FMTablo FMS ON (FMS.cbEmployee_TEXT = F.txtEffortOwner_TEXT)

INNER JOIN DOCUMENTS DO ON (DO.ID=F.ID AND DO.DELETED=0)

INNER JOIN LIVEFLOWS LF ON (DO.OWNERPROCESSID=LF.ID AND LF.DELETED=0 AND LF.STATUS=20)

INNER JOIN DOCUMENTS DC ON (DC.ID=FM.ID AND DC.DELETED=0)


INNER JOIN E_P_Parameters_F_Holidays P ON P.cmbYear_TEXT = year(GETDATE())

INNER JOIN E_P_Parameters_F_Holidays_dtyHolidays D2 ON P.ID=D2.FORMID

INNER JOIN E_P_Parameters_F_HolidaysModal F2 ON D2.DOCUMENTID=F2.ID --and @basla IN (F2.txtStartDate)


Where year(FM.txtEffortDate) = 2019 --and DATEPART(wk,FM.txtEffortDate) = DATEPART(wk,@basla)

Group by

FM.txtEffortOwner_TEXT,
--FM.txtHours,
datepart(wk,FM.txtEffortDate)

 

You are viewing reply posted by: AliYilmaz 8 days ago.
Posted 8 days ago

Hi,

Hello I want to do the users on the left side of the table, I want to get the other queries to the right side. This table F_PortfoyCalisan

 

declare @currentDate datetime = GETDATE()

declare @basla datetime;
declare @bitis datetime;
declare @toplamSaat int;

set @basla = (SELECT dateadd(hour, 12, DATEADD(wk, 0, DATEADD(DAY, 1-DATEPART(WEEKDAY, @currentDate), DATEDIFF(dd, 0,@currentDate)))))-6
set @bitis = (SELECT dateadd(hour, 12, DATEADD(DAY,0,(DATEADD(wk, 1, DATEADD(DAY, 0-DATEPART(WEEKDAY, @currentDate), DATEDIFF(dd, 0, @currentDate)))))))-7
set @toplamSaat = 0;


declare @currentDay datetime;
declare @i int = 0
declare @halfDayUsedBefore bit = 0
WHILE @i < 5
BEGIN
	set @currentDay = DATEADD(DAY, @i, @basla)
	
	declare @holidayId bigint = 0
	declare @isHalfDay bit = 0
	select top 1 @holidayId = ID from E_P_Parameters_F_HolidaysModal where @currentDay between txtStartDate and txtFinishDate
	if (@holidayId > 0)
	begin
		if (@halfDayUsedBefore = 0)
		begin
			select @isHalfDay = chcYarim from E_P_Parameters_F_HolidaysModal where ID = @holidayId and CONVERT(date, @currentDay) = CONVERT(date, txtStartDate)

			if (@isHalfDay = 1)
			begin
				set @toplamSaat = @toplamSaat + 4
				set @halfDayUsedBefore = 1
			end
		end		
	end
	else
	begin 
		set @toplamSaat = @toplamSaat + 8
	end

	set @i = @i + 1

END;



SELECT                               

PO.cbEmployee_TEXT [Kaynak],

Sum(FM.txtHours) [Saat],

datepart(wk,FM.txtEffortDate) Hafta, 


datepart(wk, @basla) [HaftaBasi],

datepart(wk, @bitis) [HaftaSonu], 

case when Sum(FM.txtHours) < @toplamSaat then 1 else 0 end as [EksikVarmi],

@toplamSaat as [Toplam Saat]


FROM 


E_DT_EforGiris_F_EforGiris F

INNER JOIN                   E_DT_EforGiris_F_EforGiris_dtyEffort D ON (F.ID=D.FORMID)

INNER JOIN                   E_DT_EforGiris_F_EforModal FM ON (D.DOCUMENTID=FM.ID)

RIGHT JOIN					 E_DT_EforGiris_F_PortfoyCalisan PO ON (PO.cbEmployee_TEXT = FM.txtEffortOwner_TEXT)

LEFT JOIN					OSUSERS O ON (PO.cbEmployee = O.ID) 

LEFT JOIN                   OSMANAGERS OS ON (OS.USERID = O.ID)

LEFT JOIN                   OSUSERS OS2 ON (OS.MANAGERUSERID = OS2.ID)

INNER JOIN                   DOCUMENTS DO ON (DO.ID=F.ID AND DO.DELETED=0)

INNER JOIN                   LIVEFLOWS LF ON (DO.OWNERPROCESSID=LF.ID AND LF.DELETED=0 AND LF.STATUS=20)

INNER JOIN                  DOCUMENTS DC ON (DC.ID=FM.ID AND DC.DELETED=0)


Where year(FM.txtEffortDate) = YEAR(@basla) --and DATEPART(wk,FM.txtEffortDate) = DATEPART(wk,@basla) 
Group by

datepart(wk,FM.txtEffortDate)