July 1, 2009 at 8:17 am
I have a database in which assets have been deployed to multiple users on various dates. I need to select the latest date that an asset was deployed and include the user it was deployed to. My query will work if I don't include the column 'To_User', but of course I need that column. It indicates that it must either be in the GROUP BY, or an aggregate function. If I include it in the GROUP BY, then I no longer get only the latest date. Can anyone help me with this? Thanks in advance.
select MIN(Asset_ID),To_User,
MAX(DATEADD(SS,Change_Date,'19700101'))AS ChangeDate
from AST_ASSET_History
where Asset_ID IN ('M 79674','M 81956','M 82602','M 87719','M 87861')
and To_Status = 'Deployed'
GROUP BY Asset_ID
Server: Msg 8120, Level 16, State 1, Line 1
Column 'AST_ASSET_History.To_User' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Here's my result set if I leave out the 'To_User' field:
M 796742007-06-20 12:34:52.000
M 819562006-04-25 16:55:39.000
M 826022007-11-29 20:05:36.000
M 877192008-11-20 13:36:45.000
M 878612007-03-15 15:28:42.000
July 1, 2009 at 8:23 am
GROUP BY Asset_ID, To_User, MAX(Change_Date)
Comes to mind.. You might have to put the home MAX(DATEADD.... logic in but I think this will work..
CEWII
July 1, 2009 at 8:27 am
To really help you we need a few things from you. Please provide the DDL (CREATE TABLE) statements for the table(s) involved in your query. Sample data in a readily consummable format (can be cut/paste/run in Query Analyzer or Management Studio), expected results based on the sample data. The fourth thing we need you have already provided in your original post, the code you have currently written.
If you need assistance iwth this request, please read and the follow the guidelines provided in the first article you will find referenced below in my signature block regarding asking for assistance.
July 1, 2009 at 12:32 pm
Lynn, thank you for pointing out the post that describes the correct format for posting to the forum. I've attempted to get it right this time, but now have run into a different problem, in that I'm now getting an error on the DATEADD function, which I do not get on my production data. Being a relative newbie, I have a tough time with date issues, so I don't know what it is about my test data that is causing that error. If I can get that worked out, then the query I posted should demonstrate the real problem, which is that my query will only work properly if I do not include the 'To_User' column in my SELECT. If I do include it, I get the error message in my first post, but if I add it to GROUP BY, the query then returns all records, not just the last date for each asset. Here's the query and below it is my attempt to create a valid test table:
SELECT MIN(Asset_ID),
To_User,
MAX(DATEADD(SS,Change_Date,'19700101'))AS ChangeDate
FROM AssetHistoryTest
WHERE Asset_ID IN ('M 79674',
'M 81956',
'M 82602',
'M 87719',
'M 87861')
AND To_Status = 'Deployed'
GROUP BY Asset_ID
NOTE: I don't have an IDENTITY column, so commented that out.
--== If the test table already exists, drop it
IF OBJECT_id('TempDB..AssetHistoryTest','U') IS NOT NULL
DROP TABLE AssetHistoryTest
--== Create the test table
CREATE TABLE AssetHistoryTest
(
Asset_ID varchar(30) NOT NULL,
To_User varchar(30) NOT NULL,
To_Status varchar(30) NOT NULL,
Change_Date datetime NOT NULL
)
--== Set up special conditions
SET DATEFORMAT MDY
--== All Inserts into the IDENTITY column
--SET IDENTITY_INSERT AssetHistoryTest ON
--== Insert the test data into the test table
INSERT INTO AssetHistoryTest
(Asset_ID,To_User,To_Status,Change_Date)
SELECT 'M 79674','Joe Smith','Deployed','Mar 28 2006 12:00AM' UNION ALL
SELECT 'M 79674','Joe Smith','Deployed','Apr 13 2006 12:00AM' UNION ALL
SELECT 'M 79674','Jim Jones','Deployed','Jun 20 2007 12:00AM' UNION ALL
SELECT 'M 79674','Company','End of Life','Mar 3 2009 12:00AM' UNION ALL
SELECT 'M 81956','Bill Booth','Deployed','Apr 25 2006 12:00AM' UNION ALL
SELECT 'M 82602','Sharon Smith','Deployed','Jul 7 2006 12:00AM' UNION ALL
SELECT 'M 82602','Amy North','Deployed','Nov 13 2007 12:00AM' UNION ALL
SELECT 'M 82602','James Dorman','Deployed','Nov 29 2007 12:00AM' UNION ALL
SELECT 'M 82602','Company','End of Life','Mar 3 2009 12:00AM' UNION ALL
SELECT 'M 87719','Kelly Johnson','Deployed','Jul 24 2006 12:00AM' UNION ALL
SELECT 'M 87719','Katherine Burke','Deployed','May 14 2007 12:00AM' UNION ALL
SELECT 'M 87719','Patrice Nelson','Deployed','Jul 23 2008 12:00AM' UNION ALL
SELECT 'M 87719','Patrice Nelson','Deployed','Nov 20 2008 12:00AM' UNION ALL
SELECT 'M 87861','Jennifer Smith','Deployed','Jan 30 2007 12:00AM' UNION ALL
SELECT 'M 87861','Patricia Yard','Deployed','Mar 15 2007 12:00AM' UNION ALL
SELECT 'M 87861','Company','End of Life','Mar 3 2009 12:00AM'
--== Set the identity insert back to normal
--SET IDENTITY_INSERT AssetHistoryTest OFF
July 1, 2009 at 12:44 pm
As I look at what you have provided, there does seem to be one thing missing that would really help. Based on the sample data provided, what SHOULD be the expected results? This doesn't requore any coding from you, just provide what you would expect back from the query.
With this, we can test our code and provide you back something that hopefully meets your needs.
July 1, 2009 at 12:54 pm
Of course, while waiting, I came up this. Does it meet your requirements?
with LastDeployment (
Asset_ID,
Change_Date
) as (
select
Asset_ID,
max(Change_Date)
from
dbo.AssetHistoryTest
where
To_Status = 'Deployed'
group by
Asset_ID
)
select
aht.Asset_ID,
aht.To_User,
aht.To_Status,
aht.Change_Date
from
dbo.AssetHistoryTest aht
inner join LastDeployment ld
on (aht.Asset_ID = ld.Asset_ID
and aht.Change_Date = ld.Change_Date)
where
To_Status = 'Deployed'
order by
Asset_ID,
Change_Date;
July 1, 2009 at 12:56 pm
Opps! Forgot this was a SQL Server 7, 2000 forum. The code above is for SQL Server 2005. I have modified the code to work with SQL Server 2000.
select
aht.Asset_ID,
aht.To_User,
aht.To_Status,
aht.Change_Date
from
dbo.AssetHistoryTest aht
inner join (
select
Asset_ID,
max(Change_Date) Change_Date
from
dbo.AssetHistoryTest
where
To_Status = 'Deployed'
group by
Asset_ID
) ld
on (aht.Asset_ID = ld.Asset_ID
and aht.Change_Date = ld.Change_Date)
where
To_Status = 'Deployed'
order by
Asset_ID,
Change_Date;
July 1, 2009 at 1:41 pm
Sorry, I forgot to specify what I expected. I want to see only records in To_Status = Deployed, and just the latest instance.
In the code you sent me, where does ld come from?
July 1, 2009 at 2:12 pm
ld (eL De) is the alias for the derived table in the FROM clause. If you look at the SQL Server 2005 version, it is the CTE declared before the SELECT clause.
July 1, 2009 at 4:09 pm
Lynn,
That is terrific, it works. The only thing now is that it's displaying a UNIX date, and I need to convert that to a human-readable format. The usual DATEADD function that I use in other queries produces errors.
July 1, 2009 at 4:12 pm
Jay Tucker (7/1/2009)
Lynn,That is terrific, it works. The only thing now is that it's displaying a UNIX date, and I need to convert that to a human-readable format. The usual DATEADD function that I use in other queries produces errors.
Go to BOL (Books Online) and read about CONVERT.
July 1, 2009 at 4:33 pm
Will do. Many thanks for your help.
July 1, 2009 at 4:36 pm
Your Welcome, and thanks for the feedback.
July 1, 2009 at 5:28 pm
Lynn,
Did I mention that this Date stuff drives me crazy? I checked out BOL and looked at examples here & elsewhere and figured I had the right idea, but the datetime continues to display in UNIX format. Here's the modification I made to your script. Should I be doing the convert somewhere else?
SELECT aht.Asset_ID ,
aht.To_User ,
aht.To_Status,
aht.Change_Date
FROM dbo.AssetHistoryTest aht
INNER JOIN
( SELECT Asset_ID,
MAX(CONVERT(char(30),Change_Date,101)) Change_Date
FROM dbo.AssetHistoryTest
WHERE To_Status = 'Deployed'
AND To_User 'Company'
GROUP BY Asset_ID
) ld
ON (
aht.Asset_ID = ld.Asset_ID
AND aht.Change_Date = ld.Change_Date
)
WHERE To_Status = 'Deployed'
ORDER BY aht.Asset_ID,
aht.Change_Date;
July 1, 2009 at 9:30 pm
I'm sorry, I don't know what you mean by UNIX format. When you select a datetime column from a SQL database, it displays a datetime value. I am use to seeing dates in the form yyyy-mm-dd hh:mm:ss.hhh.
Actual formatting of datetime values should occur in the UI (User Interface), GUI or reports.
How do you want to see the values?
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply