Solved

Dynamic SQL

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

815 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now