Click here to Skip to main content
14,268,955 members
Rate this:
Please Sign up or sign in to vote.
i have these two queries and i want to merge them into one
i.e: my first query show result of current year and second query show issues_pending balance of last year how can i combine them.
SELECT year,director, sum(issues_received) as Newly_Received,sum(issues_solved) as Solved,sum(issues_pending) as Remaining FROM subdivision_master where director='dhbvn' group by director,year

select SUM(convert(int,issues_pending)) as previous_balance from subdivision_master where year=CONVERT(int,year)-1

any help is appreciated
Posted
Updated 15-Mar-11 23:38pm
v2
Rate this:
Please Sign up or sign in to vote.

Solution 2

Would this work for you
SELECT  year,
        director,
        sum(issues_received)    as Newly_Received,
        sum(issues_solved)      as Solved,
        sum(issues_pending)     as Remaining,
        -- previous_balance (may have NULLs)
        (
        select  SUM(convert(int,issues_pending)) as previous_balance
        from    subdivision_master
        where   CONVERT(int,year) = CONVERT(int,a.year)-1
        )                       as previous_balance
FROM    subdivision_master a
where   director='dhbvn'
group
by      director,
        year
   
Comments
chitra81 17-Mar-11 2:11am
   
thanks for the solution it worked perfectly, but can you tell how can i pick balance which was on last day of last year and last day of last month
Rate this:
Please Sign up or sign in to vote.

Solution 1

SELECT year,director, sum(issues_received) as Newly_Received,sum(issues_solved) as Solved,sum(issues_pending) as Remaining FROM subdivision_master where director='dhbvn' group by director,year UNION
select SUM(convert(int,issues_pending)) as previous_balance from subdivision_master where year=CONVERT(int,year)-1


I think this works for you
   
Comments
Dalek Dave 16-Mar-11 5:45am
   
Good Call.
Toniyo Jackson 16-Mar-11 6:02am
   
Its not correct. If we are using union both select query should have same columns

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100