In SQL Server 2005 I have a table with data that looks something like this:
WTN------------Date
555-111-1212 2009-01-01
555-111-1212 2009-01-02
555-111-1212 2009-01-03
555-111-1212 2009-01-15
555-111-1212 2009-01-16
212-999-5555 2009-01-01
212-999-5555 2009-01-10
212-999-5555 2009-01-11
From this I would like to extract WTN, Min(Date), Max(Date) the twist is I would like to also break whenever there is a gap in the dates, so from the above data, my results should look like:
WTN------------ MinDate---- MaxDate
555-111-1212 2009-01-01 2009-01-03
555-111-1212 2009-01-15 2009-01-16
212-999-5555 2009-01-01 2009-01-01
212-999-5555 2009-01-10 2009-01-11
The SQL GROUP BY clause is used along with some aggregate functions to group columns that have the same values in different rows. The group by multiple columns technique is used to retrieve grouped column values from one or more tables of the database by considering more than one column as grouping criteria.
Because the COALESCE is an expression, you can use it in any clause that accepts an expression such as SELECT , WHERE , GROUP BY , and HAVING .
Syntax: SELECT column1, function_name(column2) FROM table_name WHERE condition GROUP BY column1, column2 HAVING condition ORDER BY column1, column2; function_name: Name of the function used for example, SUM() , AVG().
Why is everyone so dead set against using a table for this kind of thing? A table of numbers or a calendar table takes up such little space and is probably in memory if referenced enough anyway. You can also derive a numbers table pretty easily on the fly using ROW_NUMBER(). Using a numbers table can help with the understanding of the query. But here is a not-so-straightforward example, a trick I picked up from Plamen Ratchev a while back, hope it helps.
DECLARE @wtns TABLE
(
WTN CHAR(12),
[Date] SMALLDATETIME
);
INSERT @wtns(WTN, [Date])
SELECT '555-111-1212','2009-01-01'
UNION ALL SELECT '555-111-1212','2009-01-02'
UNION ALL SELECT '555-111-1212','2009-01-03'
UNION ALL SELECT '555-111-1212','2009-01-15'
UNION ALL SELECT '555-111-1212','2009-01-16'
UNION ALL SELECT '212-999-5555','2009-01-01'
UNION ALL SELECT '212-999-5555','2009-01-10'
UNION ALL SELECT '212-999-5555','2009-01-11';
WITH x AS
(
SELECT
[Date],
wtn,
part = DATEDIFF(DAY, 0, [Date])
+ DENSE_RANK() OVER
(
PARTITION BY wtn
ORDER BY [Date] DESC
)
FROM @wtns
)
SELECT
WTN,
MinDate = MIN([Date]),
MaxDate = MAX([Date])
FROM
x
GROUP BY
part,
WTN
ORDER BY
WTN DESC,
MaxDate;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With