Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kiki8375
Starting Member
2 Posts |
 Posted - 2008-03-19 : 12:13:02
|
| I havea 2 part issue with a query I'm trying to run for a report.I have an incident report that needs to show results based on dates from the week before. In addition, This report should run every Monday morning, with the exception of a Monday holiday, where in that case, the report will run the next business day. I have an idea to use a case statement, but it doesn't seem to work for me. Any suggestions? Also,part of the query I have is pulling back the right data, as long as I don’t include parameters. When I do add values to the parameters the query includes all the dates. I need to see either/or - not both. I just want to either see dates without the param values or see only dates that I ask for in a parameter. Does that make sense? Here’s the query (thanks!): SELECT incident.incid_id, incident.incid_short_desc, incident.incid_received_date_time, incident.incid_closed_date_time, security_users.description, incident.incid_assigned_to, incident.tagged_delete_flag, activity_result_master.result_desc, incident_priority_master.priority_desc, activities.result_id FROM activities AS activities INNER JOIN incident AS incident ON activities.incid_id = incident.incid_id INNER JOIN activity_result_master AS activity_result_master ON activities.result_id = activity_result_master.result_id INNER JOIN security_users AS security_users ON incident.incid_assigned_to = security_users.name INNER JOINincident_priority_master AS incident_priority_master ON incident.priority_id = incident_priority_master.priority_idWHERE incident.tagged_delete_flag = 'N' AND activities.result_id = '6' AND(incid_received_date_time >= DATEADD (d,-7,GETDATE()) AND incid_closed_date_time <=DATEADD (d,-3,GETDATE())) ORCONVERT(varchar,incident.incid_received_date_time,101) >= @StartDate AND CONVERT(varchar,incident.incid_closed_date_time,101) <= @EndDateORDER BY incident.incid_assigned_to, incident.incid_id |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-03-19 : 12:27:39
|
| Can you provide some sample data to supplement your explanation & also give the expected o/p out of them? |
 |
|
|
kiki8375
Starting Member
2 Posts |
Posted - 2008-03-19 : 14:02:53
|
| This is an abbreviated ex. of how the data would display in the report if it ran today showing results starting from 7 days ago:Incid_id incid_received_date_time incident_closed_date_time38954 3/12/08 2:01:00 PM 3/12/08 2:45:00 PM38955 3/12/08 2:29:00 PM 3/12/08 3:28:00 PM38954 3/13/08 2:47:00 PM 3/14/08 2:58:00 PMI also want to be able to search for a range of dates we have incidents for that DO NOT include the dates from a week ago. For example, if I want to see all the incidents that were opened between 1/14/08 to 1/18/08 my results SHOULD look like the example above, ONLY including dates within the range I specific. However, I'm getting results that look like this: Incid_id incid_received_date_time incident_closed_date_time37594 1/14/08 11:59:00 AM 1/14/08 3:42:00 PM37629 1/15/08 8:07 AM 1/16/08 2:17:00 PM38954 3/12/08 2:01:00 PM 3/12/08 2:45:00 PM38955 3/12/08 2:29:00 PM 3/12/08 3:28:00 PM38954 3/13/08 2:47:00 PM 3/14/08 2:58:00 PMI also need to create a query that I can use as a stored procedure, perhaps that will run should my report get opened on a Tuesday. Currently, no matter what day the report runs, I see results going back 7 days. I need to see results from the Monday - Friday of the previous week, regardless of what day of 'this' week when I view that report. |
|
|
|
|
|
|
|
|
Query with parameters not returning right results