Jump to content

SQL Server Experts help kavali oka doubt


sandeep169

Recommended Posts

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)))

Link to comment
Share on other sites

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)))

Link to comment
Share on other sites

(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)))

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...