October 7, 2011 at 11:29 am
Looks like home work. Show us what you have written so far and where you seem to be having a problem.
October 7, 2011 at 12:24 pm
This is what I have to sum the columns, which is not a problem. The problem lies with getting the min and max dates for each column for where data is present within the date range.
SELECT
A.name As Name,
Round(Sum(A.aa), 2) AS AA,
Round(Sum(B.bb), 2) AS BB,
Round(Sum(B.cc), 2) AS CC,
Round(Sum(B.dd), 2) AS DD
FROM
A INNER JOIN
B ON A.name = B.name
WHERE
A.date = B.date
AND
A.Name IN ('bob')
AND
A.date BETWEEN '2011-01-2' AND '2011-01-07'
GROUP BY
A.name
ORDER BY
A.name
October 7, 2011 at 2:53 pm
Never mind, I have resolved the problem. And sorry to disappoint but this was not a homework assignment!
SELECT
A.name As Name,
(Select Min(date) from A where A.name = B.name and A.date between '2011-01-02' and '2011-01-07' and A.aa is not null) as MinAADate,
Round(Sum(A.aa), 2) AS AA,
(Select Max(date) from A where A.name = B.name and A.date between '2011-01-02' and '2011-01-07' and A.aa is not null) as MaxAADate,
(Select Min(date) from B where A.name = B.name and B.date between '2011-01-02' and '2011-01-07' and B.bb is not null) as MinBBDate,
Round(Sum(B.bb), 2) AS BB,
(Select Max(date) from B where A.name = B.name and B.date between '2011-01-02' and '2011-01-07' and B.bb is not null) as MaxBBDate,
(Select Min(date) from B where A.name = B.name and B.date between '2011-01-02' and '2011-01-07' and B.cc is not null) as MinCCDate,
Round(Sum(B.cc), 2) AS CC,
(Select Max(date) from B where A.name = B.name and B.date between '2011-01-02' and '2011-01-07' and B.cc is not null) as MaxCCDate,
(Select Min(date) from B where A.name = B.name and B.date between '2011-01-02' and '2011-01-07' and B.dd is not null) as MinDDDate,
Round(Sum(B.dd), 2) AS DD,
(Select Max(date) from B where A.name = B.name and B.date between '2011-01-02' and '2011-01-07' and B.dd is not null) as MaxDDDate
FROM
A INNER JOIN
B ON A.name = B.name
WHERE
A.date = B.date
AND
A.Name IN ('bob')
AND
A.date BETWEEN '2011-01-2' AND '2011-01-07'
GROUP BY
A.name,
B.name
ORDER BY
A.name
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply