"SQL: copying data up"

Post #303027087 by figaro on Saturday 8th of December 2018 03:51:11 PM

Full Discussion: SQL: copying data up
SQL: copying data up

I need to fix an SQL statement in MySQL that should calculate a field using values from two of the columns and I prefer to do this using set-based programming, ie not procedural. What needs to happen is that in a separate column called "delta" the value of "level" is copied depending on whether "in_event" is 0 or 1. If it is 1, then the value of level is copied, else the last known value of delta is copied.
This is similar to how data is copied down in spreadsheets for underlying cells that have no value, except that in this case data is copied up instead of down.

These are the statements I am using:
Code:
create table `events` (
  `date` date default null,
  `sortorder` int(11) not null default '0',
  `level` float default null,
  `in_event` int(1) not null default '0'
);

insert into events values 
('2018-09-04', 2, 1547.8, 0),
('2018-09-03', 3, 1571, 1),
('2018-08-31', 4, 1541.6, 0),
('2018-08-30', 5, 1575.4, 0),
('2018-08-29', 6, 1596.6, 0),
('2018-08-28', 7, 1626.4, 0),
('2018-08-24', 8, 1575, 0),
('2018-08-23', 9, 1536, 0),
('2018-08-22', 10, 1579, 0),
('2018-08-21', 11, 1559, 1),
('2018-08-20', 12, 1554, 0),
('2018-08-17', 13, 1534, 0),
('2018-08-16', 14, 1543, 0),
('2018-08-15', 15, 1542.2, 0),
('2018-08-14', 16, 1646.8, 0),
('2018-08-13', 17, 1680.4, 0),
('2018-08-10', 18, 1667.6, 0),
('2018-08-09', 19, 1711.6, 0),
('2018-08-08', 20, 1726.6, 0),
('2018-08-07', 21, 1736.2, 1),
('2018-08-06', 22, 1678, 0),
('2018-08-03', 23, 1692.8, 0),
('2018-08-02', 24, 1655.4, 0),
('2018-08-01', 25, 1717.4, 0),
('2018-07-31', 26, 1732.8, 0),
('2018-07-30', 27, 1678, 0),
('2018-07-27', 28, 1691.4, 0),
('2018-07-26', 29, 1687.4, 0),
('2018-07-25', 30, 1700, 0),
('2018-07-24', 31, 1695, 0),
('2018-07-23', 32, 1604.6, 1),
('2018-07-20', 33, 1599.8, 0),
('2018-07-19', 34, 1622, 0),
('2018-07-18', 35, 1691, 0),
('2018-07-17', 36, 1666.2, 1);

I have gotten as far as the following, but it copies the data down instead of up:
Code:
alter table events add column delta decimal(10, 2);
update events set delta = level where in_event = 1;
select date, sortorder, level, in_event, case when delta is not NULL && @d := delta then delta else @d end as delta
from events
order by date desc;

This is the result that I am looking for:
Code:
insert into events values 
('2018-09-04', 2, 1547.8, 0, 1571),  -- value of 1571 is copied up, because it is the last known value and in_event is not 1
('2018-09-03', 3, 1571, 1, 1571),    -- value of 1571 is attained, because in_event is 1.
('2018-08-31', 4, 1541.6, 0, 1559),
('2018-08-30', 5, 1575.4, 0, 1559),
('2018-08-29', 6, 1596.6, 0, 1559),
('2018-08-28', 7, 1626.4, 0, 1559),
('2018-08-24', 8, 1575, 0, 1559),
('2018-08-23', 9, 1536, 0, 1559),
('2018-08-22', 10, 1579, 0, 1559),
('2018-08-21', 11, 1559, 1, 1736.2),
('2018-08-20', 12, 1554, 0, 1736.2),
('2018-08-17', 13, 1534, 0, 1736.2),
('2018-08-16', 14, 1543, 0, 1736.2),
('2018-08-15', 15, 1542.2, 0, 1736.2),
('2018-08-14', 16, 1646.8, 0, 1736.2),
('2018-08-13', 17, 1680.4, 0, 1736.2),
('2018-08-10', 18, 1667.6, 0, 1736.2),
('2018-08-09', 19, 1711.6, 0, 1736.2),
('2018-08-08', 20, 1726.6, 0, 1736.2),
('2018-08-07', 21, 1736.2, 1, 1736.2),
('2018-08-06', 22, 1678, 0, 1604.6),
('2018-08-03', 23, 1692.8, 0, 1604.6),
('2018-08-02', 24, 1655.4, 0, 1604.6),
('2018-08-01', 25, 1717.4, 0, 1604.6),
('2018-07-31', 26, 1732.8, 0, 1666.2),
('2018-07-30', 27, 1678, 0, 1666.2),
('2018-07-27', 28, 1691.4, 0, 1666.2),
('2018-07-26', 29, 1687.4, 0, 1666.2),
('2018-07-25', 30, 1700, 0, 1666.2),
('2018-07-24', 31, 1695, 0, 1666.2),
('2018-07-23', 32, 1604.6, 1, 1666.2),
('2018-07-20', 33, 1599.8, 0, 1666.2),
('2018-07-19', 34, 1622, 0, 1666.2),
('2018-07-18', 35, 1691, 0, 1666.2),
('2018-07-17', 36, 1666.2, 1, 1666.2);

 
UNIX and Linux Facts

Unix (trademarked as the UNIX certification mark) is a very mature family of multitasking, multiuser computer operating systems that derive from the original AT&T Unix. Unix development starting in the 1970s by legendary Bell Labs programmers Ken Thompson, Dennis Ritchie, and others. Unix was first targetted for the Bell System and AT&T licensed Unix to outside parties. In the 1970s time period, this lead to a variety of for-profit as well as not-for-profit Unix variants. In the early days, this included the University of California, Berkeley (BSD), Microsoft (Xenix), IBM (AIX), and Sun Microsystems (Solaris). In the early 1990s AT&T sold their Unix rights to Novell. In 1995 Novell sold their Unix business to the Santa Cruz Operation (SCO). The UNIX trademark was passed to The Open Group, a "neutral" industry consortium. The Open Group promoted the use of the UNIX trademark for certified operating systems that comply with the Single UNIX Specification (SUS). In 2014 Apple's macOS became the Unix version with the largest global install base and macOS remains the largest Unix-user base today.
Linux is a family of open source Unix-like operating systems based on the Linux kernel. The Linux kernal is an operating system kernel first released by Linus Torvalds on 17 September 1991. Linux distributions include the Linux kernel, system software and libraries. Popular Linux distributions include Debian, Fedora, and Ubuntu. Commercial Linux distributions include Red Hat Enterprise Linux and SUSE Linux Enterprise Server. Linux is freely redistributable. Anyone may create a Linux distribution for any purpose.
Related Linux and UNIX Technical Support
copying data to raw devices using 'dd'
Neville
Hello all, I'm new here, so this information may exist elsewhere on this forum. If so, please point me in the right direction. Here's the problem. I'm trying to migrate Oracle data from an HP system to a Sun system using a raw device as a 'bridge' between the two systems. Both machines...... UNIX for Advanced & Expert Users
4
UNIX for Advanced & Expert Users
How to use sql data file in unix csv file as input to an sql query from shell
Nareshp
Hi , I used the below script to get the sql data into csv file using unix scripting. I m getting the output into an output file but the output file is not displayed in a separe columns . #!/bin/ksh export FILE_PATH=/maav/home/xyz/abc/ rm $FILE_PATH/sample.csv sqlplus -s...... Shell Programming and Scripting
2
Shell Programming and Scripting
SQL: copying data down
figaro
I have a series of observations of which one column is sometimes missing (zero): date temp delta 1977 284.54 29.84 1978 149.82 0 1979 320.71 28.45 1980 176.76 0 1981 854.65 0 1984 817.65 0 1985 990.58 27.98 1986 410.21 0 1987 405.93 0 1988 482.9 0 What I would like to achieve is a...... Programming
8
Programming
Copying data from files to directories
newbie2010
I have the following that I'd like to do: 1. I have split a file into separate files that I placed into the /tmp directory. These files are named F1 F2 F3 F4. 2. In addition, I have several directories which are alphabetized as dira dirb dirc dird. 3. I'd like to be able to copy F1 F2 F3 F4...... Shell Programming and Scripting
2
Shell Programming and Scripting
Copying data from one file server to another
br1an
Hello people, I have a question regarding transferring data from one file server to another. The server is a Solaris 9 box The old file server is connected via Ethernet cable, and the new file server we are switching is a Fiber channel. can I use the dd if=server:/app1 of=server2:/app1 ...... Solaris
2
Solaris