help selecting sums from two similar tables
-
I'm having a problem. Anyone know how to make this query better? I have two tables that are pretty much almost the same table. What I'm trying to find is the sum of sales for each month over a given period (3 years) Records can exist in either table but won't exist in both tables. I've got the best solution that I can think of but I don't even know if the results are accurate. Any help is gratefully appreciated.
here's what I have so far but it doesn't return the correct info:
SELECT isnull(SUM(docamnt),0) as monthlysum, datepart(month,sop10100.invodate) as thismonth, datepart(year,invodate) as thisyear, '1' as thistable
FROM SOP10100
WHERESOP10100.SOPTYPE=3 and SOP10100.bchsourc<>'Sales Void'
AND SOP10100.invoDATE >= Dateadd(year,-3,getdate())
AND SOP10100.invoDATE <= getdate()
and sop10100.custnmbr = '10255'
group by invodate
union all
SELECT IsNull(SUM(BT.dbo.SOP30200.docamnt),0) as monthlysum, datepart(month,sop30200.invodate) as thismonth, datepart(year,sop30200.invodate) as thisyear, '2' as thistable
FROM SOP30200
WHERE SOP30200.SOPTYPE=3 and SOP30200.bchsourc<>'Sales Void'
AND SOP30200.invoDATE >= Dateadd(year,-3,getdate())
AND SOP30200.invoDATE <= getdate()
AND sop30200.Custnmbr = '10255'
group by datepart(month,sop30200.invodate)
order by thisyear desc, thismonth desc
thanks,
Jon Hirschi
-
Maybe if you replace columns thismonth and thisyear by convert(int, convert(char(6),getdate(),112)) as ThisYearMonth you could gain on grouping/sorting. You could also test performance with full-outer-join. (might be bit messy select-clause)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
What the problem is, is that I need to get a sum of all transactions for a month, on a per month basis. Tables are basically:
table1:
docamnt,date
12.37, 1/12/2003
25.01 1/15/2003
11.45, 2/20/2003
table2:
docamnt,date
32.17, 1/12/2003
200.56, 2/20/2003
Anyone know how to join these tables together so that I get all transactions for a month from both tables? eg, I want:
result set:
docamnt, monthyear
69.55, 1/2003
212.01, 2/2003
with some hints from alzdba, I got this query which will get monthly sums for the last three years from one table. I could do a union of a similar table, but that would duplicate some months. Another problem with this query is that it won't create null columns or zero value columns for months where there were no purchases made. Can anyone out there help me with this? Thanks alzdba for your help, and thanks in advance for any help anyone can offer.
thanks
SELECT convert(char(6),sop30200.invodate,112) as monthyear, SUM(SOP30200.docamnt) as monthlysum, '2' as thistable
FROM SOP30200
WHERE SOP30200.SOPTYPE=3 and SOP30200.bchsourc<>'Sales Void'
AND convert(char(6),sop30200.invodate,112) > convert(char(6),Dateadd(year,-3,getdate()),112)
And sop30200.invodate <= getdate()
group by convert(char(6),sop30200.invodate,112)
order by monthyear desc
-
Don't do the CHAR(6) conversion. Not necessary. Try this:
SELECT thismonth, thisyear, SUM(monthlysum)
(
SELECT
DATEPART(MONTH, invodate) as thismonth
, DATEPART(YEAR, invodate) as thisyear
, ISNULL(SUM(docamnt), 0) as monthlysum
FROM SOP10100
WHERE
SOPTYPE= 3
AND bchsourc<> 'Sales Void'
AND invoDATE >= DATEADD(YEAR,-3,GETDATE())
AND invoDATE <= GETDATE()
and custnmbr = '10255'
GROUP BY
DATEPART(MONTH, invodate) as thismonth
, DATEPART(YEAR, invodate) as thisyear
,
UNION ALL
SELECT
DATEPART(MONTH, invodate) as thismonth
, DATEPART(YEAR, invodate) as thisyear
, ISNULL(SUM(docamnt), 0) as monthlysum
FROM SOP30200
WHERE
SOPTYPE= 3
AND bchsourc<> 'Sales Void'
AND invoDATE >= DATEADD(YEAR,-3,GETDATE())
AND invoDATE <= GETDATE()
and custnmbr = '10255'
GROUP BY
DATEPART(MONTH, invodate) as thismonth
, DATEPART(YEAR, invodate) as thisyear
,
)
GROUP BY thismonth, thisyear
ORDER BY thisyear desc, thismonth descEdited by - jpipes on 06/18/2003 11:49:26 AM
-
jpipes,
Thanks for your help, I'm getting an error on the statement:
Incorrect syntax near the keyword 'GROUP'.
It appears that it is choking on the group last group by portion on the page. I've tried fixing it, but I have yet to figur out why it's doing that. Do you know?
Thanks for the help!
-
I forgot the FROM clause
try:
SELECT thismonth, thisyear, SUM(monthlysum)
FROM
(SELECT ... rest of codesorry bout that one...
-
You can use a derived table like this
Select Sum(T0.Docamnt)+T1.Docamnt_Sum As Total_Sum, T1.Docamnt_Month As Unique_Month
From Table1 T0,
(Select Sum(Docamnt) As Docamnt_Sum, -- Derived Table starts here
DatePart(mm, Date) As Docamnt_Month
From Table2
Group By DatePart(mm, Date)) As T1 -- Derived Table ends here
Where DatePart(mm, T0.Date) = T1.Docamnt_Month
Group By T1.Docamnt_Sum, T1.Docamnt_Month
MW
MW -
If data does not exist in both tables on [year_month] basis you can avoid the outer-grouping from jpipes solution.
btw could you give a litle feedback on the percentage of cpu- and elaps-time you managed to save ?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Alzdba, the problem wasn't trying to get the query to run faster, but to get it to return the correct information. I was having a bear of a time to have it return the correct information. Someone I know suggested making a view of the original information and then selecting from the view. That's what I've done because it appears to returning the correct information.
view is this:
SELECT DATEPART(year, DOCDATE) AS thisyear, DATEPART(month, DOCDATE) AS thismonth, SUM(DOCAMNT) AS monthamnt, CUSTNMBR
FROM dbo.SOP10100
WHERE SOPTYPE = 3 AND bchsourc <> 'Sales Void' AND docdate >= DATEADD(YEAR, - 3, GETDATE()) AND docdate <= getdate()
GROUP BY DATEPART(year, DOCDATE), DATEPART(month, DOCDATE), CUSTNMBR
UNION
SELECT datepart(year, docdate), datepart(month, docdate), SUM(docamnt), custnmbr
FROM sop30200
WHERE soptype = 3 AND bchsourc <> 'Sales Void' AND docdate >= DATEADD(YEAR, - 3, GETDATE()) AND docdate <= getdate()
GROUP BY datepart(year, docdate), datepart(month, docdate), custnmbr
then i get the aggragated info out with this:
select thisyear, thismonth, sum(monthamnt) as monthlysum
from buz_customer_overview where custnmbr = '10509093'
group by thisyear, thismonth
order by thisyear, thismonth
The queries that have were provided here weren't getting the proper info back. I apologize for my lack in communicating what I really needed. So, for right now, the above is my working solution. I don't know if it's the best solution, but it works.
Fyi, the execution time on it is:
duration = 32
cpu = 82
and reads = 106
rows returned = 36
Thanks alzdba, mworku, jpipes for your help!
-
Jon,
be aware of the fact that UNION sorts out duplicate rows ! So if you know (like you said) "Records can exist in either table but won't exist in both tables" use the union all to avoid the extra sort.
I guess regarding semantics, in this case you'll have to use UNION ALL if both tables contain rows from the same month, but not from the same date.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
alzdba,
Thanks for the heads up. I've changed the union to a union all. Thanks again!
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply