July 13, 2015 at 4:04 pm
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.
July 14, 2015 at 1:52 am
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
July 14, 2015 at 2:30 am
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
July 14, 2015 at 7:33 am
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply