logo
down
shadow

Date Condition with Case when Holiday exclude


Date Condition with Case when Holiday exclude

Content Index :

Date Condition with Case when Holiday exclude
Tag : sql , By : user150744
Date : January 12 2021, 09:11 PM

will help you If I guess correctly, You are looking to avoid Friday if it is start or end date of your date range. If I expect you have two parameter of datetime type @start_date_param and @end_date_param, this folllowing logic should work-
CASE 
    WHEN DATENAME(weekday,@start_date_param) = 'Friday' 
        THEN DATEADD(DD,1,@start_date_param)
    ELSE @start_date_param
END StartDate,
CASE 
    WHEN DATENAME(weekday,@end_date_param) = 'Friday' 
        THEN DATEADD(DD,-1,@end_date_param)
    ELSE @end_date_param
END EndDate

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

Use Python/Pandas indexed date as condition in holiday list


Tag : python , By : vbanos
Date : March 29 2020, 07:55 AM
this will help Here are some some thoughts...
Say you have a list of holidays for 2016:
cal = USFederalHolidayCalendar()
holidays = cal.holidays(start='2016-01-01', end='2016-12-31')
print holidays.size
10
idx = pd.DatetimeIndex(pd.date_range(start='2015-1-1', 
                                     end='2016-12-31', freq='30min'))
print idx.size
35041
idx[pd.DatetimeIndex(idx.date).isin(holidays.date)].size
480

Holiday Booking system, search for date - return a list of employees who are on holiday on that date


Tag : sql , By : lhoBas
Date : March 29 2020, 07:55 AM
around this issue I seem to be asking a lot of questions on here recently, thanks in advance for any help! , i think you can use between for this purpose:
    declare @InputDate date='2016-09-16' --yur input date

   select EmployeeId,
   case when (select count(1) from EmployeeLeave 
   where (@InputDate between StartDate and EndDate) 
   and EmployeeLeave .EmployeeId=Employee .EmployeeId)>1 then 'True' else 'False' end as [OnHoliday],
   case when ((select StartMidDay from EmployeeLeave 
   where (@InputDate between StartDate and EndDate) 
   and EmployeeLeave .EmployeeId=Employee .EmployeeId))=0.5 then 'True' else 'False' end as [IsHalf] ,
   from Employee 

Sql Server 2008 Exclude Company Holiday from DateDiff


Tag : sql-server-2008 , By : Tom Berthon
Date : March 29 2020, 07:55 AM
With these it helps After 2 case statements you can add below statement to subtract no of days from holiday Calendar table.
- (select ISNULL(count(1),0) from Cal where Cal.Holiday between ALERTS_CREATE_DT AND CreatedDate )

If date is a holiday, then add days to date until date is no holiday


Tag : python , By : kalfa
Date : March 29 2020, 07:55 AM
I wish did fix the issue. If day x is a holiday, then add days to day x until it is not a holiday. , You missed assigning and updating lastday.
def is_lastday_holiday(lastday):  
    while lastday in sorted(holidays.AT(years=2017)):  
        lastday = lastday + timedelta(days=1) 
    return lastday
lastday += timedelta(days=1)

Exclude weekends and public holiday for SQL datediff


Tag : sql , By : hammer_1968
Date : March 29 2020, 07:55 AM
may help you . Like has been mentioned, the best way is to create a Calendar Table and then use the Working Days column, or whatever it is you called it, to calculate the difference.
This is pseudo-SQL, in the absence of a working Calendar Table, but should get you there:
SELECT YT.{YourColumn},
       WD.WorkingDays
FROM YourTable YT
     CROSS APPLY (SELECT COUNT(CT.DateKeyColumn) -1 AS WorkingDays --Minus 1, as we don't want to include the first day
                  FROM CalendarTable CT
                  WHERE CT.[DateColumn] >= YT.StartingDateColumn
                    AND CT.[DateColumn] <= YT.EndingDateColumn
                    AND CT.WorkingDay = 1) WD
Related Posts Related QUESTIONS :
  • Postgresql: An alternative to subqueries to make the query more efficient?
  • Use returned values from a query in the same procedure
  • Sum all numeric columns in database and log results
  • Release item in current year
  • Conditional function not working as intended postgresql
  • SQL trigger, results not Grouped by
  • getting more records than expected with simple query in postgresql
  • get first occurrence of a certain combination of columns
  • Converting string timestamp to local-time zone in Oracle
  • Redshift - Filter out rows that are blank to left or right of a character
  • Teradata - Know the load volume by times of a table
  • How do I replace duplicate data with random data of same length in SQL
  • How to group by using pivot table
  • How can I achieve a Join for merging two tables FULL OUTER JOIN and avoid duplicates?
  • Multi Keyword search on multi-column sql
  • Computing the median of salaries under each manager in BigQuery SQL
  • How can insert value by selecting from another
  • Retrieving only the latest item for a set of data
  • update column with concatenating for that group only
  • SQL `SELECT 'some string value' AS fieldname` equivalent in Powershell?
  • How to determine the AVERAGE number of associated records
  • In Django, how can I build a set of criteria from a list?
  • Using replace() inside a loop
  • select user on create view
  • Calculate and aggregate column depending on a value in another colum (SQL)
  • Trigger to track all the insert or update on Oracle Table
  • Update column - remove first and last characters
  • T-SQL - Adding FK to table, make it do drop/null/efault automagically?
  • Get constraints and indexes on materialized views
  • SQL perform AVG after MAX
  • How do you join two tables A and B in order to get a non join column in A with data not in B?
  • Move Non-NULL Values Up Within Partition
  • Selecting rows based on two many-to-many relations
  • I have a table on which i want to do some advance calculation and get the table in a new shape
  • How to convert decimal to time in Oracle SQL?
  • Combine numbers to 1 row
  • Error: column does not exist when in fact it does? why?
  • How to fix: "Operation must use an updateable query" on update statement in Access VBA
  • Does information passes to a new table via CREATE TABLE update itself?
  • How to write a SQL statement which filters between two dates/times, with the date and time in separate columns
  • MS-Access-2016 Query Language 'ORDER BY' Clause
  • How to get totals for each age category?
  • get the first n characters of getdate()
  • Query to handle the dates
  • SQL grouping/counting on a string split function
  • ORDER BY and TOP statements in SQL works different on listing down the records and why?
  • SQL Server Computed Column: how to set it to NOT NULL, without persistence
  • Is nested select statement better than where statement?
  • Postgresql sequential scan slow performance on 500 million rows
  • Aggregate first order data to all orders - Microsoft SQL Server
  • Sorting comma delimited datasets in row
  • Question about the correct data structure
  • Showing two temp tables data in a single table
  • How can I find out the query execution time in Oracle SQL using Toad?
  • convert quarter number to month name in sql
  • Find max value from column that has a json object with key-value pairs
  • case statement based on 3 columns in 1 table
  • How to write a SQL query to select batsman wiht highest score than the previous year
  • Find Substring in SQL
  • Using VALUE to make temporary table
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com