Hey everyone, I'm trying to find the average elapsed time between two date fields in my application, The Initial date is stored on 1 table for all records, but the second Date, which is the DecisionDate is stored in 1 of 3 subsequent tables depending on the DecisionMade by the user. My query is as follows; SELECT Inquiry.ID, Inquiry.Date,Inquiry.DecisionMade, Inquirer.Program, coalesce(a.DecisionDate, b.DecisionDate, c.DecisionDate) as DecisionDate From Inquiry Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk left outer Join DecAdmit a on a.InquiryID_fk = Inquiry.ID And Inquiry.DecisionMade = 'Decision to admit' left outer Join NoAdmit b on b.InquiryID_fk = Inquiry.ID And Inquiry.DecisionMade = 'Decision not to admit' left outer Join NoDecision c on c.InquiryID_fk = Inquiry.ID AND Inquiry.DecisionMade = 'No decision yet' WHERE Inquiry.Date >= @StartDate AND Inquiry.Date <= @EndDate AND Inquirer.Program = 'Appleton' OR Inquirer.Program = 'The Brook' OR Inquirer.Program = 'Fernside' OR Inquirer.Program = 'Gunderson' OR Inquirer.Program = 'Hill Center' OR Inquirer.Program = 'Lincoln' OR Inquirer.Program = 'OCDI' OR Inquirer.Program = 'Pavilion' Order by Inquirer.Program ASC I am looking to display the results as follows: Program 1 Program 2 Etc... Total Inquiries 20 11 32 Avg. Days; Any Decision 4.55 3.42 11.23 : Hardest condition, need to use DecisionDate from all 3 decision tables somehow Avg. Days; Decision to admit 2.3 2.40 3.41 : I was trying to do something like IIF(Fields!DecisionMade.Value = "Decision to admit",Sum(DateDiff("d",Fields!Date.Value,Fields!DecisionDate.Value))/Count(Fields!ID.Value) ; however the DecisisionDate has to be referenced from the DecAdmit table, which I am unsure how to do. Avg. Days; Decision not to admit: Same thing as the above Decision to admit, but referencing the other NoAdmit tables DecisionDate If anyone has any idea how I may be able to accomplish this, or if I can reference the DecisionDate through the table such as a.DecisionDate, b.DecisionDate etc that would be great. Any help is 100% appreciated, I thank everyone for taking the time to look over this as I am just abotu as lost as i've ever been with these reporting services. Thanks again, NickG

Hello Nick, Thank you to post your question on TechNet forum. I reviewed the question, and found that the root cause is various. In order to narrow down this issue, I need your help to check the following things. Do you run the query in SSMS? Please try not to use coalesce() function and use the a.DecisionDate, b.DecisionDate, c.DecisionDate directly so that we can see whether the value is set NULL as expected.The format of data in the original post is not properly and we cannot understand it clearly. If possible, please take a screenshot to clarify it. If you have further questions about this issue, please tell us freely. Regards, EdwardEdward Zhu TechNet Community Support

Hello Edward, Sorry for the hastily created post, I was in a rush and didn't preview it. I ran the query in SQL Server 2008 after removing the coalesce and setting the Dates to variables, as you can see here; SELECT Inquiry.ID, Inquiry.Date,Inquiry.DecisionMade, Inquirer.Program, a.DecisionDate as AdmDecDate, b.DecisionDate as NoAdmDecDate, c.DecisionDate as NoDecDate From Inquiry Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk left outer Join DecAdmit a on a.InquiryID_fk = Inquiry.ID And Inquiry.DecisionMade = 'Decision to admit' left outer Join NoAdmit b on b.InquiryID_fk = Inquiry.ID And Inquiry.DecisionMade = 'Decision not to admit' left outer Join NoDecision c on c.InquiryID_fk = Inquiry.ID AND Inquiry.DecisionMade = 'No decision yet' WHERE Inquiry.Date >= '3/01/2012' AND Inquiry.Date <= '8/14/2012' AND Inquirer.Program = 'Appleton' OR Inquirer.Program = 'The Brook' OR Inquirer.Program = 'Fernside' OR Inquirer.Program = 'Gunderson' OR Inquirer.Program = 'Hill Center' OR Inquirer.Program = 'Lincoln' OR Inquirer.Program = 'OCDI' OR Inquirer.Program = 'Pavilion' Order by Inquiry.DecisionMade This properly retrieves the information I am looking for, it's just setting up the report that I am having my issues with. Here is what the table information should look like: Program 1 Program 2 Program 3 Total Inquiries: 20 11 32 Avg. Days; Any Decision 34.2 23.2 9.14 Avg. Days Decision to admit 2.3 1.9 9.32 and the logic(pseudo) behind what I am trying to do is like this; For Avg days to any decision: IIF(Fields!DecisionMade.Value = "Decision to admit" AND Fields!AdmDecDate.Value IsNot System.DBNull.Value ,Sum(DateDiff("d",Fields!AdmDecDate.Value,Fields!Date.Value))/Count(Fields!ID.Value), "No Decision Date") + IIF(Fields!DecisionMade.Value = "Decision not to admit" AND Fields!NoAdmDecDate.Value IsNot System.DBNull.Value ,Sum(DateDiff("d",Fields!NoAdmDecDate.Value,Fields!Date.Value))/Count(Fields!ID.Value),"No Decision Date") + IIF(Fields!DecisionMade.Value = "No decision yet" AND Fields!NoDecDate.Value IsNot System.DBNull.Value ,Sum(DateDiff("d",Fields!NoDecDate.Value,Fields!Date.Value))/Count(Fields!ID.Value),"No Decision Made") For avg days to decision to admit: Same logic as above for the first line; Basically only count the inquiries with the decisionmade = "Decision to admit", but I feel like every inquiry is being counted and since it doesn't have the AdmDecDate becomes a very long count. IIF(Fields!DecisionMade.Value = "Decision to admit" AND Fields!DecAdmDate.Value isNot System.DBNull.Value, Sum(DateDiff("d",Fields!AdmDecDate.Value,Fields!Date.Value))/Count(Fields!ID.Value),"Nothing") For avg. days to decision not to admit: Same as above for decision to admit, except with DecisionMade = "Decision not to admit" and the DecisionDate referenced becomes Fields!NoAdmDecDate.Value IIF(Fields!DecisionMade.Value = "Decision not to admit" AND Fields!NoAdmDecDate.Value isNot System.DBNull.Value, Sum(DateDiff("d",Fields!NoAdmDecDate.Value,Fields!Date.Value))/Count(Fields!ID.Value),"Nothing") If that isn't clear enough I completely understand, by the end of it I wasn't sure if I was typing correctly. Thanks for all of your help, NickG

Hello Nick, Thanks for your clarification. I reviewed all your requirement, and considered that we can modify the query using GROUP BY clause to achieve this goal. Please try to run the following query on your side to see whether it can achieve your goal. SELECT Count(Inquiry.ID), Inquiry.DecisionMade, Inquirer.Program, SUM(CASE WHEN a.DecisionDate IS NULL THEN 0 ELSE DATEDIFF(DAY, Inquiry.Date, a.DecisionDate) END) as AdmDecDate, SUM(CASE WHEN b.DecisionDate IS NULL THEN 0 ELSE DATEDIFF(DAY, Inquiry.Date, b.DecisionDate) END) as NoAdmDecDate, SUM(CASE WHEN c.DecisionDate IS NULL THEN 0 ELSE DATEDIFF(DAY, Inquiry.Date, c.DecisionDate) END) as NoDecDate From Inquiry Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk left outer Join DecAdmit a on a.InquiryID_fk = Inquiry.ID And Inquiry.DecisionMade = 'Decision to admit' left outer Join NoAdmit b on b.InquiryID_fk = Inquiry.ID And Inquiry.DecisionMade = 'Decision not to admit' left outer Join NoDecision c on c.InquiryID_fk = Inquiry.ID AND Inquiry.DecisionMade = 'No decision yet' WHERE Inquiry.Date >= '3/01/2012' AND Inquiry.Date <= '8/14/2012' AND Inquirer.Program = 'Appleton' OR Inquirer.Program = 'The Brook' OR Inquirer.Program = 'Fernside' OR Inquirer.Program = 'Gunderson' OR Inquirer.Program = 'Hill Center' OR Inquirer.Program = 'Lincoln' OR Inquirer.Program = 'OCDI' OR Inquirer.Program = 'Pavilion' Order by Inquiry.DecisionMade Group by Inquery.DecisionMade, Inquirer.Program In the query, I use SUM aggregate function to calculate the total time. If the value is NULL, then I change it to zero. I did not test the above query since I have no environment of the database. Please test it to see whether it meet your requirement. Regards, EdwardEdward Zhu TechNet Community Support

Hello Nick, Thanks for your clarification. I reviewed all your requirement, and considered that we can modify the query using GROUP BY clause to achieve this goal. Please try to run the following query on your side to see whether it can achieve your goal. SELECT Count(Inquiry.ID), Inquiry.DecisionMade, Inquirer.Program, SUM(CASE WHEN a.DecisionDate IS NULL THEN 0 ELSE DATEDIFF(DAY, Inquiry.Date, a.DecisionDate) END) as AdmDecDate, SUM(CASE WHEN b.DecisionDate IS NULL THEN 0 ELSE DATEDIFF(DAY, Inquiry.Date, b.DecisionDate) END) as NoAdmDecDate, SUM(CASE WHEN c.DecisionDate IS NULL THEN 0 ELSE DATEDIFF(DAY, Inquiry.Date, c.DecisionDate) END) as NoDecDate From Inquiry Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk left outer Join DecAdmit a on a.InquiryID_fk = Inquiry.ID And Inquiry.DecisionMade = 'Decision to admit' left outer Join NoAdmit b on b.InquiryID_fk = Inquiry.ID And Inquiry.DecisionMade = 'Decision not to admit' left outer Join NoDecision c on c.InquiryID_fk = Inquiry.ID AND Inquiry.DecisionMade = 'No decision yet' WHERE Inquiry.Date >= '3/01/2012' AND Inquiry.Date <= '8/14/2012' AND Inquirer.Program = 'Appleton' OR Inquirer.Program = 'The Brook' OR Inquirer.Program = 'Fernside' OR Inquirer.Program = 'Gunderson' OR Inquirer.Program = 'Hill Center' OR Inquirer.Program = 'Lincoln' OR Inquirer.Program = 'OCDI' OR Inquirer.Program = 'Pavilion' Order by Inquiry.DecisionMade Group by Inquery.DecisionMade, Inquirer.Program In the query, I use SUM aggregate function to calculate the total time. If the value is NULL, then I change it to zero. I did not test the above query since I have no environment of the database. Please test it to see whether it meet your requirement. Regards, EdwardEdward Zhu TechNet Community Support

Hi Guys, Im very much a newbie, but shouldnt the where clause have parentheses? WHERE (Inquiry.Date >= '3/01/2012' AND Inquiry.Date <= '8/14/2012') AND (Inquirer.Program = 'Appleton' OR Inquirer.Program = 'The Brook' OR Inquirer.Program = 'Fernside' OR Inquirer.Program = 'Gunderson' OR Inquirer.Program = 'Hill Center' OR Inquirer.Program = 'Lincoln' OR Inquirer.Program = 'OCDI' OR Inquirer.Program = 'Pavilion')