March 30, 2009 at 1:34 pm
Hello all, the below sql statement yields funds that have transactions for a certain date. But when there are no transactions I would like it show for the sales amount field 0.00.
SELECTt.fund,
'Daily Sales:' as Title,
sum(t.ticketAmt) as [Sales Amount],
f.isOpen
FROM tblfunds f INNER JOIN
tblTransActions t ON f.description = t.fund
where (CONVERT(char, t.createDate, 101) = '03/30/2009')
and (f.isopen = 0)
group by t.fund, f.isopen
example out put when there are transactions:
fund1 Daily Sales:25000.000
fund2 Daily Sales:50000.000
example of how I would like the out put to look if there were no transactions:
fund1 Daily Sales:0.000
fund2 Daily Sales:0.000
March 30, 2009 at 1:43 pm
Give this a try. It is untested as you didn't provide any DDL for the tables or sample data. For better help, try reading the first article I have referenced below in my signature block regarding asking for assistance.
SELECT
-- t.fund,
f.description as Fund,
'Daily Sales:' as Title,
sum(isnull(t.ticketAmt, 0.00)) as [Sales Amount],
f.isOpen
FROM
tblfunds f
LEFT OUTER JOIN tblTransActions t
ON (f.description = t.fund)
where
((t.createDate >= '2009-03-30' and
t.createDate < '2009-04-01') or
(t.createDate is null))
-- (CONVERT(char, t.createDate, 101) = '03/30/2009')
and (f.isopen = 0)
group by
t.fund,
f.isopen
March 30, 2009 at 1:50 pm
I think you just want a LEFT OUTER JOIN. Something like this:
DECLARE @funds TABLE (fund VARCHAR(10), isopen BIT)
DECLARE @trans TABLE (fund VARCHAR(10), createDate DATETIME, ticketAmt DECIMAL(10, 2))
INSERT INTO @funds (
fund,
isopen
)
SELECT
'FUND1',
0
UNION ALL
SELECT
'Fund2',
0
INSERT INTO @trans (
fund,
createDate,
ticketAmt
)
SELECT
'Fund1',
GETDATE()-1,
10
UNION ALL
SELECT
'Fund1',
GETDATE()-1,
10
UNION ALL
SELECT
'Fund2',
GETDATE()-1,
20
UNION ALL
SELECT
'Fund2',
GETDATE()-1,
20
SELECT
ISNULL(T.fund,f.fund) AS fund,
'Daily Sales:' as Title,
ISNULL(sum(t.ticketAmt), 0.00) as [Sales Amount],
f.isOpen
FROM
@funds f LEFT OUTER JOIN
@trans t
ON f.fund = t.fund AND
(CONVERT(char, t.createDate, 101) = '03/30/2009')
where
(f.isopen = 0)
group by
ISNULL(T.fund,f.fund) ,
f.isopen
Please take note of how I created a little bit of test data to work with, this is based on the links in my signature line. It makes providing a solution easier and helps us test it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply