Transact SQL :: Get Max Date For Data Available For All IDs
Jul 19, 2015
I have a temp table
drop table #temp
create table #temp (id int, dat datetime)
insert into #temp values(1,'2015-03-01')
insert into #temp values (2,'2015-02-01')
insert into #temp values (2,'2015-01-01')
insert into #temp values (3,'2015-01-01')
insert into #temp values (3,'2015-03-01')
insert into #temp values (3,'2014-12-01')
insert into #temp values (2,'2014-12-01')
insert into #temp values (1,'2014-12-01')
select * from #temp
The result i am expecting is 2014-12-01. I need to get the max(dat) for data available for all ids.In the sample set, only for 2014-12-01, there is a row for id 1,2,3. I need to get that date as result.
View 11 Replies
ADVERTISEMENT
Apr 23, 2015
I have a table that capture the size of databases every week.
CREATE TABLE [dbo].[DBGrowthRate](
[DBGrowthID] [int] IDENTITY(1,1) NOT NULL,
[DBName] [varchar](100) NULL,
[DBID] [int] NULL,
[CurSize] [decimal](10, 2) NULL,
[Gatherdate] [datetime] NULL,
[Serversname] [varchar](50) NULL
) ON [PRIMARY]
What I want is to make a query that will return
DBName, Cursize (oldest Gatherdate, CurSize (most recent GatherDate, ServersName
How to make this happen, I have tried working with Max and Min Gatherdate but that doesn't work.
View 7 Replies
View Related
Oct 17, 2015
i have this table the TDate in string (not date)
MEN
===
id | Name | TDate
1 | aa | 01.05.2015
2 | bb | 05.05.2015
View 6 Replies
View Related
Jul 31, 2015
I want to view all data that have a date of today, but my query is not returning them? Is it due to the actual data being a datetime and I am not accounting for time? How should I set this query up so that the data returns?
Create Table DateTest(ID int,Date1 datetime)
Insert Into DateTest Values(1, GetDate()), (2, GetDate()), (3, GetDate()), (4, GetDate())
Select * from DateTest
ORDER BY Date1 DESC
Select * from DateTest
where Date1 = convert(varchar(10), GetDate(), 126)
View 9 Replies
View Related
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
Jun 29, 2015
I've data like below:
Now, I've to get active data for a particular date range. Let me explain the active data definition as below:
StartDate : 01-Jul-2015
EndDate : 31-Dec-2015
It should return all the data which was active for that date range even if it was only for one day.If no data found for that date range, check the last record before start date and and if its active then it should be returned else not.
I though of creating a function and pass primary key with date range and return the final status but that doesn't seems like an optimized query.
View 4 Replies
View Related
Aug 9, 2015
I’m retrieving Yahoo quotes into my database and have run into an issue when the dates sometimes change format in the csv file retrieved.
I am retrieving yahoo quotes via powershell, then running a package to import data to my table. This generally works expect when the yahoo date format changes.
In the yahoo csv file, the dates normally come through in dd/mm/yyyy format. I find when a quote is old the format changes to mm/dd/yyyy, just for that particular quote.
When this happens, the package fails because the quote date format does not match my destination table format. i.e. mm/dd/yyyy vs dd/mm/yyyy
When this occurs, I would like to skip the records in mm/dd/yyyy format altogether and have the rest of the quotes imported.
One approach I can think of is to import the dates as a text type and do some data validation / conversion once imported but it feels like adding unnecessary steps.
Is there some other way I can achieve this within the process I already have?
View 4 Replies
View Related
Sep 21, 2015
I have a dynamic sql query where in I am comparing two tables and loading data for last 15 days. e.g today 2050921 then I am going to load till 20150906.
I pass on 2 variables @currentdate and @currentdate-1 to the query which are in date format 'yyyymmdd'
I need to do this for last 15 days how do I do this using while loop.
Note my date format is YYYYMMDD.
DECLARE @SQL VARCHAR(MAX)
@sql = ' insert into target
select from table_1_currentdate a
LEFT JOIN Table_2_currentdate-1 b
on a.col1=b.col1 where b.col1 is null '
exec(@sql)
I have to use while loop and decrement it every time and load data for last 15 days comparing two tables. I tried so many times I am not getting it right .
View 3 Replies
View Related
Sep 2, 2015
I am using T-SQL I have a column (ColA)that has datetime format and I simply want to pull the next day but date only into ColB
ColA
3/12/2014 12:00AM
3/19/2014 12:00AM
ColB
3/13/2014
3/20/2014
I have been trying the command below but keep getting the error "Conversion failed when convertint the varchar value '03-03-2014' to data type int."
Convert (varchar(10), "StartDate", 110)+1 as Next Day
View 8 Replies
View Related
Oct 30, 2015
Lets say I have a table, tblPersons
FirstName | LastName | DateofBirth
----------------------------------------
Thomas | Alva Edison | 10-10-2015
Benjamin | Franklin | 10-10-2015
Thomas | More | 11-10-2015
Thomas | Jefferson | 12-10-2015
Suppose today's date is 09-10-2015 in (dd-MM-yyyy format), I want to perform a query in such a way that I should get the data from the table above WHERE DateofBirth is tomorrow, so I could get the following result.
FirstName | LastName | DateofBirth
----------------------------------------
Thomas | Alva Edison | 10-10-2015
Benjamin | Franklin | 10-10-2015
View 15 Replies
View Related
Apr 21, 2015
In the below scenario we are inserting some time related fields in Temp table.But its data type is varchar. Once data loading is finished in the temp table (Data is loading by source team SQOOP and they are unable to load if the source datatype is having Date or datetime) we have to alter the column datatypes. somehow, some character data in inserted in date columns (look at into 3rd insert statement). while altering the table it is failing. Can we do any alternative for this (Means if any varchar data that is non convertible to date can we make as null)
INSERT INTO ##TEMP_TEST
SELECT '2014-09-30','2017-10-06','Nov 6 2014 6:11AM','Nov 6 2014 6:11AM'
UNION SELECT '2014-09-29','2017-10-06','Nov 6 2014 6:11AM','Nov 6 2014 6:11AM'
UNION SELECT '2014-09-28','2017-10-06','Nov 6 2014 6:11AM','Nov 6 2014 6:11AM'
GO
INSERT INTO ##TEMP_TEST SELECT NULL,NULL,NULL,NULL
[Code] ....
View 6 Replies
View Related
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
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
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
View Related
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
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
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
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
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
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
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
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
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
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
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
Jun 15, 2015
How can I get a date that would be last/previous Monday from given date?
For example for given 22/01/2015 it would give 19/01/2015.
View 16 Replies
View Related
Jun 19, 2015
I have date in my table like this
id from to
1 01/01/2013 02/01/2014
1 02/02/2014 12/31/9999
so for same id i want min (from ) and max(to) date in my select statement.
Example , for id 1
id from to
1 01/01/2013 12/31/9999
View 3 Replies
View Related
Nov 16, 2015
I have different codes and I want to specify different date of birth range based on selected code in the parameters.
DECLARE @CODE VARCHAR(5) = 'FXE'
SELECT * FROM TABLE
WHERE CODE = 'FXE' and
AND DATE_OF_BIRTH
BETWEENDATEADD(month,-15,GETDATE())
AND DATEADD(year,-1,dateadd(YEAR,-0,cast(GETDATE()asdate))))
How I can do for one code but I cant get my way around bringing another code 'WHY' which has a different date of birth range. Basically I want a user to be able to change a code in the parameter and display results based on the parameter selected.
View 9 Replies
View Related
Jun 23, 2015
I need query to get the Max date of CODE.
OID CODE Current_date
3710 250 01/01/1997
3910 250 03/03/1998
4100 250 01/01/2014
1200 251 01/01/2013
1301 252 01/01/2001
1450 252 01/01/2014
1451 252 01/01/2015
Expected result after taking max(effdate) of CODE
OID CODE Current_date
4100 250 01/01/2014
1200 251 01/01/2013
1451 252 01/01/2015
View 7 Replies
View Related
Jul 7, 2015
How do i get the mentioned format. if today is 7th July then,2015-07-07 15:30 i.e. date with HH and MM appended in today's date?
want just one simple statement. don't want to write big query as i want to use this condition in one of the joining criteria.
View 6 Replies
View Related
May 4, 2015
I have records like this
id address1 date
1 xyz 01/01/2013
1 abd 01/01/2014
2 dfg 01/03/2015
Now what I want is just 1 record by id which has max date,if there is 2 records for same id, if there is 1 record then it should come.
So here result should be
1 abd 01/01/2014
2 dfg 01/03/2015
View 12 Replies
View Related
Jul 24, 2015
Hoe to convert date format
My table contains date as " 13FEB2015:08:54:45 " need to be change in datetime or date.
View 10 Replies
View Related
Nov 27, 2015
Having an issue getting this done. The datatype in the table is varchar(50). The values in the table are
11122015
11122015
11122015
Nothing has worked so far.
View 3 Replies
View Related
