Login or Register to Ask a Question and Join Our Community

Sponsored Content
Full Discussion: Derivation of values falling on date ranges
Top Forums Programming Derivation of values falling on date ranges Post 302966435 by durden_tyler on Thursday 11th of February 2016 11:23:26 PM
Old 02-12-2016
Code:
SQL> 
SQL> select * from bal;

	ID    BALANCE BAL_DATE
---------- ---------- ---------
	 1     -11.71 01-JAN-05
	 1    -405.71 02-JAN-05
	 1    -760.71 03-JAN-05

3 rows selected.

SQL> 
SQL> select * from reference_table;

   PRODUCT EFF_FROM_DATE   EFF_TO_DATE	   TYPE     MIN_AMT    MAX_AMT	   CHARGE
---------- --------------- --------------- ----- ---------- ---------- ----------
	12 01-JAN-05	   01-JAN-06	   T1		  0	    15		0
	12 01-JAN-05	   01-JAN-06	   T2		 16	  1000	      .75
	12 01-JAN-05	   01-JAN-06	   T3	       1001	  2000	      1.5
	12 01-JAN-05	   01-JAN-06	   T4	       2001	  5000		3

4 rows selected.

SQL> 
SQL> select * from periods;

START_DAT END_DATE	    ID
--------- --------- ----------
02-JAN-05 13-OCT-05	     1

1 row selected.

SQL> 
SQL> --
with bal_dtl as (
    select b.balance, b.bal_date,
           case when coalesce(to_char(p.id), to_char(p.start_date), to_char(p.end_date)) is null then 'N'
                else 'Y'
           end as adj_reqd
      from bal b
           left outer join periods p
           on (b.id = p.id and b.bal_date between p.start_date and p.end_date)
),
ref_bal as (
    select r.type, bd.balance, bd.bal_date, r.charge, r.min_amt, r.max_amt,
           case when bd.adj_reqd = 'Y' then 500*(row_number() over (partition by bd.bal_date order by r.type)-1) end as min_amt_adj,
           case when bd.adj_reqd = 'Y' then 500*row_number() over (partition by bd.bal_date order by r.type) end as max_amt_adj
      from reference_table r, bal_dtl bd
     where bd.bal_date between r.eff_from_date and r.eff_to_date
)
select nvl(t1_val,0) as t1_val, nvl(t2_val,0) as t2_val, nvl(t3_val,0) as t3_val, nvl(t4_val,0) as t4_val,
       bal_date,
       nvl(t1,0) as t1, nvl(t2,0) as t2, nvl(t3,0) as t3, nvl(t4,0) as t4
  from (
      select rb.type, rb.balance, rb.bal_date, rb.charge
        from ref_bal rb
       where abs(round(rb.balance)) between nvl(min_amt_adj,min_amt) and nvl(max_amt_adj,max_amt)
  )
  pivot (
      max(balance) as val, max(charge)
      for type in ('T1' as t1, 'T2' as t2, 'T3' as t3, 'T4' as t4)
  )
;

    T1_VAL     T2_VAL	  T3_VAL     T4_VAL BAL_DATE	      T1	 T2	    T3	       T4
---------- ---------- ---------- ---------- --------- ---------- ---------- ---------- ----------
    -11.71	    0	       0	  0 01-JAN-05	       0	  0	     0		0
   -405.71	    0	       0	  0 02-JAN-05	       0	  0	     0		0
	 0    -760.71	       0	  0 03-JAN-05	       0	.75	     0		0

3 rows selected.

SQL> 
SQL>

 

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk to print mon and max values of ranges

HI all I'm trying to write an awk script to print the min and max value in a range(s) contained in another file - the range values are in $2 EG 114,7964,1,y,y,n 114,7965,1,y,y,n 114,7966,1,y,y,n 114,7967,1,y,y,n 114,7969,1,y,y,n 114,7970,1,y,y,n 114,7971,1,y,y,n 114,7972,1,y,y,n... (3 Replies)
Discussion started by: Mudshark
3 Replies

2. Shell Programming and Scripting

find command: various date ranges

Hi, I have writtena script that will recursivly go into subdirecotries and report out what files there are in there that have not been accessed over various date ranges. I do this using a number of find commands: find . -path './.snapshot' -prune -o -type f -atime -8 find... (4 Replies)
Discussion started by: littleIdiot
4 Replies

3. Shell Programming and Scripting

Search values between ranges in File1 within File2

