Need to count the number of orders placed in one year.

Question

Post reply

Need to count the number of orders placed in one year.

sanjeev_krs2004

Default port

Points: 1462
More actions
June 23, 2009 at 7:18 am

#135385

HI -
I have one question where I need to count the number of orders placed for a particular item.
For example:
Item Date
================================================
a 2008-01-01 00:00:00.000
a 2008-01-01 00:00:00.000
b 2008-01-01 00:00:00.000
c 2008-01-01 00:00:00.000
c 2009-01-01 00:00:00.000
c 2009-01-01 00:00:00.000
c 2009-01-01 00:00:00.000
b 2009-01-01 00:00:00.000
d 2009-01-01 00:00:00.000
d 2009-01-01 00:00:00.000
Now I want result something like:
2008 2009
====================
a 2 0
b 1 1
c 1 3
d 0 2
Please let me know if I am not clear here.
Thanks in advance..!!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Tom257 SSChampion Points: 11543 More actions · Answer 1

You could use something like

SELECT

COUNT(ITEM) OVER (PARTITION by Date) AS Count, Date

FROM Table

See the OVER() clause in BOL

you can probably use an expression like DATEPART(YEAR,Date) in the PARTITION bit

Adam Bean One Orange Chip Points: 26631 More actions · Answer 2

Adam Bean

One Orange Chip

Points: 26631

June 23, 2009 at 7:55 am

#1013943

Response after mine was better 🙂

t.hitendra SSCommitted Points: 1620 More actions · Answer 3

Hi there,

I have tried to solve your problem using row_number() and pivot function.

create table #tblItems(ItemName char(1),Date datetime)
insert into #tblItems
select 'a','2008-01-01 00:00:00.000' UNION ALL
select 'a','2008-01-01 00:00:00.000' UNION ALL
select 'b','2008-01-01 00:00:00.000' UNION ALL
select 'c','2008-01-01 00:00:00.000' UNION ALL
select 'c','2009-01-01 00:00:00.000' UNION ALL
select 'c','2009-01-01 00:00:00.000' UNION ALL
select 'c','2009-01-01 00:00:00.000' UNION ALL
select 'b','2009-01-01 00:00:00.000' UNION ALL
select 'd','2009-01-01 00:00:00.000' UNION ALL
select 'd','2009-01-01 00:00:00.000';
select ItemName,[2008] ,[2009]
from
(
select ItemName,year(Date) as Date
,count(ItemName) over (partition by Date,ItemName) as CntItem
from #tblItems
) P
Pivot( Count(CntItem) For Date in ([2008],[2009])
) as pvt
drop table #tblItems

Check it out.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Here is another solution that I think is a bit simplier, but since I seem to be having a problem (again) posting the code directly in the thread, it is attached as a text file.

Tom257 SSChampion Points: 11543 More actions · Answer 5

Adam Bean (6/23/2009)
Response after mine was better 🙂

And mine was really lazy - just a hint at what might get the OPs problem solved.

But I'll leave it there.

All points of view should be counted.

sanjeev_krs2004 Default port Points: 1462 More actions · Answer 6

Hi -

I have modified the suggested solution a bit....now I am not using #table.....But it has solved my problem.

Thanks a lot for your efforts and time on this. I appreciate this.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

As it appears you were provided several approaches to solve your problem, which one did you select?