Re: Trouble with query

From: Tom Moreau (tom_at_dont.spam.me.cips.ca)
Date: 05/04/04 

Date: Tue, 4 May 2004 15:13:59 -0400

Congrats! What you have here is known as a correlated subquery. The WHERE
clause is how the two queries are correlated. So for each row of the
"outer" query, the "inner" query is processed, based on the correlation
criteria. In this way, you can do running totals.

Various books on SQL cover this. Check out Joe Celko's books (SQL for
Smarties 2nd Ed or SQL Puzzles and Answers) or mine:

http://www.apress.com/book/bookDisplay.html?bID=72

(The book has been mentioned in the newsgroups:
http://groups.google.ca/groups?q="advanced+transact-sql+for+sql+server+2000"&hl=en&lr=&ie=UTF-8&oe=UTF-8&sa=G&scoring=d) 

-- 
Tom
---------------------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Zoury" <yanick_lefebvre@hotmail.com> wrote in message
news:OrcVulgMEHA.3988@TK2MSFTNGP09.phx.gbl...
here's the final version including the other table have talked about..
---
-- creates a temporary table for containing some transaction records
create table #trans
(
 [idtrans] int identity primary key,
 [idtranstype] int,
 [date] datetime,
 [amount] smallmoney
)
go
create table #transtype
(
 [idtranstype] int identity primary key,
 [code] char(1),
 [iscredit] bit
)
-- add available transaction types
insert into #transtype([code], [iscredit]) values('F', 1) -- id = 1
insert into #transtype([code], [iscredit]) values('E', 1) -- id = 2
insert into #transtype([code], [iscredit]) values('+', 1) -- id = 3
insert into #transtype([code], [iscredit]) values('C', 0) -- id = 4
insert into #transtype([code], [iscredit]) values('L', 0) -- id = 5
insert into #transtype([code], [iscredit]) values('-', 0) -- id = 6
-- add some transactions to the table
insert into #trans([idtranstype], [date], [amount]) values(1, getdate(),
800.00)   -- credit  balance expected : 800
insert into #trans([idtranstype], [date], [amount]) values(5, getdate(),
150.00)   -- debit  balance expected : 650
insert into #trans([idtranstype], [date], [amount]) values(6, getdate(),
150.00)   -- debit  balance expected : 500
insert into #trans([idtranstype], [date], [amount]) values(4, getdate(),
150.00)   -- debit  balance expected : 350
insert into #trans([idtranstype], [date], [amount]) values(4, getdate(),
150.00)   -- debit  balance expected : 200
insert into #trans([idtranstype], [date], [amount]) values(2, dateadd(d, 1,
getdate()), 120.00) -- credit  balance expected : 320
insert into #trans([idtranstype], [date], [amount]) values(5, dateadd(d, 1,
getdate()), 150.00) -- debit  balance expected : 170
go
--  heres a sample of what i need to get in the select query :
----------------------------------------------------------------------------
----------------------
-- date  credit  debit  balance
----------------------------------------------------------------------------
----------------------
-- 2004-05-04 800.00    --    800.00
-- 2004-05-04 120.00    --    920.00
-- 2004-05-04   --    150.00  770.00
-- 2004-05-04   --    150.00  620.00
-- 2004-05-04   --    150.00  470.00
-- 2004-05-04   --    150.00  320.00
-- 2004-05-04   --    150.00  170.00
----------------------------------------------------------------------------
----------------------
-- select statement
select  convert(char(10), t.Date, 120) as [Date],
 case when tt.iscredit = 1 then t.amount end as Credit,
 case when tt.iscredit = 0 then t.amount end as Debit,
 (select sum(case when ty.iscredit = 1 then tr.amount else -tr.amount end)
  from  #trans as tr inner join
  #transtype ty on tr.idtranstype = ty.idtranstype
  where  convert(char(10), tr.Date, 120) < convert(char(10), t.Date, 120) or
  convert(char(10), tr.Date, 120) = convert(char(10), t.Date, 120) and
  (ty.iscredit > tt.iscredit or (ty.iscredit = tt.iscredit and tr.idtrans <=
t.idtrans))) as Balance
from #trans as t inner join
 #transtype tt on t.idtranstype = tt.idtranstype
order by [Date],
  tt.iscredit desc,
  t.idtrans
-- drop temp table
drop table #trans
drop table #transtype
go
---
I have lots of questions on how the system actually treats the "where
clause" of the subquery. I've base the conditions on what you posted earlier
and it seems to me that when a field of the subquery is compared to a field
of the master query it's like if we are comparing this field to whatever
have already been processed.. is that right?  <- i not sure if made myself
clear here.. :OP
Would you know, by any chance, where I could find more information about
this topic?
Thanks a lot to both of you anyway! :O)
-- 
Best Regards
Yanick Lefebvre

Relevant Pages

  • Query Multiple Tables
    ... Actually the subquery is just pure sql language, ... query, switch to SQL view, copy the text, and then paste ... >DMax in a query criteria in the same way. ...
    (microsoft.public.access.queries)
  • Re: SQL Division
    ... however, is that you CANNOT, then have any brackets in your subquery. ... brackets and parentheses within the subquery ... the SQL text remains identical. ...
    (microsoft.public.access.queries)
  • Re: Help with derived table SQL statement in Access
    ... Table aliases increase the clarity of SQL by explicitly noting each ... If I remove the where clause from the subquery, it works, but I get ... SELECT expression on the FROM clause, but that is just that, a table ...
    (microsoft.public.access.queries)
  • Re: System.Data.SqlClient.SqlException: Subquery returned more than 1 value.
    ... The site is using SQL Server 7. ... > INSERT INTO OrderItems (ClientID, ProductID, OrderHeaderID, Quantity, ... Subquery returned more than 1 value. ... > Dim conn As New System.Data.SqlClient.SqlConnection ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Extracting maximums
    ... You should use a correlated subquery: ... field2 has the largest value of all rows for that value of field1. ... - correlation (the overall principle being used here, ...
    (microsoft.public.access.queries)