All Forums Database
odivini 3 posts Joined 02/15
26 Feb 2015
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

 
 

Rohan_Sawant 55 posts Joined 07/14
27 Feb 2015

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

dnoeth 4628 posts Joined 11/04
27 Feb 2015

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

 

Dieter

odivini 3 posts Joined 02/15
27 Feb 2015

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

You must sign in to leave a comment.