TSQL Date Query Help
Nov 19, 2004
I have a table with a DateTime column called 'cutoff'. Is it possible to grab the TOP 1 record ordered by the cutoff column based on the current date?SELECT TOP 1 * FROM mytable WHERE cutoff >= GetDate() ORDER BY cutoff...works only if there is a cutoff < the current date. If GetDate() is greater then latest cutoff date it returns nothing. In this case, is it possible to just return the latest record? I'd like to keep this to 1 query if possible? :-)
View 1 Replies
ADVERTISEMENT
Aug 2, 2000
I want to do a simple query against some date fields. Some have a time and some are set to all zeroes.
When I do select * from table where datefield = '12/12/2000' I get only records that have zeroes in the time.
I thought the following would work select title, pubdate from titles where convert(varchar(8), pubdate,103) = '11/13/1998'
I get no rows with this one, any hints? Thanks
View 1 Replies
View Related
Mar 2, 2004
Is there a better way to write this query?
SELECT t1.title,t1.record_id,
(SELECT SUM(t2.amount1) FROM table2 t2 WHERE t2.parent_id = t1.record_id)AS amt1,
(SELECT SUM(t2.amount2) FROM table2 t2 WHERE t2.parent_id = t1.record_id)AS amt2,
(SELECT SUM(t2.amount3) FROM table2 t2 WHERE t2.parent_id = t1.record_id)AS amt3
FROM table1 t1
View 13 Replies
View Related
Feb 4, 2008
Hi all..
i have a question about TSQL,
how it is possible to write this querry correctly:
Code SnippetSELECT dtEvent.EventNo as "Event ID:", FROM LIKE '%dtEvent%' AS dtEvent,WHERE dtMachine.id = dtEvent.agentmachineid
in the "FROM" part is a problem....LIKE '%%' isn't correct, but i don't know how can i write this query correctly...
by the way: for example just "dtEvent" is known ...but not the fullname like "dtEvent_XXXXXXXX"
Thanks
View 2 Replies
View Related
Jan 30, 2006
I was hoping to get a little input on a problem I'm having.
In the DataAccessLayer of my application, I have a "search" function written that takes a bunch of parameters (in the form of a class object) and depending on what each of the paramaters are set to (to include search type parameters) it builds an appropriate select statement. The issue is that my company has recently decided to require all DataAccessLayer functions to use TableAdapters. TableAdapters can use StoredProceedures, and StoredProceedures can make external calls (it all seems a bit backward to me, but there does seem to be a bit of logic in that TableAdapters contain a connection string which is set in the app.Config file)... Anyway, here's an example of how I am doing it currently, and I was hoping someone could suggest a way I could do it with either TSQL, or otherwise. If CLR is the way to go, how does that work?
internal static dsStrongTypeDataSet GetAll(clsMyClass inData)
{
bool first = true;
dsStrongTypeDataSet data = new dsStrongTypeDataSet ();
string selectStatement = "Select * from tblMyTable where ";
//There is one of these if statements for each parameter
if ((inData.Paramater1 != null))
{
if (!first)//not as important in this section of code,
//but there are areas where there are up to 30 parameters
selectStatement += " and ";
if (inData.SrchParameter1 == SearchType.Fuzzy)
selectStatement += " Column1 LIKE ('%" + inData.Parameter1 + "%') ";
else if (inData.SrchParameter1 == SearchType.Literal)
selectStatement += "Column1 = '" + inData.Parameter1 + "'";
first = false;
}
//More if statements like above for EVERY parameter possible
SqlCommand selectCommand =
new SqlCommand(selectStatement, CorrectSqlConnection());
SqlDataAdapter dataAdapter = new SqlDataAdapter(selectCommand);
dataAdapter.Fill(data, "tblMyTable");
return data;
}
View 3 Replies
View Related
Apr 17, 2008
Hi,
I hope that someone can help me understand why my query is not returning what I expect. When I run this query:
SELECT DISTINCT(TransactionKey)
FROM Transactions_Fact
WHERE DateKey = 14550
AND TransactionKey BETWEEN 1 AND 90000000
AND TransactionKey NOT IN (SELECT DISTINCT(TransactionKey)
FROM tmpTransactions
WHERE TranDate = 14550
AND TransactionKey BETWEEN 1 AND 90000000)
I get 150 rows back, which is what I expect. However, if I leave out the 'AND TransactionKey BETWEEN 1 AND 90000000', then I don't get back anything?!?
SELECT DISTINCT(TransactionKey)
FROM Transactions_Fact
WHERE DateKey = 14550
AND TransactionKey NOT IN (SELECT DISTINCT(TransactionKey)
FROM tmpTransactions
WHERE TranDate = 14550)
Any ideas as to what I'm missing here? It seems like it should at least return the same 150 rows. Thanks for any help.
Gary Hines
View 6 Replies
View Related
Apr 10, 2008
I am trying to return an ouput parameter from my query. I have tested the stored proceedure extensivly within the SQL Management Studio, and I know it works fine, so that means the error is somewhere within my code, but for the life of my I cant figure where.
Here is my stored proc:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:Name
-- Create date:
-- Description:
-- =============================================
ALTER PROCEDURE [dbo].[tblSiteVisits_FindOfficerName]
@VisitID int,
@OfficerName varchar(100) output
AS
BEGIN
-- Variables
Declare @OfficerID int
--Declare @OfficerName varchar(100)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Select @OfficerID = (Select top 1
OfficerID from tblSiteVisitOfficers Where VisitID = @VisitID)
IF (@OfficerID Is Null)
BEGIN -- Get the None Registered Officer
Select @OfficerName = (Select top 1 OfficerOther from dbo.tblSiteVisitOfficers Where VisitID = @VisitID)
print 'Got unregistered Officer ' + @OfficerName
END
ELSE
BEGIN -- Get the Registered Officer
Select @OfficerName = (Select OfficerFName + ' ' + OfficerLname from dbo.tblOfficers Where OfficerID = @OfficerID)
print 'Got Registered Officer ' + @OfficerName
END
END
And here is the code I am using to access this proceedure:1 Dim blah As String
2 Dim conn2 As New SqlConnection()
3 Dim cmd2 As New SqlCommand()
4 conn2.ConnectionString = _ConnString
5 cmd2.Connection = conn2
6 cmd2.CommandType = CommandType.StoredProcedure
7 cmd2.CommandText = "dbo.tblSiteVisits_FindOfficerName"
8
9 cmd.Parameters.AddWithValue("@VisitID", Convert.ToInt32(row("VisitID")))
10 cmd.Parameters.Add("@OfficerName", SqlDbType.VarChar, 100)
11 cmd.Parameters("@OfficerName").Direction = ParameterDirection.Output
12 Try
13 conn.Open()
14 cmd.ExecuteNonQuery()
15 blah = cmd.Parameters("@OfficerName").Value.ToString()
16
17 Catch ex As Exception
18 Throw ex
19 Finally
20 conn.Close()
21 End Try
22
However there I never recieve the output value, and because of the way my database is structures, there is no possible way, that there is no output value.If anyone can help, that would be great, kind regards.
View 2 Replies
View Related
Sep 14, 2004
VIEW -ITMISSUES
Fields
issno-issdate-item-trxqty
1 - 010904-xyz - 2
2 - 020904-xyz - 5
3 - 080804-xyz - 6
4 - 020804-xyz - 9
VIEW-USR_VIEW_RPTS
Fields
receiptno- rptdate - item- rctqty
1 - 010804 - xyz - 10
2 - 010904 - xyz - 20
3 - 150804 - xyz - 25
4 - 150904 - xyz - 15
I have 2 views (ITMISSUES,USR_VIEW_RPTS) I want to retreive (Count of Issues Alis name)-noofissues,total issqty,
total (sum of Total Receipt Qty Alis name)- rctqty between a range of items and between a range of dates.How can i do it.
Result:
noofissues - totalIssqty - rctqty
2 - 15 - 35
i have tried with this Query
SELECT COUNT(DOCNUMBR)AS NOOFISSUES,ABS(SUM(TRXQTY))AS TOTISSQTY,
MAX(ITMISSUES.DOCDATE)AS LASTISSDATE,SUM(QTY) AS RCQTY
from ITMISSUES
INNER JOIN USR_VIEW_RPTS ON ITMISSUES.ITEMNMBR=USR_VIEW_RPTS.ITEMNMBR
WHERE ITMISSUES.ITEMNMBR BETWEEN 'xyz' AND 'xyz'
AND ITMISSUES.DOCDATE BETWEEN '2004-08-01' AND '2004-08-31'
View 1 Replies
View Related
Mar 8, 2006
Hi,I am using a .dqy file to import some rows from a sql server 2003database to microsoft excel. Unfortunately, I have am importing morerows than excel can handle. Is there a way around this?It appears that there is no equivalent of the LIMIT(offset, number ofrows to return) function of mysql in tsql. Had there been anequivalent, I would have created multiple .dqy files, which would querythe database for pages of results as appropriate. There is an upperlimit to the number of records that there can be in the database, sothis would have been a feasible solution.Also, I must use .dqy files (or something equivalen) because thequeries are invoked from a website, and it is necessary to download theresults, sort/filter, etc. (in excel).Thanks for any suggestions.
View 4 Replies
View Related
Sep 27, 2007
Hi All,
Please suggest me is there any performance/other differences between the below two queries.
----query1
select T1.name,T1.Number, T2.Dept, T2.Desig
From T1 Inner Join T2 on T1.EID = T2.EID
----query2
select T1.name,T1.Number, T2.Dept, T2.Desig
From T1 Inner Join (Select Dept, Desig From T2) As T2 on T1.EID = T2.EID
Thanks
Senthil
View 3 Replies
View Related
Mar 13, 2008
Hello.
Is there any possibility in SQL server 2005 to find out the source of some sql-query result? I need some kind of meta information about result I got. For example:
[if query is]
select
t1.id as id1,
t2.id as id2,
67 as col67
from t1, t2
[result will be]
| id1 | id2 | col67 | .......................
and at the end of all I need something like :
id1-from t1.id; id2-from t2.id; col67-unknown;
thanks for help
View 3 Replies
View Related
Aug 20, 2006
Anyone seen this before?
View 5 Replies
View Related
Sep 13, 2006
The title says it all. You can do it from a ADO.Net client so why not from TSQL.
It all uses the service broker stuff so why not?
View 5 Replies
View Related
Feb 13, 2004
Just Like these:
-- batch start
Select * from someTable --maybe a query which need much res(I/O,cpu,memory)
/*
can I do something here to free res used by prior statement?
*/
select * from someOtherTable
--batch end
The Sqls above are written in a procedure to automating test for some select querys.
View 4 Replies
View Related
Aug 30, 2007
Hello all:
Here is a sample query:
DECLARE @KEYID NVARCHAR (50) ; SET @KEYID = '1074958'
DECLARE @ENTITY NVARCHAR (100); SET @ENTITY = 'HouseDimension'
DECLARE @KeyCol NVARCHAR(50);
SET @KeyCol = (SELECT LEFT(@ENTITY, (SELECT CHARINDEX( 'DIM', @ENTITY) -1)) )+ 'Key'
DECLARE @KeyValue NVARCHAR (1000)
SET @KeyValue = 'SELECT '+ @KeyCol + ' FROM HouseManagementFact WHERE HouseKey = ' + @KEYID +
' GROUP BY ' + @KeyCol + ' HAVING SUM(TotalClaimCount) > 0 OR SUM(HouseCount) > 0 '
The value resulting from Executing @KeyValue is an integer.
I want to store this value in a new variable say @VAR2
When I do this
DECLARE @VAR2 INT
SET @VAR2 = execute sp_executesql @KeyValue
its giving me an error.
can somebody let me know the correct form of storing the value resulting from @KeyValue in some variable ?
View 3 Replies
View Related
Sep 29, 2015
how to write a query to get current date or end of month date if we pass year and month as input
Eg: if today date is 2015-09-29
if we pass year =2015 and month=09 then we have to get 2015-09-29
if we pass year =2015 and month=08 then we have to get 2015-08-31(for previous months we have to get EOMonth date & for current month we have to get current date).
View 3 Replies
View Related
Nov 19, 2007
Can anyone please give me the equivalent tsql for sql server 2000 for the following two queries which works fine in sql server 2005
1
-- Full Table Structure
select t.object_id, t.name as 'tablename', c.name as 'columnname', y.name as 'typename', case y.namewhen 'varchar' then convert(varchar, c.max_length)when 'decimal' then convert(varchar, c.precision) + ', ' + convert(varchar, c.scale)else ''end attrib,y.*from sys.tables t, sys.columns c, sys.types ywhere t.object_id = c.object_idand t.name not in ('sysdiagrams')and c.system_type_id = y.system_type_idand c.system_type_id = y.user_type_idorder by t.name, c.column_id
2
-- PK and Index
select t.name as 'tablename', i.name as 'indexname', c.name as 'columnname' , i.is_unique, i.is_primary_key, ic.is_descending_keyfrom sys.indexes i, sys.tables t, sys.index_columns ic, sys.columns cwhere t.object_id = i.object_idand t.object_id = ic.object_idand t.object_id = c.object_idand i.index_id = ic.index_idand c.column_id = ic.column_idand t.name not in ('sysdiagrams')order by t.name, i.index_id, ic.index_column_id
This sql is extracting some sort of the information about the structure of the sql server database[2005]
I need a sql whihc will return the same result for sql server 2000
View 1 Replies
View Related
Jul 23, 2005
Hi Group!I am struggling with a problem of giving a date range given the startdate.Here is my example, I would need to get all the accounts opened betweeneach month end and the first 5 days of the next month. For example, inthe table created below, I would need accounts opened between'5/31/2005' and '6/05/2005'. And my query is not working. Can anyonehelp me out? Thanks a lot!create table a(person_id int,account int,open_date smalldatetime)insert into a values(1,100001,'5/31/2005')insert into a values(1,200001,'5/31/2005')insert into a values(2,100002,'6/02/2005')insert into a values(3,100003,'6/02/2005')insert into a values(4,100004,'4/30/2004')insert into a values(4,200002,'4/30/2004')--my query--Select *[color=blue]>From a[/color]Where open_date between '5/31/2005' and ('5/31/2005'+5)
View 2 Replies
View Related
Nov 3, 2006
Can someone tell me sql query for filtering date field for current day,not last 24hours but from 00:00 to current time?
View 2 Replies
View Related
Nov 1, 2013
I'm currently using the SQL to find records older than todays date in the SSD_SED field. I'm having to update the date manually each day. Is there a way I can automate this?
AND (SSD_SED < '2013-11-01')order by SSD_SED DESC
View 3 Replies
View Related
May 9, 2008
Hi.
Me any my team are soon going to work on a performance critical application. My team has some experience of writing SQL, however we have not done performance oriented coding.
I am looking for a comphrehensive document which lists information for writing good SQL with performance. Please guide if there is such a document or web site.
Thanks,
Prasad
View 1 Replies
View Related
Apr 6, 2015
I have 2 tables, one is table A which stores Resources Assign to work for a certain period. The structure is as below
Name StartDate EndDate
Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
The table B stores the item process time. The structure is as below
Item ProcessStartDate ProcessEndDate
V 2015-04-01 09:30:10.000 2015-04-01 09:34:45.000
Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000
W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000
A 2015-04-01 16:40:10.000 2015-04-01 16:42:45.000
B 2015-04-01 16:43:01.000 2015-04-01 16:45:11.000
C 2015-04-01 16:47:00.000 2015-04-01 16:49:25.000
I need to select the item which process in 2015-04-01 16:40:00 and 2015-04-01 17:30:00. Beside that I need to know how many resource is assigned to process the item in that period of time. I only has the start date is 2015-04-01 16:40:00 and end date is 2015-04-01 17:30:00. How I can select the data from both tables. There is no need for JOIN, just seperate selections.
Another item process time is in 2015-04-01 10:00:00 and 2015-04-04 11:50:59.
The result expected is
Table A
Name StartDate EndDate
Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000
Table B
Item ProcessStartDate ProcessEndDate
A 2015-04-01 16:30:10.000 2015-04-01 16:32:45.000
B 2015-04-01 16:33:01.000 2015-04-01 16:35:11.000
C 2015-04-01 16:37:00.000 2015-04-02 16:39:25.000
Scenario 2 expected result
Table A
Name StartDate EndDate
Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Table B
Item ProcessStartDate ProcessEndDate
Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000
W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000
View 8 Replies
View Related
Nov 16, 2006
I have the following 4 rows in a table
Company JobNumber BeginDate ModifyDate
1 2 12/12/2005 11/12/2006
1 2 12/12/2005 11/15/2006
2 3 11/12/2005 1/12/2006
2 3 11/12/2005 9/15/2006
The company and Job Number make up the key so yes this table has duplicate keys. My question is how would I return the two keys with the max modify date?
So the results would look like this:
1 2 12/12/2005 11/15/2006
2 3 11/12/2005 9/15/2006
Thanks,
View 2 Replies
View Related
Mar 9, 2007
Good Morning to all
I wrote a query to access some data from sql server ,my query is as follows
strsel = "select * from schedule where sector_id='" & str_sec_id & "' And dep_date = " & bb & ""
bb is the date transferred from the other module
i want to check the dep_date as short date format.because bb is in short date format.
in the above checking i am not getting the query result
please look on this code
Regards
Unni
View 2 Replies
View Related
Apr 10, 2007
I have a single table with the following columns:
rowid (pk)
whenloaded
tablename
records
comments
I want to write a query to get the max(whenloaded) date and then each individual related column corresponding to that date IE. tablename, records, comments
Query I have now - Select a.tablename, max(a.whenloaded) From dbo.table_load_tracking a group by a.tablename
This returns the list that I desire although I am not sure how to add the additional columns and maintain the origional listed max(whenloaded).
I would like to understand the concept behind building a query like this so a detailed explanation would be greatly appreciated.
Thank You,
Fullyii
View 1 Replies
View Related
Apr 7, 2005
I have a table with the following structure: FacilityID, DepartmentID, NumberBeds, DateCreated. I record gets added per FacilityID-DepartmentID. Not every facility and every department within the facility needs to have an entry and there can me multiple entries on the same date. I want to create a pivot table of sorts, which I think I can do, as well as the joins to get the Facility Name and Department Name from other tables.
What I really am having problems figuring out right now is how to pull the last entered date per FacilityID-DepartmentID. Here is a sample of what the data looks like in the database and what I want to pull:
Hospital1 Cardiology 1 4/6/05 4:24 PMHospital1 Critical Care 8 4/6/05 4:24 PMHospital1 Med-Surg 3 4/6/05 4:24 PMHospital1 Pediatrics 0 4/6/05 4:24 PMHospital1 Psychiatry 0 4/6/05 4:24 PMHospital1 Telemetry 0 4/6/05 4:24 PM Hospital1 Cardiology 8 4/7/05 9:04 AMHospital1 Critical Care 6 4/7/05 9:04 AMHospital1 Pediatrics 4 4/7/05 9:04 AMHospital1 Psychiatry 0 4/7/05 9:04 AM
I need to have as output (items in red above):Hospital Card CritCare Med-Surg Peds Psych Telem Last UpdatedHospital1 8 6 - 4 0 - 4/7/05 9:04 AM
I'm truly stumped on this one and any help, examples or links to documentation would be greatly appreciated!
View 1 Replies
View Related
Sep 19, 2001
Hi All
Could someone please give me some guidance , I think I have totally lost it.
I am trying to get an age from a dateofbirth field.
1.This is what I have done :
select datepart(year,getdate())- datepart(year,cast(dateofbirth as datetime))
from table
The values are all correct expect everything that has a year of 1949 or below.
This comes thru as 2049 , 2048 , 2047 etc instead of 1949 etc.
I am converting the dateofbirth field to a datetime as this is orginally a character field.
Why would this be happening?
2.If I use this sort of query :
select dateofbirth , datepart(year,getdate())- substring(dateofbirth,1,2)
from table
I get the right age result but in this format - 1948 , 1951, which could then just use the last two digits for the age.
Would there be a simpler way of doing this?
Thanks in advance
Tanya.
View 4 Replies
View Related
Sep 22, 1999
The user enters in a date which is a varchar type and I'm trying to query the
database for all the birthdays of that date. The problem with SQL server is that when you do a search by the date, it makes a match with the timestamp of 12:00AM. So, if you search by a certain date, it also does a search by the time.
If John's birthday in the database is 'Jan 1 1999 08:00AM' and I use this
Select * from people where birthday = @UserInputDate,
the query returns NOTHING because the defualt time is 12AM and the date on that birthday is 08:00AM.
The only way I can get it to return ALL the birthdays of 1/1/1999 is with this statement:
select * from people where birthdate like 'Jan 1 1999 %:%'
But how do I convert the '1/1/1999' to the Jan 1 1999 format and append '%:%' at the end?
I've tried variations of the following:
select * from people where birthdate like @UserInputDate + ' %:%' but for some
reason that only appends one percentage sign '%:' and not '%:%'
Help!
Angel
View 3 Replies
View Related
Dec 3, 2004
Trying to perform a query where it will return all cases between the current day and the first day of the same month, regardless of whether it is this month or 6 months down the road.
WHERE Response_Flag = '1' AND Begun_By_Id = '" & Session("Emp_Id")& "' AND Begin_Date Between GetDate AND ???????
Any help would be greatly appreciated
Chris
View 1 Replies
View Related
Sep 27, 2005
I have to run a sql query to find out whether an employee has a birthday during the month of September. The date is in the format dd-mmm-yyyy (no time) and no matter what I do I can't get it to give me an answer.
So far I've tried LIKE, BETWEEN, >, <. Appreciate any help.
Thanks.
View 4 Replies
View Related
Nov 24, 2005
how would i query the table with dateid for a particular date
DateID
2005-11-24 14:40:00
select * from tbl1 where dateid like '2005-11-24%' ?
how about date and time like 9-11am of something
View 9 Replies
View Related
Jan 11, 2006
Hi!
I have a DB with this fields:
MID - primary key
EVENT - varchar(2)
EVENT_DATE datetime (format mm/dd/yyyy)
I need to design a query that will pickup records from DB with EVENT = '02' and EVENT_DATE equal or earlier than 18 days from the date the query was executed.
In this example, the query should display these 2 records if ran today:
MID EVENT EVENT_DATE
01 02 12/25/2005
02 02 12/20/2005
But not this:
MID EVENT EVENT_DATE
03 03 12/25/2005
04 02 01/01/2006
Any help will be greatly appreciated.
View 8 Replies
View Related
Jun 19, 2007
Hi all:
I am trying to get data that wasn't used in the last six month. Created_on (date field)
select Category_tree_value, Created_on from workitem
where Created_on (field was used from the last six months)
Created_on format (2005-10-01 00:00:00.000)
Thanking everyone in advance.
Lystra
View 1 Replies
View Related
