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
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 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 (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)SSC Guru (85K reputation)

Group: General Forum Members
Points: 85323 Visits: 18118
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-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39457 Visits: 14316
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
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 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 (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)SSC Guru (103K reputation)

Group: General Forum Members
Points: 103134 Visits: 20820
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
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12970 Visits: 9099
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-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39457 Visits: 14316
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
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13907 Visits: 14544
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
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7573 Visits: 5023

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