March 26, 2014 at 9:30 am
Hi,
I'm requesting for your help, trying to fix this query
Please give me your advice:
create table t1
(cob_id int primary key,
operation_id int,
due_date smalldatetime,
inver_id int)
create table t2
(corr int,
inver_id int primary key,
tip_inver int
)
create table t3
(code int primary key,
descrip varchar(100)
)
create table t4
(inver_id int,
operation_id int primary key,
due_date smalldatetime
)
GO
ALTER TABLE t2 ADD CONSTRAINT FK_t2 FOREIGN KEY (TIP_INVER)
REFERENCES t3 (CODE)
ALTER TABLE t4 ADD CONSTRAINT FK_t4 FOREIGN KEY (INVER_ID)
REFERENCES T2 (INVER_ID)
ALTER TABLE t1 ADD CONSTRAINT FK_t1 FOREIGN KEY (INVER_ID)
REFERENCES t2 (INVER_ID)
go
INSERT INTO t3
(CODE, DESCRIP)
SELECT 1,'TEXT VALUE 1' UNION ALL
SELECT 2,'TEXT VALUE 2' UNION ALL
SELECT 3,'TEXT VALUE 3' UNION ALL
SELECT 4,'TEXT VALUE 4'
INSERT INTO t2
(CORR,INVER_ID,TIP_INVER)
SELECT 1,1,1 UNION ALL
SELECT 2,2,1 UNION ALL
SELECT 3,3,2 UNION ALL
SELECT 4,4,3 UNION ALL
SELECT 5,5,1 UNION ALL
SELECT 6,6,1 UNION ALL
SELECT 7,7,2 UNION ALL
SELECT 8,8,3 UNION ALL
SELECT 9,9,1 UNION ALL
SELECT 10,10,1 UNION ALL
SELECT 11,11,2 UNION ALL
SELECT 12,12,3 UNION ALL
SELECT 13,13,1 UNION ALL
SELECT 14,14,1 UNION ALL
SELECT 15,15,2
INSERT INTO t4
(INVER_ID,OPERATION_ID, DUE_DATE)
SELECT 1,1,'2014/20/03' UNION ALL
SELECT 1,2,'2014/21/03' UNION ALL
SELECT 1,3,'2014/24/03' UNION ALL
SELECT 1,4,'2014/24/03' UNION ALL
SELECT 5,5,'2014/20/03' UNION ALL
SELECT 6,6,'2014/21/03' UNION ALL
SELECT 7,7,'2014/24/03' UNION ALL
SELECT 8,8,'2014/25/03'
INSERT INTO t1
(COB_ID,OPERATION_ID, DUE_DATE,INVER_ID)
SELECT 1,1,'2013/20/03',1 UNION ALL
SELECT 2,3,'2013/20/03',1 UNION ALL
SELECT 3,4,'2014/24/03',1 UNION ALL
SELECT 4,1,'2013/20/03',5 UNION ALL
SELECT 5,1,'2014/20/03',6 UNION ALL
SELECT 6,1,'2014/24/03',7 UNION ALL
SELECT 7,1,'2014/24/03',8 UNION ALL
SELECT 8,5,'2014/20/03',5 UNION ALL
SELECT 9,6,'2014/25/03',6 UNION ALL
SELECT 10,7,'2013/24/03',7 UNION ALL
SELECT 11,8,'2014/20/03',8
Using this query:
SELECT A.* FROM (
SELECT 'PF' AS 'CHAIN VALUES',
c.DESCRIP AS [TIPE], b.CORR, a.OPERATION_ID, MAX(a.DUE_DATE) AS [DUE DATE 1],
d.due_date as [DUE DATE 2]
FROM T1 AS a INNER JOIN
T2 AS b ON A.INVER_ID = B.INVER_ID INNER JOIN
T3 AS c ON b.TIP_INVER = c.CODE INNER JOIN
T4 AS d ON a.OPERATION_ID = d.OPERATION_ID
GROUP BY c.DESCRIP, b.CORR, a.OPERATION_ID, d.due_date
) AS A
WHERE A.[DUE DATE 1] < A.[DUE DATE 2]
which output:
PF TEXT VALUE 1 1 1 2013-03-20 00:00:00 2014-03-20 00:00:00
PF TEXT VALUE 1 5 12013-03-20 00:00:00 2014-03-20 00:00:00
PF TEXT VALUE 1 1 32013-03-20 00:00:00 2014-03-24 00:00:00
PF TEXT VALUE 2 7 72013-03-24 00:00:00 2014-03-24 00:00:00
PF TEXT VALUE 3 8 82014-03-20 00:00:00 2014-03-25 00:00:00
but output requested:
PF TEXT VALUE 1 1 1 2013-03-20 00:00:00 2014-03-20 00:00:00
PF TEXT VALUE 1 1 32013-03-20 00:00:00 2014-03-24 00:00:00
PF TEXT VALUE 2 7 72013-03-24 00:00:00 2014-03-24 00:00:00
PF TEXT VALUE 3 8 82014-03-20 00:00:00 2014-03-25 00:00:00
Thanks a lot
March 26, 2014 at 12:05 pm
Hi,
You can use the ROW_NUMBER function to produce the desired results.
Is this what you are looking for?
SELECT
A.[CHAIN VALUES]
,A.TIPE
,A.CORR
,A.OPERATION_ID
,A.[DUE DATE 1]
,A.[DUE DATE 2]
FROM (
SELECT
ROW_NUMBER() OVER
(PARTITION BY c.DESCRIP,a.OPERATION_ID,MAX(a.DUE_DATE),d.due_date
ORDER BY (SELECT NULL)) AS PRID
,'PF' AS 'CHAIN VALUES'
,c.DESCRIP AS [TIPE]
,b.CORR
,a.OPERATION_ID
,MAX(a.DUE_DATE) AS [DUE DATE 1]
,d.due_date as [DUE DATE 2]
FROM T1 AS a
INNER JOIN T2 AS b ON A.INVER_ID = B.INVER_ID
INNER JOIN T3 AS c ON b.TIP_INVER = c.CODE
INNER JOIN T4 AS d ON a.OPERATION_ID = d.OPERATION_ID
GROUP BY c.DESCRIP, b.CORR, a.OPERATION_ID, d.due_date
) AS A
WHERE A.PRID = 1
AND A.[DUE DATE 1] < A.[DUE DATE 2]
CHAIN VALUES TIPE CORR OPERATION_ID DUE DATE 1 DUE DATE 2
------------ ------------- ----- ------------ -------------------- --------------------
PF TEXT VALUE 1 1 1 2013-03-20 00:00:00 2014-03-20 00:00:00
PF TEXT VALUE 1 1 3 2013-03-20 00:00:00 2014-03-24 00:00:00
PF TEXT VALUE 2 7 7 2013-03-24 00:00:00 2014-03-24 00:00:00
PF TEXT VALUE 3 8 8 2014-03-20 00:00:00 2014-03-25 00:00:00
March 26, 2014 at 3:00 pm
Hi,
Thanks a lot for your response, wonderful solution
but if i needed the last record of each group,
how could instruct this request ?
March 26, 2014 at 3:14 pm
You could change the value in the order by to get the desired row for each group.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply