November 1, 2013 at 3:13 am
Hi Guys,
I'm hoping this is "possible" but my skills are failing me...
I have this query that works fine...
SELECT
shipmentitems.orderid AS Ref,
shipments.id,
shipments.traderid AS "Supplier Number",
suppliers.name AS "Supplier Name",
shipmentitems.partid AS "Part Number",
purchaseorderitems.duedate AS "PO Due Date",
shipments.shipmentdate AS "Receipt Date",
CASE
when shipments.shipmentdate > purchaseorderitems.duedate +3
THEN 'NO'
else 'YES'
END AS "On Time",
purchaseorderitems.traderquantity AS "Quantity Ordered",
"Quantity Received" = CASE WHEN
shipmentitems.shipmenttype = 'PO'
THEN shipmentitems.stockedquantity
ELSE '0'
END,
"Quantity Rejected"= CASE WHEN
shipmentitems.shipmenttype = 'PR' AND
shipmentitems.stockedquantity = shipmentitems.traderquantity
THEN ABS(shipmentitems.stockedquantity)
ELSE '0'
END,
shipmentitems.originalshipmentid
FROM shipments
LEFT OUTER JOIN shipmentitems ON
shipments.id = shipmentitems.shipmentid
LEFT OUTER JOIN purchaseorderitems ON
shipmentitems.orderid = purchaseorderitems.orderid
AND
shipmentitems.orderitemnumber = purchaseorderitems.itemnumber
INNER JOIN suppliers ON
shipments.traderid = suppliers.id
where shipments.shipmentdate BETWEEN
'01 September 2013' AND '30 September 2013'
AND
shipmentitems.orderid = 'P/082926'
AND
shipmentitems.stockedquantity != '0'
order by
shipments.traderid,
shipmentitems.partid
And with these applied filters to limit output, so you can see what I'm after, produces this:
RefidSupplier NumberSupplier NamePart NumberPO Due DateReceipt DateOn TimeQuantity OrderedQuantity ReceivedQuantity Rejectedoriginalshipmentid
P/082926GR/209135H006H.P.M. LTD294-892402013-08-30 11:28:10.3872013-09-06 12:18:36.123NO5002000NULL
P/082926GR/209165H006H.P.M. LTD294-892402013-08-30 11:28:10.3872013-09-10 14:07:25.390NO5003000NULL
P/082926GR/209291H006H.P.M. LTD294-892402013-08-30 11:28:10.3872013-09-24 13:50:38.063NO500590NULL
P/082926RS/200101H006H.P.M. LTD294-892402013-08-30 11:28:10.3872013-09-09 11:51:13.227NO500059GR/209135
P/082926GR/209291H006H.P.M. LTD294-892402013-09-30 00:00:00.0002013-09-24 13:50:38.063YES500710NULL
(Sorry about the formatting...)
Now my question is:
Where it has qty rejected - originalshipmentid references shipmentid - but obviously this is a separate row on the table. My user wants me to put it the same line, so that the reject qty appears alongside the original receipt.
RefidSupplier NumberSupplier NamePart NumberPO Due DateReceipt DateOn TimeQuantity OrderedQuantity ReceivedQuantity Rejectedoriginalshipmentid
P/082926GR/209135H006H.P.M. LTD294-8924030/08/201306/09/2013NO50020059GR/209135
(Apologies again for the formatting.)
I don't need "originalshipmentid" I just put that in as it's the "link" between the two data rows.
Is this possible? If "yes" how do I do it?
The actual report will be run each month against the previous month's dates.
November 1, 2013 at 7:29 am
In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 1, 2013 at 7:29 am
The first thing that comes to mind is to make use of a temp table or a table variable. Insert your delivery information into a temp table, and then update the temp table with the quantity rejected information.
November 1, 2013 at 1:23 pm
Sean Lange (11/1/2013)
In order to help we will need a few things:1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Sorry, don't understand any of this. It's a query, that works "as is". I'm not creating tables or wishing to insert into tables. :ermm:
November 1, 2013 at 1:25 pm
stevenb 63624 (11/1/2013)
The first thing that comes to mind is to make use of a temp table or a table variable. Insert your delivery information into a temp table, and then update the temp table with the quantity rejected information.
I was reading about CTE and thought that might help, but couldn't figure out how to make it work.
I did wonder about a temp table...
November 1, 2013 at 1:29 pm
malcolm.garbett (11/1/2013)
Sean Lange (11/1/2013)
In order to help we will need a few things:1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Sorry, don't understand any of this. It's a query, that works "as is". I'm not creating tables or wishing to insert into tables. :ermm:
The point here is that I can't help you build your sql because I don't have tables to work with. It may well work on your system but on mine it won't. Remember that we can't see you screen, we have no idea what your tables look like and we have no idea what you are trying to do. The only information we have is what you have posted.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 1, 2013 at 1:39 pm
Typically what you do is something like...
(This is a generic table to illustrate the process)
CREATE TABLE #tmp
(
Col1 INT,
Col2 VARCHAR(50),
Col3 VARCHAR(50)
)
And then you insert into the temp table for all the delivery records you have.
Then you go back and update the temp table with the reject info
So...
INSERT INTO #tmp
(Col1, Col2)
SELECT col1, col2
FROM SourceTable
WHERE ...
Then you update with
UPDATE #tmp
SET Col3 = src.col3
FROM #tmp t
INNER JOIN SourceTable src on t.Col1 = src.col1
WHERE ...
You just have to fill in the where clauses so you're only getting your delivery records in the insert step and you're only getting the reject records in the update step. Of course, you do the join on whatever you're using to uniquely identify your records.
November 5, 2013 at 6:04 am
Hi all.
BIG thanks for all the help.
The temp table - that worked. 🙂 Used temp tables a lot when I was working with informix SQL, but have fallen out of the habit. Perhaps the application I'm working with now is "better."
Anyway, seeing it work with temp table, this got me thinking again about CTE - which I'd never used before. After some experimentation I had it working with that, so opted for that solution. (One concern I had with temp tables was whether it would work correctly once I moved my SQL query to the Report Writer BIRT. (Not saying it wouldn't; just had me wondering.)
My actual, working SQL below for the month of September. And thanks again. 🙂
With rejects as
(
select
shipmentitems.stockedquantity,
shipmentitems.originalshipmentid,
shipmentitems.originalshipmentitemnumber
from shipmentitems
where shipmentitems.shipmenttype = 'PR'
and shipmentitems.createddate > '01 March 2011'
)
SELECT
shipmentitems.orderid AS Ref,
shipments.traderid AS "Supplier Number",
suppliers.name AS "Supplier Name",
shipmentitems.partid AS "Part Number",
purchaseorderitems.duedate AS "PO Due Date",
shipments.shipmentdate AS "Receipt Date",
CASE
when shipments.shipmentdate > purchaseorderitems.duedate +3
THEN 'NO'
when shipments.shipmentdate <> purchaseorderitems.duedate +3
THEN 'YES'
END AS "On Time",
purchaseorderitems.traderquantity AS "Quantity Ordered",
"Quantity Received" = CASE
WHEN shipmentitems.shipmenttype = 'PO'
THEN shipmentitems.stockedquantity
END,
ABS(rejects.stockedquantity) AS "Quantity Rejected",
rejects.originalshipmentid
FROM shipments
LEFT OUTER JOIN shipmentitems ON
shipments.id = shipmentitems.shipmentid
LEFT OUTER JOIN purchaseorderitems ON
shipmentitems.orderid = purchaseorderitems.orderid
AND
shipmentitems.orderitemnumber = purchaseorderitems.itemnumber
INNER JOIN suppliers ON
shipments.traderid = suppliers.id
LEFT OUTER JOIN rejects ON
shipments.id = rejects.originalshipmentid
AND
shipmentitems.itemnumber = rejects.originalshipmentitemnumber
where shipments.shipmentdate BETWEEN
'01 September 2013' AND '30 September 2013'
AND shipmentitems.stockedquantity != '0'
AND shipmentitems.shipmenttype = 'PO'
AND shipmentitems.partid != 'N'
order by
shipments.traderid,
shipmentitems.partid
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply