Group by then Sort (SQL Server)

Question

Following the previous question

I have this query:

SELECT  Acc.DocTLItem.TLRef ,
        Acc.DocTLItem.Debit AS deb,
        Acc.DocTLItem.Credit AS cred,
        info.MiladiToShamsi(Acc.DocTLItem.StartDocDate) Date,
        Acc.TL.TLCode ,
        Acc.DocTLItem.DocTLHeaderRef ,
        Acc.DocTLHeader.Num
FROM    Acc.DocTLItem
        INNER JOIN Acc.TL ON Acc.DocTLItem.TLRef = Acc.TL.Id
        INNER JOIN Acc.DocTLHeader ON Acc.DocTLItem.DocTLHeaderRef = Acc.DocTLHeader.Id
        ORDER BY ( CASE WHEN debit > 0 THEN 0  ELSE 1 END ) ,
        Acc.TL.TLCode ,
        debit

Result:

 TLRef  deb cred        Date    TLCode  DocTLHeaderRef  Num
    --------------------------------------------------------------------------
    44   1  0       1396/09/12  111     16           2
    44   1  0       1396/09/21  111     18           4
    28   13 0       1396/09/11  982     15           1
    28   10 0       1396/09/19  982     17           3
    44   0  10      1396/09/19  111     17           3
    44   0  1       1396/09/21  111     18           4
    44   0  9       1396/09/11  111     15           1
    44   0  1       1396/09/12  111     16           2

How can I Group by Date then sort by Date?

I need to generate a result set like this that debt comes first and then ordered by TLCode column after all group by date.

Expected result:

  TLRef deb cred    Date    TLCode  DocTLHeaderRef  Num
    --------------------------------------------------------------------------------
    44  1    0  1396/09/12  111         16           2
    28  13   0  1396/09/11  982         15           1
    28  10   0  1396/09/19  982         17           3
    44  0    9  1396/09/11  111         15           1
    44  0    1  1396/09/12  111         16           2
    44  0    10 1396/09/19  111         17           3
    Sum 24   20             

    44  1   0   1396/09/21  111         18           4
    44  0   1   1396/09/21  111         18           4
    Sum 1   1   

Answer 1

May be following query block can help you: This query will work in 4 steps:

--1. Create a temporary table that we can take as base table (#TMP)
Select *
INTO #TMP
From
(
Select 44 as TLRef, 1 as deb, 0 as cred, '1396/09/12' as Date, 111 as TLCode, 16 as DocTLHeaderRef, 2 as Num Union All
Select 44 as TLRef, 1 as deb, 0 as cred, '1396/09/21' as Date, 111 as TLCode, 18 as DocTLHeaderRef, 4 as Num Union All
Select 28 as TLRef, 13 as deb, 0 as cred, '1396/09/11' as Date, 982 as TLCode, 15 as DocTLHeaderRef, 1 as Num Union All
Select 28 as TLRef, 10 as deb, 0 as cred, '1396/09/19' as Date, 982 as TLCode, 17 as DocTLHeaderRef, 3 as Num Union All
Select 44 as TLRef, 0 as deb, 10 as cred, '1396/09/19' as Date, 111 as TLCode, 17 as DocTLHeaderRef, 3 as Num Union All
Select 44 as TLRef, 0 as deb, 1 as cred, '1396/09/21' as Date, 111 as TLCode, 18 as DocTLHeaderRef, 4 as Num Union All
Select 44 as TLRef, 0 as deb, 9 as cred, '1396/09/11' as Date, 111 as TLCode, 15 as DocTLHeaderRef, 1 as Num Union All
Select 44 as TLRef, 0 as deb, 1 as cred, '1396/09/12' as Date, 111 as TLCode, 16 as DocTLHeaderRef, 2 as Num
) X

--2. Group table by "Date" and select sum of "deb", "cred" columns and insert result in another temporary table (#TMP2)
Select null as TLRef, SUM(deb) as deb, SUM(cred) as cred, Date, null as TLCode, null as DocTLHeaderRef, null as Num 
INTO #TMP2
From #TMP 
GROUP BY Date

--3. Union both tables to resulting table gets both detail and grouped data.
Select *
From
(
    Select *, 0 as IsDetail From #TMP
    Union All
    Select *, 1 as IsDetail From #TMP2
) X
Order By Date,IsDetail

--4. Drop both temporary table
DROP TABLE #TMP
DROP TABLE #TMP2