CODEWITHSUNDEEP
sqlsql-serverssms
Chataby Nabyl
Chataby Nabyl

Reputation: 1

Query using where clause between two dates (different date range for each ID)

I'm trying to query a set of data using the where clause. Here's the example of the data collection:

+-----------+------------+-------------+
| Driver ID | Paid Date  | Paid Amount |
+-----------+------------+-------------+
|    000001 | 2020-08-01 |      170000 |
|    000001 | 2020-08-02 |      170000 |
|    000001 | 2020-08-03 |      170000 |
|    000001 | 2020-08-04 |      170000 |
|    000002 | 2020-08-02 |      170000 |
|    000002 | 2020-08-03 |      170000 |
|    000002 | 2020-08-04 |      170000 |
|    000002 | 2020-08-05 |      170000 |
+-----------+------------+-------------+

I want to sum the Paid Amount column, group by Driver ID, and using this where clause:

[where 'paid date' between min(paid date) and dateadd(day, 3, paid date)]

The problem that I have is, I cannot use the where clause above, because the date range for each Driver ID are different.

Is there any way to solve those problem?

Upvotes: 0

Views: 377

Answers (1)

Venkataraman R
Venkataraman R

Reputation: 12959

You can use xquery to get the data as table and then apply filters and group data accordingly.

DECLARE @xmlvalue xml = 
'<table style=''border: 1px solid; background: #ffffff; font-size: 12px; text-align: center; width: 100%''>
 <tr >
   <th >Driver ID</th>
   <th >Paid Date</th>
   <th >Paid Amount</th>
 </tr>
 <tr>
   <td >000001</td>
   <td >2020-08-01</td>
   <td >170000</td>
 </tr>
  <tr>
   <td >000001</td>
   <td >2020-08-02</td>
   <td >170000</td>
 </tr>
  <tr>
   <td >000001</td>
   <td >2020-08-03</td>
   <td >170000</td>
 </tr>
  <tr>
   <td >000001</td>
   <td >2020-08-04</td>
   <td >170000</td>
 </tr>
   <tr>
   <td >000002</td>
   <td >2020-08-02</td>
   <td >170000</td>
 </tr>
    <tr>
   <td >000002</td>
   <td >2020-08-03</td>
   <td >170000</td>
 </tr>
    <tr>
   <td >000002</td>
   <td >2020-08-04</td>
   <td >170000</td>
 </tr>
   <tr>
   <td >000002</td>
   <td >2020-08-05</td>
   <td >170000</td>
 </tr>
</table>'

;WITH CTE_DriverData AS
(
SELECT DriverID, PaidDate, min(PaidDate) OVER ( PARTITION  BY DriverID) as minPaidDate, PaidAmount
FROM
(
SELECT 
t.cols.value('./td[1]','varchar(30)') as DriverID,
t.cols.value('./td[2]','date') as PaidDate,
t.cols.value('./td[3]','int') as PaidAmount
FROM
@XMLValue.nodes('/table/tr') as t(cols)
WHERE t.cols.exist('./td') = 1
) AS x
)
SELECT DriverID, sum(PaidAmount) as PaidAmount
FROM CTE_DriverData
WHERE PaidDate between minPaidDate and DATEADD(DD,3, minPaidDate)
group by DriverID


+----------+------------+
| DriverID | PaidAmount |
+----------+------------+
|   000001 |     680000 |
|   000002 |     680000 |
+----------+------------+

Upvotes: 1

Related Questions