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
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
I want to split the data every employeid wise based on fromdate and todate if totaldays>1.
sample output specified below ....but same output required for allthe empid's
create table attendence(EmployeeID nvarchar(20),[From] datetime,[To] datetime,TotalDays float)
insert into attendence values('1417','2015-11-02 22:48:49.450','2015-11-04 22:48:49.450',3)
insert into attendence values('1418','2015-11-04 22:48:49.450','2015-11-04 22:48:49.450',1)
insert into attendence values('1419','2015-11-03 22:48:49.450','2015-11-04 22:48:49.450',2)
insert into attendence values('1420','2015-11-04 22:48:49.450','2015-11-05 22:48:49.450',2)
insert into attendence values('1421','2015-11-01 22:48:49.450','2015-11-04 22:48:49.450',4)
OP
-------------------------
EmployeeID [From] [To] TotalDays
1417 2015-11-02 22:48:49.450 2015-11-02 22:48:49.450 3
1417 2015-11-03 22:48:49.450 2015-11-03 22:48:49.450 3
1417 2015-11-04 22:48:49.450 2015-11-04 22:48:49.450 3
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.
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.
I have a query pulling all records with a disconnect date and a transaction date. However, I would like to retrieve any records that have a transaction date greater than 30 days from the disconnect date. I have been unable to figure out the correct formula to use. I think I need to use the datediff function in SQL, but I've never really used this function before.
View 2 Replies View RelatedI 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.
I have a column of varchar(2000) but when I use it in a select statement I only get the first 255 characters displayed. (all the data is there as I can see different parts using substring) How do I get the complete column to display?
View 2 Replies View RelatedI created this unique codes and
I need all [FRMDAT] field set to ‘12/31/2014’ in the MKLOPT table, where the [JOBCOD] in the VALUE list BELOW have a [FRMDAT] that is currently (greater than) > ‘12/31/2014’
VALUE LIST
PH00059
PH02775
PH03051
PH03305
PH03336
PH03342
PH03371
PH03992
PH03993
PH03994
I am facing a strange issue with regards to generation of a report from my application in in Live. One of our client is using a report “Auditors Report”. When I try to generate the report for period of 1 month i.e. from 01 May 2015 – 31 May 2015, the report fails when the data is bound to report control. However if I try to generate the report for period of 1 day or 15 days for within the same month, report gets generated.
Now the same application and same database when attached to our local machine, the report gets generated properly for 1 month. I am not sure why the issue is coming on the server.
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.
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.
I have a table with kind of data as below
PhNo Address DP AutoID Name
0123 RH 001 4577 J
0123 RH 004 4567 JD
0124 RH 010 4467 KK
0121 HN 007 4667 RK
012 SVN 009 4787 RKK
012 SVN 009 4787 RKR
011 SVN 009 4787 KKR
I need distinct phone numbers >1 for a address and if the same phNo has 2 different names I want to skip the second one.I want the output like below
PhNo Address DP AutoID Name
0123 RH 001 4577 J
0124 RH 010 4467 KK
0121 SVN 009 4787 RKK
012 SVN 009 4787 RKR
011 SVN 009 4787 KKR
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
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
I'm using sys.dm_fts_parser dynamic management function to tokenize a string of characters >4000. The function doesn't accept a query_string parameter >4000 characters. Is there a way around this? I've tried to execute the SELECT defined in the function but that doesn't work.
View 2 Replies View RelatedFor Below example when @x=1 to retrieve col>0 rows or all rows.
With out another if else blocks or Dynamic sql to solve only in where clause.
select 0 col into #x
union
select 1 col
union
select 2 col
declare @x INT =1
SELECT * FROM #x
where col>CASE WHEN @x=1 THEN 0 ELSE (col=col) END
--here in case i want to compare only when @x=1 then col>0 other wise select all rows with out filter
I am using a table to store different size numbers for example:
Look at the picture below:
But I want this type of output look at the picture below:
How to sort the query to return greater than zero values to sort up and zeros go down.
I am using sql server 2008.
I'm trying to create a simple attendance website where a user logs in and a time and user name (with unique ID) is stored into a table, and then the user is able to click "Check Attendance" and the site will display only the times they logged in (which wasn't too bad). I want a button we can click to "clean up the database" and delete duplicate dates.
If I have a user sign in 10 times in one day - I don't need all 10 times logged into the database, only one. So how do I create a query (needs to be a query so I can tack it onto a button in PHP) that will delete 'where date = the same as a previous date'?
I appreciate the help - and I'm sure I will use this forum for help in the future as it seems very robust with information.
I am designing a database to store data from a leak tester.
We want to display the results between dates, I mean, the results of the leaking test are going to be stored as well as the datetime in which they have been performed.
ID (int)
RESULT (float)
TS (datetime)
The query will be, of course:
SELECT * FROM TABLE Where TS BETWEEN DT1 and DT2
This table is growing by 10000 rows a day, it is possible that in a year getting the values between two dates became impossible.
Using a index with a datetime field sounds like a crazy idea.
I have a need to display the difference between two dates, a start date and a end date in the format HH:mm where the hours could be greater than 60.
For example:
Start Date - 30/01/2007 09:00:01
End Date - 01/02/2007 20:40:04
When i use the following code (=Fields!dateend.Value - Fields!DateStart.Value) i get 2.11:40:03 which i can easily understand, but the customer wants it as above!
I would like to be able to get it to be 59:40:03.
Any help would be much appreciated.
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
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?
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
I'm using SQL Server 2000 sp2...I have created a view that gives me customer info from which I need to create a view and or table that gives me a 24 monthly columns of the sum of each account_number monthly revenues (going back 24 months from this month)..The columns I'm pulling from are these:
Customer_Name
Account_number
First_insert_date
Order_net_price
Here's what I have so far:
----------------------------
SELECT TOP 100 PERCENT Account_number, Customer_Name, SUM(Order_net_price) AS 'CM - 24'
FROM dbo.Customer_Feed
WHERE (First_insert_date >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 24, 0)) AND (First_insert_date < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 23,
0))
GROUP BY Account_number, Customer_Name
ORDER BY Account_number
I've basically hacked out a way to get the monthly totals for each account. What I would like to do is so be able to repeat the query but increment the date range 1 month until reaching the present or last FULL month and display these sums in individual columns named CM - n (where CM means current month and 'n' is the # of months back from current.how to make this query run over again the 23 other times I need it to and display those months sum totals in individual columns.
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.
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.
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
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
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
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
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.
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.