Link a Query results with a Table

Hi folks,
This is very useful when we use query results with a table.
for example
here i have written query to get the sum of books from one table then i join that query resultset with another table in which i have sum up.

with chequedet(
BCode,NoofLeaves,TTLBooks)
as
(
select BCode,NoofLeaves,sum(NoofBooks) TTLBooks from ChequeDetails
where JobNo='xxxxxxx'
group by BCode,NoofLeaves
)
select a.bcode,b.NoofLeaves, b.TTLBooks,sum(a.noofbooks)
from ChequeDetails a ,chequedet b
where a.BCode=b.BCode and JobNo='xxxxxxx'
group by a.bcode,b.NoofLeaves,b.TTLBooks
order by a.bcode,b.NoofLeaves, b.TTLBooks

Comments

Post a Comment

Popular posts from this blog

SQL SERVER – TRIM() Function – UDF TRIM()

SQL Server does not have Trim() function. So we can create a own UDF (User Defined Function) function for this since SQL Sever does LTRIM(),RTRIM() functions and we can use this any time. Here I have created a simple Function for this. Create Function Trim(@mText varchar(MAX)) Returns varchar(MAX) AS Begin return LTRIM(RTRIM(@mText)) End You can run this function as shown below here, Select dbo.Trim(' Test ') So this function would return ‘Test’ only as LTRIM() function would cut off the Left side spaces and RTRIM() functiom would cut off the Right side spaces.
Read more