SQL Server - Group By Query problem

Asked By Jem Savery on 25-Sep-10 12:36 AM

Hi Friends,

I have tricky query.. I have following 4 tables

tblOrder, tblOrderDetails, tblOrderStatus, tblStatus

tblOrder has single field (primarikey) : OrderId

tblOrderDetails contains - DetailId, OrderId(FK), ValueType, Value

tblOrderStatus contains - OrderId, StatusId, StatusDateTime, UserId (to see who has changed the status last)

tblStatus contains - StatusId, StatusValue.

Now the requirement is

I can have more than 1 values in tblOrderDetails & tblOrderStatus.

Now while retrieving the data I want to archive the Latest tblOrderStatus record for that orderId.

Lets say for OrderId=1 I have 3 records in tblOrderStatus

One for Intianliy placed order (Require confirmation)

Second is (Order Confirmed)

third is (Order Completed)

Now when I see the order date, I want to get all the values of tblOrderStatus. I want following columns

OrderId, StatusId, StatusValue, StatusDateTime, UserId.

so for every order I can have its latest statusvalue..

How can I do this? Please help.

harsh shah replied to Jem Savery on 25-Sep-10 01:34 AM

Hi,

just add one field to the table --- tblOrderStatus

the field name is ChkflagStatus--
when u have save the data that time insert the status in this table.
for Require confirmation - set status R
Order Confirmed - set O
Order Completed - set C

so when ever u have retrive a data from a table that time check a orderid,& status.
For Example

select * from tblOrderStatus where orderid='1' and ChkflagStatus ='C'

let me know.
Regards,

Harsh Shah

Jem Savery replied to harsh shah on 25-Sep-10 01:55 AM

Thanks Harsh,

but, I need all the records not only completed one. So while showing the orders in Status column, they should have their latest status.

So for New orders, Status = 'Required Comfirmation'

for Orders confirmed, Status = 'Order Confirmed'

& for completed orders, Status = 'Order Completed'.

Rohan Dave replied to Jem Savery on 25-Sep-10 08:14 AM

Try below query and let us know.. I hope it will solve your problem...

Select tO.OrderId,
tS.StatusId,
ts.StatusValue,
tOS.StatusDateTime,
tOS.UserId
From
tblOrder tO
Inner Join tblOrderDetails tOD on tO.OrderId = tOD.OrderId
Inner Join tblOrderStatus tOS on tOD.OrderId = tOS.OrderId
Inner Join tblStatus tS on tOS.StatusId = tS.StatusId
Order By tO.OrderID Asc, tOS.StatusDateTime Desc