Group by to get the exact date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Group by to get the exact date

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-04-18 : 05:53:48

Hi Guys,

I could not figure out to get createdate time using group by even i use case statement still i have duplicate records

I have to group by itemid but my concern, i could not get the correct
data. The createddate time of Itemid with "OEM" should be the one to be display. while if it single itemid the createddate is still the same. any inputs guys. thank you in advance...

SAMPLE DATA


Create table #myTable
(Itemid nvarcahr(35),
 Remarks nvarchar(15),
 CreatedDate datetime
order int)
 
Insert into #myTable(Itemid,Remarks,CreatedDate, order) values('HTC1005-S','-S','2011-10-03 04:00:00.000', 10)
Insert into #myTable(Itemid,Remarks,CreatedDate) values('HTC1006-R','-R','2011-10-03 04:00:00.000', 5)
Insert into #myTable(Itemid,Remarks,CreatedDate) values('HTC1006','OEM','2010-09-03 04:00:00.000', 10)

Derived Result:

Itemid--CreatedDate---- order
-----------------------------
HTC1005-- -- 2011-10-03--10 
HTC1006-- -- 2010-09-03--15


;With CTE
AS
(
Select Left(Itemd,7) as Itemid, Remarks, CreatedDate, sum(order)
From #mytable
Group By Left(Itemd,7), Remarks
) 
Select * from CTE

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-04-18 : 06:14:15

Try this


select 
	case when Itemid like '%-%' then substring(itemid,1,charindex('-',itemid)-1) else itemid end as itemid,
	min(dateadd(day,datediff(day,0,CreatedDate),0)) as CreatedDate,sum(orders) asorders 
from #mytable
	group by case when Itemid like '%-%' then substring(itemid,1,charindex('-',itemid)-1) else itemid end

Madhivanan

Failing to plan is Planning to fail

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-04-18 : 08:53:09

Hi Madhivanan,

Thank you for the reply.
try this tomorrow morning..

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-04-19 : 03:17:02

Hi Madhivanan,

Thank you very much.. it's working..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources