Solved

sql query

Posted on 2016-09-14
26
63 Views
Last Modified: 2016-10-07
how to frame a sql query to select minimum first 20 records in week and like that need to select for all the week in year with a single query.

Please suggest ASAP.
0
Comment
Question by:Jayaprakash M
  • 8
  • 7
  • 4
  • +3
26 Comments
 
LVL 35

Expert Comment

by:Terry Woods
ID: 41797535
When you say minimum first 20 do you mean ignore the week if there's less than 20 and return them all for the week if there's more than 20?
0
 

Author Comment

by:Jayaprakash M
ID: 41797538
When the records sorted by one of the column, if it having even 1 record also it should display.
I mean when the week records sorted by one column, just trying to collect only first 20 records.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41797543
select up to the first 20 records in week for all the weeks in year with a single query

Does that describe your need?

How is "first" determined? By a date/time column?
0
 

Author Comment

by:Jayaprakash M
ID: 41797549
when the records are sorted by using one of the column, then will fetch only first 20 records
0
 
LVL 10

Assisted Solution

by:HuaMinChen
HuaMinChen earned 125 total points (awarded by participants)
ID: 41797564
Hi,
You need to loop all the weeks per given here. Here is way to get start date and end date per relevant year and week number.

declare @yr int,
@week int,
@month nvarchar(2),
@start_dt datetime;
set @yr=2013
set @week=6
set @month='03'
set @start_dt=convert(datetime,ltrim(rtrim(cast(@yr as varchar)))+@month+'01',112)

select case when @week=1 then @start_dt else dateadd(dd,(@week-1)*7+8-case substring(datename(dw,@start_dt),1,3) when 'Mon' then 1 when 'Tue' then 2 when 'Wed' then 3 when 'Thu' then 4 when 'Fri' then 5 when 'Sat' then 6 else 7 end-7,@start_dt) end [1st Day of Week],case when dateadd(dd,(@week-1)*7+7-case substring(datename(dw,@start_dt),1,3) when 'Mon' then 1 when 'Tue' then 2 when 'Wed' then 3 when 'Thu' then 4 when 'Fri' then 5 when 'Sat' then 6 else 7 end,@start_dt)>=dateadd(mm,1,@start_dt) then dateadd(dd,-1,dateadd(mm,1,@start_dt)) else dateadd(dd,(@week-1)*7+7-case substring(datename(dw,@start_dt),1,3) when 'Mon' then 1 when 'Tue' then 2 when 'Wed' then 3 when 'Thu' then 4 when 'Fri' then 5 when 'Sat' then 6 else 7 end,@start_dt) end [Last Day of Week]

Open in new window

it means you have to further loop for all weeks.
0
 

Author Comment

by:Jayaprakash M
ID: 41797576
Hi Chen, thanks a lot for the quick solution. But date we are not suppose to hard code, that should be sysdate and the query should fetch the entire year data with sorted by one of the column in week and only to fetch first 20 records. So that it is easy to compare the data.

Thanks.
0
 
LVL 12

Assisted Solution

by:Nakul Vachhrajani
Nakul Vachhrajani earned 125 total points (awarded by participants)
ID: 41797580
In such cases, sample data would have helped a lot.

My understanding is that you have data that spans a few years. You want the first 20 records from each week.

Here's something that you can try:

USE AdventureWorks2014;
GO
--Table to hold the sample data
DECLARE @dataByWeekNumber TABLE (RecordId    INT          NOT NULL IDENTITY(1,1),
                                 RecordDate  DATE         NOT NULL,
                                 RecordValue VARCHAR(255)     NULL
                                );


--Prepare some test data
INSERT INTO @dataByWeekNumber (RecordDate, RecordValue)
SELECT so.create_date AS RecordDate,
       so.name AS RecordValue
FROM [sys].[objects] AS so;

--Fetch the 1st 20 records per week
;WITH RowNumberByWeek (RecordId, RecordDate, RecordValue, WeekNumber, RowNumberByWeek)
AS (SELECT dwn.RecordId,
           dwn.RecordDate,
           dwn.RecordValue,
           DATEPART(WEEK,dwn.RecordDate) AS WeekNumber,
           ROW_NUMBER() OVER (PARTITION BY DATEPART(WEEK,dwn.RecordDate) ORDER BY dwn.RecordDate, dwn.RecordId)
    FROM @dataByWeekNumber AS dwn
   )
SELECT rbw.RecordId,
       rbw.RecordDate,
       rbw.RecordValue,
       rbw.WeekNumber,
       rbw.RowNumberByWeek
FROM RowNumberByWeek AS rbw
WHERE rbw.RowNumberByWeek <= 20;

Open in new window

1
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41797591
In such cases, sample data would have helped a lot.
and the "expected result" too.

Providing both sample data and expected result is the most effective way to ask a sql query related question.
0
 

Author Comment

by:Jayaprakash M
ID: 41797597
thank a lot Vachhrajani. Let me try this and let you the results.
0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41797698
@Jayaprakash M - Do you need more help on this ?
0
 

