August 3, 2011 at 3:39 am
Hi,
I have a task to create a sql code for the following requirement in the WHERE clause:
ARRIVAL_SCHEDULE_DT @ PORT_CD 'BEZEE' <= 3 months from 'todays date'
This is what I have created till now:
ARRIVAL_SCHEDULE_DT > = dateadd(mm, -3, getdate().
However, I dont know how to incorporate PORT_CD = 'BEZEE' within the above query.
Any help would be appreciated.
Regards,
Paul
August 3, 2011 at 3:49 am
WHERE PORT_CD = 'BEZEE'
AND ARRIVAL_SCHEDULE_DT < = dateadd(mm, 3, getdate()
August 3, 2011 at 3:56 am
Dwayne Dibley (8/3/2011)
WHERE PORT_CD = 'BEZEE'
AND ARRIVAL_SCHEDULE_DT < = dateadd(mm, 3, getdate()
Thanks for your reply but I hope it was that easy. Actually the thing is I need to incorporate PORT = 'BEZEE' within the query I have created.
The reason being, the report displays other PORT_CD's as well and if I include what you have mentioned, it would only display the data where PORT_CD = 'BEZEE' and this is what I dont want.
The requirement is to display all records where ARRIVAL_SCHEDULE_DT @ PORT_CD 'BEZEE' <= 3 months from 'todays date' and not filter the whole report based on PORT_CD = 'BEZEE'.
I hope I am clear in my explanation.
August 3, 2011 at 4:10 am
Something like this?
SELECT <column list>
FROM MyTable
EXCEPT
SELECT <column list>
FROM MyTable
WHERE PORT_CD = 'BEZEE'
AND ARRIVAL_SCHEDULE_DT < dateadd(mm, 3, getdate())
John
August 3, 2011 at 4:11 am
Well..., it is not very clear, could you please use but I think you can start working with this:
SELECT *
FROM table_name
WHERE
-- PORT_CD = 'BEZEE' AND
ARRIVAL_SCHEDULE_DT < = dateadd(mm, 3, getdate())
Regards,
Iulian
August 3, 2011 at 4:13 am
or instead of :
ARRIVAL_SCHEDULE_DT <= dateadd(mm, 3, getdate())
you can use this condition too:
datediff(mm, ARRIVAL_SCHEDULE_DT, getdate()) <= 3
Iulian
August 3, 2011 at 4:18 am
Iulian -207023 (8/3/2011)
or instead of :
ARRIVAL_SCHEDULE_DT <= dateadd(mm, 3, getdate())you can use this condition too:
datediff(mm, ARRIVAL_SCHEDULE_DT, getdate()) <= 3Iulian
I would advise against doing that, since it would make the condition non-sargable and hence would mean that any index on ARRIVAL_SCHEDULE_DT would not be able to be used. It also means that the DATEDIFF operation needs to be carried out once for each row in the table, instead of the DATEADD operation being carried out just once.
John
August 3, 2011 at 4:21 am
John Mitchell-245523 (8/3/2011)
Something like this?SELECT <column list>
FROM MyTable
EXCEPT
SELECT <column list>
FROM MyTable
WHERE PORT_CD = 'BEZEE'
AND ARRIVAL_SCHEDULE_DT < dateadd(mm, 3, getdate())
John
I hope this would explain it better. Below are the conditions in the WHERE clause of my query:
-----------------------
WHERE MG_VESSEL_PARTICULAR.VESSEL_TYPE_CD = 'PCC'
AND MG_VESSEL_VOYAGE.SERVICE_CD IN ('WBTAS', 'NCTAS')
AND MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT < = dateadd(mm, 3, getdate())
AND PORT_CD IN ('BEZEE', 'GBSOU', 'MXVER', 'USGLS', 'USJAX', 'USBAL', 'USCHS', 'USSSI')
------------------------------------------
I need to manipulate the code in bold to include PORT_CD = 'BEZEE'. If you see the next line of code, you will find that the report needs to include all the PORT_CD's mentioned so I cannot just write PORT_CD = 'BEZEE' as that would filter out the other PORT_CDs.
The requirement I have is to display data where the ARRIVAL_SCHEDULE_DT is three months preceding current date when the ship first arrived at PORT_CD = 'BEZEE' (I work for a shipping company). I know the formula to calculate the preceding 3 months but dont have any idea as to how to merge this condition with 'When the ship first arrived at PORT_CD = 'BEZEE''
Hope this would make it more clear.
August 3, 2011 at 4:24 am
John Mitchell-245523 (8/3/2011)
Iulian -207023 (8/3/2011)
or instead of :
ARRIVAL_SCHEDULE_DT <= dateadd(mm, 3, getdate())you can use this condition too:
datediff(mm, ARRIVAL_SCHEDULE_DT, getdate()) <= 3Iulian
I would advise against doing that, since it would make the condition non-sargable and hence would mean that any index on ARRIVAL_SCHEDULE_DT would not be able to be used. It also means that the DATEDIFF operation needs to be carried out once for each row in the table, instead of the DATEADD operation being carried out just once.
John
Thanks John..but my problem I have is to display data where the ARRIVAL_SCHEDULE_DT is three months preceding current date when the ship first arrived at PORT_CD = 'BEZEE' (I work for a shipping company). I know the formula to calculate the preceding 3 months but dont have any idea as to how to merge this condition with 'When the ship first arrived at PORT_CD = 'BEZEE''
Hope this would make it more clear.
August 3, 2011 at 4:51 am
OK, I think we've come as far as we can without your providing any table DDL and sample data. Please post the former in terms of CREATE TABLE statements, and the latter in the form of INSERT statements. Please also show your whole query.
Thanks
John
August 3, 2011 at 5:07 am
John Mitchell-245523 (8/3/2011)
OK, I think we've come as far as we can without your providing any table DDL and sample data. Please post the former in terms of CREATE TABLE statements, and the latter in the form of INSERT statements. Please also show your whole query.Thanks
John
I dont think there is any need to post the table DDL in this case, I have posted whatever I thought was relevant to my query.
I fail to understand why would you need all this for a single line query. Maybe someone else can help me in this regard.
August 3, 2011 at 5:12 am
Click on the first link in my signature block. 😛
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
August 3, 2011 at 5:15 am
pwalter83 (8/3/2011)
John Mitchell-245523 (8/3/2011)
OK, I think we've come as far as we can without your providing any table DDL and sample data. Please post the former in terms of CREATE TABLE statements, and the latter in the form of INSERT statements. Please also show your whole query.Thanks
John
I dont think there is any need to post the table DDL in this case, I have posted whatever I thought was relevant to my query.
I fail to understand why would you need all this for a single line query. Maybe someone else can help me in this regard.
Mainly because seeing sample data and expected output will allow everyone to understand your request. Currently, what you've asked is not clear at all (probably because you yourself don't understand the method required to produce what you're expecting so are not explaining the problem). With sample data and expected output, you'll get tested working code in very little time.
August 3, 2011 at 5:17 am
pwalter83 (8/3/2011)
John Mitchell-245523 (8/3/2011)
OK, I think we've come as far as we can without your providing any table DDL and sample data. Please post the former in terms of CREATE TABLE statements, and the latter in the form of INSERT statements. Please also show your whole query.Thanks
John
I dont think there is any need to post the table DDL in this case, I have posted whatever I thought was relevant to my query.
I fail to understand why would you need all this for a single line query. Maybe someone else can help me in this regard.
This is ridiculous I am not asking to create a stored procedure from scratch, this is just a one line query we are talking about here....belos is the sql code that I have created:
--------------------
SELECT VESSEL_NAME, MAX(NATIONALITY_CD) AS NATIONALITY_CD , PORT_CD,
MAX(case when ARRIVAL_SCHEDULE_DT = DATEADD (month, 3, getdate()) and PORT_CD = 'BEZEE' then 'N' else '' end +
case when MG_VSLVOY_SCHEDULE.LAST_UPDATE_DT = getdate() and PORT_CD in ('BEZEE','GBSOU','MXVER','USGLS','USJAX','USBAL','USCHS','USSSI') then 'C' else '' end) as DateStatus,
/*
MAX(case when MG_VSLVOY_SCHEDULE.LAST_UPDATE_DT = getdate() and PORT_CD in ('BEZEE','GBSOU','MXVER','USGLS','USJAX','USBAL','USCHS','USSSI') then 'C' else '' end) as DateStatus,
*/
MG_VESSEL_VOYAGE.LEG_CD, VOYAGE_CD,
MAX(PORT_SEQUENCE_NBR) AS PORT_SEQUENCE_NBR, MAX(ARRIVAL_SCHEDULE_DT) AS ARRIVAL_SCHEDULE_DT, MAX(ARRIVAL_ESTIMATE_DT) AS ARRIVAL_SCHEDULE_DT,
MAX(ARRIVAL_STATUS_CD) AS ARRIVAL_STATUS_CD, MAX(ARRIVAL_ACTUAL_DT) AS ARRIVAL_ACTUAL_DT, MAX(DEPART_ESTIMATE_DT) AS DEPART_ESTIMATE_DT,
MAX(DEPART_ACTUAL_DT) AS DEPART_ACTUAL_DT, MAX(DEPART_STATUS_CD) AS DEPART_STATUS_CD,
MAX(Case When PORT_CD = 'BEZEE' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'BEZEE' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT end) as BEZEE_E,
MAX(Case When PORT_CD = 'GBSOU' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'GBSOU' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT end) as GBSOU_E,
MAX(Case When PORT_CD = 'MXVER' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'MXVER' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT end) as MXVER_E,
MAX(Case When PORT_CD = 'USGLS' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'USGLS' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT end) as USGLS_E,
MAX(Case When PORT_CD = 'USJAX' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'USJAX' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT end) as USJAX_E,
MAX(Case When PORT_CD = 'USBAL' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'USBAL' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT end) as USBAL_E,
MAX(Case When PORT_CD = 'USCHS' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'USCHS' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT end) as USCHS_E,
MAX(Case When PORT_CD = 'USSSI' and ARRIVAL_STATUS_CD = 'E' THEN ARRIVAL_ESTIMATE_DT
when PORT_CD = 'USSSI' and ARRIVAL_STATUS_CD = 'A' THEN ARRIVAL_ACTUAL_DT end) as USSSI_E,
MAX(Case When PORT_CD = 'BEZEE' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'BEZEE' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT end) as BEZEE_G,
MAX(Case When PORT_CD = 'GBSOU' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'GBSOU' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT end) as GBSOU_G,
MAX(Case When PORT_CD = 'MXVER' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'MXVER' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT end) as MXVER_G,
MAX(Case When PORT_CD = 'USGLS' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'USGLS' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT end) as USGLS_G,
MAX(Case When PORT_CD = 'USJAX' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'USJAX' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT end) as USJAX_G,
MAX(Case When PORT_CD = 'USBAL' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'USBAL' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT end) as USBAL_G,
MAX(Case When PORT_CD = 'USCHS' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'USCHS' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT end) as USCHS_G,
MAX(Case When PORT_CD = 'USSSI' and DEPART_STATUS_CD = 'E' THEN DEPART_ESTIMATE_DT
when PORT_CD = 'USSSI' and DEPART_STATUS_CD = 'A' THEN DEPART_ACTUAL_DT end) as USSSI_G
FROM MG_VESSEL_PARTICULAR
INNER JOIN MG_VESSEL_VOYAGE
ON MG_VESSEL_PARTICULAR.VESSEL_CD = MG_VESSEL_VOYAGE.VESSEL_CD
INNER JOIN MG_VSLVOY_HEADER
ON MG_VESSEL_VOYAGE.VESSEL_CD = MG_VSLVOY_HEADER.VESSEL_CD
AND MG_VESSEL_VOYAGE.LEG_CD = MG_VSLVOY_HEADER.LEG_CD
AND MG_VESSEL_VOYAGE.VOYAGE_CD = MG_VSLVOY_HEADER.VOYAGE_NUM
INNER JOIN MG_VSLVOY_PORT_CONTROL
ON MG_VSLVOY_HEADER.VSLVOY_HEADER_ID = MG_VSLVOY_PORT_CONTROL.VSLVOY_HEADER_ID
AND PORT_SEQUENCE_NBR = 1
INNER JOIN MG_VSLVOY_SCHEDULE
ON MG_VSLVOY_PORT_CONTROL.VSLVOY_SCHEDULE_ID = MG_VSLVOY_SCHEDULE.VSLVOY_SCHEDULE_ID
WHERE MG_VESSEL_PARTICULAR.VESSEL_TYPE_CD = 'PCC'
AND MG_VESSEL_VOYAGE.SERVICE_CD IN ('WBTAS', 'NCTAS')
AND MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT > = dateadd(dd, -90, getdate())
AND PORT_CD IN ('BEZEE', 'GBSOU', 'MXVER', 'USGLS', 'USJAX', 'USBAL', 'USCHS', 'USSSI')
group by VESSEL_NAME, VOYAGE_CD, MG_VESSEL_VOYAGE.LEG_CD, PORT_CD
-------------------------------------
August 3, 2011 at 5:39 am
Do you know what DDL and sample data is?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply