December 10, 2007 at 5:24 pm
Hi I having a condition where the data is as follows
PFID MONTH DATE
1 1 date1
1 2 date2
1 3 date3
1 4 date4
1 5 date5
1 6 date6
1 7 date7
1 8 date8
1 9 date9
1 10 date10
The required output needs to be based on the quarters
PFID Quarter1 Quarter2 Quarter3 Quarter4
1 date1 date4 date7 date10
1 date2 date5 date8
1 date3 date6 date9
I tried the normal CASE statement but then the output generated does have NULL and is of the form
PFID Quarter1 Quarter2 Quarter3 Quarter4
1 date1 NULL NULL NULL
1 date2 NULL NULL NULL
1 date3 NULL NULL NULL
1 NULL date4 NULL NULL
1 NULL date5 NULL NULL
1 NULL date6 NULL NULL
1 NULL NULL date7 NULL
1 NULL NULL date8 NULL
1 NULL NULL date9 NULL
1 NULL NULL NULL date10
1 NULL NULL NULL date11
1 NULL NULL NULL date12
I am trying to eliminate the NULL values
Thanks
Vinuverma
December 11, 2007 at 6:29 am
Can we see your case statement? Normally when we pivot, we include an aggregation along with a grouping to eliminate the values that were excluded in the case statement. I posted this query in another topic on pivot queries that works against the AdventureWorks database.
SELECT
soh.[SalesPersonID]
,Jan = sum(case when month(orderdate) = 1 Then SubTotal Else 0 End)
,Feb = sum(case when month(orderdate) = 2 Then SubTotal Else 0 End)
,Mar = sum(case when month(orderdate) = 3 Then SubTotal Else 0 End)
,Apr = sum(case when month(orderdate) = 4 Then SubTotal Else 0 End)
,May = sum(case when month(orderdate) = 5 Then SubTotal Else 0 End)
,Jun = sum(case when month(orderdate) = 6 Then SubTotal Else 0 End)
,Jul = sum(case when month(orderdate) = 7 Then SubTotal Else 0 End)
,Aug = sum(case when month(orderdate) = 8 Then SubTotal Else 0 End)
,Sep = sum(case when month(orderdate) = 9 Then SubTotal Else 0 End)
,Oct = sum(case when month(orderdate) = 10 Then SubTotal Else 0 End)
,Nov = sum(case when month(orderdate) = 11 Then SubTotal Else 0 End)
,Dec = sum(case when month(orderdate) = 12 Then SubTotal Else 0 End)
FROM [Sales].[SalesPerson] sp
INNER JOIN [Sales].[SalesOrderHeader] soh
ON sp.[SalesPersonID] = soh.[SalesPersonID]
Where Year(soh.OrderDate) = 2002
group by soh.SalesPersonID
order by salespersonid
Hope this helps.
-Jeremy
December 11, 2007 at 7:40 am
It appears that the columns in your desired output are quarters, while the rows are the first, second, and third month of each quarter. If you can compute values to show what row & column each value should be in you can use a PIVOT query.
[font="Courier New"]SET NOCOUNT ON
CREATE TABLE vinu (
PFIDSMALLINT NOT NULL,
[MONTH]SMALLINT NOT NULL,
[DATE]SMALLDATETIME)
INSERT INTO vinu (PFID, [MONTH], [DATE]) SELECT 1, 1, DATEADD(minute, 44640 * RAND(), '1/1/2007')
INSERT INTO vinu (PFID, [MONTH], [DATE]) SELECT 1, 2, DATEADD(minute, 40320 * RAND(), '2/1/2007')
INSERT INTO vinu (PFID, [MONTH], [DATE]) SELECT 1, 3, DATEADD(minute, 44640 * RAND(), '3/1/2007')
INSERT INTO vinu (PFID, [MONTH], [DATE]) SELECT 1, 4, DATEADD(minute, 43200 * RAND(), '4/1/2007')
INSERT INTO vinu (PFID, [MONTH], [DATE]) SELECT 1, 5, DATEADD(minute, 44640 * RAND(), '5/1/2007')
INSERT INTO vinu (PFID, [MONTH], [DATE]) SELECT 1, 6, DATEADD(minute, 43200 * RAND(), '6/1/2007')
INSERT INTO vinu (PFID, [MONTH], [DATE]) SELECT 1, 7, DATEADD(minute, 44640 * RAND(), '7/1/2007')
INSERT INTO vinu (PFID, [MONTH], [DATE]) SELECT 1, 8, DATEADD(minute, 44640 * RAND(), '8/1/2007')
INSERT INTO vinu (PFID, [MONTH], [DATE]) SELECT 1, 9, DATEADD(minute, 43200 * RAND(), '9/1/2007')
INSERT INTO vinu (PFID, [MONTH], [DATE]) SELECT 1, 10, DATEADD(minute, 44640 * RAND(), '10/1/2007')
SELECT * FROM vinu
SELECT PFID, [1] AS Quarter1, [2] AS Quarter2, [3] AS Quarter3, [4] AS Quarter4
FROM (
SELECT PFID, [DATE], ([MONTH] - 1) % 3 AS MIQ, ([MONTH] + 2) / 3 AS Q
FROM vinu) p
PIVOT (
MIN([DATE])
FOR Q IN ([1], [2], [3], [4])
) AS pvt
ORDER BY PFID, MIQ
DROP TABLE vinu
PFID Quarter1 Quarter2 Quarter3 Quarter4
------ ----------------------- ----------------------- ----------------------- -----------------------
1 2007-01-16 03:13:00 2007-04-14 11:55:00 2007-07-24 18:39:00 2007-10-27 17:16:00
1 2007-02-08 23:17:00 2007-05-20 10:58:00 2007-08-11 14:45:00 NULL
1 2007-03-10 02:59:00 2007-06-11 22:22:00 2007-09-06 13:45:00 NULL
[/font]
December 11, 2007 at 9:47 am
This is my query
SELECT
POLICYID
,TRANSACTIONID
,MAX(CASE
WHEN TRANSACTIONID IN (1,2,3) THEN PAYMENTDT
END) AS Q1
,MAX(CASE
WHEN TRANSACTIONID IN (4,5,6) THEN PAYMENTDT
END) AS Q2
,MAX(CASE
WHEN TRANSACTIONID IN (7,8,9) THEN PAYMENTDT
END) AS Q3
,MAX(CASE
WHEN TRANSACTIONID IN (10,11,12) THEN PAYMENTDT
END) AS Q4
FROM T1
WHERE POLICYID = 21
GROUP BY POLICYID
,TRANSACTIONID
Infact the second column is a transaction and there can only be 12 transactions, so the first 3 should fall in Q1, second 3 in Q2 and so on
POLICYID TRANS Q1 Q2 Q3 Q4
21 1 2007-12-04 NULL NULL NULL
21 2 2008-01-01 NULL NULL NULL
21 3 2008-01-29 NULL NULL NULL
21 4 NULL 2008-02-26 NULL NULL
21 5 NULL 2008-03-25 NULL NULL
21 6 NULL 2008-04-22 NULL NULL
21 7 NULL NULL 2008-05-20 NULL
21 8 NULL NULL 2008-01-01 NULL
21 9 NULL NULL 2008-07-15 NULL
21 10 NULL NULL NULL 2008-08-12
21 11 NULL NULL NULL 2008-09-09
21 12 NULL NULL NULL 2008-10-07
Appreciate the help. Any advices please
Thanks
Vinoj
December 11, 2007 at 10:48 am
If you include TRANSACTIONID in your GROUP BY, you'll get a separate row for every transaction.
You could use "GROUP BY POLICYID, (TRANSACTIONID - 1) %3" to get the output you showed in the first post.
December 11, 2007 at 12:39 pm
Thanks a lot, it helped
Vinuverma
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply