Problem with a complex SQL Query for SQL Server

[piyush12345]

Hi,

I want to do something like this -

Get Wind_direction, Wind_speed, SiteCode, Latitude, Longitude side by side-

The problem is that (DataValue as wind_direction)Wind_direction and (DataValue as)wind_speed are under the same column DataValue and for Wind_direction variableID is 4 and for Wind_speed is 3

the schema can be found here (Fixed to show instead - NeoPa)

And here's the query I wrote-

SELECT TOP(61) DataValues.DataValue AS Wind_speed, DataValues.DataValue AS Wind_direction, Sites.SiteCode, Sites.Latitude, Sites.Longitude
FROM DataValues INNER JOIN Sites ON DataValues.SiteID = Sites.SiteID
WHERE DataValues.DataValue IN (Select DataValue FROM DataValues WHERE DataValues.VariableID=3)
OR DataValues.DataValue IN (SELECT DataValue FROM DataValues WHERE DataValues.VariableID = 4) 
ORDER BY LocalDateTime DESC;

Thanks!

Piyush

[Rabbit]

You can use either a pivot (to pivot the 3/4 across the top) or join 2 queries to each other (one for 3 and the other for 4).

[piyush12345]

Sorry, didn't get you, I don't know how to use pivot but tried to join two qierues but it didn't work,

Here's my failed query-

Select speed.DataValue AS 'Wind_speed', direction.DataValue AS 'Wind_direction', Sites.Latitude, Sites.Longitude, Sites.SiteCode
FROM DataValues AS speed
INNER JOIN DataValues AS direction ON speed.SiteID = direction.SiteID
INNER JOIN Sites ON speed.SiteID = Sites.SiteID
WHERE (speed.VariableID = 3 AND direction.VariableID = 4)
HAVING COUNT(*) = 61
ORDER BY speed.LocalDateTime DESC;

Error:
Msg 8120, Level 16, State 1, Line 2
Column 'DataValues.DataValue' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

[Rabbit]

Why did you change your query into an aggregate query when your original was not? I didn't mention using an aggregate query so I don't know why you did that.

Also, what you need to do it take your original query, duplicate it, make one filter for 3, the other filter for 4, and then join them together.

[piyush12345]

It's not an aggregate query... I was just trying random stuff...

my two queries are

SELECT TOP(61) DataValue as 'Wind_speed', Sites.SiteCode, Sites.Longitude, Sites.Latitude, DataValues.VariableID, DataValues.SiteID
FROM DataValues INNER JOIN Sites ON DataValues.SiteID = Sites.SiteID
WHERE DataValues.VariableID = 3

SELECT TOP(61) DataValue as 'Wind_direction', Sites.SiteCode, Sites.Longitude, Sites.Latitude, DataValues.VariableID, DataValues.SiteID
FROM DataValues INNER JOIN Sites ON DataValues.SiteID = Sites.SiteID
WHERE DataValues.VariableID = 4
Could you tell me how to join these two...? Remember that Wind_speed and Wind_direction come from data value and I want to keep these two separate..

Thanks,

[Rabbit]

Now you join your two queries on whatever you're using as your id.

[piyush12345]

Sorry I forgot to mention that there are multiple records with silar data I want the unique data(With Latest LocalDataTime) for each site -

I just executed the following query - SELECT TOP(61) S.DataValue AS 'Wind_speed',
D.DataValue AS 'Wind_direction',
Sites.SiteCode,
Sites.Longitude,
Sites.Latitude
FROM DataValues S JOIN DataValues D ON S.SiteID = D.SiteID
INNER JOIN Sites ON S.SiteID = D.SiteID
WHERE S.VariableID = 3 and D.VariableId = 4
ORDER BY S.LocalDateTime;



And got the follwing result which is not what I want -

17.25 340 KCKP -95.556 42.732
17.25 340 KICL -95.03 40.72
17.25 340 KCAV -93.77 42.75
17.25 340 KCBF -95.77 41.27
17.25 340 KCSQ -94.37 41.02

It does give me the unique sites but does not give me the latest values for each.. and is giving me the same value for wind direction and wind speed...


Thanks for your patience..

[Rabbit]

You need to join on the fields that uniquely identify each record, not the variable id.

[ck9663]

Can you post some sample record on just the columns you need and your desired ouput?

~~ CK