Get referenced values from another table in SQL Server

PRA
 
on Jul 16, 2021 01:01 AM
405 Views

Hi!

I used below script couldn’t got requirement result.

DECLARE @Debit AS TABLE(Namber INT,Date DATE,Debit INT)
INSERT INTO @Debit VALUES(1,'11.11.2020',1500)
INSERT INTO @Debit VALUES(2,'11.21.2020',10000)
INSERT INTO @Debit VALUES(3,'12.15.2020',12500)
INSERT INTO @Debit VALUES(4,'01.05.2021',11000)
INSERT INTO @Debit VALUES(5,'01.25.2021',20000)
INSERT INTO @Debit VALUES(6,'02.02.2021',15500)
INSERT INTO @Debit VALUES(7,'02.11.2021',1500)
INSERT INTO @Debit VALUES(8,'03.05.2021',13000)
 
DECLARE @Credit AS TABLE(Namber INT,Date DATE,Credit INT)
INSERT INTO @Credit VALUES(1,'01.05.2021',1000)
INSERT INTO @Credit VALUES(2,'01.25.2021',2000)
INSERT INTO @Credit VALUES(3,'02.02.2021',5500)
INSERT INTO @Credit VALUES(4,'02.11.2021',1500)
INSERT INTO @Credit VALUES(5,'03.05.2021',3000)

DECLARE @Setting AS TABLE(Namber INT,Office varchar(25))
INSERT INTO @Setting VALUES(1,'Tajik national University')
 
SELECT t.Office,
	t.[MonthName] 'Month',
    SUM(t.Debit) 'Debit',
    SUM(t.Credit) 'Credit',
    SUM(t.Debit) - SUM(t.Credit) 'Remain'
FROM
(
    SELECT DATENAME(MONTH,Date) MonthName,
    Debit,
    0 'Credit',
    DATEPART(YYYY,Date) 'Year', s.Office
    FROM @Debit, @Setting s
    UNION
    SELECT DATENAME(MONTH,Date),
    0 'Credit',
    Credit,
    DATEPART(YYYY,Date) 'Year', ''Office
    FROM @Credit
) t
GROUP BY t.[MonthName],t.[Year],t.Office
ORDER BY t.[Year] ASC,
    CASE t.[MonthName]
        WHEN 'January' THEN 1
        WHEN 'February' THEN 2
        WHEN 'March' THEN 3
        WHEN 'April' THEN 4
        WHEN 'May' THEN 5
        WHEN 'June' THEN 6
        WHEN 'July' THEN 7
        WHEN 'August' THEN 8
        WHEN 'September' THEN 9
        WHEN 'October' THEN 10
        WHEN 'November' THEN 11
        WHEN 'December' THEN 12
    END

I want below result:

Tajik national University

Month

Debit

Credit

Remain

1

Tajik national University

November

11500

 

11500

2

Tajik national University

December

12500

 

12500

3

Tajik national University

January

31000

1000

30000

4

Tajik national University

February

17000

7000

10000

5

Tajik national University

March

13000

3000

10000

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jul 16, 2021 06:38 AM

Hi PRA,

Refer below query.

DECLARE @Debit AS TABLE(Namber INT,Date DATE,Debit INT)
INSERT INTO @Debit VALUES(1,'11.11.2020',1500)
INSERT INTO @Debit VALUES(2,'11.21.2020',10000)
INSERT INTO @Debit VALUES(3,'12.15.2020',12500)
INSERT INTO @Debit VALUES(4,'01.05.2021',11000)
INSERT INTO @Debit VALUES(5,'01.25.2021',20000)
INSERT INTO @Debit VALUES(6,'02.02.2021',15500)
INSERT INTO @Debit VALUES(7,'02.11.2021',1500)
INSERT INTO @Debit VALUES(8,'03.05.2021',13000)
  
DECLARE @Credit AS TABLE(Namber INT,Date DATE,Credit INT)
INSERT INTO @Credit VALUES(1,'01.05.2021',1000)
INSERT INTO @Credit VALUES(2,'01.25.2021',2000)
INSERT INTO @Credit VALUES(3,'02.02.2021',5500)
INSERT INTO @Credit VALUES(4,'02.11.2021',1500)
INSERT INTO @Credit VALUES(5,'03.05.2021',3000)
 
DECLARE @Setting AS TABLE(Namber INT,Office varchar(25))
INSERT INTO @Setting VALUES(1,'Tajik national University')
  
SELECT (SELECT TOP 1 Office FROM @Setting) 'Office',
    t.[MonthName] 'Month',
    SUM(t.Debit) 'Debit',
    SUM(t.Credit) 'Credit',
    SUM(t.Debit) - SUM(t.Credit) 'Remain'
FROM
(
    SELECT DATENAME(MONTH,Date) MonthName,
    Debit,
    0 'Credit',
    DATEPART(YYYY,Date) 'Year'--, s.Office
    FROM @Debit--, @Setting s
    UNION
    SELECT DATENAME(MONTH,Date),
    0 'Credit',
    Credit,
    DATEPART(YYYY,Date) 'Year'--, ''Office
    FROM @Credit
) t
GROUP BY t.[MonthName],t.[Year]--,t.Office
ORDER BY t.[Year] ASC,
    CASE t.[MonthName]
        WHEN 'January' THEN 1
        WHEN 'February' THEN 2
        WHEN 'March' THEN 3
        WHEN 'April' THEN 4
        WHEN 'May' THEN 5
        WHEN 'June' THEN 6
        WHEN 'July' THEN 7
        WHEN 'August' THEN 8
        WHEN 'September' THEN 9
        WHEN 'October' THEN 10
        WHEN 'November' THEN 11
        WHEN 'December' THEN 12
    END