Solved

T-SQL: flattening rows with counts

Posted on 2012-03-12
1
360 Views
Last Modified: 2012-03-13
Techies--

A simplified version of the data I have looks like this:

Store      DateHired      DateTerminated      
10      20090701
10      20110120                     20110817
10      20010601                     20120120
20      20030110                     20110817
20      20110802                     20110817
20              20110817      
20      20110817
20      20110817


What I want back looks like this:

Date            Store      HireCount      TerminatedCount
20010601                      10      1            NULL
20030110                      20      1            NULL
20090701                      10      1            NULL
20110120                      10      1            NULL
20110802                      20      1            NULL
20110817                      10                  1
20110817                      20      3            2
20110817                      20                  2


How do achieve this?  Please advise.
0
Comment
Question by:ditallop
1 Comment
 
LVL 15

Accepted Solution

by:
tim_cs earned 500 total points
ID: 37711241
Something like this would work.  

DECLARE @Table TABLE(store INT,datehired VARCHAR(10), DateTerminated VARCHAR(10))


INSERT INTO @Table 
        (store, datehired, DateTerminated)
VALUES
        (10,'20090701',NULL)
		,(20,'20110120','20110817')
		,(20,'20110817',NULL)
		,(20,'20110817',NULL)


;WITH CTE AS (
SELECT
	Store
	,DateHired
	,1 Hired
FROM
	@Table
UNION ALL
SELECT
	Store
	,DateTerminated
	,0
FROM
	@Table
WHERE
	DateTerminated IS NOT NULL)

SELECT 
	c.DateHired
	,c.Store	
	,SUM(CASE WHEN Hired = 1 THEN 1 ELSE 0 END) HireCount
	,SUM(CASE WHEN Hired = 0 THEN 1 ELSE 0 END) TerminatedCount
FROM 
	CTE c
GROUP BY
	c.Store
	,c.DateHired

Open in new window

0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

808 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