Below code works fine in SQL but how do i use the same to create a derived table in BO Universe.

[Former Member]

DECLARE @StartDate datetime='2017-11-01'

DECLARE @EndDate datetime=SYSDATETIME()

;WITHdaysAS

(

SELECTDATEADD(DAY, n,DATEADD(DAY,DATEDIFF(DAY, 0, @StartDate), 0))as d

FROM (SELECTTOP (DATEDIFF(DAY, @StartDate, @EndDate)+ 1)

n =ROW_NUMBER()OVER (ORDERBY [object_id])- 1

FROMsys.all_objectsORDERBY [object_id] )AS n

)

selectdays.d,count(t.EXPECTEDDATE)

FROMdaysLEFTOUTERJOIN DEPOSIT as t

ON t.EXPECTEDDATE >=days.d AND t.EXPECTEDDATE <DATEADD(DAY, 1,days.d)

GROUPBYdays.d

ORDERBYdays.d;

[denis_konovalov]

I have fixed your tags.

[mhmohammed]

Hi Jaya,

You can't use Declare or With, in a Derived table in Universe. Try to define n and days as tables in the inner queries, something like this.

select days.d, count(t.EXPECTEDDATE)
FROM

--next 12 lines (include table n) is the SQL for table days
(
SELECT DATEADD (DAY, n.rn, DATEADD(DAY,DATEDIFF(DAY, 0, CONVERT(datetime, '2017-11-01')), 0)) as 'd'
FROM

--next 5 lines (Italicized) is the SQL for table n

(
SELECT TOP (DATEDIFF(DAY, CONVERT(datetime, '2017-11-01'), Sysdatetime())+ 1), ROW_NUMBER() OVER (ORDERBY [object_id]) - 1 as 'rn'
FROM sys.all_objects ORDER BY [object_id]
) n

) days LEFT OUTER JOIN DEPOSIT t

ON (t.EXPECTEDDATE >= days.d AND t.EXPECTEDDATE < DATEADD(DAY, 1,days.d)) --(in this line we're joining tables days and t)

GROUP BY days.d
ORDER BY days.d;

Try that and let us know if that works.

Thanks,
Mahboob Mohammed