Sql Query
-
I m facing a problem in this query
select a.eventid,b.name,convert(nvarchar,c.assigndate,106)as assigndate, convert(nvarchar,a.eventdate,106)as eventdate,a.event,a.details,a.eventtype from event as a left outer join emp as c on a.eventid=c.eventid left outer join user_info as b on b.userid=c.userid where(a.eventtype='Single Month' or(a.eventtype='All Month' and (select substring(convert(nvarchar,edate,102),9,4) from event where eventtype='All Month')>( select substring(convert(nvarchar,getdate(),102),9,4)) ))order by a.eventdateselect * from eventIn event table there are some fields like eventid,eventdate,eventtype n etc In user_info there are userid, name n etc fields i wanna get all record when 1> eventtype is Single Month and the eventdate is in current month n eventdate is heigher than currentdate 2> eventype is All Month and eventdate is heigher than current date eventid -----eventdate ------- eventtype 1 -----2008-04-15 00:00:00 ----- All Month 2 -----2008-04-20 00:00:00 ----- Single Month 3 -----2008-04-16 00:00:00 ----- Single Month 4 -----2008-04-16 00:00:00 ----- All Month The answer shd be eventid 2,3,4The Great Pleasure In Doing That Things That Other People Say U Can't By Doing This U Can Shut Their Mouth
-
I m facing a problem in this query
select a.eventid,b.name,convert(nvarchar,c.assigndate,106)as assigndate, convert(nvarchar,a.eventdate,106)as eventdate,a.event,a.details,a.eventtype from event as a left outer join emp as c on a.eventid=c.eventid left outer join user_info as b on b.userid=c.userid where(a.eventtype='Single Month' or(a.eventtype='All Month' and (select substring(convert(nvarchar,edate,102),9,4) from event where eventtype='All Month')>( select substring(convert(nvarchar,getdate(),102),9,4)) ))order by a.eventdateselect * from eventIn event table there are some fields like eventid,eventdate,eventtype n etc In user_info there are userid, name n etc fields i wanna get all record when 1> eventtype is Single Month and the eventdate is in current month n eventdate is heigher than currentdate 2> eventype is All Month and eventdate is heigher than current date eventid -----eventdate ------- eventtype 1 -----2008-04-15 00:00:00 ----- All Month 2 -----2008-04-20 00:00:00 ----- Single Month 3 -----2008-04-16 00:00:00 ----- Single Month 4 -----2008-04-16 00:00:00 ----- All Month The answer shd be eventid 2,3,4The Great Pleasure In Doing That Things That Other People Say U Can't By Doing This U Can Shut Their Mouth
Try this
select a.eventid,b.name,convert(nvarchar,c.assigndate,106)as assigndate, convert(nvarchar,a.eventdate,106)as eventdate,a.event,a.details,a.eventtype from event as a left outer join emp as c on a.eventid=c.eventid left outer join user_info as b on b.userid=c.userid WHERE a.eventdate > getdate() AND ( (a.eventtype = 'Single Month' and left(convert(varchar,getdate(),102),7) = left(convert(varchar,edate,102),7)) OR (a.eventtype = 'All Month') )Bob Ashfield Consultants Ltd