CODEWITHSUNDEEP
sqlsql-server
Abhijith m jayan
Abhijith m jayan

Reputation: 1

Group rows in table by date

I have table in sql server as follows

id   | name   |    Date      | ---- |-----
-------------------------------------------

id1  | name1  | 24/09/2014   | ---- |-----
id2  | name2  | 23/09/2014   | ---- |-----
id3  | name3  | 23/09/2014   | ---- |-----
id4  | name4  | 15/09/2014   | ---- |-----
id5  | name5  | 01/08/2014   | ---- |-----
id6  | name6  | 01/08/2013   | ---- |-----

How Can i display that in a listview as

Today
----------
name 1 date :24/09/2014

yesterday
---------
name2 date :23/09/2014
name3 date :23/09/2014
last week
---------
......... date :15/09/2014

two weeks ago
--------------
..............
last month
--------------
..............
older
--------------
..............

thank you

Upvotes: 0

Views: 107

Answers (3)

Ajay2707
Ajay2707

Reputation: 5798

All you want from sqlserver it is totally bad idea, some thing you need to c# side. I created a sample in which you just pass the date and give the result.

begin  tran
--drop table tempnew
--create table tempnew 
--(id varchar(50), name varchar(100), date1 date)
--set dateformat dmy
--insert into tempnew
--values
--( 'id1' , 'name1', '24/09/2014' )
--,( 'id2' , 'name2', '23/09/2014' )
--,( 'id3' , 'name3', '23/09/2014' )
--,( 'id4' , 'name4', '22/09/2014' )
--,( 'id5' , 'name5', '15/09/2014' )

--create procedure getdataBydate 
--(
--  @paraDate date = null
--)
--as
declare @paraDate date = getdate() -3
declare @lastmonthofday int 
select @lastmonthofday = datepart(DD, DATEADD(d, -1, DATEADD(m, DATEDIFF(m, 0, GETDATE()) + 1, 0)) )
select @paraDate,@lastmonthofday,* from tempnew 

select  name , 'date :',  convert( varchar ,date1, 106) date1, 
case 
    when @paraDate = cast ( getdate() AS DATE) then 'Today <br> ----- <br>' 
    when @paraDate = cast ( dateadd(dd,-1, getdate()) as DATE) then 'Yesterday <br> ----- <br>' 
    when @paraDate >= cast ( dateadd(dd,-14, getdate()) as DATE) then 'two weeks ago <br> ----- <br>'   
    when @paraDate = cast ( dateadd(dd,-@lastmonthofday, getdate()) as DATE) then 'last month <br> ----- <br>' 
    else 'older <br> ----- <br>' 
end 
 from tempnew 
 where DATE1 <= GETDATE() 
 and date1 >= @paraDate

 --select  *
 --from tempnew 
 --where DATE1 <= GETDATE() 
 --and date1 >= @paraDate
 ----I don't know why between not work here
 ----between cast( '2014-09-24' as DATE) and cast( '2014-09-15' as date)
 ----cast DATE1  between cast( GETDATE() AS date) and @paraDate


--drop table tempnew
rollback

Upvotes: 0

arunbabu
arunbabu

Reputation: 289

please try the below code. If you are expecting different result please post the o/p clearly.

SELECT  concat(name ,' Date :',date ) 'Today'
    FROM    #tmp
    WHERE    DATEDIFF(d,GETDATE() ,date) = 0
SELECT  concat(name ,' Date :',date ) 'yesterday'
    FROM    #tmp
    WHERE    DATEDIFF(d,GETDATE() ,date) = -1

Upvotes: 1

Mairaj Ahmad
Mairaj Ahmad

Reputation: 14604

If you want this in SQL

select id,name,date from yourtable group by id,name,date

Upvotes: 0

Related Questions