May 4, 2009 at 3:12 pm
Hello, I'm having a brain freeze... Please help. I have two tables tblfunds and tbltransactions and I output 3 fields:
Fund | Daily Sales | Monthly Sales
The problem is at the start of the month when there are no transactions I would like to just display 0.00
Current results when there are just results for FUND 1
Fund | Daily Sales | Monthly Sales
Fund1 0.0054840.00
--
Desired Results if there were no transactions for Fund 2 and transactions for fund 1
Fund | Daily Sales | Monthly Sales
Fund1 0.0054840.00
Fund2 0.00 0.00
and my sql is:
SELECT
daily.Fund,
daily.salesamount AS [Daily Sales Amount],
monthly.salesamount AS [Monthly Sales Amount]
FROM
(SELECT
ISNULL(t.fund, f.description) AS fund,
ISNULL(SUM(t.ticketAmt), 0.00) AS [SalesAmount],
f.isOpen
FROM
tblFunds f
LEFT OUTER JOIN tbltransactions t
ON f.description = t.fund
AND (CONVERT(CHAR, t.createDate, 101) = CONVERT(CHAR, GETDATE(), 101))
AND t.status <> 'deleted'
WHERE
(f.isopen = 0) and (f.description not like '%WaitList%')
GROUP BY
ISNULL(t.fund, f.description),
f.isopen) Daily
INNER JOIN (
SELECT
ISNULL(t.fund, f.description) AS fund,
ISNULL(SUM(t.ticketAmt), 0.00) AS [SalesAmount],
f.isOpen
FROM
tblfunds f
INNER JOIN tblTransActions t
ON f.description = t.fund
WHERE
DATEPART(yy, t.createDate) = DATEPART(yy, GETDATE())
AND (DATEPART(m, t.createDate) = DATEPART(m, GETDATE()))
AND
(f.isopen = 0)
AND t.status <> 'deleted'
GROUP BY
ISNULL(t.fund, f.description),
f.isopen
) Monthly
ON daily.fund = monthly.fund
May 4, 2009 at 3:45 pm
And what does your query return at the start of the month?
May 4, 2009 at 3:46 pm
My query returns nothing at the start of the month:
Fund | Daily Sales | Monthly Sales
May 4, 2009 at 3:54 pm
I'm sorry, I blew right past your output section in your original post.
In the case where the daily.fund is null, is there a fund record like:
"Fund2" null null in the table? Or is there just no record? Some sample data might help with answering.
May 4, 2009 at 3:58 pm
Here is the output:
start of month w/ no transactions:
Fund | Daily Sales | Monthly Sales
--
start of month w/ 1 transaction
Fund | Daily Sales | Monthly Sales
Fund1 0.00 54840.00
Desired output would be w/ 1 transaction(for fund1):
Fund | Daily Sales | Monthly Sales
Fund1 0.00 54840.00
Fund2 0.00 0.00
May 4, 2009 at 4:11 pm
You may need to change the INNER JOIN to a RIGHT OUTER JOIN between the two derived tables, as the WHERE clause on the Daily table is different than the Monthly one, but maybe this will help:SELECT
daily.Fund,
COALESCE(daily.salesamount, 0.00) AS [Daily Sales Amount],
COALESCE(monthly.salesamount, 0.00) AS [Monthly Sales Amount]
FROM
(
SELECT
ISNULL(t.fund, f.description) AS fund,
ISNULL(SUM(t.ticketAmt), 0.00) AS [SalesAmount],
f.isOpen
FROM
tblFunds f
LEFT OUTER JOIN
tbltransactions t
ON f.description = t.fund
AND CONVERT(CHAR, t.createDate, 101) = CONVERT(CHAR, GETDATE(), 101)
AND t.status 'deleted'
WHERE
f.isopen = 0
AND f.description NOT LIKE '%WaitList%'
GROUP BY
ISNULL(t.fund, f.description),
f.isopen
) AS Daily
INNER JOIN
(
SELECT
ISNULL(t.fund, f.description) AS fund,
ISNULL(SUM(t.ticketAmt), 0.00) AS [SalesAmount],
f.isOpen
FROM
tblfunds f
LEFT OUTER JOIN
tblTransActions t
ON f.description = t.fund
AND DATEPART(yy, t.createDate) = DATEPART(yy, GETDATE())
AND DATEPART(m, t.createDate) = DATEPART(m, GETDATE())
AND t.status 'deleted'
WHERE
AND f.isopen = 0
GROUP BY
ISNULL(t.fund, f.description),
f.isopen
) AS Monthly
ON daily.fund = monthly.fund
May 5, 2009 at 8:02 am
Changing either of the two inner joins with an outer join could fix this. Can you post table definitions for tblfunds and tbltransactions along with some relevant sample data?
May 6, 2009 at 8:04 am
Something like this?
(A word of free advice: Be carefull converting the createdate column, this will often make the engine use a table scan to answer your questions.)
use tempdb
go
create table dbo.tblFunds (
fund varchar(10) not null,
description varchar(256) not null,
isOpen bit not null default 1,
constraint pkFunds primary key ( fund)
);
go
create table dbo.tblTransactions (
id bigint identity(1,1) not null,
fund varchar(10) not null,
ticketAmt money null,
createdate datetime not null default getdate(),
status varchar(10) not null default 'active',
constraint pkTransactions primary key (id),
constraint fkTransactionsFund foreign key (fund) references dbo.tblFunds(fund)
);
go
insert tblFunds (fund, description, isOpen)
select 'Fund', 'My first fund', 0
union all
select 'Fund1', 'My 2nd fund', 0;
go
set nocount on;
-- Create a nice mix of transactions in our test set to illustrate index usage:
-- all transactions have 'Fund', none created on 'Fund1' yet.
while isnull((select sum(ticketAmt) from dbo.tblTransactions), 0) = dates.DailyFrom
and t.createdate < dates.DailyTo
and t.status 'deleted'
and t.fund = f.fund), 0.0) AS [Daily Sales Amount],
isnull((
select
isnull(sum(t.ticketAmt), 0.0) as salesamount
from dbo.tblTransactions t
where t.createdate >= dates.MonthlyFrom
and t.createdate < dates.MonthlyTo
and t.status 'deleted'
and t.fund = f.fund), 0.0) AS [Monthly Sales Amount]
FROM tblFunds f
cross join (
select
convert(datetime, convert(varchar(10), getdate(), 120), 120) as dailyFrom,
dateadd(day, 1, convert(datetime, convert(varchar(10), getdate(), 120), 120)) as dailyTo,
convert(datetime, convert(varchar(8), getdate(), 120) + '01', 120) as MonthlyFrom,
dateadd(month, 1, convert(datetime, convert(varchar(8), getdate(), 120) + '01', 120)) as MonthlyTo
) as dates
go
drop table dbo.tblTransactions
go
drop table dbo.tblFunds
go
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply