How to group by the each week upto last six week sundays dates in sql?(如何在sql中按每周到最后六个星期的星期日日期分组?)
问题描述
目前正在编写报告.我需要的是样本表,
但实际表是,
我需要按SPGI01_INSTANCE_TYPE_C"列分组,并将每个星期天到最后六个星期的星期天分组.在这里我粘贴了两个示例表,一个表是我想要的,另一个表是我拥有的.给我解决方案.
我的查询是,
解决方案
我对您发布的数据做了一些假设.
首先,您发布的值都将年份声明为 2011 但作为列标题的最终结束日期与 2011 不对应,它们是 Sunday2012 的 值,所以我更改了数据.Early ASN 8/15/2011 12:00 的最后一个条目,我相信应该是一个 Late ASN 条目,否则总数匹配.
p>
要获得结果,您需要应用 PIVOT
函数.此函数允许您聚合值,然后将它们转换为列.
参见 SQL Fiddle with Demo
Currently am working on the report. What i need is sample table,
But the Actual Table is,
I need to group by the "SPGI01_INSTANCE_TYPE_C" column and group the each week sunday upto last six week sundays.Here i pasted two sample tables one table is what i want and another table is what i have. Give me the solution for this.
My query is,
I made a few assumptions about the data that you posted.
First, the values you posted all state the year as 2011
but the final end dates as column headers do not correspond to 2011
, they are the Sunday
values for 2012
so I altered the data. Also the final entry of Early ASN 8/15/2011 12:00
, I believe is supposed to be a Late ASN
entry otherwise the totals to do match up.
To get the results, you want you will want to apply the PIVOT
function. This function allows you to aggregate the values and then convert them to columns.
See SQL Fiddle with Demo
这篇关于如何在sql中按每周到最后六个星期的星期日日期分组?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!