Forum Discussion
Solution SageTranspose Row Data into Column based on a criteria
Hi rocky09,
You can write the T-SQL query like below:
SELECT * FROM test0825 t CROSS APPLY (SELECT TOP 1 ActivityID ActivityID_, Date Date_, InteractionID InteractionID_, Type Type_, ModifiedBy ModifiedBy_ FROM test0825 WHERE ModifiedBy = 'system' AND InteractionID = t.InteractionID AND Date >= t.Date ORDER BY date ASC) ca WHERE t.ModifiedBy <> 'system'
If you have any question about T-SQL query, please post a thread in Transact-SQL forum.
Best Regards,
QiuyunYu
Memorable Memberwill System always be the next sequential record?
- rocky09
- CahabaData
there's more than one way to skin this cat. since System is always the next sequential plus you have a sequential Activity ID - it gives you alot of choices;
in a formula / measure solution one can employ the EARLIER function
or you can model it at the table level. if the record set is not super huge I myself prefer the table level but it does use more memory resource. One could duplicate tables with a filter on ModifiedBy so you have the System Table and the non System Table.... then in the System table create a new calculate column SysActivityID which is ActivityID less 1. Then join these 2 tables together - that gives you all fields in 1 row at the table level. As described here these are all steps in the Query Editor which will auto fire each time you refresh data.
- rocky09
Good Idea. But, unfortunately, the database is huge and fetching from live sql server. Anyway, I will your suggestion and see the result.
Btw, can we do something in Sql query itself?
