August 4, 2011 at 7:37 am
pwalter83 (8/4/2011)
pwalter83 (8/4/2011)
Ninja's_RGR'us (8/3/2011)
Normally you'd join on the itinaty table where first port = 'port code'Still not too sure where you need help here.
Can't see the tables, data nor code.
Pls also find the sample report output attached.
Ninja, do you know how to convert the data in the tables to an insert using SQL automatically ? I dont know where to go with this ? repenting the day I got myself forced into sql..
I tried it but cannot create inserts for the tables as mentioned in the tutorial.
August 4, 2011 at 7:43 am
Try again, that code works.
August 4, 2011 at 8:55 am
Ninja's_RGR'us (8/4/2011)
Try again, that code works.
okay thanks for your help, Ninja, I cant go any further than this.
August 4, 2011 at 9:04 am
Why?
August 4, 2011 at 9:19 am
Ninja's_RGR'us (8/4/2011)
Why?
I am tired, mate and just cant seem to create inserts through the method suggested in the link you mentioned. this is really frustrating...i am done with the whole report and its just this last small thing i am getting stuck on now.
August 4, 2011 at 9:24 am
Sleep on it... can only be better tommorrow.
August 4, 2011 at 10:12 am
Now all that needs to be done is just include the condition that the above scenario will apply only if the ship reaches the port(PORT_CD) 'BEZEE' first.
How do you determine this?
ie how do you know if port 'BEZEE' is first.
Far away is close at hand in the images of elsewhere.
Anon.
August 4, 2011 at 10:46 am
Paul,
Did you ever resolve the issue with the script to insert data?
I may have missed it but where is the sample output?
Regards,
WC
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 5, 2011 at 3:34 am
David Burrows (8/4/2011)
Now all that needs to be done is just include the condition that the above scenario will apply only if the ship reaches the port(PORT_CD) 'BEZEE' first.
How do you determine this?
ie how do you know if port 'BEZEE' is first.
Thanks for your reply, David. There is a column known as arrival status , if the value in the column is 'A' for PORT_CD = 'BEZEE' then this would indicate that the ship arrived at BEZEE port.
The problem is i simply cant insert PORT_CD = 'BEZEE' as that would only bring out the data related to BEZEE and there are 7 other ports for which the data needs to be included too.
August 5, 2011 at 3:35 am
Welsh Corgi (8/4/2011)
Paul,Did you ever resolve the issue with the script to insert data?
I may have missed it but where is the sample output?
Regards,
WC
Hi,
No Welsh, I am still struggling with it but to be honest have given up on it now and trying to resolve the query on my own.
August 5, 2011 at 4:36 am
OK understand the status for arrived but still not sure about the 'arrived first' bit.
This will select all ports except 'BEZEE' which will only be selected if the ship has arrived but was scheduled (if ARRIVAL_SCHEDULE_DT is the scheduled arrival date) to arrive in next 3 months
WHERE MG_VESSEL_PARTICULAR.VESSEL_TYPE_CD = 'PCC'
AND MG_VESSEL_VOYAGE.SERVICE_CD IN ('WBTAS', 'NCTAS')
AND (PORT_CD IN ('GBSOU', 'MXVER', 'USGLS', 'USJAX', 'USBAL', 'USCHS', 'USSSI')
OR (PORT_CD = 'BEZEE'
AND MG_VSLVOY_SCHEDULE.ARRIVAL_STATUS_CD = 'A'
AND MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT >= DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
AND MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT <= DATEADD(MM, 3, GETDATE())))
Far away is close at hand in the images of elsewhere.
Anon.
August 5, 2011 at 4:54 am
David Burrows (8/5/2011)
OK understand the status for arrived but still not sure about the 'arrived first' bit.This will select all ports except 'BEZEE' which will only be selected if the ship has arrived but was scheduled (if ARRIVAL_SCHEDULE_DT is the scheduled arrival date) to arrive in next 3 months
WHERE MG_VESSEL_PARTICULAR.VESSEL_TYPE_CD = 'PCC'
AND MG_VESSEL_VOYAGE.SERVICE_CD IN ('WBTAS', 'NCTAS')
AND (PORT_CD IN ('GBSOU', 'MXVER', 'USGLS', 'USJAX', 'USBAL', 'USCHS', 'USSSI')
OR (PORT_CD = 'BEZEE'
AND MG_VSLVOY_SCHEDULE.ARRIVAL_STATUS_CD = 'A'
AND MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT >= DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
AND MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT <= DATEADD(MM, 3, GETDATE())))
Thanks for your reply. Actually the condition ARRIVAL_STATUS_CD = 'A' for PORT_CD = 'BEZEE' is sufficient to meet the requirement.
I tried your query but its displaying data for ARRIVAL_SCHEDULE_DT 3 months prior to todays date now.
August 5, 2011 at 5:33 am
pwalter83 (8/5/2011)
I tried your query but its displaying data for ARRIVAL_SCHEDULE_DT 3 months prior to todays date now.
My mistake I thought the date test was for BEZEE only
WHERE MG_VESSEL_PARTICULAR.VESSEL_TYPE_CD = 'PCC'
AND MG_VESSEL_VOYAGE.SERVICE_CD IN ('WBTAS', 'NCTAS')
AND (PORT_CD IN ('GBSOU', 'MXVER', 'USGLS', 'USJAX', 'USBAL', 'USCHS', 'USSSI')
OR (PORT_CD = 'BEZEE' AND MG_VSLVOY_SCHEDULE.ARRIVAL_STATUS_CD = 'A'))
AND MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT >= DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
AND MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT <= DATEADD(MM, 3, GETDATE())
Far away is close at hand in the images of elsewhere.
Anon.
August 5, 2011 at 6:57 am
David Burrows (8/5/2011)
pwalter83 (8/5/2011)
I tried your query but its displaying data for ARRIVAL_SCHEDULE_DT 3 months prior to todays date now.My mistake I thought the date test was for BEZEE only
WHERE MG_VESSEL_PARTICULAR.VESSEL_TYPE_CD = 'PCC'
AND MG_VESSEL_VOYAGE.SERVICE_CD IN ('WBTAS', 'NCTAS')
AND (PORT_CD IN ('GBSOU', 'MXVER', 'USGLS', 'USJAX', 'USBAL', 'USCHS', 'USSSI')
OR (PORT_CD = 'BEZEE' AND MG_VSLVOY_SCHEDULE.ARRIVAL_STATUS_CD = 'A'))
AND MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT >= DATEADD(D, 0, DATEDIFF(D, 0, GETDATE()))
AND MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT <= DATEADD(MM, 3, GETDATE())
Well thanks David, I think your code works...but would have to get it tested thoroughly by the users for affirmation.
Your help is deeply appreciated !!!
August 9, 2011 at 2:15 am
Ninja's_RGR'us (8/3/2011)
Not much.
SELECT * FROM dbo.Calendar WHERE dt >= DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())) AND dt < DATEADD(MM, 3, GETDATE())Again STOP cross posting.
Hi Ninja,
If you remember you helped me with a code where I had to create a query based on the condition to display data based on ARRIVAL_SCHEDULE_DT > = 3 months from 'todays date'. In other words the data should be within the time period of 3 months from todays date.
For e.g if say today is August 3rd, 2011, then the report should display data for all the values till the ARRIVAL_SCHEDULE_DT of November 1st, 2011.
the solution you have was:
SELECT * FROM dbo.Calendar WHERE dt >= DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())) AND dt < DATEADD(MM, 3, GETDATE())
There is now a slight change in the requirement which is that the report should display all data based on ARRIVAL_SCHEDULE_DT > = 3 months from 'todays date' and also display all back data till the third month.
For e.g. if today's date is August 9, 2011 then after calculating 3 months period i.e. November 9, 2011 all the data prior to November 9, 2011 should be displayed. Is this explanation helpful to you ? I dont know if this logic is really possible to achieve. Thanks for your help.
Thanks,
Paul
Viewing 15 posts - 31 through 45 (of 63 total)
You must be logged in to reply to this topic. Login to reply