Link to home
Start Free TrialLog in
Avatar of John Ellis
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!


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]

Open in new window

Avatar of Cameron Miller
Cameron Miller

SELECT x
FROM table
WHERE y IS NOT NULL
Avatar of John Ellis

ASKER

Could you please "expand" upon that?  I'm unclear.

John
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Hi John,
Actually i changed this ... , this part was failing...

CASE WHEN CC_CustomerMaster.BookingDate = '01/01/1900' THEN '' ELSE CONVERT (varchar(10), CC_CustomerMaster.BookingDate, 101)
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
Actually, I need to recall this as being solved.  It still has a problem.

I just found two "NULLs, as shown in the file that I have attached.

Sorry.

User generated image
John
Which column is this ?
Booking Date.
Hi,
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]

Open in new window


Hope it helps!
Ah!  The good old "ISNULL".

As far as I can tell, that worked!  Thanks, again, Pawan!

John
Welcome John. !!