SQL CrossTab query & Dates

SQL CrossTab query & Dates

Post by Chris » Sat, 08 Sep 2001 07:23:17



Hi there,

I'm a newcommer to SQLServer from Access and I'm having
difficulty with a crosstab query and how to handle dates,
I hope someone can offer me come advice.

I have a data table that serves as the central store of
some weather data. Its structure is essentially

(int)stationId, (smalldatetime)ReadingDateTime, (int)
UnitId, (float)DataValue

The values of the weather sensors are stored for each hour
of the day. What I need to do is summarise the data for
the entire day and a crosstab is the obvious way to do
this,

select StationId, ReadingDateTime,
  count(case UnitId when 6 then DataValue else NULL end)
as MyCount,
  sum(case UnitId when 6 then DataValue else NULL end) as
SolarRadn,
  avg(case UnitId when 7 then DataValue else NULL end) as
LeafR,
  avg(case UnitId when 8 then DataValue else NULL end) as
AirTemp,
  max(case UnitId when 9 then DataValue else NULL end) as
AirTemp_Max,
  min(case UnitId when 10 then DataValue else NULL end) as
AirTemp_Min,
  avg(case UnitId when 11 then DataValue else NULL end) as
VP,
  max(case UnitId when 12 then DataValue else NULL end) as
VP_Max,
  min(case UnitId when 13 then DataValue else NULL end) as
VP_Min,
  sum(case UnitId when 14 then DataValue else NULL end) as
Rain
into SCDailySummary
FROM Data
group by StationID, ReadingDateTime

The above code only does a summary by the hour, as you
would expect, the trouble that I am having is how to do
this so that the result is a summary for the day. I have
tried using the cast/convert on the ReadingDateTime to
express the datetime only as a date but this wasnt
successful. Can someone please offer me some advice on how
best to summarise this crosstab on the date and not the
date/hour.

Thanx in advance
Chris C

 
 
 

SQL CrossTab query & Dates

Post by Steve Kas » Sat, 08 Sep 2001 07:58:23


If I get what you want, try this:

SELECT stationId, CONVERT(datetime,
CONVERT(char(8),ReadingDateTime,112)) AS ReadingDate,
<all your summary expressions and everything the same up to>
GROUP BY stationId, CONVERT(datetime,
CONVERT(char(8),ReadingDateTime,112))

Steve Kass
Drew University


> Hi there,

> I'm a newcommer to SQLServer from Access and I'm having
> difficulty with a crosstab query and how to handle dates,
> I hope someone can offer me come advice.

> I have a data table that serves as the central store of
> some weather data. Its structure is essentially

> (int)stationId, (smalldatetime)ReadingDateTime, (int)
> UnitId, (float)DataValue

> The values of the weather sensors are stored for each hour
> of the day. What I need to do is summarise the data for
> the entire day and a crosstab is the obvious way to do
> this,

> select StationId, ReadingDateTime,
>   count(case UnitId when 6 then DataValue else NULL end)
> as MyCount,
>   sum(case UnitId when 6 then DataValue else NULL end) as
> SolarRadn,
>   avg(case UnitId when 7 then DataValue else NULL end) as
> LeafR,
>   avg(case UnitId when 8 then DataValue else NULL end) as
> AirTemp,
>   max(case UnitId when 9 then DataValue else NULL end) as
> AirTemp_Max,
>   min(case UnitId when 10 then DataValue else NULL end) as
> AirTemp_Min,
>   avg(case UnitId when 11 then DataValue else NULL end) as
> VP,
>   max(case UnitId when 12 then DataValue else NULL end) as
> VP_Max,
>   min(case UnitId when 13 then DataValue else NULL end) as
> VP_Min,
>   sum(case UnitId when 14 then DataValue else NULL end) as
> Rain
> into SCDailySummary
> FROM Data
> group by StationID, ReadingDateTime

> The above code only does a summary by the hour, as you
> would expect, the trouble that I am having is how to do
> this so that the result is a summary for the day. I have
> tried using the cast/convert on the ReadingDateTime to
> express the datetime only as a date but this wasnt
> successful. Can someone please offer me some advice on how
> best to summarise this crosstab on the date and not the
> date/hour.

> Thanx in advance
> Chris C


 
 
 

1. Date & Datetime SQL queries

Hi,
i've some problems with some SQL statements that i need
to perform with date and datetime.

I've a table with a datetime column. When the table was
populated, that column has date and datetime records
(e.g. some 12/24/1999 00:00:00 and 12/24/1998 12:11:11)

Problem is how do i compare with just DATE and not
datetime?? Is there some SQL function which could do that?

something like:
SELECT * FROM PRODUCT
WHERE date(product_datetime) = 12/24/1999

but i can't seem to find such DATE function which could
just retrive the DATE and not the DATETIME of the
records.. or is there??

Please help. Thanks

2. What seems simple, isnt!

3. SQL with dates displayed like crossTabs

4. Incremental process dimension and cube

5. SQL Query to a DATE & TIME Field

6. Good bood on Object PAL

7. SQL query - DATE & TIMESTAMP

8. CONTRACT/ VISUAL DBASE/ AUGUST START

9. multiple queries into a crosstab query in VB

10. SQL Server 6.0 and Query Crosstab (Access)

11. Crosstab-like queries in SQL Server