how to display daterange in crosstab ?

Question

1.00/5 (1 vote)

See more:

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

SQL

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 24-Feb-13 23:23pm

Member 9846414

Updated 24-Feb-13 23:37pm

Aarti Meswania

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Aarti Meswania · Answer 1 · 2013-02-24T23:46:00

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...

SQL

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!
:)

Posted 24-Feb-13 23:46pm

Aarti Meswania

Comments

Member 9846414 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 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 25-Feb-13 6:49am

thank u .but i dont know how to write a formula for thi can u help me :)

Member 9846414 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 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 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 28-Feb-13 1:34am

help me

Aarti Meswania 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 20-Jul-13 7:05am

thank u very much madam :)

Aarti Meswania 23-Jul-13 4:04am

Welcome!
Glad to help you!:)