April 7, 2009 at 11:06 am
Hello, I have daily and monthly sales totals and i use union to connect the two. Unfortunately, the output(which is correct) is one big column. How can I achieve the same result in rows.
For example, below is my columns and output
Fund, Title, [Sales Amount], isOpen
-------------------------------
Fund1, Daily Sales:,50000.00, 0
Fund2, Daily Sales:,3500.00,0
Fund1, Monthly Sales:,3500000.00, 0
Fund2, Monthly Sales:,170000.00,0
Here is the desired output
Fund, [Daily Sales], [Month To Date], isOpen
-------------------------------
Fund1, 50000.00, 3500000.00, 0
Fund2, 3500.00,2050000.00, 0
ANd finally here is my sql code:
--Daily Sales
(
SELECT
ISNULL(t.fund,f.description) AS fund,
'Daily Sales:' as Title,
ISNULL(sum(t.ticketAmt), 0.00) as [Sales Amount],
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)
group by
ISNULL(t.fund,f.description) ,
f.isopen
)
union all(
--monthly sales
SELECT t.fund, 'Monthly:' as Title,sum(t.ticketAmt) as [Sales Amount], 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 t.fund, f.isopen)
April 7, 2009 at 11:09 am
Instead of doing a Union between those two queries, do a join. Join them on the column that has the Fund1, Fund2, etc., in it.
Does that make sense?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 7, 2009 at 11:18 am
Hello GS, I'm not sure how to join two select. Can you provide an example.
thanks
April 7, 2009 at 12:00 pm
Try this. I can't test it (don't have the tables), so tell me if it doesn't work.
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)
GROUP BY
ISNULL(t.fund, f.description),
f.isopen) Daily
INNER JOIN (SELECT
t.fund,
SUM(t.ticketAmt) 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
t.fund,
f.isopen) Monthly
ON daily.fund = monthly.fund;
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply