Return all rows less than the value of a Min Date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Return all rows less than the value of a Min Date

Author

Topic

phrankbooth
Posting Yak Master

162 Posts

Posted - 2013-11-18 : 21:57:47

In the table below how can I return the rows where [Date] < than the MIN JoinedDate in each grouping of the JoinedTag field??

Declare @tmp table ([Date] Date, Tag varchar(10), Product Varchar(10), ProdType varchar(10), JoinedDate Date, JoinedTag varchar(10))

insert into @tmp
values
('2013-10-31', 'Tag1', 'Prod1', 'Type1', Null, NULL),
('2013-10-31', 'Tag1', 'Prod1', 'Type1', Null, NULL),
('2013-10-31', 'Tag1', 'Prod1', 'Type1', Null, NULL),
('2013-11-01', 'Tag1', 'Prod1', 'Type1', Null, NULL),
('2013-11-01', 'Tag1', 'Prod1', 'Type1', Null, NULL),
('2013-11-01', 'Tag1', 'Prod1', 'Type1', Null, NULL),
('2013-11-02', 'Tag1', 'Prod1', 'Type1', '2013-11-02', 'Tag1'), --for this joinedtag return all rows less than the joineddate
('2013-10-31', 'Tag2', 'Prod2', 'Type2', Null, NULL),
('2013-10-31', 'Tag2', 'Prod2', 'Type2', Null, NULL),
('2013-10-31', 'Tag2', 'Prod2', 'Type2', Null, NULL),
('2013-11-01', 'Tag2', 'Prod2', 'Type2', '2013-11-01', 'Tag2'), --for this joinedtag return all rows less than the joineddate
('2013-11-02', 'Tag2', 'Prod2', 'Type2', Null, NULL),
('2013-11-02', 'Tag2', 'Prod2', 'Type2', Null, NULL),
('2013-11-02', 'Tag2', 'Prod2', 'Type2', Null, NULL)

So for Tag1, the reult should be:
('2013-10-31', 'Tag1', 'Prod1', 'Type1', Null, NULL)
('2013-10-31', 'Tag1', 'Prod1', 'Type1', Null, NULL)
('2013-10-31', 'Tag1', 'Prod1', 'Type1', Null, NULL)
('2013-11-01', 'Tag1', 'Prod1', 'Type1', Null, NULL)
('2013-11-01', 'Tag1', 'Prod1', 'Type1', Null, NULL)
('2013-11-01', 'Tag1', 'Prod1', 'Type1', Null, NULL)

And for Tag2, the result should be:
('2013-10-31', 'Tag2', 'Prod2', 'Type2', Null, NULL)
('2013-10-31', 'Tag2', 'Prod2', 'Type2', Null, NULL)
('2013-10-31', 'Tag2', 'Prod2', 'Type2', Null, NULL)

Thanks

--PhB

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-11-19 : 01:27:09

[code]select *
from @tmp t
inner join
(
select JoinedTag, JoinedDate = min (JoinedDate)
from @tmp
group by JoinedTag
) m on t.Tag = m.JoinedTag
and t.Date < m.JoinedDate[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-19 : 01:38:41

[code]
SELECT t.*
FROM @tmp t
WHERE EXISTS (SELECT 1
FROM @tmp
WHERE Tag = t.Tag
AND [Date] >= t.[Date]
AND Tag = JoinedTag
AND JoinedDate IS NOT NULL
)
AND JoinedTag IS NULL
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

phrankbooth
Posting Yak Master

162 Posts

Posted - 2013-11-19 : 09:24:07

Perfect! Thanks KHTan.

--PhB

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources