• 
  Round
• 
  Princess
• 
  Heart
• 
  Cushion
• 
  Emerald
• 
  Oval
• 
  Pear
• 
  Marquise
• 
  Asscher
• 
  Radiant

select j.itemid, j.itemid_cmid , j.cmid , j.itemno , j.StoneID_of_Min , j.min_price , j.max_price , j.ddate , j.Metal_Desc_Web , j.Tot_Diamond , j.ShapeID , jis.Img1, jis.Img2 , img.img1 , img.img2 , img.img3 , img.img4 , img.img5 , jimage_sizes_id = img.id ,title_2 ,short_title ,occassion ,ld_shape ,discount ,min_price_org , ddateto_ , orgimg1 = 'https://www.javda.com/jimage/' + jis.ebayimg1 , orgimg2 = 'https://www.javda.com/jimage/' + jis.ebayimg2 , orgimg3 = 'https://www.javda.com/jimage/' + jis.ebayimg3 , orgimg4 = 'https://www.javda.com/jimage/' + jis.ebayimg4 , video_link = jis.video_link , max_price_org from ( select itemid , itemid_cmid , cmid , itemno , StoneID_of_Min , min_price = (Price + LD_Min_Price) - (discount) , max_price = (Price + LD_Max_Price) - (discount) , ddate , metal_desc_web , vpr , groups , Tot_Diamond , ROW_NUMBER() over ( order by groups desc , j.itemid desc ) as rowid ,StoneTypeID , ShapeID , Carat , GemColorId , ImgColorID ,title_2 ,short_title ,occassion ,ld_shape ,discount ,min_price_org = (Price + LD_Min_Price) , ddateto_ = to_ ,max_price_org = (Price + LD_Max_Price) from ( select j.itemid , itemid_cmid = case when isnull(jm.cmid,0) > 0 then (convert(varchar(18),j.itemid) + '-' + convert(varchar(18),isnull(jm.cmid,0))) else convert(varchar(18),j.itemid) end , cmid = isnull(jm.cmid,0) , j.itemno , price = (case when ac.subcat_id is null then (isnull(j.price,0) + isnull(jm.price,0)) else isnull((((( ms.marketprice / ms.gram ) * k.kprice ) + (isnull(j.price,0) + isnull(jm.price,0))) * isnull(jm.weight,0)),0) end), discount = (case when isnull(d.amtwise,0) = 0 then ((isnull(d.perwise,0)/100) * (case when ac.subcat_id is null then (isnull(j.price,0) + isnull(jm.price,0)) else isnull((((( ms.marketprice / ms.gram ) * k.kprice ) + (isnull(j.price,0) + isnull(jm.price,0))) * isnull(jm.weight,0)),0) end)) else isnull(d.amtwise,0) end) , ddate = cast(day(d.to_) as char(2)) + ' ' + left(datename(month,d.to_),3) +' '+cast(right(year(d.to_),2) as char(4)) , m.metal_desc_web , vpr = isnull(jc1.vpr,24) , dcarat = dc.dcarat , url_keywords = mp.url_keywords , gp.groups , cd.StoneID_of_Min , cd.LD_Min_Price , cd.LD_Max_Price , cd.Tot_Diamond , ld.StoneTypeID , ld.ShapeID , ld.Carat , ld.GemColorId , m.ImgColorID ,j.title_2 ,short_title ,occassion ,ld_shape = sh.shape ,d.to_ from jewelry j WITH(INDEX(J_idx_PreBuild_itemid_price_vendorid)) inner join ( select * , ROW_NUMBER() over (PARTITION BY jm.itemid , jm.metalid order by jm.itemid , jm.metalid , jm.[weight]) as [row] from ( select cmid = isnull(jm.cmid,0) , jm.itemid , jm.jmid , jm.metalid , jm.weight , price = isnull(jm.price,0) , jm.sizesid from jmView jm where jm.MetalTypeID = 1union all select cmid = cm.[cmid] , itemid , jmid , cm.metalid , weight , price = isnull(price,0) , sizesid from jm_cmView as cm where cm.MetalTypeID = 1) as jm ) as jm on jm.itemid = j.itemid inner join metal m on m.metalid = jm.metalid and m.MetalTypeID = 1inner join ( select * from ( select cd.* , ROW_NUMBER() over (PARTITION BY cd.Setting_Itemid order by cd.LD_Min_Price asc, cd.StoneID_of_Min asc) as rn from cd cd where 1=1 and shapeid = 1 ) x where x.rn = 1 ) as cd on cd.Setting_Itemid = j.ItemID inner join LooseDiamond LD on ld.ItemiD = cd.StoneID_of_Min inner join (select jcid , itemid from productlisting group by jcid , itemid ) as pl on j.itemid = pl.itemid inner join jcategory1 jc on jc.jcid = pl.jcid inner join jcategory1 jc1 on jc1.jcid = 1073 and jc1.active = 1 and isnull(jc1.masterid,isnull(jc.masterid,0)) = isnull(jc.masterid,0) and isnull(jc1.submasterid,isnull(jc.submasterid,0))= isnull(jc.submasterid,0) and isnull(jc1.cid,isnull(jc.cid,0)) = isnull(jc.cid,0) and isnull(jc1.scid,isnull(jc.scid,0)) = isnull(jc.scid,0) and isnull(jc1.sscid,isnull(jc.sscid,0)) = isnull(jc.sscid,0) and isnull(jc1.ssscid,isnull(jc.ssscid,0)) = isnull(jc.ssscid,0) left join Shape sh on sh.ShapeID = ld.ShapeID left join ( select itemid , dcarat = sum(js.carat) from jewelstone js left join stonetype st on st.stonetypeid = js.stonetypeid left join stone s on s.stoneid = st.stoneid where s.stoneid = 1 group by itemid , s.stoneid ) as dc on dc.itemid = j.itemid left join autoprice_cat ac on ac.subcat_id = j.subcat_id left join metals ms on ms.metalsid = m.metalsid and ms.[auto] = 1 left join karat k on k.karatid = m.karatid left join discount d on j.cat_id = d.cat_id and j.subcat_id = d.subcat_id and (case when ac.subcat_id is null then isnull((isnull(j.price,0) + isnull(jm.price,0)),0) else isnull((((( ms.marketprice / ms.gram ) * k.kprice ) + (isnull(j.price,0)+ isnull(jm.price,0))) * isnull(jm.weight,0)),0) end) between d.amtfrm and d.amtto_ and getdate() between d.frm and d.to_ and upper(d.active) = 'Y' and upper(j.discounted) = 'Y' inner join ( select pw.pwid , pw.frm , pw.to_ from pricewise_prebuild pwj left join pricewise pw on pw.pwid = pwj.pwid where pwj.jcid = 1073 and pw.pwid = 30 ) as pw on (( isnull(j.price,0) + isnull(jm.price,0) ) + cd.LD_Min_Price) - (case when isnull(d.amtwise,0) = 0 then ((isnull(d.perwise,0)/100) * (case when ac.subcat_id is null then (isnull(j.price,0) + isnull(jm.price,0)) end)) else isnull(d.amtwise,0) end) between pw.frm and pw.to_ left join mapfile mp on mp.mapid = case when isnull(jm.cmid,0) > 0 then 'tid=' + convert(varchar(18),j.itemid) + '&cmid=' + convert(varchar(18),jm.cmid) else 'tid=' + convert(varchar(18),j.itemid) end left join (select itemid , groups = count(itemid)from [grouping] group by itemid ) as gp on gp.itemid = j.itemid where j.active = 'Y' and isnull(j.websiteid,'1') = 1 ) as j ) as j left join (select jimages.* , carat.carat_from , carat.carat_to from jimages left join carat on jimages.caratid = carat.caratid) as jis on jis.ItemID = j.itemid and jis.shapeid = j.shapeid and jis.stonetypeid = j.stonetypeid and j.carat between isnull(jis.carat_from,j.carat) and isnull(jis.carat_to,j.carat) and isnull(j.gemcolorid,0) = isnull(jis.gemcolorid,0) and jis.imgcolorid = j.imgcolorid left join jimages_sizes img on img.JimagesID = jis.JimagesID and img.width = 800 where rowid between (((1 * j.vpr) - j.vpr) + 1) and (1 * j.vpr)