Instead of Temp Table

• evaleah

  Ten Centuries

  Points: 1372

  More actions

  June 8, 2008 at 2:22 pm

  #96037

  I have created the following:

  select

  MonthN = datename(month,v.SchedBegin),

  NC = NULL,

  Pats = count(distinct v.VisitID)

  INTO #Pats

  from

  tb_Visit v

  where datepart(year, v.SchedBegin) = @year

  group by

  datename(month,v.SchedBegin)

  update #Pats set NC =

  (select sum(nc.NewCount) from tb_NewPatientCount nc

  where datename(month, nc.CountDate) = MonthN

  and datepart(year, nc.CountDate) = @year)

  select * from #Pats

  drop table #Pats

  Does anyone have an idea of how to do this without using a temp table?

  Thanks!

• Ken Simmons

  SSCertifiable

  Points: 7822

  More actions

  June 8, 2008 at 4:14 pm

  #825919

  I think this should do what you want, but I did not test it.

  SELECT datename(month,v.SchedBegin) MonthN,

  SUM(B.NC) NC,

  count(distinct v.VisitID) Pats

  from

  tb_Visit v JOIN

  (select nc.NewCount NC,

  datename(month, nc.CountDate) MonthN

  from tb_NewPatientCount nc

  where datepart(year, nc.CountDate) = @year) B

  ON datename(month,v.SchedBegin) = B.MonthN

  where datepart(year, v.SchedBegin) = @year

  group by

  datename(month,v.SchedBegin)