removing repeating fields in result
-
Hi SQL experts.
I'm trying to make a query that lists out changes of an order in my database.
Example:
SELECT
orderid AS [OrderID],
firstname + ' ' + lastname AS [Customer],
orderdate AS [Order Date],
date AS [Update Date],
time AS [Updated Time],
comment AS [Comment]
FROM
order left join
customer on order.customerid = customer.id
Give's me the result:
OrderID Customer OrderDate Update Date Update Time Comment
100 Joe Doe 17/09/2005 18/09/2005 15:38 Order received and logged
100 Joe Doe 17/09/2005 18/09/2005 10:21 Payment received, packing
100 Joe Doe 17/09/2005 18/09/2005 15:05 Product shipped with tracking number #xxxxxxx
But I would like to get the result like this:
OrderID Customer OrderDate Update Date Update Time Comment
100 Joe Doe 17/09/2005 18/09/2005 15:38 Order received and logged
NULL   NULL     NULL     18/09/2005 10:21 Payment received, packing
NULL   NULL     NULL     18/09/2005 15:05 Product shipped with tracking number #xxxxxxx
I know this is possible in Oracle, but can I do this in SQL Server?
Regards,
-
something very similar has been addressed in this other post
**ASCII stupid question, get a stupid ANSI !!!** -
I had a look at that post at it looks very similar...but i can seem to find a way to do the insert into the temp table. And do I have to use 2 temp table?
-
I suppose it can be done without a temp table, but that's going to be hard... Instead, try this:
create table #temp
(
[Id] int identity(1,1),
[OrderID] int null,
[Customer] varchar(100) null,
[Order Date] datetime null,
[Update Date] datetime,
[Updated Time] datetime,
[Comment] varchar(100)
)
go
insert into #Temp (OrderId, Customer, [Order Date], [Update Date], [Updated Time], Comment)
SELECT
orderid AS [OrderID],
firstname + ' ' + lastname AS [Customer],
orderdate AS [Order Date],
date AS [Update Date],
time AS [Updated Time],
comment AS [Comment]
FROM
order left join
customer on order.customerid = customer.id
order by orderid -- , other columns ?? ...
update t1 set t1.OrderId = NULL, t1.Customer = NULL, t1.[Order Date] = NULL
from #temp t1 inner join #temp t2 on t1.id = t2.id + 1 and t1.orderid = t2.orderid
select OrderId, Customer, [Order Date], [Update Date], [Updated Time], Comment from #Temp
drop table #temp
go
-
If I have the ddl correct and you do not mind the results in updated date/time sequence then try
SELECT CASE WHEN [suppress]=0 THEN [Order_ID] ELSE NULL END AS [OrderID],
CASE WHEN [suppress]=0 THEN [Order_ID] ELSE NULL END AS [Customer],
CASE WHEN [suppress]=0 THEN [Order_ID] ELSE NULL END AS [Order Date],
[Update Date],[Updated Time],[Comment]
FROM (
SELECT o.orderid AS [Order_ID],
c.firstname + ' ' + c.lastname AS [Customer],
o.orderdate AS [Order Date],
o.[date] AS [Update Date],
o.[time] AS [Updated Time],
o.comment AS [Comment],
(select count(*) from [#order] x where x.customerid=c.[id] and x.[date]=o.[date] and x.[time]<o.[time]) AS [suppress]
FROM [#order] o
left join [#customer] c on c.[id] = o.customerid
) y
ORDER BY [Order_ID],[Update Date],[Updated Time]
Far away is close at hand in the images of elsewhere.
Anon. -
Just had a thought
May need to change the subquery to
(select count(*) from [#order] x where x.customerid=c.[id] and (x.[date]<o.[date] or (x.[date]=o.[date] and x.[time]<o.[time]))) AS [suppress]
if data spans several date/time
Far away is close at hand in the images of elsewhere.
Anon. -
Yes, it would be easier if date and time were in the same column
I guess your query will fail if the combination of date and time is not unique for a given order... But that's probably not the case...
-
I guess your query will fail....... Yep, c**p in, c**p out
Far away is close at hand in the images of elsewhere.
Anon. -
Meaning... (for a non-native English speaker
) ??? 
-
Sorry, some people might get offended (or I get beaten up by the moderator for using offensive language)
Politely put
Rubbish in, Rubbish out
Only meant as a joke
and not intended to infer poor quality of peoples systems 
Far away is close at hand in the images of elsewhere.
Anon. -
I see
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply