How to count the record in between 2 dates

Question

Post reply

How to count the record in between 2 dates

aarthikamaraj

Newbie

Points: 1
More actions
June 16, 2009 at 12:45 am

#222617

create table #temp(s_no int, record_date date, stage varchar(1000))
insert into #temp
selectCOUNT(card_record_date) as s_no,
card_record_date as record_date,
'Carding' as stage
from ns_carding_details
where card_record_date between '04-04-2009' and '04-10-2009'
group by card_record_date
insert into #temp
selectCOUNT(drawingbr_record_date) as s_no,
drawingbr_record_date as record_date,
'Drawing Breaker' as stage
from ns_drawing_br_details
where drawingbr_record_date between '04/01/2009' and '04-10-2009'
group by drawingbr_record_date
insert into #temp
selectCOUNT(finisher_record_date) as s_no,
finisher_record_date as record_date,
'Drawing Finisher' as stage
from ns_drawing_finisher_details
where finisher_record_date between '04/01/2009' and '04-10-2009'
group by finisher_record_date
My output is
s_no record stage
32009-04-06Carding
32009-04-08Carding
32009-04-01Drawing Breaker
32009-04-02Drawing Breaker
32009-04-06Drawing Breaker
32009-04-08Drawing Breaker
32009-04-01Drawing Finisher
32009-04-02Drawing Finisher
32009-04-06Drawing Finisher
32009-04-08Drawing Finisher
but i need the output in this format
Stage 2009-04-01 2009-04-02 2009-04-06 2009-04-08
Carding 3 3
Drawing Breaker 3 3 3 3

Viewing 4 posts - 1 through 4 (of 4 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

Hi,

you could either use (dynamic) Cross Tabs or the PIVOT function.

Dynamic cross tabs are used if you don't have a fixed number of target columns (dates in your case).

Cross Tabs and Pivot: http://www.sqlservercentral.com/articles/T-SQL/63681/

Dynamic Cross Tabs: http://www.sqlservercentral.com/articles/cross+tab/65048/

If you need further assistance please provide sample data as described in the link in my signature.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Jeff Moden SSC Guru Points: 1004642 More actions · Answer 2

Heh... thanks for the plug, Lutz. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

LutzM SSC Guru Points: 107049 More actions · Answer 3

Hi Jeff,

You're welcome!

Why copy the code from your article when a link transports the same message and rewards the effort you took to put those helpful articles together? 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]