CODEWITHSUNDEEP
sqlsql-server
SBP
SBP

Reputation: 3

Select weekly data from date table

I have a table with date and other columns. The dates are all weekdays excluding holidays and weekends. I need to select weekly data from the table (OR every Monday data and if Monday is a holiday select Tuesday's. Next row will be Monday's data and so on.).

    Example table columns and data:
    Date           Rate    StockQty       
    2018/08/31      22       25 
    2018/09/04      24       25
    2018/09/05      23       24
    2018/09/06      19       21
    2018/09/07      25       22
    2018/09/10      21       21

    I need to select data such that the result will be:

    Date           Rate    StockQty       
    2018/08/31      22       25
    2018/09/04      24       25
    2018/09/10      21       21

It is selecting one row per week. 9/3 is Monday and a holiday, so select Tuesday date, then select next week's Monday date.

I tried to partition by DatePart, but it lupms all week together.

Upvotes: 0

Views: 2451

Answers (3)

Vigya
Vigya

Reputation: 142

    create table #Date_rate
    (
    date smalldatetime,rate int,stockQty int
    )
    Insert into #Date_rate
    select '2018/08/31',      22 ,      25  union
    select '2018/09/04',      24 ,      25  union
    select '2018/09/05',      23 ,      24  union
    select '2018/09/06',      19 ,      21  union
    select '2018/09/07',      25 ,      22  union
    select '2018/09/10',      21 ,      21

 select 
         a.date
        ,a.rate
        ,a.stockQty
    from(
        select 
             *
            ,dense_rank() over(partition by datepart(WEEK,date) order by datepart(WEEKDAY,date) asc) as SekectedDay
        from  #Date_rate
    ) a where SekectedDay=1

Upvotes: 2

GGadde
GGadde

Reputation: 391

This should work in SQL Server:

SELECT date,Rate,StockQty FROM 
(SELECT
    date,
    Rate,
    StockQty,
    ROW_NUMBER() OVER(PARTITION BY YEAR(date),DATENAME(WK,Date) ORDER BY day(date))cnt
 FROM
    #temp
 )m
WHERE
cnt = 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269493

You can follow logic like this:

select t.*
from (select t.*,
             row_number() over (partition by extract(year from date), extract(week from date) order by date asc) as seqnum
      from t
     ) t
where seqnum = 1;

Date functions can vary by database. This uses ANSI/ISO standard functions.

Upvotes: 0

Related Questions