SQL Help - How To Figure Out Consecutive Workdays With Holidays
Jul 18, 2006
Hi everyone,
I'm hoping someone can help me with some sql statements. I have a temp table that contains 30 dates that a student has missed in the last year. I also have a holiday table of when training was not available. I want to find out if there are 6 consecutive days missed excluding weekends and holidays (from the holiday table). I know this is some nasty looping statement but I can't get my brain around it.
I would like do this in a stored proc but I could use C# if necessary.
I use the NETWORKDAYS(start_date, end_date,holidays) function in Excel and Access regularly. I'm trying to find a similar function in TSQL to use on Server2005. NetWorkDays allows me to calculate the difference between my ticket open and close dates while excluding weekends and holidays, (we are nice to our staff and let them off).
I'm trying to find an easy way to do this in TSQL without writing a large amount of code.
I use OPENROWSET to read values from Excel and store them in a SQL Server table. In the Excel file I have a row having format 'Number' with two decimal places.
Example: 1225000.00
When I select this value using SSMS I get the correct value:
1225000
Strange enough, I cannot see the decimals anymore. However, when I now store this value into my table and then select it from there I get: (the datatype in the table is VARCHAR(max))
1.225e+006
I would not care if I could convert this back to a numeric datatype but this seems not to work: CAST('1.225e+006' as INT) throws an exception. Obviously OPENROWSET sends the data strictly as a character string. Storing this into varchar(max) works for small figures but it starts to use exp values for big figures.
Does anybody has an idea how to bring huge Excel based figures safely into a MS SQL Table ?
Hello, I have a query that returns a daily revenue figure. The query is as follows:
SELECT top 1000 ds.AcctCode, ds.TxnDate, SUM(isnull(ds.FuelFee,0)) + SUM(isnull(ds.CashFee,0)) + SUM(isnull(ds.ScFee,0)) AS TotalDailyRevenue, --"MTD" = ?, --"YDT" = ?, ps.TC, CASE WHEN ps.Proj = 100 THEN 'New Account' WHEN ps.Proj = 200 THEN 'Current Account' END AS ProjStatus, ps.FSR, ps.SubmitRep1
FROM TxnRptg.dbo.tbl_DailySummary ds INNER JOIN SalesData.dbo.tbl_CYProcessedSales ps ON ds.AcctCode = ps.Acct
WHERE MONTH(ds.TxnDate) = 1 AND Proj IN (100,200) AND TC = 'HV'
GROUP BY ds.AcctCode, ds.TxnDate, ps.TC, ps.Proj, ps.FSR, ps.SubmitRep1
ORDER BY ds.AcctCode, ds.TxnDate
--*********************************
TxnDate represents a single day of the month. How can I include MTD so that the dates for the revenue total are from DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) (beginning of current month) to TxnDate, and YTD so that the revenue totals are from DATEADD(yy, DATEDIFF(yy,0,getdate()), 0) (beginning of the current year) to TxnDate?
Hi, I have a table with 2 columns, a datetime column (date) and a bit column (workday) This is a production calender. It's filled with all dates till 2020/12/31
I need a query to find the date of the n-th workday given a specific start date. Using Sql Server 2000.
For example Date Workday -------------------- 2007/11/01 0 2007/11/02 0 2007/11/03 1 2007/11/04 1 2007/11/05 0 2007/11/06 1 2007/11/07 1
Given the date 2007/11/03, the 2nd workday would be 2007/11/06
This is one way you can add a number of workdays to a specific date and return a workday (monday-friday).CREATE FUNCTION [dbo].[fnAddWorkdays] ( @StartDate DATETIME, @DaysToAdd INT ) RETURNS DATETIME AS BEGIN RETURN@StartDate + CASE(@@DATEFIRST + (DATEPART(WEEKDAY, @StartDate) - 2)) % 7 WHEN 5 THEN 2 WHEN 6 THEN 1 ELSE 0 END + @DaysToAdd / 5 * 7 + @DaysToAdd % 5 + CASE WHEN (@@DATEFIRST + (DATEPART(WEEKDAY, @StartDate + CASE (@@DATEFIRST + (DATEPART(WEEKDAY, @StartDate) - 2)) % 7 WHEN 5 THEN 2 WHEN 6 THEN 1 ELSE 0 END) - 2)) % 7 + @DaysToAdd % 5 > 4 THEN 2 ELSE 0 END END
Hello In my ASP 2.0 website, I wish to arrange holidays and specific days from database with using Sql Server. What SQL query I must add to this following code? If you can post the required codes, I'd be most thankful. Regards.private DateTime _siparis;public DateTime Siparis { get { return _siparis; }set { _siparis = value; } }protected void Button1_Click(object sender, EventArgs e) {Timer1.Enabled = true; DateTime sonTarih = DateTime.Today.AddDays(1).AddHours(13);Session["siparis"] = sonTarih; TimeSpan aradakiFark = sonTarih - DateTime.Now;StringBuilder sb = new StringBuilder(); sb.Append("Remaining time is ");if (aradakiFark.Days > 0) { sb.Append(aradakiFark.Days.ToString());sb.Append(" days "); }if (aradakiFark.Hours > 0) { sb.Append(aradakiFark.Hours.ToString());sb.Append(" hours "); }if (aradakiFark.Minutes > 0) { sb.Append(aradakiFark.Minutes.ToString());sb.Append(" minutes "); } lblSure.Text = sb.ToString(); }protected void Timer1_Tick(object sender, EventArgs e) {DateTime sonTarih = DateTime.Today.AddDays(1).AddHours(13); TimeSpan aradakiFark = (DateTime)Session["siparis"] - DateTime.Now;StringBuilder sb = new StringBuilder(); sb.Append("Remaining time is ");if (aradakiFark.Days > 0) { sb.Append(aradakiFark.Days.ToString());sb.Append(" days "); }if (aradakiFark.Hours > 0) { sb.Append(aradakiFark.Hours.ToString());sb.Append(" hours"); }if (aradakiFark.Minutes > 0) { sb.Append(aradakiFark.Minutes.ToString());sb.Append(" minutes "); } lblSure.Text = sb.ToString(); } }
I've tried a select query that uses getdate and returns me to the manually arranged holidays in the Holidays table, but I've failed. I wish to do that, this query arranges holidays and adds +1 day on that holiday, if this one day added day is a holiday again, than adds one day again. How can I write this code? Thanks.
Hi!I have a large table in SQL Server 2000 with a datetime-column 'dt'. I wantto select all rows from that table, excluding days which fall on holidays orweekends. What is the best way to accomplish this? I considered creating anew table called "holidays" and then selecting all rows (sort of "where notin (select * from holidays)") , but I was looking for a better solutionsince that implies that I have to populate the "holidays" table.Suggestions are welcome!Sincerely,Nils Magnus Englund
In my ASP 2.0 website, I wish to arrange holidays and specific days from database with using Sql Server. What SQL query I must add to this following code? If you can post the required codes, I'd be most thankful. Regards.
Hi, I'm working on a helpdesk project and I require the calculation of the holidays. I need to get the time difference of the assigned date and the solved date of the helpdesk tickets considering the week-end holidays and statutory holidays. Is there any possible way to do this. I need something similar to the NetworkDays function in excel. Thanks. Madhavi.
First things first - I have a table which holds employee information (tbl_EmployeeDetails), and another table which holds information about the holidays they have booked (tbl_Holidays).
If an employee books 5 days off, 5 rows will appear in the tbl_Holidays table, each line showing 1 under the day field and 7 under the hours field.
I'm trying to produce a function which will do the following :
1) Pass in the employee number 2) Establish whether the employee works full time or part time by looking up to tbl_employeedetails, and checking the fulltime flag 3) If full time, look up to tbl_Holidays and count number of days 4) If part time, look up to tbl_Holidays and count number of hours 5) After this, return the number of holidays booked
My code is as follows : ============= CREATE FUNCTION [dbo].[fn_Get_Booked_Holidays_Current_Year] ( @EmpNo int )
RETURNS Float AS BEGIN
-- Declare fields DECLARE @FullTime int
-- Determine if Part Time or Full Time SET @FullTime = SELECT FullTime FROM tbl_EmployeeDetails
IF @FullTime = 1 SELECT COUNT(NumberOfDays) AS TotalHolidays, EmployeeNumber AS EERef FROM dbo.tbl_Holidays GROUP BY EmployeeNumber HAVING (EmployeeNumber = @EmpNo)
IF @FullTime = 0 SELECT COUNT(NumberOfDays) AS TotalHolidays, EmployeeNumber AS EERef FROM dbo.tbl_Holidays GROUP BY EmployeeNumber HAVING (EmployeeNumber = @EmpNo)
END ==========
Can someone please let me know where I'm going wrong, or what I need to do to get this function done?
I inherited a report that counts patient visits per month. Holidays are hard coded into the query. What is the best way to handle holidays without hardcoding?
Is there an equivalent function in SQL to the'NETWORKDAYS(start_date,end_date,holidays)' function in Excel ?NetworkDays in Excel returns the number of whole working days betweenstart_date and end_date. Working days exclude weekends and any datesidentified in holidays. This part isn't vital but would be nice tohave. The weekend stuff is more important.I realise this will most likely need to be some method I write myself(possibly based on the name of the day - Mon-Fri), but any pointerswould be appreciated if anyone has done this before.ThanksRyan
The below code works fine to measure the difference in days between two dates. However, there is an additional business requirement to subtract week-ends, and holidays, from the equation. Any ideas on how to accomplish this task, and leverage the below, existing code? Thanks in advance! (SELECT ABS((TO_DATE(TO_CHAR(" & ToFieldDate & "),'yyyymmdd') - TO_DATE(TO_CHAR(" & FromFieldDate & "),'yyyymmdd'))) FROM DUAL) AS Measurement "
Dont know whether this is of any use to anyone or it has been done before but there are a lot of posts on here regarding date calculation issues & usually the most straight forward answer is to compare against a table of dates.
So while looking at Bretts blog and another post on here, i thought i'd post this on here http://weblogs.sqlteam.com/brettk/archive/2005/05/12/5139.aspx http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49698
Special thanks to rockmoose & BOL (as always)
Edit: It also moves bank holidays to the following Monday (and Tuesday - xmas) if the bank holiday(s) falls on the weekend
SET DATEFIRST 1 SET NOCOUNT ON GO
--Create ISO week Function (thanks BOL) CREATE FUNCTION ISOweek (@DATE datetime) RETURNS int AS BEGIN DECLARE @ISOweek int SET @ISOweek= DATEPART(wk,@DATE)+1 -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104') --Special cases: Jan 1-3 may belong to the previous year IF (@ISOweek=0) SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1 AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1 --Special case: Dec 29-31 may belong to the next year IF ((DATEPART(mm,@DATE)=12) AND ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28)) SET @ISOweek=1 RETURN(@ISOweek) END GO --END ISOweek
--CREATE Easter algorithm function --Thanks to Rockmoose (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=45689) CREATE FUNCTION fnDLA_GetEasterdate(@year INT) RETURNS CHAR (8) AS BEGIN -- Easter date algorithm of Delambre DECLARE @A INT,@B INT,@C INT,@D INT,@E INT,@F INT,@G INT, @H INT,@I INT,@K INT,@L INT,@M INT,@O INT,@R INT
SET @A = @YEAR%19 SET @B = @YEAR / 100 SET @C = @YEAR%100 SET @D = @B / 4 SET @E = @B%4 SET @F = (@B + 8) / 25 SET @G = (@B - @F + 1) / 3 SET @H = ( 19 * @A + @B - @D - @G + 15)%30 SET @I = @C / 4 SET @K = @C%4 SET @L = (32 + 2 * @E + 2 * @I - @H - @K)%7 SET @M = (@A + 11 * @H + 22 * @L) / 451 SET @O = 22 + @H + @L - 7 * @M
IF @O > 31 BEGIN SET @R = @O - 31 + 400 + @YEAR * 10000 END ELSE BEGIN SET @R = @O + 300 + @YEAR * 10000 END
RETURN @R END GO --END fnDLA_GetEasterdate
--Create the table CREATE TABLE MyDateTable ( FullDate datetime NOT NULL CONSTRAINT PK_FullDate PRIMARY KEY CLUSTERED, Period int, ISOWeek int, WorkingDay varchar(1) CONSTRAINT DF_MyDateTable_WorkDay DEFAULT 'Y' ) GO --End table create
--Populate table with required dates DECLARE @DateFrom datetime, @DateTo datetime, @Period int SET @DateFrom = CONVERT(datetime,'20000101') --yyyymmdd (1st Jan 2000) amend as required SET @DateTo = CONVERT(datetime,'20991231') --yyyymmdd (31st Dec 2099) amend as required WHILE @DateFrom <= @DateTo BEGIN SET @Period = CONVERT(int,LEFT(CONVERT(varchar(10),@DateFrom,112),6)) INSERT MyDateTable(FullDate, Period, ISOWeek) SELECT @DateFrom, @Period, dbo.ISOweek(@DateFrom) SET @DateFrom = DATEADD(dd,+1,@DateFrom) END GO --End population
/* Start of WorkingDays UPDATE */ UPDATE MyDateTable SET WorkingDay = 'B' --B = Bank Holiday --------------------------------EASTER--------------------------------------------- WHERE FullDate = DATEADD(dd,-2,CONVERT(datetime,dbo.fnDLA_GetEasterdate(DATEPART(yy,FullDate)))) --Good Friday OR FullDate = DATEADD(dd,+1,CONVERT(datetime,dbo.fnDLA_GetEasterdate(DATEPART(yy,FullDate)))) --Easter Monday GO
UPDATE MyDateTable SET WorkingDay = 'B' --------------------------------NEW YEAR------------------------------------------- WHERE FullDate IN (SELECT MIN(FullDate) FROM MyDateTable WHERE DATEPART(mm,FullDate) = 1 AND DATEPART(dw,FullDate) NOT IN (6,7) GROUP BY DATEPART(yy,FullDate)) ---------------------MAY BANK HOLIDAYS(Always Monday)------------------------------ OR FullDate IN (SELECT MIN(FullDate) FROM MyDateTable WHERE DATEPART(mm,FullDate) = 5 AND DATEPART(dw,FullDate) = 1 GROUP BY DATEPART(yy,FullDate)) OR FullDate IN (SELECT MAX(FullDate) FROM MyDateTable WHERE DATEPART(mm,FullDate) = 5 AND DATEPART(dw,FullDate) = 1 GROUP BY DATEPART(yy,FullDate)) --------------------AUGUST BANK HOLIDAY(Always Monday)------------------------------ OR FullDate IN (SELECT MAX(FullDate) FROM MyDateTable WHERE DATEPART(mm,FullDate) = 8 AND DATEPART(dw,FullDate) = 1 GROUP BY DATEPART(yy,FullDate)) --------------------XMAS(Move to next working day if on Sat/Sun)-------------------- OR FullDate IN (SELECT CASE WHEN DATEPART(dw,FullDate) IN (6,7) THEN DATEADD(dd,+2,FullDate) ELSE FullDate END FROM MyDateTable WHERE DATEPART(mm,FullDate) = 12 AND DATEPART(dd,FullDate) IN (25,26)) GO
---------------------------------------WEEKENDS-------------------------------------- UPDATE MyDateTable SET WorkingDay = 'N' WHERE DATEPART(dw,FullDate) IN (6,7) GO /* End of WorkingDays UPDATE */
--SELECT * FROM MyDateTable ORDER BY 1 DROP FUNCTION fnDLA_GetEasterdate DROP FUNCTION ISOweek --DROP TABLE MyDateTable
I have already created a table name 'tblHolidays' and populated with 2014 Holidays. What I would like is be able to calculate (subtract or add) number of days from a date. For example subtract 2 days from 07/08/2014 and function should return 07/03/2014.
CREATE FUNCTION [dbo].[ElapsedBDays] (@Start smalldatetime, @End smalldatetime) RETURNS int AS BEGIN /* Description: Function designed to calculate the number of business days (In hours) between two dates.
Does anyone know how many consecutive users can be logged into a MSSQL database? I have a database online and need to know how many users can be logged on at a time. someone told me 150 users and others say 200 and stillothers say it's unlimited based on licensing. I tried Microsoft's homepage but got nothing so please don't suggest it. (I spent two hours there)
Suppose that N users are executing the code, one independent of the other, in the same time and they both commit the transaction at the same time. Can I suppose that the rows inserted in the table by one user will be consecutive?
Hi,I'm trying to insert records from one table into another table. The destination table has a ROWID field which cannot be an identity key, but needs to 'act like' an identity key and have its value populated with (Max(ROWID) + 1) for each row added to the table.To my thinking, simply using (Max(ROWID) + 1) in my SELECT statement will not work as it will only be evaluated once so if I am adding 1000 records and Max(ROWID) is 1234, all 1000 entries will end up having a ROWID of 1235.Is there a way to accomplish this?Thanks
Hi, I am in need of a query which would find the same customer coming in for three or more consecutive dates. To elaborate
I have a details table where I capture the following details
CustID, DateofPurchase, PurchaseDetails
I need a query to find how many customers have come in everyday consecutive day and count of the same for the a given period, say a month. Can anyone help me with a query for the same.
I work for a charitable organization, am new to this form (and sql programming) and trying to create a flag for unique records indicating the number of consecutive years a donor has given.
I have create a sample db idenifying donor, giving year and total pledges with multiple donor records existing for multiple years having donated.
INSERT INTO mygifts06 (Id,Gift_yr,Pledges) SELECT 155758,2005,15.00 UNION ALL SELECT 155759,2004,25.00 UNION ALL SELECT 155758,2004,40.00 UNION ALL SELECT 155757,2005,100.00 UNION ALL SELECT 155758,2002,30.00 UNION ALL SELECT 155758,2001,120.00 UNION ALL SELECT 155755,2003,15.00 UNION ALL SELECT 155758,2006,80.00 UNION ALL SELECT 155757,2003,65.00 UNION ALL SELECT 155759,2005,400.00
For the above dataset, I am trying to create the following output
I am using the following query (which works fine):
select min(timex) as start_date ,end_date ,entityid ,entityname ,locationid
[code]....
However I would like to not use the delta (it takes effort to calculate and populate it); instead I am wondering if there is any way to calculate it as part / whilst running the query.
Problem 2
I have the following table which shows the location of different people at 1 hour intervals
I'm trying extract a count of consecutive numbers, or "unbroken" years inthis case, at any particular given time.For example (simplified):CREATE TABLE #Customers(CustNo INT,YearNo INT,IsCust CHAR(1))INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2006, 'Y')INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2005, 'Y')INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2004, 'Y')INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2003, 'N')INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2002, 'N')INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2001, 'Y')INSERT INTO #Customers (custno, yearno, isCust) VALUES (999, 2000, 'Y')SELECT * FROM #CustomersCustNo YearNo IsCust----------- ----------- ------999 2006 Y999 2005 Y999 2004 Y999 2003 N999 2002 N999 2001 Y999 2000 YIn 2006 CustNo 999 would have been active for 3 years, 2004 for 1, 2001 for2, etc. Ideally I'd feed it a single year to lookupI'm resisting the urge to create cursor here -- anyone have any hints?....Chris.
Hello,Can someone please help me with a query?The table looks like this:BookedRooms===========CustomerID RoomID BookDateID1 1 200507011 1 200507021 1 200507031 1 200507091 1 200507101 1 200507111 1 20050712Desired result:CUSTOMER STAYS==============CustomerID RoomID ArriveDateID DepartDateID1 1 20050701 200507031 1 20050709 20050712Basically, this is for a hotel reservation system. Charges varynightly, and customer changes (shortening/extending stay, changingrooms, etc) happen quite often. Therefore, the entire stay is bookedas a series of nights.The length of the stay is never known, so it needs to be derived viathe Arrive and Depart Dates, based on the entries in the table.Notice, customers often stay in the same room, but with gaps between,so a simple MIN and MAX doesn't work. The output needs to showconsecutive nights grouped together, only.I've researched this quite a bit, but I just can't seem to make itwork.Any help would greatly be appreciated.Thanks!
I'm in desperate need of help. I'm setting up an intranet portal using DNN. I added an event calendar module, but whenever I try to add events to it, the system rejects it with a nasty Sql exception saying the conversion from char to datetime produced an out of bounds result.
The string the table uses to convert to datetime is (I have not modified it, the module is exactly as it came when i downloaded)
(convert(varchar,getdate(),101))
The whole stack trace for the error is:
Stack Trace:
[SqlException: La conversión del tipo de datos char a datetime produjo un valor datetime fuera de intervalo.] System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) +642 System.Data.SqlClient.SqlCommand.ExecuteReader() +11 DotNetNuke.AVCalendarDB.Save() +1067 DotNetNuke.AVCalendarEdit.updateButton_Click(Objec t sender, EventArgs e) +3367 System.Web.UI.WebControls.LinkButton.OnClick(Event Args e) +108 System.Web.UI.WebControls.LinkButton.System.Web.UI .IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +57 System.Web.UI.Page.RaisePostBackEvent(IPostBackEve ntHandler sourceControl, String eventArgument) +18 System.Web.UI.Page.RaisePostBackEvent(NameValueCol lection postData) +138 System.Web.UI.Page.ProcessRequestMain() +1263
At first I thought it could be a language issue (DNN and the module are in english and my system runs XP Pro in Spanish) but I discarded it since it didn't work when I installed XP Pro in english
Any ideas?? I would really appreciate your help Best regards from Chile Javier L.
Hi, I am somewhat new to t-sql and what I do know is from trial & error and help from this forum. What I need to do is add a column of counts for id numbers that are not unique.
From individual event logs I have generated a table where arrivals and departures at a location are registered per device. As there are multiple registration points, there might be multiple consecutive registrations per location. If this is the case I need to filter those out and have one registration per location and in the result I need to get the earliest arrival and the latest departure of these consecutive rows.
So as long the field LocationID is the same in the next row, it needs to be grouped.
I have added the rows Grp1, Grp2, Grp in an attempt to get an unique grouping number with the following script in the select statement:
,ROW_NUMBER() OVER(PARTITION BY DeviceID ORDER BY logID) AS Grp1 ,ROW_NUMBER() OVER(PARTITION BY DeviceID, LocationID ORDER BY logID) AS Grp2 ,ROW_NUMBER() OVER(PARTITION BY DeviceID ORDER BY logID) - ROW_NUMBER() OVER(PARTITION BY DeviceID, LocationID ORDER BY logID) AS Grp
By subtracting Grp2 from Grp1 (Grp = Grp1 - Grp2) I hoped to get an unique group number for each set of equal consecutive locations, however the Grp2 column does not restart from 1 each time the LocationID changes: Grp2 in line 7 should have been 1 again, but it is 2 because this is the second row with LocationID = 3 in the list.
I am using the following query (which works fine):
select min(timex) as start_date ,end_date ,entityid ,entityname ,locationid
[code]....
However I would like to not use the delta (it takes effort to calculate and populate it); instead I am wondering if there is any way to calculate it as part / whilst running the query.
Problem 2:I have the following table which shows the location of different people at 1 hour intervals
I have a table with 3 columns: Item# | Date | ItemAmount. Everyday there is a number of transactions entered. An Item# can only be entered once par day (if it has occurred that day).
What I want to do is to : retrieve the number of total days where an Item has been entered for more than 2 consecutive days (for the month).
Example: if item I022 has been entered Monday and wed, then ignore, but if it's been entered Mon, Tues then return 2, if Mon, Tues, Wed then return 3 because the days are consecutive.
