Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# WindowsForms
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 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 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
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 help me by modifying above query
Posted 25-Feb-13 0:23am
Edited 25-Feb-13 0:37am
v2

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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);
 
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!
Smile | :)
  Permalink  
Comments
Member 9846414 at 25-Feb-13 6:25am
   
thank u very much but i am have table like this plz see below
id score marks date
1 20 40 6-6-2012
2 30 32 7-7-2012
3 50 52 4-3-2013
1 40 10 4-3-2013
1 56 6 6-8-2013...so on years has been they
 
from this above table having only one colounms we need not add any coloumn to this table or we need create any table . from this table only we have to retrivew daterange from date coloumn thanku for help me and thank very much for responding to me
Aarti Meswania at 25-Feb-13 6:28am
   
then you have to go with CR-formulas and do all things in crystal report which is performed in sql query
 
Member 9846414 at 25-Feb-13 6:49am
   
thank u .but i dont know how to write a formula for thi can u help me :)
Member 9846414 at 28-Feb-13 1:34am
   
toText (year(CurrentDate)) + "-" + toText (year(CurrentDate) + 1) i was tried in formula feild like but it asking error like this boolean variable required
Aarti Meswania at 28-Feb-13 1:39am
   
you have choice a difficult & static way it will need efforts
simply go with sql
 
will u going to put if-else for 100 years? how much u have code?
Member 9846414 at 28-Feb-13 3:04am
   
ya is correct but how to solve this issue help me madam .then how to go plz any suggestion or hint
Member 9846414 at 28-Feb-13 1:34am
   
help me
Aarti Meswania at 28-Feb-13 3:23am
   
create formula field name it "daterange"
now, write condition like below...
 
if ({stores.currdt}>'2010-05-31' and {stores.currdt}<'2011-06-01') then
'2010-2011'
else if ({stores.currdt}>'2011-05-31' and {stores.currdt}<'2012-06-01') then
'2011-2012'
else if ({stores.currdt}>'2012-05-31' and {stores.currdt}<'2013-06-01') then
'2012-2013'
end if
 

Save It
 

now, use this field "daterange" as column in crosstab report
Member 9846414 at 20-Jul-13 7:05am
   
thank u very much madam :)
Aarti Meswania at 23-Jul-13 4:04am
   
Welcome!
Glad to help you!:)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Maciej Los 295
1 OriginalGriff 273
2 Aajmot Sk 234
3 Marcin Kozub 205
4 Richard MacCutchan 200
0 OriginalGriff 7,903
1 Sergey Alexandrovich Kryukov 7,127
2 DamithSL 5,604
3 Manas Bhardwaj 4,986
4 Maciej Los 4,820


Advertise | Privacy | Mobile
Web03 | 2.8.1411023.1 | Last Updated 25 Feb 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100