CODEWITHSUNDEEP
sqlsql-server-ce
user3129015
user3129015

Reputation:

Stuck with this query

My database looks like this

DailyData

rID int
Stock varchar
rDate date
Shares int
price float

What I am trying to do is get the data for two dates.

Sample data

rID    stock    rDate        Shares    price
11     Stock1   21/03/2016   15        1.22
12     Stock2   21/03/2016   22        2.23
13     Stock3   21/03/2016   17        3.32
14     Stock4   21/03/2016   10        4.24
15     Stock1   22/03/2016   15        1.25
16     Stock2   22/03/2016   20        2.27
17     Stock3   22/03/2016   17        3.32
18     Stock1   23/03/2016   15        1.28
19     Stock2   23/03/2016   20        2.20
20     Stock3   23/03/2016   17        3.32
21     Stock4   23/03/2016   10        4.24

Expected output

Stock       Shares-21    Shares-20
Stock1      15           15
Stock2      22           20
Stock3      17           17
Stock4      10           0

My query against a SQL Server CE database:

Select 
    DD1.Stock, sum(DD1.Shares) as Shares-21, sum(DD2.shares) as Shares-20
from 
    DailyData DD1, DailyData DD2
where 
    DD1.rDate = '21/03/2016' and DD2.rDate = '20/03/2016' 
    and DD1.Stock = DD2.Stock
group by 
    DD1.Stock

I am getting 7 rows of data instead of 4.

Please help with the query.

******************************* new modification ********************* i followed as suggested but it seems not to work. this is a actual sql script.

Select P.pName,DD.Stock, 
  sum(case DD.rDate when '03/21/2016' then DD.Shares  else 0 end) as Shares21, 
  sum(case DD.rDate when '03/20/2016' then DD.Shares else  0 end) as Shares20
from dailyData DD, Portfolios P
where DD.rDate = '03/21/2016' or DD.rDate = '03/20/2016'
  and DD.pID = P.pID
  and DD.pID=1
group by P.pName,DD.stock
order by P.pName,DD.Stock

now for pID=1 there are 23 records for 20-Mar and 21-Mar

upon running this query, it returns way more than 23. I am expecting 23 records only.

Upvotes: 0

Views: 105

Answers (2)

TriV
TriV

Reputation: 5148

You could use PIVOT with DATEPART 

      SELECT Stock, [21] AS [Shares-21], [20] AS [Shares-20] FROM
      (
         SELECT s.Stock, Datepart(d,s.rDate) rDate , s.Shares FROM DailyData s
      ) src
      PIVOT
      (
         MAX(Shares) for rDate in ([21], [20])
      ) pvt

Upvotes: 0

Trung Duong
Trung Duong

Reputation: 3475

It actually does not need to use JOIN. You could use CASE WHEN statement like this 

SELECT stock, 
       Sum(CASE rdate 
             WHEN '21/03/2016' THEN shares 
             ELSE 0 
           END) AS Shares21, 
       Sum(CASE rdate 
             WHEN '20/03/2016' THEN shares 
             ELSE 0 
           END) AS Shares20 
FROM   dailydata 
WHERE  rdate = '21/03/2016' 
        OR rdate = '20/03/2016' 
GROUP  BY stock

Upvotes: 2

Related Questions