group by before and after date
| От | Sim Zacks |
|---|---|
| Тема | group by before and after date |
| Дата | |
| Msg-id | d0jkcf$28sb$1@news.hub.org обсуждение исходный текст |
| Ответы |
Re: group by before and after date
|
| Список | pgsql-sql |
I have 2 tables 1 has a date field and component need by that date and the other has all the upcoming orders. I am trying to build a query that will give me the Date and ComponentNeed and also how many components have been ordered before that date and how many after. PostGreSQL is telling me I need to group on DatePromisedBy. I have tried a number of different possibilities which haven't worked and now I have run into brain freeze. Any help would be appreciated. Tables TableA DueDate PartID AmountNeeded CurrentStock Table B PartID QuantityOrdered DeliveredSum DatePromisedBy The select that I want is select a.DueDate,a.PartID,a.AmountNeeded,a.CurrentStock, coalesce(case when b.DatePromisedBy<=a.DueDatethen sum(coalesce(b.QuantityOrdered,0))-sum(coalesce(b.DeliveredSum,0)) end,0) as ExpectedBefore, coalesce(case when b.DatePromisedBy >a.DueDatethen sum(coalesce(b.QuantityOrdered,0))-sum(coalesce(b.DeliveredSum,0)) end,0) as ExpectedAfter from TableA a left join (Table B) on a.partid=b.partid group by a.DueDate,a.PartID,a.AmountNeeded,a.CurrentStock
В списке pgsql-sql по дате отправления: