T-SQL (SS2K8) :: Retrieve Dates For Last 7 Days

Mar 26, 2014

The following query was used for retrieving dates for the last 7 days . Untill February this query was running fine and would return the last seven days date including today.

SELECT DISTINCT TOP 7 Convert(DateTime, DATEDIFF(DAY, 0, DateCreated)) AS DateCreated,
datepart(dw,DateCreated) AS WeekNum from [TechnologyRepository].[helpdsk].[WorkDetails]
WHERE DATEDIFF(DAY, Convert(DateTime, DATEDIFF(DAY, 0, DateCreated)),GETDATE()) <= 7

However from March (not sure of the exact date)..the query below would only give us 7 days until yesterday..i.e it would list dates from 3/19,3/20,3/21,3/22,3/23,3/24,3/25 and not 3/26 ..

I changed the query to <= 6 and it works as expected. But still not sure why it would not return todays date with <= 7.

SELECT DISTINCT TOP 7 Convert(DateTime, DATEDIFF(DAY, 0, DateCreated)) AS DateCreated,
datepart(dw,DateCreated) AS WeekNum from [TechnologyRepository].[helpdsk].[WorkDetails]
WHERE DATEDIFF(DAY, Convert(DateTime, DATEDIFF(DAY, 0, DateCreated)),GETDATE()) <= 6

View 5 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Selecting Data By Date For Last Five Days AND Avoiding Weekend Dates

Apr 16, 2014

What I am trying to do: Obtain attendance percentages for schools for the last five days. The outcome would look like this:

