输出一年中某一列的周间隔的总和，与日期一致的周日 [英] Output Sum of some column in week intervals throughout a year, week dates consistent with day
问题描述
我需要一些帮助，因为我被卡住了。这是使用SQL和Coldfusion。
基本上我有一个表，其中列出了某些票据的完成以及他们被标记为完成的日期。这个表以区域和区域为例，在GROUP BY中使用。所以我想计算一年中每个星期做的每一张票，用户选择例如运行报告。
我将列出一些我目前的SQL。
I need some help as I am stuck. This is using SQL and Coldfusion. Basically I have a table which list a completion of certain tickets along with what date they were marked complete. This table has district and area for example, which is used in the GROUP BY. So I want to count every ticket that was done on every week of the year, the user selects for example running the report. I will list a little of the SQL I have at the moment.
SELECT
SUM(CASE WHEN DATEPART(ww, completionDate) = 1 THEN 1 ELSE 0 END) AS [Jan1-7],
SUM(CASE WHEN DATEPART(ww, completionDate) = 2 THEN 1 ELSE 0 END) AS [Jan8-14],
SUM(CASE WHEN DATEPART(ww, completionDate) = 3 THEN 1 ELSE 0 END) AS [Jan15-21],
SUM(CASE WHEN DATEPART(ww, completionDate) = 4 THEN 1 ELSE 0 END) AS [Jan22-31]
SUM(CASE WHEN DATEPART(ww, completionDate) = 5 THEN 1 ELSE 0 END) AS [Feb1-7],
SUM(CASE WHEN DATEPART(ww, completionDate) = 6 THEN 1 ELSE 0 END) AS [Feb8-14],
SUM(CASE WHEN DATEPART(ww, completionDate) = 7 THEN 1 ELSE 0 END) AS [Feb15-21],
SUM(CASE WHEN DATEPART(ww, completionDate) = 8 THEN 1 ELSE 0 END) AS [Feb22-28],
正如你可以看到，我基本上试图抓住一年中的完成日期，并总结在自己的专栏中的哪一周。它应该是这种格式，除非有人有更好的建议。
问题是例如，2013年2月18日，例如，SQL告诉我，将是2013年的第8周，而我的代码将SUM，在FEB15-21，这是第7周根据我的代码。
我需要的报告是在顶部标题中显示该月份，然后在该月的下一个标题中显示每周，然后基本上对该周完成的每张票据进行计数。所以周/月需要列。
As you can see I am basically trying to grab which week of the year the completionDate was and summing that in its own column. It should be in this format unless someone has a better suggestion. The issue is for example, 2/18/2013 for example, SQL is telling me that would be the 8th week of 2013, whereas my code would SUM that in the FEB15-21, which is the 7th week according to my code. The report I need is to show the month in a top header, and then show each week in the next header of that month, then basically do a count of every ticket completed in that week. So the weeks/months need to be columns.
我已经读过一些CF函数和所有，但只是不知道如何利用他们来获得我需要的。
是否有一种方法动态SUM这些日期确保他们在基于一周的正确列中SUMMED，或使用Coldfusion处理这部分？但我也不太确定ColdFusion部分，所以如果可能的话，任何建议使用SQL或CF和可能帮助我的代码部分将真的非常感谢。
I have read some CF functions and all, but just not sure how to utilize them to get what I need. Is there either a way to dynamically SUM these dates ensuring they are SUMMED in the correct column based on the week, or use Coldfusion to handle this part? But I am not too sure on the ColdFusion part either, so if possible, any suggestion on either using SQL or CF and maybe helping me out on the code part would be really really appreciated.
谢谢！
推荐答案
是MSSQL如何处理周数。他们都是星期一星期格式。这种情况下的第1周开始于2012年12月31日星期一至2013年6月6日星期日（，而不是2013年1月1日至2013年7月7日您假设），第2周：1/7/13到1/13/13，等等... 2/18/2013是在一年的第八周，因为它从第8周（星期一）开始。
I think the part that's confusing you is how MSSQL is treating the week numbers. They're all Mon-Sun week formats. Week 1 in this case begins on the Monday 12/31/2012 to Sunday 1/6/13 (and not 1/1/2013 to 1/7/2013 you're assuming), Week 2: 1/7/13 to 1/13/13, and so on... 2/18/2013 is on the eighth week of the year because it starts the 8th week (a Monday).
您将需要使用类似于：
DATEADD(ww, DATEDIFF(ww,0,completionDate), 0) AS [Start Of Week],
DATEADD(s,-1,DATEADD(ww, DATEDIFF(ww,0,GETDATE())+1,0)) as [End Of Week]
获取开始和结束日期，并在group by子句和求和中使用。
to get the start and end dates and use it in your group by clause and summation.
但是，为了匹配您的代码，您可以将[偏移天数]添加到[星期开始]和[周末]以获取范围。在2013年，该偏移将为2013年1月1日（人类年初） - 2012年12月31日（sql年初）的 1天。日期将与您的[Jan1-7]星期格式匹配。
To match your code, though, you can add the number of offset days to [Start Of Week] and [End Of Week] to get your ranges. In 2013, that offset will be 1 day as 1/1/2013 (human start of year) - 12/31/2012 (sql start of year). The dates will all match up with your [Jan1-7] week format that way.
希望这有助于。 -Minh
Hope this helps. -Minh
这篇关于输出一年中某一列的周间隔的总和，与日期一致的周日的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
