?
Solved

sql code to pull back data from 7 days prior

Posted on 2011-02-15
5
Medium Priority
?
404 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
[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
  • 3
  • 2
5 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
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:Ephraim Wangoya
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:
Ephraim Wangoya earned 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

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, …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

743 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