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 |
|
dtsql
Starting Member
2 Posts |
 Posted - 2013-05-31 : 20:07:53
|
| (long time since used sql).... I have an excel workbook in excel services with an ODC sql connection. The database is a testing db that has test sets that have a planned date and of course and execution date. The planned date is when we THINK we are going to execute them and is filled in by user. The execution date is when the system actually records it being ran. Here is the catcher... I might plan for 20 tests to run on 5/31 but actually run 15 of them on 5/28, 3 on 5/29 and 2 on 6/2. I need to count the planned number of sets, 20, for 5/31 and show that in the 5/31 Planned column in my pivot table. My sql does this ok now. However, when I count the actual days the test set actually runs it counts the number correctly but puts all of the counts in a single PASS column; the same 5/31 date column. Again, the planned count is fine and in the right column but I want the actual runs to appear in the date column they actually ran. For example, 15 in PASS column date of 5/28, 3 in PASS Column date of 5/29 and 2 in PASS Column 6/2....if those are the dates they ran. I restrict the number of dates that amounts can be retrieved to 14 days before today and 14 days in the future (for the planned...). Here is the SQL below. Can anyone tell me how I can do this.....I am so close I think. (SELECT cy_cycle as "Test Set", tc_plan_scheduling_date as "Plan Date" , count(tc_plan_scheduling_date) as "TotalTests", SUM(CASE WHEN tc_status = 'Passed' THEN 1 Else 0 END) as "Passed"FROM td.test, td.testcycl, td.release_cycles, td.cycle WHERE (ts_test_id = tc_test_id) and (tc_assign_rcyc = rcyc_id) and (tc_cycle_id = cy_cycle_id) and ( tc_assign_rcyc = '1042') and DATEDIFF(d,getdate(), tc_plan_scheduling_date) >= -14 and DATEDIFF(d,getdate(), tc_plan_scheduling_date) < 14 GROUP BY cy_cycle, tc_plan_scheduling_dateORDER BY cy_cycle, tc_plan_scheduling_date |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-01 : 05:32:27
|
| Sorry not much help from your explanationCan you post some sample data for us from the tables and then illustrate what you want?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
 |
|
|
dtsql
Starting Member
2 Posts |
Posted - 2013-06-01 : 11:21:53
|
| how can i put a picture in here? If i could put a screen shot in here I could show you the data and execl pivot table example I am trying to build. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-06-01 : 20:25:28
|
| you need to upload picture in shared server and post link here------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|
|
counting multiple columns by date in query