Click here to Skip to main content
15,606,811 members
Home / Discussions / Database
   

Database

 
PinnedHOW TO ANSWER A QUESTION PinPopular
Chris Maunder16-Jul-09 3:10
cofounderChris Maunder16-Jul-09 3:10 
PinnedHow to get an answer to your question PinPopular
Chris Maunder10-Nov-05 16:30
cofounderChris Maunder10-Nov-05 16:30 
QuestionSQL where clause variability up against coding standards Pin
Brian L Hughes10-Mar-23 15:59
Brian L Hughes10-Mar-23 15:59 
AnswerRe: SQL where clause variability up against coding standards Pin
Dave Kreskowiak11-Mar-23 5:37
mveDave Kreskowiak11-Mar-23 5:37 
AnswerRe: SQL where clause variability up against coding standards Pin
Eddy Vluggen11-Mar-23 9:00
professionalEddy Vluggen11-Mar-23 9:00 
AnswerRe: SQL where clause variability up against coding standards Pin
jschell13-Mar-23 6:56
jschell13-Mar-23 6:56 
QuestionCode: 1292 SQL State: 22007 --- Incorrect datetime value Pin
Siavash.BRY6-Mar-23 20:56
Siavash.BRY6-Mar-23 20:56 
AnswerRe: Code: 1292 SQL State: 22007 --- Incorrect datetime value Pin
Richard MacCutchan6-Mar-23 21:26
mveRichard MacCutchan6-Mar-23 21:26 
GeneralRe: Code: 1292 SQL State: 22007 --- Incorrect datetime value Pin
Siavash.BRY6-Mar-23 21:52
Siavash.BRY6-Mar-23 21:52 
GeneralRe: Code: 1292 SQL State: 22007 --- Incorrect datetime value PinPopular
Richard MacCutchan6-Mar-23 22:09
mveRichard MacCutchan6-Mar-23 22:09 
AnswerRe: Code: 1292 SQL State: 22007 --- Incorrect datetime value Pin
jschell8-Mar-23 7:03
jschell8-Mar-23 7:03 
AnswerRe: Code: 1292 SQL State: 22007 --- Incorrect datetime value Pin
RedDk8-Mar-23 8:30
RedDk8-Mar-23 8:30 
QuestionEF Core 6 Question Pin
Kevin Marois1-Feb-23 16:24
professionalKevin Marois1-Feb-23 16:24 
AnswerRe: EF Core 6 Question Pin
Dave Kreskowiak2-Feb-23 2:34
mveDave Kreskowiak2-Feb-23 2:34 
GeneralRe: EF Core 6 Question Pin
Kevin Marois2-Feb-23 5:45
professionalKevin Marois2-Feb-23 5:45 
Questionexecute an update  using --> "Exec sp_executesql @Sql1" Pin
Member 115338921-Feb-23 4:55
Member 115338921-Feb-23 4:55 
AnswerRe: execute an update  using --> "Exec sp_executesql @Sql1" Pin
Victor Nijegorodov1-Feb-23 5:48
Victor Nijegorodov1-Feb-23 5:48 
AnswerRe: execute an update  using --> "Exec sp_executesql @Sql1" Pin
David Mujica2-Feb-23 4:31
David Mujica2-Feb-23 4:31 
AnswerRe: execute an update  using --> "Exec sp_executesql @Sql1" Pin
Eddy Vluggen10-Mar-23 12:17
professionalEddy Vluggen10-Mar-23 12:17 
QuestionQuestion On SQL Query Result Pin
crmfghtr20-Jan-23 5:13
crmfghtr20-Jan-23 5:13 
I Have 3 Tables Orders, OrderItems, OrderPymts. For this query, I only need records from the Orders and OrderPymts Tables.
The Order Table Contains Fields: OrderId, ClientId, SubTotal, Tax, OrderTotal
The OrderPymts Table Contains Fields: PaymentId, OrderId, PaymentDate, Terms, PrevBal, Payment, Balance.
I'm trying to query records only on the Last PaymentDate for each Order (To show the Outstanding Balance of Each Order).
I tried This:
SQL
SELECT Orders.OrderId, Orders.ClientId, Orders.OrderTotal, OrderPymts.Terms, (SELECT MAX(OrderPymts.PaymentDate)) AS PaymentDate, OrderPymts.PrevBal, OrderPymts.Payment, OrderPymts.Balance 
					FROM OrderPymts
                    JOIN Orders ON OrderPymts.OrderId = Orders.OrderId 
                    WHERE (Balance >= 0.01)
					ORDER BY Orders.OrderId, PaymentDate

To just get the last payment but it still returns all the payments.
Example Result of My query:
OrderId |ClientId |OrderTotal|Terms |PaymentDate|PrevBal|Payment|Balance|
1001 | 1 | 500 |Credit| 1/1/2023 | 500 | 100 | 400 |
1002 | 2 | 800 |Cash | 1/9/2023 | 800 | 200 | 600 |
1002 | 2 | 800 |Cash | 1/11/2023 | 600 | 100 | 500 |

Above is an example of the data my query is showing. I only want to get the bolded records in the query. But my code above is pulling all the records with a balance greater than 0.01. As you can see, I tried getting the Max date but that didn't work. I also tried Group By but I get the same results as above. I need someone smarter than me, to help me with this.
Thanks for your help.

modified 20-Jan-23 11:34am.

AnswerRe: Question On SQL Query Result Pin
Richard Deeming20-Jan-23 5:33
mveRichard Deeming20-Jan-23 5:33 
PraiseRe: Question On SQL Query Result Pin
crmfghtr20-Jan-23 5:41
crmfghtr20-Jan-23 5:41 
AnswerRe: Question On SQL Query Result Pin
Mycroft Holmes20-Jan-23 11:34
professionalMycroft Holmes20-Jan-23 11:34 
GeneralRe: Question On SQL Query Result Pin
crmfghtr20-Jan-23 12:41
crmfghtr20-Jan-23 12:41 
QuestionEF Core 6 Exclude Columns Pin
Kevin Marois19-Dec-22 8:47
professionalKevin Marois19-Dec-22 8:47 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

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