Using The Max(date) For A Nonselected Column

May 5, 2008

Hi i have a long query which goes something like this:

SELECT e.LoginID, d.Name, e.Title As 'Worktitle', e.MaritalStatus,
e.Gender, c.FirstName + ' ' + c.LastName AS 'FullName', c.EmailAddress,
c.Phone, a.AddressLine1, a.AddressLine2, s.Name As 'ShiftName', p.Rate,
p.PayFrequency, t.Name
FROM EmployeePayHistory p INNER JOIN
Employee e ON p.EmployeeID = e.EmployeeID
INNER JOIN Contact c ON e.ContactID = c.ContactID
INNER JOIN EmployeeDepartmentHistory ed ON ed.EmployeeID = e.EmployeeID
INNER JOIN Shift s ON s.ShiftID = ed.ShiftID
INNER JOIN Department d ON d.DepartmentID = ed.DepartmentID
INNER JOIN EmployeeAddress ea ON ea.EmployeeID = e.EmployeeID
INNER JOIN Address a ON a.AddressID = ea.AddressID
INNER JOIN StateProvince sp ON sp.StateProvinceID = a.StateProvinceID
INNER JOIN SalesTerritory t ON sp.TerritoryID = t.TerritoryID
WHERE ed.EndDate IS NULL
ORDER BY e.EmployeeID


My problem is that in EmployeePayHistory there are rows repeated for the same employees, so for example, employee with ID of 2 has 3 rows and each row has different information and there's a RateChangeDate column so I want to select only the most recent RateChangeDate. But I don't want this column to appear, how do i do this?

View 2 Replies


ADVERTISEMENT

Splitting SQL Server Date/Time Column Into Access Date Column And Access Time Column

Jan 24, 2008

I have an SSIS package that moves data from SQL Server to an legacy Access database. In SQL Server, there is a date/time column that I need to split into a separate date column and time column in the access database. Initially I just created a derrived column for the time and set the expression equal to the source date/time column from SQL Server. Unfortunately, that just makes the date column and time column the same having the full date/time.

What expression can I use during a derrived column transformation to assign just the date to a derrived column and just the time to another derrived column?

Thanks,

Steve

View 3 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

SQL Server 2012 :: How To Match Two Different Date Columns In Same Table And Update Third Date Column

May 30, 2015

I want to compare two columns in the same table called start date and end date for one clientId.if clientId is having continuous refenceid and sartdate and enddate of reference that I don't need any caseopendate but if clientID has new reference id and it's start date is not continuous to its previous reference id then I need to set that start date as caseopendate.

I have table containing 5 columns.

caseid
referenceid
startdate
enddate
caseopendate

[code]...

View 4 Replies View Related

Unable To Insert Converted Date Into Date Column (Data Type)

Aug 24, 2015

PHP Code:

INSERT INTO [GPO].dbo.tblMetric  (KPI_ID, METRIC_ID, GOAL, REPORTING_MONTH, ACTUALS) 
SELECT 
    
      1 AS KPI_OWNER_ID
    , 23 AS METRIC_ID 
    , .75 AS GOAL 
    , CAST(Z.REPORTING_MONTH as DATE) AS REPORTING_MONTH
    , SUM(CAST(FTP_COUNT AS DECIMAL))/SUM(CAST(FULL_COUNT AS DECIMAL)) AS ACTUALS

[Code] ....

The insert column I am trying to get into is a date type. The original state of the field is YYYYMM varchar. How to get this into the table.

View 3 Replies View Related

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

Integration Services :: How To Load Multiple Date Format Column Date Into Table Using SSIS

Jun 15, 2015

i have a excel file in which i have a date column it having the below date formats below 

Install Date

20140721

31.07.2014

07.04.2015

20150108

20140811

20150216

7/21/2014

11.08.2014

07.08.2014

So using SSIS how we would load this date column into the table into one format like dd/mm/yyyy or any single date format

View 6 Replies View Related

Comparing Date Records In One Column To Know Missing Date Interval?

Jun 6, 2012

I need to compare the contents of a column.

I have a table with 1 column. Below are the sample contents..

DateInterval
2012-06-01 00:30:00.000
2012-06-01 01:00:00.000
2012-06-01 01:30:00.000
2012-06-01 02:00:00.000
2012-06-01 02:30:00.000

[code]....

as you can see, the records have a 30minutes time interval. i need to create a query to know if there are missing records in the table. so basically the result should be this:

DateInterval
2012-06-01 18:00:00.000
2012-06-01 20:30:00.000

if it is not doable, we can get the nearest record of all missing records like this:

DateInterval
2012-06-01 17:30:00.000
2012-06-01 20:00:00.000

or this:

DateInterval
2012-06-01 18:30:00.000
2012-06-01 21:00:00.000

or this:

DateInterval
2012-06-01 17:30:00.000
2012-06-01 18:30:00.000
2012-06-01 20:00:00.000
2012-06-01 21:00:00.000

View 6 Replies View Related

Date Format - Column Which Select First Day Of Given Month Of Converted Date

Oct 21, 2013

Aim – Convert the following field ”[INSTALLATION_DATE]” date format from “20090709” Into this “2009-07-09” ,

Also create a new column called “BegMonth” which selects first day of the given month of the converted date column

The table is ;

SELECT
[FDMSAccountNo],
[INSTALLATION_DATE]
FROM [FDMS].[dbo].[stg_LMPAB501]

Results
FDMSAccountNoINSTALLATION_DATE
87800000088420030521

Required Results
FDMSAccountNoINSTALLATION_DATEBegMonth
8780000008842003-05-212003-05-01

View 3 Replies View Related

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

Insert Date Into Column Based On Date Field

Feb 26, 2008



Hi,

I need to insert into a column (lets say column x) a date based on the date on another column (lets say column y).

What I need is:



Take the day and month from column x (all records are formated yyyy-mm-dd)

Place it in column y

The yyyy in column y should be - currenct year +1 and no the year in column x.
All help welcome.

View 9 Replies View Related

Putting Date Or Date && Time In A Column Thats Going To Be Heavily Used?

Oct 4, 2007

Hello,

We have a bunch of Audit tables that contain almost exact copies of the operations tables. The audit tables also include:

AuditID - the audit action (insert, modify - old, modify - new, deleted)
AuditDate - date and time of action
AuditUser - User who did it...

At the end of the day I need to know for any given record what it looked like at the beginning of the day and what it looks like at the end of the day. There could have been numerous changes to the record throughout the day, those records I am not interested in. Only the first record and the last record of a give day.

I am going to be doing a lot of MIN(AuditDate) and MAX(AuditDATE) and .. WHERE AuditDate BETWEEN '10/1/2007 00:00:00' AND '10/1/2007 11:59:59' ...

Question: Whats better for performance:

1. Separating out the date and time and doing a clusterd index on the date.

2. Keeping date and time in the same column and just use a normal index.

3. Better ideas?

Thanks,
Bradley

View 1 Replies View Related

Analysis :: Calculated Column That Makes Integer In YYYYMMDD Format Form Date Column

Oct 12, 2015

I am trying to create a whole number DAX calculated column that is derived from a date column. Basically it gets the date from the source data column and outputs it as an integer in the YYYYMMDD format.So 01/OCT/2015 would become --> 20151001...I've been fidgeting with DAX but my problem is that I keep missing the leading zeroes for months and days. So 01/March/2015 becomes 201531 which is not what I want (I need 20150301 in this case).

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

Adding A Time Column To A Date Column

Jul 20, 2005

I have two columns in a table:StartDate DateTime and StartTime DateTime.The StartDate column holds a value such as 07/16/2004The StartTime column holds a value such as 3:00:00 PMI want to be able to add them in a stored procedure.When I use StartDate + StartTime I get a date two days earlier than expected.For example, instead of 7/16/2004 3:00:00 PM StartDate + StartTime returns7/14/2004 3:00:00 PM.Can anyone point out wht I'm doing wrong with this one?Thanks,lq

View 2 Replies View Related

Adding Date Column On First Column

May 2, 2014

I have a query like following

SET NOCOUNT OFF
SET ROWCOUNT 0
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = CAST (DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate()))as datetime)
SET @EndDate = GetDate()

[Code] ....

and when i execute it, it gave a return that i expected, but then i want to add a date column on the first column.

View 5 Replies View Related

Adding Column Where Adding Year To Date In Date Format

Aug 6, 2013

What is the syntax for adding a column where you are adding a year to a date in a date format? For example adding a column displaying a year after the participation date in date format?

View 1 Replies View Related

Date Column

Mar 24, 2008

how do i get a column showing the next 30 days?

View 3 Replies View Related

Default Date Value For Column

Sep 16, 2003

I would like to have the date value 1/1/2499 entered as the default value in a date field if no value is specified. I have added 1/1/2499 as the default value for the column however when I insert a record into the table the date 1/1/1900 is entered even though I haven't specified a date. Any way to accomplish this? Thanks.

View 5 Replies View Related

Column Creation Date ?

Dec 20, 2004

Hi:

Is there a way to find a column creation date? (or the latest date of type being updated)

I have tried information_schema.columns, syscolumns, sysobjects with no result related to.

thanks
David

View 2 Replies View Related

Reformat Of A Date Column

Nov 14, 2011

I need to perform caculations on two date columns using datediff. The issue I have is that some of the date columns only have mm/yyyy. When this occurs I need to change the value to mm/dd/yyyy and insert 15 as the dd.My caculations are as follows without the required modification above.

DATEDIFF(YY, DOB, Start_DATE)
- CASE WHEN Start_DATE >=DateAdd(yy,DateDIFF(yy,DOB,Start_DATE), DOB) THEN 0 ELSE 1
END AS Age,

View 4 Replies View Related

Searching Date Column

May 11, 2004

i have a table date column with smalldatetime format. data looks like
2004-05-10 00:00:00

i can search specify date but i want to retrieve all records for a month for e.g
April
how can i do this?
i tried

select * from mytable
where datecolumn like '%05%'
doesn't seem to work

View 3 Replies View Related

Date Format In Column?

Apr 15, 2008

Hi
I have a Date column with default value set up as below

(getdate()) --- This giving the format - 15/04/2008 15:36:57

How do I store the date as 15042008 or 15/04/2008 or 15-04-2008

Adavance thanks

View 6 Replies View Related

Date Column From Oracle

May 6, 2008

Hi,

I have a column in my database which came from Oracle database. In this Column I have date but the format is strange for me (Example: 105046).How can I convert this date to MM/DD/YY format in sql?
Thanks

View 15 Replies View Related

A Date Column From Oracle

May 6, 2008

Hi,

I have a column in my database which came from Oracle database. In this Column I have date but the format is strange for me (Example: 105046).How can I convert this date to MM/DD/YY format in sql 2005?
Thanks

View 1 Replies View Related

Date And Time In Same Column

Oct 24, 2013

I have a column of data that is in this format (quotes to communicate it is in one cell if viewed in excel):

"8/18/2013 23:58"

Essentially, I have the date and time in one entry.... how do I go about querying, using SQL, the following?

- all data that is less than a specific date (I don't care about time)?
- differences in time (subtracting time, while not caring about date)?

I can do it if the date and time were in different columns, but they are together and I am stuck.

View 7 Replies View Related

Collapse The Date Column?

Nov 6, 2014

Table created

declare @t table
(
Qtr VARCHAR(1000),visittype VARCHAR(1000),
CMSContractID VARCHAR(1000,memid VARCHAR(1000),
CarrierMemID VARCHAR(1000),HeadOfHouse VARCHAR(1000),
dobdatetime ,FullName VARCHAR(1000),minstartdt datetime,maxenddt datetime

[Code] ....

Expected output:

QtrvisittypeCMSContractIDmemidCarrierMemIDHeadOfHousedobFullNameminstartdtmaxenddt
06/01/2014 - 08/31/2014EDH8677CA140812115 1000000039546468-Apr-54TOM5-Jul-146-Jul-14
06/01/2014 - 08/31/2014EDH8677CA140812115 1000000039546468-Apr-54TOM9-Jul-149-Jul-14

View 3 Replies View Related

Next Run Date Column Not Sorting

Jul 20, 2005

We've just installed SQL Server 2000 on one of our servers and havenoticed a strange behavior. When clicking the column headings in thejob display in Enterprise Manager, the list is sorted (first clickascending, then descending) on the column clicked except for "Next RunDate." When we click on "Next Run Date," the list is reordered, butrandomly. Each time we click it we get a different order but neverascending or descending. This is true whether we're at the server oron a remote machine. We never saw this behavior with SQL Server 7.0.Any ideas? Thanks!

View 4 Replies View Related

Add Leading Zero To Date Column??

Jul 20, 2005

Hello All,None of the solutions I have found in the archives seem to solve myproblem. I have a date column in my tables (stored as a char(10))which I would like to append a leading zero to for those dates thatstart with 9 or lower.Any ideas?Thanks,Mike

View 2 Replies View Related

Max Date In Datetime Column?

Feb 16, 2008

Hello.
In SQL server 2000 (or 2005) I have a need to query a table for rows with the max date value in a datetime column.
Currently I coded the following to give me a specific date in the column:
...where R.start_time >= '02/15/2008' and R.start_time < DateAdd(day, 1, '02/15/2008')

This gives me the rows with the exact date of 02/15/2008.
So, if I don't want to hard-code the date or pass it as a parameter how can I always get the maximum date back from the query?

Thanks.
Walter

View 6 Replies View Related

How To Us Format A Date Into A New Column

Mar 31, 2007

Suppose my field is returning a date 3/31/2005 12:00:00 am how can i show it as Mmm-yy that is Mar-05



View 1 Replies View Related

Sort Date Column

Feb 11, 2008



Select distinct (left(date,12)) as startdate from table1

order by startdate desc


Guys what am i doing wrong here??? why is it not sorting the year part??

Sep 30 2005
Sep 29 2006
Sep 29 2005
Sep 28 2007
Sep 28 2006
Sep 28 2005
Sep 27 2007
Sep 27 2006
Sep 27 2005
Sep 26 2007

Thanks

View 16 Replies View Related







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