Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

t-sql: Keeping track of cumulative counts

Posted on 2012-03-13
6
Medium Priority
?
465 Views
Last Modified: 2012-03-13
Techies--

Here is a distilled version of the source data:

dept      empNum            dateHired            dateTerm
10      100            3/1/2008
10      120            4/17/2008
10      122            4/18/2008            6/2/2011
10      138            5/7/2008
10            200            6/2/2011
10      201            6/2/2011
200      195            6/1/2011
200      196            6/1/2011
200      197            6/1/2011
200      207            6/3/2011      


Here is what I am trying to achieve:

dept      Date            HowManyHired      HowManyTerm      ActiveEmpls
10      6/1/2011            0            0            4
10      6/2/2011            2            1            5
10      6/3/2011            0            0            5
20      6/1/2011            3            0            3
20      6/2/2011            0            0            3
20      6/3/2011            1            0            4

Basically, I need to keep a cumulative tally of the active employees for a given department--taking into account hirings and terminations.

How do I accomplish this?

Also, I'm looking for some reference material on writing SQL for reports--or in general, understanding the concepts behind more complex SQL. If you have any references in mind, I am a willing student!:-)
0
Comment
Question by:Paula DiTallo
6 Comments
 
LVL 25

Expert Comment

by:jogos
ID: 37714743
Notice that you have to replace the TODO with the expression that you find that evaluates an employee as active

select x.dept, x.dateHired, sum(HowManyHired) as  HowManyHired, sum(howmanyTerm) as howmanyTerm , sum(asActiveEmpls) as asActiveEmpls
from 
select dept , dateHired ,count(*) as HowManyHired  ,    0 as HowManyTerm
, sum(case when TODO then 1 else 0 ) as asActiveEmpls
from dbo.tableA
group by dept, dateHired
union all 
select dept,  dateTerm, 0 as HowManyHired , count (*)  as     HowManyTerm, 0 asActiveEmpls
From dbo.tableA 
where dateterm is not null
group by dept, dateHired
) as x
order by x.dept, x.dateHired
GROUP by x.dept, x.dateHired

Open in new window

0
 

Author Comment

by:Paula DiTallo
ID: 37715206
Thanks Jogos-- the determining factor for active or non-active employees is the null value in the dateTerm. So if dateTerm is null, then that is an active employee if dateTerm is not null then that is an inactive employee.  How would I work that into a case statement with a sum() function?
0
 
LVL 25

Expert Comment

by:jogos
ID: 37715279
I see, it's including the other records

select x.dept, x.dateHired, sum(HowManyHired) as  HowManyHired, sum(howmanyTerm) as howmanyTerm , sum(asActiveEmpls) as asActiveEmpls
from 
select t1.dept , t1.dateHired ,count(*) as HowManyHired  ,    0 as HowManyTerm
, (select count(*) 
  from dbo.tableA as t2
  where t2.dept = t1.dept 
  and t2.dateHired <= t1.dateHired
  and (t2.dateTerm is null or t2.dateTerm >= t1.dateHired)
  ) as asActiveEmpls
from dbo.tableA as t1
group by t1.dept, t1.dateHired
union all 
select dept,  dateTerm, 0 as HowManyHired , count (*)  as     HowManyTerm, 0 asActiveEmpls
From dbo.tableA 
where dateterm is not null
group by dept, dateHired
) as x
order by x.dept, x.dateHired
GROUP by x.dept, x.dateHire

Open in new window

0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 18

Expert Comment

by:lludden
ID: 37715370
Here is my take:

DECLARE @T TABLE (DepID INT, EmpID INT, HireDate DATE, TermDate DATE)
INSERT INTO @T SELECT 10, 100, '3/1/2008',NULL
INSERT INTO @T SELECT 10, 120, '4/17/2008',NULL
INSERT INTO @T SELECT 10, 122, '4/18/2008', '6/2/2011'
INSERT INTO @T SELECT 10, 138, '5/7/2008',NULL
INSERT INTO @T SELECT 10 ,200 ,'6/2/2011',NULL
INSERT INTO @T SELECT 10, 201, '6/2/2011',NULL
INSERT INTO @T SELECT 200 ,195 ,'6/1/2011',NULL
INSERT INTO @T SELECT 200 ,196 ,'6/1/2011',NULL
INSERT INTO @T SELECT 200 ,197 ,'6/1/2011',NULL
INSERT INTO @T SELECT 200 ,207 ,'6/3/2011',NULL


DECLARE @StartDate DATE = '2011-06-01'
DECLARE @EndDate DATE = '2011-06-05'
;WITH --Generate a tally table from 0 to 9999
  N1(N) AS (
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
            SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
           ),
  N2(N) AS (SELECT 1 FROM N1 a, N1 b),
  N3(N) AS (SELECT 1 FROM N2 a, N2 b),
  Numbers(N) AS (SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) -1 FROM N3),
DL(CalDate, DeptID) AS (SELECT dateadd(day,Numbers.n,@StartDate), D.DepID
FROM Numbers
CROSS JOIN (SELECT DISTINCT DepID FROM @T) D
WHERE dateadd(day,Numbers.n,@StartDate) <= @EndDate
)
SELECT DL.DeptID, DL.CalDate, (SELECT count(*) FROM @T T WHERE T.HireDate= DL.CalDate AND T.DepID = DL.DeptID) AS Hired,
(SELECT count(*) FROM @T T WHERE T.TermDate= DL.CalDate) AS Termed,
(SELECT count(*) FROM @T T WHERE T.HireDate< DL.CalDate) AS  Active
 FROM dl

Open in new window


The first part just builds a temp table to test with (replace any references to it with your table).

Since we want to be able to (potentially) check status for all dates, not just dates with activities, we need to build a table to contain data to query off of, so we first build a numbers or tally table.  
We use this to calculate a list of every date for every department (the CROSS JOIN).  This gives us a table of every dept and every date in the range.  Then we run through this table and check each date/dept pair for count, term, and additions.  We could test for null for term date, or just use a date that is greater than the time frame of our query.

It is real handy to have a numbers table and a calendar table in your database for queries such as this.
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 37716264
I tried for improved efficiency, in particular to do only ONE scan/search of the original table.


SET NOCOUNT ON
-- create sample data
DECLARE @employee_status TABLE (dept INT, empNum INT, dateHired DATE, dateTerm DATE)
INSERT INTO @employee_status SELECT 10, 100, '3/1/2008',NULL
INSERT INTO @employee_status SELECT 10, 120, '4/17/2008',NULL
INSERT INTO @employee_status SELECT 10, 122, '4/18/2008', '6/2/2011'
INSERT INTO @employee_status SELECT 10, 138, '5/7/2008',NULL
INSERT INTO @employee_status SELECT 10 ,200 ,'6/2/2011',NULL
INSERT INTO @employee_status SELECT 10, 201, '6/2/2011',NULL
INSERT INTO @employee_status SELECT 200 ,195 ,'6/1/2011',NULL
INSERT INTO @employee_status SELECT 200 ,196 ,'6/1/2011',NULL
INSERT INTO @employee_status SELECT 200 ,197 ,'6/1/2011',NULL
INSERT INTO @employee_status SELECT 200 ,207 ,'6/3/2011',NULL
--
SET NOCOUNT OFF



-- main code begins here

DECLARE @startDate datetime
DECLARE @endDate datetime
DECLARE @base_active int

SET @startDate = '20110601'
SET @endDate = '20110607'


DECLARE @empl_chgs TABLE (
    dept int,
    date datetime,
    base_active int,
    how_many_hired int,
    how_many_term int,
    PRIMARY KEY (dept, date)
    )
   
INSERT INTO @empl_chgs ( dept, date, base_active, how_many_hired, how_many_term )
SELECT
    dept AS dept,
    CASE WHEN action = 'Hire' THEN CASE WHEN dateHired < @startDate THEN '19000101' ELSE dateHired END
        ELSE dateTerm END AS date,    
    SUM(CASE WHEN dateHired <= @startDate THEN 1 ELSE 0 END) AS base_active,
    SUM(CASE WHEN action = 'Hire' AND dateHired BETWEEN @startDate AND @endDate THEN 1 ELSE 0 END) AS HowManyHired,
    SUM(CASE WHEN action = 'Term' AND dateTerm BETWEEN @startDate AND @endDate THEN 1 ELSE 0 END) AS HowManyTerm  
FROM @employee_status
CROSS JOIN (
    SELECT 'Hire' AS action UNION ALL SELECT 'Term'
) AS Actions
WHERE
    dateHired <= @endDate AND
    (action = 'Hire' OR (action = 'Term' AND dateTerm IS NOT NULL))
GROUP BY
    dept,
    CASE WHEN action = 'Hire' THEN CASE WHEN dateHired < @startDate THEN '19000101' ELSE dateHired END
        ELSE dateTerm END
ORDER BY
    dept, date
   
--SELECT * FROM @employee_status
--SELECT * FROM @empl_chgs

;WITH
cteDigits AS (
    SELECT 0 AS digit UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
    SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
cteTally AS (
    SELECT [1000s].digit * 1000 + [100s].digit * 100 + [10s].digit * 10 + [1s].digit AS dayCounter
    FROM cteDigits [1s]
    CROSS JOIN cteDigits [10s]
    CROSS JOIN cteDigits [100s]
    CROSS JOIN cteDigits [1000s]    
)
SELECT
    depts.Dept, dates.Date,
    COALESCE(ec.how_many_hired, 0) AS HowManyHired, COALESCE(ec.how_many_term, 0) AS HowManyTerm,
    (SELECT SUM(CASE WHEN ec2.date = '19000101' THEN ec2.base_active ELSE ec2.how_many_hired END) -
        SUM(CASE WHEN ec2.date = '19000101' THEN 0 ELSE ec2.how_many_term END)
    FROM @empl_chgs ec2
    WHERE
        ec2.dept = depts.dept AND
        (ec2.date = '19000101' OR ec2.date <= dates.date)
    ) AS ActiveEmpls
FROM (
    SELECT DATEADD(DAY, dayCounter, @startDate) AS date
    FROM cteTally
    WHERE
        dayCounter BETWEEN 0 AND DATEDIFF(DAY, @startDate, @endDate)
) AS dates
CROSS JOIN (
    SELECT DISTINCT dept
    FROM @empl_chgs
) AS depts
LEFT OUTER JOIN @empl_chgs ec ON
    ec.dept = depts.dept AND
    ec.date = dates.date
ORDER BY
    depts.dept, dates.date
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37717418
Minor correction, changing one "<=" to "<":
[Won't even affect the results now, but it's technically more accurate, and in case you change the query later, it might be significant]

....

INSERT INTO @empl_chgs ( dept, date, base_active, how_many_hired, how_many_term )
SELECT
    dept AS dept,
    CASE WHEN action = 'Hire' THEN CASE WHEN dateHired < @startDate THEN '19000101' ELSE dateHired END    --**** changed "<=" to "<" ****
        ELSE dateTerm END AS date,    
    SUM(CASE WHEN dateHired <= @startDate THEN 1 ELSE 0 END) AS base_active,
    SUM(CASE WHEN action = 'Hire' AND dateHired BETWEEN @startDate AND @endDate THEN 1 ELSE 0 END) AS HowManyHired,
    SUM(CASE WHEN action = 'Term' AND dateTerm BETWEEN @startDate AND @endDate THEN 1 ELSE 0 END) AS HowManyTerm  
FROM @employee_status
CROSS JOIN (
    SELECT 'Hire' AS action UNION ALL SELECT 'Term'
) AS Actions
WHERE
    dateHired <= @endDate AND
    (action = 'Hire' OR (action = 'Term' AND dateTerm IS NOT NULL))
GROUP BY
    dept,
    CASE WHEN action = 'Hire' THEN CASE WHEN dateHired < @startDate THEN '19000101' ELSE dateHired END
        ELSE dateTerm END
ORDER BY
    dept, date

...
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question