How can I get the average number of unique patients per month from a table?

• NineIron

  SSChampion

  Points: 12529

  More actions

  October 22, 2013 at 11:37 am

  #395707

  I need to count the number of active patients per month that are included in the doctor's panel of patients. Active patient is defined by a visit within the last year. So, given a date range, how many unique patients does the doctor have? So, if I'm running the report for the year 2013, a patient who's last visit was 1/11/2011 should not come up in the report. Then, I want to get the average number of patients for the time period.

  I have no idea how to even start this report.

  create table dbo.Visits

  (

  MRN varchar(8),

  ApptDt datetime

  )

  insert into dbo.Visits(MRN, ApptDt) values('00000203',01/03/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',01/09/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',01/18/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',01/30/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',02/21/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',03/01/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',03/25/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',03/28/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',04/26/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',04/27/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',04/29/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',04/30/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',05/13/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',06/01/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',06/19/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',06/25/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',06/28/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',07/15/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',07/17/2009)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',07/20/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',07/28/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',08/16/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',08/20/2009)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',08/21/2009)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',08/22/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',08/23/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',08/27/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',08/29/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',08/31/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',09/01/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',09/14/2009)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',09/20/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',09/28/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',10/01/2009)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',10/01/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',10/29/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',11/28/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',12/07/2009)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',12/07/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',12/10/2009)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',12/13/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',12/17/2009)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',12/20/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',01/13/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',01/17/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',01/17/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',01/22/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',02/01/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',02/02/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',02/04/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',02/04/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',02/05/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',02/06/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',02/10/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',03/05/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',03/08/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',03/14/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',03/16/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',03/18/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',03/21/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',03/23/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',04/04/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',04/22/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',04/30/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',05/04/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',05/06/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',05/08/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',05/14/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',05/17/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',05/18/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',06/03/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',06/05/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',06/05/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',06/11/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',06/12/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',06/17/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',07/17/2009)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',07/17/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',08/03/2009)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',08/03/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',08/05/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',08/06/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',08/16/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',08/25/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',09/20/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',10/03/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',10/06/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',10/07/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',10/12/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',10/12/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',10/19/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',10/19/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',10/20/2009)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',10/27/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',11/02/2009)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',11/03/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',11/04/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',11/15/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',12/09/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',12/14/2009)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',12/18/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',01/03/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',01/05/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',01/09/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',01/10/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',01/11/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',01/14/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',01/16/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',01/17/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',01/17/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',01/22/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',01/23/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',01/24/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',01/30/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',01/31/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',02/01/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',02/11/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',02/13/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',02/16/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',02/17/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',02/18/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',02/19/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',02/21/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',02/22/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',02/26/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',02/27/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',03/05/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',03/12/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',03/14/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',03/17/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',03/19/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',03/23/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',03/29/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',03/29/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',04/02/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',04/02/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',04/04/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',04/04/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',04/12/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',04/23/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',04/24/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',04/27/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',05/02/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',05/07/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',05/08/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',05/09/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',05/14/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',05/16/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',06/03/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',06/04/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',06/06/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',06/11/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',06/18/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',06/18/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',06/20/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',06/21/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',06/22/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',06/25/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',07/02/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',07/06/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',07/07/2009)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',07/08/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',07/09/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',07/11/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',07/14/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',07/16/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',07/18/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',07/19/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',07/21/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',07/27/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',07/28/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',07/31/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',08/02/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',08/02/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',08/05/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',08/08/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',08/10/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',08/11/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',08/17/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',08/18/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',08/22/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',08/22/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',08/24/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',08/27/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',08/30/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',08/31/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',09/03/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',09/04/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',09/07/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',09/07/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',09/08/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',09/14/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',09/16/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',09/17/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',09/21/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',09/23/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',09/24/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',09/24/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',09/26/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',09/27/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',10/02/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',10/03/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',10/06/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',10/13/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',10/15/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',10/17/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',10/19/2009)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',10/20/2009)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',10/21/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',10/24/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',10/25/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',10/31/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',11/01/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',11/07/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',11/07/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',11/08/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',11/15/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',11/18/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',11/22/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',11/23/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',11/28/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',11/28/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',11/29/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',12/03/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',12/06/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',12/07/2009)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',12/09/2009)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',12/11/2009)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',12/13/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',12/19/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',12/21/2009)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',12/23/2009)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',12/28/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',01/06/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',01/15/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',02/20/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',02/27/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',03/19/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',03/26/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',04/02/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',04/11/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',04/18/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',04/25/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',05/08/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',05/20/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',06/10/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',06/17/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',07/15/2009)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',07/17/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',07/19/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',08/06/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',08/06/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',08/10/2009)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',08/10/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',08/16/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',08/17/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',09/01/2009)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',09/13/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',09/16/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',10/12/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',10/16/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',11/05/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',11/24/2009)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',12/12/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',12/14/2009)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',01/03/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',01/04/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',01/07/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',01/27/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',01/27/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',01/31/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',02/04/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',02/13/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',02/20/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',02/21/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',02/23/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',03/02/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',04/03/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',04/23/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',04/28/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',05/23/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',05/23/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',06/11/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',06/25/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',07/06/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',07/07/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',07/24/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',07/26/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',07/27/2009)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',07/30/2010)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',07/31/2009)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',08/01/2011)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',08/05/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',08/13/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',08/22/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',08/23/2012)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',09/11/2009)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',09/16/2013)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',09/20/2010)

