#Date format, Order By, Group By : query ported from Sql developer to Toad for MSSQL

Toad for SQL Server
1

hi all

i'm new to Toad and using Toad 6.0 the freeware version. Having problem to make the query work to get the same result as in Sql Developer.

In screen shot #1 above, showing theexecution result of the query in Sql Developer; and in screen shot #2 above showing execution result of the query in Toad for MSSQL.

Original Query used SQL Developer :

select STATUS,INTERNAL_STATUS,STATE,LAST_SUCCESSFUL_STATE,DOCTYPEID,TO_CHAR(RECEIVEDATE,'yyyyMMdd'), COUNT(1)
from document
--where DOCTYPEID='ORDERS' AND
where senderorgid = 'AEON_MY'
and receivedate > TO_DATE('20140127 00:00','yyyyMMdd HH24:MI')
group by STATUS, INTERNAL_STATUS, STATE, LAST_SUCCESSFUL_STATE,DOCTYPEID, TO_CHAR(RECEIVEDATE,'yyyyMMdd')
order by STATUS, INTERNAL_STATUS, STATE, LAST_SUCCESSFUL_STATE,DOCTYPEID, TO_CHAR(RECEIVEDATE,'yyyyMMdd')

Amended Query used in Toad for MSSQL :

select STATUS,INTERNAL_STATUS,STATE,LAST_SUCCESSFUL_STATE,DOCTYPEID,receivedate, COUNT(1)
from document
where DOCTYPEID='ORDERS'
and sendermailboxid = 'xxx' -- note: xxx is the name of the senderid, here covered for confidential reason
--and receivedate > ('20130815 00:00','yyyyMMdd HH24:MI')
and receivedate > '2014-01-27 00:00'
group by STATUS, INTERNAL_STATUS, STATE, LAST_SUCCESSFUL_STATE,DOCTYPEID, receivedate --TO_CHAR(RECEIVEDATE,'yyyyMMdd')
order by STATUS, INTERNAL_STATUS, STATE, LAST_SUCCESSFUL_STATE,DOCTYPEID, receivedate --TO_CHAR(RECEIVEDATE,'yyyyMMdd')

Problem faced: in the COUNT(1) column :

in Sql Developer as in scr shot #1 : i can get the total accumulated figures of that particular type of documents in that particular date range: for example : for ORDERS there're total 4336 of the ORDERS received at the query running time

in Toad which hooked up to MSSQL server: after amended the query, although the COUNT(1) column still shows values, but this column unable to obtain the accumulated figure as in screen shot #1

Question:

How to fix the query, so that when executed in Toad for MSSQL, i can still get back exactly the accumulated count of each respective documents at query execution time ?

Hope all can advise. Many thanks in advance.

2

hi all, any advise appreciated.

i tried convert but the results are still the same …

select STATUS,INTERNAL_STATUS,STATE,LAST_SUCCESSFUL_STATE ,DOCTYPEID,CONVERT(DATETIME,receivedate,126) receivedate, COUNT(1)
from document
where DOCTYPEID=‘ORDERS’
and sendermailboxid = ‘xxx’ – note: xxx is the name of the senderid, here covered for confidential reason
and receivedate > ‘2014-02-10 00:00’
group by STATUS, INTERNAL_STATUS, STATE, LAST_SUCCESSFUL_STATE,DOCTYPEID, CONVERT(DATETIME,receivedate,126) receivedate
order by STATUS, INTERNAL_STATUS, STATE, LAST_SUCCESSFUL_STATE,DOCTYPEID, CONVERT(DATETIME,receivedate,126) receivedate

Is that in order to achieve back the same result in toad for mssql, must use some kind of nested query ? Or still can use back a single direct query like above ?

Thx.