Re: Can I do this in SQL? How?

[CRPence <CRPbottle@xxxxxxxxx>]

  The query that Rick proposed did not denote what [of which file] 
the dollar amount represented.  Although an unstated requirement, 
that detail was conspicuous in the proposed report output, where 
each had its own column.  Of course literals could easily be added 
in a column to denote that [i.e. which dollar amount] for each row.

  The original requirement was stated as effectively "list\select 
all accounts with at least one contribution and one distribution". 
The query in the quoted text performs "list all accounts with ( at 
least one contribution ) AND ( at least one distribution )", not 
"list\select all accounts with ( at least one contribution ) AND ( 
one distribution )".  Since the limited amount of sample output 
reflects both the latter as well as the former logic, which logic 
was desirable, is not obvious; presumably the former, such that it 
really "worked".

  FWiW the first CTE is likely the same as an INTERSECT of the data 
in Acct# from the Cont & Dist tables; IMO having requested the 
intersection of the account number in those two tables would be 
clearer than selecting those rows from the parent which are IN the 
dependents.  The second CTE is presumably an unnecessary copy of 
data, at least to obtain the originally described results; i.e. the 
FileID column exists only to provide a field on which to ORDER BY 
but omit from the result.  Since the given sample output did not 
imply such an order, probably the order could be effected across the 
existing column Acct# for which no additional copy is required; the 
sample output did show ascending order of Acct#.

  As to the columns matching in the UNION, generally I would both 
choose NULL versus a zero value and use CAST(NULL as DECIMAL(7, 2)) 
[or similar casting, to the desired data type\attributes] to effect 
the consistent type for the literal [or the NULL value] representing 
the non-existent CONTR$ and DIST$ values.

Regards, Chuck

Jack Prucha wrote:
> Thanks Joe & Rick.
>
> Joe, it worked perfectly after I put the comma back in after the
> first CTE.
>
> Joe Pluta wrote:
> > -- Get accounts with at least one of each
> > with T1 as (
> >   select ACCT# from AcctTbl
> >     where ACCT# in (select ACCT# from Conttbl)
> >       and ACCT# in (select ACCT# from DistTbl)),
> >
> > -- Create list of transactions
> > T2 as (
> > select 1 FileID, T1.ACCT#, CONTR$, 0 DIST$
> >   from T1 join ContTbl on T1.ACCT# = ContTbl.ACCT#
> > union all
> > select 2 FileID, T1.ACCT#, 0 CONTR$, DIST$
> >   from T1 join DistTbl on T1.ACCT# = DistTbl.ACCT#)
> >
> > -- Now list the results
> > select ACCT#, CONTR$, DIST$ from T2
> >   order by FileID
> >
> > You may need a little tweaking to make sure the columns match 
> > in the two selects for the union in T2, but this is the general
> > idea. I am a big fan of common table expressions. They work
> > wonders.
> >
> > Jack Prucha wrote:
> > > I'm trying to select a list that looks like this:
> > >
> > > Acct#  Contr$  Dist$
> > > 00001  15.00
> > > 00001  28.00
> > > 00001          30.00
> > > 00005  10.00
> > > 00005          25.00
> > >
> > > etc.
> > >
> > > Select accts with at least one contribution and one 
> > > distribution and list them all. The contribution and
> > > distributions can be on the same line or individual lines,
> > > but there's no matching of them. The selection will be by
> > > date ranges also.
> > >
> > > There are three separate tables that look something like
> > > these:
> > >
> > > AcctTbl
> > > Acct#
> > >
> > > ContTbl
> > > Acct#, Contr$, ContrDate
> > >
> > > DistTbl
> > > Acct#, Dist$, DistDate
> > >
> > > I can figure out the where exists to select the accounts
> > > but how do I join these together to get the details?