Query Help (SubQuery?)
-
See area in blue text below. Trying to return the max fiscal week from depot_buckets. This table contains a row for each week of the year for the past 3 years. (fiscal_wk, fiscal_yr, reporting_year, date_start). This works if I use the date column, problem is that the date_start is the Monday of every week therefore a different date each year.
Here is the error message:
No column was specified for column 1 of 't1'.
DECLARE @MAXWEEK datetime
SET @MAXWEEK =
(select
fiscal_wk from depot_buckets b inner join
(select
max(date_start)
from
depot_pos_detail
 
t1on b.date_start = t1.date_start)
select 'POS' [Account],
@maxweek [Week],
prim_prod_group,
sec_prod_group,
tert_prod_group,
sum(case when year(p.date_start) = year(getdate())-1 and p.date_start = @maxweek then qty_sold else 0 end) [LYWkQty],
sum(case when year(p.date_start) = year(getdate()) and p.date_start = @maxweek then qty_sold else 0 end) [TYWkQty],
sum(case when year(p.date_start) = year(getdate())-1 and p.date_start = @maxweek then currency_sold else 0 end) [LYWkPOS],
sum(case when year(p.date_start) = year(getdate()) and p.date_start = @maxweek then currency_sold else 0 end) [TYWkPOS]
FROM
depot_pos_item_sum p
inner join depot_items i on i.idx_item = p.idx_item
inner join depot_buckets b on b.date_start = p.date_start
group by
prim_prod_group,
sec_prod_group,
tert_prod_group
-
>>No column was specified for column 1 of 't1'.
t1 is a derived table. All columns in a derived table must be explicitly named. The expression max(date_start) has not been assigned a column name in the resultset. Change it to this, and reference the column as t1.MaxDateStart:
(select
max(date_start) As MaxDateStart --(Or whatever you want to name it)
-
You guys rock! Thanks for the help!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply