Query Help Please - Consecutive Dates
Jul 23, 2005
Hello,
Can someone please help me with a query?
The table looks like this:
BookedRooms
===========
CustomerID RoomID BookDateID
1 1 20050701
1 1 20050702
1 1 20050703
1 1 20050709
1 1 20050710
1 1 20050711
1 1 20050712
Desired result:
CUSTOMER STAYS
==============
CustomerID RoomID ArriveDateID DepartDateID
1 1 20050701 20050703
1 1 20050709 20050712
Basically, this is for a hotel reservation system. Charges vary
nightly, and customer changes (shortening/extending stay, changing
rooms, etc) happen quite often. Therefore, the entire stay is booked
as a series of nights.
The length of the stay is never known, so it needs to be derived via
the 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 show
consecutive nights grouped together, only.
I've researched this quite a bit, but I just can't seem to make it
work.
Any help would greatly be appreciated.
Thanks!
View 15 Replies
ADVERTISEMENT
Jul 30, 2012
I have a table with
EmpNum, Date, Abstype
What I want is to pull a list of all the employees and the Monday date of employees who have an absence on a Monday --> Friday consecutively.
eg Table
EmpNum, Date, Abstype
001 07/23/2012 VAC *Monday
001 07/24/2012 VAC
001 07/25/2012 VAC
001 07/26/2012 VAC
001 07/27/2012 VAC
003 07/23/2012 VAC * Monday
[code]...
As these are the 2 that run from monday-friday
View 2 Replies
View Related
Feb 23, 2015
I have a table full of service invoice records. Some of the invoices are continuous, meaning that there may be an invoice from 01-16-2015 through the end of that month, but then another invoice that starts on feb 1 and goes for 6 months.
I want to only pull the most recent. Keep in mind that there may be other invoices in the same table for a different period. An example might be:
FromDate ToDate Customer Number Contract Number
02/01/2015 07/31/2015 2555 456
01/15/2015 01/31/2015 2555 456
04/01/2013 09/30/2015 2555 123
03/13/2013 03/31/2013 2555 123
From this table, I would like a query that would give me this result:
01/15/2015 07/31/2015 2555 456
03/13/2013 09/30/2015 2555 123
There will likely be more than just 2 consecutive records per contract number.
View 4 Replies
View Related
Oct 10, 2006
Hi,I have an SQl query that will list the results if it lies between two dates, for example;SELECT * FROM TABLE WHERE { fn Now() } BETWEEN Date1 AND Date2This returns all results where Date1 and Date2 fall between the Current DateWhat i am looking for is a way to replace the { fn Now() } with a date of my choice.For example;SELECT * FROM TABLE WHERE '10/10/2006' BETWEEN Date1 AND Date2However this does not work.Has anyone any ideas why this may be ??Thanks in advanceAndrew Vint
View 8 Replies
View Related
Mar 16, 2004
How can I query date only in a where clause and not include the time part? When I use the where below if the time part of the date entry has past then the rows are not returned.
WHERE A.Selected=1 AND A.EndDate Is Null OR A.Enddate >= GETDATE()
Thanks,
View 1 Replies
View Related
Aug 3, 2004
Hi, im trying to write a sql query to check if dates are in the db,
ias it is it is telling me the dates are there and their not. any help would be great.
George
here's what i have...
SQL2 = "SELECT * FROM reservations WHERE" _
& "(arvdate >=#" & request("arvdate") & "# AND endate <= #" & request("endate") & "#) " _
& "OR " _
& "(arvdate >=#" & request("arvdate") & "# AND endate >= #" & request("endate") & "#) " _
& " AND idrent = " & idrent _
& " AND confirmation = 1" _
& " ORDER BY arvdate"
View 3 Replies
View Related
Oct 23, 2007
I have two tables: Sales, Consigners
The Sales table includes: date, description, price, consigner
The Consignment table includes: lastday, consigner
I need a query that will display all rows in Sales assuming Sales.date is less than or equal to Consigner.lastday. The .consigner fields need to be joined.
I have this and it isn't working:
SELECT DISTINCT [Sales Data].co1, [Sales Data].amount, [Sales Data].description, Consignment_Data.date AS Expr1
FROM Consignment_Data INNER JOIN [Sales Data] ON Consignment_Data.co1 = [Sales Data].co1
WHERE ((([Sales_Data].[date])=[Consignment_Data].[date]));
This is for MS Access 2003. Can anyone help me? I would appreciate some advice, as my code isn't working at all
View 7 Replies
View Related
Sep 6, 2006
Hi,
I have a query as follows:
SELECT SUM(Total) AS WeekRetailTotal, COUNT(*) AS MonthRetailOrderNo, DATEPART(wk, OrderDate) AS SalesWeek, YEAR(OrderDate) AS SalesYear
FROM dbo.Orders_Retail
WHERE (account = @Account) AND (OrderStatus <> 'Deleted') AND (PayStatus <> 'Pending') AND (OrderStatus <> 'Refunded')
GROUP BY YEAR(OrderDate), DATEPART(wk, OrderDate)
ORDER BY YEAR(OrderDate), DATEPART(wk, OrderDate)
the results look like this
WeekRetailTotal
MonthRetailOrderNo
SalesWeek
SalesYear
£397.55
8
3
2002
etc etc for each week in a year and then it goes onto the next year.
What I would like to do, is feed the query a variable as the start week and year and then also for the endweek and year.
I've tried to do a WHERE DATEPART(wk, OrderDate) > @StartDate AND DATEPART(wk, OrderDate)
< @EndDate AND YEar(OrderDate) > @StartYear AND YEAR(OrderDate) < @EndYear
But that's not correct, it only bring in the weeks in both years that are in between those two week range variables.
I need the startweek and year to be "one" starting point and the endweek and endyear be the ending point.
Any ideas?
Thanks
View 4 Replies
View Related
Sep 30, 2007
Hello,
I have a sql query that I am requireing to get records on date parameters :
SELECT * FROM table WHERE StartDate >= 2007/09/30 07:00:00 AND EndDate <= 2007/09/30 09:00:00
I have records with StartDate 2007/09/30 08:00:00, 2007/09/30 08:30:00 which this query does not select.
I am trying to select all StartDate which fall between 2007/09/30 07:00:00 AND 2007/09/30 09:00:00.
Any suggestions on the query design
Thanks
Peter
View 6 Replies
View Related
Dec 11, 2007
I have figured out how to work with dates in SQL, but don't know how to put it all together to completea sql query I am working on. Here are some examples of the basic MS SQL date routines I know:
SELECT DATEDIFF(day, '11-2-2007 11:11:11', '12-4-2007 11:11:11') -- Difference between two dates.
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY] -- Get's today's date
Now back to my problem....I have the following Query that must be modified so that the filter criteria is based upon the"StartDate" being greater than today's date. Can someone please help me out.
SELECT StartDate, BuildingAddres, TotalSquareFeet, FinishDateFROM dbo.ConstructionTable
Note that the date passed in for Start date will be in a format like the following '11-2-2007 09:1:11'.
So if I were to pseudo code out the query it would be something like the following, where the pseudo codeis in parenthesis.
SELECT StartDate, BuildingAddres, TotalSquareFeet, FinishDateFROM dbo.ConstructionTable (WHERE StartDate > Todays Date)
View 1 Replies
View Related
May 17, 2005
Hi.,
Can anyone please assist me in getting the following SQL Query to work. It returns no errors but also returns no records even though there is actually data in my database for that period.
Dim DCriteria as StringDCriteria = "StartDate <= " & EDateDCriteria = DCriteria & " AND EndDate >= " & SDatestrSQL = "SELECT * FROM vwLeavePlan WHERE " & DCriteria
This results in the follwoing SQL Statement:
strSQL = SELECT * FROM vwLeavePlan WHERE StartDate <= 31/05/2005 AND EndDate >= 01/05/2005
Regards.
Peter
View 14 Replies
View Related
Dec 1, 1998
hi,
i would like to create an SQL query, part of which involves comparing dates.
i have something like
Select ID, CONVERT(varchar(10), StartDate, 101), Name from Table WHERE StartDate .....
how can i compare the StartDate to something i have in the form of a string? for example, i have
Date = 12/12/1998. and i would like to select where the StartDate in the DB = '12/12/1998'.
i'd appreciate all the help i can get.
thanks,
sb
View 1 Replies
View Related
Feb 7, 2008
Hi all,
Been having a good root around the forums and the site here and there's some real smart people on here, i'm hoping one or more of them can help me out. I'm expecting this to be a simple question for some of you, however it's way beyond me at this point!
Table Structure (abridged, relevant columns):
Orders:
Code:
[Orders](
[OrderID] [int] IDENTITY(1,1)
[OrderDateTime] [datetime]
[OrderSiteID] [nvarchar](255)
[OrderOffline] [bit]
[OrderSentToWP] [bit]
[OrderReceivedFromWP] [bit]
[OrderAuthorised] [bit]
[OrderCancelled] [bit]
[OrderApproved] [bit]
[OrderFraud] [bit]
[OrderDispatched] [bit]
OrderItems:
Code:
[OrderItems](
[OrderID] [int]
[ProductID] [nvarchar](255)
[Quantity] [int]
[Price] [real]
[Weight] [real]
Products:
Code:
[Products](
[ProductID] [uniqueidentifier]
[ProductCode] [nvarchar](255)
[ProductTitle] [nvarchar](255)
Product price is captured at time of order, so that reports aren't affected by discounts or promotions, and stored with the productid in orderitems.
I want to get a report between a set of dates and with certain flags set (see below example) and then get a list of unique products, quantity sold and sales values for that products. Results table would have 4 columns; ProductCode, ProductTitle, QuantitySold, Sales Value.
So far I have this:
Code:
SELECT Products.ProductCode, Products.ProductTitle, SUM(OrderItems.Quantity) AS QuantitySold
FROM Orders INNER JOIN
OrderItems ON Orders.OrderID = OrderItems.OrderID INNER JOIN
Products ON OrderItems.ProductID = Products.ProductID
WHERE (Orders.OrderDateTime BETWEEN '2007/01/01' AND '2007/12/31') AND
(Orders.OrderSentToWP = 1) AND (Orders.OrderReceivedFromWP = 1) AND (Orders.OrderAuthorised = 1) AND (Orders.OrderCancelled = 0) AND
(Orders.OrderDispatched = 1) AND (Orders.OrderApproved = 1) AND (Orders.OrderFraud = 0) AND Orders.OrderSiteID= 'someguid'
GROUP BY Products.ProductCode, Products.ProductTitle
Which gets my summed quantities, and I guess I could use ASP to multiply that by the current price, but that defeats the point of setting the database up properly in the first place! I know how to design data, i just don't know how to get it back out again
I could most likely just do the whole thing in ASP and get it to output the correct answer, so if it's impossible/very difficult to do it in pure SQL then I'll go that route. Ideal situation would be a stored proc or saved query that I can pass a start date, an end date and a siteid to and that will get me the answers I want!
Thanks in advance to anyone that looks at this for me.
Also, any recommended books/sites to learn this kind of query?
Richard
View 4 Replies
View Related
Jul 19, 2007
Hi My first post :-)
I am trying to build a VBA Sub that uses a MS SQL query, this is fine it is just I am having trouble building the query to do what I need.
I need to be able to do a between month and years, so if I have a VBA routine that constructs a query from variables. Those variable will be a month and a year.
so I can return a count of all records that were logged between the month and year.
My poorly constructed query is below.
SELECT [DateRaised]
FROM HD_Call
WHERE DateRaised BETWEEN DATEADD([MONTH], -1, GETDATE()) AND DATEADD([MONTH],0, GETDATE())
AND DateRaised BETWEEN DATEADD([YEAR], -2, GETDATE()) AND DATEADD([YEAR],0, GETDATE())
AND [completed] = 1
AND [Assignee] LIKE '%'
AND [CallType] LIKE '%'
Order By 1
Thanks in advance
View 4 Replies
View Related
Sep 4, 2014
I have a need to only find distinct dates in which a worker worked in the factory using TSQL
Code:
RowNumber workerstartDate workerenddate
1 2012-08-08 2012-10-10
2 2012-08-10 2012-08-31
3 2012-09-05 2012-09-15
4 2012-10-15 2012-12-19
5 2013-01-02 2013-03-14
62013-03-15 2013-05-23
Basically, I am looking for the above to look like this
Code:
rownumberworkerstartDate workerenddate
12012-08-08 2012-10-10
42012-10-15 2012-12-19
52013-01-02 2013-03-14
62013-03-15 2013-05-23
View 2 Replies
View Related
Jul 20, 2006
i want a query which returns all the date between 2 dates . its like an calender.....for example i selected 2-1-2006(dd-mm-yyyy) to 18-03-2006 ....it should returns like this
2-1-2006
3-1-2006
4-1-2006
.
.
.
16-03-2006
17-03-2006
18-03-2006
View 1 Replies
View Related
Jul 24, 2001
Hi there, I'm a little bit stumped on this one.
I have a column in a table that records when the date and time of an event took place.
Table Name: Chronicle
Column Name: Created (of type DateTime)
I would like to select the Chronicle records that are between two dates.
(e.g. 1 May 2001 and 20 May 2001)
And I would like to select those records that are between two times.
(e.g. 6:00am and 1:00pm)
Does anyone know how to do this or have any pointers for me?
I can see it would be easier if I had the date in one column and the time in the other.
Can it be done without doing that?
cheers,
Hamish
Hamish Norton
hamish@liftrider.com
View 1 Replies
View Related
Feb 5, 2008
I have a table like FK_ID, Value, Date (here FK_ID is foreign key)this table getting updated frequently by daily bases that means one record per one day(For example in January month it has maximum 31 records or minimum 0 records, in February it has maximum 28 or 29 or minimum 0 records, based on calender year)I need to query this table to get missing dates in between particular monthsfor example for one FK_ID has only 25 records in Jan 2008 month and in Feb 2008 it has 10 records , so i need to get those missing 6 dates from JAN month and 18 dates from FEB monthhow can i query this
View 2 Replies
View Related
Dec 7, 2007
Hello Gang,
I have a strange problem that I haven't dealt with before.
I need to execute a piece of code based on date ranges. If the date range is:
Scenario 1:between 02/28 (Feb 28) and 07/31 (July 31) do x
-----------------------------------------------------------
Scenario 2:between 08/01 (Aug 1) and 01/31 (Jan 31) do y
Can anyone help me with this code. I am having a SCD (Stupid Coder Day) and can't seem to do anything that is scalable - like accounting for leap years (Feb issue) and the fact that Scenario # 2 above is between 2 different calender years.
Your help is much appreciated.
JJOSHI
View 3 Replies
View Related
Feb 8, 2008
Hi,
I have a table that temporarily stores information. Before I insert new data in the table, I want to delete the previous day's data.
This is the query I'm using
Code:
q="DELETE FROM SearchTemp WHERE theDate < "&Date()
set RS2 = DB.Execute(q, ra, adCmdText)
I should say I'm using ASP & VBScript.
Problem is, this isn't deleting the previous day's dates.
The dates are stored in this format: m/d/yyyy.
I've searched through old posts but none of the solutions offered have worked for me so far. Anyone have any suggestions?
Thanks!
View 4 Replies
View Related
Feb 8, 2006
Hi,
I have the following table:
Table name Employee
===============
emp_id,
emp_name,
emp_city
Table name EmpStatusReport
===================
emp_id,
action
date
I need to write a sql query to get the emp_name, emp_city and the recent date when the user has sent status report over last 30 days. The user has sent a status report if action field in the empStatusReport is set to 'reported'. This table gets filled everytime user sends report.
I tried to the do the following:
select e.emp_name, e.emp_city, esr.date from employee e, EmpStatusReport esr where e.emp_id and esr.emp_id and
/* esr.date = max(esr.date) and esr.date > currentDate - 30 and esr <= currentDate */
I am not able to write a correct login for date part. Any help in this will be highly appreciated.
Thanks!
View 4 Replies
View Related
Oct 3, 2005
Anyone know how to format dates from 10/3/05 to 2005/10/03 in a query?
The only way I know is (cast(year(srecordeddate) as char(4)))+ cast('/'as char) + cast (month(srecordeddate) as char(2)).... Which seems so rediculous. There's gotta be a better way!
View 3 Replies
View Related
Dec 11, 2007
Hello Gang,
I have a strange problem that I haven't dealt with before.
I need to execute a piece of code based on date ranges. If the date range is:
Scenario 1:between 02/28 (Feb 28) and 07/31 (July 31) do x
-----------------------------------------------------------
Scenario 2:between 08/01 (Aug 1) and 01/31 (Jan 31) do y
I am trying to automate a report. The report is supposed to generate a result that will differ based on the date ranges going into the future. E.g.
[1]. If the run date of the report is between '2/1/20xx' and '7/31/20xx' display <ABC> or
[2]. If the run date of the report is between '8/1/20xx' and '1/31/20xx' display <PQR>
In example # 2. I am moving from one year to the next (July to Dec and the one extra month of Jan). So for example, if the guy runs the report between August of 2008 and January of 2009, display <PQR>.
How do I achieve both # 1 & 2 above in a code? Does this explain better.
View 4 Replies
View Related
Apr 21, 2006
I am trying to test sp in Query Analyser as below but get the error
Server: Msg 8114, Level 16, State 4, Procedure spRMU_GetChaseFiles, Line 0
Error converting data type varchar to datetime.
How do I put the date as a parameter ? I think it may be to do with the fact that MaxDateOut is an expression in my view that the sp is based on. It is taking this as char when it should be datetime. So, how do I make this expression type date ?
EXEC spRMU_GetChaseFiles
@strStartDate ='01/01/05',
@strEndDate = '21/04/06',
@strDepartment = 'Central Units',
@strDivision = 'Personnel',
@strSection = ''
View 20 Replies
View Related
May 22, 2008
hello, how can I query dates using "between" function but grouped by months? for example:
QUERY:
FROM: 15/DIC/2007 TO: 15/FEB/2008
RESULT:
DECEMBER-2007 --- $49,535
JANUARY-2008 --- $45,352
FEBRUARY-2008 --- $52.345
Thanks in advance-!
View 4 Replies
View Related
Feb 10, 2008
I´m trying to select records which are between two dates. I use the following statement.
qry = System:tring::Format("SELECT sum(breakfast), sum(colacao), sum(lunch), sum(snacks), sum(dinner) FROM alunos, logtable WHERE alunos.cad_matr=logtable.studentid and alunos.cad_matr="+tbStudentId->Text+" and dateofmeal >=#"+dt->ToString("dd/MM/yyyy 00:00:00" )+"# and dateofmeal <=#"+dt2->ToString("dd/MM/yyyy 00:00:00" )+"#" );
Although the records exists the query does not get these records. If I go to the Query Design and use the same query it works but only if I enter the dates manually (dateofmel >=?).
Can anybody help me to solve thih /
Thanks
View 3 Replies
View Related
Nov 22, 1999
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)
Thank You in advance
Joey*
View 2 Replies
View Related
Jan 8, 2008
Hello,
I have a stored procedure that inserts 5 rows into a table. The execution of the SP is inside a transaction like in the code below:
Code Block
SqlConnection conn = new SqlConnection(ConnectioString);
conn.Open();
SqlTransaction trans = conn.BeginTransaction();
try
{
// execute stored procedure... insert 5 rows
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
}
finally
{
conn.Close();
}
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?
Thanks!
View 5 Replies
View Related
Sep 14, 2006
Here is the query:WHERE DATEPART(month, " + tableName + ".timestamp)>='" + startTextBox.Text + "' AND DATEPART(month, " + tableName + ".timestamp)<='" + endTextBox.Text + "'This is in a program using C# which is why it's in quotes and all that good stuff. The query itself works properly when startTextBox.Text = 8 and endTextBox.Text = 9. Itreturns results for both months 8 and 9. But when I want a result from a single month, say just 9... I put 9 in both text boxes and it ends up returning no results.Logic would tell me that say that both logics should come back TRUE but for some reason it's failing. Any ideas/suggestions? Thanks in advance!
View 2 Replies
View Related
Jan 14, 2008
Hello, I need to find the percentage of a a given contract start and end date for the year given.For example, the contract_start date is 05/08/2000 and the contract_end date is 04/30/2010, of course this will be 100% if you want to find the percentage for year 2008 but if you wanted to find the percentage for year 2010, then the percentage would be something like 42% (appr. 5 months) (b/c it would for year 2010, the contract would be from 01/01/2010 thru 04/30/2010)I need to find the percent from the beginning of the year, to the end.a few examples: start: 01/01/2007 end: 05/01/2007 if for year: 2007; this is 4 months @33.33% start: 05/01/ 2006 end: 06/01/2007 if for year: 2007, then 6 months @ 50% (01/01/2007 - 06/01/2007); but if it was for year 2006 then it would be 7 months @ 58% (05/01/2006 thru 12/31/2006) start: 01/01/2000 end: 03/01/2010 for year: 2008 then 12 months @ 100% Any help would be valued. Thank you!
View 4 Replies
View Related
Nov 30, 2011
I have to find the earliest date for any existing calendar year in a table...
For instance.....
ClientID Year
1 1/1/2000
1 2/1/2000
2 3/1/2000
2 4/1/2001
2 5/1/2001
The results I need are....
ClientID Year
1 1/1/2000
2 3/1/2000
2 4/1/2001
It is all contained in one table. I have got the earliest years by using the YEAR() function and grouping by it. The only problem is that I am having a problem joining the table back onto itself with the subselect because of it's an aggregate.
Here is the first join I have....
SELECT DT.ClientID, Year1 FROM
(
SELECT ClientID, YEAR(MyDate) as Year1
FROM MyTable
) AS DT
GROUP BY ClientID, Year1
ORDER BY ClientID, Year1
I want to use that as my derived table and then join back to it... but it's not working...
View 1 Replies
View Related
Jun 15, 2006
I have the following as part of a union query:
CONVERT(CHAR(8), r.RRDate, 1) AS [Date]
I also want to order by date but when I do that it doesn't order correctly because of the conversion to char data type (for example, it puts 6/15/05 before 9/22/04 because it only looks at the first number(s) and not the year). If I try to cast it back to smalldatetime in the order by clause it tells me that ORDER BY items must appear in the select list if the statement contains a UNION operator. I get the same message if I try putting just "r.RRDate" in the ORDER BY clause. It's not that big of a deal - I can lose the formatting on the date if I need to to get it to sort correctly, but this query gets used frequently and I'd like to keep the formatting if possible.
Thanks,
Dave
View 1 Replies
View Related
May 11, 2007
Table 1
ID PID From To Code
1 1 14/02/07 17/02/07 X
2 1 17/02/07 19/02/07 X
3 1. 19/02/07 23/02/07 E
4 1 26/02/07 28/02/07 X
5 1 1/4/07 1/5/07 E
6 2 01/03/07 03/03/07 X
7 2 04/03/07 10/03/07 X
8 2 10/03/07 14/03/07 E
Result
ID PID Date
4 1 26/02/07
7 2 04/03/07
I want to be able to create a select query on the above table. The table will show ID, PersonID (PID), From and to date, and code. If the code is X then the next €˜from record€™ should be the same date as the €˜to date€™. If the code is E then the next €˜to€™ date can be anytime after the previous €˜to€™ date.
I want to be able to report on all record where there is a day difference between the previous €˜to€™ date. I.e. ID 4 and 7 €“ the previous records both have an X and there is at least a days difference between the dates.
View 4 Replies
View Related
