How to display only few columns of a table based on the data

Question

1.00/5 (1 vote)

See more:

SQL-Server-2008R2

Consider the table below

Name partno.  sch_date    WO#   owed panels
 aa   1234     08/22/2017  121   22    26
 aa   1234     08/22/2017  222   22    27
 aa   1234     08/22/2017  242   22    27
 aa   1234     08/29/2017  152   20    24
 aa   1234     08/29/2017  167   20    24
 aa   1234     08/29/2017  202   20    26

Is it possible to display the data in such way that when i read through the data if the first partno. in the table has the number of panels  greater than owed, then i don't won't to dispaly the other partno. schedule on the same date(sch_date).

Expected Result

Name partno.  sch_date    WO#   owed panels
 aa   1234     08/22/2017  121   22    26
 aa   1234     08/29/2017  152   20    24

What I have tried:

I am not sure how to imply the case function here.

Posted 10-Aug-17 7:13am

Member 12336474

Updated 29-Aug-17 0:16am

Add a Solution

Comments

RedDk 10-Aug-17 13:34pm

Look up DISTINCT in the BOL.

3 solutions

Solution 3

;WITH TempTable (Name,partno,sch_date,WO#,owed,panels, duplicateRecCount)
AS
(SELECT Name,partno,sch_date,WO#,owed,panels,ROW_NUMBER() OVER(PARTITION by Name,partno,sch_date,WO#,owed,panels ORDER BY Name)
AS duplicateRecCount FROM [Table Name]	
Select Name,partno,sch_date,WO#,owed,panels from TempTable where duplicateRecCount=1

Posted 29-Aug-17 0:15am

Moin 10918476

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)

itsmypassion · Accepted Answer · 2017-08-10T18:36:00

Solution 1

Try following ..

SQL

select TOP 1  Name,partno,sch_date,WO#,owed,panels from TableName
ORDER BY
    ROW_NUMBER() OVER(PARTITION BY sch_date ORDER BY WO# ASC)

Posted 10-Aug-17 18:36pm

itsmypassion

Pradeep Magati · Accepted Answer · 2017-08-10T18:40:00

Hello,
Check the below solution based on ROW_NUMBER().

SELECT ROW_NUMBER() OVER (PARTITION BY [Name],[sch_date] ORDER BY [Name],[sch_date]) AS RN,*
INTO #Temp
FROM #YourTable WHERE panels>owed

SELECT * FROM #Temp

RN Name partno# sch_date WO# owed panels
1 aa 1234 2017-08-22 00:00:00.000 121 22 26
2 aa 1234 2017-08-22 00:00:00.000 222 22 27
3 aa 1234 2017-08-22 00:00:00.000 242 22 27
1 aa 1234 2017-08-29 00:00:00.000 152 20 24
2 aa 1234 2017-08-29 00:00:00.000 167 20 24
3 aa 1234 2017-08-29 00:00:00.000 202 20 26

SELECT * FROM #Temp WHERE RN=1

Result:

RN Name partno# sch_date WO# owed panels
1 aa 1234 2017-08-22 00:00:00.000 121 22 26
1 aa 1234 2017-08-29 00:00:00.000 152 20 24

Thanks&Regards,
Pradeep Magati.