SQL help - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

SQL help

Author

Topic

nbs
Starting Member

22 Posts

Posted - 2007-08-06 : 12:27:44

Hello
I have a table which consists of a item number which can have multiple roles and each combination of a item number and role would have a date associated to it. here's an example
item number role date
1234 P1 2006-11-10
1234 p2 2006-11-11
1234 p3 2006-12-01 and so on

I would like to find all the records with dates spread out in different months. Cant seem to figure out the sql for it...

any ideas??
Thanks

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-06 : 12:28:45

Can you explain what you mean by >>> with dates spread out in different months

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

nbs
Starting Member

22 Posts

Posted - 2007-08-06 : 12:34:14

there may be a item number with a specific role in say April but there may be the same item number with a same or a different role in May, Jun, July. I want to be able to pick these set of records.
hope this is clear

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-06 : 12:35:37

Can you explain with some more sample data?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

nbs
Starting Member

22 Posts

Posted - 2007-08-06 : 12:49:19

item# role date
1234 p1 2006-11-02
1234 p2 2006-11-23
1234 p3 2006-12-04
1234 p2 2006-12-16
2123 t1 2006-01-05
7212 w1 2006-04-09
7217 y1 2006-02-12
7312 h1 2006-01-02
7312 h1 2006-04-09
7312 h2 2006-10-17
8712 j1 2006-06-06
8712 j3 2006-06-10
8712 j1 2006-06-20

if this was a sample I would want the results to have all the rows that had 1234 and 7312, because they have dates in multiple months with the same or different role.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-06 : 12:53:12

Looks like you want DISTINCT records for a given combination of ITem, role and date?

Does


SELECT DISTINCT ITem, Role, Date
FROM YourTable
WHERE...

work?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

nbs
Starting Member

22 Posts

Posted - 2007-08-06 : 12:59:10

My criteria to pick the records is the date. If for a particular item# (eg:1234) there are dates in multiple months, then pick all the records related to that item#.

I dont want to see any records which have single occurrences like 7217 or if they have multiple occurrences in the same month like 8712.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-06 : 13:10:53

[CODE]
Declare @t table (item int, role varchar(5), date datetime)
insert into @t
Select 1234, 'p1' ,'2006-11-02' union all
Select 1234, 'p2' ,'2006-11-23' union all
Select 1234, 'p3' ,'2006-12-04' union all
Select 1234, 'p2' ,'2006-12-16' union all
Select 2123, 't1' ,'2006-01-05' union all
Select 7212, 'w1' ,'2006-04-09' union all
Select 7217, 'y1' ,'2006-02-12' union all
Select 7312, 'h1' ,'2006-01-02' union all
Select 7312, 'h1' ,'2006-04-09' union all
Select 7312, 'h2' ,'2006-10-17' union all
Select 8712, 'j1' ,'2006-06-06' union all
Select 8712, 'j3' ,'2006-06-10' union all
Select 8712, 'j1' ,'2006-06-20'

Select item, min(role)
from @t
group by item, year(Date) , month(date)
having count(*) > 1
order by item

[/CODE]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-06 : 13:12:40

select a.item,a.role,a.date
from yourtable a
inner join
yourtable b
on a.item = b.item
where DATEDIFF(mm,a.date,b.date) >=1

Jim

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-06 : 13:19:36

Or even this:


Declare @t table (item int, role varchar(5), date datetime)
insert into @t 
Select 1234, 'p1' ,'2006-11-02' union all
Select 1234, 'p2' ,'2006-11-23' union all
Select 1234, 'p3' ,'2006-12-04' union all
Select 1234, 'p2' ,'2006-12-16' union all
Select 2123, 't1' ,'2006-01-05' union all
Select 7212, 'w1' ,'2006-04-09' union all
Select 7217, 'y1' ,'2006-02-12' union all
Select 7312, 'h1' ,'2006-01-02' union all
Select 7312, 'h1' ,'2006-04-09' union all
Select 7312, 'h2' ,'2006-10-17' union all
Select 8712, 'j1' ,'2006-06-06' union all
Select 8712, 'j3' ,'2006-06-10' union all
Select 8712, 'j1' ,'2006-06-20'

Select T.ITem, T.role, T.date
from @t T Where T.item in 
	(
	Select item
	from @t 
	group by item, year(Date) , month(date) 
	having count(*) > 1 
	) 
order by item

although Jim's solution would be more efficient because it uses JOIN as compared to an IN.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

nbs
Starting Member

22 Posts

Posted - 2007-08-06 : 14:43:43

the example i gave you was just a sample. there is a table in the database which already has the data in the order of thousands. Is there any other possibility instead of storing the data in a table variable.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-06 : 14:49:35

You just use the SELECT query and change the table to whatever your original table is. My code was just a sample to get the expected output from a small subset of data.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Esc2Xtc
Starting Member

8 Posts

Posted - 2007-08-06 : 20:38:43

quote:
Originally posted by nbs

the example i gave you was just a sample. there is a table in the database which already has the data in the order of thousands. Is there any other possibility instead of storing the data in a table variable.

hi nbs,

what jim was trying to give you is this query:
select a.item,a.role,a.date
from nbs_table a
inner join
nbs_table b
on a.item = b.item
where DATEDIFF(mm,a.date,b.date) >=1

All you need to do is, replace the two words "nbs_table" above with your table name whatever it is. Also check to see that your column names are in fact: item, role, date. If not, then replace their names accordingly in the query above. Hope this explains your case.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources