Need T-SQL optimization Help

• vignesh.ms

  Hall of Fame

  Points: 3456

  More actions

  January 4, 2014 at 2:32 am

  #284096

  Hi There,

  Following is the sample data ,

  DECLARE @maxval int, @minval int

  select @maxval=201301,@minval=201312

  SELECT top 100001 CAST(((@maxval + 1) - @minval) *

  RAND(CHECKSUM(NEWID())) + @minval AS int)Date ,ceiling (RAND(CHECKSUM(NEWID())) *1000+25692*RAND(CHECKSUM(NEWID())) )*ceiling (RAND(CHECKSUM(NEWID())) *1000+25692*RAND(CHECKSUM(NEWID())) )ID

  into #sample from sysobjects , syscolumns

  Problem :

  * there are 2 columns date & id

  * take first month id's , say for example 201302

  * need to find whether those id's are available or not, in the next 3 months. so here 201303,201304,201305

  * need to repeat this for all months

  Here is the my code, It requires more manual intervention and time taking. Please assist.

  select distinct date from #sample order by DATE

  declare @processingdate varchar(6),@from varchar(6),@to varchar(6)

  set @processingdate = '201302'

  set @from = '201303'

  set @to = '201305'

  select distinct id into #Source from #sample where DATE = @processingdate

  select distinct id into #lookup from #sample where DATE between @from and @to

  select @processingdate,count(*) from #Source where ID not in (select * from #lookup)

  Thanks

• Jeff Moden

  SSC Guru

  Points: 1003863

  More actions

  January 4, 2014 at 12:47 pm

  #1677549

  To be honest, whomever designed the table with an integer representation of year and month did a great disservice to anyone and everyone using the table and it's going to be a real bugger when the year roles over. Are you allowed to change the datatype of that column?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• j.miner

  SSCrazy

  Points: 2264

  More actions

  January 4, 2014 at 7:39 pm

  #1677568

  I agree with Jeff that a date should be stored as a date in the source system (OLTP). I would try to change it. However, I know how hard it is sometimes to get buy in at the Business Level.

  I have work on a few data warehouses (OLAP) in my time. One part of a star schema is a date dimension. Usually some number is given to a date. That number is used throughout the tables.

  How about a date decode table that translates the 201301 (a number stored as a char(6)) to '2013-01-01' as a date. Thus, adding one more join to the equation allows you to work with dates.

  I would check Aaron's article on handling dates.

  http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx

  I would keep away from BETWEEN.

  Good luck ...

  John Miner
  Crafty DBA
  www.craftydba.com

• vignesh.ms

  Hall of Fame

  Points: 3456

  More actions

  January 6, 2014 at 1:50 am

  #1677667

  Jeff Moden (1/4/2014)

  ---

  To be honest, whomever designed the table with an integer representation of year and month did a great disservice to anyone and everyone using the table and it's going to be a real bugger when the year roles over. Are you allowed to change the datatype of that column?

  Yes I can change the datatype ... Its complex bet one time activity..

  so I could ...