如何在交叉表中显示日期范围？ [英] how to display daterange in crosstab ?

问题描述

我是水晶报道的新手。我想在交叉表中显示日期范围。我写的像下面的qury

hi i am new to crystal report .i want to display date range in cross tab . i was written qury like below

select MONTH(date),YEAR(Date) ,sum(Score)from student where id='1' and
(MONTH(Date)in (select distinct (MONTH(date)) from student where id='1'))and
(YEAR(Date) in (select distinct (YEAR(date))from student where id='1') )
group by month(date),year(Date);

i有写查询这个并且执行得很好它显示输出如下

i have write query this and excuted is working fine it shows output below like this

6     2012      56
9     2012       3
7     2013      65
8     2014      31
8     2015      51

i在交叉表中使用它在水晶报告中显示如下所示

i have use in cross tab it shows like this below one in crystal report

    2012  2013   2014  2015
6    56    65     0     0
9    3     0      0     0
7    0     65     0     0
8    0     0      31    0
8    0     0      0    51

但我想要发送信息我在十字标签中的日期工作如何帮助我，如下所示;

注意：我的学年开始于每年的2012年7月至2013年7月

but i want to dispaly daterage in cross tab how can i do it help me like below ;
note:my Acdemic year starts from june2012-july2013 for every year

2012-2013   2013-2014 2014- 2015  2015-2016
 
6   56               
9   3
7   65
8                          31
8                                       51

plz通过修改上面的查询来帮助我

plz help me by modifying above query

推荐答案

create table acdyr

create table acdyr

acdyrid acdyr     acdfromdt   acdtodt
-----------------------------------------------------------
1       2012-13   01-06-2012  31-05-2013
2       2013-14   01-06-2013  31-05-2014
3       2015-15   01-06-2014  31-05-2015

现在你的查询...

now your query...

select MONTH(date),YEAR(Date) ,sum(Score),acdyrid,acdyr
from student 
left join acdyr on date between acdfromdt and acdtodt
where id='1' and
(MONTH(Date)in (select distinct (MONTH(date)) from student where id='1'))and
(YEAR(Date) in (select distinct (YEAR(date))from student where id='1') )
group by month(date),year(Date);

现在在Acdyr专栏的交叉表组中，结果将是...

now in crosstab group by Acdyr column so, result will be...

2012-13   2013-14 2014- 15  ...
 
6   56               
9   3
7   65
8                          31
8                                       51

Happy Coding！

：）

Happy Coding!
:)

这篇关于如何在交叉表中显示日期范围？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！