CODEWITHSUNDEEP
sqlt-sqlsql-server-2012
cnd
cnd

Reputation: 33754

How to union two column tables into complex one by by merging time column?

For example I've got many tables alike [VV1] and [VV2] with:

[TimeStamp], [V1]

2013-12-12 07:00:00.000 3628460,75
2013-12-12 09:00:00.000 3628460,75
2013-12-12 10:00:00.000 3628460,75
2013-12-12 11:00:00.000 3628460,75
2013-12-12 06:00:00.000 3628460,75

[TimeStamp], [V2]

2013-12-12 07:00:00.000 3628460,75
2013-12-12 09:00:00.000 3628460,75
2013-12-12 10:00:00.000 3628460,75
2013-12-12 06:00:00.000 3628460,75

and I want to get something alike:

2013-12-12 07:00:00.000 3628460,75  3628460,75
2013-12-12 08:00:00.000 NULL        3628460,75
2013-12-12 09:00:00.000 3628460,75  3628460,75
2013-12-12 10:00:00.000 3628460,75  3628460,75
2013-12-12 11:00:00.000 3628460,75  NULL
2013-12-12 06:00:00.000 3628460,75  3628460,75

I tried to implement it with joins and my variant is really terrible:

select distinct DATEADD(second, DATEDIFF(second, GETUTCDATE(), GETDATE()), coalesce(VV1.[TimeStamp], VV2.[TimeStamp])) as Date, V1, V2
from vv1
full join vv2
on vv2.TimeStamp = vv1.TimeStamp

( DATEDD is UTC to LOCAL time)

It was realized like that because table count is dynamic and everytime when I add new table and want to see new column I can easily edit select string.

But this variant is terrible because it populate double and with more columns it's just goes crazy and distinct is dirty hack to merge...

Now I think about sane and fast realization... How to make it?

Upvotes: 1

Views: 71

Answers (1)

bendataclear
bendataclear

Reputation: 3850

I don't think there will be a better performing solution but the query below might be a neater way of doing it, especially if adding new tables:

select [Date]
     , MAX([Value1]) as [Value1]
     , MAX([Value2]) as [Value2]

from (
    select DATEADD(second, DATEDIFF(second, GETUTCDATE(), GETDATE()), VV1.[TimeStamp]) as [Date]
         , V1 as [Value1]
         , Null as [Value2]
    from vv1

    UNION ALL

    select DATEADD(second, DATEDIFF(second, GETUTCDATE(), GETDATE()), VV2.[TimeStamp]) as [Date]
         , Null as [Value1]
         , V2 as [Value2]
    from vv2 ) subquery

group by subquery.date

Upvotes: 1

Related Questions