• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 232
  • Last Modified:

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
0
BeyondBGCM
Asked:
BeyondBGCM
  • 6
  • 4
  • 2
  • +3
1 Solution
 
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
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
SELECT SUM(paidAmount) TotalPaidAmount
FROM TableName
WHERE ppd >= psd AND ppd <= psd+1

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
PortletPaulCommented:
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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 4
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now