Discussions
Categories
Groups
Community Home
Categories
INTERNAL ENABLEMENT
POPULAR
PUBLIC CLOUD
PRIVATE CLOUD
Quick Links
MY LINKS
HELPFUL TIPS
Back to website
Home
Intelligence (Analytics)
excluding weekends from query
Alec
Hi All,
I have the following report. it brings the data from yesterday.
how can i exclude the weekend days so I dont get "no data" while running the report on Mondays.
select
EMPLOYEE, COUNT(NUMBERCALLED) as TOTAL_CALLED
FROM SDDTA.PhoneReport
WHERE
(MANAGERNAME = 'X' OR
MANAGERNAME = 'Y'
) AND
DATE = current_date - 1 day
group by EMPLOYEE
order by count(numbercalled) desc
fetch first 5 rows only
Any ideas?!
Thanks!
Find more posts tagged with
Comments
rihanna
Try to put some kind of filter on your dataset. Birt has BirtDateTime.weekDay() function which might be helpful to you.
Alec
<blockquote class='ipsBlockquote' data-author="'rihanna'" data-cid="81589" data-time="1313535159" data-date="16 August 2011 - 03:52 PM"><p>
Try to put some kind of filter on your dataset. Birt has BirtDateTime.weekDay() function which might be helpful to you.<br /></p></blockquote>
I wasn't able to do it using Filters!!,<br />
any other solutions?!
mcremer
<blockquote class='ipsBlockquote' data-author="'Alec'" data-cid="81587" data-time="1313525903" data-date="16 August 2011 - 01:18 PM"><p>
Hi All,<br />
I have the following report. it brings the data from yesterday.<br />
how can i exclude the weekend days so I dont get "no data" while running the report on Mondays. <br />
<br />
select <br />
EMPLOYEE, COUNT(NUMBERCALLED) as TOTAL_CALLED<br />
FROM SDDTA.PhoneReport<br />
WHERE <br />
(MANAGERNAME = 'X' OR<br />
MANAGERNAME = 'Y' <br />
) AND<br />
DATE = current_date - 1 day <br />
<br />
group by EMPLOYEE<br />
order by count(numbercalled) desc<br />
fetch first 5 rows only<br />
Any ideas?!<br />
Thanks!<br /></p></blockquote>
<br />
Alec,<br />
<br />
This is better done trough the query it self somting like this will give the result set without giving the weekends (this is an example done for an Oracle databse but it gives the general idea I think:<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>SELECT EMPLOYEE, COUNT(NUMBERCALLED) AS TOTAL_CALLED
FROM SDDTA.PHONEREPORT
WHERE ( MANAGERNAME = 'X'
OR MANAGERNAME = 'Y' )
AND (TO_CHAR(SYSDATE, 'D') IN ('6', '7')
AND DATE = CURRENT_DATE - (TO_CHAR(SYSDATE, 'D') -5)
OR DATE - 1)
</pre>
