Help Needed
-
Hello,
I need a T-SQl code to do the following.I have a table with few records.I want to display the data from this table based on this rule.I have a field called priority(this takes values 1,2,3 for high ,medium,low).Also there is a date field
1.If no records found for the current date-Display records from previous date.
2.Records with less than 5 for current date,irrespective of any priority-get few headlines from previous date.(get onlye record with priority 1 and order by priority ascending,date descending)
3.Records more than 5 and has a combination of high,medium,low then order by priority in ascending and date descending for current date
4.Records not available for previous day,get the day before
Help!!
-
Could you post sample data (preferably in a script form such that it can be directly inserted into query analyzer) along with your desired output? Then I am sure that someone will help you write a query...
-
i agree - post us a few lines of data with a creat table statement
but your code will look something like
create procedure USP_GETMYDATA as
declare @todayscount integer
set @todayscount=(select count(*) from mytable where datefield>=convert(varchar(11),getdate()))
declare @prevday integer
set @prevcount=(select count(*) from mytable where convert(varchar(11),datefield))=convert(varchar(11),dateadd(d,-1,getdate()))
begin
if @todayscount=0
begin
if @prevcount=0
begin
select * from mytable where......
end
if @prevcount>0
begin
select ...... from .....
end
if @todayscount=dateadd(d,1,convert(varchar(11),getdate())) order by datefield desc, priority asc
end
if @todayscound>=6
begin
select top 5 * .... from .... where datefield>=convert(varchar(11),getdate()) order by priority asc
end
GO
or something similar
MVDBA
-
Agree not enough info and too many ambiguities.
My guess would be
DECLARE @date datetime
SELECT @date = MAX([date])
FROM
WHERE [date] < @currentdate
SELECT a.*
FROM
a
CROSS JOIN (SELECT COUNT(*) AS [Count], COUNT(DISTINCT priority) AS [Combination]
FROM
c
WHERE c.[date] = @date) b
WHERE a.[date] = @date
AND ((b.[Count] <= 5 AND a.priority = 1) OR (b.[Count] > 5 AND b.[Combination] > 1))
ORDER BY priority ASC, [date] DESC
nedd more clarification on count vs priority combinations
i.e what about > 5 records and all same priority?
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply