Solved

Dynamic SQL

Posted on 2013-01-16
4
186 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

752 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