0

I have an ASP.NET chart control that will show the estimated time vs time spent by each student on each class activity. I would like to have a dropdownlist control to filter the chart by the activity status; All, Open, Closed. I already populated the chart control and dropdownlist control with the data from the database. However, I am struggling now with modifying my query in order to filter the chart based on the selection in the dropdownlist.

The chart should display all the activities at the beginning (by default). Then, when the user selects a status from the dropdownlist, then the chart will be filtered based on the selection

So how can I modify my query to include this optional filtration?

DB schema:

Task Table: TaskId, Title, EstimatedTime, StatusId
Entry Table: EntryId, TaskId
EntryDetails Table: EntryDetailsId, EntryId, Hours
Status Table: StatusId, Name

Here's my query:

SELECT     tbl_Task.Title AS [Task Title], tbl_Task.EstimatedTime AS [Estimated Time], SUM(tbl_EntryDetails.Hours) AS [Time Spent]
FROM         tbl_EntryDetails INNER JOIN
                      tbl_Entry ON tbl_EntryDetails.EntryId = tbl_Entry.EntryId RIGHT OUTER JOIN
                      tbl_Task ON tbl_Entry.TaskId = tbl_Task.TaskId
GROUP BY tbl_Task.Title, tbl_Task.EstimatedTime
user3107976
  • 185
  • 1
  • 2
  • 13
  • Are you just asking how to construct a where clause? – Xedni Jan 29 '15 at 23:23
  • Add where tbl_Task.StatusId == selected item value from the dropdown list. – Bhasyakarulu Kottakota Jan 29 '15 at 23:28
  • Thanks for the help but I think if I put this where condition, then no data will be retrieved if the user selects nothing from the dropdownlist. The chart should display all the data at the beginning and it will be filtered in case there is a selection of status. – user3107976 Jan 29 '15 at 23:33
  • You need to use Coalesce function to achieve that – sqlhdv Jan 29 '15 at 23:53
  • Dear @sqlhdv could you please help me with my query? – user3107976 Jan 29 '15 at 23:57
  • declare a variable for the value of the dropdown list (say, @DropdownStatus). Then make your where clause "WHERE (@DropdownStatus = 'All' or tbl_task.StatusID = @DropdownStatus)" – Xedni Jan 30 '15 at 00:29

0 Answers0