quote:

---

Originally posted by alanhuro

1. How can you add another column for the end date for the same category. Example

Date-------------Cat
2008-07-01-------A
2008-07-03-------B
2008-07-07-------C
2008-07-10-------A
2008-07-11-------B
2008-07-13-------A

The result I want return is

Cat--------Begin Date------------End Date
A----------2008-07-01------------2008-07-10
A----------2008-07-10------------2008-07-13
A----------2008-07-13------------Null


2. My second question is what query to return the column name within a table. Take an example above the result should return

Date and cat. Since there are 2 columns in that example table?

Thanks a bunch for your help.

Alan

---

SELECT IDENTITY(int,1,1) AS Seq,
       Date,Cat INTO #Temp
FROM YourTable
ORDER BY Cat,Date


SELECT Cat,[Begin Date],[End Date]
FROM
(
SELECT t1.Cat,t1.Date AS [Begin Date],t2.Date AS [End Date]
FROM #Temp t1
INNER JOIN #Temp t2
ON t2.Seq=t1.Seq + 1
AND t2.Cat=t1.Cat

UNION ALL

SELECT Cat,Date,NULL
FROM #Temp t
INNER JOIN (SELECT Cat,MAX(Date) AS MaxDate
            FROM #Temp
            GROUP BY Cat)tmp
ON tmp.Cat=t.Cat
AND tmp.MaxDate=t.Date)r

ORDER BY r.Cat

quote:

---

Originally posted by khtan

is INFORMATION_SCHEMA.COLUMNS available in Oracle also ? alanhuro is using Oracle.
See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=100617

---

KH
[spoiler]Time is always against us[/spoiler]

---

DECLARE 
-- Setup
@Yak TABLE (Date DATETIME, Cat CHAR(1))

INSERT @Yak
SELECT '20080701', 'A'
UNION ALL SELECT '20080703', 'B'
UNION ALL SELECT '20080707', 'C'
UNION ALL SELECT '20080710', 'A'
UNION ALL SELECT '20080711', 'B'
UNION ALL SELECT '20080713', 'A'

-- Using correlated sub-query
SELECT 
	Cat,
	Date AS StartDate,
	(SELECT MIN(Date) FROM @Yak WHERE Date > A.Date and Cat = A.Cat) AS EndDate
FROM
	@Yak AS A
WHERE
	A.Cat = 'A'
ORDER BY
	A.Date

-- Using ROW_NUMBER
SELECT
	A.Cat,
	A.Date AS StartDate,
	B.Date AS EndDate
FROM
	(
		SELECT
			Cat,
			Date,
			ROW_NUMBER() OVER (PARTITION BY Cat ORDER BY Date) AS RowNum
		FROM
			@Yak
	) AS A
LEFT OUTER JOIN
	(
		SELECT
			Cat,
			Date,
			ROW_NUMBER() OVER (PARTITION BY Cat ORDER BY Date) AS RowNum
		FROM
			@Yak
	) AS B
	ON A.RowNum + 1 = B.RowNum
	AND A.Cat = B.Cat
WHERE
	A.Cat = 'A'
ORDER BY
	A.Date

quote:

---

Originally posted by Lamprey

Here are a couple other ways to query the data you want:

DECLARE 
-- Setup
@Yak TABLE (Date DATETIME, Cat CHAR(1))

INSERT @Yak
SELECT '20080701', 'A'
UNION ALL SELECT '20080703', 'B'
UNION ALL SELECT '20080707', 'C'
UNION ALL SELECT '20080710', 'A'
UNION ALL SELECT '20080711', 'B'
UNION ALL SELECT '20080713', 'A'

-- Using correlated sub-query
SELECT 
	Cat,
	Date AS StartDate,
	(SELECT MIN(Date) FROM @Yak WHERE Date > A.Date and Cat = A.Cat) AS EndDate
FROM
	@Yak AS A
WHERE
	A.Cat = 'A'
ORDER BY
	A.Date

-- Using ROW_NUMBER
SELECT
	A.Cat,
	A.Date AS StartDate,
	B.Date AS EndDate
FROM
	(
		SELECT
			Cat,
			Date,
			ROW_NUMBER() OVER (PARTITION BY Cat ORDER BY Date) AS RowNum
		FROM
			@Yak
	) AS A
LEFT OUTER JOIN
	(
		SELECT
			Cat,
			Date,
			ROW_NUMBER() OVER (PARTITION BY Cat ORDER BY Date) AS RowNum
		FROM
			@Yak
	) AS B
	ON A.RowNum + 1 = B.RowNum
	AND A.Cat = B.Cat
WHERE
	A.Cat = 'A'
ORDER BY
	A.Date

---

quote:

---

Originally posted by alanhuro

That is quick. Thank for your help you guy. I still have a problem.

- Khtan you are right I try to run the query on the Oracle db. I though the query is going to be the same.

- visakh16. I tried your script and the error I got is "ORA-00904: "IDENTITY": invalid identifier". Is there any other way? I just have a read access to the db so I can not create any table or made any change to the DB. I try to avoid doing that too. I will shade my head if I try to.

Peso - I try your script and got an error "ORA-00942: table or view does not exist" maybe I miss something.

Thanks

---

quote:

---

Originally posted by visakh16
Not sure if OP uses SQL 2005. thats why i gave 2000 based solution

---

quote:

---

Originally posted by alanhuro

Lamprey.

I have to say you are a truly genius. It works like charm on the first tried. I can not recall how many time you save me on the SQL query problem. Thank again for your help.

Maybe you should write a book just for advanced query to cover all the senarios. . Let me know I will need one.

thanks

---

quote:

---

Originally posted by Lamprey

quote:

---

Originally posted by visakh16
Not sure if OP uses SQL 2005. thats why i gave 2000 based solution

---

Ahh, I guess I don't even think about that unless I see strange syntax. :) EDIT: My brain is not workign too well. I was quoting you, but I was talking about weather the OP used Oracle or not.. <sigh>

quote:

---

Originally posted by alanhuro

Lamprey.

I have to say you are a truly genius. It works like charm on the first tried. I can not recall how many time you save me on the SQL query problem. Thank again for your help.

Maybe you should write a book just for advanced query to cover all the senarios. . Let me know I will need one.

thanks

---

You are welcome. I think there are plenty of books on queries. However, I'm tempted to write one on data warehousing to show how it should be done. ;)

---

DECLARE 
-- Setup
@Yak TABLE (Date DATETIME, Cat CHAR(1))

INSERT @Yak
SELECT '20080701', 'A'
UNION ALL SELECT '20080703', 'A'
UNION ALL SELECT '20080707', 'A'
UNION ALL SELECT '20080710', 'B'
UNION ALL SELECT '20080711', 'B'
UNION ALL SELECT '20080713', 'A'
UNION ALL SELECT '20080715', 'A'
UNION ALL SELECT '20080718', 'A'
UNION ALL SELECT '20080719', 'C'


select * from @YAK
-- Using correlated sub-query
SELECT 
	Cat, min(Date) AS StartDate,
	(SELECT MIN(Date) FROM @Yak WHERE Date > min(Date) and Cat <> 'A') AS EndDate
FROM
	@Yak AS A
WHERE
	A.Cat = 'A'
group by min(Date)
ORDER BY A.Date

DECLARE 
-- Setup
@T TABLE (Date DATETIME, Cat CHAR(1))

INSERT @T
SELECT '20080701', 'A'
UNION ALL SELECT '20080703', 'A'
UNION ALL SELECT '20080707', 'A'
UNION ALL SELECT '20080710', 'B'
UNION ALL SELECT '20080711', 'B'
UNION ALL SELECT '20080713', 'A'
UNION ALL SELECT '20080715', 'A'
UNION ALL SELECT '20080718', 'A'
UNION ALL SELECT '20080719', 'C'
UNION ALL SELECT '20080722', 'A'
UNION ALL SELECT '20080724', 'A'

SELECT MIN(T3.Date) AS MinDate
     ,MAX(T3.DATE) AS MaxDate
     , T3.cat
FROM 
   (SELECT (t.Date), Cat 
         ,(SELECT Count(*) FROM @t t2 WHERE t2.date<=t.date AND T2.cat <>'a') AS Counter
   FROM @t T) T3
GROUP BY T3.CAT, Counter
HAVING T3.Cat ='a'