SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


A Different Problem with Full Details


A Different Problem with Full Details

Author
Message
xtrmus2319
xtrmus2319
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 39
Hey guys,

I have a SQL problem.

1-) Firstly mine sql design ;



2-) Default SQL query


3-) But in some query results the values are partly the same and partly different.


4-) For this reason I grouped the coins and tarih columns. There are 100 different coins in the system. For this reason, I can capture the last minute data like this.



5-) I can get the data from 3 minutes ago.


6-) Here is my problem Smile



7-) And here Smile


Codes :

DECLARE @LastChangeDate AS smalldatetime;
SET @LastChangeDate = getDate();

Select a.tarih,a.coin,(A.price),b.tarih,b.coin,(B.price),(A.price/B.price) as 'Divide' from (SELECT tarih, coin, MIN(price ) AS price
FROM dbo.cointahmin
GROUP BY tarih, coin order by tarih desc,coin OFFSET 300 ROWS
FETCH NEXT 100 ROWS ONLY) A,
(SELECT Top 100 tarih, coin, MIN(price ) AS price
FROM dbo.cointahmin
GROUP BY tarih, coin order by tarih desc,coin) B



DECLARE @LastChangeDate AS smalldatetime;
SET @LastChangeDate = getDate();

Select a.tarih,a.coin,(A.price),b.tarih,b.coin,(B.price) from (SELECT tarih, coin, MIN(price ) AS price
FROM dbo.cointahmin
GROUP BY tarih, coin order by tarih desc,coin OFFSET 300 ROWS
FETCH NEXT 100 ROWS ONLY) A,
(SELECT Top 100 tarih, coin, MIN(price ) AS price
FROM dbo.cointahmin
GROUP BY tarih, coin order by tarih desc,coin) B


DECLARE @LastChangeDate AS smalldatetime;
SET @LastChangeDate = getDate();

SELECT Top 100 tarih, coin, MIN(price) AS price
FROM dbo.cointahmin where @LastChangeDate>tarih
GROUP BY tarih, coin order by tarih desc,coin


SELECT tarih, coin, price 
FROM dbo.cointahmin



Is it understandable enough? Thanks Smile
John Mitchell-245523
John Mitchell-245523
SSC Guru
SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)SSC Guru (133K reputation)

Group: General Forum Members
Points: 133247 Visits: 19360
First, sort out your database design. If a column is going to hold money data, give it a decimal data type, not nvarchar. If the data in your column is always seven characters wide, define it as (n)char(7). And if you've got duplicates in your data (or even if you haven't), add a primary key or unique constraint on tarih and index.

Now the query. You're doing a cross join between the results of the two subqueries. If each subquery returns a thousand rows, the cross join will return a million. Read about and understand the new-style join syntax (INNER JOIN, LEFT JOIN, RIGHT JOIN etc) and use it. I imagine you want to join on coin = coin and datediff = 3 minutes, don't you? Read about windowing functions as well: you may be able to use LAG or LEAD to avoid joins altogether.

That's the best advice I can give in the absence of table DDL and sample data.

John
drew.allen
drew.allen
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65379 Visits: 17349
This is the same problem as here. Don't start a new thread just because you don't like the answers that you are getting on your original thread.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
xtrmus2319
xtrmus2319
SSC Journeyman
SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)SSC Journeyman (82 reputation)

Group: General Forum Members
Points: 82 Visits: 39
drew.allen - Thursday, January 25, 2018 9:24 AM
This is the same problem as here. Don't start a new thread just because you don't like the answers that you are getting on your original thread.

Drew

Smile Bro I'm already on that subject. They just wanted detailed information.

ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)SSC Guru (166K reputation)

Group: General Forum Members
Points: 166480 Visits: 21721
xtrmus2319 - Thursday, January 25, 2018 9:27 AM
drew.allen - Thursday, January 25, 2018 9:24 AM
This is the same problem as here. Don't start a new thread just because you don't like the answers that you are getting on your original thread.

Drew

Smile Bro I'm already on that subject. They just wanted detailed information.

You will find the same on this thread. Best to revert back to the original thread and continue there.


“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
Exploring Recursive CTEs by Example Dwain Camps
Michael L John
Michael L John
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21500 Visits: 10427
xtrmus2319 - Thursday, January 25, 2018 9:27 AM
drew.allen - Thursday, January 25, 2018 9:24 AM
This is the same problem as here. Don't start a new thread just because you don't like the answers that you are getting on your original thread.

Drew

Smile Bro I'm already on that subject. They just wanted detailed information.

Well, if you want an answer, you need to provide the details.

For starters, your design is flawed. it appears, based upon the limited information you have provided, that this can be normalized into 2 separate tables. One for coin, the other for values and date.
Secondly, again, you are getting exactly what you are asking for in this query.

If you want to provide code to create the tables (DDL), some consumable data to fill those tables, and a sample of what you expect the output to be, then you will probably get some real answers.
If you do not know how to provide this information, then please read the links that are in multiple replies to your posts.


Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
drew.allen
drew.allen
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65379 Visits: 17349
xtrmus2319 - Thursday, January 25, 2018 9:27 AM
drew.allen - Thursday, January 25, 2018 9:24 AM
This is the same problem as here. Don't start a new thread just because you don't like the answers that you are getting on your original thread.

Drew

Smile Bro I'm already on that subject. They just wanted detailed information.

The detailed information should have been provided in the original thread instead of starting a new thread. Starting a new thread fragments the discussion.

Drew


J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
ZZartin
ZZartin
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26030 Visits: 17450
Before getting into any of the deeper issues with this, you still haven't addressed the cartesian join which was brought up multi times in the last thread you started is a basic function of SQL queries and explains what you are seeing in #6.

http://lmgtfy.com/?q=cartesian+join
HappyGeek
HappyGeek
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15007 Visits: 7336

drew.allen - Thursday, January 25, 2018 9:33 AM
xtrmus2319 - Thursday, January 25, 2018 9:27 AM
drew.allen - Thursday, January 25, 2018 9:24 AM
This is the same problem as here. Don't start a new thread just because you don't like the answers that you are getting on your original thread.

Drew

Smile Bro I'm already on that subject. They just wanted detailed information.

The detailed information should have been provided in the original thread instead of starting a new thread. Starting a new thread fragments the discussion.

Drew


Bit of déjà vu here it seems. As Drew said stick to one thread.



...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search