sql server - Stored procedure to get values in the Html table -


i trying values html table. there 2 database tables need fetch values , display in html table. have written stored procedure doesn't work. scenario: user need enter quarter , year . based on input provided summary table. summary table should have columns: id, project, s1,p1. id , project retrieved db table projectdetails , s1, p1 coming details

for example: tables this:

project details:

    id   | project  ------------------------     pr_1 | project     pr_2 | second project 

details table:

pr_1 project

item | status | amount  abc  | s1     | 1000.00 xyz  | s1     | 200.00 test1| s1     | 300.00 test2| p1     | 4000.00 

so summary table should :

id | project            | s1  | p1 pr1| project        | 1500| 4000 pr2| second project | 0   | 0 

here stored proc:

create proc [dbo].[summary] @quarter int, @year int begin  declare @s1 decimal(18,0) declare @p1 decimal(18,0)    select bd.id, bd.project,status,((select sum(amount)from details status='s1') 's1'), ((select sum(amount) details status='p1') 'p1')   projectdetails (nolock) bd  inner join details (nolock) d on bd.project = d.project   bd.quarter = @quarter , bd.year = @year , bd.project = d.project  group bd.lineid, bd.project,status end 

create proc [dbo].[summary] @quarter int, @year int begin  declare @sc decimal(18,0) declare @po decimal(18,0) declare @inv decimal(18,0)   select bd.id, bd.project,status,(select sum(amount)from details status='s1' , bd.project = a.project) s1, (select sum(amount) details b status='p1' , bd.project = b.project) p1   projectdetails (nolock) bd  inner join details (nolock) d on bd.project = d.project   bd.quarter = @quarter , bd.year = @year , bd.project = d.project  group bd.lineid, bd.project,status end 

hope helps, have added missing condition in inner query


Comments

Popular posts from this blog

php - Vagrant up error - Uncaught Reflection Exception: Class DOMDocument does not exist -

vue.js - Create hooks for automated testing -

Add new key value to json node in java -