CODEWITHSUNDEEP
sqlsql-servert-sql
piotr
piotr

Reputation: 85

Need an expert in T-Sql SQL to solve the averages per week day issue

I have decided to write a post because I run out of idea how can modify the query I wrote to pull the average number of shipments and pieces per day. The query basically picks all the shipment and pieces from OrderTbl and count them based on the office and delivery date. It works all right until it comes to Saturday and Sundays when often there are no orders to deliver. I found that then those days without deliveries stays uncountable and the result is wrong. For example, in February we got 4 Saturdays and 4 Sundays but I am getting the result divided by 3 because as I mentioned before there was no deliveries over the weekend.

Please have a look on the query and let me know what sort of solution I should be looking solve this average count issue. 

select [DAY],
    AVG(CAST(qty.PieceAmount as float)) as [PieceAmountAvg],
    AVG(CAST(qty.ShipmentAmount as float)) as [ShipmentAmountAVG]
from (
    select w = DATEDIFF(WEEK, 0, OrderTbl.DelDate),
        [Day] = DATENAME(Weekday, OrderTbl.DelDate, ShipmentAmount = COUNT(distinct OrderTbl.CEXP), PieceAmount = SUM(OrderTbl.Pieces) from dbo.OrderTbl(nolock)
            where OrderTbl.Cbur = 'GBLR'
            and (
                OrderTbl.DelDate >= '20170201'
                and OrderTbl.DelDate <= '20170228'
                ) group by DATEDIFF(WEEK, 0, OrderTbl.DelDate), DATENAME(WEEKDAY, OrderTbl.DelDate), DATEPART(WEEKDAY, OrderTbl.DelDate)) as qty
    group by [Day]
    order by case when Day = 'Monday' then 1 when Day = 'Tuesday' then 2 when Day = 'Wednesday' then 3 when Day = 'Thursday' then 4 when Day = 'Friday' then 5 when Day = 'Saturday' then 6 when Day = 'Sunday' then 7 end asc;
    )

I will appreciate much your your help.

Upvotes: 2

Views: 157

Answers (2)

SqlZim
SqlZim

Reputation: 38033

For only 152kb in memory, you can have 30 years of dates in a table with this:

/* dates table */
declare @fromdate date = '20000101';
declare @years    int  = 30;
/* 30 years, 19 used data pages ~152kb in memory, ~264kb on disk */
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
select top (datediff(day, @fromdate,dateadd(year,@years,@fromdate)))
    [Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
into dbo.Dates
from n as deka cross join n as hecto cross join n as kilo
               cross join n as tenK cross join n as hundredK
order by [Date];
create unique clustered index ix_dbo_Dates_date
  on dbo.Dates([Date]);

Without taking the actual step of creating a table, you can use it inside a common table expression with just this:

declare @fromdate date = '20000101';
declare @years    int  = 30;
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, dates as (
  select top (datediff(day, @fromdate,dateadd(year,@years,@fromdate)))
      [Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
  from n as deka cross join n as hecto cross join n as kilo
                cross join n as tenK cross join n as hundredK
   order by [Date]
)
select [Date]
from dates;

And use either like so:

set datefirst 1;
select
    [day]
  , [PieceAmountAvg] = avg(cast(qty.PieceAmount as float))
  , [ShipmentAmountavg] = avg(cast(qty.ShipmentAmount as float))
from (
   select
         w             = datediff(week, 0, d.Date)
      , [Day]          = datename(Weekday, d.Date)
      , ShipmentAmount = count(distinct o.Cexp)
      , PieceAmount    = sum(o.Pieces)
      , DayOfWeek      = datepart(weekday, d.Date)
    from dates d
      left join dbo.OrderTbl o
        on d.date = o.DelDate
          and o.Cbur = 'gblr'
     --left join fexp (nolock) on fexp.Cexp = fexpsui.Cexp
     where (d.Date >= '20170201' and d.Date <= '20170228')
     group by
       datediff(week, 0, d.Date)
     , datename(weekday, d.Date)
     , datepart(weekday, d.Date)
   ) as qty
 group by [Day], DayOfWeek
 order by DayOfWeek
  /*case
    when Day = 'Monday' then 1
    when Day = 'Tuesday' then 2
    when Day = 'Wednesday' then 3
    when Day = 'Thursday' then 4
    when Day = 'Friday' then 5
    when Day = 'Saturday' then 6
    when Day = 'Sunday' then 7 
    end asc
    */

If you set datefirst 1 you could order by datepart(weekday, d.Date) instead of your case expression.

If you wanted to create a full Calendar table, you would also be able to avoid all of those datepart() and datename() functions.

Number and Calendar table reference:

Upvotes: 1

john McTighe
john McTighe

Reputation: 1181

Create a Dummy Date table with every date possible that you'll need. Then RIGHT JOIN to this table in your inner query. That way you'll have a record for every day even if no orders occurred.

Upvotes: 0

Related Questions