|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Another Join / aggregate newbie request for helpI need to present som logged data from a tabel accumulated last hour and total. I have two tables [analog register] AR ID (unik PK) SignalText (plain text, description of the datapoint) id SignalText ----------- -------------------------------------------------------------------------------- 1 Meter1 2 Meter2 3 Meter3 4 Meter4 5 Meter5 [analog values] AV ID (foreign key) Val (float) TimeDate (date and time of the log) id val timedate ----------- ----------------------------------------------------- ------------------------------------------------------ 1 31.445295333862305 2006-05-16 16:00:00.000 1 31.445295333862305 2006-05-16 17:00:00.000 1 31.445295333862305 2006-05-16 18:00:00.000 1 31.445295333862305 2006-05-16 19:00:00.000 1 31.445295333862305 2006-05-16 20:00:00.000 1 31.445295333862305 2006-05-16 21:00:00.000 1 31.445295333862305 2006-05-16 22:00:00.000 1 31.445295333862305 2006-05-16 23:00:00.000 1 31.445295333862305 2006-05-17 00:00:00.000 1 16.766511493259003 2006-05-17 00:00:00.000 1 14.931663513183594 2006-05-17 16:00:00.000 1 14.931663513183594 2006-05-17 17:00:00.000 2 48.464839935302734 2006-05-16 19:00:00.000 2 48.464839935302734 2006-05-16 20:00:00.000 2 48.464839935302734 2006-05-16 21:00:00.000 2 48.464839935302734 2006-05-16 22:00:00.000 2 48.464839935302734 2006-05-16 23:00:00.000 2 48.464839935302734 2006-05-17 00:00:00.000 2 40.310207366943352 2006-05-17 00:00:00.000 2 39.290878295898437 2006-05-17 16:00:00.000 2 39.290878295898437 2006-05-17 17:00:00.000 2 39.290878295898437 2006-05-17 18:00:00.000 2 39.290878295898437 2006-05-17 19:00:00.000 2 39.290878295898437 2006-05-17 20:00:00.000 2 39.290878295898437 2006-05-17 21:00:00.000 2 39.290878295898437 2006-05-17 22:00:00.000 2 39.290878295898437 2006-05-17 23:00:00.000 2 39.290878295898437 2006-05-18 00:00:00.000 2 39.290878295898437 2006-05-18 00:00:00.000 2 60.707363128662102 2006-06-05 00:00:00.000 2 60.707363128662109 2006-06-05 09:00:00.000 2 60.707363128662109 2006-06-05 10:00:00.000 I would like to present one row per entry in the AR table like this [Signaltext] [ Last hour] [Total] Meter1 1200 40000 Meter2 110 14000 Meter3 1600 78000 etc Could anyone here get me started? regards Henry Select SignalText,
Sum(Case WHEN Datediff(Mi,getdate(),timedate) <60 THEN Val ELSE 0 END) as [Last Hour], Sum(val) as Total From AR INNER JOIN AV On AR.ID = AV.ID GROUP by SignalText This might not be perfect but, the total sum should be fine, The last hour - get the difference between now and the timedate field in minutes. If this difference is < 60, then the timedate field is less than 1 hour different than the current date, meaning it is this hour. When this is true add the value to the sum, otherwise add 0 to the sum.. Hope this helps get you started -- Show quoteWayne Snyder MCDBA, SQL Server MVP Mariner, Charlotte, NC I support the Professional Association for SQL Server ( PASS) and it''s community of SQL Professionals. "henry" wrote: > Hello > > > I need to present som logged data from a tabel accumulated last hour and > total. > > I have two tables > > [analog register] AR > ID (unik PK) > SignalText (plain text, description of the datapoint) > > id SignalText > ----------- -------------------------------------------------------------------------------- > 1 Meter1 > 2 Meter2 > 3 Meter3 > 4 Meter4 > 5 Meter5 > > > > [analog values] AV > ID (foreign key) > Val (float) > TimeDate (date and time of the log) > > id val timedate > ----------- ----------------------------------------------------- ------------------------------------------------------ > 1 31.445295333862305 2006-05-16 > 16:00:00.000 > 1 31.445295333862305 2006-05-16 > 17:00:00.000 > 1 31.445295333862305 2006-05-16 > 18:00:00.000 > 1 31.445295333862305 2006-05-16 > 19:00:00.000 > 1 31.445295333862305 2006-05-16 > 20:00:00.000 > 1 31.445295333862305 2006-05-16 > 21:00:00.000 > 1 31.445295333862305 2006-05-16 > 22:00:00.000 > 1 31.445295333862305 2006-05-16 > 23:00:00.000 > 1 31.445295333862305 2006-05-17 > 00:00:00.000 > 1 16.766511493259003 2006-05-17 > 00:00:00.000 > 1 14.931663513183594 2006-05-17 > 16:00:00.000 > 1 14.931663513183594 2006-05-17 > 17:00:00.000 > 2 48.464839935302734 2006-05-16 > 19:00:00.000 > 2 48.464839935302734 2006-05-16 > 20:00:00.000 > 2 48.464839935302734 2006-05-16 > 21:00:00.000 > 2 48.464839935302734 2006-05-16 > 22:00:00.000 > 2 48.464839935302734 2006-05-16 > 23:00:00.000 > 2 48.464839935302734 2006-05-17 > 00:00:00.000 > 2 40.310207366943352 2006-05-17 > 00:00:00.000 > 2 39.290878295898437 2006-05-17 > 16:00:00.000 > 2 39.290878295898437 2006-05-17 > 17:00:00.000 > 2 39.290878295898437 2006-05-17 > 18:00:00.000 > 2 39.290878295898437 2006-05-17 > 19:00:00.000 > 2 39.290878295898437 2006-05-17 > 20:00:00.000 > 2 39.290878295898437 2006-05-17 > 21:00:00.000 > 2 39.290878295898437 2006-05-17 > 22:00:00.000 > 2 39.290878295898437 2006-05-17 > 23:00:00.000 > 2 39.290878295898437 2006-05-18 > 00:00:00.000 > 2 39.290878295898437 2006-05-18 > 00:00:00.000 > 2 60.707363128662102 2006-06-05 > 00:00:00.000 > 2 60.707363128662109 2006-06-05 > 09:00:00.000 > 2 60.707363128662109 2006-06-05 > 10:00:00.000 > > > > > I would like to present one row per entry in the AR table like this > > [Signaltext] [ Last hour] [Total] > Meter1 1200 40000 > Meter2 110 14000 > Meter3 1600 78000 > etc > > > Could anyone here get me started? > > regards > Henry > > >  |
|||||||||||||||||||||||
Other interesting topics