Loading ...
Sorry, an error occurred while loading the content.
 

Need a elegant Sproc that does Avg,Total Hourly, Weekly, Daily, Monthly counts of some numbers

Expand Messages
  • Charles Carroll
    FieldName SomeDate SomeValue Blah 01/15/2011 1:02pm 207 Blah 01/15/2011 2:02pm 12 Blah 01/15/2011 2:14pm
    Message 1 of 3 , Nov 3 10:23 AM
      FieldName SomeDate SomeValue
      Blah 01/15/2011 1:02pm 207
      Blah 01/15/2011 2:02pm 12
      Blah 01/15/2011 2:14pm 33
      Blah 02/03/2011 12:03am 3
      Blah 02/03/2011 12:17am 9
      Blah 03/12/2011 9:02pm 45


      I need an Sproc that I pass 2 params:

      parm 1= Avg/Total
      parm 2=Hourlym Daily, Weekly, Monthly


      and the output for Total/Hourly would be:

      01/15/11 1pm 207
      01/15/2011 2pm 55
      02/03/2011 12am 12
      03/12/2011 9pm 45

      and of course ouput for Total/Monthly would be

      Jan 2011 252
      Feb 2011 12
      Mar 2011 45

      and of course output for Avg/Monthly would be

      Jan 2011 84
      Feb 2011 6
      Mar 2011 45

      I am starting the road to Googling and studying Celko's SQL for
      Smarties to formulate an efficient way to do this but if anyone has
      built this before and wants to give me a headstart please offer a
      Sproc that is close and I will dg in and make it work. I don't the
      best way to count and group by time in a dynamic way. Even after I get
      this working in long term I need to get it working by Fiscal Quarter
      and some custom time periods but the parameters above are a good
      starting point befre tackling custom stuff.
    • Charles Carroll
      Ok reading the followin articles which will hopefully help: http://www.sqlservercentral.com/articles/Advanced+Querying/3136/
      Message 2 of 3 , Nov 3 11:47 AM
        Ok reading the followin articles which will hopefully help:

        http://www.sqlservercentral.com/articles/Advanced+Querying/3136/
        http://www.sql-tutorial.net/SQL-GROUP-BY.asp
        http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server
        http://oreilly.com/catalog/transqlcook/chapter/ch08.html

        If anybody suggests any other articles to help me formulate this Sproc
        which btw will have to work for a dozen tables ideally wit parameters
        for table and field names so 1 Sproc rather than copy/paste/modify and
        6-12 nearly identical Sprocs except for Table input, field names and
        output table name.

        On Thu, Nov 3, 2011 at 1:23 PM, Charles Carroll <911@...> wrote:
        > FieldName  SomeDate                SomeValue
        > Blah           01/15/2011 1:02pm    207
        > Blah           01/15/2011 2:02pm    12
        > Blah           01/15/2011 2:14pm    33
        > Blah           02/03/2011 12:03am   3
        > Blah           02/03/2011 12:17am   9
        > Blah           03/12/2011 9:02pm     45
        >
        >
        > I need an Sproc that I pass 2 params:
        >
        > parm 1= Avg/Total
        > parm 2=Hourlym Daily, Weekly, Monthly
        >
        >
        > and the output for Total/Hourly would be:
        >
        > 01/15/11     1pm      207
        > 01/15/2011  2pm      55
        > 02/03/2011  12am    12
        > 03/12/2011   9pm     45
        >
        > and of course ouput for Total/Monthly would be
        >
        > Jan 2011    252
        > Feb 2011   12
        > Mar 2011    45
        >
        > and of course output for Avg/Monthly would be
        >
        > Jan 2011    84
        > Feb 2011   6
        > Mar 2011    45
        >
        > I am starting the road to Googling and studying Celko's SQL for
        > Smarties to formulate an efficient way to do this but if anyone has
        > built this before and wants to give me a headstart please offer a
        > Sproc that is close and I will dg in and make it work. I don't the
        > best way to count and group by time in a dynamic way. Even after I get
        > this working in long term I need to get it working by Fiscal Quarter
        > and some custom time periods but the parameters above are a good
        > starting point befre tackling custom stuff.
        >
      • Charles Carroll
        Ok problem solved. Created Time Table with Custom Buckets and use Joins and Views and Partition command against the views then there is elegant declarative
        Message 3 of 3 , Nov 4 8:16 AM
          Ok problem solved. Created Time Table with Custom Buckets and use
          Joins and Views and Partition command against the views then there is
          elegant declarative solution.

          On Thu, Nov 3, 2011 at 1:23 PM, Charles Carroll <911@...> wrote:
          > FieldName  SomeDate                SomeValue
          > Blah           01/15/2011 1:02pm    207
          > Blah           01/15/2011 2:02pm    12
          > Blah           01/15/2011 2:14pm    33
          > Blah           02/03/2011 12:03am   3
          > Blah           02/03/2011 12:17am   9
          > Blah           03/12/2011 9:02pm     45
          >
          >
          > I need an Sproc that I pass 2 params:
          >
          > parm 1= Avg/Total
          > parm 2=Hourlym Daily, Weekly, Monthly
          >
          >
          > and the output for Total/Hourly would be:
          >
          > 01/15/11     1pm      207
          > 01/15/2011  2pm      55
          > 02/03/2011  12am    12
          > 03/12/2011   9pm     45
          >
          > and of course ouput for Total/Monthly would be
          >
          > Jan 2011    252
          > Feb 2011   12
          > Mar 2011    45
          >
          > and of course output for Avg/Monthly would be
          >
          > Jan 2011    84
          > Feb 2011   6
          > Mar 2011    45
          >
          > I am starting the road to Googling and studying Celko's SQL for
          > Smarties to formulate an efficient way to do this but if anyone has
          > built this before and wants to give me a headstart please offer a
          > Sproc that is close and I will dg in and make it work. I don't the
          > best way to count and group by time in a dynamic way. Even after I get
          > this working in long term I need to get it working by Fiscal Quarter
          > and some custom time periods but the parameters above are a good
          > starting point befre tackling custom stuff.
          >
        Your message has been successfully submitted and would be delivered to recipients shortly.