Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Dynamic SQL

Posted on 2013-01-16
4
Medium Priority
?
190 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 23

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 23

Accepted Solution

by:
Steve Wales earned 2000 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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

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 article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

886 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