Author Comment

by:Jayaprakash M
ID: 41797716
Hi Pavan, we are trying on above solutions, if you have any better solution please provide me.
Thanks.
0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41797721
Yes sir, working on a solution for you ! you will get the same in some time.
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 24

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 250 total points (awarded by participants)
ID: 41797767
Best approach..........Enjoy !!

DECLARE @dataByWeekNumber TABLE (RecordId    INT          NOT NULL IDENTITY(1,1),
                                 RecordDate  DATE         NOT NULL,
                                 RecordValue VARCHAR(255)     NULL
                                );


--Prepare some test data
INSERT INTO @dataByWeekNumber (RecordDate, RecordValue)
SELECT so.create_date AS RecordDate,
       so.name AS RecordValue
FROM [sys].[objects] AS so;

;WITH SingleDigits(Number) AS
(
    SELECT Number
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
    (9), (0)) AS X(Number)
)
,Series AS
(
    SELECT (d1.Number+1) + (10*d2.Number) rb
    from
    SingleDigits as d1,
    SingleDigits as d2   
)
SELECT r.*,p.rb WeekNumber FROM Series p
CROSS APPLY
(
	SELECT TOP 20 
			   RecordId,
			   RecordDate,
			   RecordValue			   
	FROM @dataByWeekNumber rb	
	where p.rb = DATEPART(WEEK,rb.RecordDate) 
)r

Open in new window

0
 

Author Comment

by:Jayaprakash M
ID: 41797782
Thanks a lot Pavan. Will try and let you know the results.
0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41797785
Great ! thanks bro !
0
 

Author Comment

by:Jayaprakash M
ID: 41797819
Here Table is already having with more than 1 million records.

Assume that table name is emp, and column name need to be sorted with 'total' weekly, for whole year. In that case kindly simplify the query.

Thank lot in advance.
0
 
LVL 24

Accepted Solution

by:
Pawan Kumar earned 250 total points (awarded by participants)
ID: 41797901
Here it is


;WITH SingleDigits(Number) AS
(
    SELECT Number
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8),
    (9), (0)) AS X(Number)
)
,Series AS
(
    SELECT (d1.Number+1) + (10*d2.Number) rb
    from
    SingleDigits as d1,
    SingleDigits as d2   
)
SELECT r.*,p.rb total FROM Series p
CROSS APPLY
(
	SELECT TOP 20 
			   RecordId,
			   RecordDate,
			   RecordValue			   
	FROM emp rb	
	where p.rb = DATEPART(WEEK,rb.RecordDate) 
)r

Open in new window

0
 
LVL 12

Expert Comment

by:Nakul Vachhrajani
ID: 41797932
If you have a large number of rows, the CROSS APPLY in the proposed solution (ID: 41797901) will generate a large number of logical reads (since the inner statement will be need to be executed, i.e. "applied" for each row in the outer CTE).

Suggest you perform a performance evaluation of both solutions.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41798886
Be wary of claims such as "best solution" particularly if very few facts have been provided in the question.

Be wary of claiming a "best solution" until sufficient facts are known.

A single pass of the data is most likely to produce the best performance. So I recommend you try using the ROW_NUMBER() OVER () approach as it only requires a single pass through the data.
0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41799056
@Nakul Vachhrajani - In most of the cases CROSS APPLY will perform better as compared to inner join. Also ORDERING an entire data set is more costly the ordering chunks of data..

@author - compare the timings and stats before you implement any solution ! Enjoy
0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41803743
@author - Do you need more help with this question. If no, could you please  accept one answer as accepted solution and close the question.
0
 
LVL 12

Expert Comment

by:Nakul Vachhrajani
ID: 41803827
The performance of any query depends upon the nature of the data structures, underlying data and the query itself. Hence, to say that CROSS APPLY will generally perform better may not be correct. CROSS APPLY works great when the output of the applied function depends upon the value of the rows in the outer query (i.e. the function needs to be calculated for, i.e. "applied to" each row separately). If this is not the case, a JOIN may be faster.

Also, when using TOP, one has to use ORDER BY. Without ORDER BY, the order of the output rows is not guaranteed.

Finally depending on the size of the data, I would rather take the performance hit of ordering once rather than once with each outer row (since SQL may need to spool out to the tempdb for the sorting and IO is always the costliest operation).
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41804005
I suggest that the person claiming "best" should prove that claim, or refrain from making claims they are unwilling to prove.

There is no compelling need for cross apply in solving this question and a cross apply is not more efficient than an inner join (if that was true database vendors would not use inner joins would they?). The apply operator is a very valuable feature but it is not the only worthwhile feature.
0
 
LVL 24

Expert Comment

by:Pawan Kumar
ID: 41804158
@author - Do you need more help with this question. If no, could you please  accept one answer as accepted solution and close the question.
0
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 41833331
No further update from author while there is enough advice given to author.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
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 extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

895 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now