i'm creating timesheet app , have following tables...
tbltimeentry:
-entryid (int) -entrydate (datetime) -entereddate (datetime) -entryuser (int) -entryjob (int) -entrytask (int) -entryweekno (int) tblwagesweeks:
-weekid (int) -weekday (int) -date (datetime) tblwagesweeks contains weekid int assigning int each week. weekday int 1-7 allocating int each day of week. 3 years worth of dates have been entered tblwagesweeks corresponding weekday numbers , weekid numbers.
tbltimeentry used record time entries allocated date.
tbltimeentry.entryweekno matches tblwagesweeks.weekid , int represent week number. tbltimeentry.entrydate matches tblwagesweeks.date , datetime.
so front end display listview displaying weeks. users clicks on week , days in week displayed. when user clicks on day able enter time entry.
the problem have when try list time entries week using following query...
select *, convert(varchar(19),convert(date,date,106),103) dateformatted, case when entryhalfday = '1' 'half day' when entryhalfday = '0' '' end [entryhalfdaystring], case when entryfullday = '1' 'full day' when entryfullday = '0' '' end [entryfulldaystring], case when entereddate null '' else 'entry complete' end daystatus tblwagesweeks left join tbltimeentry b on a.[date] = b.entrydate (entryuser = '1' or entryuser null) , weekid = '25' the problem have if user i'm searching on ('1' in query above) not have time entry on particular date user does, empty date not show in list user not have opportunity enter time day
below output query above demonstrate...
weekid weekday date entryid entrydate entereddate entryuser entryjob entrytask entryfullday entryhalfday entryweekno dateformatted entryhalfdaystring entryfulldaystring daystatus 25 1 2015-06-15 23 2015-06-15 2015-07-14 1 5 5 1 0 25 15/06/2015 full day entry complete 25 2 2015-06-16 null null null null null null null null null 16/06/2015 null null 25 3 2015-06-17 null null null null null null null null null 17/06/2015 null null 25 4 2015-06-18 26 2015-06-18 2015-07-14 1 2 2 1 0 25 18/06/2015 full day entry complete 25 5 2015-06-19 null null null null null null null null null 19/06/2015 null null 25 7 2015-06-21 null null null null null null null null null 21/06/2015 null null as can see, day 6 missing.
when search same week using entryuser = 25 can see there entry day 6...
select *, convert(varchar(19),convert(date,date,106),103) dateformatted, case when entryhalfday='1' 'half day' when entryhalfday='0' '' end [entryhalfdaystring], case when entryfullday='1' 'full day' when entryfullday='0' '' end [entryfulldaystring], case when entereddate null '' else 'entry complete' end daystatus tblwagesweeks left join tbltimeentry b on a.[date] = b.entrydate (entryuser = '25' or entryuser null) , weekid = '25' weekid weekday date entryid entrydate entereddate entryuser entryjob entrytask entryfullday entryhalfday entryweekno dateformatted entryhalfdaystring entryfulldaystring daystatus 25 1 2015-06-15 23 2015-06-15 2015-07-14 1 5 5 1 0 25 15/06/2015 full day entry complete 25 2 2015-06-16 null null null null null null null null null 16/06/2015 null null 25 3 2015-06-17 null null null null null null null null null 17/06/2015 null null 25 4 2015-06-18 26 2015-06-18 2015-07-14 1 2 2 1 0 25 18/06/2015 full day entry complete 25 5 2015-06-19 null null null null null null null null null 19/06/2015 null null 25 7 2015-06-21 null null null null null null null null null 21/06/2015 null null so need know how can show week days in list, blank , populated single user , single week number.
any massively appreciated.
you need make user condition of join , week condition of main table this:
select *, convert(varchar(19),convert(date,date,106),103) dateformatted, case when entryhalfday='1' 'half day' when entryhalfday='0' '' end [entryhalfdaystring], case when entryfullday='1' 'full day' when entryfullday='0' '' end [entryfulldaystring], case when entereddate null '' else 'entry complete' end daystatus tblwagesweeks left join tbltimeentry b on a.[date] = b.entrydate , b.entryuser = 25 a.weekid = '25' 
Comments
Post a Comment