SQL Query 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 Query help!

Author

Topic

padsp
Starting Member

21 Posts

Posted - 2007-11-27 : 17:57:29

Hi all!

I am looking for a strange aql query solution & need your help. I have table called "Meetings" & structure is:
MEETINGS
mtg_id mtg_date bitmtg_new bitmtg_upd bitmtg_food
100 11/30/2007 8am 1 0 0
101 11/30/2007 10am 0 0 1
102 12/08/2007 10am 0 1 0
103 12/30/2007 8am 1 0 0
104 12/30/2007 10am 0 0 1

I need result set like this:
mtg_new mtg_upd mtg_food
11/30/2007 8am 12/08/2007 10am 11/30/2007 10am
12/30/2007 8am NULL 12/30/2007 10am

The idea is filter by above bit columns(like bitmtg_new=1 or so)

Kindly post your comments/suggestions on this!

Thanks
Bob

kutumbarao
Starting Member

13 Posts

Posted - 2007-11-28 : 00:05:36

Hi Padsp,

Please try with this, In this query I considered “bitmtg_New” as mandatory for every section.

SELECT mtg_new,
mtg_upd,
mtg_food
FROM
(SELECT new_mtg_Id,
MAX(CASE WHEN bitmtg_new = 1 THEN mtg_date ELSE NULL END) mtg_new,
MAX(CASE WHEN bitmtg_upd = 1 THEN mtg_date ELSE NULL END) mtg_upd,
MAX(CASE WHEN bitmtg_food = 1 THEN mtg_date ELSE NULL END) mtg_food
FROM
(SELECT mtg_Id,
(SELECT MAX(mtg_Id) FROM #MEETINGS WHERE bitmtg_new = 1 AND mtg_Id <= N.mtg_Id) new_mtg_Id,
mtg_date,
bitmtg_new,
bitmtg_upd,
bitmtg_food
FROM #MEETINGS N) M
GROUP BY new_mtg_Id) X

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources