August 3, 2011 at 7:44 am
Hello,
I have to create a query based on the condition to display data based on ARRIVAL_SCHEDULE_DT > = 90 days from 'todays date'. In other words the data should be within the time period of 90 days 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.
I have created the following query but it doesnt work as required:
ARRIVAL_SCHEDULE_DT > = dateadd(dd, -90, getdate())
Does anyone know what am I missing ?
Thanks,
Paul
August 3, 2011 at 7:48 am
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.
August 3, 2011 at 7:50 am
Ninja's_RGR'us (8/3/2011)
Aside for good manner and respect?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.
Thanks for your reply but this is different from what I asked initially, that was the 2nd part this one is the first one.
Now could you pls tell where did this table 'calendar' come from ?
August 3, 2011 at 7:51 am
It's just a table I have on my system with all dates. that way I can send tested code.
For some reason the >= and <= signs don't show up correctly
August 3, 2011 at 7:55 am
Trying to send the correct code again.
SELECT
*
FROM
dbo.Calendar
WHERE
dt >= DATEADD(D , 0 , DATEDIFF(D , 0 , GETDATE()))
AND dt <= DATEADD(MM , 3 , GETDATE())
Link to my calendar table http://www.sqlservercentral.com/Forums/Attachment8839.aspx
August 3, 2011 at 7:58 am
pwalter83 (8/3/2011)
Ninja's_RGR'us (8/3/2011)
Aside for good manner and respect?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.
Thanks for your reply but this is different from what I asked initially, that was the 2nd part this one is the first one.
Now could you pls tell where did this table 'calendar' come from ?
Sorry Ninja for my outburst, I am so frustrated by all this, just short of takign some extreme step...
your solution really worked well...thanks a lot and I really appreciate your help and patience with me.
out of curiousity, do you live in the US ?
August 3, 2011 at 8:02 am
Canada.
Happy to help.
See how easy it is when you can run the code as posted? That's why we ask for sample data and it takes only 2 minutes to build.
August 3, 2011 at 8:13 am
Ninja's_RGR'us (8/3/2011)
Trying to send the correct code again.
SELECT*
FROM
dbo.Calendar
WHERE
dt >= DATEADD(D , 0 , DATEDIFF(D , 0 , GETDATE()))
AND dt <= DATEADD(MM , 3 , GETDATE())
Link to my calendar table http://www.sqlservercentral.com/Forums/Attachment8839.aspx
thanks again...I hope you have understood what I am trying to do now....could you really help me with the other part of the problem now. I sincerely believe it does not need the DDL and sample data. Its just adding onto the solution you just provided. It will waste my time on something thats not required as there are a lot of tables involved.
The issue is to just add the condition where PORT_CD = 'BEZEE' to the above solution but the catch is it needs to be within the same query as the report still needs to display other PORT_CDs along with 'BEZEE'. if I just include this within the WHERE clause:
AND PORT_CD = 'BEZEE'
AND ARRIVAL_SCHEDULE_DT > = DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())) AND ARRIVAL_SCHEDULE_DT <= DATEADD(MM, 3, GETDATE())
this would filter out other PORT_CDs, which I dont want. In other words, BEZEE is one of the values in the PORT_CD column which is in the same table as the ARRIVAL_SCHEDULE_DT column. So the filtering of data in the report should be based on ARRIVAL_SCHEDULE_DT @ PORT_CD 'BEZEE' <= 3 months from 'todays date' (a part of which you have already provided the solution for).
Thanks again !
August 3, 2011 at 8:22 am
Not clear in my mind.
I need to see a few rows of data and required output.
August 3, 2011 at 8:48 am
Ninja's_RGR'us (8/3/2011)
Not clear in my mind.I need to see a few rows of data and required output.
Okay I have some sample data below from a table-
---------------------------
ARRIVAL_SCHEDULE_DT - PORT_CD
2011-08-07 08:00:00.000 - MXVER
2011-08-04 08:00:00.000 - BEZEE
2011-08-05 08:00:00.000 - GBSOU
----------------------------
In the above case, the report would display the data for August 4th as this corresponds with the PORT_CD = 'BEZEE'. I have also attached the sample output of the report.
August 3, 2011 at 8:51 am
Of those 3 rows, which ones need to stay, which one needs to go.
August 3, 2011 at 9:17 am
Ninja's_RGR'us (8/3/2011)
Of those 3 rows, which ones need to stay, which one needs to go.
Actually that would depend, if the ARRIVAL_SCHEDULE_DT is within the 3 months time range when checked against PORT_CD = 'BEZEE', then the data would be displayed on the report otherwise not.
For e.g.- if the ARRIVAL_SCHEDULE_DT is '2011-09-03 08:00:00.000' for PORT_CD = 'BEZEE', the date for the same would be displayed on the report because '2011-09-03' is within 3 months from today's date.
Am I clear now ?
August 3, 2011 at 9:19 am
So basically you want the date filter to only apply to that 1 port.
All other ports show all rows no matter the dates?
August 3, 2011 at 9:24 am
Ninja's_RGR'us (8/3/2011)
So basically you want the date filter to only apply to that 1 port.All other ports show all rows no matter the dates?
Actually no, the date filter along with the PORT_CD = 'BEZEE' is to be applied to the whole of the report. The report data would be filtered on this condition.
In a functional way it is equivalent to saying that a ship should touch or reach (ARRIVAL_SCHEDULE_DT) the first port 'BEZEE' within 3 months from today (current date) in order for the data to be qualified to be included in the report.
August 3, 2011 at 9:30 am
Can you paste the whole where clause you have right now (need to see the parenthesis)
It's still unclear to me atm. I'll read it back in a couple hours when I'm less tired.
L8r.
Viewing 15 posts - 1 through 15 (of 63 total)
You must be logged in to reply to this topic. Login to reply