Eliminating repetitive coding.

• sql_novice_2007

  Hall of Fame

  Points: 3307

  More actions

  July 13, 2015 at 4:04 pm

  #309812

  Hi,

  I have the following code.

  INSERT INTO #SummaryResults

  (ColumnID

  ,ColumnName

  ,SentDate

  ,DataResult)

  SELECT

  4

  ,13

  ,1

  ,CONVERT(VARCHAR,COUNT(*))

  FROM

  #EmailSent

  WHERE

  EmailSentID = @EmailSentID

  AND EmailSentDateTimeBegin BETWEEN @SentDate2BeginNew AND @SentDate1EndNew

  INSERT INTO #SummaryResults

  (ColumnID

  ,ColumnName

  ,SentDate

  ,DataResult)

  SELECT

  4

  ,13

  ,2

  ,CONVERT(VARCHAR,COUNT(*))

  FROM

  #EmailSent

  WHERE

  EmailSentID = @EmailSentID

  AND EmailSentDateTimeBegin BETWEEN @SentDate3BeginNew AND @SentDate1EndNew

  INSERT INTO #SummaryResults

  (ColumnID

  ,ColumnName

  ,SentDate

  ,DataResult)

  SELECT

  4

  ,13

  ,3

  ,CONVERT(VARCHAR,COUNT(*))

  FROM

  #EmailSent

  WHERE

  EmailSentID = @EmailSentID

  AND EmailSentDateTimeBegin BETWEEN @SentDate4BeginNew AND @SentDate1EndNew

  INSERT INTO #SummaryResults

  (ColumnID

  ,ColumnName

  ,SentDate

  ,DataResult)

  SELECT

  4

  ,13

  ,4

  ,CONVERT(VARCHAR,COUNT(*))

  FROM

  #EmailSent

  WHERE

  EmailSentID = @EmailSentID

  AND EmailSentDateTimeBegin BETWEEN @SentDate5BeginNew AND @SentDate1EndNew

  INSERT INTO #SummaryResults

  (ColumnID

  ,ColumnName

  ,SentDate

  ,DataResult)

  SELECT

  4

  ,13

  ,5

  ,CONVERT(VARCHAR,COUNT(*))

  FROM

  #EmailSent

  WHERE

  EmailSentID = @EmailSentID

  AND EmailSentDateTimeBegin BETWEEN @SentDate6BeginNew AND @SentDate1EndNew

  I have daterangeBegin from @SentDate1BeginNew to @SentDate12BeginNew. For this I have to write 11 select statements and code looks very repetitive.

  Is there a way I can use dynamic SQL or any other better way of coding this to avoid repetitive coding.

  Thanks in advance.

• SQLCJ

  SSCommitted

  Points: 1791

  More actions

  July 14, 2015 at 1:52 am

  #1812216

  Just out of curiosity.. Can't we have OR statements for date ranges after last AND clause within parenthesis () to overcome the problem ?

  If not then may be store the date ranges in some temp table and loop over it

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  July 14, 2015 at 2:30 am

  #1812225

  Something like this? (Not tested because no table DDL or sample data.)

  CREATE TABLE #BeginDates (

  DateNo tinyint

  SentDate datetime

  )

  INSERT INTO #BeginDates VALUES

  (1, @SentDate2BeginNew)

  ,(2, @SentDate3BeginNew)

  ,(3, @SentDate4BeginNew)

  ,(4, @SentDate5BeginNew)

  ,(5, @SentDate6BeginNew)

  INSERT INTO #SummaryResults (

  ColumnID

  ,ColumnName

  ,SentDate

  ,DataResult

  )

  SELECT

  4

  ,13

  ,b.DateNo

  ,CONVERT(VARCHAR,COUNT(*))

  FROM #BeginDates b

  JOIN #EmailSent e

  ON e.EmailSentDateTimeBegin BETWEEN b.SentDate AND @SentDate1EndNew

  John

• Brandie Tarvin

  SSC Guru

  Points: 173061

  More actions

  July 14, 2015 at 7:33 am

  #1812314

  You have several options.

  To eliminate the separate INSERT clauses, you could just use a UNION ALL like below:

  INSERT INTO #SummaryResults

  (ColumnID

  ,ColumnName

  ,SentDate

  ,DataResult)

  SELECT

  4

  ,13

  ,1

  ,CONVERT(VARCHAR,COUNT(*))

  FROM

  #EmailSent

  WHERE

  EmailSentID = @EmailSentID

  AND EmailSentDateTimeBegin BETWEEN @SentDate2BeginNew AND @SentDate1EndNew

  UNION ALL

  SELECT

  4

  ,13

  ,2

  ,CONVERT(VARCHAR,COUNT(*))

  FROM

  #EmailSent

  WHERE

  EmailSentID = @EmailSentID

  AND EmailSentDateTimeBegin BETWEEN @SentDate3BeginNew AND @SentDate1EndNew

  UNION ALL

  SELECT

  4

  ,13

  ,3

  ,CONVERT(VARCHAR,COUNT(*))

  FROM

  #EmailSent

  WHERE

  EmailSentID = @EmailSentID

  AND EmailSentDateTimeBegin BETWEEN @SentDate4BeginNew AND @SentDate1EndNew

  UNION ALL

  SELECT

  4

  ,13

  ,4

  ,CONVERT(VARCHAR,COUNT(*))

  FROM

  #EmailSent

  WHERE

  EmailSentID = @EmailSentID

  AND EmailSentDateTimeBegin BETWEEN @SentDate5BeginNew AND @SentDate1EndNew

  UNION ALL

  SELECT

  4

  ,13

  ,5

  ,CONVERT(VARCHAR,COUNT(*))

  FROM

  #EmailSent

  WHERE

  EmailSentID = @EmailSentID

  AND EmailSentDateTimeBegin BETWEEN @SentDate6BeginNew AND @SentDate1EndNew

  To eliminate the rest of the redundant code, you could use this:

  INSERT INTO #SummaryResults

  (ColumnID

  ,ColumnName

  ,SentDate

  ,DataResult)

  SELECT

  4

  ,13

  , CASE XCondition WHEN 1 THEN 1

  WHEN 2 THEN 2

  WHEN 3 THEN 3

  WHEN 4 THEN 4

  WHEN 5 THEN 5 END

  ,CONVERT(VARCHAR,COUNT(*))

  FROM

  #EmailSent

  WHERE

  EmailSentID = @EmailSentID

  AND

  (

  (EmailSentDateTimeBegin BETWEEN @SentDate2BeginNew AND @SentDate1EndNew)

  OR (EmailSentDateTimeBegin BETWEEN @SentDate3BeginNew AND @SentDate1EndNew)

  OR (EmailSentDateTimeBegin BETWEEN @SentDate4BeginNew AND @SentDate1EndNew)

  OR (EmailSentDateTimeBegin BETWEEN @SentDate5BeginNew AND @SentDate1EndNew)

  OR (EmailSentDateTimeBegin BETWEEN @SentDate6BeginNew AND @SentDate1EndNew)

  )

  Please note the CASE statement I inserted in your SELECT clause. I don't know what Xcondition is. Whatever you are using to determine the value of this column (it changes through your code) is what you should use for Xcondition.

  Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.