Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Mattn
Starting Member
2 Posts |
 Posted - 2006-11-14 : 20:02:38
|
| I am trying to automaticaly code so that I can get outstanding quantitys per week per month for an entire yearHere is the code I have written but it will not work over a Year-endIF ( [BPW_Ewos_Canada].[Sales].[Invoice date]>= ( year(getdate())*10000 + month(getdate())*100 + day(0))and [BPW_Ewos_Canada].[Sales].[Invoice date] <= ( year(getdate())*10000 + month(getdate())*100 + day(_add_days(getdate(), -7 ))))THEN ( [BPW_Ewos_Canada].[Sales].[Invoiced quantity - basic U/M]/1000 ) ELSE ( 0 ) Because there is no week function with MS SQL that I can see any ideas would be greatI am looking to get the followingJan 2006Week 1 contains Jan 1 -7Week 2 contains Jan 8 - 14Week 3 contains Jan 15 - 21Week 4 contains Jan 22 - 28Week 5 contains Jan 29 - Feb 4Feb 2006Week 1 contains Feb 1 - 7 Week 2 contains Feb 8 - 14Week 3 contains Feb 15 - 21Week 4 contains Feb 22 - 28And so on everything is dependant on the month chosen the number of weeks that show up. So the most weeks you may have will be 5. Not the 1 - 52 that show with datepart() or dateDiff()matt |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-11-14 : 20:59:31
|
"Because there is no week function with MS SQL"This will gives you the week no of the year.select datepart(week, getdate())
KH |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-11-14 : 21:26:07
|
| What is your exact definition of “week”?That is something that varies from one organization to the next. For example, many organizations use ISO week.CODO ERGO SUM |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
|
|
|
|
|
Creating weeks of the month