Grouping Active units by YYYYMM

Question

Post reply

Grouping Active units by YYYYMM

dclemens

Old Hand

Points: 304
More actions
January 23, 2013 at 9:13 am

#273078

Hey everyone! This is my first time posting here (please go easy), but I've been using the site for a while now as a reference.

I've been working on a query for a few days now and I just can't get it to give me the results I need so I was hoping to reach out for some help.

I need to get a historical count on active units grouped by by YYYYMM.

select trc_number,trc_retiredate,trc_startdate from tractorprofile

So any unit that has a trc_startdate <=2012-01 and trc_retiredate > 2012-01 would contribute to the Active Units column for that respective YYYYMM

Output should look like this.

YYYY-MM Active Units

2012-01 800

2012-02 820

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286562 More actions · Answer 1

dclemens (1/23/2013)

Hey everyone! This is my first time posting here (please go easy), but I've been using the site for a while now as a reference.

I've been working on a query for a few days now and I just can't get it to give me the results I need so I was hoping to reach out for some help.

I need to get a historical count on active units grouped by by YYYYMM.

select trc_number,trc_retiredate,trc_startdate from tractorprofile

So any unit that has a trc_startdate <=2012-01 and trc_retiredate > 2012-01 would contribute to the Active Units column for that respective YYYYMM

Output should look like this.

YYYY-MM Active Units

2012-01 800

2012-02 820

Hi and welcome to becoming an active member of the community. I would love to help but there aren't enough details here. Can you post ddl (create table scripts), sample data (insert statements) and desired output based on your sample data? Take a look at the first link in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

To assist those who can and want to help you ... please post table definition, some sample data.

To do so click on the first link in my signature block. The article referenced has sample T-SQL statements that will let you post the requested information quicky and easily.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

ScottPletcher SSC Guru Points: 100262 More actions · Answer 3

Don't know full details, but the usual outline for such queries is shown below.



SELECT

    DATEADD(MONTH, DATEDIFF(MONTH, 0, tp.trc_startdate), 0) AS start_month,

    SUM(tp.??) AS active_units

FROM dbo.tractorprofile tp

WHERE

    tp.trc_retiredate > DATEADD(MONTH, DATEDIFF(MONTH, 0, tp.trc_startdate), 0)

GROUP BY

    DATEADD(MONTH, DATEDIFF(MONTH, 0, tp.trc_startdate), 0)

ORDER BY

    DATEADD(MONTH, DATEDIFF(MONTH, 0, tp.trc_startdate), 0)

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

dclemens Old Hand Points: 304 More actions · Answer 4

ScottPletcher- Thanks for the reply, although the results are not what i'm looking for.

ScottPletcher (1/23/2013)

Don't know full details, but the usual outline for such queries is shown below.

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, tp.trc_startdate), 0) AS start_month, SUM(tp.??) AS active_units FROM dbo.tractorprofile tp WHERE tp.trc_retiredate > DATEADD(MONTH, DATEDIFF(MONTH, 0, tp.trc_startdate), 0) GROUP BY DATEADD(MONTH, DATEDIFF(MONTH, 0, tp.trc_startdate), 0) ORDER BY DATEADD(MONTH, DATEDIFF(MONTH, 0, tp.trc_startdate), 0)

Thanks for the replies! Here is the requested code.



CREATE TABLE [dbo].[tractorprofile_test]

  (

     [trc_number]     [VARCHAR](8) NOT NULL,

     [trc_startdate]  [DATETIME] NULL,

     [trc_retiredate] [DATETIME] NULL

  ); 

go 

INSERT INTO [dbo].[tractorprofile_test]

            ([trc_number],

             [trc_startdate],

             [trc_retiredate])

SELECT '509808',

       'Jan  1 1950 12:00AM',

       'Dec 10 2010  6:16PM'

UNION ALL

SELECT '509815',

       'Jan  1 1950 12:00AM',

       'Jul  1 2008 12:33PM'

UNION ALL

SELECT '509816',

       'Jun 21 2010  8:39AM',

       'Jul 12 2010  7:53PM'

UNION ALL

SELECT '510295',

       'Jan 13 2003 12:00AM',

       'May 24 2003 12:05PM'

UNION ALL

SELECT '515029',

       'Feb 10 2010  3:49PM',

       'Feb 25 2010  9:16AM'

UNION ALL

SELECT '516809',

       'Mar 30 2010  8:31AM',

       'Apr 29 2010  6:11AM'

UNION ALL

SELECT '519477',

       'Jan  1 1950 12:00AM',

       'Jun 21 2010  9:09AM'

UNION ALL

SELECT '524013',

       'Sep 12 2012  3:59PM',

       'Sep 19 2012 11:19AM'

UNION ALL

SELECT '525505',

       'Apr  2 2012  8:56AM',

       'Apr 27 2012  4:00PM'

UNION ALL

SELECT '525506',

       'Jan  1 1950 12:00AM',

       'Sep  8 2008 10:01AM'

UNION ALL

SELECT '527861',

       'Dec 31 2011 11:47AM',

       'Dec 31 2049 11:59PM'

UNION ALL

SELECT '528131',

       'Dec  1 2010  5:01PM',

       'Dec  1 2010  5:02PM'

UNION ALL

SELECT '528137',

       'Jan  1 1950 12:00AM',

       'Nov 19 2010  1:05PM'

UNION ALL

SELECT '531511',

       'Jan  1 1950 12:00AM',

       'Jan  5 2004  3:02AM'

UNION ALL

SELECT '53996',

       'Jan  1 1950 12:00AM',

       'Apr  8 2004 10:01AM'

UNION ALL

SELECT '542751',

       'Jan  1 1950 12:00AM',

       'Apr  5 2004  2:47PM'

UNION ALL

SELECT '543106',

       'Jan  1 1950 12:00AM',

       'Aug  7 2009  4:01PM'

UNION ALL

SELECT '548880',

       'Jan  1 1950 12:00AM',

       'Aug  4 2008  2:01PM'

UNION ALL

SELECT '5507725',

       'Sep 28 2001  4:55PM',

       'Oct  1 2001  5:42AM'

UNION ALL

SELECT '5507889',

       'Mar 19 2003  7:22AM',

       'May  6 2003  5:16PM'

UNION ALL

SELECT '5508500',

       'Mar 19 2003  7:21AM',

       'May  1 2003  5:01PM'

UNION ALL

SELECT '5510308',

       'Jan  7 2003 10:16AM',

       'Jan 17 2003  1:07PM'

UNION ALL

SELECT '551382',

       'Dec 31 2011 11:46AM',

       'Dec 31 2049 11:59PM'

UNION ALL

SELECT '5537818',

       'May 28 2002  3:43PM',

       'Sep  3 2002  6:01AM'

UNION ALL

SELECT '5542705',

       'Sep 28 2001 10:09AM',

       'Sep 28 2001 10:10AM'

UNION ALL

SELECT '5542790',

       'Mar  9 2004  4:24PM',

       'Jul 23 2004  5:01AM'

UNION ALL

SELECT '55555555',

       'Sep  5 2003  3:27PM',

       'Jan  1 1950 12:01AM'

UNION ALL

SELECT '556090',

       'May 23 2005 10:27AM',

       'Oct 10 2005  1:31PM'

UNION ALL

SELECT '556144',

       'May 16 2005 11:13AM',

       'Jun  1 2005  2:01PM'

UNION ALL

SELECT '557288',

       'Jan  1 1950 12:00AM',

       'Mar 31 2005 12:01PM'

UNION ALL

SELECT '561001',

       'Oct 18 2010 11:27AM',

       'Oct 20 2010  3:31PM'

UNION ALL

SELECT '561008',

       'Mar  9 2012 10:23AM',

       'Mar 22 2012  9:57AM'

UNION ALL

SELECT '562002',

       'Mar 17 2004  9:26AM',

       'Mar 17 2004  5:01AM'

UNION ALL

SELECT '562004',

       'Mar 17 2004  9:31AM',

       'Jan  1 1950 12:01AM'

UNION ALL

SELECT '562056',

       'Mar 17 2004  9:24AM',

       'Mar 17 2004  5:01AM'

UNION ALL

SELECT '562094',

       'Mar 17 2004  9:30AM',

       'Jan  1 1950 12:01AM'

UNION ALL

SELECT '562096',

       'Mar 17 2004  9:29AM',

       'Jan  1 1950 12:01AM'

UNION ALL

SELECT '564616',

       'Dec  3 2010  2:00PM',

       'Dec  9 2010  7:33AM'

UNION ALL

SELECT '565264',

       'Dec 31 2011 11:43AM',

       'Dec 31 2049 11:59PM'

UNION ALL

SELECT '565265',

       'Dec 31 2011 11:43AM',

       'Dec 31 2049 11:59PM'

UNION ALL

SELECT '565267',

       'Dec 31 2011 11:43AM',

       'Dec 31 2049 11:59PM'

UNION ALL

SELECT '565268',

       'Dec 31 2011 11:44AM',

       'Dec 31 2049 11:59PM'

UNION ALL

SELECT '565269',

       'Dec 31 2011 11:44AM',

       'Dec 31 2049 11:59PM'

UNION ALL

SELECT '565270',

       'Dec 31 2011 11:44AM',

       'Dec 31 2049 11:59PM'

UNION ALL

SELECT '569645',

       'Aug 16 2012 11:51AM',

       'Aug 20 2012  9:17AM'

UNION ALL

SELECT '569652',

       'Jan  1 1950 12:00AM',

       'Dec 20 2010  5:01PM'

UNION ALL

SELECT '569789',

       'Jun 15 2011  2:50PM',

       'Jul  1 2011  3:05PM'

UNION ALL

SELECT '569799',

       'Sep 12 2012  3:53PM',

       'Sep 27 2012 12:58PM'

UNION ALL

SELECT '570036',

       'Jan  1 1950 12:00AM',

       'Dec 21 2010  6:16AM'

UNION ALL

SELECT '570039',

       'Jan  1 1950 12:00AM',

       'Dec 22 2010  7:31AM'

UNION ALL

SELECT '570608',

       'Feb 17 2012  2:10PM',

       'Feb 17 2012  2:27PM'

UNION ALL

SELECT '57288',

       'Jan  1 1950 12:00AM',

       'Mar 27 2007 12:00PM'

UNION ALL

SELECT '572955',

       'Dec 31 2011 11:44AM',

       'Dec 31 2049 11:59PM'

UNION ALL

SELECT '572956',

       'Dec 31 2011 11:45AM',

       'Dec 31 2049 11:59PM'

UNION ALL

SELECT '572957',

       'Dec 31 2011 11:45AM',

       'Dec 31 2049 11:59PM'

UNION ALL

SELECT '572958',

       'Dec 31 2011 11:45AM',

       'Dec 31 2049 11:59PM'

UNION ALL

SELECT '572959',

       'Dec 31 2011 11:45AM',

       'Dec 31 2049 11:59PM'

UNION ALL

SELECT '572960',

       'Dec 31 2011 11:45AM',

       'Dec 31 2049 11:59PM'

UNION ALL

SELECT '572961',

       'Dec 31 2011 11:46AM',

       'Dec 31 2049 11:59PM'

UNION ALL

SELECT '572965',

       'Dec 31 2011 11:44AM',

       'Dec 31 2049 11:59PM'

UNION ALL

SELECT '572981',

       'Dec 31 2011 11:47AM',

       'Oct  8 2012  9:00AM'

UNION ALL

SELECT '572982',

       'Dec 31 2011 11:47AM',

       'Nov  9 2012  4:00PM'

UNION ALL

SELECT '575110',

       'Jan  1 1950 12:00AM',

       'Jan  7 2010 10:01PM'

UNION ALL

SELECT '575324',

       'Apr 25 2011  1:24PM',

       'Dec 31 2049 11:59PM'

UNION ALL

SELECT '575325',

       'Apr 25 2011  1:25PM',

       'Dec 31 2049 11:59PM'

UNION ALL

SELECT '575326',

       'Apr 25 2011  1:26PM',

       'Dec 31 2049 11:59PM'

UNION ALL

SELECT '575327',

       'Apr 25 2011  1:26PM',

       'Dec 31 2049 11:59PM'

UNION ALL

SELECT '575328',

       'Apr 25 2011  1:24PM',

       'Dec 31 2049 11:59PM'

UNION ALL

SELECT '575350',

       'Dec 31 2011 11:46AM',

       'Dec 31 2049 11:59PM'

UNION ALL

SELECT '575409',

       'Jan  1 1950 12:00AM',

       'Jul 24 2009  9:01PM'

UNION ALL

SELECT '575470',

       'Mar 29 2012 10:21AM',

       'Mar 29 2012 10:22AM'

UNION ALL

SELECT '575509',

       'Jan  1 1950 12:00AM',

       'Oct 16 2008  2:25PM'

UNION ALL

SELECT '575510',

       'Jan  1 1950 12:00AM',

       'Jun 26 2008 10:31PM'

UNION ALL

SELECT '575511',

       'Jan  1 1950 12:00AM',

       'Jul  1 2008 12:34PM'

UNION ALL

SELECT '575512',

       'Jan  1 1950 12:00AM',

       'Oct  1 2010 11:31AM'

UNION ALL

SELECT '575515',

       'Jan  1 1950 12:00AM',

       'Jul  1 2008 12:34PM'

UNION ALL

SELECT '575516',

       'Jan  1 1950 12:00AM',

       'Oct  7 2010  7:33PM'

UNION ALL

SELECT '575659',

       'Jan  1 1950 12:00AM',

       'Aug 19 2010  1:50PM'

UNION ALL

SELECT '576275',

       'Dec 31 2011 11:46AM',

       'Dec 31 2049 11:59PM'

UNION ALL

SELECT '577821',

       'Jan  1 1950 12:00AM',

       'Aug  1 2009  5:32PM'

UNION ALL

SELECT '577843',

       'Jan  1 1950 12:00AM',

       'Sep 24 2008 12:01PM'

UNION ALL

SELECT '577865',

       'Jan  1 1950 12:00AM',

       'Aug  7 2009  6:31PM'

UNION ALL

SELECT '577904',

       'Jan  1 1950 12:00AM',

       'Jan 11 2010  9:26AM'

UNION ALL

SELECT '577973',

       'May  3 2010  3:48PM',

       'May  6 2010  2:01PM'

UNION ALL

SELECT '578001',

       'Aug 10 2010  3:30PM',

       'Oct 21 2010  8:31PM'

UNION ALL

SELECT '578037',

       'Jan  1 1950 12:00AM',

       'Oct 17 2008  6:31AM'

UNION ALL

SELECT '582079',

       'Sep 30 2010 10:28AM',

       'Oct  8 2010 12:05PM'

UNION ALL

SELECT '582131',

       'Dec  1 2010  4:29PM',

       'Dec 15 2010  1:01PM'

UNION ALL

SELECT '592500',

       'Oct 22 2010  4:48PM',

       'Nov  2 2010  2:19PM'

UNION ALL

SELECT '593564',

       'May 25 2010 10:16AM',

       'Oct 18 2010 10:00PM'

UNION ALL

SELECT '593575',

       'Nov 18 2011  8:27AM',

       'Jan 19 2012  2:04PM'

UNION ALL

SELECT '593595',

       'Mar 29 2012 10:21AM',

       'Apr 27 2012  4:00PM'

UNION ALL

SELECT '593598',

       'Jan  1 1950 12:00AM',

       'Oct  8 2010  9:53AM'

UNION ALL

SELECT '593600',

       'Aug 15 2012  8:48AM',

       'Dec 31 2049 11:59PM'

UNION ALL

SELECT '595508',

       'Oct 22 2012 11:39AM',

       'Nov  2 2012  4:15PM'

UNION ALL

SELECT '595588',

       'Jul 16 2012  3:20PM',

       'Jul 25 2012  8:20AM'

UNION ALL

SELECT '595593',

       'Apr 29 2011  3:51PM',

       'May  6 2011  8:07AM'

UNION ALL

SELECT '595626',

       'Jun 11 2010  9:18AM',

       'Jul 28 2010  8:32PM'

UNION ALL

SELECT '595694',

       'Sep 10 2010 12:47PM',

       'Sep 24 2010  5:16PM'

UNION ALL

SELECT '595715',

       'Jan  1 1950 12:00AM',

       'Aug 24 2010  4:49PM'

UNION ALL

SELECT '595928',

       'Jan  1 1950 12:00AM',

       'Dec 31 2049 11:59PM'

UNION ALL

SELECT '595939',

       'Jan  1 1950 12:00AM',

       'Nov 18 2010  1:24PM'

UNION ALL

SELECT '595954',

       'Jun  9 2010  8:27AM',

       'Jun 11 2010  9:20AM'

UNION ALL

SELECT '596355',

       'Jan  1 1950 12:00AM',

       'Oct  7 2010  8:16PM'

UNION ALL

SELECT '596361',

       'Jan  1 1950 12:00AM',

       'Nov 12 2010  3:07PM'

UNION ALL

SELECT '596364',

       'Jul 26 2010  3:57PM',

       'Dec 15 2010  9:01PM'

UNION ALL

SELECT '596365',

       'Jan  1 1950 12:00AM',

       'Dec 20 2010 10:00AM'

UNION ALL

SELECT '596385',

       'Nov  9 2011  2:44PM',

       'Nov 28 2011  8:18AM'

UNION ALL

SELECT '596552',

       'Jan  1 1950 12:00AM',

       'Apr 17 2012  9:24AM'

UNION ALL

SELECT '596631',

       'Apr 10 2012  9:44AM',

       'Jun  4 2012  8:00AM'

UNION ALL

SELECT '596632',

       'Jan  1 1950 12:00AM',

       'Oct 18 2010 11:32AM'

UNION ALL

SELECT '596634',

       'Apr 10 2012  9:44AM',

       'Jun 21 2012 12:00PM'

UNION ALL

SELECT '596687',

       'Jan  1 1950 12:00AM',

       'Nov 15 2010  1:50PM'

UNION ALL

SELECT '597029',

       'Jul  6 2011  3:19PM',

       'Aug 21 2012  8:30AM'

UNION ALL

SELECT '597405',

       'Jan  1 1950 12:00AM',

       'Oct  6 2010 10:09AM'

UNION ALL

SELECT '597428',

       'Jan  1 1950 12:00AM',

       'Dec 20 2010  8:31AM'

UNION ALL

SELECT '597431',

       'Jan  1 1950 12:00AM',

       'Dec 21 2010  5:16PM'

UNION ALL

SELECT '597671',

       'Aug 13 2010  1:59PM',

       'Sep 21 2010  1:31PM'

UNION ALL

SELECT '597677',

       'Jun 14 2011  8:37AM',

       'Jun 20 2011  2:23PM'

UNION ALL

SELECT '597720',

       'Oct  9 2012 11:21AM',

       'Nov 12 2012  2:20PM'

UNION ALL

SELECT '60051',

       'Sep  7 2006  1:19PM',

       'Feb 26 2007 11:01AM'

UNION ALL

SELECT '6015',

       'Jan  1 1950 12:00AM',

       'Jan  1 2003 12:00AM'

UNION ALL

SELECT '602514',

       'Apr  9 2012  8:26AM',

       'Apr 16 2012  4:01PM'

UNION ALL

SELECT '602710',

       'Mar 29 2012 10:18AM',

       'Apr 27 2012  8:01PM'

UNION ALL

SELECT '603316',

       'Mar 12 2012  3:50PM',

       'Dec 31 2049 11:59PM'; 

go 

SELECT *

FROM   tractorprofile_test

The results should be similar to this, but on a month by month basis

DECLARE @startDate DATE = Getdate() - 30



DECLARE @enddate DATE = Getdate() 

SELECT Sum(CASE

             WHEN Cast(Floor(Cast(Isnull(t.trc_retiredate, '12/31/2049') AS

                                  FLOAT)) AS

                       DATETIME) >

                         @EndDate

                  AND Cast(Floor(Cast(t.trc_startdate AS FLOAT)) AS DATETIME) <=

                      @EndDate

           THEN 1

             ELSE 0

           END) AS [TrcCurrent]

FROM   tractorprofile_test AS t

       --LEFT JOIN manpowerprofile mpp

              --ON t.trc_number = mpp.mpp_tractornumber

WHERE  Cast(Floor(Cast(t.trc_startdate AS FLOAT)) AS DATETIME) IS NOT NULL

       AND Cast(Floor(Cast(Isnull(t.trc_retiredate, '12/31/2049') AS FLOAT)) AS

                DATETIME)

           >= @StartDate

       AND t.trc_number NOT IN ( '', 'Test', 'Barn', 'COWRAA', 

                                 'COWWIN', 'MCCCH01', 'UNKNOWN', 'REM', 'GRIT' )

Sean Lange SSC Guru Points: 286562 More actions · Answer 5

Your query doesn't produce any results because it has a table called manpowerprofile? If I comment out that table it returns a single value - 26. This is consistent with what I would expect. You have a sum but nothing to group by. Are you wanting to see this grouped by month? Do you want all months even if there is no data or only months where data is present?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

dclemens Old Hand Points: 304 More actions · Answer 6

Sean Lange (1/23/2013)

Your query doesn't produce any results because it has a table called manpowerprofile? If I comment out that table it returns a single value - 26. This is consistent with what I would expect. You have a sum but nothing to group by. Are you wanting to see this grouped by month? Do you want all months even if there is no data or only months where data is present?

Thanks Sean. I edit my previous post to comment out the IJ on manpowerprofile. I would like to see the data grouped by month, but I want to see all active units for each month.

For example

unit 527861 should contribute to the sum each month in year 2012 because it has a trc_startdate of12/31/2011 and a trc_retiredate of 12/31/2049

unit 525505 should contribute to the sum for April 2012 because it has a trc_startdate of 4/2/2012 and a trc_retiredate of 4/27/2012

unit 595508 should contribute to the sum for Oct and Nov 2012 because it has a trc_startdate of 10/22/2012 and a trc_retiredate of 11/2/2012

Sean Lange SSC Guru Points: 286562 More actions · Answer 7

dclemens (1/23/2013)

Sean Lange (1/23/2013)

Your query doesn't produce any results because it has a table called manpowerprofile? If I comment out that table it returns a single value - 26. This is consistent with what I would expect. You have a sum but nothing to group by. Are you wanting to see this grouped by month? Do you want all months even if there is no data or only months where data is present?

Thanks Sean. I edit my previous post to comment out the IJ on manpowerprofile. I would like to see the data grouped by month, but I want to see all active units for each month.

For example

unit 527861 should contribute to the sum each month in year 2012 because it has a trc_startdate of12/31/2011 and a trc_retiredate of 12/31/2049

unit 525505 should contribute to the sum for April 2012 because it has a trc_startdate of 4/2/2012 and a trc_retiredate of 4/27/2012

unit 595508 should contribute to the sum for Oct and Nov 2012 because it has a trc_startdate of 10/22/2012 and a trc_retiredate of 11/2/2012

So you want to see a row for every month? It would help greatly if you could show what you expect for output. Given the amount of test data maybe just for the 3 unit numbers above. Assuming those are the only rows in your table what should the output look like?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

dclemens Old Hand Points: 304 More actions · Answer 8

unit 527861 should contribute to the sum each month in year 2012 because it has a trc_startdate of12/31/2011 and a trc_retiredate of 12/31/2049

unit 525505 should contribute to the sum for April 2012 because it has a trc_startdate of 4/2/2012 and a trc_retiredate of 4/27/2012

unit 595508 should contribute to the sum for Oct and Nov 2012 because it has a trc_startdate of 10/22/2012 and a trc_retiredate of 11/2/2012

Using only the 3 units mentioned before, the output would look like

Month| Count

03-2012| 1 (527861)

04-2012| 2 (527861, 525505)

05-2012| 1

06-2012| 1

07-2012| 1

08-2012| 1

09-2012| 1

10-2012| 2 (527861, 595508 )

11-2012| 2 (527861, 595508 )

12-2012| 1

Sean Lange (1/23/2013)

dclemens (1/23/2013)

Sean Lange (1/23/2013)

Your query doesn't produce any results because it has a table called manpowerprofile? If I comment out that table it returns a single value - 26. This is consistent with what I would expect. You have a sum but nothing to group by. Are you wanting to see this grouped by month? Do you want all months even if there is no data or only months where data is present?

Thanks Sean. I edit my previous post to comment out the IJ on manpowerprofile. I would like to see the data grouped by month, but I want to see all active units for each month.

For example

unit 527861 should contribute to the sum each month in year 2012 because it has a trc_startdate of12/31/2011 and a trc_retiredate of 12/31/2049

unit 525505 should contribute to the sum for April 2012 because it has a trc_startdate of 4/2/2012 and a trc_retiredate of 4/27/2012

unit 595508 should contribute to the sum for Oct and Nov 2012 because it has a trc_startdate of 10/22/2012 and a trc_retiredate of 11/2/2012

So you want to see a row for every month? It would help greatly if you could show what you expect for output. Given the amount of test data maybe just for the 3 unit numbers above. Assuming those are the only rows in your table what should the output look like?