Solved

SELECT unique value on column - MS SQL (urgent)

Posted on 2009-03-30
9
3,375 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 55 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 26

Accepted Solution

by:
Chris Luttrell earned 70 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
efficient backup report for SQL Server 13 77
Call to SQL server times out 5 50
Complex SQL Server WHERE CLause 9 36
VM SQL server license. 1 52
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

738 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