simple query for a pro - 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 2008 Forums

Transact-SQL (2008)

simple query for a pro

Author

Topic

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2010-06-24 : 02:51:45

background:
I got a table (tblrooster [roster in english]) with rows which represent calendar events on weekbasis, so i got a specific field for weekday.

This table i use to insert into tblcalendar each time i view a specific week.

problem:
in this table tblrooster, i also got a field with an id wich links to another table to specify when this calendar event is suposed to be inserted , the datefrom ..... so to give you and example of a couple of rows..


1	...N FIELDS...2 	6
2	...N FIELDS...2 	6
3	...N FIELDS...2 	6
4	...N FIELDS...3 	7
5	...N FIELDS...3 	7
6	...N FIELDS...2 	12
7	...N FIELDS...2 	12

now the last id links to the other table , this isnt actually relevant but justtelling you to get the picture...
the id before that, is to link the calendarevent to a specific person/team.

conclusion:
I want to display all rows , but where the last id, is the highest
so i want to get this rows, in this example


4	...N FIELDS...3 	7
5	...N FIELDS...3 	7
6	...N FIELDS...2 	12
7	...N FIELDS...2 	12

im not a sql noob, but such queries give me headaches....

thx for the help in advance :)

Sachin.Nand

2937 Posts

Posted - 2010-06-24 : 02:57:20

But how do you define 'last id" ?

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-24 : 03:21:58

perhaps something like this ?


select r.*
from   tblrooster r
       inner join
       (
           select calendarevent_id, last_id = max(last_id)
           from   tblrooster
           group by calendarevent
       ) m  on  r.calendarevent = m.calendarevent
            and r.last_id       = m.last_id

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

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2010-06-24 : 08:58:26

the last id has the name planningid, that one before it i call rosterid

yes idd, i need somethink like the last reply, thx alot in advance
now trying to get it working :)

*edit*

select R.*
from tblrooster R
inner join
(
select R2.roosterid, R2.planningid = max(R2.planningid)
from tblrooster R2
group by roosterid
) RR on R.roosterid = RR.roosterid
and R.planningid = RR.planningid

this result in an error near '='

doenst the max supposed to be in the WHERE clause ? imjust guessing :(

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-24 : 09:07:35

select R2.roosterid, ~~R2.~~planningid = max(R2.planningid)

No, you're never too old to Yak'n'Roll if you're too young to die.

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2010-06-24 : 09:28:42

select R.*
from tblrooster R
inner join
(
select R2.roosterid, R2.planningid
from tblrooster R2
WHERE R2.planningid = ( select max(M.planningid) from tblrooster M where M.roosterid = R2.roosterid )
group by R2.roosterid,R2.planningid

) RR on R.roosterid = RR.roosterid
and R.planningid = RR.planningid

order by R.roosterid

this seems to do the job, cant thank you enough :)
and on such short notice .....

this forum goes into mybookmarks :)

webfred and khtan, thx alot
1000 kudos for you :p

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2010-06-24 : 10:05:17

but actually, this isnt exactly what i need.... i just notice , sorry :(

i dont need the max of planid...
i actually need the value planid from the other table tblplanning where the (from)date
is closest to the date today... ( cuz i dont have a tilldate, mb i can add it,to make the query a bit more userfriendly :))

to sum up, all the table structures (simplified)

tblroster

id nfields rosterid planningid
1 ...N FIELDS... 2 6
2 ...N FIELDS... 2 6
3 ...N FIELDS... 2 6
4 ...N FIELDS... 3 7
5 ...N FIELDS... 3 7
6 ...N FIELDS... 2 12
7 ...N FIELDS... 2 12

table planning
(the id is ofcourse planid)

id rosterid datefrom
1 6 2010-01-01 00:00:00.000
1 7 2010-01-01 00:00:00.000
1 12 2010-07-01 00:00:00.000

doubleotwo
Yak Posting Veteran

69 Posts

Posted - 2010-06-24 : 14:09:54

i think i found it using an extra tilldate field

einddatum = tilldate
begindatum = fromdate

(im dutch :))


if @date = ''
BEGIN 
select @date = getdate();
END


select R.*
	from   tblrooster R
       inner join
       (
           select R2.roosterid, R2.planningid
           from   tblrooster R2
           WHERE R2.planningid = ( 
           
								--select max(M.planningid) 
								--from tblrooster M 
								--where M.roosterid = R2.roosterid 
								select TOP 1 Y.id
								from tblrooster_planning Y 
								where Y.id = R2.planningid 
								AND @date BETWEEN Y.begindatum AND Y.einddatum
								ORDER BY Y.begindatum DESC 
								
								
								
								) 
           group by R2.roosterid,R2.planningid
           
       ) RR  on  R.roosterid = RR.roosterid
            and R.planningid = RR.planningid
            
            order by R.roosterid

im still wondering if it was possible without the extra tilldate field

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources