Basic SQL Join Error -- HELP!

eponcede · January 4, 2019, 8:15pm

Hi I have a small database and basically what I want to do is create a new table that combines by date the data in MailOut_Data table and CPE_Invoice_Detail so that I get an output like the NEW TABLE example. Is there a way to do it?

I am also attaching the database setup (bottom right in image)

I have tried JOINS but are not getting anywhere, can somebody point me in the right direction. I am not an SQL expert but have basic knowledge.

Current SQL Statement

SELECT
[MailOutData.csv].[Quantity]
FROM
[MailOutData.csv]
LEFT JOIN [MailOutData.csv] ON [MailOutData.csv].[State] = [CPE_Invoice_Detail.csv].[Invoice State]

ScottPletcher · January 4, 2019, 8:41pm

SELECT
[CID].[State],
[MOD].[Quantity],
[CID].[Invoice Date],
[CID].[Sales Price],
[MOD].[Quantity] * [CID].[Sales Price] AS InvoiceSales
FROM 
[CPE_Invoice_Detail.csv] [CID]
LEFT JOIN [MailOutData.csv] [MOD] ON [MOD].[State] = [CID].[Invoice State] AND
    [MOD].Date = [CID].[Invoice Date]

eponcede · January 4, 2019, 8:55pm

Scott, thanks for the quick response. I tried running the query and getting an error stating that [CID].[State] is not declared in Table 'CID' so my new table needs to be named CID?

ScottPletcher · January 4, 2019, 9:31pm

Nah, I used the wrong column name:

SELECT
[CID].[Invoice State],
[MOD].[Quantity],
[CID].[Invoice Date],
[CID].[Sales Price],
[MOD].[Quantity] * [CID].[Sales Price] AS InvoiceSales
--INTO dbo.new_table_name /* uncomment this line to create the new table */
FROM
[CPE_Invoice_Detail.csv] [CID]
LEFT JOIN [MailOutData.csv] [MOD] ON [MOD].[State] = [CID].[Invoice State] AND
[MOD].Date = [CID].[Invoice Date]

eponcede · January 4, 2019, 10:04pm

Scott,

Not sure I am getting the results as i wanted, is there something different we can try. I am trying to get the data in a table as the Desired Result image, however I am getting the data as in the Current Result Image

ScottPletcher · January 4, 2019, 10:11pm

I think you went back to your original join on only state, and not my join on both state and date.

eponcede · January 4, 2019, 10:43pm

I did not, maybe I am not explaining myself, so I will better with a new example. So what I want to achieve is the CURRENT RESULT TABLE and what I need is if the date in the MailOut_Data table is not in the Invoice_Detail table to add a new row with the mailoutqty.
If in another example the both Invoice_detail and MailOut_Data have the same date, just add the MailOutQty. Is this better?

jeffw8713 · January 6, 2019, 5:01pm

When asking for help on a forum - it is generally considered good practice to provide sample data in the form of table create (or declare) and insert statements. This allows others on the forums to test solutions based on actual data.

Pictures are okay to help describe the issue - but we cannot take a picture and run a query.

With that said - here is a solution, with sample data:

 --==== Sample data
Declare @CPE_Invoice_Detail Table (InvoiceDate date, InvoiceState char(2), SalesPrice numeric(8,2));
Declare @MailOut_Data Table (MailOutState char(2), Quantity int, MailOutDate date);

 Insert Into @CPE_Invoice_Detail (InvoiceDate, InvoiceState, SalesPrice)
 Values ('2018-06-20', 'NE', 149.00)
      , ('2018-06-26', 'NE', 69.00)
      , ('2018-01-18', 'NE', 50.00)
      , ('2018-10-31', 'NE', 139.00)
      , ('2018-09-20', 'NE', 108.00)
      , ('2018-03-28', 'NE', 129.00)
      , ('2018-08-15', 'NE', 129.00)
      , ('2018-09-06', 'WY', -35.60)
      , ('2018-10-02', 'WY', -32.70)
      , ('2018-01-18', 'WY', 50.00)
      , ('2018-05-24', 'WY', 35.00)
      , ('2018-09-26', 'WY', 92.00)
      , ('2018-01-15', 'WY', 49.00)
      , ('2018-05-11', 'WY', 49.00);

 Insert Into @MailOut_Data (MailOutState, Quantity, MailOutDate)
 Values ('NE', 2260, '2018-08-15')
      , ('WY', 562, '2018-08-29');

 --==== Solution
   With allStatesDates
     As (
 Select inv.InvoiceState
      , inv.InvoiceDate
   From @CPE_Invoice_Detail     inv
  Union
 Select md.MailOutState
      , md.MailOutDate
   From @MailOut_Data            md
        )
 Select asd.InvoiceState
      , Quantity = coalesce(md.Quantity, 0)
      , asd.InvoiceDate
      , SalesPrice = coalesce(inv.SalesPrice, 0.00)
   From allStatesDates          asd
   Left Join @CPE_Invoice_Detail        inv On inv.InvoiceState = asd.InvoiceState
                                           And inv.InvoiceDate = asd.InvoiceDate
   Left Join @MailOut_Data               md On md.MailOutState = asd.InvoiceState
                                           And md.MailOutDate = asd.InvoiceDate;

 --==== Second Solution
 Select inv.InvoiceState
      , Quantity = coalesce(md.Quantity, 0)
      , inv.InvoiceDate
      , inv.SalesPrice
   From @CPE_Invoice_Detail             inv
   Left Join @MailOut_Data               md On md.MailOutState = inv.InvoiceState
                                           And md.MailOutDate = inv.InvoiceDate
  Union
 Select md.MailOutState
      , md.Quantity
      , md.MailOutDate
      , SalesPrice = coalesce(inv.SalesPrice, 0.00)
   From @MailOut_Data                    md
   Left Join @CPE_Invoice_Detail        inv On inv.InvoiceState = md.MailOutState
                                           And inv.InvoiceDate = md.MailOutDate

In the sample data - I changed the column names because [State] and [Date] are reserved words and spaces in column names need to be quoted.

The first solution requires creating a list of all possible state/date combinations - then using that as the source of the query. You can then outer join in both of your tables to get the desired results - using coalesce to set the quantity and sales to 0 when not found.

The second solution UNIONS the results of 2 separate queries that return the data from both sets of data.

The UNION removes any duplicate rows that would be returned.

eponcede · January 7, 2019, 9:54pm

Jeff,

Thank you for the suggestion. Trying to update my DB and query I ran into this error.

2019-01-07%2015_52_06-TeamViewer

jeffw8713 · January 7, 2019, 10:44pm

It looks like you are not using Microsoft SQL Server - which does not have a Boolean or Double data types. To get around these issues you can try:

coalesce(md.MailOutQty, cast(0 As int))
coalesce(inv.InvoiceSalesPrice, cast(0.00) as {data type of inv.InvoiceSalesPrice}))

Replace the data between {} with the appropriate data type for the inv.InvoiceSalesPrice column. COALESCE uses the highest data type precedence and then attempts to convert the lower data type to that data type. You can force the conversion to the same data type by defining the second parameter to the same data type as the column.

No guarantees - since I am not sure what version of SQL you are using and what the rules are for that version.

eponcede · January 9, 2019, 7:41pm

is there another way to get around the COALESCE function. I know the InvoiceSalesPrice is of type decimal and the MailOutQty is int, but keep getting errors.

jeffw8713 · January 9, 2019, 8:16pm

What database product are you using? I provided a way of 'forcing' the data types in the COALESCE - is that not working?

eponcede · January 9, 2019, 8:24pm

running the query with the CAST work around

SELECT
[inv].InvoiceState,
[md].MailOutQty = coalesce([md].MailOutQty, cast(0 As int)),
[inv].InvoiceDate,
[inv].InvoiceSalesPrice
FROM [InvoiceDetail.csv] inv
LEFT JOIN [MailOutData.csv] md On [md].MailOutState = [inv].InvoiceState AND [md].MailOutDate = [inv].InvoiceDate
UNION SELECT
[md].MailOutState,
[md].MailOutDate,
[md].MailOutQty,
[inv].InvoiceSalesPrice = coalesce([inv].InvoiceSalesPrice, cast(0.00 as decimal))
FROM [MailOutData.csv] md
LEFT JOIN
[InvoiceDetail.csv] inv On inv.InvoiceState = md.MailOutState And inv.InvoiceDate = md.MailOutDate

and getting this error.

eponcede · January 9, 2019, 8:28pm

it is an OLAP Database (Sisense)

jeffw8713 · January 9, 2019, 9:20pm

Okay - I have no knowledge with Sisense but your first 2 errors are because you flipped the column order in the second query. You are returning the MailOutDate in the column used for the Quantity from the first query - and the MailOutQty in the column for InvoiceDate.

It seems the COALESCE is working for Quantity - maybe changing the 0.00 to 0 will work? You might want to try this: CASE WHEN ISNULL(inv.InvoiceSalesPrice) THEN 0 ELSE inv.InvoiceSalesPrice END)

eponcede · January 9, 2019, 9:37pm

Replaced this line
InvoiceSalesPrice = coalesce(inv.InvoiceSalesPrice, cast(0.00 AS Decimal))
for
InvoiceSalesPrice = CASE WHEN ISNULL(inv.InvoiceSalesPrice) THEN 0 ELSE inv.InvoiceSalesPrice END

on my full query

Select
inv.InvoiceState,
MailOutQty = coalesce(md.MailOutQty, 0),
inv.InvoiceDate,
inv.InvoiceSalesPrice
From [InvoiceDetail.csv] inv
Left Join [MailOutData.csv] md On md.MailOutState = inv.InvoiceState
And md.MailOutDate = inv.InvoiceDate
Union
Select
md.MailOutState,
md.MailOutQty,
md.MailOutDate,
InvoiceSalesPrice = CASE WHEN ISNULL(inv.InvoiceSalesPrice) THEN 0 ELSE inv.InvoiceSalesPrice END
--InvoiceSalesPrice = coalesce(inv.InvoiceSalesPrice, cast(0.00 AS Decimal))
From [MailOutData.csv] md
Left Join [InvoiceDetail.csv] inv On inv.InvoiceState = md.MailOutState
And inv.InvoiceDate = md.MailOutDate

and got a very similar error with the cast.

jeffw8713 · January 9, 2019, 10:04pm

I really cannot help you any further - this is an issue with SiSense and I don't know that system. It seems that the system is trying to cast the output as DECIMAL but it isn't getting a value that it can cast appropriately.

You also changed the MailOutQty back - which did seem to work appropriately. What happens if you use coalesce(md.MailOutQty, cast(0 as int)) in the first query and just return the inv.InvoiceSalesPrice in the second query.

eponcede · January 10, 2019, 2:21pm

Jeff,

I was able to get the query working with the following lines.

coalesce([md].MailOutQty, cast(0 As int)) AS MailOutQty,
coalesce([inv].InvoiceSalesPrice, cast(0 As int)) AS InvoiceSalesPrice

Now, I have an issue where there are some row that are not being counted (in red) any ideas?

jeffw8713 · January 10, 2019, 7:39pm

Sorry - I don't know anything about Sisense.