SELECT To Get The Last 5 Business Days- Tricky
Apr 7, 2008
I have stumbled upon a sql question I cannot answer. I am looking for the following SELECT sql statement:
Basically I need a way to get "5 days ago from today". BUT, the trick is that there is a table called tblnoworkday with contains weekends and holidays and those dates cannot count. So basically what I am really trying to get is "5 Business days ago from today".
So it would basically do this for an query input date of '4/9/08'. If the table is called TblNoWorkday and contains the following records:
...
2008-04-06 00:00:00.000
2008-04-05 00:00:00.000
...
This is the last 5 business days then: (not in the table)
4/9/08
4/8/08
4/7/08
****weekend****
4/4/08
4/3/08
The query should return just 4/3/08. The problem is 4/3/08 doesn't exist in the database since the database only contains the no work days.
Any ideas on how to do this in a simple query without having to create another table with the valid working dates?
Thanks Before Hand,
Adiel
View 10 Replies
ADVERTISEMENT
Jun 20, 2008
dear gurus,
I want to get the working days between two days..
in a single query.
i will give the start_date '06-02-2008',end_date '06-13-2008' the result should be as below.
06-02-2008Monday
06-03-2008Tuesday
06-04-2008Wednesday
06-05-2008Thursday
06-06-2008Friday
06-09-2008Monday
06-10-2008Tuesday
06-11-2008Wednesday
06-12-2008Thursday
06-13-2008Friday
Thanks in advance.
cool...,
View 5 Replies
View Related
Sep 24, 2007
I Have a date range and i want to calculate the number of days - the weekends(saturdays and sundays) so that only businessdays are obtained.
A simple table's example:
Name, surname, datebeg, dateend
How can can it be done with sql?
BB
View 3 Replies
View Related
Mar 22, 2001
Is there any easy way to calulate number of business days between the 2 dates ?
I want to exclude all sat and sun between the 2 dates.
View 1 Replies
View Related
Aug 16, 2007
I'm looking for a DATEDIFF function which will give me the
BUSINESS days difference between 2 dates.
e.g: datediff(day,'08/08/2007','08/14/2007) would normally result in 6 (i think), however in business days this would be 4.
is there such a function?
Thx
View 4 Replies
View Related
Apr 21, 2000
I need a function to count business days (exclude Sat, Sun) that I can call within a view. I would rather not build a "calendar table" this will be used ongoing for years into the future.
Does anyone have anything like this they could share? If there is another source you could direct me to I would appreciate that as well.
TIA
Phil
View 1 Replies
View Related
Oct 25, 2006
Hello,I am writing a query to select records added to a table today, in the last 3 days, in the last 7 days, and so on.Here is what I have (which seems that its not working exactly). -- total listed today
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 0-- total listed yesterday
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 1-- total listed in the last 3 days
SELECT COUNT (*) FROM mytable WHERE DATEDIFF(Day, mydatecolumn, getdate() ) <= 3I'd like to be able to select the count for records added within the last X number of days. Can someone please help me out? Thanks so much in advance.
View 1 Replies
View Related
Jul 21, 2001
Does anyone know of a way to calculate the date 'x' number of business days after another date?
View 2 Replies
View Related
Nov 27, 1999
Hi and Thank you in advance
I am trying to find a away to calculate the number of business days between two dates. In other word, I do not want to count Saturday nor Sundays if those days are between the two dates.
Example
if Date1 = 11/26/1999
Date2 = 11/30/1999
the DateDiff(dd,Date1,Date2) the result should be 2
I need to do this against a table which might not have a lot of records, but I also need to not count Holidays if they fall within the two Dates.
Thank you in advance
Tomas
View 1 Replies
View Related
Nov 27, 1999
Hi and Thank you in advance
I am trying to find a away to calculate the number of business days between two dates. In other word, I do not want to count Saturday nor Sundays if those days are between the two dates.
Example
if Date1 = 11/26/1999
Date2 = 11/30/1999
the DateDiff(dd,Date1,Date2) the result should be 2
I need to do this against a table which might not have a lot of records, but I also need to not count Holidays if they fall within the two Dates.
Thank you in advance
Tomas
View 1 Replies
View Related
Jul 9, 2015
I am trying to get a cumulative count of business days. I have a column in my date dimension BusinessDayInd which is 'Y' or 'N'. I have been trying to create a calculated column in the cube but have not been successful yet. I think I'm close with this:
  COUNTROWS(FILTER(DATESMTD(PostingDate[Posting Date]), PostingDate[BusinessDayInd] = "Y"))
However, this is giving me a count of calendar days for all business days and null for non-business days. How do I apply the filter to DATESMTD? I've tried many iterations of this using CALCULATETABLE, CALCULATE, and FILTER. All are giving me the same result.
What am I doing wrong?
View 7 Replies
View Related
Feb 12, 2015
select DateAdd(dd,-90,getdate())
It gives the 90 days older date but i want to exclude the weekdays and holidays (saturday ,sunday,holiday)
As we dont have permission to call a function or Sp in high level environments looking for a simple query .
View 1 Replies
View Related
May 12, 2015
I have a date that I need to add 'n' number of business days to. I have a calendar table that has a 'IsBusinessDay' flag, so it would be good if I was able to use this to get what I need. I've tried to use the 'LEAD' function in the following way;
SELECT A. Date, B.DatePlus3BusinessDays
FROM TableA A
LEFT JOIN (Select DateKey, LEAD(DateKey,3) OVER (ORDER BY datekey) AS DatePlus3BusinessDays FROM Calendar WHERE IsBusinessDay = 1) B ON A.DateKey = B.DateKey
Problem with this is that because I am filtering the Calendar for business days only, when there is a date that is not a business day in TableA, a NULL is being returned.
Is there any way to do a conditional LEAD, so it skips rows that are not business days? Or do I have do go with a completely different approach?
View 9 Replies
View Related
May 14, 2015
I am looking for a formula to calculate the number of weekdays/business days between two dates in power pivot.I do the same in SQl using  the following query
  DATEDIFF(dd, Date1, GETDATE()) - (DATEDIFF(wk, Date1, GETDATE()) * 2) -
  CASE WHEN DATEPART(dw, Date1) = 1 THEN 1 ELSE 0 END +
  CASE WHEN DATEPART(dw, GETDATE()) = 1 THEN 1 ELSE 0 END ENDÂ
I am looking for a similar query in Power Pivot.
View 2 Replies
View Related
Sep 10, 2015
I have a calendar table against entire year 2015 with each day with 2 flag,
1. WK_DT_IN == except Satarday and Sunday, value is "Y", for Sat/Sun, value is "N"
2. HOL_DT_IN == value will only be "Y" only for holiday, example for '2015-01-01' date, it's value is "Y"
DECLARE @CAL TABLE (CAL_DT DATE, WK_DT_IN CHAR(1), HOL_DT_IN CHAR(1))
INSERT INTO @CAL VALUES ('2015-01-01', 'Y', 'Y'), ('2015-01-02', 'Y', 'N'),('2015-01-03', 'N', 'N'),
('2015-01-04', 'N', 'N'), ('2015-01-05', 'Y', 'N'), ('2015-01-06', 'Y', 'N'), ('2015-01-07', 'Y', 'N')
We have a given date, example '2015-01-01', I need to find out a date after 2 business days? I can think of loop, but will it work.
I need to exclude date which having HOL_DT_IN = "Y" and WK_DT_IN = "N".
View 3 Replies
View Related
May 19, 2014
I created a dbo.Calendar table that stores dates and a work day flag (1=work day, 0=non-work day) so I can use it to calculate the next business date from a date using a function. I'm using a while group to count only the work days and a couple other internal variables but I'm not sure if I can even use them in a function.
Assuming Sats & Suns are all non-work days in April 2014, if my @WorkDays = 10 for 10 work days and my @DateFromValue - 4/1/2014, I would expect my return date to be 4/15/2014.
------ Messages after I click execute on my query window that has my function ------------------------------------------------------
Msg 444, Level 16, State 2, Procedure FGetWorkDate, Line 19
Select statements included within a function cannot return data to a client.
Msg 207, Level 16, State 1, Procedure FGetWorkDate, Line 20
Invalid column name 'WorkDay'.
Msg 207, Level 16, State 1, Procedure FGetWorkDate, Line 22
Invalid column name 'Date'.
------ my function code ----------------------------
CREATE FUNCTION [dbo].[FGetWorkDate](
   @WorkDays VARCHAR(5),
   @DateFromValue AS DateTime )
   RETURNS DATETIME
[Code] ....
View 10 Replies
View Related
Jul 18, 2007
I have a LastName field which holds this dataLastNameJohnson|VasquesAdams|Fox|JohnsonVasques|Smith Now let’s say I have a SELECT Stored Procedure which takes 1 parameter @LastName.The @LastName can be something like this: “Fox|Smith�.I would like to have my SP to return me all of the records where LastName field have any of those names (Fox or Smith).In this example it will be the last two records: Adams|Fox|Johnson and Vasques|Smith . Thank you.
View 14 Replies
View Related
May 13, 2003
Hi, I'm tring to break my head finding a solution of how to return the following result:
I have the source table -> S_TAB with some data like
COL1 COL2 COL3
ABC DTT COL
ANC DRT COL
ANC DRT COL
......
......
what I need is come up with a single select statement (a view) to get the following output:
select <something> as RID, COL1, COL2, COL3 from S_TAB
RID COL1 COL2 COL3
1 ABC DTT COL
2 ANC DRT COL
3 ANC DRT COL
4 .....
5 .....
Any Idea will be appreciated
Dim
View 2 Replies
View Related
May 22, 2008
I don't even know if this is possible, but I need to find a way to do the following:
I have a select statement that returns the the Top (x) scores from a table called Rounds. The number of rows (x) will vary based on another calculation that I have, in this example I used 3.
SELECT TOP (3) Scores
FROM Rounds AS Rounds_1
WHERE (UserID = 'testuser')
I need to take the 3 values from this example, and calculate the AVERAGE. How do I do that?
Thank you.
View 2 Replies
View Related
Jul 18, 2007
I have a LastName field which holds this data
LastName
Johnson|Vasques
Adams|Fox|Johnson
Vasques|Smith
Now let’s say I have a SELECT Stored Procedure which takes 1 parameter @LastName.
The @LastName can be something like this: “Fox|Smith�.
I would like to have my SP to return me all of the records where LastName field have any of those names (Fox or Smith).
In this example it will be the last two records: Adams|Fox|Johnson and Vasques|Smith .
Thank you.
View 13 Replies
View Related
Nov 26, 2005
In a system holding data from questionnaires, I have two defined tables: one holding info on the respondent (r) and the other holding answer data (ad)
The layout of the respondent table:
r_idint
weekint
The layout of the answer data table:
ad_id int
r_idint
qvarchar(10)
a_valint
a_text varchar(50)
Each row in the ad table matches data for one question on the questionnaire.
Lets say a
<sql>
select *
from respondent r, answer_data ad
where r.r_id = ad.r_id
</sql>
returns the following data:
<result>
r_id, week, ad_id, r_id, q, a_val, a_text
1, 40, 1, 1, '1', 1, 'Destination 1'
1, 40, 2, 1, '2', 1, 'Bad'
1, 40, 3, 1, '3', 3, 'Good'
2, 40, 4, 2, '1', 2, 'Destination 2'
2, 40, 5, 2, '2', 2, 'Acceptable'
2, 40, 6, 2, '3', 4, 'Excellent'
3, 41, 7, 3, '1', 1, 'Destination 1'
3, 41, 8, 3, '2', 4, 'Excellent'
3, 41, 9, 3, '3', 4, 'Excellent'
</result>
Extracting the mean value of answers by week is easily done using the following select:
<sql>
select week, q, avg(cast(a_val as float)) mean
from respondent r, answer_data ad
where r.r_id = ad.r_id
and q > '1'
group by q, week
order by q, week
</sql>
This would give a result like:
<result>
week, q, mean
40, '2', 1.5
40, '3', 4.0
41, '2', 3.5
41, '3', 4.0
</result>
Now the tricky part - a result by destination (ad.q = '1') has been requested by the customer.
Doing a
<sql>
select q, avg(cast(a_val as float)) mean
from respondent r, answer_data ad
where r.r_id = ad.r_id
and q > '1'
and r.r_id in (
select r.r_id
from respondent r, answer_data ad
where r.r_id = ad.r_id and q = '1'
)
group by q
order by q
</sql>
returns the requested data:
<result>
q, mean
'2', 2.3333333333333335
'3', 3.6666666666666665
</result>
Only, it lacks info on the destination. What I need is something like this:
<result>
dest, q, mean
'Destination 1', '2', 2.5
'Destination 1', '3', 3.5
'Destination 2', '2', 2.0
'Destination 2', '3', 4.0
</result>
How can I achieve that?
Thanks,
Jacob Dall
View 2 Replies
View Related
Jul 20, 2005
Dear GroupI wonder whether you can push me in a direction on how to design thefollowing statement. I'm looking for a SELECT with some tricky ORDERBY.The database table looks like this:MenuID TabText SubTabID TabOrderID------- ----------- ----------- -----------1 Main 0 12 Cars 0 23 Boats 0 34 Planes 0 45 Pick-Ups 2 16 Campers 2 2The result should look like this:MainCarsPick-UpsCampersBoatsPlanesNotice that 'Pick-Ups' and 'Campers' are a subcategory of 'Cars' andmust appear in the result directly following 'Cars'.In more detail:'Main', 'Cars', 'Boats' and 'Planes' are top-level categories and'Pick-Ups' and 'Campers' are subcategories of 'Cars'. The SubTabIDvalue of an item identifies to what top-level category a subcategorybelongs.The TabOrderID specifies in what order the items should be sorted,e.g. 'Pick-Ups' comes first and 'Campers' second.Thanks very much for your help & efforts!Martin
View 3 Replies
View Related
Apr 22, 2015
I want to display Days Hours Mins Format.
I am Having two columns Like below,
Col1 (in days) Â Â col2 (In Hours : Mins)
3days 4:5Â
In this first have to  add Col1 and Col2 (Here one day is equals to 9 hours ) so the addition is 31.5
From this 31.5 I should display 3 Days 4 Hours 30 Mins because 31.5 contains 3 (9 hours) days 4 Hours and .5 is equals to 30 mins.
View 6 Replies
View Related
May 8, 2008
I've have these following table
tbllocation
Main_ID | Date_Taken | Time |Hit
-----------------------------------------
206 | 5/9/2008 | 100 | 2
206 | 5/9/2008 | 200 | 3
206 | 5/6/2008 | 300 | 6
201 | 5/1/2008 | 400 | 5
201 | 5/4/2008 | 500 | 9
201 | 5/7/2008 | 600 | 2
204 | 5/2/2008 | 700 | 2
204 | 5/3/2008 | 800 | 4
204 | 5/6/2008 | 900 | 2
203 | 5/7/2008 | 100 | 2
203 | 5/8/2008 | 200 | 3
203 | 5/9/2008 | 300 | 6
202 | 5/4/2008 | 400 | 5
202 | 5/3/2008 | 500 | 9
202 | 5/8/2008 | 200 | 3
205 | 5/2/2008 | 300 | 6
205 | 5/1/2008 | 400 | 5
205 | 5/9/2008 | 500 | 9
tblSetValue
Main_ID | Hit2
---------------
206| 3
201| 5
204| 3
203| 1
202| 8
205| 7
*Main_ID is a primary key
Condition
1. Let's say, the current date is 5/9/2008
2. Result only display the last 7 days data. From above data. it's mean only pickup from 5/3/2008 to 5/9/2008
3. Every Main_ID only pickup the MAX Hit
4. Diff (column on the fly) = Hit - Hit2
The expected result shown as follow
tblResult
Main_ID | Date_Taken | Time | Hit | Hit2 | Diff
-----------------------------------------------
206| 5/6/2008 | 300 | 6 | 3 | 3
201| 5/4/2008 | 500 | 9 | 5 | 4
204| 5/3/2008 | 800 | 4 | 3 | 1
203| 5/9/2008 | 300 | 6 | 1 | 5
....
....
....
Anyone can help me to built the query?
View 6 Replies
View Related
Nov 29, 2006
Hi !
for MS SQL 2000/2000, I need :
SELECT * FROM table1 WHERE table1.[date] > 100 days
how can i do that ?
thank you for helping
View 2 Replies
View Related
Jul 21, 2004
What would my statement look like if I have a column in a table (SoftwareInstall) called InstalledOn which stores a date in shortDateString format and I want to select all the records where that date is <= 30 days previous to today's date. Any ideas?
View 1 Replies
View Related
Jan 13, 2008
How to get all the days of a month using select statement in sql server 2000
please help
thanks
View 1 Replies
View Related
Oct 7, 2004
I'm using DataList to return vales stored in an SQL database, of which one of the fields contains the date the record was added.
I am trying to fill the dataset with items only from the last 30 days.
I've tried a few different ways, but all the database rows are returned.
What is the WHERE clause I sholud use to do this??
Thanks
View 2 Replies
View Related
Oct 28, 2013
I have to select last 5 days login data from UserLog Table, Based on the LogMessage, Please find below example for clarity
Table : UserLog
Sample Data :
LogId | UserID | IP | DateTime | LogMessage
1 | 1012 | 102.34.23.xx | 2013-10-22 08:42:00 | User ID 1012 (Soft Token)[] - Primary authentication successful from RDS
2 | 1012 | 102.34.23.xx | 2013-10-22 08:43:00 | User ID 1012 (Soft Token)[] - Network Connect: Session started from RDS Location
3 | 1012 | 102.34.23.xx | 2013-10-22 08:45:00 | User ID 1012 (Soft Token)[] - Network Session Initiated: Success Session from RDS Location
4 | 1015 | 102.xx.203.xx | 2013-10-22 09:42:00 | User ID 1015 (Soft Token)[] - Primary authentication successful from RDS
[Code] ...
Expected Result:
I would like to select the user loged data by UserID for last 5 days, I will pass the UserID as a parameter, Time taken should be calculated based on LogMessage ( Time Between "Primary authentication successful" message and "Network Connect: Session started " Message), If multiple login for the same day We have to take the most recent one for the day.
Input : @UserID = 1012
UserID | Date | IP Address | TimeTaken (Min)
1012 | 2013-10-22 | 102.34.23.xx | 1
1012 | 2013-10-23 | 102.34.25.xx | 2
View 3 Replies
View Related
Jun 29, 2015
I have 3 month of record in my table. if i pass 2 and 10, i need to select the record of between 2 and 10 days of record of every month. if i pass 10 and 20, it should select the record between 10 and 20 of every month. How to query for that?
View 8 Replies
View Related
Dec 10, 2007
May I know how to use a "date" to select out previous 14 days record from the table? and find the duplicated records?
-- sort out duplicate order from tblOrder
Select * FROM tblOrder
WHERE DDay > @prmDDay("day", -14, getDate())
Group by DDay
Many thanks~~~~~
Fr New Learner
View 3 Replies
View Related
May 24, 2015
SQL express 2012. I am trying to case in the where part and having a syntax errors -Â This is what i am trying to do:
select all the days in week number x including last year if necessary... so if the year start not at the beginning of the week then look in last year as well ( for the same week number of this year and last week nu of last year)
declare
@yyyy int = 2014,-- THE YEAR
@mm int = 1,-- THE MONTH
@week1No int = 1,-- THE WEEK NUMBER IN THE YEAR
@week2No int = 37-- THE last WEEK NUMBER IN last YEAR
select count(tblDay.start)-- tblDay.start IS smallDatetime
[Code] ....
View 2 Replies
View Related
May 24, 2015
SQL express 2012
I am trying to case in the where part and having a syntax errors -Â this is what i am trying to do:
Select all the days in week number x including last year if necessary... so if the year start not at the beginning of the week then look in last year as well ( for the same week number of this year and last week nu of last year)
declare
@yyyy int = 2014,-- THE YEAR
@mm int = 1,-- THE MONTH
@week1No int = 1,-- THE WEEK NUMBER IN THE YEAR
@week2No int = 37-- THE last WEEK NUMBER IN last YEAR
select count(tblDay.start)-- tblDay.start IS smallDatetime
[Code] ...
View 2 Replies
View Related
