locked
Sql Server Holidays Tables join RRS feed

  • Question

  • User197255166 posted

    Hi everyone,

    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)

    Saturday, June 29, 2019 3:30 PM

All replies

  • User-719153870 posted

    Hi Aliyilmaz,

    According to your description, I couldn’t understand your requirement clearly.

    Could you please post more details explanation about your table’s  structure and expected result exa,please?

    If you could post more details information, it will be more easily for us to understand the issue and find out the solution.

    Best Regard,

    Yang Shen

    Monday, July 1, 2019 8:26 AM
  • User197255166 posted

    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)
    --HAVING 
    --Sum(FM.txtHours) < @toplamSaat
    
    

    Thursday, July 11, 2019 4:48 PM
  • User197255166 posted

    Hi,

    Hi friends I have 3 tables in a query. They enter a user table, the working hours table, the holiday table.

    This query calculates the users who logged into the worksheet that week. Here, users should come first in the user table, then the calculation time will come if the working time is entered that week.

    If it never entered, 0 will come. I took care of the calculation.

    Only the part above. What kind of path should I follow here?

    Saturday, July 13, 2019 1:07 PM
  • User197255166 posted

    Hi,

    I created sample data. Also I shared my code.

    I want to make all employees come from the table of employees and eforttable table that week, if the data entered the total hours come. If the data is not entered that week, total hours 0.

    http://sqlfiddle.com/#!9/cf4da7/1

    Monday, July 15, 2019 7:40 PM