on 05-09-2014 1:22 PM
select t0.cardcode, t1.itemcode, t1.[year], t1.[month], sum(t1.nettamnt)[Sales], sum(t1.nettgp)[GP] from ocrd t0 inner join [DEV].[dbo].[2yearbycarditem] t1 on t0.cardcode = t1.cardcode
WHERE t1.ItemCode like '12V%'
group by t0.cardcode, t1.itemcode, t1.[year], t1.[month]
order by t0.cardcode, t1.[year],t1.[month]
how do i change this so the itemcode is a parameter. They need to enter an itemcode that starts with e.g. 12V
I have tried
WHERE t1.ItemCode like '%' + @itemcode + '%'
this does not work.
please help
thanks in advance- Janice
Hi Janice Middleton....
Try This, If you want to pull all items the use % in the selection
/* select * from oitm x1 */ DECLARE @d1 AS nvarchar(100)
SET @d1=/* x1.ItemCode */ '[%0]'
SELECT t0.cardcode,
t1.itemcode,
t1.[year], t1.[month], sum(t1.nettamnt)[Sales], sum(t1.nettgp)[GP]
FROM ocrd t0
INNER JOIN [DBO].[2yearbycarditem] t1 ON t0.cardcode = t1.cardcode
INNER JOIN oitm oi ON oi.ItemCode=t1.itemcode
WHERE oi.ItemCode LIKE @d1
GROUP BY t0.cardcode,
t1.itemcode,
t1.[year], t1.[month]
ORDER BY t0.cardcode,
t1.[year],t1.[month]
Regards
Kennedy
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
Try this:
select t0.cardcode, t1.itemcode, t1.[year], t1.[month], sum(t1.nettamnt)[Sales], sum(t1.nettgp)[GP]
from ocrd t0 inner join [dbo].[@2yearbycarditem] t1 on t0.cardcode = t1.cardcode
WHERE t1.ItemCode =[%0]
Thanks & Regards,
Nagarajan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Janice Middleton,
Use following query, i included OITM table and removed dev db reference.
/* select * from oitm x1 */
declare @d1 as nvarchar(100)
set @d1=/* x1.ItemCode */ '[%0]'
select t0.cardcode, t1.itemcode, t1.[year], t1.[month], sum(t1.nettamnt)[Sales], sum(t1.nettgp)[GP]
from ocrd t0
inner JOIN [2yearbycarditem] t1 on t0.cardcode = t1.cardcode
INNER JOIN oitm oi ON oi.ItemCode=t1.itemcode
WHERE oi.ItemCode like @d1
group by t0.cardcode, t1.itemcode, t1.[year], t1.[month]
order by t0.cardcode, t1.[year],t1.[month]
With regards,
Venki .A
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
85 | |
7 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 | |
3 | |
2 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.