January 31, 2011 at 2:12 pm
[font="Courier New"]Hello SQL Gurus,
I need some help here. I have part of the problem solved but having difficulty in getting the problem fully solved.
/** Requirement: If the customer is having all Active Invoices as active
then get the Max of the Active Invoice; If all Inactive Invoices then
get Max of the Inactive Invoice; If the customer has both then the Max
of the Active one should be returned and not the max of Inactive One
*/
--
CREATE TABLE my_table(mt_Cust_Id VARCHAR(10)
,mt_state VARCHAR(1) --1 for Active , 2 for Inactive
,mt_Invoice_No BIGINT
)
GO
BEGIN
--cust 1 all active
insert into my_table values ('Cust_Id_1' , '1' ,10001);
insert into my_table values ('Cust_Id_1' , '1' ,10999); -- Should be returned for Cust_Id_1
--cust 2 All inactive
insert into my_table values ('Cust_Id_2' , '2' ,20001);
insert into my_table values ('Cust_Id_2' , '2' ,20999); --Should be returned for Cust_Id_2
--cust 3 Two active Two Inactive
insert into my_table values ('Cust_Id_3' , '1' ,30001);
insert into my_table values ('Cust_Id_3' , '1' ,30999); --Should be returned for Cust_Id_3
insert into my_table values ('Cust_Id_3' , '2' ,31000);
insert into my_table values ('Cust_Id_3' , '2' ,31101);
--cust 4 Three active Two Inactive
insert into my_table values ('Cust_Id_4' , '1' ,40001);
insert into my_table values ('Cust_Id_4' , '1' ,40002);
insert into my_table values ('Cust_Id_4' , '1' ,40999); --Should be returned for Cust_Id_4
insert into my_table values ('Cust_Id_4' , '2' ,41001);
END
GO
SELECT TOP 1 sq0.mt_Cust_Id ,sq1.state_X, sq1.mt_Invoice_No , sq1.rownum
FROM my_table sq0
JOIN
(
SELECT mt_Cust_Id
,CASE mt_state WHEN 1 THEN 'Active'
WHEN 2 THEN 'Inactive'
END State_X
,mt_Invoice_No
,ROW_NUMBER() OVER (PARTITION BY mt_Cust_Id , (CASE mt_state WHEN 2 THEN 1 ELSE mt_state END)
ORDER BY mt_Cust_Id , mt_state DESC
) rownum
FROM my_table
--ORDER BY 1,4 DESC
) AS sq1
ON sq0.mt_cust_Id = sq1.mt_Cust_Id
AND CASE sq0.mt_state WHEN 1 THEN 'Active' WHEN 2 THEN 'Inactive' END = sq1.state_X
AND sq0.mt_Invoice_No = sq1.mt_Invoice_No
WHERE sq0.mt_cust_Id = 'Cust_Id_4'
AND CASE sq0.mt_state WHEN 1 THEN 'Active' WHEN 2 THEN 'Inactive' END = sq1.state_X
ORDER BY sq0.mt_Cust_Id , sq1.rownum DESC
GO
Returns correctly
/*
mt_Cust_Id state_X mt_Invoice_No rownum
---------- -------- -------------------- --------------------
Cust_Id_4 Active 40999 4
(1 row(s) affected)
*/
How do i modify this query so that i get the output as follows:
Cust_Id_1 10999
Cust_Id_2 20999
Cust_Id_3 30999
Cust_Id_4 40999
I know i am missing something obvious and which is within reach. Any inputs would be appreciated.
[/font]
January 31, 2011 at 2:25 pm
I would personally approach this in a different method, using a full outer join structure.
CREATE NONCLUSTERED INDEX idx_my_table ON #my_table (mt_state, mt_cust_id, mt_Invoice_No)
GO
SELECT
ISNULL( drvActive.mt_cust_id, drvInactive.mt_cust_id) AS mt_Cust_id,
ISNULL( drvActive.MaxActive, drvInactive.MaxInactive) AS MaxInvoiceNo
FROM
(SELECT
mt_cust_id,
MAX( mt_Invoice_No) AS MaxActive
FROM
#my_table
WHERE
mt_state = '1'
GROUP BY
mt_cust_id
) AS drvActive
FULL OUTER JOIN
(SELECT
mt_cust_id,
MAX( mt_Invoice_No) AS MaxInactive
FROM
#my_table
WHERE
mt_state = '2'
GROUP BY
mt_cust_id
) AS drvInactive
ONdrvActive.mt_cust_id = drvInactive.mt_cust_id
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 31, 2011 at 2:33 pm
[font="Courier New"]Thanks Craig.. I think i found the solution soon after posting 🙂 but by using the CROSS APPLY feature. I was trying JOIN etc and they were not allowing me to do a ORDER BY inside;
SELECT sq99.mt_cust_id ,CASE sq99.mt_state WHEN 1 THEN 'Active' WHEN 2 THEN 'Inactive' END state
, sq99.mt_Invoice_No , SubQuery.MAX_INVOICE_NO
FROM my_table sq99
CROSS APPLY
(
SELECT TOP 1 sq0.mt_Cust_Id , sq1.state_X Modified_State, sq1.mt_Invoice_No AS MAX_INVOICE_NO
, sq1.rownum
FROM my_table sq0
JOIN
(
SELECT mt_Cust_Id
,CASE mt_state WHEN 1 THEN 'Active'
WHEN 2 THEN 'Inactive'
END State_X
,mt_Invoice_No
,ROW_NUMBER() OVER (PARTITION BY mt_Cust_Id , (CASE mt_state WHEN 2 THEN 1 ELSE mt_state END)
ORDER BY mt_Cust_Id , mt_state DESC
) rownum
FROM my_table
--ORDER BY 1,4 DESC
) AS sq1
ON sq0.mt_cust_Id = sq1.mt_Cust_Id
AND CASE sq0.mt_state WHEN 1 THEN 'Active' WHEN 2 THEN 'Inactive' END = sq1.state_X
AND sq0.mt_Invoice_No = sq1.mt_Invoice_No
WHERE sq0.mt_cust_Id = sq99.mt_Cust_Id
AND CASE sq0.mt_state WHEN 1 THEN 'Active' WHEN 2 THEN 'Inactive' END = sq1.state_X
ORDER BY sq0.mt_Cust_Id , sq1.rownum DESC
) SubQuery
GO
Gives the desired result
mt_cust_id state mt_Invoice_No MAX_INVOICE_NO
---------- -------- -------------------- --------------------
Cust_Id_1 Active 10001 10999
Cust_Id_1 Active 10999 10999
Cust_Id_2 Inactive 20001 20999
Cust_Id_2 Inactive 20999 20999
Cust_Id_3 Active 30001 30999
Cust_Id_3 Active 30999 30999
Cust_Id_3 Inactive 31000 30999
Cust_Id_3 Inactive 31101 30999
Cust_Id_4 Active 40001 40999
Cust_Id_4 Active 40002 40999
Cust_Id_4 Active 40999 40999
Cust_Id_4 Inactive 41001 40999
(12 row(s) affected)
Nevertheless a Million thanks for the quick reply. I achieved the same result using a function earlier. But was preferring to avoid a temporary table or a function.
Thanks Again.[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply