June 15, 2006 at 3:08 pm
Hi all,
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
June 19, 2006 at 8:00 am
This was removed by the editor as SPAM
June 20, 2006 at 4:15 am
Your demand is not very clear but according to what I understand.
You must retrieve data you need.
For example
testkey cDate count(*)
12345 2006-01-01 5
54321 2006-01-01 3
12345 2006-02-01 10
54321 2006-02-01 6
And next, you must do a pivot on this recordset.
Pivot result will be :
testkey 2006-01-01 2006-02-01
12345 5 10
54321 3 6
If you use SQL 2k5, you're lucky because it exists a pivot function else
if you use SQL 2k, it much more complicated because you have to develop the pivot function, code is available on Internet.
Good luck
fabricej
June 20, 2006 at 4:59 pm
No your requirement are not very clear.
You want data for each month. What data?
You have start and end date. So are you counting this as a range of days, and does that mean that your monthly data will consist of a count of the number of days covered and how many times covered? E.g. if your data were about work shifts, you want a total of person-days per month?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply