SQL query min(date) from one column and max(amount) from another

Question

I have a table and it looks like

I need to create a table that consists of the following columns

Table includes unique ID, min date when the number of sms was max and max date when the number of sms was min, including the numbers of sms for both cases

I've tried several queries, but I feel that the problem is not in finding min(sms) in a column "sms" in general but with a condition max(date) for the column "date"

So if I have to find maximum amount of sms sent on minimum data, I should pick the minimum data when the number was maximum. Now I can only find the number of sms sent on min data.

Will be glad for any explanation

P.S.: I'm using MS SQL 2014

Here is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ And you should decide which DBMS you are using. mysql <> sql server. — Sean Lange, Aug 08 '16 at 20:17
http://stackoverflow.com/users/3813116/sean-lange Thanks! Tables the app create can't be pasted here. Uploaded screenshots — Yevgenyia Yusova, Aug 08 '16 at 21:35
It's really note terribly easy to try to understand what you're looking for without having some data structures & a clearer picture of what you're actually looking for. Popping out images into new tabs doesn't help so much as just typing in the data would. — David T. Macknet, Aug 08 '16 at 21:39
This doesn't make any sense at all. Where are those values coming from? For userID 1 where does the 8/10/2010 come from? It isn't in the data. I can't speak for everyone else but I am pretty decent at sql server. However, I am really awful as a mind reader. — Sean Lange, Aug 09 '16 at 12:46
@SeanLange there is a model of data that looks like create table dbo.statistics ( login varchar(64) , send_date datetime , sms_cnt int ) I gave an example of data and how it may look — Yevgenyia Yusova, Aug 09 '16 at 20:33
That's nice. Can you answer the questions about how you come up with the output? It would appear to be random values as there is no rhyme or reason as to how those values are determined. And as has been said before, you should post ddl and sample data in a consumable format. Pictures do not help us with test data to work with. Sure I could type it in manually but I would rather spend my time working on your problem instead of setting it up. Create a sqlfiddle or find some way to get us the basic table with data. And a clear explanation of the output. — Sean Lange, Aug 09 '16 at 21:01

score 0 · Answer 1 · answered Aug 09 '16 at 02:22

That should work or at least starting point to tune:

SELECT * INTO Table1 FROM (VALUES
(1,'1/1/2015',10),
(1,'2/1/2015',10),
(1,'3/1/2015',20),
(1,'4/1/2015',20),
(2,'5/1/2015',30),
(2,'6/1/2015',30),
(2,'7/1/2015',40),
(2,'8/1/2015',40)
) as x ([User_ID], [Date], [SMS])
GO
;WITH MX as (
    SELECT t1.[User_ID], MIN(t1.[Date]) as [Date], t1.[SMS]
    FROM Table1 as t1
    WHERE t1.[SMS] = (SELECT MAX(i.[SMS]) as [SMS] FROM Table1 as i WHERE i.[User_ID] = t1.[User_ID] ) 
    GROUP BY t1.[User_ID], t1.[SMS]
), MI as (
    SELECT t1.[User_ID], MAX(t1.[Date]) as [Date], t1.[SMS]
    FROM Table1 as t1
    WHERE t1.[SMS] = (SELECT MIN(i.[SMS]) as [SMS] FROM Table1 as i WHERE i.[User_ID] = t1.[User_ID] ) 
    GROUP BY t1.[User_ID], t1.[SMS]
)
SELECT MX.[User_ID], MX.[Date] as Min_Date, MX.[SMS] as Max_SMS, MI.[Date] as Max_Date, MI.[SMS] as Min_SMS
FROM MX INNER JOIN MI ON MX.[User_ID] = MI.[User_ID];
GO

SQL query min(date) from one column and max(amount) from another

1 Answers1