sql server filter data

Question

0.00/5 (No votes)

See more:

Hi all

I have the data like this

Name	                  Date 	       Holidayname
mohammed.shareef	6/1/2013	Weekend
mohammed.shareef	6/2/2013	Weekend
mohammed.shareef	6/3/2013	
mohammed.shareef	6/3/2013	Holiday
mohammed.shareef	6/4/2013	
mohammed.shareef	6/5/2013	
nagarjuna.doddipatla	6/1/2013	Weekend
nagarjuna.doddipatla	6/2/2013	Weekend
nagarjuna.doddipatla	6/3/2013	Holiday
nagarjuna.doddipatla	6/4/2013	
nagarjuna.doddipatla	6/5/2013

I want to get result as

Name	                  Date          Holidayname
mohammed.shareef	6/1/2013	Weekend
mohammed.shareef	6/2/2013	Weekend
mohammed.shareef	6/3/2013	Holiday
mohammed.shareef	6/4/2013	
mohammed.shareef	6/5/2013	
nagarjuna.doddipatla	6/1/2013	Weekend
nagarjuna.doddipatla	6/2/2013	Weekend
nagarjuna.doddipatla	6/3/2013	Holiday
nagarjuna.doddipatla	6/4/2013	
nagarjuna.doddipatla	6/5/2013

could any one help me out.

Prakash.ch

Posted 26-Jun-13 6:22am

prakash.chakrala

Updated 26-Jun-13 7:26am

Aarti Meswania

v2

Add a Solution

Comments

ArunRajendra 26-Jun-13 12:36pm

do you want all the holidays and then working days?

prakash.chakrala 26-Jun-13 12:40pm

Hi Arun

for example mohammed.shareef for june 3rd I got 2 records, so if I get like that I have to take only one record and that record having with holidayname.

mohammed.shareef 6/3/2013
mohammed.shareef 6/3/2013 Holiday

from these records I have to get only
mohammed.shareef 6/3/2013 Holiday
this record

eg

3 solutions

Solution 3

Hi,

You can try below SQL Query

SQL

SELECT * FROM(
SELECT
    NAME,DATE,HOLIDAYNAME,ROW_NUMBER() OVER (PARTITION BY  NAME,DATE ORDER BY NAME,DATE,HOLIDAYNAME ) AS ROW
FROM
    TABLENAME) TBL
WHERE TBL.ROW = 1

Posted 26-Jun-13 8:23am

MaulikDusara

Comments

Maciej Los 26-Jun-13 14:31pm

See solution 1. Does it plagiat?

[no name] 26-Jun-13 14:36pm

I think we both posted on same time :)

Maciej Los 26-Jun-13 14:40pm

Not really... 3 minutes of difference ;)
To avoid down-voting, i would suggest you to remove this solution.

[no name] 26-Jun-13 14:41pm

I didn't copied this from solution 1.

Maciej Los 26-Jun-13 14:42pm

I understand, but it looks like plagiat...
;)

prakash.chakrala 27-Jun-13 3:01am

Hi MaulikDusara
sorry to say your solution is not correct for my question

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Aarti Meswania · Accepted Answer · 2013-06-26T08:20:00

Solution 1

SQL

select name,date,Holidayname from
(
select ROW_NUMBER() over(partition by name, date order by name,date,len(Holidayname) desc)
as srno,* from TBL_Nm
) as a
where srno=1

Happy Coding!
:)

Posted 26-Jun-13 8:20am

Aarti Meswania

Comments

Maciej Los 26-Jun-13 14:23pm

Good job!
+5!

Aarti Meswania 26-Jun-13 14:24pm

Thank you!
:)

Raja Sekhar S 27-Jun-13 2:42am

Nice...
+5

Aarti Meswania 27-Jun-13 6:54am

Thank you! :)

prakash.chakrala 27-Jun-13 2:59am

Hi Aarti Meswania
Thank you for helping

Aarti Meswania 27-Jun-13 6:54am

Welcome!
Glad to help you! :)

Maciej Los · Accepted Answer · 2013-06-26T08:22:00

Try this:

SQL

DECLARE @tmp TABLE([Name] VARCHAR(30), [Date] DATETIME, Holidayname VARCHAR(30) NULL)

INSERT INTO @tmp ([Name], [Date], [Holidayname])
SELECT 'mohammed.shareef' AS [Name], '6/1/2013' AS [Date], 'Weekend' AS [Holidayname]
UNION ALL SELECT 'mohammed.shareef', '6/2/2013', 'Weekend'
UNION ALL SELECT 'mohammed.shareef', '6/3/2013', NULL
UNION ALL SELECT 'mohammed.shareef', '6/3/2013', 'Holiday'
UNION ALL SELECT 'mohammed.shareef', '6/4/2013', NULL
UNION ALL SELECT 'mohammed.shareef', '6/5/2013', NULL
UNION ALL SELECT 'nagarjuna.doddipatla', '6/1/2013', 'Weekend'
UNION ALL SELECT 'nagarjuna.doddipatla', '6/2/2013', 'Weekend'
UNION ALL SELECT 'nagarjuna.doddipatla', '6/3/2013', 'Holiday'
UNION ALL SELECT 'nagarjuna.doddipatla', '6/4/2013', NULL 
UNION ALL SELECT 'nagarjuna.doddipatla', '6/5/2013', NULL

SELECT DISTINCT t1.[Name], t1.[Date], (SELECT [HolidayName] FROM @tmp WHERE [Name] = t1.[Name] AND [Date] = t1.[Date] AND [HolidayName] IS NOT NULL) AS [HolidayName]
FROM @tmp AS t1 
ORDER BY [Name], [Date]

Warning! This is not optimal solution (time of execution would be long for thousands of data).

sql server filter data

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us