Hi, I was wondering if anyone can help me write a SELECT statement to return all records (rows) based on whether they fall within two dates. For example, consider this table:
Code Snippet --DROP TABLE #Records CREATE TABLE #Records (RecordID INT PRIMARY KEY NOT NULL, StartDate DATETIME, EndDate DATETIME) INSERT INTO #Records VALUES (1, '2008-05-01', '2008-05-12') INSERT INTO #Records VALUES (2, '2008-05-08', '2008-05-12') INSERT INTO #Records VALUES (3, '2008-05-19', '2008-05-22') INSERT INTO #Records VALUES (4, '2008-05-22', '2008-05-23') INSERT INTO #Records VALUES (5, '2008-05-26', '2008-06-01') INSERT INTO #Records VALUES (6, '2008-05-28', '2008-06-01') SELECT * FROM #Records
I want to return the RecordID for any row which span this week. I've tried the simple approach of using WHERE GETDATE() BETWEEN StartDate AND EndDate but this doesn't produce the desired results. For example, RecordID 4 spans this current week but is not returned (obviously) with this approach.
The idea is to show which events (rows) are coming up this week and next week. Eventually the data will be used to populate an SSRS report.
Hello. I need to create a page with asp 3 and sql server 2000 that lists all the records in a table that match a date criterion, but when doing so I get an error "Error Converting datetime from character string"... I've tried this versions of the query:
Code:
SELECT (...) CONVERT(DATETIME,PAYMENTS_RECEIVED.PYR_DATE,103), (...) FROM RESERVES LEFT OUTER JOIN (...) WHERE (PAYMENTS_RECEIVED.PYR_DATE BETWEEN '20/03/2006' AND '21/03/2006')
and...
Code:
SELECT (...) PAYMENTS_RECEIVED.PYR_DATE, (...) FROM RESERVES LEFT OUTER JOIN (...) WHERE (PAYMENTS_RECEIVED.PYR_DATE >= CONVERT(DATETIME,'20/03/2006',103)) AND (PAYMENTS_RECEIVED.PYR_DATE < CONVERT(DATETIME,'21/03/2006',103))
I also tried adding " 00:00:00 a.m." to the dates to be converted in the second piece of code. If I recall correctly, "103" is the convertion code meaning "dd/mm/yyyy". That is, I expect people to type in a date like "27/03/2006" and get the table records from that date.
Weirdest thing of all is that, when using the query builder of a sqlsource control in Visual Studio Web Developer Express 2005 this following query works just fine as I expect:
Code:
SELECT (...) PAYMENTS_RECEIVED.PYR_DATE, (...) FROM RESERVES LEFT OUTER JOIN (...) WHERE (PAYMENTS_RECEIVED.PYR_DATE >= CONVERT(DATETIME, '20/03/2006', 103)) AND (PAYMENTS_RECEIVED.PYR_DATE < CONVERT(DATETIME, ' 21 / 03 / 2006 ', 103))
Both the server and the DB are the same in all cases, but in the first two I run the query from a (working of other queries) vbscript asp 3 page.
I know that similar posts exist already (I've searched for this before posting), but I can't fix it, at least not with the usual answer on this one (that seems to be using the convert function as in the first piece of code). Thanks in advance!!!
tblCustomers contains a CustomerID that is unique to each customer.
tblMachines contains a list of all machines with a MachineID that is unique to each machine. It also contains the CustomerID number.
tblServiceOrders contains a list of each time each customer was serviced. It contains the ServiceDate, CustomerID, and ServiceOrderNo. But it does not have any information on the machines.
tblMachinesServiced contains a list of each machine that was serviced for each service order. It contains the ServiceOrderNo and the MachineID number.
What I want is to be able to extract a list of machines that were not serviced between 2 dates. What I end up getting is a list of machines that were serviced outside of the date range I provide.
For instance, say machine A was serviced in 2013 and 2015 but not in 2014. And say machine B was serviced in all 3 years. When I try to extract my list of machines not serviced in 2014 I end up with a list that contains machine A-2013, A-2015, B-2013 & B-2015. But what I need is just machine A-2014, since that machine wasn’t serviced in 2014.
I’ve tried several different queries but here is an example:
SELECT tblMachines.MachineID,ServiceMachines.ServiceDate FROM tblMachines LEFT JOIN (SELECT MachineID, ServiceDate FROM tblServiceOrders, tblMachinesServiced WHERE tblServiceOrders.ServiceOrderNo=tblMachinesServiced.ServiceOrderNo ) ServicedMachines ON tblMachines.MachineID=ServicedMachines.MachineID WHERE YEAR(ServiceDate) != '2014'
I understand why it returns the records that it does, but I'm not sure how to get what I want, which is a list of machines not serviced in 2014.
I want the top 1, having the highest counter from each ID. This is a highly simplified version of that I am pulling which also is between a date range, but same principle.
IE: SELECT * FROM Table WHERE ID in (SELECT DISTINCT ID FROM Table WHERE Date BETWEEN <date> AND <date>
I'd rather keep it in one statement if possible, but if I have to do it in multiple passes then so be it.
I understand how to find (generate) missing dates for the year 2006 if I have a range value like 1-1-2006 to 3-1-2006. (I'm just using 2006 for arguements sake - the needed approach starts back in 2004 or so and will extend to the current day)
Now the question that presents itself is, is there an elegant way to do this same process with an arbitrary number of date ranges per customer? There would be 1 record per range, per customer
What I'd like to generate is a record per missing date in 2006, which would be: 4 records for 1-1-2006 to 1-4-2006 several records for 2-1-2006 to 10-31-2006 and then more for 11-18-2006 to 12-14-2006
As I said before, the number of ranges isn't static. It could be 1 or a dozen and could increase as time goes on. At this time, all I can see to potentially make it work in query is to do a dozen joins - which kind of sucks.
My other thought is to do specific processing per customer, per range gap, but it will be terribly slow.
I want generating Valid date ranges from any list of dates.
The List of Dates could be generated from the below TSQL -
SELECT '2015-06-02' [Date] UNION ALL SELECT '2015-06-13' UNION ALL SELECT '2015-06-14' UNION ALL SELECT '2015-06-15' UNION ALL SELECT '2015-06-16' UNION ALL SELECT '2015-06-22' UNION ALL SELECT '2015-06-23' UNION ALL SELECT '2015-06-24'
And the expected output should look like -
SELECT '2015-06-02' FromDate, '2015-06-02' ToDate UNION ALL SELECT '2015-06-13' FromDate, '2015-06-16' ToDate UNION ALL SELECT '2015-06-22' FromDate, '2015-06-24' ToDate
Have a SP that takes 2 parameters (@login, @logout) both in the form of "convert(varchar,@Login,108)" or 08:00 AM, etc
What I need to do is then check to see: 1. What period did they login in? 2. What period did they logout in? 3. How much time in a period did they actually miss?
The ranges look like: Set @Period1Start='08:20 AM' Set @Period1End='09:10 AM' Set @Period2Start='09:15 AM' Set @Period2End='10:00 AM' ... ... ...
So..... if @login = 08:30 AM and @logout = 08:45 then they missed 15 minutes of period 1
If @login = 08:30 AM and @logout = 09:45 then they missed 40 minutes of period 1 AND 30 minutes of period 2
Not knowing all of the time functions in SQl, I am looking for some ideas on how to accomplish this.
Find Time Ranges I have a DateTime field, I need to find out how many records are in 8am-11am, 12pm-5pm, 6pm-7am regardless of date. How can I do this?
Find Time Ranges I have a DateTime field, I need to find out how many records are in 8am-11am, 12pm-5pm, 6pm-7am regardless of date. How can I do this?
I'm trying to populate the sp_fact_sit_budget_test2 table with the employee_key from the base_employee_test2 table where the person_id's match and the the base_employee_test2 time_added_fis<= max sp_fact_sit_budget_test2 time_added_fis. So for the data shown below the sp_fact_sit_budget_test2 employee_key should get the value 312436.
CREATE TABLE [dbo].[SP_FACT_SIT_BUDGET_TEST2] ( [TIME_ADDED_FIS] datetime NULL, [PERSON_ID] int NULL, [EMPLOYEE_KEY] int NULL) ON [PRIMARY] GO
insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')insert into SP_FACT_SIT_BUDGET_TEST2 (TIME_ADDED_FIS, PERSON_ID) VALUES ('3/23/2008 9:12:29 PM', '163634')
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' CREATE TABLE [dbo].[BASE_EMPLOYEE_TEST2] ( [PERSON_ID] varchar(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [TIME_ADDED_FIS] datetime NULL, [LAST_NAME] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [FIRST_NAME] varchar(100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [EMPLOYEE_KEY] int NULL) ON [PRIMARY] GO
insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-03-20 00:00:00','x','Guillermo',311123) insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-03-20 00:00:00','x','Guillermo',311123) insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-03-23 00:00:00','x','Guillermo',312436) insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-04-13 00:00:00','x','Guillermo',332196) insert into [dbo].[BASE_EMPLOYEE_TEST2]([PERSON_ID],[TIME_ADDED_FIS],[LAST_NAME],[FIRST_NAME],[EMPLOYEE_KEY]) values ('163634','2008-04-16 00:00:00','x','Guillermo',336180)
Hi, I have the following query that is not working correctly
Basically I have a fields called TimeLeave (datetime) and I want to classify every record according to that field
The multiplication by the field b.Zulu is just to convert to local time, once converted I want to classify the local time with those numbers, but for example nothing falls in the first category and I get a bunch of records with a wrong ID
Any idea what should bet the correct sql statement to classify the records??
Thanks in Advance
CASE
WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '22:30' AND '05:59' THEN 1
WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '06:00' AND '08:59' THEN 2
WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '09:00' AND '11:59' THEN 3
WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '12:00' AND '14:59' THEN 4
WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '15:00' AND '16:59' THEN 5
WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '17:00' AND '19:59' THEN 6
WHEN SUBSTRING(CONVERT(VARCHAR(30),DATEADD(hh, -1*b.Zulu, a.TimeLeave),13),13,5) BETWEEN '20:00' AND '22:29' THEN 7
I need to be able to select records based on dates that are before a certain date. That bit's easy, my problem is that this certain date needs to be the current date. Any body be able to help?
Hello Forum, I am have posted questions regarding selecting a series of dates and am still having trouble doing so. I have been using vwd to build a website, and now would like to retrieve data from access and display a weeks worth of information at one time from selecting a column of information from the data base. I have used this section of code to do the required function. 'SELECT OrderId, CustomerId, Price FROM Orders WHERE OrderedDate BETWEEN DATEADD(d, -7, GETDATE()) AND GETDATE(), I have replaced these variables with my own, and keep getting an error message 'Undefined function 'GETDATE' in expression', I have tried many different functions to get it to work. Any ideas? Thanks Forum, Tim p.s. thank you todd for replying to my first question!
I am having a problem in outputting the last two dates that a bill has been sent out.
Background: A customers recieves a bill 4 times a year. The dates for issuing these bills are not fixed and I dont know the date that bills are usually sent out.
The database that I am using stores all of the dates that a bill would have been sent out.
Issue: A customer recently requested to see the dates of last 2 bills along with their value.
I know that you can view a customer last date by using the following statement.
Select max(issuedate)LastBill from statements
but i don't know how i could output the last 2 bills that were issued
I have two tables a stock table and a price table and I want to select the correct price for the Stock Date.
Problem is sometimes there is a promotion date in the price table between the live dates so the rows double up. Below is my sql but I get over 17,000 rows when it should be about 16,964.
I will post up the table and data.
SELECT a.[Company] ,a.[ProductID] ,a.[ColourSize] ,a.[StockDate] ,a.[Quantity] ,b.[Ticket Price] FROM[Stock_Ledger] a LEFT OUTER JOIN [Product_Prices] b ON a.[Company] = b.[Company] AND a.[ProductID] = b.[ProductID] AND a.[ColourSize] = b.[ColourSize] AND a.[StockDate] BETWEEN b.[StartDate] AND b.[EndDate] ORDER BY a.[StockDate],a.[ProductID],a.[ColourSize]
I'm looking for a way of taking a query which returns a set of date time fields (probable maximum of 20 rows) and looping through each value to see if it exists in a separate table.
E.g.
Query 1
Select ID, Person, ProposedEvent, DayField, TimeField from MyOptions where person = 'me'
Table
Select Person, ExistingEvent, DayField, TimeField from MyTimetable where person ='me'
Loop through Query 1 and if it finds ANY matching Dayfield AND Timefield in Query/Table 2, return the ProposedEvent (just as a message, the loop could stop there), if no match a message saying all is fine can proceed to process form blah blah.
I'm essentially wanting somebody to select a bunch of events in a form, query 1 then finds all the days and times those events happen and check that none of them exist in the MyTimetable table.
HiI have a table in my database called 'tblUsers'It contains usernames and a few columns of data.One of them in 'birthday'it is in the format dd/mm (ie 28/04 is 28th of april)it is always 5 characters longIts data type is char in my db.Now I try this<asp:SqlDataSource ID="SqlDataSource8" runat="server" ConnectionString="<%$ ConnectionStrings:IntranetConnectionString %>" SelectCommand="SELECT DisplayName FROM [tblUsers] WHERE ([Birthday] = @ShortDate)"> <SelectParameters> <asp:ControlParameter ControlID="Calendar1" Name="ShortDate" PropertyName="SelectedDate" Type="string" /> </SelectParameters> </asp:SqlDataSource>where the selected date from a calendar control is used.Now it works when the table has a Date column which is off type DateTime but how would I modify it to work like I want?Thanks!
For some reason this is just not "clicking" with me and the longer I stare at it the more I overcomplicate things and then I just confuse myself further...
Provided this relation showing where "pkey" and "skey" met on "ServiceDate":
I need to select out a list of distinct pkeys and skeys only when there was no meeting between the two in November 2005. In other words, this query would produce only one record - the starred record - when run against this sample table. This is because even though, for example, skey 124 / pkey 2 has an entry outside the desired range, it also has one inside the desired range. The same goes for the record outside our range for 123 and 2 - it also has a record inside our range.
At this point, I've come to the conclusion that I can first select all the distinct pkeys and skeys where the servicedate is not in 11/2005 then join it to a selection of distinct pkeys and skeys where the servicedate IS in the desired date range. Does that seem like the most straightforward way through this?
I don't get the impression that this is that complicated a problem, but it's one of those deals where I goofed up somewhere along the line, and now I think I'm really overthinking the problem, so I'd be much obliged if someone could give me a hearty slap to clear the ol' noggin.
I need a formula to calculate the time (let's say in minutes) between two dates/times. The problem is that I have to exclude the time between 06 PM and 06 AM and also exclude the time in the weekend (Saturday and Sunday). I will use this in a couple of reports made in Reporting Services. If anyone have an algoritm that could be modified for this and is willing to share this I would be very grateful. Many thanks! /Per Lissel
I have a large table (2+Million records, 2000+ new per day). If I want to count the records in one month, I could do :-
Select Count(*) from Table1 where Added_Date between '1999-12-01' and '1999-12-31'
or
Select Count(*) from Table1 where Added_Date >= '1999-12-01' and Added_Date <= '1999-12-31'
For these two above, I get the same result.
HOWEVER!!
If I then do:-
Select Count(*) from Table1 where Added_Date between '1999-12-01 00:00:00' and '1999-12-31 23:59:59' or use the >= and <= operators, the result is larger.
If I then do :-
Select Count(*) from Table1 where Added_Date between '1999-12-01 00:00:00.000' and '1999-12-31 23:59:59.999' or use the >= and <= operators, the result is larger still but i believe this including more rows than there actually is?.
I can get over the problem programatically using Convert to truncate to a day, month or year however, I don't understand why the results are different.
Any Ideas as to why this is happening, A'm I being stupid!!!!!!!
(PS Added_Date has a non_clustered index which has been rebuilt twice and DBCC finds no errors)
I have three tables: EMP (ID, NAME), EMPDATE (ID, STARTDATE, ENDDATE), YEAR(YEAR)
I would like to get the most recent date within a given year per each EMP? For example, EMPID 1 can be enrolled in many programs, each program has start end dates. I need to list the most recent date an employee was enrolled (max date between START AND END DATE which ever is most recent enrollment) for a given year. For example, for 2014 his/her most recent enrollment should be 10/23/2014 for year 2014 and 2013-10-24 for year 2013.
SELECT ID, EMP.NAME, DTE.StartDate, DTE.ENDDATE, year FROM EMP_DATE DTE join EMP_INFO EMP on EMP.ID = DTE.ID join YEAR YR on YR.YEAR = YEAR(DTE.STARTDATE)
On my SSRS report, I use a date parameter to let the user select a date with the little calendar tool. When a date is selected, I have a small bit of coding run to convert the date to text, because when the report first fires up, the field is populated with "1/1/1900," and I need that turned into a blank character to let the report pull up all rows. So far, all well and good.If someone picks a date, then the report will filter the data on that selected date. Works fine.
But, it appears there is no way to get the calendar tool to go back to allowing ALL dates - so that all records are pulled - except by manually typing in, or selecting it with the tool, 1/1/1900.If I try to clear the field, causing it to use '' as a WHERE criteria (WHERE AdmitDate LIKE '%' + @AdmitDateTxt + '%'), it repopulates the field with the last selected date. So, I guess the question is, how does one tell the calendar tool for picking dates for a date parameter to reset back such that all records are pulled, not just those for a single date, without being required to type in, 1/1/1900? Or, is there some way of telling the date parameter to select all dates?
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.
Table2 contains fields Group, Name,Category, Dimension (Group and Name are not in Table1)
So basically I need to read the records in Table1 using Groupid and each time there is a Groupid then select records from Table2 where Table2.Category in (Select Catergory from Table1) and Table2.Dimension in (Select Dimension from Table1)
In Table1 There might be 10 Groupid records all of which are different.
I have a database which has a field called fldTimes. basically this field records the number of hits a file gets. How can I choose the most 5 popular files with the greatest hits. Thanks
This might be a simple question. I have a LIKE statement that is working fine, however I am not sure if something else is possible.
I can pull all records on a query for a person's name with a parameter value of "MARTIN". It will also pull records for "LYNN MARTIN". However, what if I would like to have that search also pull "LYNN M MARTIN"? Currently "LYNN MARTIN" is not finding "LYNN M MARTIN".
When the end user wants to search on LYNN MARTIN and that is what they input, I want SQL to find all records that match LYNN MARTIN and also find records that HAVE LYNN % MARTIN.
I hope this make sense? I guess I need to build my select statement using a WHERE LIKE statement, but I am not sure of the syntax.
