Selecting a single row in the same table/view if a query returns no results

0

I have the following view in my SQL database, which selects data from a Transaction table and a Customer table:

+-------+-----------+---------------------+--------+

| RowNo |   Name    |        Date         | Amount |

+-------+-----------+---------------------+--------+

|     1 | Customer1 | 2018-11-10 01:00:00 | 55.49  |

|     2 | Customer2 | 2018-11-10 02:00:00 | 58.15  |

|     3 | Customer3 | 2018-11-10 03:00:00 | 79.15  |

|     4 | Customer1 | 2018-11-11 04:00:00 | 41.89  |

|     5 | Customer2 | 2018-11-11 05:00:00 | 5.15   |

|     6 | Customer3 | 2018-11-11 06:00:00 | 35.17  |

|     7 | Customer1 | 2018-11-12 07:00:00 | 43.78  |

|     8 | Customer1 | 2018-11-12 08:00:00 | 93.78  |

|     9 | Customer2 | 2018-11-12 09:00:00 | 80.74  |

+-------+-----------+---------------------+--------+

I need an SQL query that will return all a customer's transactions for a given day (easy enough), but then if a customer had no transactions on the given day, the query must return the customer's most recent transaction.

Edit:

The view is as follows:

Create view vwReport as

Select c.Name, t.Date, t.Amount 

from Transaction t

inner join Customer c on c.Id = t.CustomerId

And then to get the data I just do a select from the view:

Select * from 

vwReport r

where r.Date between '2018-11-10 00:00:00' and '2018-11-11 00:00:00'

So, to clarify, I need one query that returns all the customer transactions for a day, and included in that results set is the last transaction of any customers who don't have a transaction on that day. So, in the table above, running the query for 2018-11-12, should return row 7, 8 and 9, as well as row 6 for Customer3 that did not have a transaction on the 12th.

sql sql-server datetime

share|improve this question

edited Nov 20 '18 at 10:14

Salman A

175k66336424

asked Nov 19 '18 at 12:51

staterium

1,47021830

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Please tag your question with the database you are using.
  – Gordon Linoff
  Nov 19 '18 at 12:52
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  It'd be great to see your existing query so that it's easier to understand how to modify it
  – Martin Parkin
  Nov 19 '18 at 12:52
  

  
  
  
  

add a comment | 

0

I have the following view in my SQL database, which selects data from a Transaction table and a Customer table:

+-------+-----------+---------------------+--------+

| RowNo |   Name    |        Date         | Amount |

+-------+-----------+---------------------+--------+

|     1 | Customer1 | 2018-11-10 01:00:00 | 55.49  |

|     2 | Customer2 | 2018-11-10 02:00:00 | 58.15  |

|     3 | Customer3 | 2018-11-10 03:00:00 | 79.15  |

|     4 | Customer1 | 2018-11-11 04:00:00 | 41.89  |

|     5 | Customer2 | 2018-11-11 05:00:00 | 5.15   |

|     6 | Customer3 | 2018-11-11 06:00:00 | 35.17  |

|     7 | Customer1 | 2018-11-12 07:00:00 | 43.78  |

|     8 | Customer1 | 2018-11-12 08:00:00 | 93.78  |

|     9 | Customer2 | 2018-11-12 09:00:00 | 80.74  |

+-------+-----------+---------------------+--------+

I need an SQL query that will return all a customer's transactions for a given day (easy enough), but then if a customer had no transactions on the given day, the query must return the customer's most recent transaction.

Edit:

The view is as follows:

Create view vwReport as

Select c.Name, t.Date, t.Amount 

from Transaction t

inner join Customer c on c.Id = t.CustomerId

And then to get the data I just do a select from the view:

Select * from 

vwReport r

where r.Date between '2018-11-10 00:00:00' and '2018-11-11 00:00:00'

So, to clarify, I need one query that returns all the customer transactions for a day, and included in that results set is the last transaction of any customers who don't have a transaction on that day. So, in the table above, running the query for 2018-11-12, should return row 7, 8 and 9, as well as row 6 for Customer3 that did not have a transaction on the 12th.

sql sql-server datetime

share|improve this question

edited Nov 20 '18 at 10:14

Salman A

175k66336424

asked Nov 19 '18 at 12:51

staterium

