Querying data for time period

Database

Querying data for time period

Hi All,

I’m a new Teradata user and trying to create SQL to pull appropriate device for a specific bill period. Appreciate your help with writing SQL accurately.

Data tables:








USER_ID CHARGE_AMT BILL_ST_DT BILL_END_DT  
A 5.00 12/2/2014 1/2/2015  
B 20.00 12/2/2014 1/2/2015  
C 55 12/2/2014 1/2/2015  
         
USER_DMD_ID USER_ID DEVICE ACTIVATION_DT DEACTIVATION_DT
2 A Samsung Convoy II 8/6/2014 12/22/2014
1 A Motorola E815 4/15/2013 3/10/2014
5 B ADR6200 12/20/2014 12/22/2014
4 B Samsung Intensity II Gray 10/3/2014 12/20/2014
3 B LG-VX5500 3/24/2014 8/13/2014
2 B LG-VX5500 4/24/2012 3/24/2014
1 B UNKNOWN 4/19/2012 4/24/2012
2 C Samsung Illusion 4/18/2014 12/5/2014
1 C UNKNOWN 4/18/2014 4/18/2014

Results should be:






USER_ID CHARGE_AMT DEVICE
A 5.00 Samsung Convoy II
B 20.00 ADR6200
C 55 Samsung Illusion
3 REPLIES
Enthusiast

Re: Querying data for time period

Hi Odivini,

Please correctly specify your requirement. I have derived your output predicting the requirement. I have not used the date columns at all. And please from next time onward provide the data in a correct format so that we could help you out. The below may fulfil your requirement (I am not sure since the requirement was not specified at all).

/* Creating test data */

CREATE MULTISET VOLATILE TABLE VT_TEST_DATA_1 ,NO FALLBACK ,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO,
NO LOG
(
USER_ID VARCHAR(1),
CHARGE_AMT INTEGER,
BILL_ST_DT DATE,
BILL_END_DT DATE
)
PRIMARY INDEX(USER_ID)
ON COMMIT PRESERVE ROWS;

INSERT INTO VT_TEST_DATA_1 VALUES ('A',5,DATE '2014-12-02',DATE '2015-01-02');
INSERT INTO VT_TEST_DATA_1 VALUES ('B',20,DATE '2014-12-02',DATE '2015-01-02');
INSERT INTO VT_TEST_DATA_1 VALUES ('C',55,DATE '2014-12-02',DATE '2015-01-02');

CREATE MULTISET VOLATILE TABLE VT_TEST_DATA_2 ,NO FALLBACK ,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO,
NO LOG
(
USER_DMD_ID INTEGER,
USER_ID VARCHAR(1),
DEVICE VARCHAR(50),
ACTIVATION_DT DATE,
DEACTIVATION_DT DATE
)
PRIMARY INDEX(USER_ID)
ON COMMIT PRESERVE ROWS;

INSERT INTO VT_TEST_DATA_2 VALUES (2,'A','Samsung Convoy II',DATE '2014-08-06',DATE '2014-12-22');
INSERT INTO VT_TEST_DATA_2 VALUES (1,'A','Motorola E815',DATE '2013-04-15',DATE '2014-03-10');
INSERT INTO VT_TEST_DATA_2 VALUES (5,'B','ADR6200',DATE '2014-12-20',DATE '2014-12-22');
INSERT INTO VT_TEST_DATA_2 VALUES (4,'B','Samsung Intensity II Gray',DATE '2014-10-03',DATE '2014-12-20');
INSERT INTO VT_TEST_DATA_2 VALUES (3,'B','LG-VX5500',DATE '2014-03-24',DATE '2014-08-13');
INSERT INTO VT_TEST_DATA_2 VALUES (1,'B','UNKNOWN',DATE '2012-04-19',DATE '2012-04-24');
INSERT INTO VT_TEST_DATA_2 VALUES (2,'C','Samsung Illusion',DATE '2014-04-18',DATE '2014-12-05');
INSERT INTO VT_TEST_DATA_2 VALUES (1,'C','UNKNOWN',DATE '2014-04-18',DATE '2014-04-18');

/* Completed creating test data */

/* Your required output */
SELECT
A.USER_ID
, A.CHARGE_AMT
, A.DEVICE
FROM
(
SELECT
A.USER_ID
, A.CHARGE_AMT
, B.USER_DMD_ID
, B.DEVICE
, MAX(B.USER_DMD_ID) OVER (PARTITION BY B.USER_ID ORDER BY B.USER_ID) AS MAX_USER_DMD_ID
FROM
VT_TEST_DATA_1 A
INNER JOIN
VT_TEST_DATA_2 B
ON A.USER_ID = B.USER_ID
) A
WHERE
A.USER_DMD_ID = A.MAX_USER_DMD_ID;

Thanks,

Rohan Sawant

Junior Contributor

Re: Querying data for time period

You want the device with the latest activation date for the specified bill period?

  select * 
FROM
VT_TEST_DATA_1 A
INNER JOIN
VT_TEST_DATA_2 B
ON A.USER_ID = B.USER_ID
and (BILL_ST_DT, BILL_END_DT) overlaps (ACTIVATION_DT, DEACTIVATION_DT)
qualify row_number()
over (partition by A.USER_ID
order by ACTIVATION_DT DESC) = 1

Re: Querying data for time period

Thank you very much for your help. SQL works as intended.