Results 1 to 13 of 13

Thread: [RESOLVED] SQL - Query to list all year-to-date weeks and sum up occurrences of tasks for a week

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    437

    Resolved [RESOLVED] SQL - Query to list all year-to-date weeks and sum up occurrences of tasks for a week

    Hi
    I am not able to construct a query to perform the following:
    - I need to list all week numbers year-to-date; so, 1,2,3,... 34 and
    - Sum up how many tasks were created in each week and how many tasks were completed in that week in two different columns.

    My table contains weeknum for both created and completed tasks;

    My query so far:
    Code:
    Select   NG.CompletedDate_WeekNum As Weeknum, 
    Sum(Case When NG.Classification ='Major' And NG.SubmittedDate_WeekNum = 1 then 1 else 0 End) As CreatedMajor,
    Sum(Case When NG.Classification ='Major' And NG.CompletedDate_WeekNum = 1 then 1 else 0 End) As CompletedMajor
    
    FROM [DMS_DV_Data_NEW] As NG  
     
    Where  NG.Classification In ('Minor','Major','Unclassified') And 
    NG.IS_PLANNED_DEVIATION = 'NO'   And 
    ( Format(NG.SUBMITTED_DATE,'yyyy-MM-dd')  >= '2020-01-01' And 
    Format(NG.[Completed_DATE],'yyyy-MM-dd')  <= '2020-09-14')  And 
    NG.Staus = 'Closed' And 
    NG.Phase <> 'Cancelled' 
    Group by NG.CompletedDate_WeekNum 
    Order by NG.CompletedDate_WeekNum ASC
    Which apparently dose not do what I need.

    Thanks for any help

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,963

    Re: SQL - Query to list all year-to-date weeks and sum up occurrences of tasks for a

    You need the total number of the tasks submitted fopr each weeknum:
    Code:
    Select   NG.SubmittedDate_WeekNum As Weeknum, 
    Sum(Case When NG.Classification ='Major' then 1 else 0 End) As SubmittedMajor
    
    FROM [DMS_DV_Data_NEW] As NG  
     
    Where  NG.Classification In ('Minor','Major','Unclassified') And 
    NG.IS_PLANNED_DEVIATION = 'NO'   And 
    ( Format(NG.SUBMITTED_DATE,'yyyy-MM-dd')  >= '2020-01-01' And 
    Format(NG.[Completed_DATE],'yyyy-MM-dd')  <= '2020-09-14')  And 
    NG.Staus = 'Closed' And 
    NG.Phase <> 'Cancelled' 
    Group by NG.SubmittedDate_WeekNum
    And you need the number competed:
    Code:
    Select   NG.CompletedDate_WeekNum As Weeknum, 
    Sum(Case When NG.Classification ='Major' then 1 else 0 End) As CompletedMajor
    
    FROM [DMS_DV_Data_NEW] As NG  
     
    Where  NG.Classification In ('Minor','Major','Unclassified') And 
    NG.IS_PLANNED_DEVIATION = 'NO'   And 
    ( Format(NG.SUBMITTED_DATE,'yyyy-MM-dd')  >= '2020-01-01' And 
    Format(NG.[Completed_DATE],'yyyy-MM-dd')  <= '2020-09-14')  And 
    NG.Staus = 'Closed' And 
    NG.Phase <> 'Cancelled' 
    Group by NG.CompletedDate_WeekNum
    Now you put the two together, using the weeknum as the join:
    Code:
    Select *
    from (Select   NG.SubmittedDate_WeekNum As Weeknum, 
    Sum(Case When NG.Classification ='Major' then 1 else 0 End) As SubmittedMajor
    
    FROM [DMS_DV_Data_NEW] As NG  
     
    Where  NG.Classification In ('Minor','Major','Unclassified') And 
    NG.IS_PLANNED_DEVIATION = 'NO'   And 
    ( Format(NG.SUBMITTED_DATE,'yyyy-MM-dd')  >= '2020-01-01' And 
    Format(NG.[Completed_DATE],'yyyy-MM-dd')  <= '2020-09-14')  And 
    NG.Staus = 'Closed' And 
    NG.Phase <> 'Cancelled' 
    Group by NG.SubmittedDate_WeekNum ) SUBMITTED
    full outer join (Select   NG.CompletedDate_WeekNum As Weeknum, 
    Sum(Case When NG.Classification ='Major' then 1 else 0 End) As CompletedMajor
    
    FROM [DMS_DV_Data_NEW] As NG  
     
    Where  NG.Classification In ('Minor','Major','Unclassified') And 
    NG.IS_PLANNED_DEVIATION = 'NO'   And 
    ( Format(NG.SUBMITTED_DATE,'yyyy-MM-dd')  >= '2020-01-01' And 
    Format(NG.[Completed_DATE],'yyyy-MM-dd')  <= '2020-09-14')  And 
    NG.Staus = 'Closed' And 
    NG.Phase <> 'Cancelled' 
    Group by NG.CompletedDate_WeekNum ) COMPLETE on SUBMITTED.WeekNum = COMPLETE.WeekNum
    Shooting from the hip, without the data and an actual editor, that's how I'd do it... or that's how I'd start at least... go from there.
    Seems clunky... there's probably a way to optimize it...


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    437

    Re: SQL - Query to list all year-to-date weeks and sum up occurrences of tasks for a

    Thank you so much. It does what I need And special thanks for speedy response and explanation. I just had to modify a bit to start with
    Code:
    Select SUBMITTED.Weeknum,SubmittedMajor,CompletedMajor
    And all good.
    Great.

    Just one question though, will week numbers appear even though there are neither submitter or completed tasks for that week?
    Last edited by Grand; Sep 14th, 2020 at 08:34 AM.

  4. #4
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,963

    Re: [RESOLVED] SQL - Query to list all year-to-date weeks and sum up occurrences of t

    They will appear if they in either query... if there is a weekNum is missing from both, then it will not show.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,240

    Re: [RESOLVED] SQL - Query to list all year-to-date weeks and sum up occurrences of t

    If you need consecutive weeknumbers (if they have entries or not), then you have to first "produce" the weeknumbers as a "master".
    Then you can LEFT JOIN everything else to it
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    437

    Re: [RESOLVED] SQL - Query to list all year-to-date weeks and sum up occurrences of t

    Thanks. How will I produce that master thing. Can I produce that with a query or should be a table by itself.

  7. #7
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,240

    Re: [RESOLVED] SQL - Query to list all year-to-date weeks and sum up occurrences of t

    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  8. #8

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    437

    Re: [RESOLVED] SQL - Query to list all year-to-date weeks and sum up occurrences of t

    Thanks.
    I think, being lazy, I will just go ahead and create a permanent table containing numbers 1 to 53

  9. #9
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    2,240

    Re: [RESOLVED] SQL - Query to list all year-to-date weeks and sum up occurrences of t

    Quote Originally Posted by Grand View Post
    Thanks.
    I think, being lazy, I will just go ahead and create a permanent table containing numbers 1 to 53
    That's not the Problem.
    The Problem is: When is Week 1 (according to date)?
    Is it the Week having Jan 1st, or is it the week having the first Monday of the year?
    One System to rule them all, One IDE to find them,
    One Code to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    For health reasons i try to avoid reading unformatted Code

  10. #10
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,963

    Re: [RESOLVED] SQL - Query to list all year-to-date weeks and sum up occurrences of t

    Quote Originally Posted by Zvoni View Post
    That's not the Problem.
    The Problem is: When is Week 1 (according to date)?
    Is it the Week having Jan 1st, or is it the week having the first Monday of the year?
    That's not a problem either - he's already got a field for it in the database and storing it... so it's already been calculated and stored.


    Meanwhile *I* have concerns that he has two dates, and only one WeekNum field... which seems to correspond to one of the dattes, but not the other... and yet... using the other date to get the one weeknum.... so if CompletedDate and Submitted_Date are weeks apart, they will report the same CompletedDate_WeekNum in the result ...

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  11. #11
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,576

    Re: [RESOLVED] SQL - Query to list all year-to-date weeks and sum up occurrences of t

    Quote Originally Posted by Grand View Post
    Thanks.
    I think, being lazy, I will just go ahead and create a permanent table containing numbers 1 to 53
    don't be lazy
    here a start
    Code:
     Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            Dim nYear As Integer
            Dim nMonth As Integer
            Dim actDate As Date
            Dim dc As New System.Globalization.GregorianCalendar()
            Dim LastDayOfMonth
            Dim FirstDayOfMonth
            For nYear = 2020 To 2025
                Debug.Print("Start")
                For nMonth = 1 To 12
                    LastDayOfMonth = DateSerial(nYear, nMonth + 1, 0)
                    FirstDayOfMonth = DateSerial(nYear, nMonth, 1)
    
                    Debug.Print("First Day Of Month : " & Format(FirstDayOfMonth, "ddd dd.MM.yyyy") & Space(5) & "Week-Nr.:" & GetWeekfromDate(actDate.ToString("dd.MM.yyyy")))
    
                    For d = 1 To dc.GetDaysInMonth(nYear, nMonth)
                        actDate = DateSerial(nYear, nMonth, d)
    
                        'If Not (actDate.DayOfWeek = DayOfWeek.Sunday Or actDate.DayOfWeek = DayOfWeek.Saturday) Then
                        If actDate.DayOfWeek = DayOfWeek.Sunday OrElse actDate.DayOfWeek = DayOfWeek.Saturday Then
                            Debug.Print("is Weekend")
                        Else
                            Debug.Print("Day in month  : " & actDate.ToString("ddd dd.MM.yyyy") & Space(5) & "Week-Nr.:" & GetWeekfromDate(actDate.ToString("dd.MM.yyyy")))
                        End If
                    Next
                    Debug.Print("Last Day of Month  : " & Format(LastDayOfMonth, "ddd dd.MM.yyyy") & Space(5) & "Week-Nr.:" & GetWeekfromDate(actDate.ToString("dd.MM.yyyy")))
                    Debug.Print("---------------------------------------------------------------------")
    
                Next nMonth
            Next nYear
            Debug.Print("End")
        End Sub
    
    Function GetWeekfromDate(ByVal Datum As Date) As Byte
            Return CByte(DatePart(DateInterval.WeekOfYear, _
            Datum, FirstDayOfWeek.System, _
            FirstWeekOfYear.System))
        End Function
    the output...
    Code:
    Start
    First Day Of Month : Mi 01.01.2020     Week-Nr.:1
    Day in month  : Mi 01.01.2020     Week-Nr.:1
    Day in month  : Do 02.01.2020     Week-Nr.:1
    Day in month  : Fr 03.01.2020     Week-Nr.:1
    is Weekend
    is Weekend
    Day in month  : Mo 06.01.2020     Week-Nr.:2
    Day in month  : Di 07.01.2020     Week-Nr.:2
    Day in month  : Mi 08.01.2020     Week-Nr.:2
    Day in month  : Do 09.01.2020     Week-Nr.:2
    Day in month  : Fr 10.01.2020     Week-Nr.:2
    is Weekend
    is Weekend
    Day in month  : Mo 13.01.2020     Week-Nr.:3
    Day in month  : Di 14.01.2020     Week-Nr.:3
    Day in month  : Mi 15.01.2020     Week-Nr.:3
    Day in month  : Do 16.01.2020     Week-Nr.:3
    Day in month  : Fr 17.01.2020     Week-Nr.:3
    is Weekend
    is Weekend
    Day in month  : Mo 20.01.2020     Week-Nr.:4
    Day in month  : Di 21.01.2020     Week-Nr.:4
    Day in month  : Mi 22.01.2020     Week-Nr.:4
    Day in month  : Do 23.01.2020     Week-Nr.:4
    Day in month  : Fr 24.01.2020     Week-Nr.:4
    is Weekend
    is Weekend
    Day in month  : Mo 27.01.2020     Week-Nr.:5
    Day in month  : Di 28.01.2020     Week-Nr.:5
    Day in month  : Mi 29.01.2020     Week-Nr.:5
    Day in month  : Do 30.01.2020     Week-Nr.:5
    Day in month  : Fr 31.01.2020     Week-Nr.:5
    Last Day of Month  : Fr 31.01.2020     Week-Nr.:5
    ---------------------------------------------------------------------
    First Day Of Month : Sa 01.02.2020     Week-Nr.:5
    
    'etc.....
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  12. #12
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,963

    Re: [RESOLVED] SQL - Query to list all year-to-date weeks and sum up occurrences of t

    Quote Originally Posted by ChrisE View Post
    don't be lazy
    here a start
    Code:
     Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
            Dim nYear As Integer
            Dim nMonth As Integer
            Dim actDate As Date
            Dim dc As New System.Globalization.GregorianCalendar()
            Dim LastDayOfMonth
            Dim FirstDayOfMonth
            For nYear = 2020 To 2025
                Debug.Print("Start")
                For nMonth = 1 To 12
                    LastDayOfMonth = DateSerial(nYear, nMonth + 1, 0)
                    FirstDayOfMonth = DateSerial(nYear, nMonth, 1)
    
                    Debug.Print("First Day Of Month : " & Format(FirstDayOfMonth, "ddd dd.MM.yyyy") & Space(5) & "Week-Nr.:" & GetWeekfromDate(actDate.ToString("dd.MM.yyyy")))
    
                    For d = 1 To dc.GetDaysInMonth(nYear, nMonth)
                        actDate = DateSerial(nYear, nMonth, d)
    
                        'If Not (actDate.DayOfWeek = DayOfWeek.Sunday Or actDate.DayOfWeek = DayOfWeek.Saturday) Then
                        If actDate.DayOfWeek = DayOfWeek.Sunday OrElse actDate.DayOfWeek = DayOfWeek.Saturday Then
                            Debug.Print("is Weekend")
                        Else
                            Debug.Print("Day in month  : " & actDate.ToString("ddd dd.MM.yyyy") & Space(5) & "Week-Nr.:" & GetWeekfromDate(actDate.ToString("dd.MM.yyyy")))
                        End If
                    Next
                    Debug.Print("Last Day of Month  : " & Format(LastDayOfMonth, "ddd dd.MM.yyyy") & Space(5) & "Week-Nr.:" & GetWeekfromDate(actDate.ToString("dd.MM.yyyy")))
                    Debug.Print("---------------------------------------------------------------------")
    
                Next nMonth
            Next nYear
            Debug.Print("End")
        End Sub
    
    Function GetWeekfromDate(ByVal Datum As Date) As Byte
            Return CByte(DatePart(DateInterval.WeekOfYear, _
            Datum, FirstDayOfWeek.System, _
            FirstWeekOfYear.System))
        End Function
    the output...
    Code:
    Start
    First Day Of Month : Mi 01.01.2020     Week-Nr.:1
    Day in month  : Mi 01.01.2020     Week-Nr.:1
    Day in month  : Do 02.01.2020     Week-Nr.:1
    Day in month  : Fr 03.01.2020     Week-Nr.:1
    is Weekend
    is Weekend
    Day in month  : Mo 06.01.2020     Week-Nr.:2
    Day in month  : Di 07.01.2020     Week-Nr.:2
    Day in month  : Mi 08.01.2020     Week-Nr.:2
    Day in month  : Do 09.01.2020     Week-Nr.:2
    Day in month  : Fr 10.01.2020     Week-Nr.:2
    is Weekend
    is Weekend
    Day in month  : Mo 13.01.2020     Week-Nr.:3
    Day in month  : Di 14.01.2020     Week-Nr.:3
    Day in month  : Mi 15.01.2020     Week-Nr.:3
    Day in month  : Do 16.01.2020     Week-Nr.:3
    Day in month  : Fr 17.01.2020     Week-Nr.:3
    is Weekend
    is Weekend
    Day in month  : Mo 20.01.2020     Week-Nr.:4
    Day in month  : Di 21.01.2020     Week-Nr.:4
    Day in month  : Mi 22.01.2020     Week-Nr.:4
    Day in month  : Do 23.01.2020     Week-Nr.:4
    Day in month  : Fr 24.01.2020     Week-Nr.:4
    is Weekend
    is Weekend
    Day in month  : Mo 27.01.2020     Week-Nr.:5
    Day in month  : Di 28.01.2020     Week-Nr.:5
    Day in month  : Mi 29.01.2020     Week-Nr.:5
    Day in month  : Do 30.01.2020     Week-Nr.:5
    Day in month  : Fr 31.01.2020     Week-Nr.:5
    Last Day of Month  : Fr 31.01.2020     Week-Nr.:5
    ---------------------------------------------------------------------
    First Day Of Month : Sa 01.02.2020     Week-Nr.:5
    
    'etc.....
    1) He's already got the week number in the database, so it doesn't need to be recalculating it in the code.
    2) Jeezes... what's wrong with a table with the numbers in it like that, especially for QUERIES? Not everything needs or should be solved with VB code. Not everything is a nail. Some are staples, and some are screws.
    3) That said, I'd have done it as a table function. That's what we did at my last job... we had a table function that took one parameter and returned a table 1-X where x is your parameter value. It was pretty useful for a number of things.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  13. #13
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    2,576

    Re: [RESOLVED] SQL - Query to list all year-to-date weeks and sum up occurrences of t

    Quote Originally Posted by techgnome View Post
    1) He's already got the week number in the database, so it doesn't need to be recalculating it in the code.
    2) Jeezes... what's wrong with a table with the numbers in it like that, especially for QUERIES? Not everything needs or should be solved with VB code. Not everything is a nail. Some are staples, and some are screws.
    3) That said, I'd have done it as a table function. That's what we did at my last job... we had a table function that took one parameter and returned a table 1-X where x is your parameter value. It was pretty useful for a number of things.

    -tg
    I was board,
    sitting here on a 5 week Job with a 2-3 hour workday (a Bank in Frankfurt)
    seem's everybody is in Homeoffice, creepy sometimes
    this must be the most boring, but well paid Job in the last 5 years I've had
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

Posting Permissions

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



Click Here to Expand Forum to Full Width