Learn how to a build a cloud-first strategyRegister Now

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

SELECT unique value on column - MS SQL (urgent)

How can I get a unique TM column on this query, I need to get the unique time(tm). At this query I cannot use it by distinct.  

Sorry, this is urgent.

SELECT DISTINCT RIGHT(CONVERT(VARCHAR(19), CONVERT(SMALLDATETIME, TIME), 0), 7) AS tm, TIME AS tme, DATE AS dte
FROM         tbl1
WHERE     (GETDATE() + 2 < DATE)
GROUP BY TIME, DATE
ORDER BY tme

  TM                   tme                         DTE
 8:15AM            1900-01-01 08:15:00.000            2009-06-09 00:00:00.000            
 9:30AM            1900-01-01 09:30:00.000              2009-06-16 00:00:00.000      
10:30AM            1900-01-01 10:30:00.000              2009-06-25 00:00:00.000      
11:00AM            1900-01-01 11:00:00.000              2009-04-29 00:00:00.000      
11:15AM            1900-01-01 11:15:00.000             2009-05-12 00:00:00.000      
12:15PM            1900-01-01 12:15:00.000             2009-04-16 00:00:00.000      
12:15PM            1900-01-01 12:15:00.000            2009-05-08 00:00:00.000      
12:15PM            1900-01-01 12:15:00.000            2009-06-02 00:00:00.000      
12:30PM            1900-01-01 12:30:00.000            2009-04-20 00:00:00.000      
12:30PM            1900-01-01 12:30:00.000            2009-05-15 00:00:00.000      
12:30PM            1900-01-01 12:30:00.000            2009-06-12 00:00:00.000      
0
jr_bautista
Asked:
jr_bautista
  • 4
  • 2
  • 2
  • +1
2 Solutions
 
8080_DiverCommented:
You're going to have to provide more definition regarding what you are trying to accomplish.  For instance, use the data that you got from your query and provide an example of the output you are trying to achieve.
If you have multiple rows with the same time value but different dates, do you want the earliest or the latest date associated with that particular time.  If you want them all, then you cannot get a DISTINCT time value because you are going to have multiple rows.
As a passing observeation, the use of Date and Time as column names is an extremely bad practice.  Those are normally Reserved Words and some SQL engines will seriously balk at their use.

SELECT Z.tm, TIME AS tme, DATE AS dte
FROM         tbl1
INNER JOIN
(
SELECT DISTINCT RIGHT(CONVERT(VARCHAR(19), CONVERT(SMALLDATETIME, TIME), 0), 7) AS tm,
       MAX(DATE) AS DTE
FROM         tbl1
WHERE     (GETDATE() + 2 < DATE) 
GROUP BY RIGHT(CONVERT(VARCHAR(19), CONVERT(SMALLDATETIME, TIME), 0), 7), MAX(DATE)
) Z
 ON Z. tm = RIGHT(CONVERT(VARCHAR(19), CONVERT(SMALLDATETIME, tbl1.TIME), 0), 7) AND
    Z.DTE = tbl1.Date
ORDER BY tme

Open in new window

0
 
Chris LuttrellSenior Database ArchitectCommented:
Not sure your exact problem.  If you drop DATE from the select list and from the GROUP BY you will get unique values of time for the whole result set.  If you need the DATE in there then we are missing something in the logic you are trying to apply.  Explain it a little more.
0
 
jr_bautistaAuthor Commented:
We cannot drop the date column, as it needs to be there....

0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
SharathData EngineerCommented:
With your query result, what is your expected result?
0
 
jr_bautistaAuthor Commented:
This is the actual data:

DATE                        TIME
2006-10-09 00:00:00.000            1899-12-30 22:00:00.000
2006-10-09 00:00:00.000            1899-12-30 21:00:00.000
2006-10-09 00:00:00.000            1900-01-01 20:00:00.000
2006-10-10 00:00:00.000            1899-12-30 22:00:00.000
2006-10-10 00:00:00.000            1899-12-30 20:00:00.000
2006-10-10 00:00:00.000            1899-12-30 21:00:00.000
2007-06-11 00:00:00.000            1900-01-01 21:00:00.000
2007-06-11 00:00:00.000            1900-01-01 22:00:00.000


I should output this: (TM should be unique, and date should be included, never mind the tme)

  TM                   tme                         DTE
 8:15AM            1900-01-01 08:15:00.000            2009-06-09 00:00:00.000            
 9:30AM            1900-01-01 09:30:00.000              2009-06-16 00:00:00.000      
10:30AM            1900-01-01 10:30:00.000              2009-06-25 00:00:00.000      
11:00AM            1900-01-01 11:00:00.000              2009-04-29 00:00:00.000      
11:15AM            1900-01-01 11:15:00.000             2009-05-12 00:00:00.000      
12:15PM            1900-01-01 12:15:00.000             2009-04-16 00:00:00.000      
12:15PM            1900-01-01 12:15:00.000            2009-05-08 00:00:00.000      
12:15PM            1900-01-01 12:15:00.000            2009-06-02 00:00:00.000      
12:30PM            1900-01-01 12:30:00.000            2009-04-20 00:00:00.000      
12:30PM            1900-01-01 12:30:00.000            2009-05-15 00:00:00.000      
12:30PM            1900-01-01 12:30:00.000            2009-06-12 00:00:00.000      
0
 
SharathData EngineerCommented:
I observed that there is no differecne between your query result (in original question) and the output you want (post id: 24023876).
Also, in your actual data, you don't have data for year 2009. How do you get that?
0
 
8080_DiverCommented:
Your actual data does not support any means for extracting the following line:
 8:15AM            1900-01-01 08:15:00.000            2009-06-09 00:00:00.000            
 Where in your actual data (reproduced below) does any time exist that is not a whole hour?
DATE                        TIME
2006-10-09 00:00:00.000            1899-12-30 22:00:00.000
2006-10-09 00:00:00.000            1899-12-30 21:00:00.000
2006-10-09 00:00:00.000            1900-01-01 20:00:00.000
2006-10-10 00:00:00.000            1899-12-30 22:00:00.000
2006-10-10 00:00:00.000            1899-12-30 20:00:00.000
2006-10-10 00:00:00.000            1899-12-30 21:00:00.000
2007-06-11 00:00:00.000            1900-01-01 21:00:00.000
2007-06-11 00:00:00.000            1900-01-01 22:00:00.000

Your original data had some times that were like 8:15 but the newest "actual data" that you provided does not.
Did you try the code snippet I provided?
0
 
jr_bautistaAuthor Commented:
8080_Diver I got error with your code, I just drop the date (sorry I messed up, it can be eliminated) .

Thank you guys, sorry it's kinda urgent and I can't entertain your question well...
0
 
jr_bautistaAuthor Commented:
8080_Diver I got error with your code, I just drop the date (sorry I messed up, it can be eliminated) .

Thank you guys, sorry it's kinda urgent and I can't entertain your question well...
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now