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.
| Author |
Topic |
|
attman
Starting Member
16 Posts |
 Posted - 2009-02-27 : 12:41:05
|
| my tableid device_id dateTime values __ ___________ ____________1 - 1 - 01.01.2009 07:00- 152 - 1 - 01.01.2009 11:00 - 20 3 - 1 - 01.01.2009 15:00 - 28 4 - 1 - 01.01.2009 23:00 - 35 5 - 1 - 02.01.2009 06:00 - 50 7 - 1 - 02.01.2009 10:00 - 71 8 - 1 - 02.01.2009 18:00 - 80 9 - 1 - 03.01.2009 07:00 - 90 10 - 1 -03.01.2009 14:00 - 125 11 - 1 -03.01.2009 16:00 - 128 haw to :02.01.2009 last values - 01.012009 last values = 80-35=45 ???03.01.2009 last values - 01.01.2009 first values = 128-15=113 |
|
|
vijayisonly
Master Smack Fu Yak Hacker
1836 Posts |
Posted - 2009-02-27 : 13:28:08
|
| Try this..select (r.values - t.values )from (select rank() over(partition by dateTime order by dateTime desc) as ranknum,* from urtable where convert(char,dateTime,101) = '01/01/2009') t inner join(select rank() over(partition by dateTime order by dateTime desc) as ranknum,* from urtable where convert(char,dateTime,101) = '02/01/2009') ron r.deviceid = t.deviceid and r.ranknum = 1 and t.ranknum = 1this will give 02.01.2009 last values - 01.012009 last values = 80-35=45 ???If it works, change it accordingly to get the other value as well.But I'm sure someone will give you a better solution than this one. |
 |
|
|
attman
Starting Member
16 Posts |
Posted - 2009-02-27 : 13:54:53
|
my question is wrong.i want to "today last record - previously day last record for one day values and " today last record - last record of last day of previousl month for montly valuues.[ so weeks and years values vs.. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-03-02 : 10:43:11
|
| please post what your expected output is |
|
|
|
|
|
|
dayly calculation