Re: [SQL] group by before and after date
I got it. I had to put the whole case statement into the sum so my statement ended up: select a.DueDate,a.PartID,a.AmountNeeded,a.CurrentStock, sum(coalesce(case when b.DatePromisedBy<=a.DueDate then coalesce(b.QuantityOrdered,0)-coalesce(b.DeliveredSum,0) end,0)) as ExpectedBefore, sum(coalesce(case when b.DatePromisedBy >a.DueDate then coalesce(b.QuantityOrdered,0)-coalesce(b.DeliveredSum,0) end,0)) as ExpectedAfter from TableA a left join TableB on a.partid=b.partid group by a.DueDate,a.PartID,a.AmountNeeded,a.CurrentStock "Sim Zacks" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > 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.DueDate > then sum(coalesce(b.QuantityOrdered,0))-sum(coalesce(b.DeliveredSum,0)) > end,0) as ExpectedBefore, > coalesce(case when b.DatePromisedBy >a.DueDate > then 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 > > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] group by before and after date
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.DueDate then sum(coalesce(b.QuantityOrdered,0))-sum(coalesce(b.DeliveredSum,0)) end,0) as ExpectedBefore, coalesce(case when b.DatePromisedBy >a.DueDate then 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[SQL] plpython setof
Anybody know how to return a setof from a plpython function? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] scroll cursor bug or me?
It seems to me that scroll cursors are not valid in plpgsql. The following query in PGAdmin works. run one line at a time. begin work; declare bob scroll cursor for select * from testtbl; fetch forward 5 from bob; fetch prior from bob; rollback work; "Larry Morroni" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Hi, I am hitting an issue when using SCROLL CURSORS. > > -->My Setup: > PostgreSQL 8.0.3 on Windows XP Pro SP2 > > > -->My Test Function: > CREATE OR REPLACE FUNCTION TestFunction () RETURNS varchar(50) AS > $$ > DECLARE CursorProductGroups SCROLL CURSOR FOR SELECT * FROM TEST_TABLE; > BEGIN > RETURN NULL; > END; > $$ > LANGUAGE plpgsql > ; > > -->psql spits back the following: > > STORE_PHYSICAL_A=# \i test.sql > psql:test.sql:9: ERROR: syntax error at or near "CURSOR" > CONTEXT: invalid type name "SCROLL CURSOR FOR SELECT * FROM TEST_TABLE" > compile of PL/pgSQL function "testfunction" near line 1 > STORE_PHYSICAL_A=# > > This seems like a bug to me because my demo looks > just like the examples given in the PostgreSQL documentation. ie... > > -- BEGIN POSTGRES DOC EXAMPLE > BEGIN WORK; > DECLARE liahona SCROLL CURSOR FOR SELECT * FROM films; > FETCH FORWARD 5 FROM liahona; > CLOSE liahona; > COMMIT WORK; > -- END POSTGRES DOC EXAMPLE > > Any ideas? > > Larry Morroni ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[SQL] join if all matches
I am trying to figure out an sql statement and I was hoping someone could help. I'm having brainfreeze right now. Table Rules RuleID RuleName Table RuleAgents RuleAgentID RuleID Agent Table RuleActions RuleActionID RuleID Action I am passing in an array of agents into a function and I would like to see all the actions for which all of the agents of a rule have been found. For example: Rules RuleIDRuleName 1 Rule1 2 Rule2 RuleAgents RuleAgentIDRuleIDAgent 1115 2117 3291 RuleActions RuleActionIDRuleIDAction 111000 211005 311010 411099 521500 629807 721409 If I pass into my function 15 then I don't want it to return anything, because rule1 requires both 15 and 17. If I pass in 19 then I want it to return a resultset including the actions with Rule2 (1500,9807,1409) If I pass in both 15 and 17 then I want it to return all the actions with Rule1 Any thoughts on the join? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] join on a like
A friend suggested that I use the Like predicate as part of my join condition and I was wondering if that was wise. For example, I have a column that describes the level of an event. There is A,B,C and then they can have children, such as AA,AB,BA,BB and they can all have children as well. So if I wanted to see all the children with their parents at every level it would be: select a.* from tbl1 a join tbl1 b on a.level like b.level || '%' That would give me on one side A and on the other side A,AA,AB,AAAB,... as well as AA and on the other side all of its children. I'm just nervous about using a Like in a join. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] join on a like
I guess the real question is: what is more efficient - a join on a LIKE or a PLPGSQL function that has 2 loops, 1 for each bottom level child and one that takes the substring of that child one character at a time to get each parent? It sounds like you are saying that the join will actaully use the index and therefore it is optimal, not just possible. [EMAIL PROTECTED] wrote: If you use a btree index on the column, you should be able to use it in the like: http://www.postgresql.org/docs/8.1/interactive/indexes-types.html. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] join on next row
I am having brain freeze right now and was hoping someone could help me with a (fairly) simple query. I need to join on the next row in a similar table with specific criteria. I have a table with events per employee. I need to have a query that gives per employee each event and the event after it if it happened on the same day. The Events table structure is: EventID Employee EventDate EventTime EventType I want my query resultset to be Employee,EventDate(1),EventTime(1),EventType(1),EventTime(2),EventType(2) Where Event(2) is the first event of the employee that took place after the other event. Example EventID EmployeeEventDate EventTime EventType 1 John6/15/2006 7:00A 2 Frank 6/15/2006 7:15B 3 Frank 6/15/2006 7:17C 4 John6/15/2006 7:20C 5 Frank 6/15/2006 7:25D 6 John6/16/2006 7:00A 7 John6/16/2006 8:30R Expected Results John, 6/15/2006, 7:00, A, 7:20, C Frank, 6/15/2006, 7:15, B, 7:17, C Frank, 6/15/2006, 7:17, C, 7:25, D John, 6/16/2006, 7:00, A, 8:30, R To get this result set it would have to be an inner join on employee and date where the second event time is greater then the first. But I don't want the all of the records with a greater time, just the first event after. Thank You Sim ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
