Select Records Between Two Times
Jul 20, 2005
This seems so simple yet I can't figure it out.
I have a table that has two important columns for this query.
Column A has varchar
Column B is datetime
All I want to do is select any record that is between 5:00 am and
10:00 am regardless of what date it falls under.
In my brain I see
Select *
From <table>
Where B Between Like ‘%5%am' and Like ‘%10%am'
This chokes and I suppose its because Between is literal and Like is
variable.
I cant convert column B to just times because I still need to see the
date in the results of the query.
Thanks in advance for help
View 2 Replies
ADVERTISEMENT
Jul 20, 2005
Hi All,Sorry if the subject line is too obscure -- I couldn't think of a wayof describing this request.I have a table that contains approximately 1 million records.I want to be able to be able to select the top x records out of thistable matching variable criteria.Pseudo table records:custid, category, segment1,1,12,1,13,1,14,1,15,1,26,1,27,1,28,1,29,2,110,2,111,2,112,2,113,2,214,2,215,2,216,2,217,2,318,2,319,2,320,2,3So, what I'm trying to do is return a recordset, for example, thatcontains the top 2 of each variation of category and segment.ie:1,1,12,1,15,1,26,1,29,2,110,2,113,2,214,2,217,2,318,2,3The only way I can think to achieve this is in a while statement,performing individual selects against each combination, feeding thewhere criteria by variables that I automatically increment.I can't help thinking there's a much more graceful way of achievingthis?If anyone can give me any insight into this I'd be incrediblyappreciative!Many thanks in advance!Much warmth,Murray
View 1 Replies
View Related
Oct 6, 2007
i have a stored procedure with one coming id parameter
Code BlockALTER PROCEDURE [dbo].[sp_1]
@session_id int
...
and a view that holds these @session_id s to be sent to the stored procedure.
how could i execute this sp_1 in a select loop of the view. I mean i want to call the stored procedure as times as the view has records with different ids.
View 1 Replies
View Related
Aug 5, 2004
Hi All...
if i had the following sp...
*******************************************************
create procedure my_insert (param1 int, param2 int, paramx int)
as
...
complicated insert routine
...
return
*******************************************************
and then i wanted to exec this sp in another procedure i would have
exec my_insert( 1_value, 2_value, 3_value )
My question is how could i exec this will the result set of a select.... something like this
exec my_insert (select 1_value, 2_value, 3_value from another_table).
I know i could have this in an insert result type statement ie...
insert into dest_table (select 1_value, 2_value, 3_value from another_table)
but my insert routine is quite complicated and carries out some other functions so I would like to call (exec) a sp rather than repeating the complication in the select statement
Many Thanks
Gary T
View 4 Replies
View Related
Sep 14, 2006
The developers of frond end ask to select only one row from one table, but many times, such as 10. That means the output has 10 rows and same.
Any suggestion will be appreciated.
ZYT
View 4 Replies
View Related
Nov 7, 2005
Hello,The select statement needs only 1 second to complete the query.But the update statement spends 30 minutes. Why?SELECT STATEMENT:declare @IDate smalldatetimeselect @IDate=col001 from USDayselect * from USDay Ajoin (select US990010, US990020, US990030, US990040, US990050, US990060,US990070 from US99000D where US990010=@IDate) B on A.col001=B.US990010 and A.col002=B.US990020where B.US990010 is not nullUPDATE STATEMENT:update US99000Dset US990030=A.col003,US990040=A.col004,US990050=A.col005,US990060=A.col006,US990070=A.col007from USDay Ajoin (select US990010, US990020, US990030, US990040, US990050, US990060,US990070 from US99000D where US990010=@IDate) B on A.col001=B.US990010 and A.col002=B.US990020where B.US990010 is not nullINDEX:clustered index: US990020, US990010non-unique index: US990010, US990020
View 4 Replies
View Related
Nov 2, 2006
Hello
Im searching for a solution to set all matrix row or cell the same height.
it schoud looks like this example:
This is a simple matrix
test a
text b
text c
text d
text e
text f
text g
This is a matrix with all the same row-height.
test a
text b
.
text c
.
.
text d
text e
text f
text g
.
.
Thx you a lot
View 3 Replies
View Related
Nov 19, 2015
There are 3 tables Property , PropertyExternalReference , PropertyAssesmentValuation which are common for 60 business rule
SELECT Â
 PE.PropertyExternalReferenceValue  [BAReferenceNumber]
, PA.DescriptionCode
  [PSDCode]
, PV.ValuationEffectiveDate
  [EffectiveDate]
, PV.PropertyListAlterationDate
  [ListAlterationDate]
[code]....
Can we push the data for the above query in a physical table and create index to make the query fast rather than using the same set  tables multiple timesÂ
View 11 Replies
View Related
Aug 16, 2007
Dear All
I need to cerate a SP that SELECTS all the records from a table WHERE the first letter of each records starts with 'A' or 'B' or 'C' and so on. The letter is passed via a parameter from a aspx web page, I was wondering that someone can help me in the what TSQL to use I am not looking for a solution just a poin in the right direction. Can you help.
Thanks Ross
View 3 Replies
View Related
Aug 6, 2007
SELECT Top 10 Name, Contact AS DCC, DateAdded AS DateTimeFROM NameTaORDER BY DateAdded DESC
I'm trying to right a sql statement for a gridview, I want to see the last ten records added to the to the database. As you know each day someone could add one or two records, how can I write it show the last 10 records entered.
View 2 Replies
View Related
Aug 17, 2005
I have an unusual problem. I am using VB.Net 2003 and sqlexpress using .NET dataset to insert records into an timecards table. After inserting several records I tried a 'Select * from timecards' and the inserted records where not selected. if I 'select * from timecards order by employee' ( or any other field) the inserted records are selected! The table was created by an Access Upsize command.
Any suggestions?
Thanks!
GordonG
View 13 Replies
View Related
Aug 30, 2006
A basic select statement to be used.
View 3 Replies
View Related
Apr 30, 2007
Hello,I am selecting some articles and some comments related with it:SELECT a.ArticleID, a.Title, a.Content, c.CommentId, c.Title, c.Comment, u.UserName AS ArticleAuthorName, u.UserEmail AS ArticleAuthorEmailFROM Articles aINNER JOIN Users u ON a.AuthorID = u.UserIDINNER JOIN Comments c ON a.ArticleID = c.ArticleIDI have 2 problems which I am trying to solve:1. Comments table also have an AuthorId So for each comment I also want to join to Users table and get the author name and email. How can I do this?2. I want to select all Articles even if it has comments or not. Can I use Inner Join or should I use Left Join? Is Outer Join still available in SQL 2005?Thank You,Miguel
View 2 Replies
View Related
Jul 14, 2007
Hi there,
I'm new to SQL.
I have encoutered a problem, I know how to select top 10 records from the database, but what about 11-20?
I can't use
Quote:
View 3 Replies
View Related
Jan 12, 2006
I have a simple table something like this
ID First Last
1 Bob Hope2 Charles Draw3 Mark Andrews
Etc, etc
I need the SQL statement select the last 10 records in the table, so if the highest ID id 82 I need all fields of records with ID 72-82
Help appreciated, i'm sure it must be simple enough buy mind is blank.
thanks
View 1 Replies
View Related
Jul 14, 2007
Hi there,
I'm new to SQL.
I have encoutered a problem, I know how to select top 10 records from the database, but what about 11-20?
I can't use
Quote: SELECT TOP 10 * FROM table WHERE id > 10
because even though my ID is auto numbered, but I have deleted some entries.
Does anyone have good solutions?
p.s I tried this and doesn't work either
Quote: SELECT TOP 10 * FROM table WHERE id IN(SELECT TOP 20 * FROM table ORDER by date DESC) ORDER BY date ASCENDING
TIA
-noPcz
View 5 Replies
View Related
Aug 16, 2005
Stijn writes "Hello,
I have SQL 7 with WINDOWS 2000 SERVER
My problem is that i am looking for a script
my db is for equipement inventory
how can i select all the records from a room,
if this room (room1)has less of 100 records he needs te go to the follow room(room2), en the next room until he's got 100 records,
sorry for my english"
View 2 Replies
View Related
Aug 18, 2005
Mack writes "I want to select last 2 records from a table AA which has following 5 records.
111
222
333
444
555
There is no primary key.
Any tip will be really appretiated.
Thanks for taking time to read it."
View 2 Replies
View Related
Oct 4, 2007
Hi,
How to write a query to get top 50 distinct records from a table.
I mean I have a table 'C1_Subscribers' with a field name 'Nickname'. Here I need to select top 50 non-repeated nickname only.
Can anybody can help me!!
View 3 Replies
View Related
Jul 23, 2005
Hello,Firstly, sorry for my english.I have problem with creating SQL statement. I am beginner and I think thatit is very easy to do. Look -I have to get only the last 20 records from table ABC (f.eg.) and accordingto that how to create the SQL statement? F.eg. SELECT name, forename, dateFROM abc WHERE....You know - how to finish the statement to get only 20 records lastlyinserted into the table?Thanks and I am waiting for helpfull answers.
View 3 Replies
View Related
Jan 2, 2008
I have a table, productTable, that contains a ProductID column, a bunch of attribute columns like manufacturer etc, and a GroupID. Many different productIDs can share the same GroupID which means that they are basically the same product with one different attribute such as color. What I'm trying to do is select all the products in the TOP N GroupIDs. I'm using this to paginate my query results. I tried this which does not work:
SELECT DISTINCT TOP 10 *
FROM productTable
WHERE GroupID NOT IN
(SELECT DISTINCT TOP 10 GroupID
FROM productTable
ORDER BY GroupID)
ORDER BY GroupID
This returns the TOP 10 rows, but I would like all the ProductIDs for the TOP 10 GroupIDs
View 7 Replies
View Related
Jul 20, 2005
I can't get my head around this:I want to select all IDs from table A that do not have a related record intable B according to some condition:Table A contains, say, Parents and table B contains Children. I want toselect all Parents that have no children called "Sally" (this is a noddyexample, reminds me of being at Uni again :) ).Any ideas?Thanks
View 2 Replies
View Related
Apr 29, 2007
Hello,I have two tables, Articles and Comments, with the following columns:Articles - [ArticleId] (PK), [ArticleTitle], [ArticleText], [ArticlePubDate]Comments - [CommentId] (PK), [ArticleId] (FK), [CommentTitle], [CommentText]I need to display on a web page the articles published during the last week and their comments.What I need is:1. Get Articles (DONE) 2. Get Comments for each ArticleThe solution I see are:1. Create a Stored procedure that somehow outputs 2 tables: Articles and Comments associated with those articles2. Create 2 Stored procedures: The first one outputs the articles. The second output all comments given an article ID In this case, while the data is being displayed on the page it will load the comments for each article. The problem is that I will have many round trips to the server.I know how to use (2) but this would give me many round trips to the database.Could someone help me out with this?Thanks,Miguel
View 4 Replies
View Related
Oct 10, 2007
Hello, I have the following tables: declare @B table (Bid int identity, description varchar(50)) declare @P table (Pid int identity, Bid int, description varchar(50)) declare @T table (Tid int identity, description varchar(50)) declare @TinP table (TinPid int identity, Tid int, Pid int) insert into @B (description) select 'B1' insert into @B (description) select 'B2' insert into @P (description, Bid) select 'P1', 1 insert into @P (description, Bid) select 'P2', 1 insert into @P (description, Bid) select 'P3', 2 insert into @T (description) select 'T1' insert into @T (description) select 'T2' insert into @T (description) select 'T3' insert into @TinP (Tid, Pid) select 1, 2 insert into @TinP (Tid, Pid) select 2, 2 insert into @TinP (Tid, Pid) select 3, 3 select * from @B select * from @P select * from @T select * from @TinP I need to get all records in T (Tid and description) which are related to a given BId So for @Bi = 1 I would get: Tid Description 1 T1 2 T2 So I need the distinct values. How to solve this? Thanks, Miguel
View 1 Replies
View Related
Nov 23, 2007
Hello,
I am writing a piece of code in ASP.NET and I'd like to get the # of records on a table and used this code:
Dim ConnString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='G:Aco ProntoBSCBSC_v1.mdb'"Dim Con As New OleDbConnection(ConnString)
Dim Cmd As New OleDbCommand("SELECT COUNT(*) AS Expr1 FROM Metricas", Con)Dim reader As OleDbDataReader
Con.Open()
reader = Cmd.ExecuteReader()Dim NumMetr As Integer = Val(reader("Expr1"))
reader.Close()
Con.Close()
I am getting an error that that's no data in the table.
Any suggestions?
View 1 Replies
View Related
Apr 6, 2004
I am trying to select all records added between 2 dates that the user inputs into a form and am having problems. I had this working no problems with asp but can't seem to get it working with .net. BTW I am using SQL Server and Visual Studio.
The asp.net code I am trying to use is:
Me.SqlSelectCommand1.CommandText = "SELECT news_title, news_date, news_type, news_link FROM news WHERE (news_type = 'news') AND (news_date BETWEEN CONVERT(DATETIME, '"" & startdate & ""', 102) AND CONVERT(DATETIME, '"" & enddate & ""', 102))"
....
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim startdate As DateTime
startdate = Request.Form("date_from")
Dim enddate As DateTime
enddate = Request.Form("date_to")
SqlDataAdapter1.Fill(DataSet1)
Repeater1.DataSource = DataSet1
Repeater1.DataBind()
End Sub
With this I am getting the following error:
"Syntax error converting datetime from character string. "
So I am assuming it is something to do with the way I am getting the date from the form as when I hardcode the dates in it works???
Any help would be greatly appreciated, thanx
View 1 Replies
View Related
Nov 14, 2005
Hi world,Normally we receive the results of a query in several or thousands of rows. Select * From Clients-------------------------Row1. Client1Row2. Client2....Which is the the way to have everything on the same row separated by commas?Row1. Client1, Client2...thxDavid
View 1 Replies
View Related
Sep 21, 2007
Hello!
I have a table, where one of the columns is the date/timestamp of when each row was inserted. I want to be able to extract the most recently inserted rows.
With Sybase (a not so distant cousin of MS SQL) the following works:
select * from TABLE having date = max(date)
With MS SQL, however, the same query does not work:
Column 'TABLE.date' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
What's the solution? Thanks!
View 14 Replies
View Related
Jan 10, 2013
I have table that I need to retrieve the top 2 records, the issue is I have 3 records with the same date, but I only want the first 2. Each record looks something like this.
id, team, date, setnr, series
1, 3, 1/1/2013, 1, 1102
1, 3, 1/1/2013, 2, 1231
1, 3, 1/1/2013, 3, 1023
1, 3, 1/5/2013, 4, 1024
1, 3, 1/5/2013, 5, 1123
1, 3, 1/5/2013, 6, 1232
2, 2, 1/1/2013, 1, 1032
2, 2, 1/1/2013, 2, 1221
2, 2, 1/1/2013, 3, 1023
2, 2, 1/5/2013, 4, 1231
2, 2, 1/5/2013, 5, 1112
2, 2, 1/5/2013, 6, 1231
I have to be able to add the series up of only the first two records for each id based on date. Here is a sample query
select sum(series), date from table group by date order by sum(series) desc
This gives me the total for all three and gives it to me in descending order. I need the records for set 1 and 2 of each of the Id. There are many records but the date and the setnr doesn't duplicate.
View 1 Replies
View Related
Apr 22, 2008
Hello,
Is it possible to select 10 random records from a table?
Thanks,
Miguel
View 1 Replies
View Related
Jun 11, 2008
Hi guys
I have a table that stores records and each receord has a default logflag value of 0.Each time any record is changed,a new record is inserted with similar vaues but logflag flag remains 0.The original record that was modified will is kept but will have its log flag set to 1.
Now i want to display the two records in a report one on top of the other so that users can clearly see what chages were made for audit purposes.
How do i do my select statement so that for each record that was modified,the original one(logflag 1) is displayed and the new modified one(logflag 0 ) is also displayed just below it.
ie
id name email address city logflag modifieddate
23 rob rob@me.com oxford lane wolverton 1 20/6/2008
23 rob robe@me.com huge road burnside 0 null
The same should be done for all the modified records.
When a record is has not been modified,it should not be displayed by the select statement.
Thanks in advance
In god we trust,everything else we test.
View 4 Replies
View Related
Jan 24, 2014
I am trying to write a script that will only insert records that do not exist to a local table. Whenever a form is changed, it keeps the same name, but a new record is created with a new ID. I need to be able to pull the new record by checking for ID's that are not on the local table, but using a form name to isolate. The nested select I am attempting is
select distinct qf.[eform_id]
, qf.[name]
, qf.[description]
from [qfiniti].[qfiniti_platform].[dbo].[eval_forms] qf
inner join FORMS_REF fr on qf.[name] collate database_default = fr.form_name collate database_default
where not exists (select fr.eform_id
from forms_ref fr
inner join [qfiniti].[qfiniti_platform].[dbo].[eval_forms] qf on fr.eform_id = qf.eform_id);
It is outputting nothing, but I have intentionally deleted one form record to force it to show up.
View 4 Replies
View Related
Feb 11, 2015
Here is my table data:
CREATE TABLE
#TestTable (
Pk INT,
GroupID INT,
Enabled BIT
[code]..
I need to write a select query that will retrieve any GroupID in which every record has an Enabled value of 1.In the example I've provided, only GroupID 1 and 3 will be returned since GroupID 2 has a record with an Enabled value of 0.What would be the most efficient way to write such a query?
View 2 Replies
View Related