SQL Server Experts help kavali oka doubt

[sandeep169]

E below Oracle query ni SQL server ki compatible ga ela marchali. I'm confused with Level and Connect by Level, endukante sql server doesn't have this concept. Kani accommodate cheyali ante ela cheyali..help chesi punyam katukondi brothers

 

(SELECT DISTINCT last_day(mydate) AS eom_date FROM ( select TRUNC (ADD_MONTHS (SYSDATE, -12), 'yy') - 1 + LEVEL AS mydate FROM dual

CONNECT BY LEVEL <= (SELECT TRUNC (ADD_MONTHS (SYSDATE, ), 'yy')- TRUNC (ADD_MONTHS (SYSDATE, -12), 'yy') FROM DUAL)))

[Katravelli]

SELECT DISTINCT last_day(mydate) AS eom_date FROM ( select TRUNC (DATEADD (MONTH, -12, GETDATE()), 'yy') - 1 + LEVEL AS mydate BY LEVEL <= (SELECT CONVERT(FLOAT, TRUNC (DATEADD (MONTH, ), GETDATE(), 'yy'))- CONVERT(FLOAT, TRUNC (DATEADD (MONTH, -12, GETDATE()), 'yy')) FROM DUAL)))

[mainst]

(SELECT DISTINCT last_day(mydate) AS eom_date FROM ( select TRUNC (DATEADD (MONTH, -12, GETDATE()), 'yy') - 1 + LEVEL AS mydate BY LEVEL <= (SELECT CONVERT(FLOAT, TRUNC (DATEADD (MONTH, ), GETDATE(), 'yy'))- CONVERT(FLOAT, TRUNC (DATEADD (MONTH, -12, GETDATE()), 'yy')) FROM DUAL)))

[sandeep169]

Thank you bhayya ...kani SQL Server lo Level undadu and TRUNC kuda predefined functions kaavu..akkade I'm struck to check any alternative for this ani 

[riashli]

[loveindia]

Man, as an FYI,

The equivalent of Level in PL/SQL  is Recursion. You can do that using a CTE. Let me see this better and write back in an another post man..

[loveindia]

SELECT DATEADD(yy, DATEDIFF(yy,0, DATEADD(MONTH, -12, GETDATE())), 0) - 1

this is something you can use for the inner part of the query that uses TRUNC and ADD_MONTHS. It is actually subracting 12 months and getting the first day of that resulted year and subracting 1 day at the end and a level is added at the end. It is like an auto incrementing number 1,2,3,....

How you will re-write that totally depends on what you want and the other tables in sql server man... Hope that helps...