1,47021830

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Please tag your question with the database you are using.
  – Gordon Linoff
  Nov 19 '18 at 12:52
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  It'd be great to see your existing query so that it's easier to understand how to modify it
  – Martin Parkin
  Nov 19 '18 at 12:52
  

  
  
  
  

add a comment | 

0

0

0

I have the following view in my SQL database, which selects data from a Transaction table and a Customer table:

+-------+-----------+---------------------+--------+

| RowNo |   Name    |        Date         | Amount |

+-------+-----------+---------------------+--------+

|     1 | Customer1 | 2018-11-10 01:00:00 | 55.49  |

|     2 | Customer2 | 2018-11-10 02:00:00 | 58.15  |

|     3 | Customer3 | 2018-11-10 03:00:00 | 79.15  |

|     4 | Customer1 | 2018-11-11 04:00:00 | 41.89  |

|     5 | Customer2 | 2018-11-11 05:00:00 | 5.15   |

|     6 | Customer3 | 2018-11-11 06:00:00 | 35.17  |

|     7 | Customer1 | 2018-11-12 07:00:00 | 43.78  |

|     8 | Customer1 | 2018-11-12 08:00:00 | 93.78  |

|     9 | Customer2 | 2018-11-12 09:00:00 | 80.74  |

+-------+-----------+---------------------+--------+

I need an SQL query that will return all a customer's transactions for a given day (easy enough), but then if a customer had no transactions on the given day, the query must return the customer's most recent transaction.

Edit:

The view is as follows:

Create view vwReport as

Select c.Name, t.Date, t.Amount 

from Transaction t

inner join Customer c on c.Id = t.CustomerId

And then to get the data I just do a select from the view:

Select * from 

vwReport r

where r.Date between '2018-11-10 00:00:00' and '2018-11-11 00:00:00'

So, to clarify, I need one query that returns all the customer transactions for a day, and included in that results set is the last transaction of any customers who don't have a transaction on that day. So, in the table above, running the query for 2018-11-12, should return row 7, 8 and 9, as well as row 6 for Customer3 that did not have a transaction on the 12th.

sql sql-server datetime

share|improve this question

edited Nov 20 '18 at 10:14

Salman A

175k66336424

asked Nov 19 '18 at 12:51

staterium

1,47021830

I have the following view in my SQL database, which selects data from a Transaction table and a Customer table:

+-------+-----------+---------------------+--------+

| RowNo |   Name    |        Date         | Amount |

+-------+-----------+---------------------+--------+

|     1 | Customer1 | 2018-11-10 01:00:00 | 55.49  |

|     2 | Customer2 | 2018-11-10 02:00:00 | 58.15  |

|     3 | Customer3 | 2018-11-10 03:00:00 | 79.15  |

|     4 | Customer1 | 2018-11-11 04:00:00 | 41.89  |

|     5 | Customer2 | 2018-11-11 05:00:00 | 5.15   |

|     6 | Customer3 | 2018-11-11 06:00:00 | 35.17  |

|     7 | Customer1 | 2018-11-12 07:00:00 | 43.78  |

|     8 | Customer1 | 2018-11-12 08:00:00 | 93.78  |

|     9 | Customer2 | 2018-11-12 09:00:00 | 80.74  |

+-------+-----------+---------------------+--------+

I need an SQL query that will return all a customer's transactions for a given day (easy enough), but then if a customer had no transactions on the given day, the query must return the customer's most recent transaction.

Edit:

The view is as follows:

Create view vwReport as

Select c.Name, t.Date, t.Amount 

from Transaction t

inner join Customer c on c.Id = t.CustomerId

And then to get the data I just do a select from the view:

Select * from 

vwReport r

where r.Date between '2018-11-10 00:00:00' and '2018-11-11 00:00:00'

So, to clarify, I need one query that returns all the customer transactions for a day, and included in that results set is the last transaction of any customers who don't have a transaction on that day. So, in the table above, running the query for 2018-11-12, should return row 7, 8 and 9, as well as row 6 for Customer3 that did not have a transaction on the 12th.

sql sql-server datetime

sql sql-server datetime

share|improve this question

edited Nov 20 '18 at 10:14

Salman A

175k66336424

asked Nov 19 '18 at 12:51

staterium

1,47021830

share|improve this question

edited Nov 20 '18 at 10:14

Salman A

175k66336424

asked Nov 19 '18 at 12:51

staterium

1,47021830

share|improve this question

share|improve this question

edited Nov 20 '18 at 10:14

Salman A

175k66336424

edited Nov 20 '18 at 10:14

Salman A

175k66336424

edited Nov 20 '18 at 10:14

Salman A

175k66336424

175k66336424

asked Nov 19 '18 at 12:51

staterium

1,47021830

asked Nov 19 '18 at 12:51

staterium

1,47021830

asked Nov 19 '18 at 12:51

staterium

1,47021830

1,47021830

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Please tag your question with the database you are using.
  – Gordon Linoff
  Nov 19 '18 at 12:52
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  It'd be great to see your existing query so that it's easier to understand how to modify it
  – Martin Parkin
  Nov 19 '18 at 12:52
  

  
  
  
  

add a comment | 

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Please tag your question with the database you are using.
  – Gordon Linoff
  Nov 19 '18 at 12:52
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  It'd be great to see your existing query so that it's easier to understand how to modify it
  – Martin Parkin
  Nov 19 '18 at 12:52
  

  
  
  
  

Please tag your question with the database you are using.
– Gordon Linoff
Nov 19 '18 at 12:52

Please tag your question with the database you are using.
– Gordon Linoff
Nov 19 '18 at 12:52

1

1

It'd be great to see your existing query so that it's easier to understand how to modify it
– Martin Parkin
Nov 19 '18 at 12:52

It'd be great to see your existing query so that it's easier to understand how to modify it
– Martin Parkin
Nov 19 '18 at 12:52

add a comment | 

5 Answers
5

active

oldest

votes

2

Take your existing query and UNION ALL it with a "most recent transaction query" for everyone who doesn't have a transaction in that range.

with found as

( 

    select c.Id, c.Name, t.Date, t.Amount 

    from Transaction t

    inner join Customer c on c.Id = t.CustomerId

    where Date between '2018-11-10 00:00:00' and '2018-11-11 00:00:00' 

)

with unfound as

(

    select c.Id, c.Name, t.Date, t.Amount, RANK() OVER (PARTITION BY Name ORDER BY CAST(Date AS DATE) DESC) AS row

    from Transaction t

    inner join Customer c on c.Id = t.CustomerId

    WHERE Date < '2018-11-10 00:00:00'

)

select Name, Date, Amount 

from found

union all

select Name, Date, Amount 

from unfound

where Id not in ( select Id from found ) and row = 1

share|improve this answer

answered Nov 19 '18 at 14:14

Caleth

16.5k22138

add a comment | 

1

You're interested in selecting multiple rows with ties, you could use the RANK() function to find all rows ranked by date descending:

SELECT * FROM (

    SELECT *, RANK() OVER (PARTITION BY Name ORDER BY CAST(Date AS DATE) DESC) AS rn

    FROM txntbl

    WHERE CAST(Date AS DATE) <= '2018-11-12'

) AS x

WHERE rn = 1

Demo on DB Fiddle

share|improve this answer

edited Nov 19 '18 at 14:36

answered Nov 19 '18 at 13:05

Salman A

175k66336424

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks. This only returns one record per customer. If a customer has many transactions on a given day, it must return all of them. If a customer has no transactions, it must return just their most recent.
  – staterium
  Nov 19 '18 at 13:49
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Use rank() Instead of row_number() (I am assuming Date is DATE not DATETIME). Let me know if it works.
  – Salman A
  Nov 19 '18 at 13:50
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  It's actually a DATETIME, I've updated my question
  – staterium
  Nov 19 '18 at 13:56
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Just CAST( AS DATE) a couple of times. See revised answer.
  – Salman A
  Nov 19 '18 at 14:13
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  you have 2 problems. You should handle this via CTE and inserting each result set into temp table or variable and joining the 2 result sets back via a Union. Depending on the size of your data you may have to use index on your temp table(s).
  – junketsu
  Nov 19 '18 at 14:32
  

  
  
  
  

add a comment | 

0

You can use a correlated subquery:

select t.*

from transactions t

where t.date = (select max(t2.date)

                from transactions t2

                where t2.name = t.name and

                      t2.date <= @date

               );

Note: This only returns customers who had a transaction on or before the date in question.

share|improve this answer

answered Nov 19 '18 at 12:53

Gordon Linoff

758k35291399

add a comment | 

0

With the limited information available from the question, the following presents a solution using a join as opposed to a correlated subquery:

select t1.*

from

    vwReport t1 inner join

    (

        select t2.name, max(t2.date) as mdate

        from vwReport t2

        group by t2.name

    ) t3

    on t1.name = t3.name and t1.date = t3.mdate

where

    t1.date <= @date

share|improve this answer

edited Nov 19 '18 at 13:12

answered Nov 19 '18 at 12:59

Lee Mac

3,47031339

add a comment | 

0

Use UNION for the last date transactions only if there are no transactions for the given dates (BETWEEN '2018-11-10 00:00:00' AND '2018-11-11 00:00:00'):

SELECT * FROM vwReport r

WHERE (r.Date BETWEEN '2018-11-10 00:00:00' AND '2018-11-11 00:00:00')

AND (r.Name = @name)

UNION

SELECT * FROM vwReport r

WHERE (r.Date = (SELECT MAX(r.Date) FROM vwReport r WHERE r.Name = @name))

AND (r.Name = @name)

AND ((SELECT COUNT(*) FROM vwReport r 

    WHERE (r.Date BETWEEN '2018-11-10 00:00:00' AND '2018-11-11 00:00:00') 

    AND (r.Name = @name)) = 0)

share|improve this answer

answered Nov 19 '18 at 14:41

forpas

8,6141421

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53375050%2fselecting-a-single-row-in-the-same-table-view-if-a-query-returns-no-results%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

5 Answers
5

active

oldest

votes

5 Answers
5

active

oldest

votes

active

oldest

votes

active

oldest

votes

2

Take your existing query and UNION ALL it with a "most recent transaction query" for everyone who doesn't have a transaction in that range.

with found as

( 

    select c.Id, c.Name, t.Date, t.Amount 

    from Transaction t

    inner join Customer c on c.Id = t.CustomerId

    where Date between '2018-11-10 00:00:00' and '2018-11-11 00:00:00' 

)

with unfound as

(

    select c.Id, c.Name, t.Date, t.Amount, RANK() OVER (PARTITION BY Name ORDER BY CAST(Date AS DATE) DESC) AS row

    from Transaction t

    inner join Customer c on c.Id = t.CustomerId

    WHERE Date < '2018-11-10 00:00:00'

)

select Name, Date, Amount 

from found

union all

select Name, Date, Amount 

from unfound

where Id not in ( select Id from found ) and row = 1

share|improve this answer

answered Nov 19 '18 at 14:14

Caleth

16.5k22138

add a comment | 

2

Take your existing query and UNION ALL it with a "most recent transaction query" for everyone who doesn't have a transaction in that range.

with found as

( 

    select c.Id, c.Name, t.Date, t.Amount 

    from Transaction t

    inner join Customer c on c.Id = t.CustomerId

    where Date between '2018-11-10 00:00:00' and '2018-11-11 00:00:00' 

)

with unfound as

(

    select c.Id, c.Name, t.Date, t.Amount, RANK() OVER (PARTITION BY Name ORDER BY CAST(Date AS DATE) DESC) AS row

    from Transaction t

    inner join Customer c on c.Id = t.CustomerId

    WHERE Date < '2018-11-10 00:00:00'

)

select Name, Date, Amount 

from found

union all

select Name, Date, Amount 

from unfound

where Id not in ( select Id from found ) and row = 1

share|improve this answer

answered Nov 19 '18 at 14:14

Caleth

16.5k22138

add a comment | 

2

2

2

Take your existing query and UNION ALL it with a "most recent transaction query" for everyone who doesn't have a transaction in that range.

with found as

( 

    select c.Id, c.Name, t.Date, t.Amount 

    from Transaction t

    inner join Customer c on c.Id = t.CustomerId

    where Date between '2018-11-10 00:00:00' and '2018-11-11 00:00:00' 

)

with unfound as

(

    select c.Id, c.Name, t.Date, t.Amount, RANK() OVER (PARTITION BY Name ORDER BY CAST(Date AS DATE) DESC) AS row

    from Transaction t

    inner join Customer c on c.Id = t.CustomerId

    WHERE Date < '2018-11-10 00:00:00'

)

select Name, Date, Amount 

from found

union all

select Name, Date, Amount 

from unfound

where Id not in ( select Id from found ) and row = 1

share|improve this answer

answered Nov 19 '18 at 14:14

Caleth

16.5k22138

Take your existing query and UNION ALL it with a "most recent transaction query" for everyone who doesn't have a transaction in that range.

with found as

( 

    select c.Id, c.Name, t.Date, t.Amount 

    from Transaction t

    inner join Customer c on c.Id = t.CustomerId

    where Date between '2018-11-10 00:00:00' and '2018-11-11 00:00:00' 

)

with unfound as

(

    select c.Id, c.Name, t.Date, t.Amount, RANK() OVER (PARTITION BY Name ORDER BY CAST(Date AS DATE) DESC) AS row

    from Transaction t

    inner join Customer c on c.Id = t.CustomerId

    WHERE Date < '2018-11-10 00:00:00'

)

select Name, Date, Amount 

from found

union all

select Name, Date, Amount 

from unfound

where Id not in ( select Id from found ) and row = 1

share|improve this answer

answered Nov 19 '18 at 14:14

Caleth

16.5k22138

share|improve this answer

share|improve this answer

answered Nov 19 '18 at 14:14

Caleth

16.5k22138

answered Nov 19 '18 at 14:14

Caleth

16.5k22138

answered Nov 19 '18 at 14:14

Caleth

16.5k22138

16.5k22138

add a comment | 

add a comment | 

1

You're interested in selecting multiple rows with ties, you could use the RANK() function to find all rows ranked by date descending:

SELECT * FROM (

    SELECT *, RANK() OVER (PARTITION BY Name ORDER BY CAST(Date AS DATE) DESC) AS rn

    FROM txntbl

    WHERE CAST(Date AS DATE) <= '2018-11-12'

) AS x

WHERE rn = 1

Demo on DB Fiddle

share|improve this answer

edited Nov 19 '18 at 14:36

answered Nov 19 '18 at 13:05

Salman A

175k66336424

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks. This only returns one record per customer. If a customer has many transactions on a given day, it must return all of them. If a customer has no transactions, it must return just their most recent.
  – staterium
  Nov 19 '18 at 13:49
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Use rank() Instead of row_number() (I am assuming Date is DATE not DATETIME). Let me know if it works.
  – Salman A
  Nov 19 '18 at 13:50
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  It's actually a DATETIME, I've updated my question
  – staterium
  Nov 19 '18 at 13:56
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Just CAST( AS DATE) a couple of times. See revised answer.
  – Salman A
  Nov 19 '18 at 14:13
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  you have 2 problems. You should handle this via CTE and inserting each result set into temp table or variable and joining the 2 result sets back via a Union. Depending on the size of your data you may have to use index on your temp table(s).
  – junketsu
  Nov 19 '18 at 14:32
  

  
  
  
  

add a comment | 

1

You're interested in selecting multiple rows with ties, you could use the RANK() function to find all rows ranked by date descending:

SELECT * FROM (

    SELECT *, RANK() OVER (PARTITION BY Name ORDER BY CAST(Date AS DATE) DESC) AS rn

    FROM txntbl

    WHERE CAST(Date AS DATE) <= '2018-11-12'

) AS x

WHERE rn = 1

Demo on DB Fiddle

share|improve this answer

edited Nov 19 '18 at 14:36

answered Nov 19 '18 at 13:05

Salman A

175k66336424

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks. This only returns one record per customer. If a customer has many transactions on a given day, it must return all of them. If a customer has no transactions, it must return just their most recent.
  – staterium
  Nov 19 '18 at 13:49
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Use rank() Instead of row_number() (I am assuming Date is DATE not DATETIME). Let me know if it works.
  – Salman A
  Nov 19 '18 at 13:50
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  It's actually a DATETIME, I've updated my question
  – staterium
  Nov 19 '18 at 13:56
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Just CAST( AS DATE) a couple of times. See revised answer.
  – Salman A
  Nov 19 '18 at 14:13
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  you have 2 problems. You should handle this via CTE and inserting each result set into temp table or variable and joining the 2 result sets back via a Union. Depending on the size of your data you may have to use index on your temp table(s).
  – junketsu
  Nov 19 '18 at 14:32
  

  
  
  
  

add a comment | 

1

1

1

You're interested in selecting multiple rows with ties, you could use the RANK() function to find all rows ranked by date descending:

SELECT * FROM (

    SELECT *, RANK() OVER (PARTITION BY Name ORDER BY CAST(Date AS DATE) DESC) AS rn

    FROM txntbl

    WHERE CAST(Date AS DATE) <= '2018-11-12'

) AS x

WHERE rn = 1

Demo on DB Fiddle

share|improve this answer

edited Nov 19 '18 at 14:36

answered Nov 19 '18 at 13:05

Salman A

175k66336424

You're interested in selecting multiple rows with ties, you could use the RANK() function to find all rows ranked by date descending:

SELECT * FROM (

    SELECT *, RANK() OVER (PARTITION BY Name ORDER BY CAST(Date AS DATE) DESC) AS rn

    FROM txntbl

    WHERE CAST(Date AS DATE) <= '2018-11-12'

) AS x

WHERE rn = 1

Demo on DB Fiddle

share|improve this answer

edited Nov 19 '18 at 14:36

answered Nov 19 '18 at 13:05

Salman A

175k66336424

share|improve this answer

share|improve this answer

edited Nov 19 '18 at 14:36

edited Nov 19 '18 at 14:36

edited Nov 19 '18 at 14:36

answered Nov 19 '18 at 13:05

Salman A

175k66336424

answered Nov 19 '18 at 13:05

Salman A

175k66336424

answered Nov 19 '18 at 13:05

Salman A

175k66336424

175k66336424

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks. This only returns one record per customer. If a customer has many transactions on a given day, it must return all of them. If a customer has no transactions, it must return just their most recent.
  – staterium
  Nov 19 '18 at 13:49
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Use rank() Instead of row_number() (I am assuming Date is DATE not DATETIME). Let me know if it works.
  – Salman A
  Nov 19 '18 at 13:50
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  It's actually a DATETIME, I've updated my question
  – staterium
  Nov 19 '18 at 13:56
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Just CAST( AS DATE) a couple of times. See revised answer.
  – Salman A
  Nov 19 '18 at 14:13
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  you have 2 problems. You should handle this via CTE and inserting each result set into temp table or variable and joining the 2 result sets back via a Union. Depending on the size of your data you may have to use index on your temp table(s).
  – junketsu
  Nov 19 '18 at 14:32
  

  
  
  
  

add a comment | 

• 
  
  
  

  
  

  
  
  
  
  
  

  
  Thanks. This only returns one record per customer. If a customer has many transactions on a given day, it must return all of them. If a customer has no transactions, it must return just their most recent.
  – staterium
  Nov 19 '18 at 13:49
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Use rank() Instead of row_number() (I am assuming Date is DATE not DATETIME). Let me know if it works.
  – Salman A
  Nov 19 '18 at 13:50
  
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  It's actually a DATETIME, I've updated my question
  – staterium
  Nov 19 '18 at 13:56
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  Just CAST( AS DATE) a couple of times. See revised answer.
  – Salman A
  Nov 19 '18 at 14:13
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  you have 2 problems. You should handle this via CTE and inserting each result set into temp table or variable and joining the 2 result sets back via a Union. Depending on the size of your data you may have to use index on your temp table(s).
  – junketsu
  Nov 19 '18 at 14:32
  

  
  
  
  

Thanks. This only returns one record per customer. If a customer has many transactions on a given day, it must return all of them. If a customer has no transactions, it must return just their most recent.
– staterium
Nov 19 '18 at 13:49

Thanks. This only returns one record per customer. If a customer has many transactions on a given day, it must return all of them. If a customer has no transactions, it must return just their most recent.
– staterium
Nov 19 '18 at 13:49

Use rank() Instead of row_number() (I am assuming Date is DATE not DATETIME). Let me know if it works.
– Salman A
Nov 19 '18 at 13:50

Use rank() Instead of row_number() (I am assuming Date is DATE not DATETIME). Let me know if it works.
– Salman A
Nov 19 '18 at 13:50

It's actually a DATETIME, I've updated my question
– staterium
Nov 19 '18 at 13:56

It's actually a DATETIME, I've updated my question
– staterium
Nov 19 '18 at 13:56

Just CAST( AS DATE) a couple of times. See revised answer.
– Salman A
Nov 19 '18 at 14:13

Just CAST( AS DATE) a couple of times. See revised answer.
– Salman A
Nov 19 '18 at 14:13

you have 2 problems. You should handle this via CTE and inserting each result set into temp table or variable and joining the 2 result sets back via a Union. Depending on the size of your data you may have to use index on your temp table(s).
– junketsu
Nov 19 '18 at 14:32

you have 2 problems. You should handle this via CTE and inserting each result set into temp table or variable and joining the 2 result sets back via a Union. Depending on the size of your data you may have to use index on your temp table(s).
– junketsu
Nov 19 '18 at 14:32

add a comment | 

0

You can use a correlated subquery:

select t.*

from transactions t

where t.date = (select max(t2.date)

                from transactions t2

                where t2.name = t.name and

                      t2.date <= @date

               );

Note: This only returns customers who had a transaction on or before the date in question.

share|improve this answer

answered Nov 19 '18 at 12:53

Gordon Linoff

758k35291399

add a comment | 

0

You can use a correlated subquery:

select t.*

from transactions t

where t.date = (select max(t2.date)

                from transactions t2

                where t2.name = t.name and

                      t2.date <= @date

               );

Note: This only returns customers who had a transaction on or before the date in question.

share|improve this answer

answered Nov 19 '18 at 12:53

Gordon Linoff

758k35291399

add a comment | 

0

0

0

You can use a correlated subquery:

select t.*

from transactions t

where t.date = (select max(t2.date)

                from transactions t2

                where t2.name = t.name and

                      t2.date <= @date

               );

Note: This only returns customers who had a transaction on or before the date in question.

share|improve this answer

answered Nov 19 '18 at 12:53

Gordon Linoff

758k35291399

You can use a correlated subquery:

select t.*

from transactions t

where t.date = (select max(t2.date)

                from transactions t2

                where t2.name = t.name and

                      t2.date <= @date

               );

Note: This only returns customers who had a transaction on or before the date in question.

share|improve this answer

answered Nov 19 '18 at 12:53

Gordon Linoff

758k35291399

share|improve this answer

share|improve this answer

answered Nov 19 '18 at 12:53

Gordon Linoff

758k35291399

answered Nov 19 '18 at 12:53

Gordon Linoff

758k35291399

answered Nov 19 '18 at 12:53

Gordon Linoff

758k35291399

758k35291399

add a comment | 

add a comment | 

0

With the limited information available from the question, the following presents a solution using a join as opposed to a correlated subquery:

select t1.*

from

    vwReport t1 inner join

    (

        select t2.name, max(t2.date) as mdate

        from vwReport t2

        group by t2.name

    ) t3

    on t1.name = t3.name and t1.date = t3.mdate

where

    t1.date <= @date

share|improve this answer

edited Nov 19 '18 at 13:12

answered Nov 19 '18 at 12:59

Lee Mac

3,47031339

add a comment | 

0

With the limited information available from the question, the following presents a solution using a join as opposed to a correlated subquery:

select t1.*

from

    vwReport t1 inner join

    (

        select t2.name, max(t2.date) as mdate

        from vwReport t2

        group by t2.name

    ) t3

    on t1.name = t3.name and t1.date = t3.mdate

where

    t1.date <= @date

share|improve this answer

edited Nov 19 '18 at 13:12

answered Nov 19 '18 at 12:59

Lee Mac

3,47031339

add a comment | 

0

0

0

With the limited information available from the question, the following presents a solution using a join as opposed to a correlated subquery:

select t1.*

from

    vwReport t1 inner join

    (

        select t2.name, max(t2.date) as mdate

        from vwReport t2

        group by t2.name

    ) t3

    on t1.name = t3.name and t1.date = t3.mdate

where

    t1.date <= @date

share|improve this answer

edited Nov 19 '18 at 13:12

answered Nov 19 '18 at 12:59

Lee Mac

3,47031339

With the limited information available from the question, the following presents a solution using a join as opposed to a correlated subquery:

select t1.*

from

    vwReport t1 inner join

    (

        select t2.name, max(t2.date) as mdate

        from vwReport t2

        group by t2.name

    ) t3

    on t1.name = t3.name and t1.date = t3.mdate

where

    t1.date <= @date

share|improve this answer

edited Nov 19 '18 at 13:12

answered Nov 19 '18 at 12:59

Lee Mac

3,47031339

share|improve this answer

share|improve this answer

edited Nov 19 '18 at 13:12

edited Nov 19 '18 at 13:12

edited Nov 19 '18 at 13:12

answered Nov 19 '18 at 12:59

