Help in creating a query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help in creating a query

Author

Topic

mgandra
Starting Member

15 Posts

Posted - 2008-05-15 : 15:35:34

Script for creating a table and populate with sample data is given below:

CREATE TABLE #SHIPMENT_FOLDER_LEGS_T(SHIPMENT_FOLDER_SEQUENCE_NR INT,
SHIPMENT_EQUIPMENT_SEQUENCE_NR INT,
LEG_SEQUENCE_NR INT,
VEHICLE_TYPE_CD INT)

INSERT INTO #SHIPMENT_FOLDER_LEGS_T
VALUES(1002085213,1,1,3)
INSERT INTO #SHIPMENT_FOLDER_LEGS_T
VALUES(1002085213,1,2,3)
INSERT INTO #SHIPMENT_FOLDER_LEGS_T
VALUES(1002085213,1,3,4)

INSERT INTO #SHIPMENT_FOLDER_LEGS_T
VALUES(1002085214,1,1,3)
INSERT INTO #SHIPMENT_FOLDER_LEGS_T
VALUES(1002085214,1,2,3)
INSERT INTO #SHIPMENT_FOLDER_LEGS_T
VALUES(1002085214,1,3,2)
INSERT INTO #SHIPMENT_FOLDER_LEGS_T
VALUES(1002085214,1,4,10)
INSERT INTO #SHIPMENT_FOLDER_LEGS_T
VALUES(1002085214,1,5,4)

INSERT INTO #SHIPMENT_FOLDER_LEGS_T
VALUES(1002085213,1,1,2)
INSERT INTO #SHIPMENT_FOLDER_LEGS_T
VALUES(1002085213,1,2,3)
INSERT INTO #SHIPMENT_FOLDER_LEGS_T
VALUES(1002085213,1,3,4)

I want to create a query to find SHIPMENT_FOLDER_SEQUENCE_NR of the record where LEG_SEQUENCE_NR of VEHICLE_TYPE_CD of 2 is less than LEG_SEQUENCE_NR of VEHICLE_TYPE_CD of 4. It may be clarified that SHIPMENT_FOLDER_SEQUENCE_NR and SHIPMENT_EQUIPMENT_SEQUENCE_NR are repeated for each leg.

Thanks for help.

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2008-05-15 : 15:58:58

Something like this? Your requirements are a little vague. For instance, would you want to return multiple rows if SHIPMENT_FOLDER_SEQUENCE_NR and SHIPMENT_EQUIPMENT_SEQUENCE_NR are repeated for each leg? This query returns one row for VEHICLE_TYPE_CD = 2 since i use min(). If you change it to max(), you get 2 rows.

select SHIPMENT_FOLDER_SEQUENCE_NR from #SHIPMENT_FOLDER_LEGS_T
where VEHICLE_TYPE_CD = 2
and LEG_SEQUENCE_NR < (select min(LEG_SEQUENCE_NR) from #SHIPMENT_FOLDER_LEGS_T where VEHICLE_TYPE_CD = 4)

Terry

chandan_joshi80
Starting Member

30 Posts

Posted - 2008-05-16 : 02:28:45

select * FROM
(select * from #SHIPMENT_FOLDER_LEGS_T WHERE VEHICLE_TYPE_CD=2 ) A
INNER JOIN (select * from #SHIPMENT_FOLDER_LEGS_T WHERE VEHICLE_TYPE_CD=4 ) B
ON A.SHIPMENT_FOLDER_SEQUENCE_NR=B.SHIPMENT_FOLDER_SEQUENCE_NR
WHERE A.LEG_SEQUENCE_NR<B.LEG_SEQUENCE_NR

chandan Joshi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-18 : 13:41:40

[code]SELECT * FROM
(
SELECT SHIPMENT_FOLDER_SEQUENCE_NR,
SHIPMENT_EQUIPMENT_SEQUENCE_NR,
SUM(CASE WHEN VEHICLE_TYPE_CD=4 THEN LEG_SEQUENCE_NR ELSE 0 END) AS Sum4,
SUM(CASE WHEN VEHICLE_TYPE_CD=2 THEN LEG_SEQUENCE_NR ELSE 0 END)
AS Sum2
FROM #SHIPMENT_FOLDER_LEGS_T
GROUP BY SHIPMENT_FOLDER_SEQUENCE_NR,
SHIPMENT_EQUIPMENT_SEQUENCE_NR)t
WHERE Sum4>0 AND Sum2>0 AND Sum4-Sum2 >0[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources