July 1, 2009 at 9:34 pm
Is this what you are trying to get?
select
aht.Asset_ID,
aht.To_User,
aht.To_Status,
convert(varchar(10),aht.Change_Date, 101) as 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 9:51 pm
That looks like it may work. I'll have to wait until I can try it on the Prod DB in the morning. Thanks again.
July 2, 2009 at 6:39 am
Lynn,
The CONVERT function just wasn't doing anything, so I went back to what I usually use, which is DATEADD. I had forgotten that it was failing only in my test data, and was not causing an error with my production data. When I put that in place of the CONVERT, my dates displayed correctly.
Can you tell me how you get your formatted code into these posts? I format mine, but when I paste them in here the formatting disappears. I'd like to post the code that worked, which is all yours with DATEADD substituted for CONVERT.
Something that surprised me was that you could assign an alias to that derived table when it didn't have a name. For clarity, I tried to add a name to the code, but kept getting syntax errors.
July 2, 2009 at 7:21 am
Jay Tucker (7/2/2009)
Lynn,The CONVERT function just wasn't doing anything, so I went back to what I usually use, which is DATEADD. I had forgotten that it was failing only in my test data, and was not causing an error with my production data. When I put that in place of the CONVERT, my dates displayed correctly.
Can you tell me how you get your formatted code into these posts? I format mine, but when I paste them in here the formatting disappears. I'd like to post the code that worked, which is all yours with DATEADD substituted for CONVERT.
Something that surprised me was that you could assign an alias to that derived table when it didn't have a name. For clarity, I tried to add a name to the code, but kept getting syntax errors.
Posting the code, I post it using the IFCode tags, which are [ code ][ /code ] with out the spaces inside the brackets.
As for CONVERT not working and having to use DATEADD, that just doesn't make any sense to me.
July 2, 2009 at 9:57 am
OK, thanks for the tip about IFCodes. I checked out some of the posts on that subject, and will use in future. That CONVERT works with my test data, but the dates in Production must be formatted differently. I should have sent you an example of how they display if I don't modify them. Here's an example:
Unmodified Change_date = 1182342892 (Someone told me it's a UNIX date??)
With DATEADD function = 2007-06-20 12:34:52.000
Anyway, here's the script that works in Production and many thanks for sticking with me on this:
SELECT aah.Asset_ID ,
aah.To_User ,
aah.To_Status,
DATEADD(SS,aah.Change_Date,'19700101') Change_Date
FROM AST_ASSET_History aah
INNER JOIN
(SELECT Asset_ID,
MAX(Change_Date)Change_Date
FROM AST_ASSET_History
WHERE To_Status = 'Deployed'
AND To_User 'Company'
AND Asset_ID IN ( 'M 79674',
'M 81956',
'M 82602',
'M 87719',
'M 87861' )
GROUP BY Asset_ID
) ld
ON (
aah.Asset_ID = ld.Asset_ID
AND aah.Change_Date = ld.Change_Date
)
ORDER BY aah.Asset_ID,
aah.Change_Date;
July 2, 2009 at 10:04 am
Jay Tucker (7/2/2009)
OK, thanks for the tip about IFCodes. I checked out some of the posts on that subject, and will use in future. That CONVERT works with my test data, but the dates in Production must be formatted differently. I should have sent you an example of how they display if I don't modify them. Here's an example:Unmodified Change_date = 1182342892 (Someone told me it's a UNIX date??)
With DATEADD function = 2007-06-20 12:34:52.000
Anyway, here's the script that works in Production and many thanks for sticking with me on this:
SELECT aah.Asset_ID ,aah.To_User ,
aah.To_Status,
DATEADD(SS,aah.Change_Date,'19700101') Change_Date
FROM AST_ASSET_History aah
INNER JOIN
(SELECT Asset_ID,
MAX(Change_Date)Change_Date
FROM AST_ASSET_History
WHERE To_Status = 'Deployed'
AND To_User 'Company'
AND Asset_ID IN ( 'M 79674',
'M 81956',
'M 82602',
'M 87719',
'M 87861' )
GROUP BY Asset_ID
) ld
ON (
aah.Asset_ID = ld.Asset_ID
AND aah.Change_Date = ld.Change_Date
)
ORDER BY aah.Asset_ID,
aah.Change_Date;
Okay. Your dates aren't stored in a datetime column. That explains it.
Two questions about your code. Why the not equals 'Company', can assets be 'Deployed' to the 'Company' and not an individual? Also, why the restriction to the Assest_ID? That didn't seem to be part of problem, or was the test data based on the actual requirements?
July 2, 2009 at 10:52 am
Lynn,
Yeah, they can be deployed to the Company - if someone screws up. They should theoretically always be deployed to an enduser, so we audit now & then to find the exceptions. The test was as close to the actual requirements as I could make it, so Asset_ID was required.
Have a great Holiday weekend.
Thanks,
Jay Tucker
July 2, 2009 at 10:56 am
Okay. Thank you for the feedback.
Happy Holidays!!
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply