CODEWITHSUNDEEP
sqlsql-server-2008
Tanya
Tanya

Reputation: 1621

Complex Query using CTE

I have a table which has data like,

Date            Custid  CreditDebit     Units   Value
2013-03-29 06:21:46.903     1       C   1   10
2013-03-29 06:21:46.903     1       C   2   11
2013-03-29 06:21:46.903     1       C   1   12
2013-03-29 06:21:46.903     1       C   2   13
2013-03-29 06:21:46.903     1       D   1   20
2013-03-29 06:21:46.903     1       D   2   21
2013-03-29 06:21:46.903     1       C   1   20
2013-03-29 06:21:46.903     1       C   2   21

I need to get a temp table output as follow,

Date            Custid  CreditDebit     Units   Value Balance
2013-03-29 06:21:46.903     1       C   1   42      42-20
2013-03-29 06:21:46.903     1       c   2   45      22+45-21
2013-03-29 06:21:46.903     1       D   1   20      ---
2013-03-29 06:21:46.903     1       D   2   21      ---

That is sum of the Value column which is based on 'CreditDebit' and Units. How can i achieve this using CTE sql Query

Upvotes: 0

Views: 191

Answers (1)

Duncan Howe
Duncan Howe

Reputation: 3025

;WITH summary_table_cte
     AS (SELECT Date,
                Custid,
                CreditDebit,
                Units,
                sum(value) AS [Value]
         FROM   YourTable
         GROUP  BY Date,
                   Custid,
                   CreditDebit,
                   Units)
SELECT *
FROM   summary_table_cte;

Note the semi-colon at the start of the statement. A statement preceding a CTE must be terminated with a semi-colon. By prefixing WITH with it, you can put it anywhere.

Upvotes: 2

Related Questions