Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Dynamic SQL

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
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…

670 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