March 12, 2008 at 2:33 pm
I got it pared down to this:
--
DECLARE @COnsultantID CHAR(20)
DECLARE @StartDate Datetime
DECLARE @EndDate Datetime
SET @ConsultantID = '0000344'
SET @StartDate ='12/01/2007'
SET @EndDate =GetDate()
SELECT Distinct o.orderNumber
,c.consultantid
,(Select c.firstname + ' ' + c.Lastname) As Consultantname
,o.ShiptofirstName + ' ' + o.ShiptoLastname AS ShipToConsultantName
--,e.EffectiveDate
,o.ShipToAddressLine1
,o.ShipToAddressLine2
,o.ShipToCity
,o.ShipToRegionCode
,o.ShipToPostalCode
,o.ShipToHomePhone
,'CareerQuestion' =
CASE
WHEN f.CareerQuestion = 0 Then 'N'
ELSE 'Y'
END
,'InfoQuestion'=
CASE
WHEN f.InfoQuestion = 0 THEN 'N'
ELSE 'Y'
END
,'HostPartyQuestion'=
CASE
WHEN f.HostPartyQuestion = 0 THEN 'N'
ELSE 'Y'
END
,CONVERT(VARCHAR(10),o.ORderCreateDate,101) AS OrderCreateDate
,o.OrderGroupNumber
FROM uvwconsultantdownline c
LEFT OUTER JOIN [uvw_OrderListingSummary] o ON
c.consultantid COLLATE SQL_Latin1_General_CP1_CI_AS = o.[ConsultantID]
LEFT Outer Join OrderForm f ON o.OrderGroupNumber = f.ORderGroupNumber
AND o.OrderNumber = f.OrderNumber
WHERE o.Ordertypexid = 8 AND o.ConsultantID = @ConsultantID AND
o.ORderCreateDate Between @StartDate AND @EndDate
--OR o.[OrderCreateDate] IS NULL
ORDER BY
OrderCreateDate DESC
,o.OrderGroupNumber
,o.orderNumber
This works great up to the point where a empty recordset is returned and I need a single row containing the consultantID and ConsultantName.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 12, 2008 at 2:37 pm
The MAX statement isn't working now?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 12, 2008 at 2:40 pm
I tried it with the MAX statement and received the same results.
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
March 12, 2008 at 2:48 pm
I hate to say it, at this point, I'm confused. I'd have to see structure & some data to better understand what you're trying to do and what's going wrong. Sorry.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 12, 2008 at 3:00 pm
I got it. What a pain. This is what it looks like:
SELECT Distinct o.orderNumber
,c.consultantid
,(Select c.firstname + ' ' + c.Lastname) As Consultantname
,o.ShiptofirstName + ' ' + o.ShiptoLastname AS ShipToConsultantName
--,e.EffectiveDate
,o.ShipToAddressLine1
,o.ShipToAddressLine2
,o.ShipToCity
,o.ShipToRegionCode
,o.ShipToPostalCode
,o.ShipToHomePhone
,'CareerQuestion' =
CASE
WHEN f.CareerQuestion = 0 Then 'N'
ELSE 'Y'
END
,'InfoQuestion'=
CASE
WHEN f.InfoQuestion = 0 THEN 'N'
ELSE 'Y'
END
,'HostPartyQuestion'=
CASE
WHEN f.HostPartyQuestion = 0 THEN 'N'
ELSE 'Y'
END
,CONVERT(VARCHAR(10),o.ORderCreateDate,101) AS OrderCreateDate
,o.OrderGroupNumber
FROM uvwconsultantdownline c
LEFT OUTER JOIN [uvw_OrderListingSummary] o ON
c.consultantid = o.[ConsultantID]
AND o.Ordertypexid = 8
AND o.ORderCreateDate Between @StartDate AND @EndDate
LEFT Outer Join OrderForm f ON o.OrderGroupNumber = f.ORderGroupNumber
AND o.OrderNumber = f.OrderNumber
WHERE C.ConsultantID = @ConsultantID
--OR o.[OrderCreateDate] IS NULL
ORDER BY
OrderCreateDate DESC
,o.OrderGroupNumber
,o.orderNumber
Thanks,
Art
Database Analyst
Tastefully Simple, Inc.
alorenzini@tastefullysimple.com
" Some days, it's not even worth chewing through the restraints!
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply