March 29, 2005 at 11:55 am
Hello i have this query
can someone help me to transform this query in dynamic sql ? as the clause WHERE IN do not handle more than 1 parameters
select pro_name, month(dis_dat) as month_pp ,sum(distrib_first) as tot_draw_first
from
(SELECT x.iss_no, x.pro_name,x.distrib, x.dis_dat, sum(ret_qty) AS unsold,
CASE WHEN DIS_DAT BETWEEN @periodfrom AND @periodto THEN distrib end as DISTRIB_FIRST
FROM
(SELECT iss_no, pro_name, dis_dat, distribution.pro_cod, sum(dis_qty) AS distrib
FROM distribution
INNER JOIN publisher on product.pub_cod = publisher.pub_cod
WHERE
(product.pro_cod IN (@product) and DIS_DAT BETWEEN @periodfrom AND @periodto)
GROUP BY distribution.iss_no, pro_name, dis_dat, distribution.pro_cod )
AS x(iss_no, pro_name, dis_dat, pro_cod, distrib)
JOIN returns ON returns.pro_cod = x.pro_cod AND returns.iss_no = x.iss_no
GROUP BY x.iss_no, x.pro_name, x.dis_dat, x.distrib
) AS sales_comp
group by pro_name , month(dis_dat) order by pro_name
GO
thanks
March 29, 2005 at 12:37 pm
This is what you need :
March 30, 2005 at 12:44 am
And while you're at Erland's site, read also this http://www.sommarskog.se/arrays-in-sql.html
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
March 30, 2005 at 1:56 am
thanks a lot
useful link
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply