richa1960
asked on
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.LegalNa me) AS 'BrokerGroup',
--ao.AlternateId AS 'BrokerOfficeId',
--dbo.ProperCase(ao.Formal Name) AS 'BrokerOffice',
--aos.Status AS 'BrokerOfficeStatus',
'BUS' AS 'Source - Custom Field 1',
@EvalDate AS 'Type',
dbo.ProperCase(RTRIM(LTRIM (ae.FirstN ame))) AS 'FirstName',
dbo.ProperCase(RTRIM(LTRIM (ae.LastNa me))) AS 'LastName',
ISNULL(RTRIM(LTRIM(aeemail .EmailAddr ess)), ' ') AS 'EmailAddress',
CAST(VELAREPORTING.dbo.For matDateTim e(ae.Creat eDate, 'YYYY-MM-DD') AS DATETIME)
AS 'BUSBrokerCreateDate - Do Not Import',
CAST(VELAREPORTING.dbo.For matDateTim e(ae.Updat eDate, '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 _AgencyOff ice ao
ON a.AgencyId = ao.AgencyId
INNER JOIN VELA_BUS2.dbo.Organization _AgencyOff iceStatus aos
ON ao.AgencyOfficeStatusId = aos.AgencyOfficeStatusId
INNER JOIN VELA_BUS2.dbo.Organization _AgencyEmp loyee ae
ON ao.AgencyOfficeId = ae.AgencyOfficeId
INNER JOIN VELA_BUS2.dbo.Organization _AgencyEmp loyeeStatu s aes
ON ae.AgencyEmployeeStatusId = aes.AgencyEmployeeStatusId
INNER JOIN VELA_BUS2.dbo.Organization _Email aeemail
ON ae.AgencyEmployeeId = aeemail.ParentId
INNER JOIN VELAREPORTING.dbo.Broker_I nfo binfo
ON ao.AlternateId = binfo.AlternateId
LEFT OUTER JOIN VELA_BUS2.dbo.Organization _AgencyRol e agcyemplrol0
ON ae.AgencyRoleId = agcyemplrol0.AgencyRoleId
AND agcyemplrol0.AgencyRoleId = 0
LEFT OUTER JOIN VELA_BUS2.dbo.Organization _AgencyRol eAssociati on rolassoc0
ON ae.AgencyEmployeeId = rolassoc0.AgencyEmployeeId
AND rolassoc0.AgencyRoleId = 0
LEFT OUTER JOIN VELA_BUS2.dbo.Organization _AgencyRol e agcyemplrol1
ON ae.AgencyRoleId = agcyemplrol1.AgencyRoleId
AND agcyemplrol1.AgencyRoleId = 1
LEFT OUTER JOIN VELA_BUS2.dbo.Organization _AgencyRol eAssociati on rolassoc1
ON ae.AgencyEmployeeId = rolassoc1.AgencyEmployeeId
AND rolassoc1.AgencyRoleId = 1
LEFT OUTER JOIN VELA_BUS2.dbo.Organization _AgencyRol e agcyemplrol2
ON ae.AgencyRoleId = agcyemplrol2.AgencyRoleId
AND agcyemplrol2.AgencyRoleId = 2
LEFT OUTER JOIN VELA_BUS2.dbo.Organization _AgencyRol eAssociati on rolassoc2
ON ae.AgencyEmployeeId = rolassoc2.AgencyEmployeeId
AND rolassoc2.AgencyRoleId = 2
WHERE
VELAREPORTING.dbo.FormatDa teTime( ae.UpdateDate, 'YYYY-MM-DD' ) >= @EvalDate
AND aes.Status = 'Enabled'
AND aos.Status = 'Enabled'
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.LegalNa
--ao.AlternateId AS 'BrokerOfficeId',
--dbo.ProperCase(ao.Formal
--aos.Status AS 'BrokerOfficeStatus',
'BUS' AS 'Source - Custom Field 1',
@EvalDate AS 'Type',
dbo.ProperCase(RTRIM(LTRIM
dbo.ProperCase(RTRIM(LTRIM
ISNULL(RTRIM(LTRIM(aeemail
CAST(VELAREPORTING.dbo.For
AS 'BUSBrokerCreateDate - Do Not Import',
CAST(VELAREPORTING.dbo.For
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
INNER JOIN VELA_BUS2.dbo.Organization
ON a.AgencyId = ao.AgencyId
INNER JOIN VELA_BUS2.dbo.Organization
ON ao.AgencyOfficeStatusId = aos.AgencyOfficeStatusId
INNER JOIN VELA_BUS2.dbo.Organization
ON ao.AgencyOfficeId = ae.AgencyOfficeId
INNER JOIN VELA_BUS2.dbo.Organization
ON ae.AgencyEmployeeStatusId = aes.AgencyEmployeeStatusId
INNER JOIN VELA_BUS2.dbo.Organization
ON ae.AgencyEmployeeId = aeemail.ParentId
INNER JOIN VELAREPORTING.dbo.Broker_I
ON ao.AlternateId = binfo.AlternateId
LEFT OUTER JOIN VELA_BUS2.dbo.Organization
ON ae.AgencyRoleId = agcyemplrol0.AgencyRoleId
AND agcyemplrol0.AgencyRoleId = 0
LEFT OUTER JOIN VELA_BUS2.dbo.Organization
ON ae.AgencyEmployeeId = rolassoc0.AgencyEmployeeId
AND rolassoc0.AgencyRoleId = 0
LEFT OUTER JOIN VELA_BUS2.dbo.Organization
ON ae.AgencyRoleId = agcyemplrol1.AgencyRoleId
AND agcyemplrol1.AgencyRoleId = 1
LEFT OUTER JOIN VELA_BUS2.dbo.Organization
ON ae.AgencyEmployeeId = rolassoc1.AgencyEmployeeId
AND rolassoc1.AgencyRoleId = 1
LEFT OUTER JOIN VELA_BUS2.dbo.Organization
ON ae.AgencyRoleId = agcyemplrol2.AgencyRoleId
AND agcyemplrol2.AgencyRoleId = 2
LEFT OUTER JOIN VELA_BUS2.dbo.Organization
ON ae.AgencyEmployeeId = rolassoc2.AgencyEmployeeId
AND rolassoc2.AgencyRoleId = 2
WHERE
VELAREPORTING.dbo.FormatDa
AND aes.Status = 'Enabled'
AND aos.Status = 'Enabled'
This is better
DECLARE @EvalDate AS CHAR(10)
SET @EvalDate = CONVERT(VARCHAR, DATEADD(DD, -7, GETDATE()), 101)
DECLARE @EvalDate AS CHAR(10)
SET @EvalDate = CONVERT(VARCHAR, DATEADD(DD, -7, GETDATE()), 101)
ASKER
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'
i just enter a date range like below.
SET @EvalDate = '2011-01-31'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
sweet, thank great solution.
Just change the first part to
DECLARE @EvalDate AS CHAR(10)
SET @EvalDate = CAST(DATEADD(DD, -7, GETDATE() AS VARCHAR(10))