Database Discussion Boards

Re: Complex SQL queries

Richard MacCutchan3-Jul-14 5:22

Richard MacCutchan

3-Jul-14 5:22

Start at http://www.w3schools.com/sql/[^].

Re: Complex SQL queries

data modeling guy3-Jul-14 20:02

data modeling guy

3-Jul-14 20:02

Thanks

Re: Complex SQL queries

PIEBALDconsult3-Jul-14 5:53

PIEBALDconsult

3-Jul-14 5:53

Sure, try implementing a Turing Machine in SQL.

You'll never get very far if all you do is follow instructions.

Re: Complex SQL queries

data modeling guy3-Jul-14 20:03

data modeling guy

3-Jul-14 20:03

Thanks

Re: Complex SQL queries

Eddy Vluggen3-Jul-14 11:24

Eddy Vluggen

3-Jul-14 11:24

data modeling guy wrote:

Hello- can you give me any example
scenarios/questions for writing a complex SELECT statements? My intention is to
improve my SQL skills.

Calculate reputation points of a user.
Fetch the contibutions-section for a user (see the section on your user-page; it contains some statistics)
Fetch the ten members from the database that answered most questions last month.
Fetch the voting-percentages for the straw-poll result for all possible answers.
Fetch any low-repuation member that posts the same message in multiple places, ordered by the amount of similar messages.
Fetch the data to show the forum-content for page 3 of this forum, in threaded view, with 50 posts per page.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Re: Complex SQL queries

Mycroft Holmes3-Jul-14 14:20

Mycroft Holmes

3-Jul-14 14:20

No no no these suggestions expect him to do the work and write the queries, it sounds like he wants YOU to write the queries and he will study them.

Good luck getting CM to lend out the database and schema Laugh | :laugh:

Never underestimate the power of human stupidity
RAH

Re: Complex SQL queries

data modeling guy3-Jul-14 20:05

data modeling guy

3-Jul-14 20:05

Thanks Eddy. I Did some more google search and found some more. Cheers!

Re: Complex SQL queries

Jörgen Andersson3-Jul-14 20:28

Jörgen Andersson

3-Jul-14 20:28

Get Joe Celko's Puzzles and Answers[^]

Wrong is evil and must be defeated. - Jeff Ello[^]

SQL convert(datetime, number) wrong?

Miller Nguyen29-Jun-14 16:38

Miller Nguyen

29-Jun-14 16:38

I tried this

SQL

select convert(datetime, 41818) as mydate

result

SQL

2014-06-30 00:00:00.000

In Excel, it should be

6/28/2014

It's always 2 days in advance

Please help!

Thanks a lot

Re: SQL convert(datetime, number) wrong?

PIEBALDconsult29-Jun-14 17:45

PIEBALDconsult

29-Jun-14 17:45

Why do you think it's wrong?

SELECT DATEDIFF ( dd , '1900-01-01' , '2014-06-30' )
SELECT DATEADD ( dd, 41818 , '1900-01-01' )

Miller Nguyen wrote:
In Excel, it should be

What do you mean "it should be" ?

You'll never get very far if all you do is follow instructions.

Re: SQL convert(datetime, number) wrong?

Miller Nguyen29-Jun-14 22:41

Miller Nguyen

29-Jun-14 22:41

I don't say it's wrong, that's why I put the QUESTION MARK in the topic.

I just wonder why Excel and SQL are giving different result

Re: SQL convert(datetime, number) wrong?

Bernhard Hiller29-Jun-14 21:19

Bernhard Hiller

29-Jun-14 21:19

