0
$query = "SELECT A.Date, B.Fee
FROM [Client] A JOIN [Price] B
ON A.SID = B.SID
WHERE A.Date BETWEEN '$from' AND '$to' AND A.ClientID = '$client'
ORDER BY A.Date";

I use the above query to display fee of transactions in a period (usually monthly), and where there's no transaction in a day it should display the fee from the last day transaction while there's no transaction data on that day. For example I wanna display this:

2015-01-05 | 234,567
2015-01-06 | 123,456
2015-01-07 | 123,456 // this is taken from 0106 cause no transaction on Sat
2015-01-08 | 123,456 // this is taken from 0106 cause no transaction on Sun
2015-01-09 | 345,678

While the data in database:

2015-01-05 | 234,567
2015-01-06 | 123,456
2015-01-09 | 345,678

I use the code below to display the data, and to do the trick currently, I re-query the data from date+1 and date+2 and display the friday's data. The problem is that won't do it when it's holiday (and I don't think that's efficient).

$stmt = $con->prepare($query);
$stmt->execute();
$rows = $stmt->fetchAll();
$num = count($rows);
foreach ($rows as $row) {
  echo date('d-m-Y', strtotime($row['Date']));
  echo number_format($row['Fee'], 0, ",", ".");
  }

I'm hoping for a solution.

Rikesh
  • 26,156
  • 14
  • 79
  • 87
anon3776
  • 33
  • 5
  • 1
    I answered one like this yesterday: http://stackoverflow.com/a/28553294/2427560 – Turophile Feb 18 '15 at 03:14
  • 1
    And here is another to address the other half of your question: http://stackoverflow.com/questions/8926134/selecting-all-dates-from-a-table-within-a-date-range-and-including-1-row-per-emp You can probably read the linked and related questions for other ideas. – Turophile Feb 18 '15 at 03:16
  • @Turophile I tried but no luck. Couldn't get LIMIT to work on the query in your solution you gave on the thread cause the platform is MSSQL 2008. Can you help on the sqlfiddle link above? Thanks. – anon3776 Feb 18 '15 at 05:08

1 Answers1

0

Okay I figured it out by the help on the comment above and here's the sqlfiddle:

http://sqlfiddle.com/#!3/2d6b8/1

Credit goes to @Turophile. Thank you.

Community
  • 1
  • 1
anon3776
  • 33
  • 5