March 8, 2004 at 1:21 pm
As mentioned in the stored procedure, I did not understand the requirements for that part.
This query should do it :
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=(100*(25-month))+(99-datepart(dd,dte)),A.dte from
(
select TOP 6 month = datepart(mm,dte),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
)
order by dte desc
/**) A
) B
order by id
----- and this is the new procedure :
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
insert @result_table (dte)
select dte
from dbo.tst
where dte not in ( select dte from @result_table )
order by dte desc
set rowcount 0
end
select * from @result_table
end
March 8, 2004 at 1:39 pm
Let me put out my requirements clearly
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
If i have data in the table like this
01 Jan 2004
02 Jan 2004 01 Feb 2004
07 Feb 2004
04 Mar 2004
06 Apr 2004
19 Apr 2004
04 May 2004
28 May 2004
03 Jun 2004
05 Jul 2004
Then my outout should be
01 Jan 2004
04 Mar 2004
19 Apr 2004
28 May 2004
03 Jun 2004
05 Jul 2004
The whole idea is like this.
I need to display 6 Records at a time
First record is never a problem
the question is the last 5 records.
If i have data for more than 6 months, then i pick up the
Max date for every month and add it to the First row and display the records
This should be in descending. starting from the Latest month (5 records)
If i dont have data for 6 months.
I find out how many months are there, pick up the Max date for every month
Check if this count is actually = 4.
If its not, go to the current month and check how many records are there.
If the number of records in the current month matches the required records, then populate all
these records and show.
If the number of records in the current month does not match the required records, then
goto Month-1 and get all the records to make the count 5.
Keep doing this till i get my count of records as 6
This is my requirement
Hope its clear now
March 8, 2004 at 1:48 pm
the query I posted should do this, but will not work if the dates are not in the same year. This new query should do the job.
query :
select dte from
(
select top 6 B.dte
from
(
select id='999999999',dte = min(dte) from dbo.tst
union all
select id='999999998',dte = max(dte) from dbo.tst
union all
select id='8' + convert(varchar(8),A.dte,112),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='7' + convert(varchar(8),A.dte,112),A.dte from
(
select TOP 6 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
)
order by dte desc
/**/) A
) B
order by id desc
) C
order by dte
March 8, 2004 at 2:15 pm
hey ...
looks like the query does not fit this criteria
01 Jan 2004
02 Jan 2004
07 Jan 2004
01 Feb 2004
04 Feb 2004
06 Feb 2004
04 Mar 2004
09 Mar 2004
18 Mar 2004
20 Mar 2004
28 Apr 2004
The output should be like this
01 Jan 2004
06 Feb 2004
18 Mar 2004
19 Mar 2004
20 Mar 2004
28 Apr 2004
But i get the output as
2004-01-01 00:00:00.000
2004-01-07 00:00:00.000
2004-02-06 00:00:00.000
2004-03-18 00:00:00.000
2004-03-20 00:00:00.000
2004-04-28 00:00:00.000
March 8, 2004 at 2:16 pm
SELECT CONVERT(char(12),dt,113)
FROM
(SELECT MIN(Dt) dt
FROM Dates
UNION ALL
SELECT TOP 4 dt
FROM
(SELECT MAX(Dt) dt
FROM Dates
WHERE Dt <
(SELECT MAX(Dt)
FROM Dates)
GROUP BY CONVERT(char(6),Dt,112)) m
ORDER BY dt DESC
UNION ALL
SELECT MAX(Dt)
FROM Dates) o
ORDER BY dt
--Jonathan
March 8, 2004 at 2:25 pm
19 Mar 2004 is not in the input ... I supposed only records in the input table should come up ?
March 8, 2004 at 2:29 pm
oops... i'm sorry
i wanted to say 09 March and instead i typed 19 March there...
sorry the output should have it as 09 Mar 2004
March 8, 2004 at 2:40 pm
Oops, you do not want the max(dte) fro the first month ? :
select dte from
(
select top 6 B.dte
from
(
select id='999999999',dte = min(dte) from dbo.tst
union all
select id='999999998',dte = max(dte) from dbo.tst
union all
select id='8' + convert(varchar(8),A.dte,112),A.dte from
(
select TOP 4 month = datepart(mm,dte),dte = max(dte)
from dbo.tst
where datepart(yy,dte)*1000 + datepart(mm,dte) != ( select datepart(yy,min(dte))*1000 + datepart(mm,min(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='7' + convert(varchar(8),A.dte,112),A.dte from
(
select TOP 6 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
where datepart(yy,dte)*1000 + datepart(mm,dte) != ( select datepart(yy,min(dte))*1000 + datepart(mm,min(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
)
order by dte desc
/**/) A
) B
order by id desc
) C
order by dte
March 8, 2004 at 3:19 pm
Thanks a ton...
I got my requirement fixed beautifully.
Thanks a lot for all the people who took efforts to get my think done.
Without u guys..it would have been really tough for me...
But, i have learnt a lot of SQL from this assignment. \
Hopefully, from next time i'll be able to work it out on my own
THANKS A TON again...
One last thing...
Can u explain me the Logic on how u have done this .....I can be prepared for
debugging from Tomm, even if there are any changes to the logic
THANKS AGAIN
Shankar
March 9, 2004 at 7:27 am
You can copy/paste the following lines. Whiles explaining the query, I changed it a little bit. This new "explained" (?) query should do it :
/*
Shankar query :
I will try to explain how the query has been build.
Step 1 : get the minimum and the maximum :
select dte=min(dte) from dbo.tst
union all
select dte = max(dte) from dbo.tst
Step 2 : select the max date for each month ( max 4 records ), starting from the last month.
max 4 records : TOP 4
starting from the last month : order by dte desc
max by month : group by month => the month has to be in the select.
As the dates are not always from the same year, a month will be defined as YYYYMM.
The easeast way to get this, is to use convert(char(6),dte,112), or if you want to be sure
not having warning messages about truncation, left(convert(char(8),dte,112),6)
Also, the previous selected dates may not be used as a result => in the having clause.
Also, we do not want results from the first month. => exclude the using the where clause.
Should should read the BOL about the difference between where and having clause.
select TOP 4 month = left(convert(char(8),dte,112),6) ,dte = max(dte)
from dbo.tst
where left(convert(char(8),dte,112),6) != ( select left(convert(char(8),min(dte),112),6) from dbo.tst )
group by left(convert(char(8),dte,112),6)
having max(dte) not in ( select min(dte) from dbo.tst union all select max(dte) from dbo.tst )
order by dte desc
as I have to do a union with the result of step 1, so I use the previous query as a 'derived table', and
select only the dte from it
select step2.dte from ( <<previous query>> ) step2
Query up till now :
select dte=min(dte) from dbo.tst
union all
select dte = max(dte) from dbo.tst
union all
select step2.dte from
(
select TOP 4 month = left(convert(char(8),dte,112),6) ,dte = max(dte)
from dbo.tst
where left(convert(char(8),dte,112),6) != ( select left(convert(char(8),min(dte),112),6) from dbo.tst )
group by left(convert(char(8),dte,112),6)
having max(dte) not in ( select min(dte) from dbo.tst union all select max(dte) from dbo.tst )
order by dte desc
) step2
step 3 : add dates starting from the last date to make sure we have at least 6 dates. Exclude allready used dates.
The query for this would be like :
select TOP 6 dte -- I never need more then 6 dates
from db.tst
where dte not in ( <<dates allready selected>> )
order by dte desc
and I have to place the query I allready have till now in the <<dates allready selected>> part.
Now we still have to solve 1 problem.
When using union, SQL server wil try to output each date only once. We con not use this, because we have a certain order
to respect for the queries. I can use "union all" to tell SQL server the result should not be unique, but At that point I can
not be sure for 100% that the results will allways be in the specific order. As only the first 6 dates should
selected from the total query, I have to force an order. Step 1 should have priority 1, step 2 2 and step 3 3. In each step, the order by
clause should be respected. => I wil add an id colomn to each subquery.
for step 1, id can be a constant value of 1
for step 2, the value should be > the value of step 1, and the biggest date should have the smallest values
=> PROBLEM. The easiest way is to reverse the order, and have step 1 an id > step 2 > step 3.
so, as the order for step 3 and step 4 are based on a date value, the best way to have an id is to use the date in
format YYYYMMDD. add the step number as first character ( but step 1 should be > step 2, so I start to count from 9 down to 7 ),
and now I have I good orderby criteria. Step 1 and Step 2 get A constant value, as big as possible
SO the result of all this :
select id='999999999',dte = min(dte) from dbo.tst
union all
select id='999999998',dte = max(dte) from dbo.tst
union all
select id='8' + convert(varchar(8),step2.dte,112),step2.dte from
(
select TOP 4 month = left(convert(char(8),dte,112),6) ,dte = max(dte)
from dbo.tst
where left(convert(char(8),dte,112),6) != ( select left(convert(char(8),min(dte),112),6) from dbo.tst )
group by left(convert(char(8),dte,112),6)
having max(dte) not in ( select min(dte) from dbo.tst union all select max(dte) from dbo.tst )
order by dte desc
) step2
union all
select id='7' + convert(varchar(8),step3.dte,112),step3.dte from
(
select TOP 6 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 step2.dte from
(
select TOP 4 month = left(convert(char(8),dte,112),6) ,dte = max(dte)
from dbo.tst
where left(convert(char(8),dte,112),6) != ( select left(convert(char(8),min(dte),112),6) from dbo.tst )
group by left(convert(char(8),dte,112),6)
having max(dte) not in ( select min(dte) from dbo.tst union all select max(dte) from dbo.tst )
order by dte desc
) step2
)
order by dte desc
) step3
step 4 : from the above query, only get the first 6 records
select TOP 6 Alldte.dte from ( <<above query>> ) Alldte order by Alldte.id desc
step 5 : I want the finale result ordered by date :
select Final.dte from ( <<above query>> ) Final order by Final.dte
RESULT :
*/
select Final.dte from
(
select TOP 6 Alldte.dte from
(
select id='999999999',dte = min(dte) from dbo.tst
union all
select id='999999998',dte = max(dte) from dbo.tst
union all
select id='8' + convert(varchar(8),step2.dte,112),step2.dte from
(
select TOP 4 month = left(convert(char(8),dte,112),6) ,dte = max(dte)
from dbo.tst
where left(convert(char(8),dte,112),6) != ( select left(convert(char(8),min(dte),112),6) from dbo.tst )
group by left(convert(char(8),dte,112),6)
having max(dte) not in ( select min(dte) from dbo.tst union all select max(dte) from dbo.tst )
order by dte desc
) step2
union all
select id='7' + convert(varchar(8),step3.dte,112),step3.dte from
(
select TOP 6 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 step2.dte from
(
select TOP 4 month = left(convert(char(8),dte,112),6) ,dte = max(dte)
from dbo.tst
where left(convert(char(8),dte,112),6) != ( select left(convert(char(8),min(dte),112),6) from dbo.tst )
group by left(convert(char(8),dte,112),6)
having max(dte) not in ( select min(dte) from dbo.tst union all select max(dte) from dbo.tst )
order by dte desc
) step2
)
order by dte desc
) step3
) Alldte
order by Alldte.id desc
) Final
order by Final.dte
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply