2

I have one table which is my stocks stored and I have another table which is stock order stores , now I want to get current week records count from Stock table based on inquiry table date.

Query works fine for me except some days is no records but I want to show zero also.

   select datename(dw,DATEADD(dd, 0, DATEDIFF(dd, 0, DATEADD(HOUR, 11, inquiry_tbl.order_date)))) date
,      count(stock_id) as TotalShipment
from      stock_tbl  
left join inquiry_tbl on stock_tbl.Inquiry_id = inquiry_tbl.Inquiry_id
where stock_tbl.inquiry_id = inquiry_tbl.inquiry_id
    and DATEADD(dd, 0, DATEDIFF(dd, 0, DATEADD(HOUR, 11, inquiry_tbl.order_date)))
    >=
    DATEADD(wk, DATEDIFF(wk,0,DATEADD(HOUR, 11, getdate())), -2)
    and
    DATEADD(dd, 0, DATEDIFF(dd, 0, DATEADD(HOUR, 11, inquiry_tbl.order_date)))
    <=
    DATEADD(wk, DATEDIFF(wk,0,DATEADD(HOUR, 11, getdate())), 4)
group by DATEADD(dd, 0, DATEDIFF(dd, 0, DATEADD(HOUR, 11, inquiry_tbl.order_date)))

Any solution?

Dudi Konfino
  • 1,126
  • 2
  • 13
  • 24
Shaahin Roshan
  • 55
  • 1
  • 8

2 Answers2

2

you need to remove stock_tbl.inquiry_id = inquiry_tbl.inquiry_id from your where clause. it will eliminate rows that would otherwise be included - it effectively changes your left outer join to an inner join:

edit: I just realized you're grouping by the column that is being left-joined in. In that case, your join is backwards as well. Try this:

edit: From your comment I'm assuming that there is no row in either table for the missing dates. In that case you must introduce those days into your query - here's an example of doing it with a values statement, you may want to consider using a date table if performance is an issue:

declare @start_date datetime = DATEADD(wk, DATEDIFF(wk, 0, DATEADD(HOUR, 11, getdate())), -2)

select
    week.day date,
    count(stock_id) as TotalShipment
from
    (select DATEADD(dd, d, @start_date) day
     from (values (0),(1),(2),(3),(4),(5),(6)) x(d)) week
left join
    inquiry_tbl on inquiry_tbl.order_date between week.day and week.day + 1
left join
    stock_tbl on stock_tbl.Inquiry_id = inquiry_tbl.Inquiry_id
group by
    week.day
gordy
  • 9,360
  • 1
  • 31
  • 43
  • Msg 4104, Level 16, State 1, Line 3 The multi-part identifier "inquiry_tbl.inquiry_id" could not be bound. Msg 4104, Level 16, State 1, Line 3 The multi-part identifier "inquiry_tbl.order_date" could not be bound. Msg 4104, Level 16, State 1, Line 5 The multi-part identifier "inquiry_tbl.order_date" could not be bound. Msg 4104, Level 16, State 1, Line 6 The multi-part identifier "inquiry_tbl.order_date" could not be bound. Msg 4104, Level 16, State 1, Line 2 The multi-part identifier "inquiry_tbl.order_date" could not be bound. – Shaahin Roshan Dec 21 '14 at 09:11
  • It's redundant but I'm not sure if it causes a problem. – VahidNaderi Dec 21 '14 at 09:11
  • @VahidND it will eliminate rows that would otherwise be included from the left join - it effectively changes the left outer join to an inner join. – gordy Dec 21 '14 at 09:14
  • @ShaahinRoshan that error message should not result from changing your where clause. let me clarify my answer with what the entire query should be – gordy Dec 21 '14 at 09:16
  • @gordy Same Result only getting two days instead of 7days – Shaahin Roshan Dec 21 '14 at 09:24
  • @gordy You're right I thought it is an inner join which is not. – VahidNaderi Dec 21 '14 at 09:46
  • @ShaahinRoshan you'll need to introduce the rows for the missing days from another table. I've edited my answer with one way you could do it – gordy Dec 21 '14 at 09:56
1

It seems there's not an elegant solution to this, but one possible solution is creating a Date table which have the rows for every day so you can join your result with that table. Another solution can be creating a Stored Procedure which return the week rows for a specified date range.

Take a look at this post and this.

Community
  • 1
  • 1
VahidNaderi
  • 2,448
  • 1
  • 23
  • 35