Go Premium for a chance to win a PS4. Enter to Win

x
Solved

# sql query

Posted on 2015-02-23
Medium Priority
230 Views
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
Question by:BeyondBGCM
• 6
• 4
• 2
• +3

Author Comment

ID: 40625779
the date column 1 is PPD , and date column 2 is PSD , and amount column is PaidAmount
0

Author Comment

ID: 40625781
please refer the attached sheet , for data
0

LVL 52

Expert Comment

ID: 40625794
``````SELECT SUM(paidAmount) TotalPaidAmount
FROM TableName
WHERE ppd >= psd AND ppd <= psd+1
``````
0

LVL 34

Expert Comment

ID: 40625796
WHERE PPD <= PSD  AND PPD < PSD  + 1
0

LVL 24

Expert Comment

ID: 40625797
No sheet attached.
0

LVL 25

Expert Comment

ID: 40625799
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
``````
0

LVL 24

Expert Comment

ID: 40625800
Ignore my last comment.
0

LVL 34

Expert Comment

ID: 40625803
You may have to use the following if these dates have time included with them:

``````WHERE PPD <= PSD  AND PPD < PSD  + 2
``````
0

Author Comment

ID: 40626009
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

LVL 34

Expert Comment

ID: 40626079
0

Author Comment

ID: 40627345
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

LVL 34

Expert Comment

ID: 40627358
You are correct. My apology. I was focusing on between date of column 2 and +1 of date of column 2 part.
0

Author Comment

ID: 40627480
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

Author Comment

ID: 40627484
in above 2nd case 0 because , it has no amount between 01-Jan-14 and 01-Jan-15
0

LVL 49

Accepted Solution

PortletPaul earned 1000 total points
ID: 40631884
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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
###### Suggested Courses
Course of the Month10 days, 22 hours left to enroll