Hello all,
This may be covered somewhere but I couldn't find it with searching here.
If i built a view that has distinct posted_dt and posted_by, like this
select distinct posted_by, posted_dtfrom T_BATCH a (nolock)where posted_by is not null
I would like to have report parameters that return all posted_by between a given date range but DISTINCT.
I run this report for multiple dates and whenever i have multiple dates the users appear multiple times. If a single user posted multiple batches at different points in the date the posted_dt is different. This is all okay.
However I want to build a report that looks like this - a report that pulls some details between a posting start and end date, but i gives the user the ability to restrict it by who posted the batches. It also only displays users who posted batches in the respective date range.
The issue is I now get users appearing in my list multiple times. I can't get rid oft he date bit because I need the date bit to filter which users we see as part of the output, but I only want to see distinct users.
I think you can accomplish this by looking at the basic T_METUSER table that has 1 row for each user, but then nesting a query to the T_BATCH table in your WHERE clause.Table = T_METUSERDisplay column = useridData column = useridWhere Clause = userid in (SELECT distinct posted_by FROM T_BATCH WHERE posted_dt between <<p1>> and <<p2>>)Haven't tested it out myself but I think this would work. I'm pretty certain nested queries work in the WHERE clause.