Creating derived table -parsing failed

[Former Member]

Hi All,

When i am trying create a Derived table from Universe designer , parsing is not working, but the same code working fine in SQL Server 2008 editor.

Kindly help me out on this how to create Derived table based on the following query.

declare @sdate datetime

set @sdate = dateadd(d,-46,getdate())

SELECT x.droprate as med1

FROM

(select upper(DimSiteMarket99.market_name) as market,IDENSLABBHDLY.FullDate as date_value,

(sum(IDENSLABBHDLY.Dy_LOT_DROPS_N][IDENSLABBHDLY.Dy_OB_HO_DROPS][IDENSLABBHDLY.Dy_NonRF_DROP]))/(sum([IDENSLABBHDLY.Dy_Calls]),0)*100 as droprate

from IDENSLABBHDLY sla

inner join DimSiteMarket99 mkt

on sla.bts_name = mkt.bts_name and sla.SectorID = mkt.Sector_Id

where IDENSLABBHDLY.FullDate >= @sdate

group by DimSiteMarket99.market_name,IDENSLABBHDLY.fulldate) x,

(select DimSiteMarket99.market_name as market, IDENSLABBHDLY.FullDate as date_value,

(sum([IDENSLABBHDLY.Dy_LOT_DROPS_N][IDENSLABBHDLY.Dy_OB_HO_DROPS][IDENSLABBHDLY.Dy_NonRF_DROP]))/(sum([IDENSLABBHDLY.Dy_Calls]),0)*100 as droprate

from IDENSLABBHDLY sla

inner join DimSiteMarket99 mkt

on sla.bts_name = mkt.bts_name and sla.SectorID = mkt.Sector_Id

where IDENSLABBHDLY.FullDate >= @sdate

group by DimSiteMarket99.market_name, IDENSLABBHDLY.fulldate) y

where x.market = y.market

GROUP BY x.droprate, x.market

HAVING

SUM(CASE WHEN y.droprate <= x.droprate

THEN 1 ELSE 0 END)>=(COUNT(*)+1)/2 AND

SUM(CASE WHEN y.droprate >= x.droprate

THEN 1 ELSE 0 END)>=(COUNT(*)/2)+1

Thanks

[Former Member · Accepted solution]

I believe, we cannot use declare and set command. Please try without those commands.