November 30, 2007 at 7:06 am
I want to add one more column to the results of this query. Right now the results only show the last_invoice_no and customer_id columns. I'd like to include the CUSTOMER_NAME column from DBO.P21_VIEW_CUSTOMER
Can anyone give me a hand with how to modify this?
SELECT MAX(COALESCE (ih.max_P21_no, iha.max_acclaim_no)) AS last_invoice_no, cs.customer_id
FROM (SELECT customer_id, MAX(invoice_no) max_P21_no FROM dbo.p21_view_invoice_hdr
WHERE invoice_no <= 9999999
GROUP BY customer_id) ih
RIGHT OUTER JOIN dbo.p21_view_customer cs ON ih.customer_id = cs.customer_id
LEFT OUTER JOIN (SELECT customer_id, MAX(invoice_no) max_acclaim_no
FROM dbo.p21_view_invoice_hdr
WHERE invoice_no > 9999999
GROUP BY customer_id) iha ON iha.customer_id = cs.customer_id
WHERE (cs.delete_flag = 'N')
GROUP BY ih.customer_id, cs.customer_id
HAVING (NOT (MAX(COALESCE (ih.max_P21_no, iha.max_acclaim_no)) IS NULL))
November 30, 2007 at 7:15 am
Try having the following select part:
SELECT MAX(COALESCE(ih.max_P21_no, iha.max_acclaim_no)) AS last_invoice_no
, cs.customer_id
, max(cs.CUSTOMER_NAME)
Regards,
Andras
November 30, 2007 at 7:25 am
Although, you can just add the required column in your select list but this would not scale well in production.
Here is the optimized way of it...
SELECTcs.*, l.invoice_no as last_invoice_no
FROMdbo.p21_view_customer cs
INNER JOIN
(
SELECTcustomer_id, invoice_no, invtype,
ROW_NUMBER OVER( PARTITION BY customer_id ORDER BY invtype, invoice_no DESC ) AS RowNum
FROM(
SELECTcustomer_id, invoice_no,
( case when invoice_no <= 9999999 then 1 else 0 end ) as invtype
FROMdbo.p21_view_invoice_hdr
) A
) l on cs.customer_id = l.customer_id and cs.RowNum = 1
WHEREcs.delete_flag = 'N'
--Ramesh
November 30, 2007 at 7:40 am
Andras - that worked, thanks
Rameesh - I tried the query but it failed with this error:
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'OVER'.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'A'.
November 30, 2007 at 7:43 am
bjohnson (11/30/2007)
Andras - that worked, thanksRameesh - I tried the query but it failed with this error:
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'OVER'.
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near 'A'.
Glad I could help, but in the long term Ramesh's solution is the better way (it is worth rewriting your query).
Try adding () to the ROW_NUMBER, so something like: ...ROW_NUMBER() OVER( PARTITION BY...
Regards,
Andras
November 30, 2007 at 7:44 am
Also, what would be the proper way to add the INVOICE_DATE column from P21_view_invoice_hdr and the DATE_ACCT_OPENED column from P21_view_customer ?
Thanks in advance for your help guys!
November 30, 2007 at 7:45 am
Too much coffee late in the day....:w00t::w00t:
Missed parenthesis....
SELECTcs.*, l.invoice_no as last_invoice_no
FROMdbo.p21_view_customer cs
INNER JOIN
(
SELECTcustomer_id, invoice_no, invtype,
ROW_NUMBER() OVER( PARTITION BY customer_id ORDER BY invtype, invoice_no DESC ) AS RowNum
FROM(
SELECTcustomer_id, invoice_no,
( case when invoice_no <= 9999999 then 1 else 0 end ) as invtype
FROMdbo.p21_view_invoice_hdr
)
) l on cs.customer_id = l.customer_id and cs.RowNum = 1
WHEREcs.delete_flag = 'N'
--Ramesh
November 30, 2007 at 7:47 am
Ramesh, I ran the modified query and it returned:
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near ')'.
November 30, 2007 at 7:56 am
Darn.........
SELECTcs.*, l.invoice_no as last_invoice_no
FROMdbo.p21_view_customer cs
INNER JOIN
(
SELECTcustomer_id, invoice_no, invtype,
ROW_NUMBER() OVER( PARTITION BY customer_id ORDER BY invtype, invoice_no DESC ) AS RowNum
FROM(
SELECTcustomer_id, invoice_no,
( case when invoice_no <= 9999999 then 1 else 0 end ) as invtype
FROMdbo.p21_view_invoice_hdr
) a
) l on cs.customer_id = l.customer_id and l.RowNum = 1
WHEREcs.delete_flag = 'N'
--Ramesh
November 30, 2007 at 8:02 am
I realized I can just replace cs.* with any of the p21_view_customer columns I want - which is great! 😀
Now I just need to figure out how to add the invoice_date from p21_view_invoice_hdr back in their as well.
November 30, 2007 at 8:19 am
Trying to understand your data I think you will find this should be a simplified version of your original post plus the extras you asked for.
I assume thou Customer ID applies to only one customer name with a single DATE_ACCT_OPENED value.
But I had a question based on your concept for Last Invoice Number value. How do you determine a particular is the last.
In your code you do this
MAX(COALESCE (ih.max_P21_no, iha.max_acclaim_no)) AS last_invoice_no
which if you look at my changes below I realized there is a potential transitive relationship between you derived tables based on whether invoice_no 9999999 which lines up along the customer information if there is a match in both cases. What would define which is the value to use in that case and are invoices always going to be increasing values guarteeing the last invoice is the greatest value, in which case you derive the below query then join back to the invoice view to get the date of the invoice.
------Exmample
SELECT
CS.customer_id,
CS.customer_name,
CS.DATE_ACCT_OPENED,
Coalesce(
MAX(CASE WHEN INV.invoice_no <= 9999999 THEN INV.invoice_no ELSE NULL END),
MAX(CASE WHEN INV.invoice_no > 9999999 THEN INV.invoice_no ELSE NULL END)
) last_invoice_no
--,MAX(INVOICE_DATE) INVOICE_DATE -Questionable based on last_invoice_no value.
FROM
dbo.p21_view_customer CS
LEFT JOIN
dbo.p21_view_invoice_hdr INV
ON
INV.customer_id = CS.customer_id
WHERE
CS.delete_flag = 'N'
GROUP BY
CS.customer_id,
CS.customer_name,
CS.DATE_ACCT_OPENED
HAVING
Coalesce(
MAX(CASE WHEN INV.invoice_no <= 9999999 THEN INV.invoice_no ELSE NULL END),
MAX(CASE WHEN INV.invoice_no > 9999999 THEN INV.invoice_no ELSE NULL END)
) IS NOT NULL
November 30, 2007 at 8:27 am
The invoices numbers pose a bit of a problem at the moment as we just migrated data from one an old business application to a new one and the invoice numbers are not consistent between the two.
The old system recording the invoice numbers as 9 digits with the highest (most recent) going up to 75xxxxxxx while the new system has 7 digit invoice numbers that start in the 50xxxxx range and go up from there.
My original query was falsely identifying the old system's 9 digit invoice numbers as the "last invoice number" even though there were newer 7 digit invoice numbers for the same customer.
Another user of the business app provided me with the TSQL in my original post and to be totally honest I'm not really a TSQL programmer so the context of the code is over my head, I'm just trying to hack it together to make it work :hehe:
November 30, 2007 at 8:27 am
Sorry just occurred to me you don't need to have the HAVIN clause if you use an INNER JOIN as that is the net affect of the inner join is getting rid of any account where no invoice exists. So here is an updated version of mine
------Exmample
SELECT
CS.customer_id,
CS.customer_name,
CS.DATE_ACCT_OPENED,
Coalesce(
MAX(CASE WHEN INV.invoice_no <= 9999999 THEN INV.invoice_no ELSE NULL END),
MAX(CASE WHEN INV.invoice_no > 9999999 THEN INV.invoice_no ELSE NULL END)
) last_invoice_no
--,MAX(INVOICE_DATE) INVOICE_DATE -Questionable based on last_invoice_no value.
FROM
dbo.p21_view_customer CS
INNER JOIN
dbo.p21_view_invoice_hdr INV
ON
INV.customer_id = CS.customer_id
WHERE
CS.delete_flag = 'N'
GROUP BY
CS.customer_id,
CS.customer_name,
CS.DATE_ACCT_OPENED
November 30, 2007 at 8:46 am
OK then this might do the trick.
SELECT
X.*,
INVA.invoice_no
FROM
dbo.p21_view_invoice_hdr INVA
INNER JOIN
(
SELECT
CS.customer_id,
CS.customer_name,
CS.DATE_ACCT_OPENED,
,MAX(INVOICE_DATE) INVOICE_DATE
FROM
dbo.p21_view_customer CS
INNER JOIN
dbo.p21_view_invoice_hdr INV
ON
INV.customer_id = CS.customer_id
WHERE
CS.delete_flag = 'N'
GROUP BY
CS.customer_id,
CS.customer_name,
CS.DATE_ACCT_OPENED
) X
ON
X.customer_id = INVA.customer_id AND
X.INVOICE_DATE = INVA.INVOICE_DATE
November 30, 2007 at 8:48 am
Sorry had a typo and the edit feature never seems to work for me here.
SELECT
X.*,
INVA.invoice_no
FROM
dbo.p21_view_invoice_hdr INVA
INNER JOIN
(
SELECT
CS.customer_id,
CS.customer_name,
CS.DATE_ACCT_OPENED,
MAX(INVOICE_DATE) INVOICE_DATE
FROM
dbo.p21_view_customer CS
INNER JOIN
dbo.p21_view_invoice_hdr INV
ON
INV.customer_id = CS.customer_id
WHERE
CS.delete_flag = 'N'
GROUP BY
CS.customer_id,
CS.customer_name,
CS.DATE_ACCT_OPENED
) X
ON
X.customer_id = INVA.customer_id AND
X.INVOICE_DATE = INVA.INVOICE_DATE
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply