Transact SQL :: Selecting Latest Date But Over Another Date Column Including Nulls?

Nov 26, 2015

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? 

View 9 Replies


ADVERTISEMENT

Transact SQL :: Find Latest Start Date After A Gap In Date Column

Apr 19, 2015

My requirement is to get the earliest start date after a gap in a date column.My date field will be like this.

Table Name-XXX
StartDate(Column Name)
2014/10/01
2014/11/01
2014/12/01

[code]...

 In this scenario i need the latest start date after the gap ie. 2015/09/01 .If there is no gap in the date column i need 2014/10/01

View 10 Replies View Related

Transact SQL :: Find Latest Start Date After A Gap In Date Field For Each ID

Apr 23, 2015

My requirement is to get the latest start date after a gap in a month for each id and if there is no gap for that particular id minimum date for that id should be taken….Given below the scenario

ID          StartDate
1            2014-01-01
1            2014-02-01
1            2014-05-01-------After Gap Restarted
1            2014-06-01
1            2014-09-01---------After last gap restarted
1            2014-10-01
1            2014-11-01
2            2014-01-01
2           2014-02-01
2            2014-03-01
2            2014-04-01
2            2014-05-01
2            2014-06-01
2            2014-07-01

For Id 1 the start date after the latest gap is  2014-10-01 and for id=2 there is no gap so i need the minimum date  2014-01-01

My Expected Output
id             Startdate
1             2014-10-01
2             2014-01-01

View 4 Replies View Related

Selecting Latest Date

May 8, 2008

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

View 4 Replies View Related

Selecting The Latest Date From Different Columns

Jul 20, 2005

I 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 Related

Error Selecting Date Convert Fields With Nulls

Aug 3, 2000

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.

View 1 Replies View Related

Transact SQL :: Querying Data With Latest Date

Jun 24, 2015

Below is the table information. I want the Code information with latest EDate only.

CREATE TABLE Test
(
EDate Datetime,
Code varchar(255),
Cdate int,
Price int
);

[Code] ....

Expected output :
 
2015-06-23 00:00:00.000 CL 20150701 73
2011-04-08 00:00:00.000 XP 20110501 37
2015-06-23 00:00:00.000 HO 20150701 22

View 13 Replies View Related

Transact SQL :: How To Select Rows From Table Where DATE Column Is Today's Date

Aug 31, 2015

So my data column [EODPosting].[MatchDate] is defined as a DATE column. I am trying to SELECT from my table where [EODPosting].[MatchDate] is today's date.

SELECT*
FROM[dbo].[EODPosting]
WHERE[EODPosting].[MatchDate]=GETDATE()

Is this not working because GETDATE() is like a timestamp format? How can I get this to work to return those rows where [EODPosting].[MatchDate] is equal to today's date?

View 2 Replies View Related

Transact SQL :: Getting Closest Date From A Given Date Column

Sep 28, 2015

Basically, the sample raw data is a result from my last cte process which consolidate all the records. I want to find the closest date in PO ended using Receipt_date column but my problem, for this ESN R9000000000019761824, i wanted to get the latest or the last transaction date.

please see below DDL and sample data.

--this is the result from my last cte process
Create table #sample
(ESN nvarchar(35),ESN2 nvarchar(35), Receipt_Date datetime,PO_ENDED datetime)

insert into #sample(ESN,ESN2,Receipt_Date,PO_ENDED)values('990002036924452','990002036924452','2015-01-07 17:39:44.660','2014-01-09 04:13:29.000')

[Code] .....

Expected result:

ESN-------------------ESN2-------------Receipt_Date-------------POENDED---------------
--------------------------------------------------------------------------------------
990002036924452-------990002036924452--2015-01-07 17:39:44.660--2015-04-23 20:26:55.000
990002038378525-------990002038378525--2015-01-07 05:29:56.923--2015-05-29 14:50:52.000
R9000000000019761824--354403060637509--2015-01-03 19:23:54.980--2015-03-20 16:43:07.000

[Code] ....

View 8 Replies View Related

Transact SQL :: Selecting First Row After Date Matching Condition?

Sep 22, 2015

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.

View 9 Replies View Related

How To Get Latest Temperature Reading For Each Date In A Date Range

Sep 28, 2012

I have to display the last temperature reading from an activity table for all the dates in a selected date range.So if I select the date range from 09/01/2012 to 09/30/2012, the results should look like this:

Date Temperature
09/01/2012 73.5
09/02/2012 75.2
09/03/2012 76.3
09/04/2012 73.3
09/05/2012 77.0
09/06/2012 74.5
and so on.

I am using this to get the dates listed:
WITH CTE_DatesTable
AS
(
SELECT CAST('20120901' as date) AS [Date]
UNION ALL
SELECT DATEADD(dd, 1, [Date])
FROM CTE_DatesTable
WHERE DATEADD(dd, 1, [Date]) <= '20120930'
)
SELECT [Date]
FROM CTE_DatesTable

How could I get the temperature if I did a sub-query here?

View 5 Replies View Related

Problem Inserting Integers And Date In A Sql Server 2005 Datatable Row And Selecting It Afterwards Based On The Date

May 4, 2007

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

View 5 Replies View Related

Transact SQL :: Calculate DateTime Column By Merging Values From Date Column And Only Time Part Of DateTime Column?

Aug 3, 2015

How can I calculate a DateTime column by merging values from a Date column and only the time part of a DateTime column?

View 5 Replies View Related

Transact SQL :: Convert Server Date MM/DD/CCYY To Oracle Date Formatted As NUMBER (8,0)

Apr 30, 2015

So I have to build dynamic T-SQL because of a date parameter that will be provided. The Date Parameter will be provided in SSRS in normal MM/DD/CCYY format. So how do I then convert that date to my Oracle format

NUMERIC(8,0) CCYYMMDD?

I tried this...

SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF<='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;
SET@SQLQuery=@SQLQuery+'ANDMEMBER_SPAN.YMDEFF>='''''+CAST(@AsOfDateASVARCHAR)+''''''+@NewLineChar;

but that put it in the format of...

AND
MEMBER_SPAN.YMDEFF<=''2015-04-01''
AND
MEMBER_SPAN.YMDEFF>=''2015-04-01''

Which is close...I think I just need to lose the "-"

View 5 Replies View Related

Transact SQL :: Due Date - Conversion Failed When Converting Date And / Or Time From Character String

Nov 16, 2015

SELECT * ,[Due]
  FROM [Events]
 Where Due >= getdate() +90

This returns the error: Conversion failed when converting date and/or time from character string

Why would this be? How to cast or convert this so that it will work? 

View 24 Replies View Related

Transact SQL :: Return Date Which Is 15 Working Days Prior To Given Future Date

Oct 28, 2015

i have written a sql function which returns only number of working days (excludes holidays and Weekends)  between given StartDate  and EndDate.
                 
USE [XXX]
GO
/****** Object:  UserDefinedFunction [dbo].[CalculateNumberOFWorkDays]    Script Date: 10/28/2015 10:20:25 AM ******/
SET ANSI_NULLS ON
GO

[code]...

I need  a function or stored procedure which will return the date which is 15 working days (should exclude holidays and Weekends) prior to the given future Date? the future date should be passed as a parameter to this function or stored procedure to return the date.  Example scenario:  If i give date as  12/01/2015, my function or stored procedure should return the date which is 15 working days (should exclude holidays and Weekends) prior to the given date i.e 12/01/2015...In my application i have a table  tblMasHolidayList where all the 2015 year holidays dates and info are stored.

View 18 Replies View Related

Transact SQL :: How To Write A Query To Get Current Date Or End Of Month Date

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

Transact SQL :: Updating Date Part In Datetime Column

Sep 18, 2015

I need to set only the date part of tblEventStaffRequired.StartTime to tblEvents.EventDate while maintaining the time part in tblEventStaffRequired.StartTime.

UPDATE tblEventStaffRequired
SET StartTime = <expression here>
FROM tblEvents INNER JOIN
tblEventStaffRequired ON tblEvents.ID = tblEventStaffRequired.EventID

View 4 Replies View Related

Date Range Only Including Work Days -HELP!!

May 23, 2001

I've been fighting with this for a few days now, hopefully it's a simple answer.

I need to take a daterange between two dates and output the number of work days in that range.

I wrote this but it doesn't work. I know how to write a query that tells me if a date is a word day or not but not to actually determine a date range.

SELECT in_date,out_date,DATEDIFF((weekday, in_date, out_date) in (1,2,3,4,5)) AS no_of_days
from <table>
where ikey = 'whatever'

I am in desperate need of help as my deadline is coming up soon.

View 7 Replies View Related

Transact SQL :: How To Get Date Where Planned Delivery Date Key Is Less Than Getdate

Aug 13, 2015

How to get the date where planned delivery date key is less than getdate()

I want to make a view where I want put the condition :

select CONVERT(datetime, CAST(planned_delivery_date_key AS CHAR(8)), 101) < CONVERT(datetime,getdate(),101)
from qlk_fact_sales_back_order

View 2 Replies View Related

Transact SQL :: Retrieve Another Column (exchange Rate) From Particular Date For Subquery?

May 25, 2015

Below is the query in which i want to retrieve another column (exchange rate) from a particular date for the sub query. 

Actually PurchaseOrderDet table get records related to purchaseorder but for each row in purchaseorderdet need info from the same table for all rows on a particular date.

how i can achieve this query without using RANK() and other functions.

"SELECT  Supplier.Uniid AS SupplierID, Supplier.Name, PurchaseOrder.Uniid AS PurchaseID, PurchaseOrder.OrderNo, PurchaseOrder.FormDate, StockItem.Uniid AS StockID, StockItem.StockCode + N' - ' + StockItem.Description1 AS StockItem,
PurchaseOrderDet.ExchangeRate, PurchaseOrderDet.UnitPrice, PurchaseOrderDet.Discount, SUM(PurchaseOrderDet.OrderQty) AS SumOfOrderQty,

[Code] ....

View 7 Replies View Related

Transact SQL :: How To Remove Duplicate Records Based On Date Column

Jul 28, 2015

I have a requirement where i want to delete the records based on the Date column. I have table which contain the columns like machinename ,lasthardwarescandate

I want to delete the records based on the max(Lasthardwarescandate) i.e. latest one, column where the machine name is duplicate menace it repeats. So how would i remove the duplicate machine names based on the Lasthardwarescandate column(There are multiple entries for the Lasthardwarescandate so i want to fetch the latest date column).

Note: Duplication should be removed based on “Last Hardware Scan” date.

Only latest date should be considered from multiple records for the same system. "

View 4 Replies View Related

Transact SQL :: Use Date / Hour And Minute In Datetime Column Type?

Nov 9, 2015

I just need the date, hour, and minute...not the micro seconds. Do I have to DATEPART and concatenate each or is there any way to simply truncate the milliseconds from it? Or is there a date format to put extract and report on it as...

MM/DD/CCYY HH:MM:SS AM

I see there is a format 131 that puts it in..

DD/MM/YYYY HH:MM:SS:MMMAM

View 7 Replies View Related

Transact SQL :: How To Update New Table By Separating Single Date Column

Oct 11, 2013

I am trying to build a DIM table using a source table that has the following setup...

CREATE TABLE [dbo].[APPL_STATUSES](
[APPLICATIONS_ID] [varchar](10) NOT NULL,
[POS] [decimal](10, 0) NOT NULL,
[APPL_STATUS] [varchar](5) NULL,
[APPL_STATUS_DATE] [datetime] NULL,
[APPL_APPLICANT] [varchar](10) NULL)
GO

[code]....

What I am trying to do is to break out the APPL_STATUS_DATE into a STATUS_START_DATE and an STATUS_END_DATE in a new table.  I also need to be able to update the STATUS_END_DATE based on the previous day's date. Like so...

CREATE TABLE [dbo].[APPL_APPLICANT_STATUSES](
[APPLICATIONS_ID] [varchar](10) NOT NULL,
[POS] [decimal](10, 0) NOT NULL,
[APPL_STATUS] [varchar](5) NULL,
[STATUS_START_DATE] [datetime] NULL,
[STATUS_END_DATE] [datetime] NULL,
[APPL_APPLICANT] [varchar](10) NULL)
GO

[code]...

View 10 Replies View Related

Help With Getting Row With Latest Date

Sep 3, 2007

guys help please...I have a table with 3 columns (TransactionID, CustomerID, TrasanctionDate) and there could be posibility that one or more rows could have the same value for CustomerID. Now my question is it posible to retrieve the row of latest TrasanctionDate made by a particular customer?

Ex:

TransactionID | Customer ID | TrasanctionDate

1 | 2 | 9/3/2007 12:00:00 AM
2 | 3 | 9/4/2007 12:00:00 AM
3 | 2 | 9/5/2007 12:00:00 AM
4 | 2 | 9/6/2007 12:00:00 AM

Say, i want to retive the latest trasanction of customer with CustomerID equals to 2. The output shoudl be is row 4 since it has the lates date with CustomerID equals to 2

4 | 2 | 9/6/2007 12:00:00 AM

Any help will be greatly appreciated. Thanks in advance!

View 3 Replies View Related

Getting The Most Latest Date

Jun 14, 2006

I have a column which stores dates (datetime data type). I would liketo fetch one data which is the most latest date among them. So if thereare 04/01/06, 05/08/06, 05/12/06, 06/15/06, then 06/15/06 is the one Ineed to output.how can I write this in sql stmt?select datebeginfrom testtablewhere datebegin = ?????I don't think it is simple as I thought. I have no idea what I need towrite in where clause to make this work.thanks.

View 1 Replies View Related

Transact SQL :: Case Statement For A New Column Based On Date And Username Columns

Sep 2, 2015

Below is the SQL Query i am currently having

SELECT IG_FinancialTransactionSummary.ClaimNum,IG_FinancialTransactionSummary.TransactionCode,
IG_FinancialTransactionSummary.TransactionDate,IG_FinancialTransactionSummary.Username,
FinancialTransactionSummaryTest.ClaimNum,FinancialTransactionSummaryTest.TransactionAmount,
FinancialTransactionSummaryTest.UserName--,FinancialTransactionSummaryTest.TransactionDate

[Code] ....

And here is the result dataset

ClaimNumTransactionDateUsername ClaimNum TransactionAmountUserName
2000074 20150209jerry.witt 2000074 -10000DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL
2000074 20150626DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL 2000074 -10000DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL

[Code] .....

So,if we look at the result set, we notice 2 conditions where the IG_FinancialTransactionSummary.Username is like 'Data' and if we see the transaction date then sometimes that is the max transaction date or sometimes there are transactions that happened after but that doesn't have like '%data%' in username . So, i need to add a new column to my sql query which should basically verify if the username is like '%data%' and if that is the max(transaction date) or even if there are any transactions after that doesn't have like '%data%' then YES else No.

View 13 Replies View Related

Transact SQL :: Date Variables That's being Set Around Current Date

Nov 23, 2015

I have a the following date variables that's being set around the current date.  how to adjust it to work around a date variable @Date instead of the current date?   Lets the @Date = 2015-06-30 then the it would adjust the variable below accordingly.  I'm assuming the getdate()) needs to be replaced with @Date but I can't seem to get it to work.

Declare @EndOfLMPriorYear  Date = DateAdd(yyyy,-1,EOMONTH(Dateadd(Month,Datediff(Month,0,getdate())-1,0)))
Declare @EndOfPriorMonth Date =  EOMONTH(Dateadd(Month,Datediff(Month,0,getdate())-2,0))
DECLARE @EndOfLastMonth Date = EOMONTH(Dateadd(Month,Datediff(Month,0,getdate())-1,0))

View 3 Replies View Related

Transact SQL :: Outputting Date Difference Between Two Date

Sep 11, 2015

I require outputting the date difference between two date's if it is greater than 7(DateDiff(day, DateAdd(day, t.[Started], Nxt.started), (t.[started])) > 7).I get incorrect syntax on my operator.What is the correct code?

View 6 Replies View Related

Transact SQL :: Date Arithmetic With Date And Datetime

Sep 21, 2015

if I do this:

print @@version
print 'arithmetic with datetime'
go
begin try
declare @datetime datetime = getdate()

[Code] ....

... I get this:

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64) 
Mar 26 2015 21:18:04 
Copyright (c) Microsoft Corporation
Express Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )

arithmetic with datetime
@datetime = Sep 22 2015  1:39PM
arithmetic with date
Msg 206, Level 16, State 2, Line 3
Operand type clash: date is incompatible with int

Why the inconsistency? Datetime is lenient in this regard - I can even do set @datetime += 0.5 (although the meaning is less intuitive).

View 6 Replies View Related

Transact SQL :: Expand Date Within Date Parameter

Jul 23, 2015

I am working into a t-sql where i need to expand the date within a date parameter. For example:

Start Date = '1/1/2014'
End Date = '12/31/2014'

I want to expand this as:

01/2014
02/2014
03/2014
04/2014
05/2014
06/2014
up to 
12/2014

View 4 Replies View Related

Getting The LATEST DATE In The Table

Mar 5, 2007

How to modify the below procedure in order to get the latest date ( the biggest value ) in the column “Deadline?, type smalldatetime, ( example: 24/02/2007 00:00:00 ) for a given customer “UserName? ?
 
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CREATE PROCEDURE GetLatestDate
(@UserName VARCHAR(50)
AS
SELECT Deadline
FROM CustomerItems
WHERE UserName = @UserName

View 2 Replies View Related

Get Record With Latest Date

Jun 4, 2007

 if there are 2 records with different date  how to write query for ----> get record with latest date 

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved