sql query

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.
Jayaprakash MAsked:
Who is Participating?
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
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
 
Terry WoodsIT GuruCommented:
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
 
Jayaprakash MAuthor Commented:
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
PortletPaulfreelancerCommented:
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
 
Jayaprakash MAuthor Commented:
when the records are sorted by using one of the column, then will fetch only first 20 records
0
 
HuaMin ChenConnect With a Mentor System AnalystCommented:
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
 
Jayaprakash MAuthor Commented:
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
 
Nakul VachhrajaniConnect With a Mentor Technical Architect, Capgemini IndiaCommented:
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
 
PortletPaulfreelancerCommented:
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
 
Jayaprakash MAuthor Commented:
thank a lot Vachhrajani. Let me try this and let you the results.
0
 
Pawan KumarDatabase ExpertCommented:
@Jayaprakash M - Do you need more help on this ?
0
 
Jayaprakash MAuthor Commented:
Hi Pavan, we are trying on above solutions, if you have any better solution please provide me.
Thanks.
0
 
Pawan KumarDatabase ExpertCommented:
Yes sir, working on a solution for you ! you will get the same in some time.
0
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
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
 
Jayaprakash MAuthor Commented:
Thanks a lot Pavan. Will try and let you know the results.
0
 
Pawan KumarDatabase ExpertCommented:
Great ! thanks bro !
0
 
Jayaprakash MAuthor Commented:
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
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
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
 
PortletPaulfreelancerCommented:
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
 
Pawan KumarDatabase ExpertCommented:
@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
 
Pawan KumarDatabase ExpertCommented:
@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
 
Nakul VachhrajaniTechnical Architect, Capgemini IndiaCommented:
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
 
PortletPaulfreelancerCommented:
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
 
Pawan KumarDatabase ExpertCommented:
@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
 
HuaMin ChenSystem AnalystCommented:
No further update from author while there is enough advice given to author.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.