sql server - MSSQL datetime datetime in php -


i have problem think exist couple solutions.

scenario have build intranetpage internal performance kpi's visible office. data extracted mssql database , shown on webpage in combination php, data , converted array google chart api can create beautiful charts there. far, good!

problem have chart created tickets last 7 days shown (in reverse order). number of tickets created mssql count, 1 number each day (today - 7) in total.

but on days, no tickets created saterday or sunday. thats things go wrong.

today thursday , shows: 

day          amount thursday     25 friday       10 monday       30 (etc) tuesday      15 (day before)  wednesday    20 (yesterday) thursday     50 (today)

instead of

saturday      0 sunday        0 monday       30 tuesday      15 wednesday    20 thursday     50

the problem there no records (tickets) create-date on saturday or sunday, mssql cant count them.

php calculates dates (also today -7) shown beneath axis in chart, php include saterday , sunday, axis titles not representive data axis shows.

possible solution 1 solution somehow mssql shows saterday , sunday in result table. dont think possible, because records dont exist.

other solution retrieve datetime used in mssql query , parse them intranetpage google charts can display dates instead of php created dates. "convert datetime class string" error.

because datetime in mssql datetime type , not varchar type. after trying lot of things , searching more on google , stackoverflow, can succesfull convert datetime varchar. part take todays date , go 7 days, doesnt work anymore.

my php (and query) follow:

$sql = "                                                                  select  top 7     cast(bb_casestartingtime date) date, count(*) totalrows             crm_mscrm.dbo.filteredincident            bb_casestartingtime > (convert(date, getdate() - 7)) group cast(bb_casestartingtime date) order date  "; 
$stmt = sqlsrv_query( $conn, $sql );                                     if( $stmt === false) {die( print_r( sqlsrv_errors(), true) );}  $x=5;  while( $row = sqlsrv_fetch_array( $stmt, sqlsrv_fetch_assoc) )       {   **$datevalue = date("y-m-d", strtotime(" -$x day"));**         echo "[',".$row[**date**].$row['totalrows'].",],";         $x--;        }

i broke 2 pieces of code in half,

the table looks (at least, interesting part):

case id   bb_casestartingtime 01        2016-11-10 15:30:45.000 02        2016-11-10 16:22:10.000 03        2016-11-09 15:02:15.000

i count amount of occurrences date (bb_casestartingtime) total. today have today 2 records yesterday 1 record

acutal question can me extraced date-time used in query?

i use one table called: filteredincident there way convert datetime bb_casestartingtime varchar (so php can extract it) still count amount of occurrences per day?

if information needed, please ask

thanks in advance!

(i rewrote entire question, because wrote initial post in rush)

you achieve you're after creating date range table , can left join it, 0 days no tickets. can run code in isolation test , should give can adapt:

-- table dummy data create table #filteredincident (bb_casestartingtime datetime)  -- insert dummy data has gaps , more 7 days insert #filteredincident         ( bb_casestartingtime ) values  ( '20161110 12:00'),         ( '20161110 11:00'),         ( '20161109 10:00'),         ( '20161108 13:00'),         ( '20161107 09:00'),         ( '20161104 07:00'),         ( '20161104 05:00'),         ( '20161104 16:00'),         ( '20161103 18:00'),         ( '20161103 19:00')  -- create date range table last 7 days create table #daterange ( dateval date )  -- insert last 7 days insert #daterange         ( dateval ) values  ( getdate() )        ,( dateadd(d,-1,getdate()))        ,( dateadd(d,-2,getdate()))             ,( dateadd(d,-3,getdate()))             ,( dateadd(d,-4,getdate()))             ,( dateadd(d,-5,getdate()))             ,( dateadd(d,-6,getdate()))       -- select * #filteredincident -- uncomment see data -- select * #daterange        -- uncomment see date range data  select count(fi.bb_casestartingtime) totalrows, dr.dateval #daterange dr left join #filteredincident fi on dr.dateval = cast(fi.bb_casestartingtime date) group dr.dateval order dr.dateval  drop table #filteredincident drop table #daterange

produces:

totalrows   dateval 3           2016-11-04 0           2016-11-05 0           2016-11-06 1           2016-11-07 1           2016-11-08 1           2016-11-09 2           2016-11-10

note: i'd recommend moving sql code in stored procedure opposed building string execute doing.


Comments

Post a Comment

Popular posts from this blog

asynchronous - C# WinSCP .NET assembly: How to upload multiple files asynchronously -

i using winscp .net assembly uploading files. want upload multiple files asynchronous way. have created method works single upload. public class uploadingdata { private sessionoptions _sessionoptions; private session _session; //connection etc private void onconnected() { foreach (var fileinfo in localfileslist) { var task = task.factory.startnew(() => uploadfilesasync(fileinfo)); } } private async task uploadfilesasync(string file) { string remotefilepath = _session.translatelocalpathtoremote(file, @"/", "/test_data_upload"); var uploading = _session.putfiles(file, remotefilepath, false); //when done await task.run(() => thread.sleep(1000)); } } please suggest me correct way. thanks you can use backgroundworker class this: string[] images = new string[50]; foreach (var path in images) { backgroundworker
Read more

aws api gateway - SerializationException in posting new Records via Dynamodb Proxy Service in API -

i getting "__type": "com.amazon.coral.service#serializationexception" as reply in postman & in test console in api gateway trying post record directly dynamodb using api proxy services.. referring aws article - https://aws.amazon.com/blogs/compute/using-amazon-api-gateway-as-a-proxy-for-dynamodb/ here's mapping { "tablename": "tablenamegoeshere", "item": { "id" : "$context.requestid" "eventname" : "$input.path('$.eventname')", "timestamp" : $input.path('$.timestamp'), "answers": "$util.parsejson($input.path('$.answers'))" } } update: did asked ... , worked if try add array of json objects gives me above same error - here's trying now. please - couldnt find on google well #set($inputroot = $input.path('$')) { "tablename": "answer", "item": {
Read more

asp.net - Problems sending emails from forum -

i have been redesigning site has message board, has been written guy looked after site. it has few problems , written in .asp , uses .mdb files database. you can see whole thing here . feel free post if helps, test thing have set using original files , new database. the basic problem when posts message, supposed email sent them, have posted it. when replies, originator supposed message, replier has been posted. i’d have thought @ point in process there should script/file in postmessage.asp? the postmessage.asp page below. <% option explicit %> <!-- #include virtual="common/adovbs.inc" --> <html> <head> <meta http-equiv="content-type" content="text/html; charset=windows-1252"> <meta name="generator" content="microsoft frontpage 4.0"> <meta name="progid" content="frontpage.editor.document"> <title>new page 2</title> <base target="_self"
Read more