Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
DHL
Starting Member
16 Posts |
Posted - 2008-05-14 : 08:57:00
|
Hello,I am quite new to SQL, so forgive me if this is a stupid question, but I need to have the answer.I make a join between 2 same tables (i.e. FactSummary), the reason is because this table contains invoice info and payment info, recognized by the field TransactionIDType (AP_INVOICES_ALL for invoices and AP_CHECKS_ALL for payments). As I need to know whether invoices are already paid or not, I need to join FactSummary with Fact_Summary, which is not really a problem. But I am facing following issue:Now I have a case where there is no payment done yet (so no info around this in the table), but there is invoice data... I however do not seem to get any records but I would like to have records without the payment date or where the payment date is empty. Below is my SQL statement...Please help...SELECT FactSummary.ERPInvoiceID, FactSummary.TransactionDate, FactSummary.TransactionIDType, FactSummary.DoxisNumber, FactSummary.Currency, FactSummary.OrgID, FactSummary_1.TransactionIDType AS Expr2, FactSummary_1.TransactionDate AS PaymentDateFROM FactSummary LEFT OUTER JOIN FactSummary AS FactSummary_1 ON FactSummary.ERPInvoiceID = FactSummary_1.ERPInvoiceIDWHERE (FactSummary.ERPInvoiceID = 40343) AND (FactSummary.TransactionIDType = 'AP_INVOICES_ALL') AND (FactSummary_1.TransactionIDType = 'AP_CHECKS_ALL') |
|
singularity
Posting Yak Master
153 Posts |
Posted - 2008-05-14 : 09:02:28
|
SELECT FactSummary.ERPInvoiceID, FactSummary.TransactionDate, FactSummary.TransactionIDType, FactSummary.DoxisNumber, FactSummary.Currency, FactSummary.OrgID, FactSummary_1.TransactionIDType AS Expr2, FactSummary_1.TransactionDate AS PaymentDateFROM FactSummary LEFT OUTER JOIN(SELECT ERPInvoiceID, TransactionIDType AS Expr2, TransactionDate AS PaymentDate FROM FactSummaryWHERE FactSummary_1.TransactionIDType = 'AP_CHECKS_ALL') AS FactSummary_1 ON FactSummary.ERPInvoiceID = FactSummary_1.ERPInvoiceIDWHERE (FactSummary.ERPInvoiceID = 40343) AND (FactSummary.TransactionIDType = 'AP_INVOICES_ALL') |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-05-14 : 09:26:12
|
[code]SELECT fs.ERPInvoiceID, fs.TransactionDate, fs.TransactionIDType, fs.DoxisNumber, fs.Currency, fs.OrgID, d.TransactionIDType AS Expr2, d.TransactionDate AS PaymentDateFROM FactSummary AS fsLEFT JOIN FactSummary AS d ON d.ERPInvoiceID = fs.ERPInvoiceID AND d.TransactionIDType = 'AP_CHECKS_ALL'WHERE fs.ERPInvoiceID = 40343 AND fs.TransactionIDType = 'AP_INVOICES_ALL'[/code] E 12°55'05.25"N 56°04'39.16" |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-05-14 : 09:38:55
|
[code]SELECT FactSummary.ERPInvoiceID, MAX(CASE WHEN FactSummary.TransactionIDType ='AP_INVOICES_ALL' THEN FactSummary.TransactionDate ELSE NULL END) AS TransactionDate, MAX(CASE WHEN FactSummary.TransactionIDType ='AP_INVOICES_ALL' THEN FactSummary.TransactionIDType ELSE NULL END) as TransactionIDType, MAX(CASE WHEN FactSummary.TransactionIDType ='AP_INVOICES_ALL' THEN FactSummary.DoxisNumber ELSE NULL END) as DoxisNumber, MAX(CASE WHEN FactSummary.TransactionIDType ='AP_INVOICES_ALL' THEN FactSummary.Currency ELSE NULL END) as Currency,MAX(CASE WHEN FactSummary.TransactionIDType ='AP_INVOICES_ALL' THEN FactSummary.OrgID ELSE NULL END) as OrgID, MAX(CASE WHEN FactSummary.TransactionIDType ='AP_CHECKS_ALL' THEN FactSummary.TransactionIDType ELSE NULL END)AS Expr2, MAX(CASE WHEN FactSummary.TransactionIDType ='AP_CHECKS_ALL' THEN FactSummary.TransactionDate ELSE NULL END) AS PaymentDateFROM FactSummary WHERE (FactSummary.ERPInvoiceID = 40343) AND (FactSummary.TransactionIDType IN('AP_INVOICES_ALL','AP_CHECKS_ALL'))GROUP BY FactSummary.ERPInvoiceID[/code] |
|
|
DHL
Starting Member
16 Posts |
Posted - 2008-05-14 : 09:39:07
|
Thanks! I think this should be it (solution by Peso). It works at least at first sight, I will do some further testing and if it does not work I will get back on this issue.Thanks! |
|
|
|
|
|
|
|