Solved

sql code to pull back data from 7 days prior

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Count where two id types exist in column 8 30
Help Required 3 108
Find results from sql within a time span 11 46
What is this datetime? 1 19
Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
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…
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…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

837 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