nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

select the max value in a date

hugoscp1

please see attach file the post its too long to view here.
 
thank you

Find more posts tagged with

Comments

Clement Wong

Your question appears more to be a SQL issue than a BIRT issue. We can help, but you'll need to provide additional information...
 
1. What database are you using?
2. How are your tables defined? Specifically, what data type is DTPDPR?
3. Are you able to create a SQL Fiddle (<a data-ipb='nomediaparse' href='http://sqlfiddle.com/'>http://sqlfiddle.com/</a>) for us so that we can assist? It supports different database engines. 
 
Take a look at this solution on Stack Overflow @ <a data-ipb='nomediaparse' href='http://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql. '>http://stackoverflow.com/questions/612231/how-can-i-select-rows-with-maxcolumn-value-distinct-by-another-column-in-sql. </a>In your case, you'll want to max(DTPDPR). 
 
 
The alternative (in BIRT), if you retrieve all the rows (which is less efficient), you can create a computed column aggregation of the MAX date. Then add a filter where DTPDPR is equal to the max date.

hugoscp1

hi, thank you for you answer.
its DB2 
and the field DTPDPR its decimal
but dont forget i want the max value in a date, please see the examples in the attach, its two criterias its the date and the value.
can you use teamviewer to help me.
please its very urgent
 
CIUSMD NMISMD LTESMH QSFSMH DTVSLT TPRDPR DTPDPR PREC_MAX 
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 LM 20130507 92.0 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 LM 20121106 89.0 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 LM 20121009 86.0 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 LM 20120904 86.0 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 LM 20120820 86.0 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 MD 20130507 93.0 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 MD 20130404 92.0 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 MD 20121210 91.0 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 MD 20121106 89.0 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 MD 20121026 86.85 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 MD 20120610 86.0 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 PA 20160105 49.321 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 PA 20150505 62.946 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 PA 20150428 62.64 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 PA 20150423 62.946 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 PA 20150305 62.64 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 PA 20140613 78.13 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 PA 20130705 78.12 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 PA 20130506 73.43 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 PA 20130423 78.49 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 PA 20121003 73.43 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 PA 20121002 73.08 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 PA 20120912 73.43 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 PA 20120911 73.78 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 PA 20120907 73.43 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 PA 20120906 73.78 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 PA 20120905 73.78 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 PA 20120831 73.43 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 PA 20120824 73.78 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 PA 20120821 73.43 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 PA 20120817 73.43 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 PA 20120816 73.78 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 PA 20120518 73.78 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 PA 20120301 72.89 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 PA 20120201 78.49 </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 PA 20120101 78.49 </div>
<div> </div>
<div>In this case the resull should be:</div>
<div> </div>
<div>5090865 ALZEN SR 200MG 60COMP LK426 1.0 20180131 MD 20130507 93.0 </div>

Clement Wong

Your first sample case in the text file, your expected result contained the maximum PREC_MAX row with value of 93 the did not have a max DTPDPR. Th max was 20160105 with a value of 49.321.
 
In the second sample case, you chose the PREC_MAX rows with values of 5.6, and did have a max DTPDPR. 
 
"max value in a date" needs further clarification. Can you explain why those rows were chosen? Either psedo-code, or or a description would help me understand the requirements.