I have a table of records. The user is allowed to enter data based on the date the event happened. The table becomes something like this:
ID Date Name Hrs
1 2016-03-01 Username 8
2 2016-03-02 Username 8
3 2016-03-03 Username 9
4 2016-03-04 Username 10
…
31 2016-03-31 Username 8
I also want to display all the records in the table breaking them into weeks. Something like this:
Day Date Name Hrs
Mon 2016-03-01 Username 8
Tue 2016-03-02 Username 8
Wed 2016-03-03 Username 9
Thu 2016-03-04 Username 10
Fri 2016-03-05 Username 10
Sat 2016-03-06 -
Sun 2016-03-07 Username 10
Mon 2016-03-08 Username 8
Tue 2016-03-09 -
Wed 2016-03-10 -
Thu 2016-03-11 -
Fri 2016-03-12 Username 10
Sat 2016-03-13 -
Sun 2016-03-15 Username 10
...
...
...
Mon 2016-03-30 Username 8
Tue 2016-03-31 Username 8
Below is my code:
<?php
...Connection to DB...
$query="SELECT *
FROM Table_I
ORDER BY DATEPART(wk,date) as wk
";
$params = array($_REQUEST['query']);
$results = sqlsrv_query($conn, $query, array(), array( "Scrollable" => 'static' ));
if($results===false)
{ die( FormatErrors( sqlsrv_errors() ) ); }
$rows = sqlsrv_num_rows($results);
$array = array();
while ($row = sqlsrv_fetch_array($results, SQLSRV_FETCH_ASSOC))
{
$array[] = $row;
}
?>
<html>
<head></head>
<body>
<table border="1">
<?php
for($a=0; $a<$rows; $a++)
{
echo '<tr><td>'.$array[$a]['wk'].'</td>';
echo '<td>'.date("l", strtotime($array[$a]['date'])).'</td>';
echo '<td>'.date('d M Y', strtotime(strip_tags($array[$a]['date']))). "</td>";
echo '<td>'.strip_tags($array[$a]['name'])."</td>";
echo '<td>'.strip_tags($array[$a]['hrs'])."</td>";
}
</body>
</html>
It prints alright but doesn't include dates the user didn't enter and doesn't break it down into each week. I'm very new to programming but it's fun though.
Please, help is greatly appreciated.
You can start with below query. I believe you can get what you want by some minor changes.
Below query gives you missing dates.
;WITH numbers AS
(
SELECT cast('2016-03-01' as date) AS num
UNION ALL
SELECT dateadd(day, 1, num)
FROM numbers
WHERE num < '2016-03-31'
)
select num as MissingDate
from numbers
left join yourEventTable on yourEventTable.Dt = numbers.num
where ID is null
using above query, you can group by week number:
;WITH numbers AS
(
SELECT cast('2016-03-01' as date) AS num
UNION ALL
SELECT dateadd(day, 1, num) FROM numbers
WHERE num < '2016-03-31'
)
, dys as (
select num
from numbers
)
select datepart(week, num), *
from dys
left join #events on #events.Dt = dys.num
with below sample,