display record between null dates

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 2005 Forums

Transact-SQL (2005)

display record between null dates

Author

Topic

somenoob
Posting Yak Master

112 Posts

Posted - 2011-11-03 : 04:29:30

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 04:32:46

[code]
SELECT CASE WHEN TERMINAL_ID IS NULL THEN 'OTHER LOCATION' ELSE TERMINAL_ID END AS 'TERMINAL',CNTR_N,CNTR_TYPE_C,DERIVED_CNTR_TYPE_C,DG_GROUP_C,DG_IMO_CLASS_C,
LENGTH_Q,LOAD_SPECIAL_DETAIL_X,CNTR_ST_C,CNTR_OPR_C,DIRECT_LOAD_I,
DIRECT_DELIVERY_I,UC_I,ISO_SIZE_TYPE_C,REEFER_TEMP_Q,PURP_C,
DERIVED_CNTR_PURP_C,COMMODITY_C,LOCATION_C,BLOCK_N,
SLOT_FROM_N,SLOT_TO_N,ROW_N,LEVEL_N,LOAD_PORT_C,DEST_PORT_C,DISC_PORT1_C,
DISC_PORT2_C,DISC_PORT3_C,ARIV_DT,EXIT_DT,DISC_DT,LOAD_DT,
DISC_ABBR_VESSEL_M,LOAD_ABBR_VESSEL_M,DISC_ABBR_VOYAGE_IN_N,
LOAD_ABBR_VOYAGE_OUT_N,CAT_C,LOAD_CONNECTION_ST_C,INSTRUCTION_TYPE_C,STAY_DAYS_Q,INTERGATEWAY_I,BATCH_ID
FROM OPS_DLY_AGING_DTL
WHERE ((TERMINAL_ID=@Terminal) OR (TERMINAL_ID IS NULL)) AND ((STAY_DAYS_Q>=@range1 OR @range1 IS NULL) AND (STAY_DAYS_Q<=@range2 OR @range2 IS NULL))

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

somenoob
Posting Yak Master

112 Posts

Posted - 2011-11-03 : 04:35:29

wow that was a fast reply. thanks it worked!!!

somenoob
Posting Yak Master

112 Posts

Posted - 2011-11-03 : 04:41:06

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 04:47:47

sorry you explanation doesnot match with query provided, i cant see any filter on terminal as BT
Can you elaborate with some sample data what exactly is problem?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

somenoob
Posting Yak Master

112 Posts

Posted - 2011-11-03 : 04:51:19

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 04:53:27

if you dont want Alla and otherlocation query should be

SELECT DISTINCT TERMINAL_ID, TERMINAL_ID
AS 'TERMINAL'
FROM OPS_DLY_AGING_DTL
WHERE TERMINAL_ID IS NOT NULL

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

somenoob
Posting Yak Master

112 Posts

Posted - 2011-11-03 : 04:58:40

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 05:01:20

are you telling that all and other location comes in data or are you telling its appearing inside dropdown?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

somenoob
Posting Yak Master

112 Posts

Posted - 2011-11-03 : 05:03:10

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 05:22:19

then it should be like


SELECT DISTINCT TERMINAL_ID AS value,TERMINAL_ID AS Prompt
FROM OPS_DLY_AGING_DTL
WHERE TERMINAL_ID IS NOT NULL
UNION
SELECT 'All', ''
ORDER BY TERMINAL_ID ASC


link value to value property of parameter and prompt to label property

and make query like


SELECT  CASE WHEN TERMINAL_ID IS NULL THEN 'OTHER LOCATION' ELSE TERMINAL_ID END AS 'TERMINAL',CNTR_N,CNTR_TYPE_C,DERIVED_CNTR_TYPE_C,DG_GROUP_C,DG_IMO_CLASS_C,
LENGTH_Q,LOAD_SPECIAL_DETAIL_X,CNTR_ST_C,CNTR_OPR_C,DIRECT_LOAD_I,
DIRECT_DELIVERY_I,UC_I,ISO_SIZE_TYPE_C,REEFER_TEMP_Q,PURP_C,
DERIVED_CNTR_PURP_C,COMMODITY_C,LOCATION_C,BLOCK_N,
SLOT_FROM_N,SLOT_TO_N,ROW_N,LEVEL_N,LOAD_PORT_C,DEST_PORT_C,DISC_PORT1_C,
DISC_PORT2_C,DISC_PORT3_C,ARIV_DT,EXIT_DT,DISC_DT,LOAD_DT,
DISC_ABBR_VESSEL_M,LOAD_ABBR_VESSEL_M,DISC_ABBR_VOYAGE_IN_N,
LOAD_ABBR_VOYAGE_OUT_N,CAT_C,LOAD_CONNECTION_ST_C,INSTRUCTION_TYPE_C,STAY_DAYS_Q,INTERGATEWAY_I,BATCH_ID
FROM         OPS_DLY_AGING_DTL
WHERE    ((TERMINAL_ID=@Terminal OR @Terminal='All') OR (TERMINAL_ID IS NULL)) AND ((STAY_DAYS_Q>=@range1 OR @range1 IS NULL) AND (STAY_DAYS_Q<=@range2 OR @range2 IS NULL))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

somenoob
Posting Yak Master

112 Posts

Posted - 2011-11-03 : 05:24:55

after i edit the things,

when the date range are null and the terminal is bt, other location still appear in the report..
please help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-04 : 05:50:41

i'm not understand your problem fully. can you post some screenshots of what you're facing?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-04 : 12:14:34

quote:
Originally posted by somenoob

after i edit the things,

when the date range are null and the terminal is bt, other location still appear in the report..
please help

The only help I can provide is a link that will assist you in asking a question:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources