Query help or Suggestion - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Query help or Suggestion

Author

Topic

PatDeV
Posting Yak Master

197 Posts

Posted - 2006-06-15 : 17:07:50

select distinct testkey
from Test
WHERE
(Flag = 1)
and
(
(testType LIKE 'ES-%')
OR (testType LIKE 'RS-%')
OR (testType LIKE 'DELL-%')
)
AND (testType NOT LIKE 'ES-PR%')

AND Status NOT IN ('R','S')
AND (StartDate <= @date)
AND (EndDate IS NULL OR EndDate > @date)

I have this query and want to run to get all the data for the each entry. Such as from 2001.

I have field startdate and enddate field.

I want to return all the data from the data given (01/01/2006)
and want to return data for each month or 0 if data is not in particular month.

for example

testkey Jan-2001 Feb-2001 ...........Jan-2006...June-2006
12345 1 1 5 6

Thanks

MichaelP
Jedi Yak

2489 Posts

Posted - 2006-06-15 : 17:22:46

Do a search on the main site for "crosstab" or "dynamic crosstab."
That's what you are wanting here.

Michael

<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda>

Opinions expressed in this post are not necessarily those of TeleVox Software, inc. All information is provided "AS IS" with no warranties and confers no rights.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-16 : 04:18:09

http://weblogs.sqlteam.com/jeffs/archive/2005/05.aspx

Madhivanan

Failing to plan is Planning to fail

PatDeV
Posting Yak Master

197 Posts

Posted - 2006-06-16 : 09:01:56

how can i use that in my query!! please help!!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-16 : 09:36:34

quote:
Originally posted by PatDeV

how can i use that in my query!! please help!!

Post your table structure, some sample data and result that you want

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-16 : 09:40:18

quote:
Originally posted by PatDeV

how can i use that in my query!! please help!!

Did you read the link I specified?

Tan, when did you go to Malaysia?

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-16 : 09:46:44

"Tan, when did you go to Malaysia?"
Was here on business trip for this entire week. Will be going back tomorrow.

PatDeV
Posting Yak Master

197 Posts

Posted - 2006-06-16 : 10:57:21

Test Table Structure:

testkey IDentityt,
Amount Number,
TestType Varchar(75,
status char(1),
Startdate datetime,
enddate datetime,
Flag int

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-17 : 05:05:40

How about same sample data and the result that you want ?

PatDeV
Posting Yak Master

197 Posts

Posted - 2006-06-19 : 10:26:56

testkey,Amount, TestType, status, Startdate, enddate, Flag
123 25000 Test Active 01/01/2006 12/31/2006 1
124 25640 Test2 Active 02/01/2006 12/31/2006 1
125 30000 Test3 Active 03/01/2006 12/31/2006 1
126 12540 Test4 Active 05/01/2006 12/31/2006 1
127 16854 Test5 Active 06/01/2006 12/31/2006 1

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-19 : 10:48:03

Something like this

select testkey,
       sum(case when Startdate between '20060101' and '20060131' then 1 else 0 end) as [Jan-2006],
       sum(case when Startdate between '20060201' and '20060228' then 1 else 0 end) as [Feb-2006],
       . . .
from   table
where  ( . . . )
group by testkey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources