Solved

sql code to pull back data from 7 days prior

Posted on 2011-02-15
5
398 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article I will describe the Detach & Attach 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…
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

929 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

9 Experts available now in Live!

Get 1:1 Help Now