Star79
asked on
Crystal Report Design Help
Hello,I have to design a report which is more like a productivity report.
There is going to be a date range thats gets selected.
Say 2013-06-25 to 2013-08-25,I will have to create something like in the attached file.
I can skip the days,but how can I dynamically generate the dates in sql or in crystal and the query that iam using to generate one column in the extract is below :
How can I change my query so that say there is no data row returned for a particular date,it should still come up on the extract with zero count.
I think its a combination of both crytsal and SQL.Any ideas will help.
Thanks all.
screenshot.gif
There is going to be a date range thats gets selected.
Say 2013-06-25 to 2013-08-25,I will have to create something like in the attached file.
I can skip the days,but how can I dynamically generate the dates in sql or in crystal and the query that iam using to generate one column in the extract is below :
create table #tblposted
(
cnt integer,
ddofwk dateTIME
)
insert into #tblposted
SELECT COUNT(RXNO) CNT,DispenseDt DAYOFWEEK FROM HRXS LEFT JOIN KeyIdentifiers K ON HRXS.NDC = K.NDC WHERE DispenseDt between '2013-06-25' and '2013-08-25'AND NewRx=0 AND (CCType IS NULL OR CCType ='C') AND Reversed =0 GROUP BY DispenseDt union SELECT COUNT(RXNO) CNT,DIspenseDt FROM RXS LEFT JOIN KeyIdentifiers K ON RXS.NDC = K.NDC WHERE DispenseDt between '2013-06-25' and '2013-08-25'AND NewRx=0 AND (CCType IS NULL OR CCType ='C') --K.DeaClass IN('C-II','C-III','C-IV','C-V') AND GROUP BY DispenseDt order by DispenseDt DESC select SUM(cnt) as count,ddofwk from #tblposted group by ddofwk order by ddofwk desc drop table #tblposted
How can I change my query so that say there is no data row returned for a particular date,it should still come up on the extract with zero count.
I think its a combination of both crytsal and SQL.Any ideas will help.
Thanks all.
screenshot.gif
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.