What the f...? You are right, I tested it.
Look at day number 0: with SQL Server, it is Jan 1, 1900; with Excel: Jan 0, 1900.
The next bug is the leap year: Excel treats 1900 as a leap year (that's wrong!), while SQL Server correctly knows that 1900 is not a leap year.
In sum, those differences account for the 2 days difference in current dates.

Re: SQL convert(datetime, number) wrong?

Jörgen Andersson29-Jun-14 22:38

Jörgen Andersson

29-Jun-14 22:38

Here's[^] Joels explanation.

Wrong is evil and must be defeated. - Jeff Ello[^]

Re: SQL convert(datetime, number) wrong?

PIEBALDconsult30-Jun-14 3:39

PIEBALDconsult

30-Jun-14 3:39

Yeah, as I drifted off to sleep last night I recalled that Joel had written about Excel having to support a Lotus 123 issue. Thumbs Up | :thumbsup:

You'll never get very far if all you do is follow instructions.

Re: SQL convert(datetime, number) wrong?

Jörgen Andersson30-Jun-14 3:53

Jörgen Andersson

30-Jun-14 3:53

I love that story.
They brought a f*** counter to all meetings with BillG. Laugh | :laugh:

Wrong is evil and must be defeated. - Jeff Ello[^]

Re: SQL convert(datetime, number) wrong?

Jörgen Andersson29-Jun-14 22:39

Jörgen Andersson

29-Jun-14 22:39

See my answer to Bernhard

Wrong is evil and must be defeated. - Jeff Ello[^]

Re: SQL convert(datetime, number) wrong?

Miller Nguyen29-Jun-14 22:52

Miller Nguyen

29-Jun-14 22:52

Thanks a lot

Re: SQL convert(datetime, number) wrong?

Jörgen Andersson29-Jun-14 23:03

Jörgen Andersson

29-Jun-14 23:03

Oh, btw, have a look if this[^] and this[^] would be of any use for you.

Wrong is evil and must be defeated. - Jeff Ello[^]

Re: SQL convert(datetime, number) wrong?

Bernhard Hiller30-Jun-14 0:38

Bernhard Hiller

30-Jun-14 0:38

For example, -0.5 and 0.5 both mean noon on 30 December 1899

No, no, no! I do not want to use that function!

Re: SQL convert(datetime, number) wrong?

Jörgen Andersson30-Jun-14 1:15

Jörgen Andersson

30-Jun-14 1:15

It's not a bug, it's a feature.

Wrong is evil and must be defeated. - Jeff Ello[^]

Re: SQL convert(datetime, number) wrong?

GuyThiebaut30-Jun-14 0:52

GuyThiebaut

30-Jun-14 0:52

I get around this, and other issues such as collation issues between SQL Server and Excel, by always passing dates to Excel as text in a format of dd-MMM-yyyy e.g. '01-Jan-2014'.
It's not pretty but it has worked so far...

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

modified 30-Jun-14 8:06am.

MySQL Stored Procs -

ericgahn29-Jun-14 9:08

ericgahn

29-Jun-14 9:08

Hi all;

I am learning to write stored procs on MySQL and am going crazy.

SQL

DELIMITER $$

DROP PROCEDURE IF EXISTS CleanCopyEnvData$$

CREATE PROCEDURE CleanCopyEnvData ()
BEGIN

  insert into  EnvData(UserDate, XAction, Balance, UserID)
  select 
    CAST(udate as Date)
    , CAST(amount1 as Decimal(6,3))
    , CAST(amount2 as Decimal(6,3))
    , CAST(UID as UnSigned)
  from 
    DataLoad;
    
END $$

DELIMITER ;

I get an error:

Error starting at line : 17 in command -
END $$
Error at Command Line : 17 Column : 1
Error report -
SQL Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'END $$' at line 1

I am using Oracle SQL Developer and the INSERT statement works ok when executed on its own. I can do these things blindfolded with my arms tied on MSSQL. Any help appreciated.

EG.

Re: MySQL Stored Procs -

Wayne Gaylard29-Jun-14 22:37

Wayne Gaylard

29-Jun-14 22:37

You don't need the delimiter expression $$ after your END statement.

SQL

DELIMITER $$

DROP PROCEDURE IF EXISTS CleanCopyEnvData$$

CREATE PROCEDURE CleanCopyEnvData ()
BEGIN

  insert into  EnvData(UserDate, XAction, Balance, UserID)
  select
    CAST(udate as Date)
    , CAST(amount1 as Decimal(6,3))
    , CAST(amount2 as Decimal(6,3))
    , CAST(UID as UnSigned)
  from
    DataLoad;

END

Everyone dies - but not everyone lives

Re: MySQL Stored Procs -

ericgahn1-Jul-14 19:03

ericgahn

1-Jul-14 19:03

Thanks Wayne.

Re: MySQL Stored Procs -

Wayne Gaylard1-Jul-14 19:28

Wayne Gaylard

1-Jul-14 19:28

Glad to help.

Everyone dies - but not everyone lives

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.