Join on non-matching - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

Join on non-matching

Author

Topic

twhitle
Starting Member

11 Posts

Posted - 2008-02-12 : 10:51:58

I have two tables. One is a fiscal date(1) table that gives calendar dates based on years, periods, weeks and days. The other(2) only has the years periods and weeks. I would like to pull data from 2 joining the yr, period, and wks and in the where clause specify the calendar dates associated with the years, periods and weeks. I am not getting the right data for what I have now. My join is not correct. Any suggestions?

SELECT Loc, SUM(Qty) AS Qty, SUM(Amt_Paid) AS Amt_Paid, SUM(Mkdn_Amt) AS Mkdn_Amt, SUM(Early_Units) AS Early_Units,
case when SUM(Qty) <> 0 then (SUM(Early_Units)/SUM(Qty))*100 else 0 end as Early_Per,
SUM(Early_Dlrs) AS Early_Dlrs, SUM(Barcode_Units) AS Barcode_Units,
case when SUM(QTY) <> 0 then (SUM(Barcode_units) / SUM(Qty)) * 100 else 0 end as Barcode_per, SUM(Barcode_Dlrs) AS Barcode_Dlrs, SUM(Manual_Units) AS Manual_Units,
case when SUM(QTY) <> 0 then (SUM(Manual_Units) / SUM(Qty)) * 100 else 0 end as Manual_Per ,SUM(Manual_Dlrs) AS Manual_Dlrs, SUM(Manual_Units - Early_Units) as O_Man_Units,
SUM(Manual_Dlrs - Early_Dlrs) as O_Man_Dlrs, case when SUM(Qty) <> 0 then ((SUM(Manual_Units) - SUM(Early_Units)) / SUM(Qty)) *100 else 0 end as O_Man_Per
FROM dbo.TBL_Dept_Appnd a inner join dbo.v_temp_fisc b on a.amc_year = b.fisc_yr and a.amc_period = b.fisc_pd and a.amc_week = b.fisc_wk

where b.cal_date between '2/4/2007' and '8/4/2007'
group by loc
order by loc

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-12 : 11:00:27

[code]SELECT Loc,
SUM(Qty) AS Qty,
SUM(Amt_Paid) AS Amt_Paid,
SUM(Mkdn_Amt) AS Mkdn_Amt,
SUM(Early_Units) AS Early_Units,
SUM(case qty
when 0 then 0.0
else 100.0 * Early_Units / Qty
end) as Early_Per,
SUM(Early_Dlrs) AS Early_Dlrs,
SUM(Barcode_Units) AS Barcode_Units,
SUM(case qty
when 0 then 0.0
else 100.0 * Barcode_units / Qty
end) as Barcode_per,
SUM(Barcode_Dlrs) AS Barcode_Dlrs,
SUM(Manual_Units) AS Manual_Units,
SUM(case qty
when 0 then 0.0
else 100.0 * Manual_Units / Qty
end as Manual_Per,
SUM(Manual_Dlrs) AS Manual_Dlrs,
SUM(Manual_Units - Early_Units) as O_Man_Units,
SUM(Manual_Dlrs - Early_Dlrs) as O_Man_Dlrs,
SUM(case qty
when 0 then 0.0
else 100.0 * (Manual_Units - Early_Units) / Qty
end) as O_Man_Per
FROM dbo.TBL_Dept_Appnd AS a
LEFT JOIN dbo.v_temp_fisc AS b ON b.fisc_yr = a.amc_year
and b.fisc_pd = a.amc_period
and b.fisc_wk = a.amc_week
and b.cal_date >= '20070402'
and b.cal_date < '20070409'
group by a.loc
order by a.loc[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources