[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 410
  • Last Modified:

sql code to pull back data from 7 days prior

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
richa1960
Asked:
richa1960
  • 3
  • 2
1 Solution
 
Ephraim WangoyaCommented:

Just change the first part to

DECLARE @EvalDate AS CHAR(10)                                                                                    
SET @EvalDate = CAST(DATEADD(DD, -7, GETDATE() AS VARCHAR(10))
0
 
Ephraim WangoyaCommented:
This is better

DECLARE @EvalDate AS CHAR(10)  
                                                                             
SET @EvalDate = CONVERT(VARCHAR, DATEADD(DD, -7, GETDATE()), 101)
0
 
richa1960Author Commented:
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
 
Ephraim WangoyaCommented:
Its the date format
Change it to

DECLARE @EvalDate AS CHAR(10)  
                                                                             
SET @EvalDate = CONVERT(VARCHAR, DATEADD(DD, -7, GETDATE()), 120)
0
 
richa1960Author Commented:
sweet, thank great solution.
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.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now