A simple join to another table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

A simple join to another table

Author

Topic

calladrum
Starting Member

6 Posts

Posted - 2012-09-03 : 09:09:33

Hi everyone

Can you help out a newbie, please? I've inherited the maintenance of some SQL queries. This query delivers a set of results no problem but we now want to display another field - a description of a deduction code - which requires me to JOIN to another table to pull that field out - can anyone help?

Existing query is:

DECLARE @YearWeekFrom AS INTEGER
DECLARE @YearWeekTo AS INTEGER

-------------------------------------------------------------
-- CHANGE THESE VALUES ONLY
-------------------------------------------------------------
SET @YearWeekFrom = 201035 -- Change this value!
SET @YearWeekTo = 201035 -- Change this value!
-------------------------------------------------------------
-------------------------------------------------------------

SELECT Deduction_Code, Year, Week, SUM(ISNULL(NUM_Vouchers,0)) AS NumVouchers,
SUM(ISNULL(Total_Value,0)) AS TotalValue, SUM(ISNULL(Commission_Tot,0)) AS TotalCommission
FROM Vouchers
WHERE ((Year * 100) + Week) >= @YearWeekFrom AND ((Year * 100) + Week) <= @YearWeekTo
GROUP BY Deduction_Code, Year, Week
ORDER BY Year, Week, Deduction_Code

There is another table called DEDUCK which has the Deduction_Code field in common with the VOUCHERS table (above) and we need to pull out the Code_Description field from the DEDUCK table.

Any help much appreciated!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2012-09-03 : 09:48:24

SELECT v.Deduction_Code,d.Code_Description, v.Year, v.Week, SUM(ISNULL(v.NUM_Vouchers,0)) AS NumVouchers,
SUM(ISNULL(v.Total_Value,0)) AS TotalValue, SUM(ISNULL(v.Commission_Tot,0)) AS TotalCommission
FROM Vouchers v
INNER JOIN Deduck d ON
v.Deduction_Code = d.Deduction_Code
WHERE ((v.Year * 100) + v.Week) >= @YearWeekFrom AND ((v.Year * 100) + Week) <= @YearWeekTo
GROUP BY v.Deduction_Code,Code_Description, v.Year, v.Week
ORDER BY v.Year, v.Week, v.Deduction_Code

Are there any columns with date data type? That where clause could be made a lot more efficient if you had date fields instead of year and week fields.

Jim

Everyday I learn something that somebody else already knew

calladrum
Starting Member

6 Posts

Posted - 2012-09-03 : 10:00:07

Hi Jim

Thanks so much for your help - I'll give that a try now. In answer to your date field question - no, all the data tables here are week and year!

Calladrum

calladrum
Starting Member

6 Posts

Posted - 2012-09-03 : 10:12:38

Fabulous - works a treat! And I'm learning...

Thank you, thank you!

Calladrum

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources