CODEWITHSUNDEEP
sql-servert-sqlsql-server-2005
Zain Ali
Zain Ali

Reputation: 15963

Select data between date ranges from 2 tables

Let suppose I have two table

  1. Taxes
  2. Products

I want to select sum of product values multiplied by their respective VAT value in taxes table as a percentage where the date of the product is less than date of tax and mDate is less than product date, grouped by VAT or ttid

For example 

        Taxes                                          products

 ttid     date    value  mDate                    Vat    value    date
  0     02-06-11    20    03-07-11                0       15    01-07-11
  1     03-07-11    10    03-08-11                0       15    01-07-11
  0     03-07-11    14    03-08-11                0       15    02-08-11
  1     03-08-11    15    03-09-11                1       10    04-07-11
                                                  1       10    06-08-11
                                                  1       10    08-09-11  --this will not include

So my result will look like this.

TTId   Value
0       3+3+2.1=8.1
1       1.5+1=2.5

The query I have written so far.

select   sum
          ( 
            (cast(t.Value as float)
             *
            (cast(x.value as float)/100)
          )
    as 'Vat'
   ,x.ttid
 from prducts t 
,taxes x where x.ttid=t.vat  and  x.mDate>t.date and x.date<=t.date
group by x.ttid

Will somebody help me?

Upvotes: 1

Views: 1298

Answers (3)

Zain Ali
Zain Ali

Reputation: 15963

select sum(Vat)'Vat',itemvat,sum(productvalue)'ProductValue' from
(
SELECT
 t.vat,
t.value,
   isnull(t.value *
     (SELECT TOP 1 value FROM #TMP x where x.ttid=t.vat  and 
 convert(varchar,cast(x.effectiveDate as datetime),3)>convert(varchar,cast(t.date as datetime),3)
and convert(varchar,cast(x.mDate as datetime),3)<=convert(varchar,cast(t.date as datetime),3)

)/100,'5') 
as 'Vat'
FROM products t  
) 
as t where Vat<>'5' group by itemvat

Upvotes: 0

Jon Egerton
Jon Egerton

Reputation: 41549

There are a few problems here:

As stated in the comments you've got + followed by *

In the Tax table date always has a value less than mdate. Your WHERE test has x.date>t.datee and x.mDate<=t.date which implies that x.date is greater than x.mDate. This will always be false

You've spelt t.date with an extra e in the WHERE clause

You misspelled products as the name of the table.

In addition, using t as the alias for products and x as the alias for tax is really confusing.

Upvotes: 2

CristiC
CristiC

Reputation: 22698

SELECT
   SUM( t.Value *
     ISNULL((SELECT TOP 1 value FROM taxes x where x.ttid=t.vat and t.date BETWEEN x.date AND x.mDate order by t.datee), 100)/100) as 'Vat'
FROM prducts t

Upvotes: 1

Related Questions