We help IT Professionals succeed at work.

Database problem

cvbmn
cvbmn asked
on
Medium Priority
194 Views
Last Modified: 2010-04-06
Database problem

Here are two database problems :

->1st.

--------------------------
   CustField     DateField
--------------------------
1  Cust1         01/01/01
2  Cust2         02/01/01
3  Cust3         03/01/01
4  Cust4         04/01/01
5  05/01/01      Cust5
6  06/01/01      Cust6
7  07/01/01      Cust7
8  08/01/01      Cust8
--------------------------

CustField and DateField are string type. How with SQL to sort (order) it by Date and Cust using CustField and DateField as one ? Or You know some beter solutions (not SQL).

->2nd.

---------------------------
   Date1Field    Date2Field
---------------------------
1                01/01/01
2                02/01/01
3                03/01/01
4                04/01/01
5  05/01/01      
6  06/01/01      
7  07/01/01      
8  08/01/01      
---------------------------

Date1Field and Date2Field are date type. How with SQL to sort (order) it as one ? Or You know some beter solutions (not SQL).

Thanks.
Comment
Watch Question

Commented:
what did you mean by (as one)???
 


 

Author

Commented:
That datas in 1. field and 2. field are as one field. Tanks.

Commented:
For part 1:
If you mean you want to concatenate (combine) the two fields, the following would work:

Select (CustField+DateField) as CustDate from TABLE order by (CustField+DateField)


From what I've seen, you can't use the alias name of a calculated field in an Order By



I have no idea what you mean by "Date1Field and Date2Field are date type. How with SQL to sort (order) it as one ? "

What are you attempting to accomplish?

Commented:
you meen to merge  them in one filed like this

NewField
------------
  Cust1        
  01/01/01
  Cust2
  02/01/01
  Cust3
  03/01/01
  Cust4
  04/01/01
  05/01/01
  Cust5
  06/01/01  
  Cust6
  07/01/01  
  Cust7
  08/01/01  
  Cust8

Commented:
Hi,

For Example 2:

If I understand you correctly, I think you want to combine both columns and sort according to date.

Here's one way of doing it.

/*** ALTER EXISTING TABLE AND ADD 3RD COLUMN ******/
Alter table TableName
Add DateFinal datetime

/**** UPDATE 3RD COLUMN WITH ALL DATES FROM COLUMN 1 ****/
Update TableName
set DateFinal = Date1Field from TableName
where Date1Field is not null

/**** UPDATE 3RD COLUMN WITH ALL DATES FROM COLUMN 2 ****/
Update TableName
set DateFinal = Date2Field from TableName
where Date2Field is not null

/***** SELECT DATA AND SORT **********/

Select DateFinal from TableName
order by DateFinal

Hope this helps

Regards
TAZI


Author

Commented:
I have just one question more about this.
So there is no some regular way to do this problem ?
And I have to find some my own as you comment TAZI ?

Thanks.

Commented:
Hi,

Thanks for the points ?? I have to ask why do you allow capturing of dates in two different columns. Enforce date capturing to one column only.  This will be so much easier as you do not need to add a 3rd column and update it with all the date values.  You can just do the last statement, Select and Sort

  Select DateFinal from TableName order by DateFinal

I don't know of any other way that this can be done.

Thanks again for the point
Hope this helps
Regards
TAZI

Explore More ContentExplore courses, solutions, and other research materials related to this topic.