• mickyT

  SSChampion

  Points: 10360

  More actions

  October 22, 2013 at 12:39 pm

  #1660644

  Hi

  I'm not sure if this is the logic you want, but this might help you get started. Unfortunately your insert statements need the dates fixed and I was unable to test it.

  declare @reportyear date = '20130101';

  with ActivePatients as (

  SELECT MRN

  FROM dbo.Visits

  WHERE ApptDt > DATEADD(year,-1,@reportyear) and ApptDt < @reportyear

  GROUP BY MRN

  )

  SELECT DATEPART(month, ApptDt) [Month], DATEPART(year, ApptDt) [Year], COUNT(distinct MRN)

  FROM dbo.Visits

  WHERE ApptDt >= @reportyear and ApptDt < DATEADD(year,1,@reportyear)

  group by DATEPART(month, ApptDt), DATEPART(year, ApptDt)

• NineIron

  SSChampion

  Points: 12529

  More actions

  October 22, 2013 at 12:45 pm

  #1660646

  Thanx. Sorry about the dates.

  I'll give it a shot.

• NineIron

  SSChampion

  Points: 12529

  More actions

  October 22, 2013 at 1:23 pm

  #1660667

  It returned nothing. I tried to tweak it a bit but, still couldn't get anything. I formatted the dates, if your willing to do some testing.

  Thanx.

  create table dbo.Visits

  (

  MRN varchar(8),

  ApptDt datetime

  )

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2011-01-03)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2013-01-09)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2010-01-18)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2012-01-30)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2011-02-21)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2012-03-01)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2013-03-25)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2013-03-28)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2010-04-26)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2012-04-27)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2013-04-29)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2012-04-30)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2010-05-13)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2012-06-01)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2013-06-19)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2013-06-25)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2010-06-28)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2010-07-15)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2009-07-17)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2012-07-20)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2010-07-28)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2013-08-16)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2009-08-20)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2009-08-21)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2011-08-22)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2010-08-23)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2013-08-27)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2012-08-29)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2011-08-31)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2011-09-01)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2009-09-14)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2013-09-20)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2010-09-28)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2009-10-01)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2013-10-01)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2010-10-29)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2011-11-28)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2009-12-07)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2012-12-07)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2009-12-10)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2010-12-13)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2009-12-17)

  insert into dbo.Visits(MRN, ApptDt) values('00000203',2012-12-20)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2012-01-13)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2011-01-17)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2013-01-17)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2010-01-22)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2012-02-01)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2010-02-02)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2011-02-04)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2013-02-04)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2010-02-05)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2012-02-06)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2012-02-10)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2013-03-05)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2012-03-08)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2011-03-14)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2012-03-16)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2013-03-18)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2013-03-21)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2012-03-23)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2013-04-04)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2010-04-22)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2013-04-30)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2010-05-04)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2010-05-06)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2012-05-08)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2012-05-14)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2011-05-17)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2012-05-18)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2013-06-03)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2012-06-05)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2013-06-05)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2012-06-11)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2013-06-12)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2013-06-17)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2009-07-17)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2013-07-17)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2009-08-03)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2010-08-03)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2011-08-05)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2012-08-06)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2011-08-16)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2011-08-25)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2011-09-20)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2013-10-03)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2011-10-06)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2011-10-07)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2010-10-12)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2011-10-12)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2011-10-19)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2012-10-19)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2009-10-20)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2011-10-27)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2009-11-02)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2011-11-03)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2010-11-04)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2012-11-15)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2011-12-09)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2009-12-14)

  insert into dbo.Visits(MRN, ApptDt) values('00003436',2012-12-18)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-01-03)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-01-05)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-01-09)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2013-01-10)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-01-11)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2013-01-14)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-01-16)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-01-17)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2013-01-17)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2010-01-22)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-01-23)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2013-01-24)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-01-30)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-01-31)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2013-02-01)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2013-02-11)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-02-13)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2010-02-16)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2010-02-17)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-02-18)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2013-02-19)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-02-21)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-02-22)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2013-02-26)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-02-27)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2013-03-05)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-03-12)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-03-14)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-03-17)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2013-03-19)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-03-23)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-03-29)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-03-29)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-04-02)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2013-04-02)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-04-04)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-04-04)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-04-12)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2013-04-23)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-04-24)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2010-04-27)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-05-02)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-05-07)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2013-05-08)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-05-09)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2013-05-14)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-05-16)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2013-06-03)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-06-04)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-06-06)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-06-11)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2010-06-18)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-06-18)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2013-06-20)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2010-06-21)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-06-22)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-06-25)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-07-02)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-07-06)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2009-07-07)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2013-07-08)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-07-09)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-07-11)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2010-07-14)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-07-16)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-07-18)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-07-19)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2010-07-21)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-07-27)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2010-07-28)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-07-31)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-08-02)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-08-02)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2013-08-05)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-08-08)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-08-10)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2010-08-11)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-08-17)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-08-18)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-08-22)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2013-08-22)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-08-24)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-08-27)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-08-30)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-08-31)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2013-09-03)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2010-09-04)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-09-07)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-09-07)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2010-09-08)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-09-14)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2010-09-16)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-09-17)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2010-09-21)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2010-09-23)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-09-24)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2013-09-24)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-09-26)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2010-09-27)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-10-02)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-10-03)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2010-10-06)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2010-10-13)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2013-10-15)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-10-17)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2009-10-19)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2009-10-20)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2010-10-21)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-10-24)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2010-10-25)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-10-31)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2010-11-01)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-11-07)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-11-07)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2010-11-08)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2010-11-15)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2010-11-18)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2010-11-22)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-11-23)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-11-28)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-11-28)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2010-11-29)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-12-03)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2010-12-06)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2009-12-07)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2009-12-09)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2009-12-11)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2010-12-13)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2012-12-19)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2009-12-21)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2009-12-23)

  insert into dbo.Visits(MRN, ApptDt) values('00005636',2011-12-28)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',2010-01-06)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',2013-01-15)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',2013-02-20)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',2012-02-27)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',2013-03-19)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',2012-03-26)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',2012-04-02)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',2012-04-11)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',2013-04-18)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',2013-04-25)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',2012-05-08)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',2013-05-20)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',2013-06-10)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',2013-06-17)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',2009-07-15)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',2013-07-17)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',2012-07-19)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',2010-08-06)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',2012-08-06)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',2009-08-10)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',2012-08-10)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',2013-08-16)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',2012-08-17)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',2009-09-01)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',2012-09-13)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',2013-09-16)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',2012-10-12)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',2013-10-16)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',2012-11-05)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',2009-11-24)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',2012-12-12)

  insert into dbo.Visits(MRN, ApptDt) values('00006823',2009-12-14)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2011-01-03)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2012-01-04)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2013-01-07)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2010-01-27)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2012-01-27)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2011-01-31)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2011-02-04)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2012-02-13)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2012-02-20)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2013-02-21)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2010-02-23)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2012-03-02)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2013-04-03)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2010-04-23)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2010-04-28)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2012-05-23)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2013-05-23)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2012-06-11)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2010-06-25)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2012-07-06)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2010-07-07)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2013-07-24)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2010-07-26)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2009-07-27)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2010-07-30)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2009-07-31)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2011-08-01)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2013-08-05)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2012-08-13)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2013-08-22)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2012-08-23)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2009-09-11)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2013-09-16)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2010-09-20)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2010-09-27)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2009-09-28)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2009-10-01)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2012-10-01)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2012-10-05)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2010-10-06)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2011-10-06)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2013-10-07)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2011-10-14)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2010-10-16)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2010-10-25)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2011-11-01)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2009-11-18)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2012-11-26)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2009-12-10)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2009-12-30)

  insert into dbo.Visits(MRN, ApptDt) values('00007828',2012-12-31)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2013-01-04)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2010-01-06)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2010-01-15)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2012-01-17)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2011-01-19)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2011-01-25)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2012-01-27)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2012-01-30)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2012-02-02)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2012-02-09)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2012-02-16)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2013-02-25)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2013-03-07)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2013-03-11)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2012-03-22)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2010-04-07)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2010-04-15)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2011-04-28)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2013-05-09)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2013-05-10)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2012-05-24)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2011-06-09)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2011-06-21)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2012-06-21)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2011-07-14)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2010-07-17)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2010-07-22)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2012-07-28)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2011-08-02)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2013-08-08)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2009-08-17)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2013-09-09)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2011-09-15)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2009-09-17)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2012-09-20)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2013-09-27)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2009-10-01)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2012-10-04)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2012-10-08)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2009-10-12)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2010-10-14)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2011-10-14)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2009-10-15)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2010-10-28)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2010-11-09)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2012-11-15)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2009-11-19)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2009-12-01)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2011-12-01)

  insert into dbo.Visits(MRN, ApptDt) values('00012527',2012-12-06)

  insert into dbo.Visits(MRN, ApptDt) values('00014345',2012-01-12)

  insert into dbo.Visits(MRN, ApptDt) values('00014345',2010-02-16)

  insert into dbo.Visits(MRN, ApptDt) values('00014345',2012-02-17)

  insert into dbo.Visits(MRN, ApptDt) values('00014345',2010-02-25)

  insert into dbo.Visits(MRN, ApptDt) values('00014345',2011-02-28)

  insert into dbo.Visits(MRN, ApptDt) values('00014345',2013-03-13)

  insert into dbo.Visits(MRN, ApptDt) values('00014345',2010-03-16)

  insert into dbo.Visits(MRN, ApptDt) values('00014345',2010-05-27)

  insert into dbo.Visits(MRN, ApptDt) values('00014345',2011-06-15)

  insert into dbo.Visits(MRN, ApptDt) values('00014345',2010-06-22)

  insert into dbo.Visits(MRN, ApptDt) values('00014345',2011-08-01)

  insert into dbo.Visits(MRN, ApptDt) values('00014345',2011-08-15)

  insert into dbo.Visits(MRN, ApptDt) values('00014345',2009-08-20)

  insert into dbo.Visits(MRN, ApptDt) values('00014345',2010-08-27)

  insert into dbo.Visits(MRN, ApptDt) values('00014345',2010-08-30)

  insert into dbo.Visits(MRN, ApptDt) values('00014345',2011-09-14)

  insert into dbo.Visits(MRN, ApptDt) values('00014345',2009-09-17)

  insert into dbo.Visits(MRN, ApptDt) values('00014345',2012-09-19)

  insert into dbo.Visits(MRN, ApptDt) values('00014345',2010-09-21)

  insert into dbo.Visits(MRN, ApptDt) values('00014345',2010-09-30)

  insert into dbo.Visits(MRN, ApptDt) values('00014345',2010-10-01)

  insert into dbo.Visits(MRN, ApptDt) values('00014345',2010-12-21)

  insert into dbo.Visits(MRN, ApptDt) values('00018250',2012-02-06)

  insert into dbo.Visits(MRN, ApptDt) values('00018250',2010-02-09)

  insert into dbo.Visits(MRN, ApptDt) values('00018250',2010-02-23)

  insert into dbo.Visits(MRN, ApptDt) values('00018250',2012-02-27)

  insert into dbo.Visits(MRN, ApptDt) values('00018250',2011-03-01)

  insert into dbo.Visits(MRN, ApptDt) values('00018250',2011-03-09)

  insert into dbo.Visits(MRN, ApptDt) values('00018250',2012-05-02)

  insert into dbo.Visits(MRN, ApptDt) values('00018250',2010-05-26)

  insert into dbo.Visits(MRN, ApptDt) values('00018250',2011-05-27)

  insert into dbo.Visits(MRN, ApptDt) values('00018250',2013-05-29)

  insert into dbo.Visits(MRN, ApptDt) values('00018250',2011-06-09)

  insert into dbo.Visits(MRN, ApptDt) values('00018250',2013-06-18)

  insert into dbo.Visits(MRN, ApptDt) values('00018250',2009-07-09)

  insert into dbo.Visits(MRN, ApptDt) values('00018250',2010-07-29)

  insert into dbo.Visits(MRN, ApptDt) values('00018250',2013-07-31)

  insert into dbo.Visits(MRN, ApptDt) values('00018250',2012-08-02)

  insert into dbo.Visits(MRN, ApptDt) values('00018250',2009-08-11)

  insert into dbo.Visits(MRN, ApptDt) values('00018250',2009-08-21)

  insert into dbo.Visits(MRN, ApptDt) values('00018250',2010-08-23)

  insert into dbo.Visits(MRN, ApptDt) values('00018250',2010-09-01)

  insert into dbo.Visits(MRN, ApptDt) values('00018250',2011-09-01)

  insert into dbo.Visits(MRN, ApptDt) values('00018250',2012-09-05)

  insert into dbo.Visits(MRN, ApptDt) values('00018250',2012-10-11)

  insert into dbo.Visits(MRN, ApptDt) values('00018250',2011-10-25)

  insert into dbo.Visits(MRN, ApptDt) values('00018250',2011-11-07)

  insert into dbo.Visits(MRN, ApptDt) values('00018250',2009-11-12)

  insert into dbo.Visits(MRN, ApptDt) values('00018250',2009-11-20)

  insert into dbo.Visits(MRN, ApptDt) values('00018250',2010-12-02)

  insert into dbo.Visits(MRN, ApptDt) values('00022517',2010-01-20)

  insert into dbo.Visits(MRN, ApptDt) values('00022517',2013-02-25)

  insert into dbo.Visits(MRN, ApptDt) values('00022517',2010-03-16)

  insert into dbo.Visits(MRN, ApptDt) values('00022517',2011-03-23)

  insert into dbo.Visits(MRN, ApptDt) values('00022517',2012-03-26)

  insert into dbo.Visits(MRN, ApptDt) values('00022517',2011-03-30)

  insert into dbo.Visits(MRN, ApptDt) values('00022517',2010-03-31)

  insert into dbo.Visits(MRN, ApptDt) values('00022517',2012-04-02)

  insert into dbo.Visits(MRN, ApptDt) values('00022517',2013-04-08)

  insert into dbo.Visits(MRN, ApptDt) values('00022517',2013-04-15)

  insert into dbo.Visits(MRN, ApptDt) values('00022517',2010-04-21)

  insert into dbo.Visits(MRN, ApptDt) values('00022517',2013-04-22)

  insert into dbo.Visits(MRN, ApptDt) values('00022517',2011-04-27)

  insert into dbo.Visits(MRN, ApptDt) values('00022517',2013-04-29)

  insert into dbo.Visits(MRN, ApptDt) values('00022517',2011-05-31)

  insert into dbo.Visits(MRN, ApptDt) values('00022517',2009-07-07)

  insert into dbo.Visits(MRN, ApptDt) values('00022517',2009-07-08)

  insert into dbo.Visits(MRN, ApptDt) values('00022517',2009-07-15)

  insert into dbo.Visits(MRN, ApptDt) values('00022517',2009-08-05)

  insert into dbo.Visits(MRN, ApptDt) values('00022517',2009-08-26)

  insert into dbo.Visits(MRN, ApptDt) values('00022517',2011-09-20)

  insert into dbo.Visits(MRN, ApptDt) values('00022517',2010-09-22)

  insert into dbo.Visits(MRN, ApptDt) values('00022517',2011-09-26)

  insert into dbo.Visits(MRN, ApptDt) values('00022517',2009-09-28)

  insert into dbo.Visits(MRN, ApptDt) values('00022517',2010-09-29)

  insert into dbo.Visits(MRN, ApptDt) values('00022517',2009-09-30)

  insert into dbo.Visits(MRN, ApptDt) values('00022517',2012-10-01)

  insert into dbo.Visits(MRN, ApptDt) values('00022517',2013-10-07)

  insert into dbo.Visits(MRN, ApptDt) values('00022517',2012-10-15)

  insert into dbo.Visits(MRN, ApptDt) values('00022517',2013-10-21)

  insert into dbo.Visits(MRN, ApptDt) values('00022517',2012-10-22)

  insert into dbo.Visits(MRN, ApptDt) values('00022517',2011-11-28)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2011-01-04)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2010-01-05)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2011-01-13)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2013-01-15)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2013-01-16)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2010-01-19)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2011-01-19)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2010-01-27)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2012-01-30)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2011-01-31)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2011-02-08)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2010-02-09)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2010-02-17)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2011-02-18)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2012-02-20)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2010-02-24)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2010-03-02)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2011-03-03)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2012-03-05)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2013-03-05)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2010-03-09)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2011-03-10)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2010-03-11)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2013-03-12)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2011-03-17)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2010-03-18)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2012-03-20)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2010-03-23)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2011-03-24)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2011-03-31)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2010-04-01)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2012-04-03)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2013-04-03)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2010-04-06)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2011-04-07)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2010-04-08)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2010-04-12)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2012-04-17)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2013-04-17)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2011-04-19)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2011-04-21)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2011-04-28)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2013-05-02)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2012-05-09)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2011-05-10)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2010-05-12)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2012-05-15)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2013-05-16)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2010-05-17)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2010-05-26)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2013-05-30)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2011-06-01)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2013-06-04)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2012-06-06)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2013-06-12)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2010-06-14)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2011-06-15)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2012-06-15)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2010-06-19)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2011-06-22)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2012-06-25)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2010-06-29)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2011-06-29)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2010-07-06)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2011-07-06)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2012-07-09)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2013-07-10)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2009-07-13)

  insert into dbo.Visits(MRN, ApptDt) values('00024106',2010-07-13)

• mickyT

  SSChampion

  Points: 10360

  More actions

  October 22, 2013 at 1:52 pm

  #1660688

  Hi

  I put quotes around your dates, otherwise the dates are messed up.

  I also noticed an issue with the query I provided and have updated it so that it now only includes active patients.

  declare @reportyear date = '20120101';

  with ActivePatients as (

  SELECT MRN

  FROM dbo.Visits

  WHERE ApptDt > DATEADD(year,-1,@reportyear) and ApptDt < @reportyear

  GROUP BY MRN

  )

  SELECT DATEPART(month, ApptDt) [Month], DATEPART(year, ApptDt) [Year], COUNT(distinct v.MRN) Patients

  FROM dbo.Visits v

  INNER JOIN ActivePatients ap ON v.MRN = ap.MRN

  WHERE ApptDt >= @reportyear and ApptDt < DATEADD(year,1,@reportyear)

  group by DATEPART(month, ApptDt), DATEPART(year, ApptDt)

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  October 22, 2013 at 2:37 pm

  #1660718

  How would you even calculate the average of active patients? You could count them, but an average needs something from which we could calculate it. Average by doctor? by month? by quarter?

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• mickyT

  SSChampion

  Points: 10360

  More actions

  October 22, 2013 at 3:36 pm

  #1660738

  Luis Cazares (10/22/2013)

  ---

  How would you even calculate the average of active patients? You could count them, but an average needs something from which we could calculate it. Average by doctor? by month? by quarter?

  Forgot about the average part of the question :w00t:

  I suppose you could do an average monthly patient count for the report period. This could then be used to calculate a deviation from that average.

  declare @reportyear date = '20120101';

  with ActivePatients as (

  SELECT MRN

  FROM #Visits

  WHERE ApptDt > DATEADD(year,-1,@reportyear) and ApptDt < @reportyear

  GROUP BY MRN

  ),

  MonthlyCounts as (

  SELECT DATEPART(month, ApptDt) [Month], DATEPART(year, ApptDt) [Year], COUNT(distinct v.MRN) UniquePatients, COUNT(v.MRN) Patients

  FROM #Visits v

  INNER JOIN ActivePatients ap ON v.MRN = ap.MRN

  WHERE ApptDt >= @reportyear and ApptDt < DATEADD(year,1,@reportyear)

  group by DATEPART(month, ApptDt), DATEPART(year, ApptDt)

  )

  SELECT [Month], [Year],

  UniquePatients,

  AVG(UniquePatients + 0.0) OVER () MonthlyAverageForYear,

  UniquePatients - AVG(UniquePatients + 0.0) OVER () Deviation,

  Patients,

  AVG(Patients + 0.0) OVER () MonthlyAverageForYear,

  Patients - AVG(Patients + 0.0) OVER () Deviation

  FROM MonthlyCounts;

