Join problems - 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 2005 Forums

Transact-SQL (2005)

Join problems

Author

Topic

larryleisure
Starting Member

5 Posts

Posted - 2011-01-27 : 16:49:19

Hi Everyone, I have a pretty standard question I guess, but I can't find a way to get the data displayed the way I want.

I have 3 tables (dates, sales, targets), they are like this:
1) dates:
|date|year|quarter_of_year|month_of_year|week_of_year|

2) sales:
|product_id|date|quantity|

3) targets:
|product_id|year|quarter|target_quantity|

Targets are defined for every product, every year & quarter, for example:
|ProductID|year|quarter|target|
|Product 1|2008| Q1 | 300 |
|Product 1|2008| Q2 | 400 |
|Product 1|2008| Q3 | 450 |
|Product 1|2008| Q4 | 420 |
|Product 2|2008| Q1 | 120 |
etc....

But the actual sales are on a daily basis, for example:
|ProductID| date |quantity|
|Product 1|15-04-2008| 230 |
|Product 1|16-03-2008| 125 |
|Product 1|14-08-2009| 352 |

I would like to see every product target per year&quarter vs the actual quarterly sales.
For example:
|ProductID|year|Quarter|target|actual_qty|
|Product 1|2008| Q1 | 300 | 455 |
|Product 1|2008| Q2 | 400 | 352 |

Every product has a target for every year and every quarter.
In case a product has not been sold at all during a specific quarter, the target row should be visible with the actual quantity = null

Any idea?
I've tried tons of things but I never get the result I want :/

Thanks for any help!

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-27 : 17:31:33

What have you tried? we can likely point you in the right direction if you post your query

Poor planning on your part does not constitute an emergency on my part.

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-28 : 00:06:39

The column in your required output "Actual_qty" is the part of which table? Sales.Quantity or Targets.target_quantity?

sathishmangunuri
Starting Member

32 Posts

Posted - 2011-01-28 : 00:59:07

As per my understand

create table #dates(ddate date,syear int,quarter_of_year varchar(5),month_of_year int,week_of_year int)
insert into #dates
select '2008-01-01',2008,'Q4',1,1 UNION ALL
select '2008-01-02',2008,'Q4',1,1 UNION ALL
select '2008-01-03',2008,'Q4',1,1 UNION ALL
select '2008-01-04',2008,'Q4',1,1 UNION ALL
select '2008-01-05',2008,'Q4',1,1

create table #sales(product_id varchar(10),sdate date,quantity int)
insert into #sales
select 'p1','2008-01-01',10 union all
select 'p2','2008-01-01',null union all
select 'p1','2008-01-02',5 union all
select 'p2','2008-01-02',5 union all
select 'p1','2008-01-03',25 union all
select 'p2','2008-01-03',50 union all
select 'p1','2008-01-04',100 union all
select 'p2','2008-01-04',50 union all
select 'p1','2008-01-05',null union all
select 'p2','2008-01-05',75

create table #targets(product_id varchar(10),tyear int ,tquarter varchar(5),target_quantity int)
insert into #targets
select 'p1','2008','Q4',450 union all
select 'p2','2008','Q4',250 union all
select 'p3','2008','Q4',450

select t.product_id,tyear [year],tquarter [quarter],target_quantity [target],sales.actual_qty
from #targets t left join
(select product_id ,sum(quantity) actual_qty,quarter_of_year ,syear from #sales s
join #dates d on s.sdate=d.ddate
group by syear,quarter_of_year,product_id) as sales
on t.product_id=sales.product_id and t.tyear=sales.syear and t.tquarter=sales.quarter_of_year
order by tyear,tquarter ,t.product_id

sathish

sathish

larryleisure
Starting Member

5 Posts

Posted - 2011-01-28 : 06:32:49

@Mik_2008, the column actual_qty comes from sales. It's a sum of sales.quantity on year/quarter level.

@Sathish,
my date and target tables are like you described.
The sales table has the same columns as the one you described, but the difference is that when a product has not been sold in some year/quarter, then there is no row at all. Unlike your example where there would be a row with NULL as the quantity.
See, that row would simply not be in the sales table:
'p2','2008-01-01',null
Don't know if that alters the result though, I'll try within an hour as I have to leave now.

@Dataguru, I've tried so many different queries that I don't really know which one is best to start with anymore, they all give me wrong results anyway.

Thanks all in any case for your help so far, really appreciated!

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-28 : 06:47:21

Check this out!!!

Select
T.product_id,T.year,T.quarter,isnull(Sum(S.Quantity),0)
From targets T
Left Join sales S on T.Product_ID=S.Product_ID
And T.[Year]=YEAR(S.[Date])
And T.Quarter = (Case
When Month(S.[date]) between 1 and 3 then 'Q1'
When Month(S.[date]) between 4 and 6 then 'Q2'
When Month(S.[date]) between 7 and 9 then 'Q3'
When Month(S.[date]) between 10 and 12 then 'Q4'
end)
Group by T.product_id,T.year,T.quarter

larryleisure
Starting Member

5 Posts

Posted - 2011-02-07 : 05:43:50

actually this happened to be the solution (in case someone needs something similar)

SELECT t.product_id
, t.year
, t.quarter
, t.target_quantity
, SUM(sales.sales_qty) AS actual_qty
FROM target_dimension AS t
INNER
JOIN date_dimension AS dates
ON dates.year = t.year
AND dates.quarter_of_year = t.quarter
LEFT OUTER
JOIN fact_sales AS sales
ON sales.date_id = dates.date_id
AND sales.product_id = t.product_id
GROUP
BY t.product_id
, t.year
, t.quarter
, t.target_quantity

Thanks all for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources