0

--TestData
declare @table table (groupname varchar(50),
                      credit numeric(18,2),
                      debit numeric(18,2),
                      drcreffect varchar(50),
                      undergroup varchar(50)
                     )

declare @table1 table (groupname varchar(50),
                       credit numeric(18,2),
                       debit numeric(18,2),
                       drcreffect varchar(50),
                       undergroup varchar(50)
                      )

insert into @table 
values 
       ('Opening Stock',   200.00, 0.00, 'Dr', 'Opening Stock'),
       ('Opening Stock',   500.00, 0.00, 'Dr', 'Opening Stock'),
       ('Purchase',        0.00, 7500.00, 'Dr', 'Purchase'),
       ('Purchase Return', 7000.00, 0.00, 'Dr', 'Purchase'),
       ('Purchase Return', 200.00, 0.00, 'Dr', 'Purchase')


--Query
SELECT 
    RN = ROW_NUMBER() OVER(ORDER BY groupname),
    undergroup,
    groupname,
    credit+debit Amt,
    LAG(debit, 1,0) OVER(PARTITION BY undergroup ORDER BY undergroup)+credit AS Total
FROM
(
    SELECT DISTINCT
        groupname,
        SUM(credit) OVER(PARTITION BY groupname) AS Credit,
        SUM(debit*-1) OVER(PARTITION BY groupname) AS debit,
        undergroup
    FROM @table
)X

this one my table data

enter image description here

I want output based on Undergroup with sum on group on last rows

enter image description here

0

you can try use case when SumValue < -1 then -1 * SumValue else SumValue emd

--TestData
declare @table table (groupname varchar(50),
                      credit numeric(18,2),
                      debit numeric(18,2),
                      drcreffect varchar(50),
                      undergroup varchar(50)
                     )

declare @table1 table (groupname varchar(50),
                       credit numeric(18,2),
                       debit numeric(18,2),
                       drcreffect varchar(50),
                       undergroup varchar(50)
                      )

insert into @table 
values 
       ('Opening Stock',   200.00, 0.00, 'Dr', 'Opening Stock'),
       ('Opening Stock',   500.00, 0.00, 'Dr', 'Opening Stock'),
       ('Purchase',        0.00, 7500.00, 'Dr', 'Purchase'),
       ('Purchase Return', 7000.00, 0.00, 'Dr', 'Purchase'),
       ('Purchase Return', 200.00, 0.00, 'Dr', 'Purchase');


--Query
with CTE as (
    SELECT 
        RN = ROW_NUMBER() OVER(ORDER BY groupname),
        undergroup,
        groupname,
        credit+debit Amt,
        LAG(debit, 1,0) OVER(PARTITION BY undergroup ORDER BY undergroup)+credit AS Total
    FROM
    (
        SELECT DISTINCT
            groupname,
            SUM(credit) OVER(PARTITION BY groupname) AS Credit,
            SUM(debit*-1) OVER(PARTITION BY groupname) AS debit,
            undergroup
        FROM @table
    )X
)
select 
    RN,
    undergroup,
    groupname,
    case when Amt < 0 then -1*Amt else Amt end as Amt,
    case when Total < 0 then -1*Total else Total end as Total
from CTE

Result:

+----+---------------+-----------------+---------+--------+
| RN |   undergroup  | groupname       | Amt     | Total  |
+----+---------------+-----------------+---------+--------+
|  1 | Opening Stock | Opening Stock   | 700.00  | 700.00 |
+----+---------------+-----------------+---------+--------+
|  2 | Purchase      | Purchase        | 7500.00 | 0.00   |
+----+---------------+-----------------+---------+--------+
|  3 | Purchase      | Purchase Return | 7200.00 | 300.00 |
+----+---------------+-----------------+---------+--------+

Demo Link

  • ans right.add new value& check closing record total insert into @table values ('Opening Stock', 200.00, 0.00, 'Dr', 'Opening Stock'), ('Opening Stock', 500.00, 0.00, 'Dr', 'Opening Stock'), ('Purchase', 0.00, 7500.00, 'Dr', 'Purchase'), ('Purchase Return', 7000.00, 0.00, 'Dr', 'Purchase'), ('Purchase Return', 200.00, 0.00, 'Dr', 'Purchase'), ('Sale', 0.00, 5000.00 ,'Dr', 'Sales'), ('Sale', 0.00, 5000.00 ,'Dr', 'Sales'), ('Sale return', 200, .00 ,'Dr', 'Sales'), ('Closing Stock',0.00 ,1000,'Dr', 'Closing Stock'); – alpesh ramani Jun 29 '18 at 7:07
  • not + or - value actuall you hunderstand some thing wrong i have put some other result please check this result with your query and check total value in gropwise – alpesh ramani Jun 29 '18 at 8:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.