i apologize if title not descriptive enough. having hard time describing looking for.
i understand done on front end client requesting have data displayed in way. should show single column client information (lastname, firstname) , any/all booked appointments. since there may more 1 appointment per client duplicate data displayed along each line. this:
lastname | firstname | apptdate | mystarttime | myendtime
smith..............| john................| 4/7/2016........| 7:00.........................| 8:00
smith..............| john................| 4/9/2016........| 6:00.........................| 7:00
smith..............| john................| 4/14/2016......| 10:00.......................| 11:00
arnold.............| williams..........| 4/10/2016......| 7:00.........................| 11:00
arnold.............| williams..........| 4/11/2016......| 8:00.........................| 12:00
but displayed as:
smith..............| john................| 4/7/2016........| 7:00.........................| 8:00
................................................| 4/9/2016........| 6:00.........................| 7:00
................................................| 4/14/2016......| 10:00.......................| 11:00
arnold..............| williams.........| 4/10/2016.......| 7:00.........................| 11:00
................................................| 4/11/2016.......| 8:00.........................| 12:00
this working with:
select case when cli.rownum = 1 cli.clientid end 'clientid' ,case when cli.rownum = 1 cli.lastname end 'lastname' ,case when cli.rownum = 1 cli.firstname end 'firstname' ,case when cli.rownum = 1 cli.homephone end 'home' ,case when cli.rownum = 1 cli.cellphone end 'cell' ,info.* ,case when cli.rownum = 1 cli.staffalertmsg end 'alert' ,cli.notes 'notes' ,convert(varchar(10),cli.classdate,101) 'date' ,cli.mystarttime 'start' ,cli.myendtime 'end' ,cli.typename 'appointment' (select c.clientid ,c.lastname ,c.firstname ,c.homephone ,c.cellphone ,info.* ,r.notes ,c.staffalertmsg ,r.classdate ,r.mystarttime ,r.myendtime ,vt.typename ,row_number() over(partition c.clientid order c.clientid) 'rownum' clients c left outer join tblreservation r on c.clientid = r.clientid left outer join tblvisittypes vt on vt.typeid = r.visittype outer apply (select max(case when civ.clientindexid = 4 civ.clientindexvaluename end) 'priority' ,max(case when civ.clientindexid = 5 civ.clientindexvaluename end) 'lang' ,max(case when civ.clientindexid = 17 civ.clientindexvaluename end) 'inter' ,max(case when ccf.name 'prac' ccv.textval end) 'prac' ,max(case when st.typeid = 100000001 st.typename end) 'ride?' ,max(case when st.typeid = 100000006 st.typename end) 'l?' tblclientcustomvalues ccv inner join tblclientcustomfields ccf on ccv.id = ccf.id inner join tblclientindexdata cid on ccv.clientid = cid.clientid inner join tblclientindexvalue civ on cid.clientindexvalueid = civ.clientindexvalueid left join [type details] td on ccv.clientid = td.clientid left join [student types] st on td.typeid = st.typeid c.clientid = ccv.clientid ) info ) cli cli.clientid != '-2' , cli.clientid != '0' , cli.clientid != '1' , (cli.classdate >= '4/1/2016' , cli.classdate <= '4/30/2016')
assuming want return name first row of each client should work (also assuming primary key column of id on clients table):
select case when cli.rownum = 1 cli.lastname end lastname ,case when cli.rownum = 1 cli.firstname end firstname ,convert(varchar(10),cli.apptdate,101) 'apptdate' ,cli.mystarttime ,cli.myendtime (select c.id ,c.lastname ,c.firstname ,r.apptdate ,r.mystarttime ,r.myendtime ,row_number() on (partition c.id order c.id, r.apptdate, r.mystarttime) rownum clients c left outer join tblreservation r on c.clientid = r.clientid left outer join tblvisittypes vt on vt.typeid = r.visittype) cli order cli.id, cli.rownum i have made both join left outer joins second inner join have made first join inner, assume not wanted.
Comments
Post a Comment