Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Query on Active/Inactive history table Expand / Collapse
Author
Message
Posted Tuesday, July 01, 2008 7:34 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 2:09 PM
Points: 12, Visits: 137
Assumption:
You have an Account_Master table (and may be some other table(s) you need for the report) as follows
account_id int,
account_name varchar(100),
..
etc..

And you want to run a query for the report where you pass a date value as an input parameter, I would do something like this:

SELECT T1.ACCOUNT_ID, T1.ACCOUNT_NAME, ....
, (CASE WHEN T2.ACTIVE = 1 THEN 'Active' ELSE 'Inactive' END) as ACCOUNT_STATUS
FROM Account_Master AS T1
LEFT OUTER JOIN
( SELECT ACCOUNT_ID, ACTIVE FROM
(SELECT account_id, active, status_date
, ROW_NUMBER() OVER(PARTITION BY account_id ORDER BY status_date DESC) AS ROWNUM
FROM account_DeactivationReactivationHistory ) AS T2
WHERE ROWNUM = 1
) AS T2
ON T1.ACCOUNT_ID = T2.ACCOUNT_ID


HTH



Post #526980
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse