I'm trying to return the Max and Min Date value for each Item in a History table. Thanks for any help. Example:
Code Block
ID Item Date
1 A 12/1
2 A 11/1
3 A 10/1
4 B 9/1
5 B 8/1
6 B 7/1
The result I would be expecting is:
Code Block
Item Max Min
------------
A 12/1 10/1
B 9/1 7/1
CREATE TABLE #Dateissue
(ID int,
Code nvarchar (20),
Datein datetime,
Declined datetime )
[Code] .....
I have a table here.  I want  find a way of getting the latest date, when the code is the same.  If the Declined date is null.  Then I still want the latest date.  E.g. ID 3. Â
If the declined date is filled in. Â Then I want to get the row, when the Datein column value is greater then the declined date only.
I tried grouping it by max date, but  i got an error message when trying this out.  Against the code Â
WHERE MAX(Datein) > Declined
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference. Â What do I need to do to get both my outputs working?Â
Hi,
I have soma ado.net code that inserts 7 parameters in a database ( a date, 6 integers).
I also use a self incrementing ID but the date is set as primary key because for each series of 6 numbers of a certain date there may only be 1 entry. Moreover only 1 entry of 6 integers is possible for 2 days of the week, (tue and fr).
I manage to insert a row of data in the database, where the date is set as smalldatetime and displays as follows: 1/05/2007 0:00:00 in the table.
I want to retrieve the series of numbers for a certain date that has been entered (without taking in account the hours and seconds).
A where clause seems to be needed but I don’t know the syntax or don’t find the right function
I use the following code to insert the row :
command.Parameters.Add(new SqlParameter("@Date", SqlDbType.DateTime, 40, "LDate"));
command.Parameters[6].Value = DateTime.Today.ToString();
command.ExecuteNonQuery();
and the following code to get the row back (to put in arraylist):
“SELECT C1, C2, C3, C4, C5, C6 FROM Series WHERE (LDate = Today())�
WHERE LDate = '" + DateTime.Today.ToString() + "'"
Which is the correct syntax? Is there a better way to insert and select based on the date?
I don’t get any error messages and the code executes fine but I only get an empty datatable in my dataset (the table isn’t looped for rows I noticed while debugging).
Today’s date is in the database but isn’t found by my tsql code I think.
Any help would be greatly appreciated!
Grtz
Pascal
Hi --
What is the proper way to select results with a date in the where statement when the datatype of the column is datetime?
select * from table where date_field = '3-23-2006' does not get any results.
Thank-you for your help.
Hello,I have a couple of tables. The client tables and the contactedtables.I am not sure how to start on this, what I need is a way to query allmy clientsthen show any client that the last visit and or called day is greaterthan 30 days.Now it gets confusing, Suppose the client was visited more than 30 daysagobut was called only 10 days ago, I really would like to have thisappear on the samequery.So the report would look similar to this below.Visit Date Called DateClientA 2006-11-02 2006-12-16ClientB 2006-12-17 2006-10-30ClientC 2006-10-15 2006-10-16ClientDFields (Simplified)Clients: Name, Address, Phone.Contacted: Name, Date, Visit, Call.I need to query all l names, but I only need the last visit and lastphone call. Then determine if either date is greater than 30 days ifso, display the last date of each type of contact. And if there isnothing for the client in the contacted table this needs to show also,ClientD.Any tips, ideas would be greatly appreciated....ThanksIce
View 9 Replies View Related I'm having a mental block on a select statement.I have a table with the following columns:KitId int
LotNo varchar(10)
DateReceived smalldatetimeIt is possible to have many rows with the same LotNo but a differing DateReceived I need to write a select statement that returns the KitId for a given LotNo with the earliest DateReceivedso if I had rows:KitId =1, LotNo = 123, DateReceived = 11th May 2008KitId =2, LotNo = 123, DateReceived = 28th May 2008KitId =3, LotNo = 125, DateReceived = 28th May 2008KitId =4, LotNo = 127, DateReceived = 28th May 2008KitId =5, LotNo = 123, DateReceived = 12th June 2008I would want to retrieve KitId=1 if I provided LotNo 123 as a parameter Whilst it should always be the case that the LotNo with the earliest date will have the lowest KitId I cannot guarantee that will be the case so going for the lowest KitId isn't an optionCan one of you SQL gurus provide me with the statement I need?ThanksNeil
I have table with column Date Of Birth its datatype is smalldatetime. Now I was looking for SQL Statement like I will give from date and to date as parameter it should select date of birth occurring between that date and month.
View 5 Replies View RelatedSo I have this query where i need to get the average date of about five different dates... Is there any way to do this or am I screwed. I looked at using the avg function but SQL server 2005 did not like that.
Thanks in advance for any help.
Hello all. I ma using the following query to pull back data. The MergeHistory table has a column named DateMerged. I am looking to pull back the one record with the most recent DateMerged. I have managed to get the query as far as below but not sure how to select the most recent one. Can anyone help with this? I was told it may be along the line of SELECT TOP 1 or something?
INSERT INTO @List (IndexID, IndexName, MergeSystem, Status, DateCreated, CreatedBy, DataTag, MergedDate)
SELECT DISTINCT
RT.IndexId,
isnull(dbo.ufn_GetBestIdentifier(RT.IndexId), dbo.ufn_GetBestVirtualIdentifier(RT.IndexId)),
dbo.ufn_GetEntitySystemName(RT.IndexId),
RT.Status,
CONVERT(varchar, RT.DateCreated, 106) as DateCreated,
RT.CreatedBy,
RT.DataTag,
MH.MergedDate
FROM @resulttable AS RT, MergeHistory AS MH
WHERE RT.IndexId = MH.EntityID
Hi,
I've been wrestling with this problem for a while, but my newbie SQL skills are no match for it, so I'm hoping somebody here can point me in the right direction.
I have the following table, called AccountPayments:
PaymentID CaseID_FK AmountPaid InvoiceDate PaymentDate
12135 250 01/16/2006 01/31/2006
13136 150 01/16/2006 01/31/2006
14 135 200 01/24/2006 02/05/2006
15 136 300 01/24/2006 02/05/2006
16139 120 01/25/2006 02/07/2006
17 136 100 01/25/2006 02/10/2006
18 137 115 01/25/2006 02/13/2006
19 136 110 02/12/2006 02/25/2006
20139 310 02/12/2006 03/06/2006
21 138 220 02/15/2006 03/09/2006
I would like to select all the entries where the payment date is, at the latest, the 7th day of the month following the one in which the invoice was issued.
In other words: If the invoice date is in January, I would like to select all the entries where the payment date is February 7th at the latest. If the invoice date is in February, I would like to select all the entries where the payment date is March 7th at the latest.
So, for the above table, I would like to get the following result:
PaymentID CaseID_FK AmountPaid InvoiceDate PaymentDate
12135 250 01/16/2006 01/31/2006
13136 150 01/16/2006 01/31/2006
14 135 200 01/24/2006 02/05/2006
15 136 300 01/24/2006 02/05/2006
16139 120 01/25/2006 02/07/2006
19 136 110 02/12/2006 02/25/2006
20139 310 02/12/2006 03/06/2006
Does anybody know if it's possible to do this? I'm working with SQL Server 2000 and have been playing around with dateadd, but I can't seem to figure it out.
Thank you for your help.
I need to select the most recent price for an item from this table where certain criteria are met:
flexing_stock_transactions
--------------------------
item date price status week firm_or_commission
---- -------- ----- ----- ---- ------------------
CH 10/05/07 200 2 35 F
AL 10/05/07 195 1 35 C
CH 10/05/07 209 1 35 F
CH 11/05/07 210 1 35 C
I am currently using:
SELECT
price, date
FROM flexing_stock_transactions
WHERE (
[date] = (SELECT MAX([date]) FROM flexing_stock_transactions)
AND item = 'CH'
AND status = '1'
AND week = '35'
AND firm_or_commission = 'F'
)
zero rows are returned wheras I was expecting:
209, 10/05/07
I'm using MS Server 2005
Good Morning,I have a view that contains rate information, contractIDs, and effectivedates.I need to select the rate info based on contractID and date.I can provide a date and contractID, and I need to select the rate info forthat contract where the effective date is <= dateprovided.I need the 1 record that is closest to that date.I am thinking something with max() perhaps. Any ideas? The <= effectivedate will return several rows, I just need the one closest to the date Iprovide.Thanks for any advice,CK
View 2 Replies View RelatedI'm trying to construct code that will return the last non-NULL value in a column containing daily records.
For E.G. I want to know what the LAST value of Description field when it is not NULL, AND the Date is within the range t=1 to t=5 => i.e. "Dog" in the below example:
Date Description
1 NULL
2 NULL
3 Cat
4 Cat
5 Dog
6 NULL
7 NULL
8 Mouse
9 NULL
10 NULL
How this can be achieved?
I have to select rows based on if the transaction date = todays date.The column is defined as numeric 8 with 0 decimal.how to code for todays date with such a column?
View 8 Replies View RelatedI have table like this and I need to remove the duplicates selecting the Min Date
IDDate Alloc ProdWater Tub Cas
3752011-10-10 00:00:00.00000014.70
3752011-10-11 00:00:00.00000014.70
3232014-04-24 00:00:00.000000352555.7
3232014-04-26 00:00:00.000000352555.7
3562014-04-01 00:00:00.000000318.8471.1
3562014-04-02 00:00:00.000000318.8471.1
3562014-04-03 00:00:00.000000318.8471.1
3562014-04-04 00:00:00.000000318.8471.1
3232014-05-20 00:00:00.000000649.1976.9
3232014-05-21 00:00:00.000000649.1976.9
3232014-05-22 00:00:00.000000649.1976.9
I would like my result set to be like this:
3752011-10-10 00:00:00.00000014.70
3232014-04-24 00:00:00.000000352555.7
3562014-04-01 00:00:00.000000318.8471.1
3232014-05-20 00:00:00.000000649.1976.9
The following is the sql being executed in my Crystal report. There seems to be an issue with the same date request. Sometimes it shows data, other times not. We have data every day, we are a mass market company. Is there anything i can do for the sql to do select when from and to dates are the same?
SELECT "OEHIS1"."ODORD#", "OEHIS1"."ODORDT", "OEHIS1"."ODNTU$", "OEHIS1"."ODSHP#", "ICPRT1"."IARC11", "OEHIS1"."ODORDD", "ICPRT1"."IARCC4", "OEHIS1"."ODQTY#", "OEHIS1"."ODRQSD", "MFHHMH"."MHAWGT", "OEHIS1"."ODPRT#", "OEHIS1"."ODPRLC"
FROM ("S10M10"."ASTCCDTA"."EODDETAILS" "OEHIS1" INNER JOIN "S10M10"."ASTDTA"."ICPRT1" "ICPRT1" ON "OEHIS1"."ODPRT#"="ICPRT1"."IAPRT#") INNER JOIN "S10M10"."DLIB"."MFHHMH" "MFHHMH" ON "OEHIS1"."ODORD#"="MFHHMH"."MHORDP"
[Code] ....
Here's my current SQL:
SELECT
RN_TEST_ID AS 'Test ID',
MAX(RN_EXECUTION_DATE) AS 'Last Execution Date',
MAX(RN_EXECUTION_TIME) AS 'Execution Time',
RN_DURATION AS 'Run Duration'
FROM RUN
[code]....
Here's a sample of data returned:
Test IDLast Execution DateExecution TimeRun Duration
86722/9/2015 0:0012:08:16180
86822/9/2015 0:0011:29:06181
86842/9/2015 0:0008:29:17119
105252/3/2015 0:0019:03:4089
105252/3/2015 0:0019:10:13305
106682/3/2015 0:0018:55:43103
106682/6/2015 0:0018:10:50123
114572/3/2015 0:0011:40:0726
What I need are two things:
1. The query should only return one record for each test id
2. The record returned should be the most recent. By most recent I mean the RN_EXECUTION_DATE and RN_EXECUTION_TIME of the returned row should be the most recent in time.
For example, in the sample data there are multiple rows with the same test id (for example 10668 and 10525. The 10525 is even more problematic since its execution date is the same for both rows returned - the execution times differ. Again, I want one record per test id and that record should be the most recent in time.
Using MS SQL 2005, how can I SELECT only the records whos date field is equal to the most recent date held in that field?
For example:
date item colour
---- ---- ------
12/15/2007 shirt red
12/13/2007 shirt black
12/15/2007 shirt blue
result(s) expected:
12/15/2007 shirt red
12/15/2007 shirt blue
I have the following function(s) that each joins an active record withit's most recent record in an audit trail table, to show differences.I've perused the execution plans for 2 versions of this function in QAand it seems to me that the two versions execute identically - that is,the addressing the sub-query of the WHERE clause with either IN or =results in the same execution plan.Does this seem plausible or did I misinterpret something in QA?---ALTER FUNCTION Function9()RETURNS TABLEAS RETURN(SELECT ID, Numb, Name, Q.Diff_ID, Q.Diff_Numb, Q.Diff_Name,FROM tblQuestion INNER JOIN(SELECT ID AS Diff_ID, Numb AS Diff_Numb, Name as Diff_NameFROM tblQuestion_AuditWHERE AuditDate IN (SELECT MAX(AuditDate) FROM tblQuestion_Audit)) AS QON dbo.tblItem.ID = Q.Diff_ID)ALTER FUNCTION Function10()RETURNS TABLEAS RETURN(SELECT ID, Numb, Name, Q.Diff_ID, Q.Diff_Numb, Q.Diff_Name,FROM tblQuestion INNER JOIN(SELECT ID AS Diff_ID, Numb AS Diff_Numb, Name as Diff_NameFROM tblQuestion_AuditWHERE AuditDate = (SELECT MAX(AuditDate) FROM tblQuestion_Audit)) AS QON dbo.tblItem.ID = Q.Diff_ID)
View 3 Replies View RelatedI have a table that has a DateTime column which uses a DataTimedatatype. How do I retrieve a range of records based on the month andyear using ms sql?Eugene Anthony*** Sent via Developersdex http://www.developersdex.com ***
View 4 Replies View RelatedI have 6 columns, all with dates within them, i.e.Proposed Start Date 1Proposed Start Date 2Proposed Start Date 3Proposed Finish Date 1Proposed Finish Date 2Proposed Finish Date 3What I need to do is narrow this down into two fields:Start DateFinish DateSo I need to find the newest value from the columns. i.e.If PS1 filled PS2 and PS3 empty, then Start Date = PS1If PS3 empty and PS2 filled, then Start Date = PS2If PS3 filled then PS3and similarly for Proposed Finish Dates.Anyone knew how I can do this.(Maybe the following will help for the programmers out there:If PS3 <> nullThen StartDate = PS3Else if PS2 <> nullThen StartDate = PS2ElseStartDate = PS1End IFJagdip
View 5 Replies View RelatedOK. I have this query, works on another box fine.
SELECT *
FROM bookkeep RIGHT OUTER JOIN
acraccts ON LEFT(bookkeep.accnum, 9) = acraccts.p_accnum
WHERE (bookkeep.busdate = '03/09/10') AND (bookkeep.tradetype = 'S')
on my sql box, if i run it, i get no data.
i figured out that if i change the where clause to (bookkeep.busdate='2003/09/10') it works
OR
if i simply put SET DATEFORMAT YMD on the first line before the SELECT * that it also works.
my problem is the basic query is hard coded and i really can't change it.
is there a global sql server setting that will make my sql 2000 sp3 box recognize '30/09/10' as 2003/09/10?
Hi,
I did some tsql a year or so ago, it wasnt much but it allows me to do most things i need to do in my job.
I have created a new database for bookings, each booking has all the relevant details and also the date that it will occur. im trying to create a script that will look for the 10 soonest dates that there is no booking for. at first i thought it would be easy, but now once i have got round to creating it i cant seem to think of a way to make it work!
TABLE: BOOKINGS
FIELD: DATE_BOOKED
has anybody advice on how i can do this?
many thanks,
Ben
In SQL 2005 I have the following view:
SELECT TOP (100) PERCENT StockCode, Warehouse, QtyOnHand, QtyAllocated, QtyOnOrder, QtyOnBackOrder, DateLastSale, DateLastStockMove,
DateLastPurchase
FROM dbo.MBL_VW_AgedStock_Sales
ORDER BY StockCode
This basically shows a list of stock codes (there are multiple stock codes the same) and the last sold date. What i need to do is group the stock codes which are the same together, and show the latest date.
For example I could have the following:
STOCK CODE Last Date Sold
PC1113 11/01/2007
PC1104 15/03/2007
PC1113 15/02/2007
What I want to see is a list that shows PC1113 with its latest sold date, i.e.
STOCK CODE Last Date Sold
PC1113 15/02/2007
PC1104 15/03/2007
Any ideas?
Thanks
Kris
When im using the below query im getting the output, but when i change the starting date to 2006 I'm not getting the data for 2007 though it falls between the 2006 and 2008 range...
select * From dbname..tbl where date>= '03/jan/2007' and date <= '11/feb/2008' and Status= 'C' and ID is not null
AND (ACCOUNT = '25869' or ACCOUNT = '0' + '25869' )
Check and post your comments ASAP...
Hi,
I was wondering how you perform a select statement based on a specific date that will show all the records no matter which times belong to the specific date.
I have been having trouble with this one when using a single date, I think this is because of the time property as no records are displayed.
Thanks for any help.
I've two audit tables, AUDIT_ORDERS and AUDIT_ORDER_LINES.
The AUDIT_ORDERS has these columns: AUDIT_ID, ORDER_ID, AUDIT_DATE and other ones.
The AUDIT_ORDER_LINES has these columns: AUDIT_ID, ORDER_ID, ORDER_LINE_ID, AUDIT_DATE and other ones.
I need to join these two tables in order to select for each order line row the first order having the related audit date lower than or equal to the audit date of the related order line.
I don't want to use the TOP 1 clause or a subquery. I think to complete a such statement:
SELECT OL.Order_Line_ID, O.Order_ID, OL.Audit_Date, O.Audit_Date
FROM AUDIT_ORDER_LINES as OL INNER JOIN AUDIT_ORDERS as O
on OL.Order_ID = O.Order_ID and O.Audit_Date <= OL.Audit_Date ...
I'd like to get the first row of the Audit_Orders with audit_date <= of the audit_date of the Audit_Order_Lines table by using the join clause.
Hello i currently have a website that has an SQL server 2005 dbs that stores appointments. I would like to do a select statement in my sqldatasource that selects all the records that have an 'appointmentDate' more than 2 weeks after the current date (ie the system date). I am stuck on the SQL statement i need to produce to achieve this. I was thinking along the lines of
SELECT * FROM appointments WHERE appointmentDate > System.Date + 14; However this is clearly not the right SQL statement. Any help would be appreciated.
Many thanks,
James.
I have a select statement which has to convert some date fields stored
as varchar based on critera of adding numbers to the date. What I have below
will return data show below but gives me an error message when it encounters
any data with null in the field. Is there something wrong in my conversion?
Thanks
declare @tdate varchar(10)
declare @rldate datetime
select @tdate = 20000630
select @rldate = @rldate + 2
select patient_.df_admit_date, patient_.df_ppdb_date from patient_
where (convert(datetime, df_ppdb_date, 101)+ 12) >= @tdate or
(convert(datetime, df_admit_date, 101) + 2 ) >= @tdate
19980910 20000626
19981215 20000627
19590114 20000621
20000629 NULL
20000706 NULL
20000711 NULL
(6 row(s) affected)
Server: Msg 242, Level 16, State 3, Line 5
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
So let's say I have a table Orders with columns: Order# and ReceiptDate. Order#'s may be duplicated (Could have same Order# with different ReceiptDate). I want to select Order#'s that go back 6 months from the last ReceiptDate for each Order#.
I can't just do something like:
SELECT *
FROM Orders
WHERE ReceiptDate >= add_months(date,-6)
because there could be Order#'s whose last ReceiptDate was earlier than 6 months ago. I want to capture all of the instances of each Order# going back 6 months from each last ReceiptDate relative to each Order#.
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?
When I run this query:
SELECT orderno, *
FROM _order
WHERE order_date >= '5/14/2015 00:00:00'
AND order_date < '5/15/2015 11:59:59'
ORDER BY order_date asc
I get a result of:
A1G7222015-05-14 13:00:11.143
A1G7232015-05-14 13:33:35.407
A1G7242015-05-14 13:39:16.657
A1G7252015-05-14 14:25:43.507
A1G7262015-05-14 14:29:18.050
A1G7272015-05-14 15:38:12.263
But I know there is one more record that falls into 05/15/2015
A1G7282015-05-15 12:26:52.807
Can you see what I am missing in my query in order for me to retrieve the missing record A1G728?
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.