Getting results using three tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Getting results using three tables

Author

Topic

karrojo
Starting Member

26 Posts

Posted - 2011-05-10 : 22:45:59

Good Day!

i really need all your help

i have a query, getting results using three tables.
1. MasterTable
2. ChecksTable
3. SuppChecksTable

however, i have the ff conditions
1. That in ChecksTable, i only get the checks issued on 2009 and compute the sum of amount
2. In SuppChecksTable, only checks issued in 2009.

i got the result that i expected, however if a record in SuppChecksTable has check issued in 2009 and other year, it gives 2 results:
(1) the checks issued in 2009 and
(2) the checks issued in previous years

i want to eliminate the result the checks are issued the previous years

here is my query:

select distinct a.col1, a.col2, a.col3, sum(b.Amt),
'SuppPay' = case
when (printed between '01/01/2009' and '01/01/2010') then c.col4
else '--'
end,
'Amount' = case
when (printed between '01/01/2009' and '01/01/2010') then c.col5
else '--'
end,
'Date_Printed' = case
when (printed between '01/01/2009' and '01/01/2010') then c.col6
else '--'
end
from MasterTable as a inner join ChecksTable as b
on a.col1 = b.col1
inner join SuppChecksTable as c
on a.col1 = c.col1
where (b.date between '01/01/2009' and '01/01/2010')
group by a.col1, a.col2, a.col3

your comments and suggestions will greatly help. thanks so much

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-10 : 22:52:54

add another condition ?


and (c.date >= '2009-01-01' and c.date < '2010-01-01')

you might want to change the rest of the date checking to >= '2009-01-01' and < '2010-01-01'

As using "between '01/01/2009' and '01/01/2010", you are including the date 2010-01-01 in the result

KH
[spoiler]Time is always against us[/spoiler]

karrojo
Starting Member

26 Posts

Posted - 2011-05-11 : 01:23:49

thanks for the reply khtan

but i still get the same results :(

double result of record with dates 2009 and previous years

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-11 : 01:26:04

then do provide the table structure, sample data and expected result.

KH
[spoiler]Time is always against us[/spoiler]

karrojo
Starting Member

26 Posts

Posted - 2011-05-11 : 01:50:10

MasterTable (idno,lname, fname,mi)
ChecksTable (idno,date_issued, amount)
SuppChecksTable (idno, PaymentDetails, amount, date_issued)

Sample Data
MasterTAble
idno lname fname mi
1 Smith John Q

ChecksTable
idno date_issued amount
1 01/10/2009 5000
1 02/10/2009 5000

SuppChecksTable
idno PaymentDetails Amount Date_Issued
1 Payment for Jan-March 15000 02/20/2009
1 Payment for Jan-March 2008 15000 03/20/2008

Expected Result
1. To get the sum of the checks issued in 2009 only on the table ChecksTable
2. To get the checks issued in 2009 only on the table SuppChecksTable. Excluding the checks issued in the previous years

Sample:
idno LName Fname MI Payment2009 PaymentDetails Amount Date_Issued
1 Smith John Q 10000 Payment for Jan-March 15000 02/20/2009

- the Payment2009 column is the sum of all checks issued in 2009 on the Table ChecksTable
- the PaymentDetails, Amount, Date_issued will be extracted from table SuppChecksTable and will only get the checks issued in 2009

My Sample Query result:
idno LName Fname MI Payment2009 PaymentDetails Amount Date_Issued
1 Smith John Q 10000 Payment for Jan-March 15000 02/20/2009
1 Smith JOhn Q 10000 -- -- --

- i want the second line not to be included since i only need the first line result

thanks so much KH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources