Solved

Dynamic SQL

Posted on 2013-01-16
4
187 Views
Last Modified: 2013-01-21
I'm looking to change a call date from a static subquery like..

where a.CALL_DATE between '12/1/2012' and '12/31/2012' 

Open in new window


to Dynamic SQL so that it pulls dates between the first and last day of the previous month.

Can anyone give me an idea to do this?
0
Comment
Question by:sonofstimpy1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38784665
This works if your call_Date is always just the date and does not care about the time.

declare @start datetime
declare @end datetime
declare @end2 datetime
declare @today datetime
declare @sql nvarchar(4000)

SELECT @Today = dateadd(dd, datediff(dd, 0, getdate())+0, 0)     -- Today's Date, midnight past
SELECT @Today = DATEADD(month, -1, @Today);                      -- One Month back
SELECT @start = DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,0,@Today))  -- First of last month
SELECT @end = DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,1,@Today))      -- Last of last month
SELECT @end2 = DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,1,@Today))  -- First of this month

set @sql = 'select mydate from mytable where mydate between '''+convert(char(8), @start, 112)+''' and '''+convert(char(8), @end, 112)+''''
exec (@sql)

Open in new window


If you have time as well, use @end2 (which is the first of this month) and instead of between construct a where clause that's

>= @start and < @end2
0
 

Author Comment

by:sonofstimpy1
ID: 38787065
How would I fit this into this example of code I already have?
Noting line 5 is the part I need to make dynamic...

select c.TAX_ID as "TAXID",  date( rtrim( char(year (current_date - 1 month)))||'-'||rtrim(char(month (current_date - 1 month)))||'-01') as "RUN_DT", 320 as "TYP", Count(*) as "cnt"
from 
(select a.SERVER_CD, a.PORT_NUMBER, a.CALL_DATE, a.CALL_START_TIME, b.TAX_ID, b.MODULE_NAME
   from hum.a_vrhcal a, hum.a_vrhact b
   where a.CALL_DATE between  '12/1/2012' and '12/31/2012'
    and a.CALLER_TYPE_IND = 'P'
    and not a.server_cd like 'D%'
    and not a.server_cd = 'PR'
    and not b.MODULE_NAME = 'GREETING'
    and not b.MODULE_NAME like  '%ELIGIBILITY%'
    and not b.MODULE_NAME like '%CLAIM%'
    and not b.MODULE_NAME like '%PRECERTIFICATION%'
    and not b.MODULE_NAME like '%REFERRALS%'
    and not b.MODULE_NAME IN ('MAIN MENU','AALTDRUGLST','AAPID','ACOMEM','ACONFDOB','ACONFDRUGNAME','ACONFMID','ACPID','ADOBP','ADOBPT','ADRUGNAME','AENDM','AFAXYESNO','AGETDOSAGE','AGETFAX','AHEARAGAIN','ALTDRUGTRANS','AMID','ANEEDSMET','ANEWREQHWSE','ASPECNOMID','ASTATORREQ','AVERFAX','DRUGDETAILTRANS','DrugPricing_Reason','DRUGPRICINGREASON','DRUGPRICINGTRANS','EESDEDUCT','EESFAMILY','FAXFORMAVAILTRANS','FAXSENDTRANS','LISTDRUGREPLY','RXArgusElig','VRAUTH','VRDAUTH','VRMAUTH','VRPAUTH')

    and not a.DNIS = 3055
    and a.SERVER_CD = b.SERVER_CD
    and a.PORT_NUMBER = b.PORT_NUMBER
    and a.CALL_DATE  = b.CALL_DATE
    and a.CALL_START_TIME = b.CALL_START_TIME
group by a.SERVER_CD, a.PORT_NUMBER, a.CALL_DATE, a.CALL_START_TIME, b.TAX_ID, b.MODULE_NAME) as c
group by TAX_ID,  date( rtrim( char(year (current_date - 1 month)))||'-'||rtrim(char(month (current_date - 1 month)))||'-01'), MODULE_NAME;

Open in new window

0
 
LVL 22

Accepted Solution

by:
Steve Wales earned 500 total points
ID: 38787568
I believe this is pretty close (I don't have your data structures to test it on).   I replaced exec (@sql) with print @sql and it looked pretty close to what is needed without actually running it.

declare @start datetime
declare @end datetime
declare @end2 datetime
declare @today datetime
declare @sql nvarchar(4000)
 
SELECT @Today = dateadd(dd, datediff(dd, 0, getdate())+0, 0)     -- Today's Date, midnight past
SELECT @Today = DATEADD(month, -1, @Today);                      -- One Month back
SELECT @start = DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,0,@Today))  -- First of last month
SELECT @end = DATEADD(dd, -DAY(DATEADD(m,1,@Today)), DATEADD(m,1,@Today))      -- Last of last month
SELECT @end2 = DATEADD(dd,-(DAY(DATEADD(mm,1,@Today))-1),DATEADD(mm,1,@Today))  -- First of this month
 
set @sql = 
'select c.TAX_ID as "TAXID",  date( rtrim( char(year (current_date - 1 month)))||''-''||rtrim(char(month (current_date - 1 month)))||''-01'') as "RUN_DT", 320 as "TYP", Count(*) as "cnt" ' +
'from ' +
'(select a.SERVER_CD, a.PORT_NUMBER, a.CALL_DATE, a.CALL_START_TIME, b.TAX_ID, b.MODULE_NAME ' +
   'from hum.a_vrhcal a, hum.a_vrhact b ' +
   'where a.CALL_DATE between '''+convert(char(8), @start, 112)+''' and '''+convert(char(8), @end, 112)+''' ' +
    'and a.CALLER_TYPE_IND = ''P'' ' +
    'and not a.server_cd like ''D%'' ' +
    'and not a.server_cd = ''PR'' ' +
    'and not b.MODULE_NAME = ''GREETING'' ' +
    'and not b.MODULE_NAME like  ''%ELIGIBILITY%'' ' +
    'and not b.MODULE_NAME like ''%CLAIM%'' ' +
    'and not b.MODULE_NAME like ''%PRECERTIFICATION%'' ' +
    'and not b.MODULE_NAME like ''%REFERRALS%'' ' +
    'and not b.MODULE_NAME IN (''MAIN MENU'',''AALTDRUGLST'',''AAPID'',''ACOMEM'',''ACONFDOB'',''ACONFDRUGNAME'',''ACONFMID'',''ACPID'',''ADOBP'',''ADOBPT'',''ADRUGNAME'',''AENDM'',''AFAXYESNO'',''AGETDOSAGE'',''AGETFAX'',''AHEARAGAIN'',''ALTDRUGTRANS'',''AMID'',''ANEEDSMET'',''ANEWREQHWSE'',''ASPECNOMID'',''ASTATORREQ'',''AVERFAX'',''DRUGDETAILTRANS'',''DrugPricing_Reason'',''DRUGPRICINGREASON'',''DRUGPRICINGTRANS'',''EESDEDUCT'',''EESFAMILY'',''FAXFORMAVAILTRANS'',''FAXSENDTRANS'',''LISTDRUGREPLY'',''RXArgusElig'',''VRAUTH'',''VRDAUTH'',''VRMAUTH'',''VRPAUTH'') ' +
    'and not a.DNIS = 3055 ' +
    'and a.SERVER_CD = b.SERVER_CD ' +
    'and a.PORT_NUMBER = b.PORT_NUMBER ' +
    'and a.CALL_DATE  = b.CALL_DATE ' +
    'and a.CALL_START_TIME = b.CALL_START_TIME ' +
'group by a.SERVER_CD, a.PORT_NUMBER, a.CALL_DATE, a.CALL_START_TIME, b.TAX_ID, b.MODULE_NAME) as c ' +
'group by TAX_ID,  date( rtrim( char(year (current_date - 1 month)))||''-''||rtrim(char(month (current_date - 1 month)))||''-01''), MODULE_NAME;'
 
exec (@sql)

Open in new window


As mentioned in the original reply, depending upon how your dates are stored you may need to tweak it just a little in order to account for times, if they are included in your date column too.
0
 

Author Closing Comment

by:sonofstimpy1
ID: 38800962
Great work sjwales!
0

Featured Post

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

635 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question