Solved

sql code to pull back data from 7 days prior

Posted on 2011-02-15
5
397 Views
Last Modified: 2012-05-11
I have a query that pulls data from several day ago, but I have to enter date manualy,
I'd like to have it just pull the data 7 days removed from the current day. see code below.

DECLARE @EvalDate AS CHAR(10)                                                                                    
SET @EvalDate = '2011-01-31'                                                                                    
                                                                                    
SELECT                                                                                    
      DISTINCT                                                                              
      --dbo.ProperCase(a.LegalName)            AS 'BrokerGroup',                                                                  
      --ao.AlternateId                        AS 'BrokerOfficeId',                                                      
      --dbo.ProperCase(ao.FormalName)            AS 'BrokerOffice',                                                                  
      --aos.Status                        AS 'BrokerOfficeStatus',                                                      
      'BUS'                                                                        AS 'Source - Custom Field 1',      
      @EvalDate                                                                  AS 'Type',            
      dbo.ProperCase(RTRIM(LTRIM(ae.FirstName)))                  AS 'FirstName',                                                            
      dbo.ProperCase(RTRIM(LTRIM(ae.LastName)))                  AS 'LastName',                                                            
      ISNULL(RTRIM(LTRIM(aeemail.EmailAddress)), ' ')            AS 'EmailAddress',                                                                  
      CAST(VELAREPORTING.dbo.FormatDateTime(ae.CreateDate, 'YYYY-MM-DD') AS DATETIME)                                                                              
                                                                                    AS 'BUSBrokerCreateDate - Do Not Import',
      CAST(VELAREPORTING.dbo.FormatDateTime(ae.UpdateDate, 'YYYY-MM-DD') AS DATETIME)                                                                              
                                                                                    AS 'BUSBrokerUpdateDate - Custom Field 2',
      ae.JobTitle                                                                  AS 'BUSBrokerJobTitle',            
      ao.FormalName                                                            AS 'BUSBrokerOffice',                  
      binfo.PhoneNumber                                                      AS 'BUSBrokerOfficePhone',                        
      ' '                                                                              AS 'BUSBrokerHomePhone',
      binfo.Street                                                            AS 'BUSBrokerOfficeStreet',                  
      binfo.Suite                                                                  AS 'BUSBrokerOfficeSuite',            
      binfo.City                                                                  AS 'BUSBrokerOfficeCity',            
      binfo.StateAbbreviation                                                AS 'BUSBrokerOfficeState',                              
      binfo.PostalCode                                                      AS 'BUSBrokerOfficeZIP',                        
      CASE                                                                              
            WHEN ISNULL( agcyemplrol0.Role, ' ' ) > ' ' AND ISNULL( agcyemplrol1.Role, ' ' ) > ' ' AND ISNULL( agcyemplrol2.Role, ' ' ) > ' '                                                                         
                  THEN ISNULL( agcyemplrol0.Role, ' ' ) +       ', ' + ISNULL( agcyemplrol1.Role, ' ' ) + ', ' + REPLACE( ISNULL( agcyemplrol2.Role, ' ' ), 'Other', 'Billing Contact' )                                                            
            WHEN ISNULL( agcyemplrol0.Role, ' ' ) > ' ' AND ISNULL( agcyemplrol1.Role, ' ' ) > ' '                                                                         
                  THEN ISNULL( agcyemplrol0.Role, ' ' ) +       ', ' + ISNULL( agcyemplrol1.Role, ' ' )                                                            
            WHEN ISNULL( agcyemplrol0.Role, ' ' ) > ' ' AND ISNULL( agcyemplrol2.Role, ' ' ) > ' '                                                                         
                  THEN ISNULL( agcyemplrol0.Role, ' ' ) + REPLACE( ISNULL( agcyemplrol2.Role, ' ' ), 'Other', 'Billing Contact' )                                                                  
            WHEN ISNULL( agcyemplrol1.Role, ' ' ) > ' ' AND ISNULL( agcyemplrol2.Role, ' ' ) > ' '                                                                         
                  THEN ISNULL( agcyemplrol1.Role, ' ' ) + ', ' + REPLACE( ISNULL( agcyemplrol2.Role, ' ' ), 'Other', 'Billing Contact' )                                                                  
            WHEN ISNULL( agcyemplrol0.Role, ' ' ) > ' '                                                                         
                  THEN ISNULL( agcyemplrol0.Role, ' ' )                                                                  
            WHEN ISNULL( agcyemplrol1.Role, ' ' ) > ' '                                                                         
                  THEN ISNULL( agcyemplrol1.Role, ' ' )                                                                  
            WHEN ISNULL( agcyemplrol2.Role, ' ' ) > ' '                                                                         
                  THEN REPLACE( ISNULL( agcyemplrol2.Role, ' ' ), 'Other', 'Billing Contact' )                                                                  
            ELSE ' '                                                                        
      END                                                                              AS 'BUSBrokerRole1 - Custom Field 4',
      UPPER(ao.AlternateId)                                                AS 'BUSBrokerRole1 - Custom Field 4'                              
                                                                                    
FROM                                                                                    
      VELA_BUS2.dbo.Organization_Agency a                                                                              
                                                                                    
      INNER JOIN VELA_BUS2.dbo.Organization_AgencyOffice ao                                                                              
ON      a.AgencyId                                    = ao.AgencyId                                          
                                                                                    
      INNER JOIN VELA_BUS2.dbo.Organization_AgencyOfficeStatus aos                                                                              
ON      ao.AgencyOfficeStatusId                  = aos.AgencyOfficeStatusId                                                            
                                                                                    
      INNER JOIN VELA_BUS2.dbo.Organization_AgencyEmployee ae                                                                              
ON      ao.AgencyOfficeId                        = ae.AgencyOfficeId                                                      
                                                                                    
      INNER JOIN VELA_BUS2.dbo.Organization_AgencyEmployeeStatus aes                                                                              
ON      ae.AgencyEmployeeStatusId            = aes.AgencyEmployeeStatusId                                                                  
                                                                                    
      INNER JOIN VELA_BUS2.dbo.Organization_Email aeemail                                                                              
ON      ae.AgencyEmployeeId                        = aeemail.ParentId                                                      
                                                                                    
      INNER JOIN VELAREPORTING.dbo.Broker_Info binfo                                                                              
ON      ao.AlternateId                              = binfo.AlternateId                                                
                                                                                    
      LEFT OUTER JOIN VELA_BUS2.dbo.Organization_AgencyRole agcyemplrol0                                                                              
ON      ae.AgencyRoleId                              = agcyemplrol0.AgencyRoleId                                                
AND      agcyemplrol0.AgencyRoleId            = 0                                                                  
                                                                                    
      LEFT OUTER JOIN VELA_BUS2.dbo.Organization_AgencyRoleAssociation rolassoc0                                                                              
ON      ae.AgencyEmployeeId                        = rolassoc0.AgencyEmployeeId                                                      
AND      rolassoc0.AgencyRoleId                        = 0                                                      
                                                                                    
      LEFT OUTER JOIN VELA_BUS2.dbo.Organization_AgencyRole agcyemplrol1                                                                              
ON      ae.AgencyRoleId                              = agcyemplrol1.AgencyRoleId                                                
AND      agcyemplrol1.AgencyRoleId            = 1                                                                  
                                                                                    
      LEFT OUTER JOIN VELA_BUS2.dbo.Organization_AgencyRoleAssociation rolassoc1                                                                              
ON      ae.AgencyEmployeeId                        = rolassoc1.AgencyEmployeeId                                                      
AND      rolassoc1.AgencyRoleId                  = 1                                                            
                                                                                    
      LEFT OUTER JOIN VELA_BUS2.dbo.Organization_AgencyRole agcyemplrol2                                                                              
ON      ae.AgencyRoleId                              = agcyemplrol2.AgencyRoleId                                                
AND      agcyemplrol2.AgencyRoleId            = 2                                                                  
                                                                                    
      LEFT OUTER JOIN VELA_BUS2.dbo.Organization_AgencyRoleAssociation rolassoc2                                                                              
ON      ae.AgencyEmployeeId                        = rolassoc2.AgencyEmployeeId                                                      
AND      rolassoc2.AgencyRoleId                              = 2                                                
                                                                                    
WHERE                                                                                    
      VELAREPORTING.dbo.FormatDateTime( ae.UpdateDate, 'YYYY-MM-DD' )      >= @EvalDate                                                                        
AND      aes.Status                                    = 'Enabled'                                          
AND      aos.Status                                    = 'Enabled'            
0
Comment
Question by:richa1960
  • 3
  • 2
5 Comments
 
LVL 32

Expert Comment

by:ewangoya
ID: 34898055

Just change the first part to

DECLARE @EvalDate AS CHAR(10)                                                                                    
SET @EvalDate = CAST(DATEADD(DD, -7, GETDATE() AS VARCHAR(10))
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 34898158
This is better

DECLARE @EvalDate AS CHAR(10)  
                                                                             
SET @EvalDate = CONVERT(VARCHAR, DATEADD(DD, -7, GETDATE()), 101)
0
 

Author Comment

by:richa1960
ID: 34898924
The new line in the code parse ok, But no data gets returned. I get data if
i just enter a date range like below.

SET @EvalDate = '2011-01-31'
0
 
LVL 32

Accepted Solution

by:
ewangoya earned 500 total points
ID: 34899005
Its the date format
Change it to

DECLARE @EvalDate AS CHAR(10)  
                                                                             
SET @EvalDate = CONVERT(VARCHAR, DATEADD(DD, -7, GETDATE()), 120)
0
 

Author Comment

by:richa1960
ID: 34899102
sweet, thank great solution.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

For a while now I'v been searching for a circular progress control, much like the one you get when first starting your Silverlight application. I found a couple that were written in WPF and there were a few written in Silverlight, but all appeared o…
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 Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
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…

758 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

18 Experts available now in Live!

Get 1:1 Help Now