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 |
mitin
Yak Posting Veteran
81 Posts |
Posted - 2013-02-17 : 06:15:01
|
Hi, please see the query I've posted below, which selects some fields from a table based on a certain date range, and also calculates the following fields:'NumberofEvents' - This should be the number of events for each individual eventid, if other values vary then it shouldn't affect this column, since it is adding up the tally of those rows with the same Eventid, it shouldn't matter if the other fields vary.'DayTotalByServer' - The total number of events for all rows with the same 'servername', calculated by adding up the tally field of those rows.'DayTotal' - The total of the tally column for all rows within the specified date range.Here is the query:use databaseselect distinct servername,firstoccurrence,lastoccurrence,eventid,key,group,node,summary,sum(tally) as NumberofEvents,sum(count(*)) over(partition by dateadd(dd,datediff(dd,0,lastoccurrence),0)) as daytotalsum(count(*)) over(partition by servername, dateadd(dd,datediff(dd,0,lastoccurrence),0)) as daytotalbyserverfrom databasetablewhere firstoccurrence >= '2013-01-16' and firstoccurrence < '2013-01-17'and lastoccurrence >= '2013-01-16' and lastoccurrence < '2013-01-16'group byfirstoccurrence,lastoccurrence,servername,eventid,alertkey,alertgroup,node,summaryorder by numberofevents desc The problem I have at the minute is that:1.) The 'NumberofEvents' field is not calculated how I want it to be, it calculates a tally for each row based on whether all the fields in the select statement are the same, as opposed to just the event ID being the same. Basically I want the rows to be returned with the eventid and other fields to go with it, but although the other fields may vary from row to row, if the eventid in 5 rows are the same for example, although the values in the other selected fields may vary for those rows, those rows should be given the same 'NumberofEvents' because this should be calculated based purely upon Eventid.2.) Also, I'm not sure whether the 'Daytotalbyserver' and 'daytotal' fields are being calculated accurately. 'Daytotal' should be a sum of the tally column based upon servername. Whilst 'daytotal' should be a sum of the tally column for all rows within the specified date range....I really really hope I have explained myself well enough here, please help if you can, im still new to SQL and trying to keep my head above water with a DB I have inherited.Help is greatly appreciated! thank you! |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-17 : 08:30:23
|
Without some sample input data and the desired output, it is hard to tell how you should write the query. The following will calculate the sums as I understood it. It will produce one row for every row in the databasetable that satisfy the where clause. Perhaps that is not what you want? Do you want one row per eventid or one row per server, or some other type of output?SELECT DISTINCT servername, firstoccurrence, lastoccurrence, eventid, [key] [group] , node, summary, SUM(tally) OVER(PARTITION BY eventid) AS NumberofEvents, SUM(tally) OVER( PARTITION BY DATEADD(dd, DATEDIFF(dd, 0, lastoccurrence), 0) ) AS daytotal, SUM(tally) OVER( PARTITION BY servername, DATEADD(dd, DATEDIFF(dd, 0, lastoccurrence), 0) ) AS daytotalbyserverFROM databasetableWHERE firstoccurrence >= '2013-01-16' AND firstoccurrence < '2013-01-17' AND lastoccurrence >= '2013-01-16' AND lastoccurrence < '2013-01-16'ORDER BY numberofevents DESC |
|
|
mitin
Yak Posting Veteran
81 Posts |
Posted - 2013-02-17 : 12:12:23
|
Thanks for the reply James, I want a total for each eventid, however the same eventid might (or definately will actually) come from different servers , and might have other values that are different(summary, key, node etc), I still want these differing rows to be displayed, however since they have the same event id, the 'numberofevents' which is the total for that eventid over all servers, should be the same... there should then be a different total for eventid's for each servername (totalby server), and a grand total of all the event id's added togther (daytotal). This is all using the tally fielddoes this make sense?thanks |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-17 : 12:27:00
|
Can you post some sample data and the corresponding output that you want to get? For me (and probably for many others on the forum), looking at the data is more meaningful and easier to understand than description in words. Does the query I posted earlier give you wrong results? |
|
|
mitin
Yak Posting Veteran
81 Posts |
Posted - 2013-02-17 : 13:24:40
|
Hi James, sorry but I can't test your query right now. Will be able to tomorrow though :)Here is some sample data with the results I want, I hope it makes things a bit clearerservername firstoccurrence lastoccurrence eventid key group node summary tally abiobjcl 16/01/2013 21:28 16/01/2013 21:29 41 key1 group1 node1 critical alert 1 abiobjcl 16/01/2013 22:28 16/01/2013 23:28 41 key1 group2 node1 critical alert 1 abiobjcl 16/01/2013 23:28 16/01/2013 21:28 41 key2 group1 node1 critical alert 1 lchobjcl 16/01/2013 21:28 16/01/2013 21:28 45 key5 group2 node4 caution alert 1 lchobjcl 16/01/2013 21:28 16/01/2013 21:29 45 key5 group2 node4 caution alert 1 servername firstoccurrence lastoccurrence eventid key group node summary noofevents daytotalbyserver daytotalabiobjcl 16/01/2013 21:28 16/01/2013 21:29 41 key1 group1 node1 critical alert 3 3 5abiobjcl 16/01/2013 22:28 16/01/2013 23:28 41 key1 group2 node1 critical alert 3 3 5abiobjcl 16/01/2013 21:28 16/01/2013 21:28 41 key2 group1 node1 critical alert 3 3 5lchobjcl 16/01/2013 21:28 16/01/2013 21:28 45 key5 group2 node4 caution alert 2 2 5 I have also just realised that I don't actually want the firstoccurence and lastoccurrence fields to be selected, since this will return too may rows, as these values will always be unique to each record. |
|
|
mitin
Yak Posting Veteran
81 Posts |
Posted - 2013-02-17 : 13:46:04
|
sorry, just reformatted the example data above, hopefully thats clearer :) |
|
|
mitin
Yak Posting Veteran
81 Posts |
Posted - 2013-02-17 : 16:09:43
|
make sense? :) |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-17 : 17:41:38
|
Got it. So the query I posted earlier with one change should work:SELECT DISTINCT servername, firstoccurrence, lastoccurrence, eventid, [key] [group] , node, summary, SUM(tally) OVER(PARTITION BY eventid,servername) AS NumberofEvents, SUM(tally) OVER( PARTITION BY DATEADD(dd, DATEDIFF(dd, 0, lastoccurrence), 0) ) AS daytotal, SUM(tally) OVER( PARTITION BY servername, DATEADD(dd, DATEDIFF(dd, 0, lastoccurrence), 0) ) AS daytotalbyserverFROM databasetableWHERE firstoccurrence >= '2013-01-16' AND firstoccurrence < '2013-01-17' AND lastoccurrence >= '2013-01-16' AND lastoccurrence < '2013-01-16'ORDER BY numberofevents DESC |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-17 : 23:21:56
|
quote: Originally posted by James K Got it. So the query I posted earlier with one change should work:SELECT DISTINCT servername, firstoccurrence, lastoccurrence, eventid, [key] [group] , node, summary, SUM(tally) OVER(PARTITION BY eventid,servername) AS NumberofEvents, SUM(tally) OVER( PARTITION BY DATEADD(dd, DATEDIFF(dd, 0, lastoccurrence), 0) ) AS daytotal, SUM(tally) OVER( PARTITION BY servername, DATEADD(dd, DATEDIFF(dd, 0, lastoccurrence), 0) ) AS daytotalbyserverFROM databasetableWHERE firstoccurrence >= '2013-01-16' AND firstoccurrence < '2013-01-17' AND lastoccurrence >= '2013-01-16' AND lastoccurrence < '2013-01-17'ORDER BY numberofevents DESC
couple of small typosAlso @OPwhy is last row of sample data missing from the output? is it intentional?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
mitin
Yak Posting Veteran
81 Posts |
Posted - 2013-02-18 : 04:07:02
|
Hi, yes its intentional, because those last two rows in the example data are the same, apart from the firstoccurrence/lastoccurrence fields, which I don't actually want included in my select anymore.... |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-02-18 : 04:13:22
|
Ok..then James's suggestion with my last modification should work for you------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|