?
Solved

SELECT unique value on column - MS SQL (urgent)

Posted on 2009-03-30
9
Medium Priority
?
3,397 Views
Last Modified: 2012-05-06
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
Comment
Question by:jr_bautista
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 22

Assisted Solution

by:8080_Diver
8080_Diver earned 220 total points
ID: 24023456
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
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 280 total points
ID: 24023507
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
 

Author Comment

by:jr_bautista
ID: 24023669
We cannot drop the date column, as it needs to be there....

0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 41

Expert Comment

by:Sharath
ID: 24023674
With your query result, what is your expected result?
0
 

Author Comment

by:jr_bautista
ID: 24023876
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
 
LVL 41

Expert Comment

by:Sharath
ID: 24023916
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
 
LVL 22

Expert Comment

by:8080_Diver
ID: 24024005
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
 

Author Closing Comment

by:jr_bautista
ID: 31564542
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
 

Author Comment

by:jr_bautista
ID: 24024164
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question