John Ellis
asked on
T-SQL: Sigh---Boy, this is fun....
How do I tell SQL to not show "NULL" for blank dates?
Is there a "street-version" of T-SQL or are we limited to this programming language that refuses to speak a normal language? LOL!
Is there a "street-version" of T-SQL or are we limited to this programming language that refuses to speak a normal language? LOL!
select [Salesforce Opportunity ID #],
[GreatPlains Acct Number],
[GreatPlains Customer Name],
[Sales Rep],
[Booking Amount],
[Booking Date],
[First Invoice Date], [Oct-15], [Nov-15], [Dec-15], [Jan-16],
[Feb-16], [Mar-16], [Apr-16], [May-16], [Jun-16], [Jul-16],
[Aug-16], [Sep-16], [Oct-16], [Nov-16], [Dec-16], [Jan-17], [Feb-17]
FROM(
SELECT TOP 100 PERCENT * FROM
(
select CC_CustomerMaster.HR_OPID as [Salesforce Opportunity ID #],
CC_CustomerMaster.CustomerId as [GreatPlains Acct Number],
CC_CustomerMaster.CustomerName as [GreatPlains Customer Name],
CC_InvoicedOrderLines.Salesperson1Id as [Sales Rep],
CC_InvoicedOrderLines.Amount as [Booking Amount],
CASE CONVERT (varchar(10), CC_CustomerMaster.BookingDate, 101) WHEN '01/01/1900' THEN '' ELSE
CONVERT (varchar(10), CC_CustomerMaster.BookingDate, 101)
END as [Booking Date],
CASE CONVERT (varchar(10), CC_CustomerMaster.FirstInvoiceDate, 101) WHEN '01/01/1900' THEN '' ELSE
CONVERT (varchar(10), CC_CustomerMaster.FirstInvoiceDate, 101)
END as [First Invoice Date], 0 as [Oct-15], 0 as [Nov-15], 0 as [Dec-15], 0 as [Jan-16],
0 as [Feb-16], 0 as [Mar-16], 0 as [Apr-16], 0 as [May-16], 0 as [Jun-16], 0 as [Jul-16],
0 as [Aug-16], 0 as [Sep-16], 0 as [Oct-16], 0 as [Nov-16], 0 as [Dec-16], 0 as [Jan-17],
0 as [Feb-17]
from CC_CustomerMaster
INNER JOIN CC_ArTransactions on
CC_CustomerMaster.CustomerId = CC_ArTransactions.CustomerId
LEFT OUTER JOIN CC_InvoicedOrderLines on
CC_ArTransactions.ArDocId= CC_InvoicedOrderLines.ArDocId
where left(CC_InvoicedOrderLines.ArDocId, 2) = 'BK'
--and CC_CustomerMaster.HR_OPID = '0063200001nbXeC'
UNION ALL
select CC_CustomerMaster.HR_OPID as [Salesforce Opportunity ID #],
CC_CustomerMaster.CustomerId as [GreatPlains Acct Number],
CC_CustomerMaster.CustomerName as [GreatPlains Customer Name],
CC_InvoicedOrderLines.Salesperson1Id as [Sales Rep],
0 as [Booking Amount],
CASE CONVERT (varchar(10), CC_CustomerMaster.BookingDate, 101) WHEN '01/01/1900' THEN '' ELSE
CONVERT (varchar(10), CC_CustomerMaster.BookingDate, 101)
END as [Booking Date],
CASE CONVERT (varchar(10), CC_CustomerMaster.FirstInvoiceDate, 101) WHEN '01/01/1900' THEN '' ELSE
CONVERT (varchar(10), CC_CustomerMaster.FirstInvoiceDate, 101)
END as [First Invoice Date],
CASE WHEN CC_InvoicedOrderHeader.DocDate BETWEEN '10/01/2015' AND '10/31/2015'
THEN SUM(CC_InvoicedOrderHeader.Amount) ELSE 0 END AS [Oct-15],
CASE WHEN CC_InvoicedOrderHeader.DocDate BETWEEN '11/01/2015' AND '11/30/2015'
THEN SUM(CC_InvoicedOrderHeader.Amount) ELSE 0 END AS [Nov-15],
CASE WHEN CC_InvoicedOrderHeader.DocDate BETWEEN '12/01/2015' AND '12/31/2015'
THEN SUM(CC_InvoicedOrderHeader.Amount) ELSE 0 END AS [Dec-15],
CASE WHEN CC_InvoicedOrderHeader.DocDate BETWEEN '01/01/2016' AND '01/31/2016'
THEN SUM(CC_InvoicedOrderHeader.Amount) ELSE 0 END AS [Jan-16],
CASE WHEN CC_InvoicedOrderHeader.DocDate BETWEEN '02/01/2016' AND '02/29/2016'
THEN SUM(CC_InvoicedOrderHeader.Amount) ELSE 0 END AS [Feb-16],
CASE WHEN CC_InvoicedOrderHeader.DocDate BETWEEN '03/01/2016' AND '03/31/2016'
THEN SUM(CC_InvoicedOrderHeader.Amount) ELSE 0 END AS [Mar-16],
CASE WHEN CC_InvoicedOrderHeader.DocDate BETWEEN '04/01/2016' AND '04/30/2016'
THEN SUM(CC_InvoicedOrderHeader.Amount) ELSE 0 END AS [Apr-16],
CASE WHEN CC_InvoicedOrderHeader.DocDate BETWEEN '05/01/2016' AND '05/31/2016'
THEN SUM(CC_InvoicedOrderHeader.Amount) ELSE 0 END AS [May-16],
CASE WHEN CC_InvoicedOrderHeader.DocDate BETWEEN '06/01/2016' AND '06/30/2016'
THEN SUM(CC_InvoicedOrderHeader.Amount) ELSE 0 END AS [Jun-16],
CASE WHEN CC_InvoicedOrderHeader.DocDate BETWEEN '07/01/2016' AND '07/31/2016'
THEN SUM(CC_InvoicedOrderHeader.Amount) ELSE 0 END AS [Jul-16],
CASE WHEN CC_InvoicedOrderHeader.DocDate BETWEEN '08/01/2016' AND '08/31/2016'
THEN SUM(CC_InvoicedOrderHeader.Amount) ELSE 0 END AS [Aug-16],
CASE WHEN CC_InvoicedOrderHeader.DocDate BETWEEN '09/01/2016' AND '09/30/2016'
THEN SUM(CC_InvoicedOrderHeader.Amount) ELSE 0 END AS [Sep-16],
CASE WHEN CC_InvoicedOrderHeader.DocDate BETWEEN '10/01/2016' AND '10/31/2016'
THEN SUM(CC_InvoicedOrderHeader.Amount) ELSE 0 END AS [Oct-16],
CASE WHEN CC_InvoicedOrderHeader.DocDate BETWEEN '11/01/2016' AND '11/30/2016'
THEN SUM(CC_InvoicedOrderHeader.Amount) ELSE 0 END AS [Nov-16],
CASE WHEN CC_InvoicedOrderHeader.DocDate BETWEEN '12/01/2016' AND '12/31/2016'
THEN SUM(CC_InvoicedOrderHeader.Amount) ELSE 0 END AS [Dec-16],
CASE WHEN CC_InvoicedOrderHeader.DocDate BETWEEN '01/01/2017' AND '01/31/2017'
THEN SUM(CC_InvoicedOrderHeader.Amount) ELSE 0 END AS [Jan-17],
CASE WHEN CC_InvoicedOrderHeader.DocDate BETWEEN '02/01/2017' AND '02/28/2017'
THEN SUM(CC_InvoicedOrderHeader.Amount) ELSE 0 END AS [Feb-17]
from CC_CustomerMaster
INNER JOIN CC_ArTransactions on
CC_CustomerMaster.CustomerId = CC_ArTransactions.CustomerId
LEFT OUTER JOIN CC_InvoicedOrderLines on
CC_ArTransactions.ArDocId= CC_InvoicedOrderLines.ArDocId
INNER JOIN CC_InvoicedOrderHeader on
CC_InvoicedOrderLines.ArDocId= CC_InvoicedOrderHeader.ArDocId
where CC_InvoicedOrderHeader.OPType <> 'B'
--and CC_CustomerMaster.HR_OPID = '0063200001nbXeC'
GROUP BY CC_CustomerMaster.HR_OPID, CC_CustomerMaster.CustomerId, CC_CustomerMaster.CustomerName,
CC_InvoicedOrderLines.Salesperson1Id, CC_CustomerMaster.BookingDate, CC_CustomerMaster.FirstInvoiceDate,
CC_InvoicedOrderHeader.DocDate)
AS
DATADUMP
)
As
YIELD
ORDER BY [Sales Rep]
ASKER
Could you please "expand" upon that? I'm unclear.
John
John
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That worked, Pawan! Thank you! How did you do that? SQL ain't nowhere near as nice to me, when I look up articles on how to fix such things. That's why I've given up on understanding T-SQL.
John
John
Hi John,
Actually i changed this ... , this part was failing...
CASE WHEN CC_CustomerMaster.BookingD ate = '01/01/1900' THEN '' ELSE CONVERT (varchar(10), CC_CustomerMaster.BookingD ate, 101)
Actually i changed this ... , this part was failing...
CASE WHEN CC_CustomerMaster.BookingD
ASKER
Well, I could have sworn that I did that at one point. But, SQL gave me the raspberry, as usual. It doesn't like me. Technology, in general, never has. That's why I have given up on trying to understand it. Why waste valuable brain space on trying to understand something that doesn't want to work with you.
Like I said, there's always politics. I was always good, at that.
Thanks, so much, Pawan!
John
Like I said, there's always politics. I was always good, at that.
Thanks, so much, Pawan!
John
ASKER
Which column is this ?
ASKER
Booking Date.
Hi,
Pls try this..
Hope it helps!
Pls try this..
select [Salesforce Opportunity ID #],
[GreatPlains Acct Number],
[GreatPlains Customer Name],
[Sales Rep],
[Booking Amount],
[Booking Date],
[First Invoice Date], [Oct-15], [Nov-15], [Dec-15], [Jan-16],
[Feb-16], [Mar-16], [Apr-16], [May-16], [Jun-16], [Jul-16],
[Aug-16], [Sep-16], [Oct-16], [Nov-16], [Dec-16], [Jan-17], [Feb-17]
FROM(
SELECT TOP 100 PERCENT * FROM
(
select CC_CustomerMaster.HR_OPID as [Salesforce Opportunity ID #],
CC_CustomerMaster.CustomerId as [GreatPlains Acct Number],
CC_CustomerMaster.CustomerName as [GreatPlains Customer Name],
CC_InvoicedOrderLines.Salesperson1Id as [Sales Rep],
CC_InvoicedOrderLines.Amount as [Booking Amount],
ISNULL(CASE WHEN CC_CustomerMaster.BookingDate = '01/01/1900' THEN '' ELSE
CONVERT (varchar(10), CC_CustomerMaster.BookingDate, 101)
END,'') as [Booking Date],
CASE WHEN CC_CustomerMaster.FirstInvoiceDate = '01/01/1900' THEN '' ELSE
CONVERT (varchar(10), CC_CustomerMaster.FirstInvoiceDate, 101)
END as [First Invoice Date], 0 as [Oct-15], 0 as [Nov-15], 0 as [Dec-15], 0 as [Jan-16],
0 as [Feb-16], 0 as [Mar-16], 0 as [Apr-16], 0 as [May-16], 0 as [Jun-16], 0 as [Jul-16],
0 as [Aug-16], 0 as [Sep-16], 0 as [Oct-16], 0 as [Nov-16], 0 as [Dec-16], 0 as [Jan-17],
0 as [Feb-17]
from CC_CustomerMaster
INNER JOIN CC_ArTransactions on
CC_CustomerMaster.CustomerId = CC_ArTransactions.CustomerId
LEFT OUTER JOIN CC_InvoicedOrderLines on
CC_ArTransactions.ArDocId= CC_InvoicedOrderLines.ArDocId
where left(CC_InvoicedOrderLines.ArDocId, 2) = 'BK'
--and CC_CustomerMaster.HR_OPID = '0063200001nbXeC'
UNION ALL
select CC_CustomerMaster.HR_OPID as [Salesforce Opportunity ID #],
CC_CustomerMaster.CustomerId as [GreatPlains Acct Number],
CC_CustomerMaster.CustomerName as [GreatPlains Customer Name],
CC_InvoicedOrderLines.Salesperson1Id as [Sales Rep],
0 as [Booking Amount],
CASE WHEN CC_CustomerMaster.BookingDate = '01/01/1900' THEN ''
ELSE CONVERT(varchar(10), CC_CustomerMaster.BookingDate, 101) END
[Booking Date],
CASE WHEN CC_CustomerMaster.FirstInvoiceDate = '01/01/1900' THEN ''
ELSE CONVERT (varchar(10), CC_CustomerMaster.FirstInvoiceDate, 101)
END [First Invoice Date],
CASE WHEN CC_InvoicedOrderHeader.DocDate BETWEEN '10/01/2015' AND '10/31/2015'
THEN SUM(CC_InvoicedOrderHeader.Amount) ELSE 0 END AS [Oct-15],
CASE WHEN CC_InvoicedOrderHeader.DocDate BETWEEN '11/01/2015' AND '11/30/2015'
THEN SUM(CC_InvoicedOrderHeader.Amount) ELSE 0 END AS [Nov-15],
CASE WHEN CC_InvoicedOrderHeader.DocDate BETWEEN '12/01/2015' AND '12/31/2015'
THEN SUM(CC_InvoicedOrderHeader.Amount) ELSE 0 END AS [Dec-15],
CASE WHEN CC_InvoicedOrderHeader.DocDate BETWEEN '01/01/2016' AND '01/31/2016'
THEN SUM(CC_InvoicedOrderHeader.Amount) ELSE 0 END AS [Jan-16],
CASE WHEN CC_InvoicedOrderHeader.DocDate BETWEEN '02/01/2016' AND '02/29/2016'
THEN SUM(CC_InvoicedOrderHeader.Amount) ELSE 0 END AS [Feb-16],
CASE WHEN CC_InvoicedOrderHeader.DocDate BETWEEN '03/01/2016' AND '03/31/2016'
THEN SUM(CC_InvoicedOrderHeader.Amount) ELSE 0 END AS [Mar-16],
CASE WHEN CC_InvoicedOrderHeader.DocDate BETWEEN '04/01/2016' AND '04/30/2016'
THEN SUM(CC_InvoicedOrderHeader.Amount) ELSE 0 END AS [Apr-16],
CASE WHEN CC_InvoicedOrderHeader.DocDate BETWEEN '05/01/2016' AND '05/31/2016'
THEN SUM(CC_InvoicedOrderHeader.Amount) ELSE 0 END AS [May-16],
CASE WHEN CC_InvoicedOrderHeader.DocDate BETWEEN '06/01/2016' AND '06/30/2016'
THEN SUM(CC_InvoicedOrderHeader.Amount) ELSE 0 END AS [Jun-16],
CASE WHEN CC_InvoicedOrderHeader.DocDate BETWEEN '07/01/2016' AND '07/31/2016'
THEN SUM(CC_InvoicedOrderHeader.Amount) ELSE 0 END AS [Jul-16],
CASE WHEN CC_InvoicedOrderHeader.DocDate BETWEEN '08/01/2016' AND '08/31/2016'
THEN SUM(CC_InvoicedOrderHeader.Amount) ELSE 0 END AS [Aug-16],
CASE WHEN CC_InvoicedOrderHeader.DocDate BETWEEN '09/01/2016' AND '09/30/2016'
THEN SUM(CC_InvoicedOrderHeader.Amount) ELSE 0 END AS [Sep-16],
CASE WHEN CC_InvoicedOrderHeader.DocDate BETWEEN '10/01/2016' AND '10/31/2016'
THEN SUM(CC_InvoicedOrderHeader.Amount) ELSE 0 END AS [Oct-16],
CASE WHEN CC_InvoicedOrderHeader.DocDate BETWEEN '11/01/2016' AND '11/30/2016'
THEN SUM(CC_InvoicedOrderHeader.Amount) ELSE 0 END AS [Nov-16],
CASE WHEN CC_InvoicedOrderHeader.DocDate BETWEEN '12/01/2016' AND '12/31/2016'
THEN SUM(CC_InvoicedOrderHeader.Amount) ELSE 0 END AS [Dec-16],
CASE WHEN CC_InvoicedOrderHeader.DocDate BETWEEN '01/01/2017' AND '01/31/2017'
THEN SUM(CC_InvoicedOrderHeader.Amount) ELSE 0 END AS [Jan-17],
CASE WHEN CC_InvoicedOrderHeader.DocDate BETWEEN '02/01/2017' AND '02/28/2017'
THEN SUM(CC_InvoicedOrderHeader.Amount) ELSE 0 END AS [Feb-17]
from CC_CustomerMaster
INNER JOIN CC_ArTransactions on
CC_CustomerMaster.CustomerId = CC_ArTransactions.CustomerId
LEFT OUTER JOIN CC_InvoicedOrderLines on
CC_ArTransactions.ArDocId= CC_InvoicedOrderLines.ArDocId
INNER JOIN CC_InvoicedOrderHeader on
CC_InvoicedOrderLines.ArDocId= CC_InvoicedOrderHeader.ArDocId
where CC_InvoicedOrderHeader.OPType <> 'B'
--and CC_CustomerMaster.HR_OPID = '0063200001nbXeC'
GROUP BY CC_CustomerMaster.HR_OPID, CC_CustomerMaster.CustomerId, CC_CustomerMaster.CustomerName,
CC_InvoicedOrderLines.Salesperson1Id, CC_CustomerMaster.BookingDate, CC_CustomerMaster.FirstInvoiceDate,
CC_InvoicedOrderHeader.DocDate)
AS
DATADUMP
)
As
YIELD
ORDER BY [Sales Rep]
Hope it helps!
ASKER
Ah! The good old "ISNULL".
As far as I can tell, that worked! Thanks, again, Pawan!
John
As far as I can tell, that worked! Thanks, again, Pawan!
John
Welcome John. !!
FROM table
WHERE y IS NOT NULL