Set-based query

• David Burrows

  SSC Guru

  Points: 65143

  More actions

  January 24, 2006 at 7:31 am

  #616660

  Using a temp table, not sure if results are right

  CREATE TABLE #temp (period int, rfreq int, pfreq int, startdate datetime PRIMARY KEY CLUSTERED (period,rfreq,pfreq))

  INSERT INTO #temp (period, rfreq, pfreq, startdate)

  SELECT n.n, m.rfreq, m.pfreq, y.startdate

  FROM tblMain m

  INNER JOIN tblMaxPeriods p ON m.rfreq = p.rfreq

  INNER JOIN tblNumbers n ON n.n <= p.MaxPeriod

  INNER JOIN tblMain y ON y.period = (SELECT MAX(a.period) AS [period] FROM tblMain a WHERE a.period < n.n) AND y.rfreq = m.rfreq AND y.pfreq = m.pfreq

  LEFT OUTER JOIN tblMain x ON x.period = n.n

  WHERE x.period IS NULL

  ORDER BY n.n, m.rfreq, m.pfreq

  SELECT t.period, t.rfreq, t.pfreq

  ,DATEADD(mm, SUM(a.rfreq), MIN(t.StartDate))

  ,SUM(a.rfreq)

  FROM #temp t

  INNER JOIN (SELECT period,rfreq,pfreq FROM tblMain UNION SELECT period,rfreq ,pfreq FROM #temp) a

  ON a.period < t.period AND a.rfreq = t.rfreq AND a.pfreq = t.pfreq

  GROUP BY t.period, t.rfreq, t.pfreq

  ORDER BY t.period, t.rfreq, t.pfreq

  DROP TABLE #temp

  Far away is close at hand in the images of elsewhere.
  Anon.

• SWELL

  SSC Enthusiast

  Points: 114

  More actions

  January 25, 2006 at 3:37 am

  #616853

  David

  Brilliant - Looks to be doing exactly what I need but it's gonna take me a while to work out what's going on !!

  Thx again and to everyone else for your help and what I'm learning from this post and the site generally