sql query

i have a sheet , where , i want to get sum of the amount , which has following condition

if date of column 1 falls between date of column 2 and +1 of date of column 2.

Data-of-Test-Query-22.xlsx
BeyondBGCMAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
PaulConnect With a Mentor Commented:
one very small but vital component of your question appears to be missing I think, the word: "year"

>>i want to get sum of the amount ...
>>if date of column 1 falls between date of column 2 and +1 year of date of column 2.

SELECT SUM(paidAmount) TotalPaidAmount
FROM TableName
WHERE CPD >= PSD and CPD < dateadd(year,1,PSD)


or... if needing this is a more elaborate query, perhaps this:

select somefield, SUM( CASE WHEN CPD >= PSD and CPD < dateadd(year,1,PSD) THEN paidAmount else 0 end )
where ...
group by somefield
0
 
BeyondBGCMAuthor Commented:
the date column 1 is PPD , and date column 2 is PSD , and amount column is PaidAmount
0
 
BeyondBGCMAuthor Commented:
please refer the attached sheet , for data
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
SELECT SUM(paidAmount) TotalPaidAmount
FROM TableName
WHERE ppd >= psd AND ppd <= psd+1

Open in new window

0
 
Mike EghtebasDatabase and Application DeveloperCommented:
WHERE PPD <= PSD  AND PPD < PSD  + 1
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
No sheet attached.
0
 
Lee SavidgeCommented:
Something like this:

select sum(paidamount) from mytable
where ppd is not null and psd is not null
and ppd between psd and psd+1

Open in new window

0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Ignore my last comment.
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
You may have to use the following if these dates have time included with them:

WHERE PPD <= PSD  AND PPD < PSD  + 2

Open in new window

0
 
BeyondBGCMAuthor Commented:
Ok, how can I write a regular expression which can tell me a particular row data is not correct date, out of a column , in a table
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
This is a new question. Please add it as new question.
0
 
BeyondBGCMAuthor Commented:
The question of sheet is like, it should bring sum of amount for all cpd dates which falls in range of psd and psd +1,

For every year ie if psd is 1jan15 then it should calculate it for every year till 1jan95
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
You are correct. My apology. I was focusing on between date of column 2 and +1 of date of column 2 part.
0
 
BeyondBGCMAuthor Commented:
do you have answer now .....?
to elaborate question , i want to share following details

1. the column CPD has date 01-Feb-14 and column PSD has date 01-Jan-14  and amount is 5 , then it should return sum =5
(because 01-Feb-14 will fall between 01-Jan-14 and 01-Jan-15
2. the column CPD has date 01-Feb-15 and column PSD has date 01-Jan-14  and amount is 7 , then it should return sum =0 ,7
(because 01-Feb-15 will fall between 01-Jan-15 and 01-Jan-16

and so on .....
0
 
BeyondBGCMAuthor Commented:
in above 2nd case 0 because , it has no amount between 01-Jan-14 and 01-Jan-15
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.