Getting the last date time from a column

Question

Getting the last date time from a column

kdnicholsster

Ten Centuries

Points: 1152
More actions
December 16, 2011 at 9:12 am

#261920

Hello all,
I am trying the following code:
SELECT Distinct CONVERT(varchar,20111216,111) AS thedate FROM DimWorkItem DWI
INNER JOIN DimIteration DI ON DI.IterationSK = DWI.IterationSK
WHERE
--DWI.LastUpdatedDateTime BETWEEN '20111215' AND '20111215' DWI.LastUpdatedDateTime = GETDATE('2011-12-16')
DI.IterationPath LIKE '\xxxx%'
AND DI.IterationPath NOT LIKE '\xxxx\(_Bug Migration)%'
AND DI.IterationPath NOT LIKE '\xxxx\(Bug Inbox)%'
AND DWI.System_WorkItemType = 'Bug'
AND DWI.System_State <> 'Done'
AND DWI.System_State <> 'Removed'
AND DWI.Microsoft_VSTS_Common_Severity = '3 - Medium'
AND DWI.System_Title NOT LIKE '%Pilot:%'
AND DWI.System_Title NOT LIKE '%TechnicalDemonstrator%'
AND DWI.System_Title NOT LIKE '%NonProductionCode%'
AND DWI.LastUpdatedDateTime = DI.LastUpdatedDateTime
AND DWI.System_Rev = (SELECT MAX(System_Rev) FROM DimWorkItem DWI_1
WHERE DWI_1.System_Id = DWI.System_Id AND DWI_1.System_WorkItemType = 'Bug' AND DWI_1.TeamProjectSK = 159)
GROUP BY DWI.LastUpdatedDateTime --CONVERT(varchar,20111216,111)
It just displays the column heading thedate and no results.
Can someone point out to me what I am doing wrong?
Thanks,
Kurt

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Your sub-query that gets the max date doesn't have the same criteria as the outer query. Is it possible there's a date in there that's higher than the max date that does match those other criteria?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

kdnicholsster Ten Centuries Points: 1152 More actions · Answer 2

We could do away with the Max if needed.

I know that GetDate() returns todays date.

What do you recommend?

GSquared SSC Guru Points: 260824 More actions · Answer 3

Have you looked into using the T-SQL ranking functions, Row_Number, Rank, Dense_Rank?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

kdnicholsster Ten Centuries Points: 1152 More actions · Answer 4

Hello GSquared,

No, not yet.

How can I use a Date Format something similar to what I am working on now?

SELECT {[Measures].[Work Item Count]} on columns,

NON EMPTY LASTPERIODS(30, [Date].[Date].[Date].[Date].[Month].[Date].[Year].[Year].&[20111201]) on rows

FROM [Team System]

WHERE [Microsoft_VSTS_Common_Priority_String].&[1 - High]

I am thinking I need the date in a AND clause at the bottom. What is the best way to go? These syntaxes are directly from my cube.

Thanks,

Kurt

GSquared SSC Guru Points: 260824 More actions · Answer 5

Is this an Analysis Services cube, or a pair of relational tables? The first query you posted looks like it's from a pair of relational tables, but the last post doesn't look like T-SQL (I'm not particularly familiar with MDX; is it that?), and you mention a cube. So now I'm a little confused here.

If you're trying to query relational tables, I can help with that. I think I just need the create scripts for the tables and I'll be able to help you clean up your first query. Not so much with SSAS, since I haven't touched that in years. Maybe someone who knows more about that will happen by, but you're more likely to find such a person in the Analysis Services forums, rather than the SQL 2008 General forums.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

kdnicholsster Ten Centuries Points: 1152 More actions · Answer 6

Hello G Squared,

I would still love to see some TSQL examples and I will post in the Analysis Services forum.

Thanks for your time.

I still hope to see your TSQL examples.

Best,

Kurt

GSquared SSC Guru Points: 260824 More actions · Answer 7

I need the table definitions so I can write a query that will work for you.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon