March 16, 2012 at 6:50 am
Hi Experts,
I have a slight problem which im hoping you can help me with,
I have a stored procedure that returns 5 columns of data which would cosist of
OrderID SharedGPAmount CompanyName IsConnected ConnectedDate
and this is displayed on a popup in a grid which is working fine, my problem is this
I recently added the ConnectedDate to show when the phone was connected etc again this works fine if there is only one order under the orderID but some people may have one a more order under the orderID and im tyring to return the connected date if all the orders under the orderID that is passed in are connected is that makes sense?
So say i have two items under one OrderID and only one of the items is connected and there waiting to connect the other this would mean the actual order isnt completed yet because there is still one outstanding and when i run it like this is in the popup that is displayed i get the first item with the connecteddate shown and another row with the same OrderID but without a connected date so my question is how can i only show just the one record displaying a connecteddate or not depending on if all the items are connected?
this is my procedure
if(@Type='GP')
begin
Select distinct OSP.OrderID,SharedGPAmount,c.CompanyName,dbo.Func_IsOrderConnected(CO.OrderID,@FromDate,@ToDate)as IsConnected,
convert(char(10),ORH.ConnectedDate,103) ConnectedDate
from OrderSplitGP(nolock) OSP
join CustomerOrders CO(nolock) on CO.OrderID = OSP.OrderID
join Customer c(nolock) on c.CustomerID = CO.CustomerID
join OrderHandsets ORH(nolock) on ORH.OrderID = co.OrderID
where OSP.ShortAbbr = @UserAbbr
and CO.orderstatus = 4
and CONVERT(date, OSP.GPAddedDate)between @FromDate and @ToDate
end
Hope to hear from you soon
March 16, 2012 at 7:22 am
I can't test this, since I don't have table definitions, et al, but it should do what you need:
IF (@Type = 'GP')
BEGIN
SELECT DISTINCT
OSP.OrderID,
SharedGPAmount,
c.CompanyName,
dbo.Func_IsOrderConnected(CO.OrderID, @FromDate, @ToDate) AS IsConnected,
CASE WHEN EXISTS ( SELECT *
FROM OrderHandsets AS ORH
WHERE ORH.OrderID = co.OrderID
AND ORH.ConnectedDate IS NULL ) THEN NULL
ELSE (SELECT MAX(ConnectedDate)
FROM OrderHandsets AS ORH
WHERE ORH.OrderID = co.OrderID)
END AS ConnectedDate
FROM OrderSplitGP (NOLOCK) OSP
JOIN CustomerOrders CO (NOLOCK)
ON CO.OrderID = OSP.OrderID
JOIN Customer c (NOLOCK)
ON c.CustomerID = CO.CustomerID
JOIN OrderHandsets ORH (NOLOCK)
ON ORH.OrderID = co.OrderID
WHERE OSP.ShortAbbr = @UserAbbr
AND CO.orderstatus = 4
AND CONVERT(DATE, OSP.GPAddedDate) BETWEEN @FromDate AND @ToDate
END
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 16, 2012 at 8:15 am
SSChampion Thank you so much, thats perfect, one other thing if i may, say for example i have an order with two items, and one item was completed on 01/03/2012 and the other was completed on 15/03/2012 how can i return the most recent date within that SQL proc you provided?
March 20, 2012 at 6:42 am
Is the "most recent date" the most recent ConnectedDate? If so, this will already do that. The Max() function in the sub-query does that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply