SQL Between first and last of the month 4

[THOR01]

I have a sql script that was written for sqlserver and was wanting some help in converting some of the date functions to oracle.

DECLARE @FirstDayOfMonth, @FirstDayOfLastMonth

SET @FirstDayOfMonth = convert(datetime, convert(char(7), getdate(), 120) + '-01')

SET @FirstDayOfLastMonth = dateadd(mm, -1, convert(datetime, convert(char(7), getdate(), 120) + '-01'))

SET @LastMonth = dateadd(mm,-1,getdate())
SET lines 80 pages 1000
SET PERIOD_ENDING = @LastMonth
break on CRD_CLNT_ID on PERIOD_ENDING skip 2
break on CRD_CLNT_ID skip 2
COMPUTE COUNT OF CRD_CLNT_ID ON CRD_CLNT_ID

--SELECT "CRD_CLNT_ID", "PERIOD_ENDING", "ACCT_NBR", "CAS_CRE_DT", "CAS_CLS_DT"
FROM "table007"
WHERE ("CAS_CLS_DT" >= @FirstDayOfLastMonth);
AND ("CAS_CLS_DT" <= @FirstDayOfMonth))
group by "CRD_CLNT_ID"
ORDER BY count(*), "CRD_CLNT_ID";

When I try and run it doesn't If I stip out the functions @xxxxx and query between 1 march and 31 march it works ?

Thanks!

 

[LKBrwnDBA]

Maybe you would be better off if you posted this in the MS SQL Server forum -- This is Oracle forum and many of us don't do WinDoze.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Unfortunately, the MS SQL Server people may be just as oblivious about Oracle functions/conventions as we Oracle-ites are of SQL Server.

So, I'm willing, Thor, to take a stab at helping you with the conversions. To help me help you, I need you to give, for each SQL Server statement and its component functions/behaviours, a clear, non-technical functional narrative of what that component does. (Gear it for an 8-year-old, and we should be fine. <grin>)

Also helpful would be a "CREATE TABLE <table_name>..." and some accompanying "INSERT INTO <table_name>..." statements to facilitate our creating a proof-of-concept of our code that resolves your need.

Looking forward to your post, Thor.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[THOR01]

I thought that sql was kinda, sorta sql BUTTTT anyway I found some coede that Ireworked and it seems to be doing the trick.
I really appreciate y'alls support. Comments?

spool sql_3.out;


SET lines 80 pages 1000
break on CRD_CLNT_ID skip 2
COMPUTE COUNT OF CRD_CLNT_ID ON CRD_CLNT_ID


SELECT "CRD_CLNT_ID", "ACCT_NBR", "CAS_CRE_DT", "CAS_CLS_DT"
FROM "FALT007"
WHERE "CAS_CLS_DT"
between trunc(
trunc(sysdate,'MM') -1 ,'MM') and trunc(sysdate,'MM') -(1/24/60/60)
GROUP BY "CRD_CLNT_ID", "ACCT_NBR", "CAS_CRE_DT", "CAS_CLS_DT"
ORDER BY count(*), "CRD_CLNT_ID";

spool off;
exit 0


Now I will admitt that I will have to do some reading to see exactly what all is going on in the "WHERE" clause !!

THANKS AGAIN

 

[LKBrwnDBA]

Ooops, missed the "convert to Oracle" part...sorry, it's been a long day.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[SantaMufasa]

Yes, Thor, the above code is syntactically correct for use in Oracle (specifically from Oracle's SQL*Plus application).

I shall observe that generally, we consider use of double quotes (") to surround Oracle object names to be poor form. The only time that double quotes are necessary to name an object is when the creating user created the object (i.e., table, column, et cetera) and wanted the object's name to include either (normally illegal) special characters or to become case sensitive.

Oracle professionals consider both of the above situations to be extremely poor form. Use of quotes for object names that conform to Oracle's default character set for names (i.e., Alpha, numeric, "_", "$", with a leading alpha character) are extraneous, therefore a waste of typing time, therefore looked down upon.

Therefore, I suggest to get in the habit of not using double quotes to surround Oracle object names unless required by the poor-form naming of an object.

Now, for an explanation of your WHERE clause:

...WHERE "CAS_CLS_DT"
        between trunc(
                     trunc(sysdate,'MM') -1 ,'MM') and trunc(sysdate,'MM') -(1/24/60/60)...

I'll explain the WHERE clause in Oracle's order of execution:[ul][li]trunc(sysdate,'MM') - Obtain the current date and time, then truncate the result to midnight on the morning of the first day of the month. (Using today, Friday, May 2, 2008, the result of this function is momentarily Thursday, May 1, 2008 [at midnight that morning][/li][li]-1 - Back up one full day from May 1, resulting in April 30 [at midnight that morning][/li][li]trunc(...<previous result>...,'MM') - Again, truncate the current focus date (April 30) to the first day of that month, resulting in April 1, 2008 [at midnight that morning].[/li][li]trunc(sysdate,'MM') - As before, produce May 1, 2008.[/li][li]-(1/24/60/60) - Start within the parentheses and divide "1" [Day] by "24" [hours], then divide by "60" [minutes], then divide by "60" [seconds], yielding "1/86400th" of a Day, which equals "1 second". Then subtract that one second from May 1, 2008 [at midnight that morning], resulting in April 30, 2008 [at 11:59:59].[/li][li]WHERE "CAS_CLS_DT" between... - Check to see if the current value of CAS_CLS_DT is between "April 1, 2008 at 00:00:00" and "April 30, 2008 at 11:59:59". If that is true, then make that row part of the result set for continued processing.[/li][/ul]Let us know if this is helpful or if you have any additional questions.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[THOR01]

Thanks to both of you LKBrwnDBA & SANTA.
Also thanks for the quote lesson and I really appreciate the explanation.

Good Day from Atlanta

 

[Thargy]

Thor,

on your behalf, I've awarded stars to your helpers.

Regards

T