Solved

t-sql: Keeping track of cumulative counts

Posted on 2012-03-13
6
442 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:ditallop
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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:ditallop
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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 69

Accepted Solution

by:
Scott Pletcher earned 500 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 69

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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Suggested Solutions

Title # Comments Views Activity
How can I get the entire database script? 7 34
shrink table after huge delete 2 34
SQL Server Shrink hurting performance? 4 49
point in time restore in SQL server 26 54
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

739 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