Solved

Select 'First', Count and Having Expressions

Posted on 2015-01-15
10
59 Views
Last Modified: 2015-02-26
I am trying to convert the following MS Access Query to T-SQL and the solution eludes me.

SELECT First([The_Raw_Data].[ICN]) AS ICN, Count([The_Raw_Data].[ICN]) AS Row_Count
INTO [_DB_No_Line_1_ICNs]
FROM [The_Raw_Data]
GROUP BY [The_Raw_Data].[ICN]
HAVING (((Count([The_Raw_Data].[ICN]))>=1) AND ((Min(CInt([DTL_NUM])))>1))
ORDER BY [The_Raw_Data].[ICN], Min(CInt([DTL_NUM]));

It works in Access and I already know what the correct result is; I have not been able to replicate that result in SQL. Here is one of my many iterations ...

SELECT [ICN], [DTL_NUM] INTO [DB_No_Line_1_ICNs]
FROM [Raw_Data]
GROUP BY [ICN], [DTL_NUM]
HAVING COUNT([ICN])>=1 AND MIN(CAST([DTL_NUM] AS INTEGER))>1
ORDER BY [ICN] ASC;

Here is some sample data ...
ICN      DTL_NUM
2311018009815      2
2311018009815      3
2311018009815      4
2311025005375      5
2311025005375      6
2311237000712      1
2311237000712      2
2312144002972      2
2312144002972      3
2312144002999      2
2312144002999      4
2312185136028      1
2312185136028      2
2312185136028      3

... and the Result should be ...
2311018009815      2
2312144002972      2
2312144002999      2
0
Comment
Question by:d2beetle
[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
10 Comments
 
LVL 37

Expert Comment

by:PatHartman
ID: 40552213
Why did you change the SQL?  Your new version doesn't produce the same results as the Access original.  The Access version looks like it will work in SQL Server.

The only change I would make is to use Count(*) rather than Count(somefieldname).  The former counts all the rows in the set.  The latter counts all the rows in the set but ONLY if the specified field is not null.  Also, the count(*) is more efficient than the Count(somefieldname) since it can frequently be obtained directly from the table statistics whereas counting a specific column name requires that each row be examined one at a time.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40552391
SELECT [ICN], MIN(CAST([DTL_NUM] AS integer)) AS DTL_NUM
INTO [DB_No_Line_1_ICNs]
FROM [Raw_Data]
GROUP BY [ICN]
HAVING COUNT([ICN])>=1 AND MIN(CAST([DTL_NUM] AS integer))>1
ORDER BY [ICN] ASC;
0
 
LVL 32

Expert Comment

by:awking00
ID: 40553427
>>Here is some sample data ...
ICN      DTL_NUM
2311018009815      2
2311018009815      3
2311018009815      4
2311025005375      5
2311025005375      6
2311237000712      1
2311237000712      2
2312144002972      2
2312144002972      3
2312144002999      2
2312144002999      4
2312185136028      1
2312185136028      2
2312185136028      3

... and the Result should be ...
2311018009815      2
2312144002972      2
2312144002999      2 <<
Can you explain in plain terms the criteria used to get those desired results?
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:d2beetle
ID: 40564294
I apologize for the delay, been swamped around here ...

PatHartman ...
I believe that count(*) was in one of my iterations; however I went through so many variations I do not remember the results. I will have to try it again.


ScottPletcher ...
I will testing your query later today.
 

awking00 ...
The Access Query that I posted produces those results; each ICN must have a #1 line or it cannot be processed by the application; as result I need a way to identify the "No Line 1" ICN's and pull all of those related data rows out, so the remaining data set can be processed. My typical data set has upwards of a half-million data rows and Access does OK processing that or less; however the data sets are getting larger. The current data set has 3.1 million data rows, which is to much for Access to handle.
0
 

Author Comment

by:d2beetle
ID: 40604865
Have not forgot; other projects moved ahead of this one.
0
 
LVL 32

Expert Comment

by:awking00
ID: 40616972
>>each ICN must have a #1 line or it cannot be processed by the application; as result I need a way to identify the "No Line 1" ICN's <<
Then why is icn 2311025005375 not in your expected results as it only has dtl_num of 5 and 6?
0
 

Author Comment

by:d2beetle
ID: 40617327
awking00,
I actually typed those in, not a cut paste and it does show in the query results.
0
 
LVL 32

Expert Comment

by:awking00
ID: 40618993
So the following record should show in the final results?
icn                           dtl_num
2311025005375       5

If so,
select icn, dtl_num from
(select icn, dtl_num, row_number() over (parttion by icn order by dtl_num) as rn
 from yourtable) x
where x.rn = 1 and x.rn < x.dtl_num;
0
 

Author Comment

by:d2beetle
ID: 40625783
Have not forgot ...
0
 

Author Closing Comment

by:d2beetle
ID: 40633562
ScottPletcher,

BINGO! :)
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

740 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