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 |
|
yasinirshad
Starting Member
18 Posts |
 Posted - 2010-06-27 : 07:13:26
|
| Hello,From this select query,SELECT '26/06/2010' as Date, max(hr)+1 hrFROM StackedChart where day(date_g)='26' and month(date_g)='06'group by statusorder by hrMy output is :Date hr26/06/2010 326/06/2010 726/06/2010 2126/06/2010 24How to get result as below :Date hr Difference26/06/2010 3 326/06/2010 7 426/06/2010 21 1426/06/2010 24 3What i need is a new column 'Differece', which comes by subtracting hr from previous hr i.e., 24-21 = 3, 21-7 = 14, 7-3=4, 3-0 = 3 ...assuming first row value is subtracted by '0' ...Can anyone pls get me sql query for this ...Thanks... |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-06-27 : 07:41:13
|
| [code]set dateformat dmydeclare @tbl as table(id int identity,dt datetime ,hr int)insert into @tblselect '26/06/2010', 3 union allselect '26/06/2010', 7 union allselect '26/06/2010', 21 union allselect '26/06/2010', 24select t1.dt,t1.hr,coalesce(t1.hr-T.hr,t1.hr) as difference from @tbl t1outer apply(select * from @tbl t2 where t1.id=t2.id+1)T[/code]Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless. PBUH |
 |
|
|
yasinirshad
Starting Member
18 Posts |
Posted - 2010-06-28 : 06:31:53
|
| Hi,Thanks for the reply... But i am not getting it ... Well i have a table which has data like this :ORIGINAL TABLE SNo Date hr Status1 26/06/2010 3 42 26/06/2010 3 43 26/06/2010 3 44 26/06/2010 7 55 26/06/2010 7 5 .. ......... .. .. (And so on) ...Ok now, in above table i have Sno,date,hr and status ... It means that Hr 3 has status 4 (3 times), hour 7 will have status 5 (7 times) and so on ....Now from this below select query,SELECT '26/06/2010' as Date, max(hr)+1 hrFROM StackedChart where day(date_g)='26' and month(date_g)='06'group by statusorder by hrI get below o/p ...My output is :Date hr26/06/2010 326/06/2010 726/06/2010 2126/06/2010 24How to get result as below : (FROM MY ORIGINAL TABLE LISTED ABOVE)Date hr Difference26/06/2010 3 326/06/2010 7 426/06/2010 21 1426/06/2010 24 3I TRIED IDERA'S QUERY, BUT not getting desired result ... below is query,select '26/06/2010' as Date,t1.hr,coalesce(t1.hr-T.hr,t1.hr) as difference from StackedChart t1outer apply(select * from StackedChart t2 where t1.id=t2.id+1)Twhere day(t1.date_g)='26' and month(t1.date_g)='06'group by t1.status,t1.hr,T.hrorder by t1.hrAnd output is :Date Hr Difference26/06/2010 0 026/06/2010 0 026/06/2010 1 126/06/2010 1 026/06/2010 2 126/06/2010 2 026/06/2010 3 126/06/2010 3 026/06/2010 4 126/06/2010 4 026/06/2010 5 126/06/2010 5 026/06/2010 6 126/06/2010 6 026/06/2010 7 126/06/2010 7 026/06/2010 8 126/06/2010 8 026/06/2010 9 126/06/2010 9 026/06/2010 10 126/06/2010 10 026/06/2010 11 126/06/2010 11 026/06/2010 12 126/06/2010 12 026/06/2010 13 126/06/2010 13 026/06/2010 14 126/06/2010 14 026/06/2010 15 126/06/2010 15 026/06/2010 16 126/06/2010 16 026/06/2010 17 126/06/2010 17 026/06/2010 18 126/06/2010 18 026/06/2010 19 126/06/2010 19 026/06/2010 20 126/06/2010 20 026/06/2010 21 126/06/2010 21 026/06/2010 22 126/06/2010 22 026/06/2010 23 126/06/2010 23 0As u see above, each record is repeating twice ... Kindly help. |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-06-28 : 06:58:56
|
What is the o/p of this query?select * from StackedChart Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless. PBUH |
|
|
|
yasinirshad
Starting Member
18 Posts |
Posted - 2010-06-28 : 07:06:11
|
| O/P of select * from StackedChart is :SNO Index Date Hr Status1 2 6/26/10 0:00 0 42 4 6/26/10 0:00 0 43 6 6/26/10 0:00 0 44 8 6/26/10 0:00 0 45 120 6/26/10 0:00 0 46 3 6/26/10 0:00 0 47 5 6/26/10 0:00 0 48 7 6/26/10 0:00 0 49 9 6/26/10 0:00 0 410 7 6/26/10 0:00 1 411 9 6/26/10 0:00 1 412 3 6/26/10 0:00 1 413 5 6/26/10 0:00 1 414 6 6/26/10 0:00 1 415 8 6/26/10 0:00 1 416 2 6/26/10 0:00 1 417 4 6/26/10 0:00 1 418 120 6/26/10 0:00 1 419 6 6/26/10 0:00 2 420 8 6/26/10 0:00 2 421 2 6/26/10 0:00 2 422 4 6/26/10 0:00 2 423 120 6/26/10 0:00 2 424 7 6/26/10 0:00 2 425 9 6/26/10 0:00 2 426 3 6/26/10 0:00 2 427 5 6/26/10 0:00 2 428 6 6/26/10 0:00 3 329 7 6/26/10 0:00 3 330 8 6/26/10 0:00 3 331 9 6/26/10 0:00 3 332 2 6/26/10 0:00 3 333 3 6/26/10 0:00 3 334 4 6/26/10 0:00 3 335 5 6/26/10 0:00 3 336 120 6/26/10 0:00 3 337 6 6/26/10 0:00 4 338 8 6/26/10 0:00 4 339 2 6/26/10 0:00 4 340 4 6/26/10 0:00 4 341 120 6/26/10 0:00 4 342 7 6/26/10 0:00 4 343 9 6/26/10 0:00 4 344 3 6/26/10 0:00 4 345 5 6/26/10 0:00 4 346 7 6/26/10 0:00 5 347 9 6/26/10 0:00 5 348 3 6/26/10 0:00 5 349 5 6/26/10 0:00 5 350 6 6/26/10 0:00 5 351 8 6/26/10 0:00 5 352 2 6/26/10 0:00 5 353 4 6/26/10 0:00 5 354 120 6/26/10 0:00 5 355 7 6/26/10 0:00 6 356 9 6/26/10 0:00 6 357 3 6/26/10 0:00 6 358 5 6/26/10 0:00 6 359 6 6/26/10 0:00 6 360 8 6/26/10 0:00 6 361 2 6/26/10 0:00 6 362 4 6/26/10 0:00 6 363 120 6/26/10 0:00 6 364 6 6/26/10 0:00 7 665 9 6/26/10 0:00 7 666 3 6/26/10 0:00 7 667 5 6/26/10 0:00 7 668 7 6/26/10 0:00 7 669 8 6/26/10 0:00 7 670 2 6/26/10 0:00 7 671 4 6/26/10 0:00 7 672 120 6/26/10 0:00 7 673 7 6/26/10 0:00 8 674 9 6/26/10 0:00 8 675 3 6/26/10 0:00 8 676 5 6/26/10 0:00 8 677 6 6/26/10 0:00 8 678 8 6/26/10 0:00 8 679 2 6/26/10 0:00 8 680 4 6/26/10 0:00 8 681 120 6/26/10 0:00 8 682 7 6/26/10 0:00 9 683 9 6/26/10 0:00 9 684 3 6/26/10 0:00 9 685 5 6/26/10 0:00 9 686 6 6/26/10 0:00 9 687 8 6/26/10 0:00 9 688 2 6/26/10 0:00 9 689 4 6/26/10 0:00 9 690 120 6/26/10 0:00 9 691 7 6/26/10 0:00 10 692 9 6/26/10 0:00 10 693 3 6/26/10 0:00 10 694 5 6/26/10 0:00 10 695 6 6/26/10 0:00 10 696 8 6/26/10 0:00 10 697 2 6/26/10 0:00 10 698 4 6/26/10 0:00 10 699 120 6/26/10 0:00 10 6100 6 6/26/10 0:00 11 6101 8 6/26/10 0:00 11 6102 2 6/26/10 0:00 11 6103 4 6/26/10 0:00 11 6104 120 6/26/10 0:00 11 6105 7 6/26/10 0:00 11 6106 9 6/26/10 0:00 11 6107 3 6/26/10 0:00 11 6108 5 6/26/10 0:00 11 6109 6 6/26/10 0:00 12 6110 8 6/26/10 0:00 12 6111 2 6/26/10 0:00 12 6112 4 6/26/10 0:00 12 6113 120 6/26/10 0:00 12 6114 7 6/26/10 0:00 12 6115 9 6/26/10 0:00 12 6116 3 6/26/10 0:00 12 6117 5 6/26/10 0:00 12 6118 7 6/26/10 0:00 13 6119 9 6/26/10 0:00 13 6120 3 6/26/10 0:00 13 6121 5 6/26/10 0:00 13 6122 6 6/26/10 0:00 13 6123 8 6/26/10 0:00 13 6124 2 6/26/10 0:00 13 6125 4 6/26/10 0:00 13 6126 120 6/26/10 0:00 13 6127 7 6/26/10 0:00 14 6128 9 6/26/10 0:00 14 6129 3 6/26/10 0:00 14 6130 5 6/26/10 0:00 14 6131 6 6/26/10 0:00 14 6132 8 6/26/10 0:00 14 6133 2 6/26/10 0:00 14 6134 4 6/26/10 0:00 14 6135 120 6/26/10 0:00 14 6136 6 6/26/10 0:00 15 6137 8 6/26/10 0:00 15 6138 2 6/26/10 0:00 15 6139 4 6/26/10 0:00 15 6140 9 6/26/10 0:00 15 6141 7 6/26/10 0:00 15 6142 120 6/26/10 0:00 15 6143 3 6/26/10 0:00 15 6144 5 6/26/10 0:00 15 6145 6 6/26/10 0:00 16 6146 8 6/26/10 0:00 16 6147 2 6/26/10 0:00 16 6148 4 6/26/10 0:00 16 6149 9 6/26/10 0:00 16 6150 7 6/26/10 0:00 16 6151 120 6/26/10 0:00 16 6152 3 6/26/10 0:00 16 6153 5 6/26/10 0:00 16 6154 6 6/26/10 0:00 17 6155 8 6/26/10 0:00 17 6156 2 6/26/10 0:00 17 6157 4 6/26/10 0:00 17 6158 9 6/26/10 0:00 17 6159 7 6/26/10 0:00 17 6160 120 6/26/10 0:00 17 6161 3 6/26/10 0:00 17 6162 5 6/26/10 0:00 17 6163 6 6/26/10 0:00 18 6164 7 6/26/10 0:00 18 6165 8 6/26/10 0:00 18 6166 120 6/26/10 0:00 18 6167 2 6/26/10 0:00 18 6168 3 6/26/10 0:00 18 6169 9 6/26/10 0:00 18 6170 4 6/26/10 0:00 18 6171 5 6/26/10 0:00 18 6172 6 6/26/10 0:00 19 6173 8 6/26/10 0:00 19 6174 2 6/26/10 0:00 19 6175 9 6/26/10 0:00 19 6176 5 6/26/10 0:00 19 6177 7 6/26/10 0:00 19 6178 120 6/26/10 0:00 19 6179 3 6/26/10 0:00 19 6180 4 6/26/10 0:00 19 6181 7 6/26/10 0:00 20 6182 120 6/26/10 0:00 20 6183 3 6/26/10 0:00 20 6184 4 6/26/10 0:00 20 6185 6 6/26/10 0:00 20 6186 8 6/26/10 0:00 20 6187 2 6/26/10 0:00 20 6188 9 6/26/10 0:00 20 6189 5 6/26/10 0:00 20 6190 6 6/26/10 0:00 21 5191 8 6/26/10 0:00 21 5192 2 6/26/10 0:00 21 5193 9 6/26/10 0:00 21 5194 5 6/26/10 0:00 21 5195 7 6/26/10 0:00 21 5196 120 6/26/10 0:00 21 5197 3 6/26/10 0:00 21 5198 4 6/26/10 0:00 21 5199 6 6/26/10 0:00 22 5200 8 6/26/10 0:00 22 5201 2 6/26/10 0:00 22 5202 9 6/26/10 0:00 22 5203 5 6/26/10 0:00 22 5204 7 6/26/10 0:00 22 5205 120 6/26/10 0:00 22 5206 3 6/26/10 0:00 22 5207 4 6/26/10 0:00 22 5208 6 6/26/10 0:00 23 5209 8 6/26/10 0:00 23 5210 2 6/26/10 0:00 23 5211 9 6/26/10 0:00 23 5212 5 6/26/10 0:00 23 5213 7 6/26/10 0:00 23 5214 120 6/26/10 0:00 23 5215 3 6/26/10 0:00 23 5216 4 6/26/10 0:00 23 5217 5 6/27/10 0:00 0 4218 4 6/27/10 0:00 1 5219 6 6/27/10 0:00 1 5220 5 6/27/10 0:00 1 5This is just a partial list for 26 June 2010 and some of 27th June data ... Table will have data starting from March 01 till date ... |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-06-28 : 07:16:30
|
I ran the query for the first 10 records of the data you posted.set dateformat mdydeclare @tbl as table(id int ,hr int,Indexdt datetime,Status int)insert into @tblselect 1 ,2, '6/26/10 00:00:00' ,4 union allselect 2 ,4, '6/26/10 00:00:00' ,4 union allselect 3 ,6, '6/26/10 00:00:00' ,4 union allselect 4 ,8, '6/26/10 00:00:00' ,4 union allselect 5 ,120, '6/26/10 00:00:00',4 union allselect 6 ,3, '6/26/10 00:00:00' ,4 union allselect 7 ,5, '6/26/10 00:00:00' ,4 union allselect 8 ,7, '6/26/10 00:00:00' ,4 union allselect 9 ,9, '6/26/10 00:00:00' ,4 union allselect 10 ,7, '6/26/10 00:00:00' ,4select * from @tblselect t1.Indexdt,t1.hr,coalesce(t1.hr-T.hr,t1.hr) as difference from @tbl t1outer apply(select * from @tbl t2 where t1.id=t2.id+1)T OUTPUTIndexdt hr difference2010-06-26 00:00:00.000 2 22010-06-26 00:00:00.000 4 22010-06-26 00:00:00.000 6 22010-06-26 00:00:00.000 8 22010-06-26 00:00:00.000 120 1122010-06-26 00:00:00.000 3 -1172010-06-26 00:00:00.000 5 22010-06-26 00:00:00.000 7 22010-06-26 00:00:00.000 9 22010-06-26 00:00:00.000 7 -2 Now tell me what is wrong with the above o/p?Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless. PBUH |
|
|
|
yasinirshad
Starting Member
18 Posts |
Posted - 2010-06-29 : 01:12:04
|
| Hello Idera,Thanks ... But my problem is this ...I tried to run with below query which is my original scenario ...set dateformat mdydeclare @tbl as table(id int ,hr int,Indexdt datetime,Status int)insert into @tblselect 1 ,12, '6/26/10 00:00:00' ,4 union allselect 2 ,14, '6/26/10 00:00:00' ,4 union allselect 3 ,14, '6/26/10 00:00:00' ,4 union allselect 4 ,24, '6/26/10 00:00:00' ,3 union allselect 5 ,6, '6/27/10 00:00:00',3 union allselect 6 ,6, '6/27/10 00:00:00' ,3 union allselect 7 ,6, '6/27/10 00:00:00' ,3 union allselect 8 ,24, '6/27/10 00:00:00' ,3 union allselect 9 ,19, '6/28/10 00:00:00' ,3 union allselect 10 ,24, '6/28/10 00:00:00' ,3Please Note above i have HRS repeating for everyday ... i.e., for 6/26/10 i have 12,14,14,24 and for 6/27/10 i got 6,6,6,24 and so on ... The data in my original table exists in this way ...select * from @tblselect t1.id,t1.Indexdt,t1.hr,t1.Status,coalesce(t1.hr-T.hr,t1.hr) as difference from @tbl t1outer apply(select * from @tbl t2 where t1.id=t2.id+1)TAnd O/P is :1 2010-06-26 00:00:00.000 12 4 122 2010-06-26 00:00:00.000 14 4 23 2010-06-26 00:00:00.000 14 4 04 2010-06-26 00:00:00.000 24 3 105 2010-06-27 00:00:00.000 6 3 -186 2010-06-27 00:00:00.000 6 3 07 2010-06-27 00:00:00.000 6 3 08 2010-06-27 00:00:00.000 24 3 189 2010-06-28 00:00:00.000 19 3 -510 2010-06-28 00:00:00.000 24 3 5Ok now i have 2 issues with above o/p ...1.) If Hrs are repeating for same status then i dont want them to be selected (ex: ID3, ID 6 and ID 7 in above o/p) i.e., how to select distinct values (group by Status) ...2.) When there is a change in date (from 2010-06-26 to 2010-06-27), i see difference is negative .. For ex: in ID 5 difference is -18 (6-24=-18) ... but i want ID 5 Difference to be 6 as its start of new day ... same way difference for ID 9 should be 19 ...I need this to plot bar chart ...Thanks to help !!! |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-06-29 : 03:33:52
|
| [code]select * from(select t1.id,t1.Indexdt,t1.hr,t1.Status,coalesce(t1.hr-T.hr,t1.hr) as differencefrom @tbl t1outer apply(select top 1 hr from @tbl t2 where t1.id=t2.id+1 and t1.Indexdt=t2.Indexdt)T)T where difference<>0[/code]Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless. PBUH |
|
|
|
yasinirshad
Starting Member
18 Posts |
Posted - 2010-06-29 : 04:21:24
|
| Hi,Thanks a lot for replyn me n sorry but i ddnt get wat i want ... below is my o/p on running ur query ..ID Date Hour Status Difference10 2010-06-26 00:00:00.000 1 4 119 2010-06-26 00:00:00.000 2 4 128 2010-06-26 00:00:00.000 3 3 137 2010-06-26 00:00:00.000 4 3 146 2010-06-26 00:00:00.000 5 3 155 2010-06-26 00:00:00.000 6 3 164 2010-06-26 00:00:00.000 7 6 173 2010-06-26 00:00:00.000 8 6 182 2010-06-26 00:00:00.000 9 6 191 2010-06-26 00:00:00.000 10 6 1100 2010-06-26 00:00:00.000 11 6 1109 2010-06-26 00:00:00.000 12 6 1118 2010-06-26 00:00:00.000 13 6 1127 2010-06-26 00:00:00.000 14 6 1136 2010-06-26 00:00:00.000 15 6 1145 2010-06-26 00:00:00.000 16 6 1154 2010-06-26 00:00:00.000 17 6 1163 2010-06-26 00:00:00.000 18 6 1172 2010-06-26 00:00:00.000 19 6 1181 2010-06-26 00:00:00.000 20 6 1190 2010-06-26 00:00:00.000 21 5 1199 2010-06-26 00:00:00.000 22 5 1208 2010-06-26 00:00:00.000 23 5 1218 2010-06-27 00:00:00.000 1 5 1Now in my original table, i have hours starting from 0 to 23, but in above output hr 0 is ignored ... maybe b'coz of the condition where difference<>0 its not coming ... so how can we make it to come ...Can you pls pls get me a query which will result in below o/p : Date Status Status_Add26/06/2010 4 226/06/2010 3 426/06/2010 6 1426/06/2010 5 327/06/2010 5 1I need o/p to have Date,Status and Status_Add (Status_Add is number of times the status appears for a particular day (26/6/2010)... As we see from first o/p Status '4' is repeated '2' times, Status '3' is repeated '4' times, status '6' for '14' times & so on (Then Status '5' for '1' time for DATE 27/6/2010 which is next day)... Now when we add this (2+4+14+3 it should come to '24' (24 hour period) but here it comes to '23' mayb bcoz of the condition "where difference<>0")...This is my last request ... Kindly help me out ... Hope i get it ...Thanks once again !!! |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-06-29 : 05:41:54
|
See you are simply changing your requirements from time to time.First you said.quote:
If Hrs are repeating for same status then i dont want them to be selected
Now with this data10 2010-06-26 00:00:00.000 1 4 119 2010-06-26 00:00:00.000 2 4 128 2010-06-26 00:00:00.000 3 3 137 2010-06-26 00:00:00.000 4 3 146 2010-06-26 00:00:00.000 5 3 155 2010-06-26 00:00:00.000 6 3 164 2010-06-26 00:00:00.000 7 6 173 2010-06-26 00:00:00.000 8 6 182 2010-06-26 00:00:00.000 9 6 1 you are saying that the o/p is wrong.In the above data the hrs are not repeating with same status.That is why the difference is displayed as 1.Please clarify it.As for your second requirement here is the queryselect Indexdt,status,COUNT(status)as cnt from @tbl group by Indexdt,Statusorder by Indexdt Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless. PBUH |
|
|
|
yasinirshad
Starting Member
18 Posts |
Posted - 2010-06-29 : 06:16:17
|
| Really sorry for that...my bad ...anywayz with the query u gave,select date_g,status,COUNT(hr)as cnt from StackedChart group by date_g,Statusorder by date_gi got2010-06-26 00:00:00.000 3 362010-06-26 00:00:00.000 4 272010-06-26 00:00:00.000 5 272010-06-26 00:00:00.000 6 1262010-06-27 00:00:00.000 4 12010-06-27 00:00:00.000 5 3which is not wat i want...Ok ... Now with this dataID Date Hour Status10 2010-06-26 00:00:00.000 1 4 19 2010-06-26 00:00:00.000 2 4 28 2010-06-26 00:00:00.000 2 4 37 2010-06-26 00:00:00.000 3 3 46 2010-06-26 00:00:00.000 3 3 55 2010-06-26 00:00:00.000 3 3 64 2010-06-26 00:00:00.000 4 6 73 2010-06-26 00:00:00.000 5 6 82 2010-06-26 00:00:00.000 5 6 83 2010-06-27 00:00:00.000 1 6 84 2010-06-27 00:00:00.000 1 6 I need result like below Date Status No_Of_Times_Status_For_Hrs2010-06-26 00:00:00.000 4 22010-06-26 00:00:00.000 3 12010-06-26 00:00:00.000 6 22010-06-27 00:00:00.000 6 1So, Status '4' occurs '2' times for 26/6/2010 on Hours '1','2' ...Status '3 occurs '1 time for 26/6/2010 on Hours '3' ...Status '6 occurs '2' times for 26/6/2010 on Hours '4,'5 ...And againStatus '6 occurs '1 time for 27/6/2010 on Hours '1' ...Thanks ... |
|
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-06-29 : 06:53:26
|
Just change the query to thisselect Indexdt,status,COUNT(distinct hr)as cnt from @tbl group by Indexdt,Statusorder by Indexdt Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless. PBUH |
|
|
|
yasinirshad
Starting Member
18 Posts |
Posted - 2010-06-29 : 07:23:18
|
| Thanks a lot... am almost near to what i want from this query:select date_g,status,COUNT(distinct hr)as cnt from StackedChart group by date_g,Statusorder by date_gMy o/p is : Date Status cnt2010-06-26 00:00:00.000 3 42010-06-26 00:00:00.000 4 32010-06-26 00:00:00.000 5 32010-06-26 00:00:00.000 6 14Here i feel Status is sorted 3,4,5,6... but in my table its of the order 4,3,6,5So how can my o/p be like below : Date Status cnt2010-06-26 00:00:00.000 4 32010-06-26 00:00:00.000 3 42010-06-26 00:00:00.000 6 142010-06-26 00:00:00.000 5 3Thanks Again !!! |
|
|
|
|
|
|
|
|
Sql Query - Subtract row from previous row ...