-
Sep 14th, 2020, 07:31 AM
#1
Thread Starter
Fanatic Member
[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
-
Sep 14th, 2020, 08:11 AM
#2
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
-
Sep 14th, 2020, 08:23 AM
#3
Thread Starter
Fanatic Member
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.
-
Sep 14th, 2020, 08:40 AM
#4
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
-
Sep 15th, 2020, 01:01 AM
#5
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
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE 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.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Sep 15th, 2020, 03:40 AM
#6
Thread Starter
Fanatic Member
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.
-
Sep 16th, 2020, 12:53 AM
#7
Re: [RESOLVED] SQL - Query to list all year-to-date weeks and sum up occurrences of t
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE 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.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Sep 16th, 2020, 06:36 AM
#8
Thread Starter
Fanatic Member
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
-
Sep 18th, 2020, 01:15 AM
#9
Re: [RESOLVED] SQL - Query to list all year-to-date weeks and sum up occurrences of t
Originally Posted by Grand
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?
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE 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.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Sep 18th, 2020, 07:09 AM
#10
Re: [RESOLVED] SQL - Query to list all year-to-date weeks and sum up occurrences of t
Originally Posted by Zvoni
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
-
Sep 18th, 2020, 08:51 AM
#11
Re: [RESOLVED] SQL - Query to list all year-to-date weeks and sum up occurrences of t
Originally Posted by Grand
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.
-
Sep 18th, 2020, 09:14 AM
#12
Re: [RESOLVED] SQL - Query to list all year-to-date weeks and sum up occurrences of t
Originally Posted by ChrisE
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
-
Sep 18th, 2020, 09:39 AM
#13
Re: [RESOLVED] SQL - Query to list all year-to-date weeks and sum up occurrences of t
Originally Posted by techgnome
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|