Lee Mac

3,47031339

answered Nov 19 '18 at 12:59

Lee Mac

3,47031339

answered Nov 19 '18 at 12:59

Lee Mac

3,47031339

3,47031339

add a comment | 

add a comment | 

0

Use UNION for the last date transactions only if there are no transactions for the given dates (BETWEEN '2018-11-10 00:00:00' AND '2018-11-11 00:00:00'):

SELECT * FROM vwReport r

WHERE (r.Date BETWEEN '2018-11-10 00:00:00' AND '2018-11-11 00:00:00')

AND (r.Name = @name)

UNION

SELECT * FROM vwReport r

WHERE (r.Date = (SELECT MAX(r.Date) FROM vwReport r WHERE r.Name = @name))

AND (r.Name = @name)

AND ((SELECT COUNT(*) FROM vwReport r 

    WHERE (r.Date BETWEEN '2018-11-10 00:00:00' AND '2018-11-11 00:00:00') 

    AND (r.Name = @name)) = 0)

share|improve this answer

answered Nov 19 '18 at 14:41

forpas

8,6141421

add a comment | 

0

Use UNION for the last date transactions only if there are no transactions for the given dates (BETWEEN '2018-11-10 00:00:00' AND '2018-11-11 00:00:00'):

SELECT * FROM vwReport r

WHERE (r.Date BETWEEN '2018-11-10 00:00:00' AND '2018-11-11 00:00:00')

AND (r.Name = @name)

UNION

SELECT * FROM vwReport r

WHERE (r.Date = (SELECT MAX(r.Date) FROM vwReport r WHERE r.Name = @name))

AND (r.Name = @name)

AND ((SELECT COUNT(*) FROM vwReport r 

    WHERE (r.Date BETWEEN '2018-11-10 00:00:00' AND '2018-11-11 00:00:00') 

    AND (r.Name = @name)) = 0)

share|improve this answer

answered Nov 19 '18 at 14:41

forpas

8,6141421

add a comment | 

0

0

0

Use UNION for the last date transactions only if there are no transactions for the given dates (BETWEEN '2018-11-10 00:00:00' AND '2018-11-11 00:00:00'):

SELECT * FROM vwReport r

WHERE (r.Date BETWEEN '2018-11-10 00:00:00' AND '2018-11-11 00:00:00')

AND (r.Name = @name)

UNION

SELECT * FROM vwReport r

WHERE (r.Date = (SELECT MAX(r.Date) FROM vwReport r WHERE r.Name = @name))

AND (r.Name = @name)

AND ((SELECT COUNT(*) FROM vwReport r 

    WHERE (r.Date BETWEEN '2018-11-10 00:00:00' AND '2018-11-11 00:00:00') 

    AND (r.Name = @name)) = 0)

share|improve this answer

answered Nov 19 '18 at 14:41

forpas

8,6141421

Use UNION for the last date transactions only if there are no transactions for the given dates (BETWEEN '2018-11-10 00:00:00' AND '2018-11-11 00:00:00'):

SELECT * FROM vwReport r

WHERE (r.Date BETWEEN '2018-11-10 00:00:00' AND '2018-11-11 00:00:00')

AND (r.Name = @name)

UNION

SELECT * FROM vwReport r

WHERE (r.Date = (SELECT MAX(r.Date) FROM vwReport r WHERE r.Name = @name))

AND (r.Name = @name)

AND ((SELECT COUNT(*) FROM vwReport r 

    WHERE (r.Date BETWEEN '2018-11-10 00:00:00' AND '2018-11-11 00:00:00') 

    AND (r.Name = @name)) = 0)

share|improve this answer

answered Nov 19 '18 at 14:41

forpas

8,6141421

share|improve this answer

share|improve this answer

answered Nov 19 '18 at 14:41

forpas

8,6141421

answered Nov 19 '18 at 14:41

forpas

8,6141421

answered Nov 19 '18 at 14:41

forpas

8,6141421

8,6141421

add a comment | 

add a comment | 

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

• Please be sure to answer the question. Provide details and share your research!

But avoid …

• Asking for help, clarification, or responding to other answers.


• Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53375050%2fselecting-a-single-row-in-the-same-table-view-if-a-query-returns-no-results%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Name

Name

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

Email

Required, but never shown

This page is only for reference, If you need detailed information, please check here