CODEWITHSUNDEEP
sqlasp.netvb.netdb2
kodym
kodym

Reputation: 47

DB2 SQL Query to group by week

I am writing an ASP.NET web form page using VB.Net. I am writing code to use on a line graph, but I can't seem to get my query to group by week. Here is my query:

SELECT F42119LA.SDMCU || '-' || F42119LA.SDLNTY AS BranchCode,
       AVG(F42119la.SDIVD-F42119LA.SDDRQJ) AS Days,
       WEEK(SDTRDJ) AS Day 
FROM KAI400.KAIPRDDTA.EXCHBYDATE EXCHBYDATE,
     KAI400.KAIPRDDTA.F42119L14 F42119LA 
WHERE F42119LA.SDBCRC = EXCHBYDATE.CXCRCD
  AND EXCHBYDATE.EXCHDATE = F42119LA.SDTRDJ
  AND F42119LA.SDTRDJ>='118006'
  AND F42119LA.SDTRDJ<='118096'
  AND F42119LA.SDNXTR<>'999'
  AND SDIVD <> 0
  AND SDDRQJ <> 0
  AND F42119LA.SDAEXP <> 0
  AND EXCHBYDATE.CXCRDC='USD'
  AND F42119LA.SDLNTY IN ('S','W')
  AND (SDMCU LIKE '%100' OR SDMCU LIKE '%150') 
GROUP BY SDMCU,
         SDLNTY,
         SDIVD,
         F42119LA.SDMCU || '-' || F42119LA.SDLNTY,
         WEEK(SDTRDJ) 
ORDER BY SDIVD,
         SDMCU,
         SDLNTY

and this is the code the sql string runs through:

Public Shared Function GetMyDataTableString(SqlString As String, Optional IncDb As Integer = 0) As DataTable
    Dim MyConn As OleDbConnection = GetMyConn(IncDb)
    Dim DbCmd As New OleDbCommand(SqlString, MyConn)
    Dim ReturnDataTable As New DataTable
    Try
        If Not MyConn.State = ConnectionState.Open Then
            MyConn.Open()
        End If
        Dim Reader As OleDbDataReader = DbCmd.ExecuteReader(CommandBehavior.CloseConnection)
        Using Reader
            ReturnDataTable.Load(Reader)
        End Using

    Catch ex As Exception
        LogSqlErrors(SqlString, "GetMyDataTableString " & ex.Message.ToString(), IncDb)
        If HttpContext.Current.Session("SITEADMIN") = "True" Then
        HttpContext.Current.Response.Write("<b>OleFun.GetMyDataTableString, datatable failed</b>---<br />" & ex.ToString)
        End If
    Finally 'Happens regardless of failing or succeeding
        MyConn.Close()
    End Try
    Return ReturnDataTable
End Function

Whenever I use WEEK(), it gives me this error: 

Value in date, time, or timestamp string not valid

ONDATE is a date field in format MM/DD/YYYY. Does anyone know another way to group by week or what might can be giving me this error? Thanks in advance for your responses.

Upvotes: 1

Views: 1735

Answers (3)

ReemRashwan
ReemRashwan

Reputation: 371

In DB2, you can group days in same week using following methods:

  1. DATE_PART('WEEK', dateColumn) -> week starts at Saturday
  2. WEEK(dateColumn) -> week starts at Sunday
  3. WEEK_ISO(dateColumn) -> week starts at Monday
  4. DAYS(dateColumn) / 7 -> week starts at Sunday

Notes:

  • I believe that they work with columns of type DATE as will as TIMESTAMP.
  • "DAYS(dateColumn) / 7" doesn't get you week number, however it is helpful in grouping by week.
  • Kindly check your week start day as they differ in their results as following:

Comparison between week calculation methods

Upvotes: 0

Paul Vernon
Paul Vernon

Reputation: 3901

DATEPART() might be a function in SQL Server, but it not a function in Db2. You could use WEEK() or WEEK_ISO() which are Db2 functions. https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0005481.html

You could also use EXTRACT if you are on a recent version of Db2 LUW https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0053629.html

db2 "values extract(WEEK FROM  current date)"

1          
-----------
         14

1 record(s) selected.

BTW you don't need to group by the concatenation of F42119LA.SDMCU || '-' || F42119LA.SDLNTY, you can group by those columns individually, and only concat in the SELECT.

Upvotes: 0

Brad
Brad

Reputation: 3601

-- date part takes the part of the date as first paramater:
-- you have:
DATEPART(SDTRDJ, wk)

-- needs to be:
DATEPART(wk, SDTRDJ)

Upvotes: 1

Related Questions