sql query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sql query

Author

Topic

Sieciowiec
Starting Member

4 Posts

Posted - 2011-09-08 : 09:57:05

Hi,
I have a question:
I have a Table name 'History'
in this table I have a records like:
DateTime | TagName | Value
2011-08-01 10:42:30.543 | Transport | 0
2011-08-01 10:42:30.543 |Service | 0
2011-08-01 10:42:30.543 |Work | 1
2011-08-01 10:43:31.543 | Service | 0
2011-08-01 10:43:31.543 | Work | 0
2011-08-01 10:43:31.543 | Transport | 1
etc.

I want have something like that:
DateTimeStart|DateTimeEnd | Work |Transport | Service
2011-08-01 10:42:30.543 | 2011-08-01 10:43:31.543 | 1 | 0 |0
2011-08-01 10:43:31.543 | (next date) | 0 | 1 | 0

How do that ??

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2011-09-08 : 14:20:45

Try something like this:


SELECT DateTime AS DateTimeStart,
       (SELECT TOP(1) H2.DateTime
          FROM History AS H2
         WHERE H2.DateTime > H1.DateTime
         ORDER BY H2.DateTime) AS DateTimeEnd,
       MAX(CASE WHEN TagName = 'Work' THEN Value END) AS Work,
       MAX(CASE WHEN TagName = 'Transport' THEN Value END) AS Transport,
       MAX(CASE WHEN TagName = 'Service' THEN Value END) AS Service
  FROM History AS H1
 GROUP BY DateTime;

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2011-09-09 : 02:01:45

I think malpashaa way is faster and less memory consumpation ..
But still i tried this and found another solution..

WITH CTE AS
(
SELECT*, DENSE_RANK() OVER(ORDER BY Dates) as RN FROM #History
)
SELECT
A.Dates,
B.Dates,
MAX(CASE WHEN A.TagName = 'Work' THEN A.Value END) AS Work,
MAX(CASE WHEN A.TagName = 'Transport' THEN A.Value END) AS Transport,
MAX(CASE WHEN A.TagName = 'Service' THEN A.Value END) AS Service
FROM CTE A LEFT JOIN CTE B on A.RN+1=B.RN
GROUP BY A.Dates,B.Dates

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources