October 7, 2011 at 11:22 am
I have what I think is a rather complex query to write and was hoping someone here could shed some light on whether it is even possible to write. If not then it we will do the bulk of the work in code but we would like to do it on the server with SQL if possible.
Here are the requirements:
1: We cannot use stored procedures.
2: We will not have write access to the database.
3: Joining 2 tables.
4: Select by a name.
5: Select by a date range.
6: Select the minimum date that a column has data available within the date range.
7: Select the maximum date that a column has data available within the date range.
8: Do 6 & 7 for 1 numeric column in table A.
9: Do 6 & 7 for 3 numeric columns in table B.
10: Numeric columns will be sumed.
11: Table A's structure is:
name (char),
date(date),
aa(numeric)
12: Table B's structure is:
name(char),
date(date),
bb(numeric),
cc(numeric),
dd(numeric)
Table A:
namedate aa
bob1/1/111
bob1/2/112
bob1/3/112
bob1/4/115
bob1/5/115
bob1/6/115
bob1/7/115
bob1/8/115
Table B:
namedate bbccdd
bob1/1/11nullnullnull
bob1/2/111nullnull
bob1/3/1134null
bob1/4/11216
bob1/5/11462
bob1/6/1122null
bob1/7/111nullnull
bob1/8/11 nullnullnull
Resultant Join: select on 'bob' date range '2011-01-02' - '2011-01-07' displaying one row with dates and sumed columns.
nameminDate-aa sum-aamaxDate-aaminDate-bb sum-bb
bob2011-01-02 24 2011-01-07 2011-01-02 13
maxDate-bbminDate-cc sum-ccmaxDate-ccminDate-dd sum-dd
2011-01-07 2011-01-03 13 2011-01-06 2011-01-04 8
maxDate-dd
2011-01-05
I'm not a great SQL expert and I'm not sure where to even begin with this query. Thanks for any help or insight.
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 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply