Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

Help with tables

I have table Collection_Dashboard (accno, del_no,value_Segment, circle, region,NAD_count, BC, Cust_name, last_invoice_date, acc_bal)


Consider  that the above table is my base table, now I will add few calculated columns
Say I have another table Cluster (Cluster,Region)
Now I have new column Cluster from the Cluster table based on Region column.
I did the left outer join and got the cluster column.
Second calculated column:
DELIQUENCY_DAY = DATEDIFF(DD,[LAST_INVOICE_DATE],GETDATE)

Third Calculated Column:
FROM THE NEW DERIVED COLUMN DELIQUENCY_DAY NEW COLUMN IS CALCULATED WHICH IS “OVER DUE” BASED ON CONDITION:-
IF THE DELIQUENCY_DAY COLUMN IS GREATER THAN 21 THEN ‘YES’ ELSE ‘NO’
So at this point I have 3 calculated columns created.
Eventually I would like to have result set as all columns from base table and few calculated columns

What I had done now is I had derived cluster column and delinquency _day from base table and inserted into new table TM_deli. So now my table TM_DELI have fields
(accno, del_no,value_Segment, circle, region,NAD_count, BC, Cust_name, last_invoice_date, acc_bal, cluster,delinquency_day) now at this point I have 2 columns created

To add third column,
With simple case statement I can calculate the OVER DUE column. I stuck up here, do I have to create new table and add this column to it?
Please help me out.



Hope am not confusing you.
0
parpaa
Asked:
parpaa
  • 3
1 Solution
 
parpaaAuthor Commented:
Please find the attachment of my table structures.
I know, this is very wrong way of creating tables, please suggest.
to make things worst, no keys on these tables.
dashboard-table.xlsx
0
 
parpaaAuthor Commented:
Is there any alternative way of calculating these columns and stored in some temp (or something I dont know),  avoiding in creation of multiple tables.
0
 
lcohanDatabase AnalystCommented:
You should be able to get the third column with a simple CASE like below:

CASE WHEN DELIQUENCY_DAY > 21 THEN 'YES' ELSE 'NO' END AS column_name

FROM ....
0
 
parpaaAuthor Commented:
this task is not required anymore.

Cheers
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now