Hi people, I have 2 files, one with a list of non consecutive ranges (File1.txt), where each range begins with the value in column 1 and finishes with the value in column 2 in the same line, as can be seen above. 215312581156279 215312581166279 215312582342558 215312582357758... (4 Replies)
Discussion started by: cgkmal
4 Replies

4. Shell Programming and Scripting

date ranges

Hi, Please anyone help to achive this using perl or unix scripting . This is date in my table 20090224,based on the date need to check the files,If file exist for that date then increment by 1 for that date and check till max date 'i.e.20090301 and push those files . files1_20090224... (2 Replies)
Discussion started by: akil
2 Replies

5. Shell Programming and Scripting

getting files between specific date ranges in solaris

hi ! how can i get files in a directory between certain date ranges ? say all files created/modified between Jan24 - Jan31 thanks (10 Replies)
Discussion started by: aliyesami
10 Replies

6. Shell Programming and Scripting

extracting columns falling within specific ranges for multiple files

Hi, I need to create weekly files from daily records stored in individual monthly filenames from 1999-2010. my sample file structure is like the ones below: daily record stored per month: 199901.xyz, 199902.xyz, 199903.xyz, 199904.xyz ...199912.xyz records inside 199901.xyz (original data... (4 Replies)
Discussion started by: ida1215
4 Replies

7. Shell Programming and Scripting

Values between ranges

Hi, I have two files file1 chr1_22450_22500 chr2_12300_12350 chr1_34500_34550 file2 11000_13000 15000_19000 33000_44000 If the file 1 ranges fall between file2 ranges then assign the value of file2 in column 2 to file1 output: chr2_12300_12350 11000_13000 chr1_34500_34550 ... (7 Replies)
Discussion started by: Diya123
7 Replies

8. UNIX for Dummies Questions & Answers

Extracting rows from a text file based on the values of two columns (given ranges)

Hi, I have a tab delimited text file with multiple columns. The second and third columns include numbers that have not been sorted. I want to extract rows where the second column includes a value between -0.01 and 0.01 (including both numbers) and the first third column includes a value between... (1 Reply)
Discussion started by: evelibertine
1 Replies

9. Shell Programming and Scripting

Date derivation

Need to get saturday's date of the previous week. Input will be the sysdate (today's date) Output should be previous weeks's saturday format Your help is required extremely. I have done all the date arthimetic calculation, but not getting the desired output. Please help (10 Replies)
Discussion started by: help_scr_seeker
10 Replies

10. Shell Programming and Scripting

Grepping the data between 2 date ranges

Hi There, Good Day !! I have txt file containing data in the below format. There are many lines, here i have mentioned for example. cat remo.txt 2/3/2017 file1 3/4/2016 file2 6/6/2015 file5 1/1/2018 file3 4/3/2014 file4 - - - I need to grep the file names for given date rage... (11 Replies)
Discussion started by: kumar85shiv
11 Replies

LEARN ABOUT SUNOS

nvlist_add_int8

nvlist_add_boolean(3NVPAIR)				 Name-value Pair Library Functions			       nvlist_add_boolean(3NVPAIR)

NAME

       nvlist_add_boolean,  nvlist_add_boolean_value,  nvlist_add_byte,  nvlist_add_int8,  nvlist_add_uint8,  nvlist_add_int16, nvlist_add_uint16,
       nvlist_add_int32,  nvlist_add_uint32,  nvlist_add_int64,  nvlist_add_uint64,   nvlist_add_string,   nvlist_add_nvlist,	nvlist_add_nvpair,
       nvlist_add_boolean_array,      nvlist_add_byte_array,	  nvlist_add_int8_array,      nvlist_add_uint8_array,	   nvlist_add_int16_array,
       nvlist_add_uint16_array,     nvlist_add_int32_array,	nvlist_add_uint32_array,     nvlist_add_int64_array,	  nvlist_add_uint64_array,
       nvlist_add_string_array, nvlist_add_nvlist_array - add new name-value pair to nvlist_t

SYNOPSIS

       cc [ flag... ] file... -lnvpair [ library... ]
       #include <libnvpair.h>

       int nvlist_add_boolean(nvlist_t *nvl, const char *name);

       int nvlist_add_boolean_value(nvlist_t *nvl, const char *name, boolean_t val);

       int nvlist_add_byte(nvlist_t *nvl, const char *name, uchar_t val);

       int nvlist_add_int8(nvlist_t *nvl, const char *name, int8_t val);

       int nvlist_add_uint8(nvlist_t *nvl, const char *name, uint8_t val);

       int nvlist_add_int16(nvlist_t *nvl, const char *name, int16_t val);

       int nvlist_add_uint16(nvlist_t *nvl, const char *name, uint16_t val);

       int nvlist_add_int32(nvlist_t *nvl, const char *name, int32_t val);

       int nvlist_add_uint32(nvlist_t *nvl, const char *name, uint32_t val);

       int nvlist_add_int64(nvlist_t *nvl, const char *name, int64_t val);

       int nvlist_add_uint64(nvlist_t *nvl, const char *name, uint64_t val);

       int nvlist_add_string(nvlist_t *nvl, const char *name, const char *val);

       int nvlist_add_nvlist(nvlist_t *nvl, const char *name, nvlist_t *val);

       int nvlist_add_nvpair(nvlist_t *nvl, nvpair_t *nvp);

       int nvlist_add_boolean_array(nvlist_t *nvl, const char *name, boolean_t *val, uint_t nelem);

       int nvlist_add_byte_array(nvlist_t *nvl, const char *name, uchar_t *val, uint_t nelem);

       int nvlist_add_int8_array(nvlist_t *nvl, const char *name, int8_t *val, uint_t nelem);

       int nvlist_add_uint8_array(nvlist_t *nvl, const char *name, uint8_t *val, uint_t nelem);

       int nvlist_add_int16_array(nvlist_t *nvl, const char *name, int16_t *val, uint_t nelem);

       int nvlist_add_uint16_array(nvlist_t *nvl, const char *name, uint16_t *val, uint_t nelem);

       int nvlist_add_int32_array(nvlist_t *nvl, const char *name, int32_t *val, uint_t nelem);

       int nvlist_add_uint32_array(nvlist_t *nvl, const char *name, uint32_t *val, uint_t nelem);

       int nvlist_add_int64_array(nvlist_t *nvl, const char *name, int64_t *val, uint_t nelem);

       int nvlist_add_uint64_array(nvlist_t *nvl, const char *name, uint64_t *val, uint_t nelem);

       int nvlist_add_string_array(nvlist_t *nvl, const char *name, char *const *val, uint_t nelem);

       int nvlist_add_nvlist_array(nvlist_t *nvl, const char *name, nvlist_t **val, uint_t nelem);

PARAMETERS

       nvl	The nvlist_t (name-value pair list) to be processed.

       nvp	The nvpair_t (name-value pair) to be processed.

       name	Name of the nvpair (name-value pair).

       nelem	Number of elements in value (that is, array size).

       val	Value or starting address of the array value.

DESCRIPTION

       These  functions  add a new name-value pair to an nvlist_t. The uniqueness of nvpair name and data types follows the nvflag argument speci-
       fied for nvlist_alloc(). See nvlist_alloc(3NVPAIR).

       If NV_UNIQUE_NAME was specified for nvflag, existing nvpairs with matching names are removed before the new nvpair is added.

       If NV_UNIQUE_NAME_TYPE was specified for nvflag, existing nvpairs with matching names and data types are removed before the new	nvpair	is
       added.

       If neither was specified for nvflag, the new nvpair is unconditionally added at the end of the list. The library preserves the order of the
       name-value pairs across packing, unpacking, and duplication.

       Multiple threads can simultaneously read the same nvlist_t, but only one thread can actively change a given nvlist_t at a time. The  caller
       is responsible for the synchronization.

       The nvlist_add_boolean() function is deprecated. The nvlist_add_boolean_value() function should be used instead.

RETURN VALUES

       These functions return 0 on success and an error value on failure.

ERRORS

       These functions will fail if:

       EINVAL	       There is an invalid argument.

       ENOMEM	       There is insufficient memory.

ATTRIBUTES

       See attributes(5) for descriptions of the following attributes:

       +----------------------------+------------------------------+
       |      ATTRIBUTE TYPE	    |	    ATTRIBUTE VALUE	   |
       +----------------------------+------------------------------+
       |Interface Stability	    | Evolving			   |
       +----------------------------+------------------------------+
       |MT-Level		    | MT-Safe			   |
       +----------------------------+------------------------------+

SEE ALSO

       libnvpair(3LIB), attributes(5)

SunOS 5.10							    2 Feb 2004					       nvlist_add_boolean(3NVPAIR)
All times are GMT -4. The time now is 09:47 AM.
Unix & Linux Forums Content Copyright 1993-2022. All Rights Reserved.
Privacy Policy