Need help with the sql code

• winnielxx

  Newbie

  Points: 2

  More actions

  July 17, 2015 at 10:51 am

  #319936

  Hi,

  I have two tables:

  table a:

  date ID

  ...

  2012-09-21 1

  2012-09-28 1

  2012-10-05 1

  2012-10-12 1

  2012-10-19 1

  2012-10-26 1

  2012-11-02 1

  2012-11-09 1

  ...

  2013-01-18 1

  2013-01-25 1

  2013-02-01 1

  ...

  table b:

  date ID eps

  ...

  2012-03-31 1 1.3

  2012-06-30 1 0.8

  2012-09-30 1 0.5

  2012-12-31 1 2.5

  ...

  how to merge the two tables to make it like this:

  date ID eps

  ...

  2012-09-21 1 0.8

  2012-09-28 1 0.8

  2012-10-05 1 0.5

  2012-10-12 1 0.5

  2012-10-19 1 0.5

  2012-10-26 1 0.5

  2012-11-02 1 0.5

  2012-11-09 1 0.5

  ...

  2013-01-18 1 2.5

  2013-01-25 1 2.5

  2013-02-01 1 2.5

  ...

  In other words, I want to populate the quarterly eps into weekly frequency. Any help is appreciated. Thank you.

  Best,

  Xixi Lin

• Lowell

  SSC Guru

  Points: 323507

  More actions

  July 17, 2015 at 11:18 am

  #1813380

  adding quick consumable info for others to run with the ball:

  ;WITH TableA([date],[ID])

  AS

  (

  SELECT '2012-09-21','1' UNION ALL

  SELECT '2012-09-28','1' UNION ALL

  SELECT '2012-10-05','1' UNION ALL

  SELECT '2012-10-12','1' UNION ALL

  SELECT '2012-10-19','1' UNION ALL

  SELECT '2012-10-26','1' UNION ALL

  SELECT '2012-11-02','1' UNION ALL

  SELECT '2012-11-09','1' UNION ALL

  SELECT '2013-01-18','1' UNION ALL

  SELECT '2013-01-25','1' UNION ALL

  SELECT '2013-02-01','1'

  ),

  TableB([date],[ID],[eps])

  AS

  (

  SELECT '2012-03-31','1','1.3' UNION ALL

  SELECT '2012-06-30','1','0.8' UNION ALL

  SELECT '2012-09-30','1','0.5' UNION ALL

  SELECT '2012-12-31','1','2.5'

  )

  SELECT * FROM TableA

  INNER JOIN TableB

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  July 17, 2015 at 11:34 am

  #1813384

  Something like this? (I dropped the leading ; from the CTE as it is not needed.)

  WITH TableA([date],[ID])

  AS

  (

  SELECT '2012-09-21','1' UNION ALL

  SELECT '2012-09-28','1' UNION ALL

  SELECT '2012-10-05','1' UNION ALL

  SELECT '2012-10-12','1' UNION ALL

  SELECT '2012-10-19','1' UNION ALL

  SELECT '2012-10-26','1' UNION ALL

  SELECT '2012-11-02','1' UNION ALL

  SELECT '2012-11-09','1' UNION ALL

  SELECT '2013-01-18','1' UNION ALL

  SELECT '2013-01-25','1' UNION ALL

  SELECT '2013-02-01','1'

  ),

  TableB([date],[ID],[eps])

  AS

  (

  SELECT '2012-03-31','1','1.3' UNION ALL

  SELECT '2012-06-30','1','0.8' UNION ALL

  SELECT '2012-09-30','1','0.5' UNION ALL

  SELECT '2012-12-31','1','2.5'

  )

  select

  *

  from

  TableA a

  cross apply (select top (1) * from TableB b where a.ID = b.ID and b.[date] <= a.[date] order by b.[date] desc)ca;

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  July 17, 2015 at 11:45 am

  #1813386

  If you're using natural quarters, you might be able to use something like this:

  WITH TableA([date],[ID])

  AS

  (

  SELECT '2012-09-21','1' UNION ALL

  SELECT '2012-09-28','1' UNION ALL

  SELECT '2012-10-05','1' UNION ALL

  SELECT '2012-10-12','1' UNION ALL

  SELECT '2012-10-19','1' UNION ALL

  SELECT '2012-10-26','1' UNION ALL

  SELECT '2012-11-02','1' UNION ALL

  SELECT '2012-11-09','1' UNION ALL

  SELECT '2013-01-18','1' UNION ALL

  SELECT '2013-01-25','1' UNION ALL

  SELECT '2013-02-01','1'

  ),

  TableB([date],[ID],[eps])

  AS

  (

  SELECT '2012-03-31','1','1.3' UNION ALL

  SELECT '2012-06-30','1','0.8' UNION ALL

  SELECT '2012-09-30','1','0.5' UNION ALL

  SELECT '2012-12-31','1','2.5'

  )

  SELECT a.date,

  a.ID,

  b.eps

  FROM TableA a

  INNER JOIN TableB b ON a.ID = b.ID

  AND a.date >= b.date

  AND a.date < DATEADD( DD, - DATEPART( dd, DATEADD( MM, 4, b.date)), DATEADD( MM, 4, b.date))

  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

• winnielxx

  Newbie

  Points: 2

  More actions

  July 17, 2015 at 12:10 pm

  #1813391

  Thank you for all of your help. I will try it.

  Thank you.