November 8, 2006 at 12:34 pm
I need help in writing a query.
The query should get top 10 items and their values from current year and the values for the same items from previous year table.
I was able to write the code for 1st part that gets values from 1st table but I don't know how to get the values from 2nd table.
The 2 tables does not have any primary/foreign key relations. Both tables have same structure and same columns.
I am attaching some images below to give more information.
Image of results from my query.
Image of how the final output should look like.
The Store Procedure code is:
ALTER Procedure [dbo].[free_customsHS4](
@TblName1 varchar(20),
@TblType varchar(20),
@District varchar(6),
@Month varchar(3)
)
AS
Begin
SET NOCOUNT ON;
Declare @SQuery nvarchar(3000)
set @TblName1 = '[' + @TblName1 + ']'
set @TblType = '[' + @TblType + ']'
SELECT @SQuery = 'select top 10 a.commodity1 as HS4, b.descrip_1 as Description,
sum(a.all_val_mo) as [Amount],
(sum(a.all_val_mo)/(select Sum(a.all_val_mo) FROM ' + @TblName1 + 'a
where a.stat_month <=' + @Month + ' and a.district=' + @District +'))*100 as [% Share]
FROM ' + @TblName1 + ' a left outer join ' + @TblType + ' b on a.commodity1=b.commodity1
where a.stat_month <=' + @Month + ' and a.district=' + @District +'
Group by a.commodity1, b.descrip_1
order by [Amount] desc'
EXEC sp_executesql @SQuery
END
November 8, 2006 at 12:58 pm
If you need to have dynamic sql...
The code below should give you the results you are looking for.
create table tmp2004 (descID int, amt numeric, share numeric(4,2)
create table tmp2005 (descID int, amt numeric, share numeric(4,2)
create table desc (descID, description varchar(50), hs4 int)
create procedure thisproc @table1 varchar(50), @table2 varchar(50)
as
set nocount on
declare @strSQL nvarchar(2000)
set @strSQL = 'SELECT d.description, desc.hs4, t4.amt, t4.share, t5.amt, t5.share '
+ ' from desc d '
+ ' LEFT JOIN ' + @table1 + ' t4 ON d.descID = t4.descID '
+ ' LEFT JOIN ' + @table2 + ' t5 ON d.descID = t5.descID '
exec sp_executeSQL @strSQL
November 8, 2006 at 1:38 pm
I will try to implement your code and see what happens.
I was playing around with my code and I was able to calculate Amount2 for previous year but I am still stuck at Share2. The code after modification is
ALTER
Procedure [dbo].[free_customsHS41](
@TblName1
varchar(20), @TblName2 varchar(20), @District varchar(6), @Month varchar(3)
)
AS
Begin
SET NOCOUNT ON;
Declare
@SQuery nvarchar(3000)
SELECT
@SQuery = 'select top 10 a.commodity1 as HS4, sum(a.all_val_mo) as [Amount1],
(sum(a.all_val_mo)/(select Sum(a.all_val_mo) FROM '
+ @TblName1 + 'a where a.stat_month <=' + @Month + ' and a.district=' + @District +'))*100 as [% Share1],
(select sum(b.all_val_mo) from '
+ @TblName2 +' b where b.commodity1=a.commodity1 and b.stat_month <=' + @Month + ' and b.district=' + @District +') as [Amount2]
--(sum(b.all_val_mo)/(select Sum(b.all_val_mo) FROM '
+ @TblName2 + 'b where b.commodity1=a.commodity1 and
--b.stat_month <='
+ @Month + ' and b.district=' + @District +'))*100 as [% Share2]
from '
+ @TblName1 + 'a where a.stat_month <=' + @Month + ' and a.district=' + @District +'
Group by a.commodity1 order by [Amount1] desc'
EXEC
sp_executesql @SQuery
END
The results look like
November 9, 2006 at 5:31 am
Hello,
I think you should not use dynamic SQL for this... but let's leave that for now and look at the way how to get the result.
It is a lot easier to look for solution in normal SQL. If you later decide to put it into dynamic SQL, it should be easy.
You didn't supply any table definitions and sample data, so maybe it will not fit your data precisely, but it should give you at least an idea.
Let me know if there are some problems - I had to guess data types and had no data to test it, just ran a test with empty tables (so that the syntax should be OK).
declare @month char(2), @district varchar(40)
set @month = '09'
set @district = 'Lake District'
SELECT this_year.HS4, this_year.Amount1, (this_year.amount1/this_year.total1)*100 as Share1,
prev_year.Amount2, (prev_year.amount2/prev_year.total2)*100 as Share2
FROM
(SELECT TOP 10
a.commodity1 as HS4,
SUM(a.all_val_mo) as Amount1,
(select sum(a1.all_val_mo) from #2005expview a1 WHERE a1.stat_month <= @month and a1.district = @district) as Total1
FROM #2005expview a
WHERE a.stat_month <= @month and a.district = @district
GROUP BY a.commodity1
ORDER BY Amount1 DESC) this_year
JOIN
(SELECT
b.commodity1 as HS4,
SUM(b.all_val_mo) as Amount2,
(select sum(b1.all_val_mo) from #2004expview b1 WHERE b1.stat_month <= @month and b1.district = @district) as Total2
FROM #2004expview b
WHERE b.stat_month <= @month and b.district = @district
GROUP BY b.commodity1) prev_year ON this_year.HS4=prev_year.HS4
November 20, 2006 at 9:02 am
Vladan,
Your code worked perfectly.
Thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
