I have some code that Iam struggling with. It works fine until I add a column from different table called (d.commenttext).
Originally this code below brought back thousands of rows. Yet, when I connected the (d.commenttext) field from the (vp_tmshtcommments d) table it only brings back a few records. It's because in that (vp_tmshtcomments d) table there is only comments once in a while. So most of the time, there is no value that exists and it won't bring back all those row that showing originally in this report.
I understand why it does this. Yet, I am trying to figure out a way to create a 'NULL' value for all those records that dont' exist so it still brings back the same amount of data that it did before I added the (d.commenttext) field and linked it to the report with the (vp_tmshtcomments d) table with this part of the code (and a.personnum = d.personnum and a.applydate = d.eventdate)
Here is the code below.
To restate my issue one more time. The field above sometimes does not have a line or record for the (d.commenttext) so I need a way to still bring back all the original data, and only fill that column unless if data exists in the the table where that field resides. I know it's a rookie question. Please help. Much thanks will be rewarded.
Originally this code below brought back thousands of rows. Yet, when I connected the (d.commenttext) field from the (vp_tmshtcommments d) table it only brings back a few records. It's because in that (vp_tmshtcomments d) table there is only comments once in a while. So most of the time, there is no value that exists and it won't bring back all those row that showing originally in this report.
I understand why it does this. Yet, I am trying to figure out a way to create a 'NULL' value for all those records that dont' exist so it still brings back the same amount of data that it did before I added the (d.commenttext) field and linked it to the report with the (vp_tmshtcomments d) table with this part of the code (and a.personnum = d.personnum and a.applydate = d.eventdate)
Here is the code below.
Code:
select b.homelaboracctdsc,
b.homelaborleveldsc1 + '/' + b.homelaborleveldsc2 + '/' + b.homelaborleveldsc3 + '/' + b.homelaborleveldsc4 + '/' + b.homelaborleveldsc5 as HomeLaborDesc,
a.personnum,
a.personfullname,
a.paycodename,
c.payrulename,
sum(a.timeinseconds / 60) as Hours,
sum(a.wageamount) as Wages,
sum(a.moneyamount) as Wages2
d.commenttext
from vp_totals a,
vp_person b,
vp_employee c,
vp_tmshtcomments d
where a.personnum = b.personnum
and b.personnum = c.personnum
and a.personnum = d.personnum
and a.applydate = d.eventdate
and (a.applydate >= getdate()-7
and a.applydate <= getdate())
and b.homelaborlevelname2 = 'D12'
and b.homelaborlevelname3 = '06882'
group by b.homelaboracctdsc,
b.homelaborleveldsc1,
b.homelaborleveldsc2,
b.homelaborleveldsc3,
b.homelaborleveldsc4,
b.homelaborleveldsc5,
a.personnum,
a.personfullname,
a.paycodename,
c.payrulename,
d.commenttext
To restate my issue one more time. The field above sometimes does not have a line or record for the (d.commenttext) so I need a way to still bring back all the original data, and only fill that column unless if data exists in the the table where that field resides. I know it's a rookie question. Please help. Much thanks will be rewarded.