Hi All,
I need help to write a HQL query for the following SQL query. The query should work in all databases. At least in MySQL and SQL Server. Please help me out.
sql = select convert(varchar(11) ,billing_date,120) as billingDate, isnull(f.mul,1) mul , (isnull((select top 1 price from table1 bp1 where bp1.id=bp.id and billing_date>=dateadd(dd,1,dateadd(mm,-12,convert(varchar(11),bp.billing_date,120))) and billing_date<dateadd(dd,2,dateadd(mm,-12,convert(varchar(11),bp.billing_date,120))) order by billing_date),0)- isnull((select top 1 price from table1 bp1 where bp1.id=bp.id and billing_date>=dateadd(mm,-12,convert(varchar(11),bp.billing_date,120)) and billing_date<dateadd(dd,1,dateadd(mm,-12,convert(varchar(11),bp.billing_date,120))) order by billing_date),0)) as previous, (isnull((select top 1 price from table1 bp1 where bp1.id=bp.id and billing_date>=dateadd(dd,1,convert(varchar(11),bp.billing_date,120)) and billing_date<dateadd(dd,2,convert(varchar(11),bp.billing_date,120)) order by billing_date),0)- isnull((select top 1 price from table1 bp1 where bp1.id=bp.id and billing_date>=convert(varchar(11),bp.billing_date,120) and billing_date<dateadd(dd,1,convert(varchar(11),bp.billing_date,120)) order by billing_date),0)) as selMonth from table1 bp left outer join table2 f on f.id = bp.id left outer join table3 m on m.id = bp.id left outer join table4 c on c.id = m.tid Thanks in Advance
|