Date Join Issue

Question

Date Join Issue

nymets86

SSC Enthusiast

Points: 191
More actions
June 4, 2020 at 6:57 pm

#3760618

I joined two tables together trying to get a field out of one and inserted into the other. The field ABC is a total. It has to be linked to the other table by a date. The other table has a Y which represents Year and a M which represents month. The table I am connecting has only a char(8) field which displays by the following for example:
NOV-FY19
DEC-FY19
JAN-FY20
So TABLE A has the date listed as JAN-FY20 while TABLE B has a column Y 20 and M 1
Is there a way to format this column and then concatenate and link to the other table so when a match is found the total will go into that area of the data?
So M=1 and Y=20 from Table A and reformatting that second one to 1-20 or something on the second one so that total for the specific month populates the table? I do not have write access to the database so it will have to be something embedded in the code. I am what I would consider a novice/advanced novice (if that makes sense) at this so please take that into consideration. Any input would be greatly appreciated. Thanks.
June 5, 2020 at 3:32 am

This was removed by the editor as SPAM

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

Can you provide sample data and tables that we can use?

BTW - you are not comparing dates. These are not dates they are strings that may represent some type of value that can be interpreted as a date but they are not. So your question is really about how to format or parse the strings so they will match.

To do that, you either have to parse JAN-FY20 into separate Y and M columns - or parse the 'Y 20' and 'M 1' data into a single value to match the other table.

Either way - this is not going to perform well for a large set of data. A better method would be to create a derived column on each table - that converts the string data into an actual date. You can then persist and index that column in each table and join on the derived column.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

nymets86 SSC Enthusiast Points: 191 More actions · Answer 2

I cant provide anything specific to tables for sensitivity reasons but can explain in detail below.

TABLE1 has Y for Year and M for Month. It also is the main table for the code.

TABLE2 has a value in it that I need lets call it Total.

Both tables have a ID that can connect each together but I need to connect the rows by the dates in each (Table1 (Y, M) and Table 2 (JANFY20 or char(8) field).

I kinda understand what you are saying but am lost a little. In the table with the total that I need I would have to reformat that date field. I cannot physically change anything in the table so it would have to be in my SQL code. So if that is accurate, you are suggesting I do the join, the add a column reformatting Y, M into one field, then reformat the char(8) date in the other table in the code, then link those together in a join? Apologies if I am way off but again not an expert by any means.

frederico_fonseca SSCoach Points: 16155 More actions · Answer 3

I'm sure you can do a sample create table and insert statements of sample data to help us help you.

field names don't even need to mean anything so no excuse for not doing it for sensitive reasons (or any other reason)

nymets86 SSC Enthusiast Points: 191 More actions · Answer 4

I am new to this and appreciate the help. Of course I would like to provide whatever I can. The only thing I did to this point was join the tables. That is it. I can paste that into here but that is all there is and I was concerned about doing that but will just rename it and show below. In addition I can specify exactly what the field types are.

select

T2.TOTAL

FROM TABLE1.counts t1

INNER JOIN TABLE2.counts as t2 on T1.ID = T2.ID where T2.Mth = 'APR-FY20'

I just put the month in for now, but here are the field types.

TABLE1 Month is an integer that lists date as follows 202001 for January 2020 ( I made a mistake in detailing that earlier. This is the format)

TABLE2 Month is a char(8) but lists same date as JANFY20. I would need to convert that JAN to a 1, disregard the FY for fiscal year, and the 20 to 2020.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 5

There is an article that I link to about posting - please review that link. It shows how to provide sample data that we can utilize to build a working and tested query.

I cannot use what you have provided because you reference TABLE1.counts - which isn't really a table and you reference a column that might have 202001 or it might have Y and M or something else...it isn't clear. The other column might be JANFY20 - or it could be JAN-FY20...not sure.

Either way - you don't have a date...you have strings that need to be compared.

The other problem is that you have an ID column in both tables. If you join on that column then why would you need to join on any other columns? We cannot tell because we can't see your tables...so you need to help us understand further.

Declare @table1 Table (ID int, mth int);
Declare @table2 Table (ID int, month_string char(8));

 Insert Into @table1 (ID, mth)
 Values (1, '202001')
      , (2, '202002');

 Insert Into @table2 (ID, month_string)
 Values (1, 'JAN-FY20')
      , (2, 'FEB-FY20');

 Select *
   From @table1         t1
  Inner Join @table2    t2 On t2.ID = t1.ID
                          And convert(char(6), convert(date, '01-' + replace(t2.month_string, 'FY', '20')), 112) = t1.mth

If the values you show are correct - this is one way to convert. It will not perform well - as I stated before.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

nymets86 SSC Enthusiast Points: 191 More actions · Answer 6

Jeffrey:

Thanks for your patience. I will review the procedures for posting questions and the code you submitted to help me. About the joining ID to ID...I guess I wanted to do that join then ensure that the month total from the one table went into the correct spot in the other table by the month. I will review your suggested procedures for posting and then respond tomorrow. Again thanks for the help.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 7

nymets86 wrote:

I cant provide anything specific to tables for sensitivity reasons but can explain in detail below.
TABLE1 has Y for Year and M for Month. It also is the main table for the code.
TABLE2 has a value in it that I need lets call it Total.
Both tables have a ID that can connect each together but I need to connect the rows by the dates in each (Table1 (Y, M) and Table 2 (JANFY20 or char(8) field).
I kinda understand what you are saying but am lost a little. In the table with the total that I need I would have to reformat that date field. I cannot physically change anything in the table so it would have to be in my SQL code. So if that is accurate, you are suggesting I do the join, the add a column reformatting Y, M into one field, then reformat the char(8) date in the other table in the code, then link those together in a join? Apologies if I am way off but again not an expert by any means.

So knock up an example of "readily consumable data" that represents what you're trying to describe. It should include the CREATE TABLE statements for each of your example tables and INSERT/VALUES to populate them. Jeffrey Williams provides an example above. See the first link in my signature line below for a different way to extract just the data for your example (just dates in this case and so is NOT a security risk in any way, shape, or form).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 8

nymets86 wrote:

Jeffrey:
Thanks for your patience. I will review the procedures for posting questions and the code you submitted to help me. About the joining ID to ID...I guess I wanted to do that join then ensure that the month total from the one table went into the correct spot in the other table by the month. I will review your suggested procedures for posting and then respond tomorrow. Again thanks for the help.

Don't think of it as a procedure or requirement for posting. It's all about helping you get a good answer. When we don't have any code and just vague descriptions, it's really hard to give a meaningful and helpful answer. We really want to help. That's why we're asking for example code. It's not a punishment or homework. It's to try to help you better.

Thanks for posting. I hope Jeffrey's code does the trick.

This reply was modified 4 years, 5 months ago by Grant Fritchey.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

nymets86 SSC Enthusiast Points: 191 More actions · Answer 9

Yes I understand. I will follow the procedure going forward. Let me go over his post today and familiarize myself with it before proceeding. Thanks guys!