• NineIron

  SSChampion

  Points: 12529

  More actions

  October 23, 2013 at 5:19 am

  #1660856

  You find the number of active patients per month then, average those months. Remember, the number of active patients changes each month due to deaths, new patients, patients move out of the area, etc.

• NineIron

  SSChampion

  Points: 12529

  More actions

  October 23, 2013 at 5:22 am

  #1660858

  Could you tell me why I am getting the following errors?

  Msg 139, Level 15, State 1, Line 0

  Cannot assign a default value to a local variable.

  Msg 137, Level 15, State 2, Line 6

  Must declare the scalar variable "@reportyear".

  Msg 137, Level 15, State 2, Line 12

  Must declare the scalar variable "@reportyear".

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  October 23, 2013 at 5:33 am

  #1660863

  NineIron (10/23/2013)

  ---

  Could you tell me why I am getting the following errors?

  Msg 139, Level 15, State 1, Line 0

  Cannot assign a default value to a local variable.

  Msg 137, Level 15, State 2, Line 6

  Must declare the scalar variable "@reportyear".

  Msg 137, Level 15, State 2, Line 12

  Must declare the scalar variable "@reportyear".

  Because you're using SQL Server 2005 or compatibility 90.

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• NineIron

  SSChampion

  Points: 12529

  More actions

  October 23, 2013 at 6:07 am

  #1660873

  I'm using 2008. How do I change the compatibility? What do I change it to?

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  October 23, 2013 at 6:13 am

  #1660877

  NineIron (10/23/2013)

  ---

  I'm using 2008. How do I change the compatibility? What do I change it to?

  SELECT compatibility_level, name

  FROM sys.databases;

  Execute the above. The database that you're working on will have a compatibility level of less than 100 (probably 90).

  To change it: -

  ALTER DATABASE YourDatabaseNameHere

  SET COMPATIBILITY_LEVEL = 100;

  The other way to fix the script, is to change it to use SQL Server 2005 syntax: -

  DECLARE @reportyear DATETIME;

  SET @reportyear = '20120101';

  WITH ActivePatients AS

  (

  SELECT MRN

  FROM #Visits

  WHERE ApptDt > DATEADD(year, - 1, @reportyear)

  AND ApptDt < @reportyear

  GROUP BY MRN

  ),

  MonthlyCounts AS

  (

  SELECT DATEPART(month, ApptDt) [Month], DATEPART(year, ApptDt) [Year], COUNT(DISTINCT v.MRN) UniquePatients, COUNT(v.MRN) Patients

  FROM #Visits v

  INNER JOIN ActivePatients ap ON v.MRN = ap.MRN

  WHERE ApptDt >= @reportyear

  AND ApptDt < DATEADD(year, 1, @reportyear)

  GROUP BY DATEPART(month, ApptDt), DATEPART(year, ApptDt)

  )

  SELECT

  [Month], [Year], UniquePatients,

  AVG(UniquePatients + 0.0) OVER () MonthlyAverageForYear,

  UniquePatients - AVG(UniquePatients + 0.0) OVER () Deviation,

  Patients, AVG(Patients + 0.0) OVER () MonthlyAverageForYear,

  Patients - AVG(Patients + 0.0) OVER () Deviation

  FROM MonthlyCounts;

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• NineIron

  SSChampion

  Points: 12529

  More actions

  October 23, 2013 at 6:26 am

  #1660887

  Thanx. I'll do the latter.