sql query requirement
-
thanks for your help,
i got a kind off tricky requirement here.
the query that i write has to return me 6 records always.
The first record that will be displayed will be the initial record that is created. The last record in these 6 records will be the latest record that is created. so now my question is i want to retrieve the remaining 4 records.
my conditions are like this.
i want to display the maximum date of a particular month in the 4 records. now if this condition does not give me 4 records, then i need to
pick up the current month's record also and make sure the count is equal to 4. What if i have only one record in the current month.
then i need to go to current - 1 month and pick up that record.
I need to keep doing this until i get the 4 records that i want.
Let me give u the example for this.
Lets take the example that i have given previously.
DATE
------
01 Jan 2004
02 Jan 2004
05 Jan 2004
07 Jan 2004
04 Feb 2004
06 Feb 2004
19 Feb 2004
04 Mar 2004
28 Mar 2004
03 Apr 2004
05 Apr 2004
My output should be something like this
01 Jan 2004 --> First Record that is created
07 Jan 2004 --> Max of Jan
06 Feb 2004 --> Max of Feb
28 Mar 2004 --> Max of March
03 Apr 2004 --> I choose this record becoz the count for max months
is not equal to 4 and hence to make it 4 , i add this
05 Apr 2004 --> The most recent record
Hope this helps in understanding the requirement
Thanks
Shankar
-
I'd suggest you make a stored proc to perform this functionality. Use temporary objects to store intermediate data and returen your selected subset from the temporary object.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
my requirement is i need to use SQL's only.
I should not use Stored Proc for this.
i can have multiple SQL's and then i do a Union of these.. thats allowed.
Can anyone help me out
Thanks
Shankar
-
What do you mean with 'i need to use SQL's only'. Do you want anly select statements ? I do not think it is possible to do this without some if ... then ... else statements.
If you can run a SQL batch, you could run the script as mentioned above, but without the "create procedure dbo.Shankar as begin" end the last "end" statement. This script should do the work.
-
Thanks for your Stored Proc here.
I have an additional thing that is needed here.
Suppose if the number of months exceeds 6, then i need to pick the max of the last 5 months and show the data
Example -- if these are the records in my table
01 Jan 2004
02 Jan 2004
05 Feb 2004
07 Feb 2004
04 Mar 2004
06 Mar 2004
19 Apr 2004
22 May 2004
01 Jun 2004
07 Jun 2004
19 Jul 2004
My output should be
01 Jan 2004
06 Mar 2004
19 Apr 2004
22 May 2004
07 Jun 2004
19 Jul 2004
but currently the output that i get is
2004-01-01 00:00:00.000
2004-01-02 00:00:00.000
2004-02-07 00:00:00.000
2004-03-06 00:00:00.000
2004-04-19 00:00:00.000
2004-07-19 00:00:00.000
sorry if i have missed this requirement previously
thanks
Shankar
-
Just change the first 'order dte asc' to 'order by dte desc' :
create procedure dbo.Shankar as
begin
set nocount on
declare @result_table table
( dte datetime not null UNIQUE clustered )
declare @mindte datetime,
@maxdte datetime,
@curdte datetime,
@count int
-- Get min and max
select @mindte = min(dte), @maxdte = max(dte), @curdte = getdate() from dbo.tst
-- add min and max to result
insert @result_table (dte) values (@mindte)
insert @result_table (dte) values (@maxdte)
-- get maximum 4 records, each time the maximum for a month
-- starting from first month
-- but do not take dates allready used
insert @result_table (dte)
select TOP 4 A.dte from (
select TOP 4 month = datepart(mm,dte),dte = max(dte)
from dbo.tst
group by datepart(mm,dte)
having max(dte) not in ( select dte from @result_table)
order by dte desc) A
-- count how much records to add
select @count = 6 - count(*) from @result_table
-- if still records to add
if @count > 0
begin
set rowcount @count
-- I did not understand the requirements, so I take the max for each month
-- without taking the allready used dates, and start with the most current
-- the set rowcount will only insert the number of needed records ...
insert @result_table (dte)
select A.dte from (
select TOP 6 month = datepart(mm,dte),dte = max(dte)
from dbo.tst
where dte not in ( select dte from @result_table )
group by datepart(mm,dte)
having max(dte) not in ( select dte from @result_table)
order by dte desc ) A
set rowcount 0
end
select * from @result_table
end
-
Perfect.
this is what i want.
But, can u tell me one thing.
Is there a way i can get this done using only Select Statements.
thats my requirement actually.
I can have even 6 sql statements, and join each one of them using a UNION
statement.
Can you help me out in this .
thanks
shankar
-
Try this using the table 'dbo.tst' designed in a previous reply.
SELECT *
FROM dbo.tst
WHERE dte = (SELECT MIN(dte) FROM dbo.tst)
UNION
SELECT *
FROM dbo.tst
WHERE dte = (SELECT MAX(dte) FROM dbo.tst)
UNION
SELECT TOP 4 *
FROM dbo.tst
WHERE dte IN (SELECT top 4 dte
FROM dbo.tst
WHERE dte != (SELECT MAX(dte) FROM dbo.tst)
AND dte != (SELECT MAX(dte) FROM dbo.tst)
ORDER by dte DESC)
ORDER BY dte ASC
-
Oops. I just saw the requirement for Max of each month descending. I'll have to think about this one.
What University are you attending? This has got to be an academic problem.
-
Hey. I am not attending any university.
this is one of my project requirements
Thanks
Shankar
-
here it is ...
Does this query make sense
SELECT *
FROM dbo.tst
WHERE dte = (SELECT MIN(dte) FROM dbo.tst)
UNION
SELECT *
FROM dbo.tst
WHERE dte = (SELECT MAX(dte) FROM dbo.tst)
UNION
SELECT TOP 4 *
FROM dbo.tst
WHERE dte IN (
select A.dte from (
select TOP 4 month = datepart(mm,dte),dte = max(dte)
from dbo.tst
where dte != (SELECT MAX(dte) FROM dbo.tst) and
dte != (SELECT MAX(dte) FROM dbo.tst)
group by datepart(mm,dte)
having max(dte) in ( select dte from dbo.tst)
order by dte desc) A )
ORDER BY dte ASC
Can anyone tell me whether its correct
-
Well this gets you the max dates of each month not to include the first or last month.
SELECT YEAR(dte)
,MONTH(dte)
,max(DAY(dte))
from dbo.tst
WHERE dte >= (SELECT DATEADD(day, (DAY(DATEADD(MONTH, 1, MIN(dte))) * -1) + 1, MIN(dte)) FROM dbo.tst)
AND dte <= (SELECT DATEADD(day, DAY(MAX(dte)) * -1, MAX(dte)) FROM dbo.tst)
GROUP BY YEAR(dte)
,MONTH(dte)
ORDER BY YEAR(dte) DESC
,MONTH(dte) DESC
Interpolate from there.
-
Your query looks right. I'd add some more dates to the test and continue testing... but by-golie I think you've got it.
-
This query should do exactly the same as my stored procedure :
select top 6 B.dte
from
(
select id=1,dte = min(dte) from dbo.tst
union all
select id=2,dte = max(dte) from dbo.tst
union all
select id=14-month,A.dte from
(
select TOP 4 month = datepart(mm,dte),dte = max(dte)
from dbo.tst
group by datepart(mm,dte)
having max(dte) not in ( select min(dte) from dbo.tst union all select max(dte) from dbo.tst)
order by dte desc
/**/) A
union all
select id=25-month,A.dte from
(
select TOP 6 month = datepart(mm,dte),dte = max(dte)
from dbo.tst
where dte not in ( select dte = min(dte) from dbo.tst
union all
select dte = max(dte) from dbo.tst
union all
select dte from (
select TOP 4 month = datepart(mm,dte),dte = max(dte)
from dbo.tst
group by datepart(mm,dte)
having max(dte) not in ( select min(dte) from dbo.tst union all select max(dte) from dbo.tst)
order by dte desc ) A
)
group by datepart(mm,dte)
order by dte desc
/**/) A
) B
order by id
-
Just a question on your stored proc
This is the data that i have in the table
2004-01-01 00:00:00.000
2004-01-02 00:00:00.000
2004-01-03 00:00:00.000
2004-02-07 00:00:00.000
2004-02-14 00:00:00.000
2004-03-06 00:00:00.000
2004-03-19 00:00:00.000
2004-03-24 00:00:00.000
2004-03-28 00:00:00.000
2004-03-29 00:00:00.000
2004-03-31 00:00:00.000
My output should be like this
01 Jan 2004
03 Jan 2004
14 Feb 2004
28 Mar 2004
29 Mar 2004
31 Mar 2004
But i am getting it like this
2004-01-01 00:00:00.000
2004-01-03 00:00:00.000
2004-02-07 00:00:00.000
2004-02-14 00:00:00.000
2004-03-29 00:00:00.000
2004-03-31 00:00:00.000
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply