March 6, 2012 at 7:19 am
I have below fields in sql table
segment Posted D/T
Test 1 06/06/2012 10:00 AM
Test 1 06/06/201210 11:00 AM
Test 2 06/06/201210 10:00 AM
Test 2 06/06/201210 11:00 AM
Need assistance in finding unique segments(marked with bold) from above table.
What sould be TSQL for this?
March 6, 2012 at 7:23 am
From what I see each record is unique, which brings up the question – how do you decide which records are unique and which records are not unique?
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
March 6, 2012 at 7:24 am
nilesh k (3/6/2012)
I have below fields in sql tablesegment Posted D/T
Test 1 06/06/2012 10:00 AM
Test 1 06/06/201210 11:00 AM
Test 2 06/06/201210 10:00 AM
Test 2 06/06/201210 11:00 AM
Need assistance in finding unique segments(marked with bold) from above table.
What sould be TSQL for this?
First, sample data: -
--Your sample data
CREATE TABLE yourTable (segment CHAR(6), [Posted D/T] DATETIME)
INSERT INTO yourTable
SELECT segment, [Posted D/T]
FROM (VALUES('Test 1', '06/06/2012 10:00 AM'),
('Test 1', '06/06/2012 11:00 AM'),
('Test 2', '06/06/2012 10:00 AM'),
('Test 2', '06/06/2012 11:00 AM')) a(segment, [Posted D/T])
When you lay out sample data in a readily consumable format like that shown above, people are much more likely to respond with tested, working solutions to your problem.
On to the solution: -
SELECT segment, MAX([Posted D/T])
FROM yourTable
GROUP BY segment
March 6, 2012 at 7:25 am
oops ...i framed it wrong.
i need record set based on lateset post D/T
March 6, 2012 at 7:28 am
nilesh k (3/6/2012)
oops ...i framed it wrong.i need record set based on lateset post D/T
Like this: -
Cadavre (3/6/2012)
SELECT segment, MAX([Posted D/T])FROM yourTable
GROUP BY segment
March 6, 2012 at 7:37 am
Thanks for the reply.
What if there are n number of columns n table with ID as primary key?
Select * , MAX[Posted D/T] from MyTable
It is giving me below error:
Column 'MyTable.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
March 6, 2012 at 7:41 am
nilesh k (3/6/2012)
Thanks for the reply.What if there are n number of columns n table with ID as unique key?
Select * , MAX[Posted D/T] from MyTable
It is giving me below error:
Column 'MyTable.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
That depends on what data you actually want. Do you just want to grab the data with the latest [Posted D/T] for each unique ID?
March 6, 2012 at 7:44 am
I want to grab many of the columns including 'posted D/T' column from this table and i do not want to write individual column name in select query.
It has to be
Select * from ....
March 6, 2012 at 7:47 am
nilesh k (3/6/2012)
I want to grab many of the columns including 'posted D/T' column from this table and i do not want to write individual column name in select query.It has to be
Select * from ....
That doesn't answer my question. What I wanted to know was if the "segment" is duplicated, but none of the other columns are duplicates for a single row - do you just want to return the row with the latest [Posted D/T] ?
If so, I think this is what you're after.
--OPTION 1
SELECT base.*
FROM yourTable base
INNER JOIN (SELECT segment, MAX([Posted D/T]) AS [Max Posted D/T]
FROM yourTable
GROUP BY segment) sub ON base.segment = sub.segment AND base.[Posted D/T] = sub.[Max Posted D/T]
--OPTION 2
SELECT *
FROM (SELECT *, MAX([Posted D/T]) OVER(PARTITION BY segment) AS [Max Posted D/T]
FROM yourTable) a
WHERE a.[Max Posted D/T] = a.[Posted D/T]
March 6, 2012 at 8:07 am
both the options are giving syntax error 🙁
March 6, 2012 at 8:43 am
nilesh k (3/6/2012)
both the options are giving syntax error 🙁
Paste exactly what you tried. There is no syntax error in either of the code snippets I posted.
March 9, 2012 at 7:47 am
This is what i tried:
Option 1:
SELECT base.*
FROM table base
INNER JOIN (SELECT segment, MAX([PostedDate]) AS Max (PostedDate)
FROM EBBReport
GROUP BY segment) sub ON base.segment = sub.segment AND base.[PostedDate]
Option 2:
SELECT *
FROM (SELECT *, MAX(PostedDate) OVER(PARTITION BY segment) AS Max(PostedDate) FROM table) a
WHERE a.Max (PostedDate) = a.PostedDate
March 9, 2012 at 8:02 am
nilesh k (3/9/2012)
This is what i tried:Option 1:
SELECT base.*
FROM table base
INNER JOIN (SELECT segment, MAX([PostedDate]) AS Max (PostedDate)
FROM EBBReport
GROUP BY segment) sub ON base.segment = sub.segment AND base.[PostedDate]
Option 2:
SELECT *
FROM (SELECT *, MAX(PostedDate) OVER(PARTITION BY segment) AS Max(PostedDate) FROM table) a
WHERE a.Max (PostedDate) = a.PostedDate
Well, yes. Of course you have syntax errors.
1. You have "AS Max(PostedDate)" which won't work. Either wrap it in [] or get rid of the ()
2. You have "AND base.[PostedDate]" but no comparison. Add the comparison back in.
3. FROM table --> INNER JOIN EBBReport. Which is it?
Try the below: -
--Option 1:
SELECT base.*
FROM EBBReport base
INNER JOIN (SELECT segment, MAX([PostedDate]) AS MaxPostedDate
FROM EBBReport
GROUP BY segment) sub ON base.segment = sub.segment
AND base.[PostedDate] = sub.MaxPostedDate
--Option 2:
SELECT *
FROM (SELECT *, MAX(PostedDate) OVER(PARTITION BY segment) AS MaxPostedDate
FROM EBBReport) a
WHERE a.MaxPostedDate = a.PostedDate
March 9, 2012 at 8:25 am
OK.. my bad.
My earlier query is somethig like this and i want to acomodate maxposteddate in this query:
SELECT * From Table WHERE IsCurrent=0 AND
(Datediff(dd,@EFFdate,convert(varchar, getdate(), 1))<= 90
and Datediff(dd,@EFFdate,convert(varchar, getdate(), 1))>= 0)
and ( DATEPART(dd,effdate) = DATEPART(dd,@EFFdate) and
DATEPART(mm,effdate) = DATEPART(mm,@EFFdate) and
DATEPART(yy,effdate) = DATEPART(yy,@EFFdate))
so if there are two records per segment it should pick only the latest segment out of it.
i appreciate your efforts!
March 12, 2012 at 2:54 am
Bit of a late reply.
--Your current code
SELECT *
FROM EBBReport
WHERE IsCurrent = 0
AND (Datediff(dd, @EFFdate, convert(VARCHAR, getdate(), 1)) <= 90 AND
Datediff(dd, @EFFdate, convert(VARCHAR, getdate(), 1)) >= 0)
AND (DATEPART(dd, effdate) = DATEPART(dd, @EFFdate) AND
DATEPART(mm, effdate) = DATEPART(mm, @EFFdate)AND
DATEPART(yy, effdate) = DATEPART(yy, @EFFdate))
--Option 1
SELECT base.*
FROM (SELECT *
FROM EBBReport
WHERE IsCurrent = 0
AND (Datediff(dd, @EFFdate, convert(VARCHAR, getdate(), 1)) <= 90 AND
Datediff(dd, @EFFdate, convert(VARCHAR, getdate(), 1)) >= 0)
AND (DATEPART(dd, effdate) = DATEPART(dd, @EFFdate) AND
DATEPART(mm, effdate) = DATEPART(mm, @EFFdate) AND
DATEPART(yy, effdate) = DATEPART(yy, @EFFdate)) ) base
INNER JOIN (SELECT segment, MAX([PostedDate]) AS MaxPostedDate
FROM (SELECT *
FROM EBBReport
WHERE IsCurrent = 0
AND (Datediff(dd, @EFFdate, convert(VARCHAR, getdate(), 1)) <= 90 AND
Datediff(dd, @EFFdate, convert(VARCHAR, getdate(), 1)) >= 0)
AND (DATEPART(dd, effdate) = DATEPART(dd, @EFFdate) AND
DATEPART(mm, effdate) = DATEPART(mm, @EFFdate) AND
DATEPART(yy, effdate) = DATEPART(yy, @EFFdate))) a
GROUP BY segment) sub ON base.segment = sub.segment AND base.[PostedDate] = sub.MaxPostedDate
--Option 2:
SELECT *
FROM (SELECT *, MAX(PostedDate) OVER(PARTITION BY segment) AS MaxPostedDate
FROM EBBReport
WHERE IsCurrent = 0
AND (Datediff(dd, @EFFdate, convert(VARCHAR, getdate(), 1)) <= 90 AND
Datediff(dd, @EFFdate, convert(VARCHAR, getdate(), 1)) >= 0)
AND (DATEPART(dd, effdate) = DATEPART(dd, @EFFdate) AND
DATEPART(mm, effdate) = DATEPART(mm, @EFFdate)AND
DATEPART(yy, effdate) = DATEPART(yy, @EFFdate))) a
WHERE a.MaxPostedDate = a.PostedDate
--Make your code better
SELECT *
FROM EBBReport
WHERE IsCurrent = 0
AND @EFFdate >= DATEADD(dd,-90+DATEDIFF(dd,'19000101',GETDATE()),'19000101') --I'm not convinced this is needed at all.
--May be better doing an IF based on it, return
--empty result-set if false.
AND DATEADD(dd, '19000101', DATEDIFF(dd, '19000101', @EFFdate)) = DATEADD(dd, '19000101', DATEDIFF(dd, '19000101', effdate))
--New Option 1:
SELECT base.*
FROM EBBReport base
WHERE IsCurrent = 0
AND @EFFdate >= DATEADD(dd,-90+DATEDIFF(dd,'19000101',GETDATE()),'19000101')
AND DATEADD(dd, '19000101', DATEDIFF(dd, '19000101', @EFFdate)) = DATEADD(dd, '19000101', DATEDIFF(dd, '19000101', effdate))
INNER JOIN (SELECT segment, MAX([PostedDate]) AS MaxPostedDate
FROM EBBReport
WHERE IsCurrent = 0
AND @EFFdate >= DATEADD(dd,-90+DATEDIFF(dd,'19000101',GETDATE()),'19000101')
AND DATEADD(dd, '19000101', DATEDIFF(dd, '19000101', @EFFdate)) = DATEADD(dd, '19000101', DATEDIFF(dd, '19000101', effdate))
GROUP BY segment) sub ON base.segment = sub.segment
AND base.[PostedDate] = sub.MaxPostedDate
--New Option 2:
SELECT *
FROM (SELECT *, MAX(PostedDate) OVER(PARTITION BY segment) AS MaxPostedDate
FROM EBBReport
WHERE IsCurrent = 0
AND @EFFdate >= DATEADD(dd,-90+DATEDIFF(dd,'19000101',GETDATE()),'19000101')
AND DATEADD(dd, '19000101', DATEDIFF(dd, '19000101', @EFFdate)) = DATEADD(dd, '19000101', DATEDIFF(dd, '19000101', effdate))) a
WHERE a.MaxPostedDate = a.PostedDate
--With IF instead
IF @EFFdate >= DATEADD(dd, - 90 + DATEDIFF(dd, '19000101', GETDATE()), '19000101')
BEGIN
SELECT base.*
FROM EBBReport base
WHERE IsCurrent = 0 AND
DATEADD(dd, '19000101', DATEDIFF(dd, '19000101', @EFFdate)) = DATEADD(dd, '19000101', DATEDIFF(dd, '19000101', effdate))
INNER JOIN (SELECT segment, MAX([PostedDate]) AS MaxPostedDate
FROM EBBReport
WHERE IsCurrent = 0 AND
DATEADD(dd, '19000101', DATEDIFF(dd, '19000101', @EFFdate)) = DATEADD(dd, '19000101', DATEDIFF(dd, '19000101', effdate))
GROUP BY segment) sub ON base.segment = sub.segment
AND base.[PostedDate] = sub.MaxPostedDate
END
ELSE
BEGIN
SELECT *
FROM EBBReport
WHERE 1 = 0
END
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply