SQL code issue

Question

SQL code issue

Viewing 14 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply

pwalter83 SSChampion Points: 14575 More actions · Answer 1

Welsh Corgi (8/3/2011)
Do you know what DDL and sample data is?

No actually my English is not good neither are my SQL skills...I do know what a stored procedure is though but I wonder what would a DDL look like, would that be a table structure and would the sample data be the data within the table...i think i maybe wrong but you know the correct answer, right ? that must be cos ur the only SQL expert that exists and the world must bow at your feet ?

Forget about answering my query instead don't even try to reply...that would block others from doing so...

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 2

Bon Voyage.

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/

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

pwalter83 (8/3/2011)
Welsh Corgi (8/3/2011)
Do you know what DDL and sample data is?
No actually my English is not good neither are my SQL skills...I do know what a stored procedure is though but I wonder what would a DDL look like, would that be a table structure and would the sample data be the data within the table...i think i maybe wrong but you know the correct answer, right ? that must be cos ur the only SQL expert that exists and the world must bow at your feet ?
Forget about answering my query instead don't even try to reply...that would block others from doing so...

I'm out too.

pwalter83 SSChampion Points: 14575 More actions · Answer 4

pwalter83

SSChampion

Points: 14575

August 3, 2011 at 7:05 am

#1363009

Welsh Corgi (8/3/2011)
Bon Voyage.

You too

pwalter83 SSChampion Points: 14575 More actions · Answer 5

Ninja's_RGR'us (8/3/2011)
pwalter83 (8/3/2011)
Welsh Corgi (8/3/2011)
Do you know what DDL and sample data is?
No actually my English is not good neither are my SQL skills...I do know what a stored procedure is though but I wonder what would a DDL look like, would that be a table structure and would the sample data be the data within the table...i think i maybe wrong but you know the correct answer, right ? that must be cos ur the only SQL expert that exists and the world must bow at your feet ?
Forget about answering my query instead don't even try to reply...that would block others from doing so...
I'm out too.

Were you ever in ?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Yup, waiting for ddl and sample data to GIVE YOU the tested solution.

You guys have been turning round and round for long enough, it's time for tested code.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

I hope that someone answers your question soon or you could take a hit for missing ships movement.

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/

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Welsh Corgi (8/3/2011)
I hope that someone answers your question soon or you could take a hit for missing ships movement.

No need to add any pressure, I think he feels it enough. That's why he's not seeing the obvious and not keen on wasting more time.

Cadavre SSC-Forever Points: 41690 More actions · Answer 9

pwalter83 (8/3/2011)
Welsh Corgi (8/3/2011)
Do you know what DDL and sample data is?
No actually my English is not good neither are my SQL skills...I do know what a stored procedure is though but I wonder what would a DDL look like, would that be a table structure and would the sample data be the data within the table...i think i maybe wrong but you know the correct answer, right ? that must be cos ur the only SQL expert that exists and the world must bow at your feet ?

Last try. . . seeing sample data and expected output will allow everyone to understand your request. Please read the following link and post readily consumable test data and sample output.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

We're talking about 5 minutes of your time (which is less time than you've spent on here refusing to set up test scripts), help us to help you.

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

pwalter83 (8/3/2011)
... 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.

Something like this?

AND (

(PORT_CD = 'BEZEE' AND MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT < = dateadd(mm, 3, getdate()))

OR PORT_CD IN ('GBSOU', 'MXVER', 'USGLS', 'USJAX', 'USBAL', 'USCHS', 'USSSI')

)

- but of course, without sample data, it's at best a long shot.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 11

ChrisM@Work (8/3/2011)
pwalter83 (8/3/2011)
... 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.
Something like this?
AND (
(PORT_CD = 'BEZEE' AND MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT < = dateadd(mm, 3, getdate()))
OR PORT_CD IN ('GBSOU', 'MXVER', 'USGLS', 'USJAX', 'USBAL', 'USCHS', 'USSSI')
)
- but of course, without sample data, it's at best a long shot.

I was just about to post the same thing...I guess I got to this thread too late in the day...;)

I probably would have switched it up though, and done this:

AND (

PORT_CD IN ('GBSOU', 'MXVER', 'USGLS', 'USJAX', 'USBAL', 'USCHS', 'USSSI')

OR (PORT_CD = 'BEZEE'

AND MG_VSLVOY_SCHEDULE.ARRIVAL_SCHEDULE_DT < = dateadd(mm, 3, getdate()))

)

But, that is only a style difference....

Edit: forgot a pair of parentheses

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 12

pwalter83 (8/3/2011)
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:

People like to test their code against data to be sure that YOU get the correct answer and the data also helps explain the problem. Read the article at the link you were provided to that so that you understand how easy that actually can be.

In the meantime, two possible answers to your question is to simply create a correlated, minimal-aggregated subquery in the WHERE clause to get the first date of arrival in the port of ''BEZEE" for the current ship...

... or ...

Create a CTE or derived table that finds the first date of arribal in the port of "BEZEE" and join to it using it's contents as criteria.

If you'd care to post some data IAW the first link in my signature below so I can check that my code works, I'd be happy to post some code for you. Thanks. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 13

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()) <= 3
Iulian
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

Thank you John

Iulian

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

Finally got him to post ddl and sample data.

Please forward this thread over here : http://www.sqlservercentral.com/Forums/Topic1153535-149-1.aspx

I'm out for now (tired, nothing against anyone).