Solved

sql query

Posted on 2016-09-14
26
61 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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)
Comment Utility
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
Comment Utility
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 11

Assisted Solution

by:Nakul Vachhrajani
Nakul Vachhrajani earned 125 total points (awarded by participants)
Comment Utility
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
Comment Utility
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
Comment Utility
thank a lot Vachhrajani. Let me try this and let you the results.
0
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
@Jayaprakash M - Do you need more help on this ?
0
 

Author Comment

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

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Yes sir, working on a solution for you ! you will get the same in some time.
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 16

Assisted Solution

by:Pawan Kumar Khowal
Pawan Kumar Khowal earned 250 total points (awarded by participants)
Comment Utility
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
Comment Utility
Thanks a lot Pavan. Will try and let you know the results.
0
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Great ! thanks bro !
0
 

Author Comment

by:Jayaprakash M
Comment Utility
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 16

Accepted Solution

by:
Pawan Kumar Khowal earned 250 total points (awarded by participants)
Comment Utility
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 11

Expert Comment

by:Nakul Vachhrajani
Comment Utility
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
Comment Utility
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 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
@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 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
@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 11

Expert Comment

by:Nakul Vachhrajani
Comment Utility
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
Comment Utility
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 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
@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
Comment Utility
No further update from author while there is enough advice given to author.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Backup skipping a few tables 7 25
Backup Job question 4 17
Concatenating multiple comments into one row 16 34
Sql query 34 16
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

762 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

9 Experts available now in Live!

Get 1:1 Help Now