Help needed for SQL

Transact-SQL
1

Hello,

I have below two tables in my database

CREATE TABLE details(
	[id] [bigint] IDENTITY(1,1) NOT NULL primary key,
	[tarih] [date] NULL,
	[barkod] [nvarchar](40) NULL,
	[miktar] [float] NULL,
	[birimfiyat] [float] NULL
)

Details table keeps sales of goods. tarih column keeps date when sales took place. birimfiyat column is the sales price per piece.

CREATE TABLE prices(
	[autoinc] [bigint] IDENTITY(1,1) NOT NULL primary key,
	[barkod] [nvarchar](40) NULL,
	[alisfiyati] [float] NULL,
	[tarih] [date] NULL
)

When there is a change of purchase price, I add a new row in prices table with the date new price is effective.

Assuming there is just two rows in prices table:

barkod                                   alisfiyati             tarih
---------------------------------------- ---------------------- ----------
123                                      10                     2017-01-15
123                                      12                     2017-01-31

I could not write an SQL which lists all rows of detail table and purchase price column.

  • Any sales of barcode 123 between dates 2017-01-15 and 2017-01-30 in the result set should list 10 as purchase price
  • Dates listed 2017-01-31 and later should list 12 as purchase price
  • If there are sales before date 2017-01-15 these should have null as purchase price listed.

I appreciate any help.

Thanks.

2

Try this:

select barkod
      ,alisfiyati
      ,tarih
  from (select d.barkod
              ,p.alisfiyati
              ,d.tarih
              ,row_number() over(partition by d.barkod
                                 order by p.tarih desc
                                )
               as rn
          from details as d
               left outer join prices as p
                            on p.barkod=d.brakod
                           and p.tarih<=d.tarih
       ) as t
 where rn=1
;

or this:

select d.barkod
      ,p.alisfiyati
      ,d.tarih
  from details as d
       left outer join (select a.barkod
                              ,min(p.tarih) as tarih
                          from details as d
                               inner join prices as p
                                       on p.barkod=d.brakod
                                      and p.tarih<=d.tarih
                         group by a.barkod
                       ) as t
                    on t.barkod=d.barkod
       left outer join prices as p
                    on p.barkod=t.barkod
                   and p.tarih=t.tarih
;
3

Hi bitsmed,

Thank you for your help. Your second query seems what I need. However, I get wrong values for p.alisfiyati column with below values in my tables.

prices table:

autoinc              barkod                                   alisfiyati             tarih
-------------------- ---------------------------------------- ---------------------- ----------
1                    1                                        10                     2016-11-15
2                    1                                        12                     2016-11-30
3                    2                                        5                      2016-11-01
4                    2                                        7                      2016-11-15
5                    2                                        10                     2016-11-20
6                    3                                        5                      2016-11-01
7                    3                                        10                     2016-11-10
8                    3                                        15                     2016-11-15

details table:

id                   tarih      barkod                                   miktar                 birimfiyat
-------------------- ---------- ---------------------------------------- ---------------------- ----------------------
29                   2016-11-01 019954121143                             1                      32,76
30                   2016-11-01 019954121143                             1                      32,76
31                   2016-11-01 019954121143                             1                      32,76
32                   2016-11-16 1                                        1                      10
33                   2016-11-16 2                                        1                      5
34                   2016-11-16 3                                        1                      15
35                   2016-11-16 1                                        1                      10
36                   2016-11-16 2                                        1                      5
37                   2016-11-16 3                                        1                      15
38                   2016-11-16 1                                        1                      10
39                   2016-11-16 1                                        1                      10
40                   2016-11-18 1                                        1                      10
41                   2016-11-18 2                                        1                      5
42                   2016-11-18 3                                        1                      15

Result I receive for your second suggested SQL is:

barkod                                   alisfiyati             tarih
---------------------------------------- ---------------------- ----------
019954121143                             NULL                   2016-11-01
019954121143                             NULL                   2016-11-01
019954121143                             NULL                   2016-11-01
1                                        10                     2016-11-16
2                                        5                      2016-11-16
3                                        5                      2016-11-16  /* wrong alisfiyati, should be 15*/
1                                        10                     2016-11-16
2                                        5                      2016-11-16
3                                        5                      2016-11-16
1                                        10                     2016-11-16
1                                        10                     2016-11-16
1                                        10                     2016-11-18
2                                        5                      2016-11-18
3                                        5                      2016-11-18

I have marked only one wrong row, but there are others, too. Seems like SQL is returning smallest date price. Though, I might be wrong.

Thanks.

4

My mistake.

Try this:

select barkod
      ,alisfiyati
      ,tarih
  from (select d.barkod
              ,p.alisfiyati
              ,d.tarih
              ,row_number() over(partition by d.id
                                             ,d.barkod
                                 order by p.tarih desc
                                )
               as rn
          from details as d
               left outer join prices as p
                            on p.barkod=d.barkod
                           and p.tarih<=d.tarih
       ) as t
 where rn=1
 order by barkod
         ,tarih
;

Or this:

select d.barkod
      ,p.alisfiyati
      ,d.tarih
  from details as d
       left outer join (select d.id
                              ,d.barkod
                              ,max(p.tarih) as tarih
                          from details as d
                               inner join prices as p
                                       on p.barkod=d.barkod
                                      and p.tarih<=d.tarih
                         group by d.id
                                 ,d.barkod
                       ) as t
                    on t.id=d.id
                   and t.barkod=d.barkod
       left outer join prices as p
                    on p.barkod=t.barkod
                   and p.tarih=t.tarih
 order by barkod
         ,tarih
;
1 Like
5

Hi bitsmed,

Your second post works flawless.

Thank you.