SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Addict Shalin's Avatar
    Join Date
    Sep 2003
    Location
    __beyond
    Posts
    295
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL - how do I tackle this

    Hi,

    I have dataset which contains the data like this

    TranDate PurchaseVal
    ------------ -------------
    2003-01-01 51
    2003-01-02 52
    2003-01-03 54
    2003-01-04 53
    2003-01-05 54
    2003-01-06 56
    2003-01-07 58
    2003-01-08 52
    2003-01-09 51
    2003-01-10 55

    From this data I want weekly Info

    Week Value
    1 208
    2 ---
    3 ---

    How I can get this

    If I am using .Select("DatePart(week,TranDate)= " & 1)
    I'm gettin an error: DatePart not defined.

    Need Advice

    Shalin
    Help Desk Software - Helpdesk Pilot
    Flash Screensaver - Screenswift for Windows
    Online Polls - Tezaa.com
    Address Book Software - Addza for Windows

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    what is the period in front of .Select for? and why is the select list in parentheses?
    Code:
    select datepart(week,TranDate) as theWeek
         , sum(PurchaseVal) as theValue 
      from theTable
    group by datepart(week,TranDate)
    rudy
    http://r937.com/

  3. #3
    SitePoint Addict Shalin's Avatar
    Join Date
    Sep 2003
    Location
    __beyond
    Posts
    295
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This was written in VB .NET (ADO .net).

    we have a dataset which has all the data. only thing is I need to summarze and show it in week order.
    Help Desk Software - Helpdesk Pilot
    Flash Screensaver - Screenswift for Windows
    Online Polls - Tezaa.com
    Address Book Software - Addza for Windows

  4. #4
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Shalin, I'm guessing that you're using the DataTable.Select method, in which case the error is caused by trying to use the DatePart function. The DatePart function isn't supported in expressions, check the .Net docs for the DataColumn.Expression Property for the allowed operators and functions.

    It might be possible to use a combination of the Substring and Convert (ie DateTime to String) methods to get the week value and group by by this value. Alternatively, you could do it 'manually' by looping through the Rows calculating the summary values.

    If possible, it'd be simpler to use a separate SQL query to get the summary data.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    well, ya learn something new every day -- not that i'll remember it, though

    i don't know ASP.NET (nor ASP classic), but it seems rather strange that it would disallow a perfectly good SQL Server function when connecting to a SQL Server database -- or maybe i misunderstand the scenario

    thanks for jumping in, shane

    rudy

  6. #6
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Rudy, the DataTable object is a .Net object which respresents an in-memory table of data (any data, not necessarily a database table). The DataTable.Select method provides a means of querying the data in a DataTable but the functions that the query can use are fairly limited.

    Shalin's problem is really a .Net problem and not an SQL problem.

  7. #7
    SitePoint Enthusiast
    Join Date
    Sep 2003
    Location
    Daytona Beach
    Posts
    27
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you can do it with SQL actaully with the datepart.

    select datepart(w,TranDate),sum(PurchaseVal) from table

  8. #8
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Milton Keynes, UK
    Posts
    1,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    afterburn, have you actually read the posts in this thread?

    This can be done in SQL, as Rudy pointed out. Rudy also posted the correct SQL to do so (ie including the required GROUP BY clause).

    In my first post I pointed out the problem with Shalins code and possible ways around it, including using SQL to get the data, in which case Shalin could use the SQL posted by Rudy.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •