Question Can I split range into weeks - any hint would help

[Musab]

Hello all,

I hope this is not very complex for expert programmers as it is for me. Any hint regarding this would help. Here is my case:

- I have a table in database with borrowed books and the date it has been borrowed.

- In my web application I need to view borrowed books in a given range (FromDate --> ToDate)

- I have to problems here:

after the user enters the date range, I need to split the results per week cause I'm building a chart based on weekly borrowed book. for exmaple

The user enters From: 5/1/2010 To: 5/9/2010
I need a way to output Issues in period 5/1/2010 - 5/7/2010
And issues on 5/8/2010 - 5/9/2010

Any idea where and how to start this ?
Thanks

 

[jmcilhinney]

How do you define a week? Does a week start on one specific day of the week or do you just count in sevens from the start date the user enters?

 

[Musab]

How do you define a week? Does a week start on one specific day of the week or do you just count in sevens from the start date the user enters?

Hi jmcilhinney,

I'll be counting 7 days from start date the user enters

 

[jmcilhinney]

E.g.

Private Sub DisplayWeeks(ByVal startDate As Date, ByVal endDate As Date)
    Dim weekStart As Date = startDate
    Dim weekEnd As Date

    Do
        weekEnd = weekStart.AddDays(6)

        If weekEnd > endDate Then
            weekEnd = endDate
        End If

        MessageBox.Show(String.Format("{0:d} to {1:d}", weekStart, weekEnd))

        weekStart = weekEnd.AddDays(1)
    Loop Until weekEnd = endDate
End Sub

 

[Musab]

Thanks a lot jmcilhinney.. works great

Here is my code if anyone needs it

'------------------Begin Charting-----------------------'

        Dim weekStart As Date = txtDateFrom.SelectedDate
        Dim weekEnd As Date
        Dim endDate As Date = txtDateTo.SelectedDate

        'Calculate Days for array allocation
        Dim weeks As Integer
        Dim tsTimeSpan As TimeSpan
        Dim iNumberOfDays As Integer
        tsTimeSpan = endDate.Subtract(weekStart)
        iNumberOfDays = tsTimeSpan.Days
        weeks = Math.Ceiling(iNumberOfDays / 7)

        Dim Qty(weeks - 1) As Integer
        Dim frmToDate(weeks - 1) As String
        Dim i As Integer = 0

        Do
            weekEnd = weekStart.AddDays(6)
            'Start SQL Commands
            sql = "SELECT COUNT(DateAccessioned) AS Qty FROM Reports WHERE "
            sql &= "Reports.DateAccessioned > '" & weekStart.ToString() & "' AND Reports.DateAccessioned < '" & weekEnd & "'"
            con.Open()
            cmd = New SqlCommand(sql, con)
            rd = cmd.ExecuteReader()
            While rd.Read()
                Qty(i) = rd.GetInt32(0)
            End While

            frmToDate(i) = weekStart & " To " & weekEnd
            con.Close()
            rd.Close()
            i += 1
            'End SQL Commands
            If weekEnd > endDate Then
                weekEnd = endDate
            End If
            weekStart = weekEnd.AddDays(1)
        Loop Until weekEnd = endDate

        'Creat the Chart
        Dim products As New List(Of Product)()
        For i = 0 To weeks - 1 Step +1
            products.Add(New Product(frmToDate(i), Qty(i)))
            'chartSeries1.AddItem(Qty(i), frmToDate(i))
        Next
        RadChart1.DataSource = products
        RadChart1.Series(0).DataYColumn = "QuantityInStock"
        RadChart1.PlotArea.XAxis.DataLabelsColumn = "Name"
        'RadChart1.PlotArea.XAxis.Appearance.TextAppearance.TextProperties.Font = New System.Drawing.Font("Arial", 8)
        RadChart1.PlotArea.XAxis.Appearance.LabelAppearance.RotationAngle = 300
        RadChart1.PlotArea.Appearance.Dimensions.Margins.Bottom = Telerik.Charting.Styles.Unit.Percentage(30)
        RadChart1.DataBind()

This will result in:

 

[rjbullock]

This is great, but how do I get the returned list of weeks to span Monday - Sunday instead of Saturday - Friday?

 

[cjard]

Thanks a lot jmcilhinney.. works great

Nooo.. Tell me you didnt just write a query that hammers the DB for data a week at a time?!

Here's how we'd solve the problem "How many sales were made on a weekly basis?" in Oracle:

SELECT
  trunc(saledate, 'WW') as week_commencing,
  count(*)
FROM
  sales
WHERE
  salesdate BETWEEN :startDate AND :endDate
GROUP BY
  trunc(salesdate, 'WW')

Note: Single hit of the DB, single table access, parameterized query

Now, if youre on SQLServer, it might not be able to truncate a date like Oracle can.. Convert it to a string form whose granularity stops at the week-of-year level

 

[cjard]

This is great, but how do I get the returned list of weeks to span Monday - Sunday instead of Saturday - Friday?

Given that Oracle truncates to the nearest Monday, we slide the date back some way relative to where we want week to start.

eg Wednesday (day 3) instead of Monday (day 1) we get:

SELECT
  trunc(saledate - 2, 'WW') + 2 as week_commencing,
  count(*)
FROM
  sales
WHERE
  salesdate BETWEEN :startDate AND :endDate
GROUP BY
  trunc(salesdate - 2, 'WW') + 2

Note the -2 +2
-2 = slide date 2 days back. round it down to nearest monday, add 2 back to make it back to wednesday..

 

[MattP]

This should give you some ideas for using T-SQL to accomplish your query.

SQLHacks - Dates - How to group by week with MSSQL

 

[Musab]

Thanks cjard, the DB says thank you as well lol.