DISTRICTGROUPING, SCHOOLNAME, 5 DAYS AGO PCTG, 4 DAYS AGO PCTG, 3 DAYS AGO PCTG, 2 DAYS AGO PCTG, 1 DAY AGO PCTG
I am using nested subqueries for each day as follows: (total enrollment-total absent/total enrollment)
,(
((SELECTCOUNT(*)--GET TOTAL ENROLLMENT COUNT FOR SPECIFIED DATE

[Code]....

The query works with the following exceptions:

My issues are:

1. Avoid the "division by zero" error. This can occur if a school is closed for a day or if a smaller school has no absences for a day.

2. Avoid weekend dates. I need the query to display only weekdays

3. Currently I am using "PERCENTAGE 5: as a column header whereas I need the actual date as the header.

View 6 Replies View Related

Remove Weekends And Non Working Days When Calculating Days Difference Between Two Dates

Jan 7, 2014

I have an SQL code below which removes weekends and non working days when calculating days difference between two dates:

ce.enquiry_time represents when the enquiry was logged

(DATEDIFF(dd, ce.enquiry_time, getdate()) + 1)
-(DATEDIFF(wk, ce.enquiry_time, getdate()) * 2)
-(CASE WHEN DATENAME(dw, ce.enquiry_time) = 'Sunday' THEN 1 ELSE 0 END)
-(CASE WHEN DATENAME(dw, getdate()) = 'Saturday' THEN 1 ELSE 0 END)
-(SELECT COUNT(*) FROM nonworking_day WHERE nonworking_day.nonworking_date >= ce.enquiry_time AND nonworking_day.nonworking_date < dateadd(dd,datediff(dd,0,getdate()),1))

It works but I don't understand how it works it out. I am having issues understanding each coloured piece of code and how it works together.

View 1 Replies View Related

T-SQL (SS2K8) :: Calculate Sum Of Dates Minus Repetitive Dates

Jul 18, 2014

Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U'))
DROP TABLE [dbo].[temp_tbl]
GO
CREATE TABLE [dbo].[temp_tbl](
[tbl_id] [bigint] NULL,
[cs_id] [int] NOT NULL,
[USERID] [int] NOT NULL,

[code]....

View 9 Replies View Related

T-SQL (SS2K8) :: Insert Into Table Dates In Between Two Dates

Feb 28, 2015

I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.

CREATE TABLE hotel_guests
(
[guest_name] [varchar](25) NULL,
[start_date] [date] NULL,
[end_date] [date] NULL,
[comment] [varchar](255) NULL

[code]...

View 7 Replies View Related

Days Between Two Dates.

Aug 6, 2007

Hi all,
I have a table in which I have two fields in my DB.
FromDate and ToDate.
Both are stored as Varchar(MAX).
I would like to have an SP which gives me the Days in Between them.
Regards,
Naveen.

View 4 Replies View Related

How To Get Dates Within Certain # Of Days

Apr 16, 2007

Hello,
I have a table with a field Expiration of smalldatetime. I want to only display the data that has 60 days or more between Expiration and todays date. Any help would be highly appreciated.

View 5 Replies View Related

Cal Number Of Days In Between Dates

Feb 27, 2005

select a.RelocateID,a.DateEntered,a.CompanyID,b.FileClose dDate from test1 a inner join test b on
(a.RelocateID = b.RelocateID) where
CompanyID ='5710' and DateEntered >= '01/01/2004' and convert(varchar,FileClosedDate,101) < '31/12/2004'

Hi I was wondering if somebody could help me alter this query so I an calulate the difference in days in between DateEntered and FileClosedDate having the above criteria.
I can't seem to be able to get the datediff function right in this particular example

View 3 Replies View Related

Recalculate Days Between Two Dates

Jul 9, 2014

Have a table called Customers that has a datetime column called Submitted. I need a way to compare the Submitted field against the current date to tell if 5 days have pasted excluding weekends. In the sample data below, the record submitted on 7/2 is the record that should be returned in the query when compared to today's date 7/9.

FirstName........LastName.........Submitted
John................Miller...............7/01/2014
Mary................Smith..............7/02/2014
Bob.................Williams..........7/05/2014

View 5 Replies View Related

Retrieve Data For Working Days Using Date Function

Aug 22, 2007

Hi
I would like to return data for working days only. This will need to exclude holidays.For eg In the Month of August we have 31 Days and every 1st day of 1st week is holiday.So my output should retrieve me 31-4=27 .
Any ideas?

Thanks...

View 2 Replies View Related

How To Count Working Days Only Between Two Dates

Dec 16, 2013

I have the following simple SQL which counts the days difference between two date fields:

SELECT
DATEDIFF(dd,central_enquiry.enquiry_time, GETDATE()) as Days_Open

FROM
central_enquiry
---

How do I get it to exclude weekends?

I also have a table nonworkingdays which has a nonworking_date field where users can manually record national holidays and bank holidays etc.

Example date

nonworking_date
01/01/2014
18/04/2014
05/05/2014

How can I include this table in the calculation too?

View 16 Replies View Related

Number Of Days Between Two Dates In Same Column

Apr 17, 2007

I have a table like this (a small section of the table)

Cu_id |Tr_id |Date
1234 |1 |12/3/2006
1234 |2 |12/18/2006
1234 |3 |1/5/2007
1234 |4 |1/9/2007
1234 |5 |2/21/2007
9999 |91 | 1/3/2006
9999 |81 |1/10/2006
9999 |71 |1/18/2007
9999 |61 |2/1/2007

I have to find the number of days between the dates for the same cu_id and add the number as a new column. The new table should look like this.

Cu_id |Tr_id |Date| Days_between
1234 |1 |12/3/2006 |0
1234 |2 |12/18/2006 |15
1234 |3 |1/5/2007 |18
1234 |4 |1/9/2007 |4
1234 |5 |2/21/2007 | 43
9999 |91 |1/3/2006 |0
9999 |81 |1/10/2006 |7
9999 |71 |1/18/2007 |8
9999 |61 |2/1/2007 |14

Please let me know how I can find the number of days between two dates in the same column for the same cu_id (customer_id).

Thanks

View 14 Replies View Related

TSQL : Calculating Working Days Between Two Dates

Jul 12, 2000

Hello,
Can anyone out there tell me if there's a simple way to calculate the number of week days between two dates in TSQL? Need it to calc. average turnaround times, excluding weekends. Can do it v. easily in VB, but gets a little more tricky in TSQL as there's no way to return the number of Sundays and Saturdays between the two dates. Any help much appreciated !

Jon Reade
Sql Server DBA
NEC Technologies (UK) Ltd.

View 2 Replies View Related

TSQL : Calculating Working Days Between Two Dates

Jul 12, 2000

Hello,
Can anyone out there tell me if there's a simple way to calculate the number of week days between two dates in TSQL? Need it to calc. average turnaround times, excluding weekends. Can do it v. easily in VB, but gets a little more tricky in TSQL as there's no way to return the number of Sundays and Saturdays between the two dates. Any help much appreciated !

Jon Reade
Sql Server DBA
NEC Technologies (UK) Ltd.

View 1 Replies View Related

Help With Dates-finding Number Of Days Per Month

Mar 28, 2000

I am trying to determine the amount of days in a month to prorate a month end estimate.
We measure service calls and need to approximate how many we will have at month end.
I would like to automate a query to post on our web and need to know how many days are in the current month.

A possible solution would be to piece together a datetime variable using getdate and dateadd then use a datepart.
However , I don't know how to create a datetime variable this way.

Thanks in advance

View 1 Replies View Related

Finding Number Of Business Days Between Two Dates

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

Finding Number Of Business Days Between Two Dates

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

Transact SQL :: Displaying POs With Due Dates Greater Than Three Days

Apr 24, 2015

What I want to see is how to show PO's who's due dates  are > three

Select * from mytable
where myorderstatus = 'onorder'

This is my duedate format and what I want is any that past that date over 3 days

2014-08-11 00:00:00.000

View 11 Replies View Related

SQL Server 2008 :: Selecting Days In Month Between Two Dates

May 19, 2015

I need some with selecting the number of days, in a month, between a date range. For example, my data looks like:

FileNumb | startdate | enddate
1 04/25/2015 05/02/2015
2 05/01/2015 05/10/2015

The output I am looking for would be:

FileNumb | Year | Month | Days
1 2015 4 6
1 2015 5 2
2 2015 5 10

View 9 Replies View Related

Sql To Pull Appt Dates 2 Days Greater Than Current

Feb 4, 2008

I am pulling data that shows appt information on a patient:

FName,LName, PhoneNumber, ApptDate, ApptTime, and Status that equals Active. I need to pull this info everyday. How can I set this up in a stored procedure to pull all this information automatically on a daily basis where it pulls by Appt Date that is 2 days greater than the current date?

Thanks!

Thanks!
Lisa

View 9 Replies View Related

Transact SQL :: Total Number Of Days Between Two Dates By Year?

May 6, 2015

I am currently working on a T-Sql query(Sql server 2008) to calculate total no of days between date ranges by year

Table:

Start Date End Date
01/01/2013 04/30/2014
11/01/2014 05/31/2015
06/01/2015 12/31/2015

My expected result.

2013 - 365
2014 - 181
2015 - 365

Note:

Date range can span b/w  multiple years

Date ranges will not overlap

I just want the total number of days covered by the range for each year.

Is there any simple way to do this calculation.

View 9 Replies View Related

Power Pivot :: Calculate Number Of Business Days Between Two Dates

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

Transact SQL :: How To Get Date Difference Between 2 Dates In DAYS Excluding Weekends

Oct 14, 2015

Here I have 2 Dates. CreatedDttm & ModifiedDttm.

I want  - DATEDIFF(Day,CreatedDttm,ModifiedDttm)  and I have to exclude the Weekend days from that query result.

View 10 Replies View Related

Retrieve Last Record For Each Month Between Two Dates

Oct 10, 2013

I need to retrieve the last record for each month between two given dates from a unique table that contains on record per day.

View 3 Replies View Related

Retrieve Dates Within The Previous Month

Apr 9, 2008

I have a CheckDate field and I only what to retrieve dates that fall in the month previous to when the query is run. I'm assuming it will involve DATEADD or DATEPART, but I'm not sure how to do it since the end of the month day will vary from month to month.

For example, today is 04/09/2008 so I want to retrieve checks with a date between 03/01/2008 and 03/31/2008.

View 13 Replies View Related

T-SQL (SS2K8) :: Hot To Get Number Of Days For Given Date Range

Sep 15, 2014

--From the rows I want to know how many number of days a person was active for the given date range.

create table [dbo].[personstatus]
(
id int identity(1,1),
name varchar(100),
DateAdded date,
InactivationDate date ) ;
insert into [dbo].[personstatus] values

[Code] ....

--The output I am looking for.
/*
1) FromDt = '2014-01-01' ToDt ='2014-01-30'
KRISS = 7
VDENTI = 7 days

2) FromDt = '2013-01-01' ToDt ='2014-01-01'
KRISS = 1
VDENTI = 1 days

3) FromDt = '2013-01-01' ToDt ='2014-01-01'
KRISS = 0
VDENTI = 1 days

4) FromDt = '2013-01-01' ToDt ='2014-12-31'

KRISS = 8+24+8+21 = 61 Days
VDENTI = 31+24+31+30 = 116 Days
*/

View 9 Replies View Related

T-SQL (SS2K8) :: Winscp To Get Only Current Days Files?

Jan 21, 2015

I have to download the files from SFTP server, for which i am using WINSCP and i am able to successfully automate the process to download the files. But it is downloading all the files instead of only current day's files. Ihave searched for WINSCP documentation for time query parameter to pass to the get command. But its not working.

My source file name contains Datefield as "Filenameexample_150120_N001.txt".

Here 150120 mean Jan 20 2015.

Winscp has no functionality to query the files to parse using datefield. how to look for files which are from today's date.

Currently i am downloading files which contain *.* (meaning all files).

View 6 Replies View Related

T-SQL (SS2K8) :: Average Number Of Days Between Orders

Apr 7, 2015

I need to calculate the average number of days between customer orders.

My table has customer number and order number and the order date.

I would like to end with customer number last, order date and the average number of days between orders for each customer.

cust_idorder_numorder_date
HOLLGCAB 119482 02/27/2015
JILCO 119484 02/27/2015
KEY 119491 02/27/2015
TURNER 119496 02/27/2015
KEY 119499 02/27/2015

[Code] .....

View 9 Replies View Related

SQL Server 2008 :: How To Retrieve Only Monday Dates From Each Month

Jan 23, 2015

How to retrieve only Monday dates from each month in sql server.

If i pass any value (Let say GETDATE()) then i should get all monday values from the current month.

I want to calculate bi weekly range data in the sql.

View 9 Replies View Related

T-SQL (SS2K8) :: Today - Subtract 30 Days From Date Field

Feb 4, 2015

We are running sql 2008 R2. We have the JE_DATE field set up as an int. We are trying to subtract 30 days from this date field.

select * from GENERAL_LEDGER
where JE_DATE >= DATEADD(DAY,-30,GETDATE())

In addition to the where clause above we have also tried
where JE_DATE >= DATEADD(dd,-30,GETDATE())

For both we get the following error "Arithmetic overflow error converting expression to data type datetime."

View 7 Replies View Related

T-SQL (SS2K8) :: Calculate 90 Days And 3 Years Ago From Effective Date In A Table?

Sep 30, 2014

What would be the most straight forword to Calculate 90 days and 3 Years ago from an Effective Date in a table?

as in

SELECT EffectiveDate
from FL.CEFHistory

I need to return the effective date - 90 days and 1 year from that.

[URL]

View 6 Replies View Related

T-SQL (SS2K8) :: Query To Find Older Date That Is Less Than 90 Business Days

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

T-SQL (SS2K8) :: Get Particular Day Between Two Dates?

Aug 8, 2014

I want to list Dates for particular day between two dates.

for eg. if user enters 08/01/2014 and 08/31/2014 with value 0 then all the Sundays and related dates should be display.

if above contamination with 1 then all Mondays,

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved