Date Validation

Hi Guys!
I am running the following query. it pulls up data for a previous day.
today is Monday and and it shows me the dta from Sunday.
Do you know how to exclude the weekends.
Thanks!

SELECT
M.#SLNAM as Name,
M.#SLMG1 as Manager1,
C.#COSLS * C.#COPCT as Amount
FROM SDDTA.CUSORD C
INNER JOIN
SDDTA.SALESMAN M
ON (M.#SLSMN = C.#COSMN)
WHERE C.#COENT = 40
and DATE(SUBSTR(DIGITS(#COEDT), 5, 2)||'/' || SUBSTR(DIGITS(#COEDT), 7, 2)||'/'||SUBSTR(DIGITS(#COEDT), 1, 4)) = (current_date-1 DAY)

Find more posts tagged with

Comments

johnw

This question always comes up.

I would add a filter into BIRT that would look something like:

var parser = new Packages.java.text.SimpleDateFormat("E");
parser.format(myDateValue);

And filter where the value is not in Saturday, Sunday, Sat, Sun.

You should do this on the Data Set filter itself.

There are SQL ways of doing this, but I am not sure which platform you are using, so I would go the BIRT route to make it platform independent.

Alec

I tried to do that, it is not working as well!

johnw

Take a look at the following example. In it, I am generating 100 dates, going forward from today. I created a column called dayOfWeek that is being set based on the expression I sent you. In the data set, there is a filter where the value of dayOfWeek is not in "Sat", "Sun". It filters out all weekend dates. There is an aggregation showing the count of days, which is less than the 100 days being generated. The reason is that the filtering takes place at the data set, and is not counted in the aggregation.<br />
<br />
you will have to copy and paste the XML into a new rptdesign file. I cannot attach for some reason.<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
<?xml version="1.0" encoding="UTF-8"?>
<report xmlns="http://www.eclipse.org/birt/2005/design" version="3.2.22" id="1">
<property name="createdBy">Eclipse BIRT Designer Version 2.6.2.r262_v20110209 Build <2.6.2.v20110219-1842></property>
<property name="units">in</property>
<property name="iconFile">/templates/blank_report.gif</property>
<property name="bidiLayoutOrientation">ltr</property>
<property name="imageDPI">96</property>
<data-sources>
<script-data-source name="Data Source" id="8"/>
</data-sources>
<data-sets>
<script-data-set name="Data Set" id="9">
<list-property name="resultSetHints">
<structure>
<property name="position">1</property>
<property name="name">date</property>
<property name="dataType">date-time</property>
</structure>
<structure>
<property name="position">2</property>
<property name="name">dayOfWeek</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">3</property>
<property name="name">rowNum</property>
<property name="dataType">integer</property>
</structure>
</list-property>
<list-property name="columnHints">
<structure>
<property name="columnName">date</property>
</structure>
<structure>
<property name="columnName">dayOfWeek</property>
</structure>
<structure>
<property name="columnName">rowNum</property>
</structure>
</list-property>
<list-property name="filter">
<structure>
<property name="operator">not-in</property>
<expression name="expr" type="javascript">row["dayOfWeek"]</expression>
<simple-property-list name="value1">
<value>"Sat"</value>
<value>"Sun"</value>
</simple-property-list>
</structure>
</list-property>
<structure name="cachedMetaData">
<list-property name="resultSet">
<structure>
<property name="position">1</property>
<property name="name">date</property>
<property name="dataType">date-time</property>
</structure>
<structure>
<property name="position">2</property>
<property name="name">dayOfWeek</property>
<property name="dataType">string</property>
</structure>
<structure>
<property name="position">3</property>
<property name="name">rowNum</property>
<property name="dataType">integer</property>
</structure>
</list-property>
</structure>
<property name="dataSource">Data Source</property>
<method name="open"><![CDATA[rowNum = 0;
date = new Date();]]></method>
<method name="fetch"><![CDATA[if (rowNum < 100)
{
//advance the row number
rowNum++;

//set the data sets values
row["date"] = date;
row["rowNum"] = rowNum;

//get the day of week value ("Mon", "Tues", "Wed", etc)
var parser = new Packages.java.text.SimpleDateFormat("E");
var dayOfWeek = parser.format(date);
row["dayOfWeek"] = dayOfWeek;

//advance the date by 1
date = BirtDateTime.addDay(date, 1);

return true;
}

return (false);]]></method>
</script-data-set>
</data-sets>
<styles>
<style name="report" id="4">
<property name="fontFamily">sans-serif</property>
<property name="fontSize">10pt</property>
</style>
<style name="crosstab-cell" id="5">
<property name="borderBottomColor">#CCCCCC</property>
<property name="borderBottomStyle">solid</property>
<property name="borderBottomWidth">1pt</property>
<property name="borderLeftColor">#CCCCCC</property>
<property name="borderLeftStyle">solid</property>
<property name="borderLeftWidth">1pt</property>
<property name="borderRightColor">#CCCCCC</property>
<property name="borderRightStyle">solid</property>
<property name="borderRightWidth">1pt</property>
<property name="borderTopColor">#CCCCCC</property>
<property name="borderTopStyle">solid</property>
<property name="borderTopWidth">1pt</property>
</style>
<style name="crosstab" id="6">
<property name="borderBottomColor">#CCCCCC</property>
<property name="borderBottomStyle">solid</property>
<property name="borderBottomWidth">1pt</property>
<property name="borderLeftColor">#CCCCCC</property>
<property name="borderLeftStyle">solid</property>
<property name="borderLeftWidth">1pt</property>
<property name="borderRightColor">#CCCCCC</property>
<property name="borderRightStyle">solid</property>
<property name="borderRightWidth">1pt</property>
<property name="borderTopColor">#CCCCCC</property>
<property name="borderTopStyle">solid</property>
<property name="borderTopWidth">1pt</property>
</style>
</styles>
<page-setup>
<simple-master-page name="Simple MasterPage" id="2">
<page-footer>
<text id="3">
<property name="contentType">html</property>
<text-property name="content"><![CDATA[<value-of>new Date()</value-of>]]></text-property>
</text>
</page-footer>
</simple-master-page>
</page-setup>
<body>
<table id="10">
<property name="dataSet">Data Set</property>
<list-property name="boundDataColumns">
<structure>
<property name="name">date</property>
<text-property name="displayName">date</text-property>
<expression name="expression" type="javascript">dataSetRow["date"]</expression>
<property name="dataType">date-time</property>
</structure>
<structure>
<property name="name">dayOfWeek</property>
<text-property name="displayName">dayOfWeek</text-property>
<expression name="expression" type="javascript">dataSetRow["dayOfWeek"]</expression>
<property name="dataType">string</property>
</structure>
<structure>
<property name="name">rowNum</property>
<text-property name="displayName">rowNum</text-property>
<expression name="expression" type="javascript">dataSetRow["rowNum"]</expression>
<property name="dataType">integer</property>
</structure>
<structure>
<property name="name">countOfDays</property>
<property name="dataType">integer</property>
<property name="aggregateFunction">COUNT</property>
<list-property name="arguments">
<structure>
<property name="name">Expression</property>
</structure>
</list-property>
</structure>
</list-property>
<column id="31">
<property name="repeat">1</property>
</column>
<column id="29"/>
<column id="30"/>
<header>
<row id="11">
<property name="textAlign">left</property>
<cell id="16">
<label id="17">
<text-property name="text">Row Number</text-property>
</label>
</cell>
<cell id="12">
<label id="13">
<text-property name="text">Date</text-property>
</label>
</cell>
<cell id="14">
<label id="15">
<text-property name="text">Day of Week</text-property>
</label>
</cell>
</row>
</header>
<detail>
<row id="18">
<cell id="23">
<data id="24">
<property name="whiteSpace">nowrap</property>
<property name="resultSetColumn">rowNum</property>
</data>
</cell>
<cell id="19">
<data id="20">
<property name="whiteSpace">nowrap</property>
<property name="resultSetColumn">date</property>
</data>
</cell>
<cell id="21">
<data id="22">
<property name="whiteSpace">nowrap</property>
<property name="resultSetColumn">dayOfWeek</property>
</data>
</cell>
</row>
</detail>
<footer>
<row id="25">
<cell id="28"/>
<cell id="26">
<label id="37">
<property name="fontWeight">bold</property>
<property name="textAlign">right</property>
<text-property name="text">Count of Days</text-property>
</label>
</cell>
<cell id="27">
<data id="36">
<property name="resultSetColumn">countOfDays</property>
</data>
</cell>
</row>
</footer>
</table>
</body>
</report>

</pre>