Solved

sql query

Posted on 2015-02-23
15
210 Views
Last Modified: 2015-03-05
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
Comment
Question by:BeyondBGCM
  • 6
  • 4
  • 2
  • +3
15 Comments
 

Author Comment

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

Author Comment

by:BeyondBGCM
ID: 40625781
please refer the attached sheet , for data
0
 
LVL 47

Expert Comment

by:Vitor Montalvão
ID: 40625794
SELECT SUM(paidAmount) TotalPaidAmount
FROM TableName
WHERE ppd >= psd AND ppd <= psd+1

Open in new window

0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40625796
WHERE PPD <= PSD  AND PPD < PSD  + 1
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40625797
No sheet attached.
0
 
LVL 25

Expert Comment

by:Lee Savidge
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

Open in new window

0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40625800
Ignore my last comment.
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 40625803
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
 

Author Comment

by:BeyondBGCM
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 33

Expert Comment

by:Mike Eghtebas
ID: 40626079
This is a new question. Please add it as new question.
0
 

Author Comment

by:BeyondBGCM
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 33

Expert Comment

by:Mike Eghtebas
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

by:BeyondBGCM
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

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

Accepted Solution

by:
PortletPaul earned 500 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question