count - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

count

Author

Topic

enniwesw
Starting Member

8 Posts

Posted - 2010-04-16 : 03:28:57

How would you query a table to give you a count of all invoices given that the val > 100 and display the results by year by month from data like...
invoice dated val
OP/I176750 22/07/2009 687.5
OP/I176750 23/07/2008 0
OP/C029223 24/07/2007 742
OP/I166716 25/07/2007 371
OP/C029228 26/07/2008 287
OP/C029205 27/07/2007 10906
OP/C029205 28/07/2010 2737
OP/C029324 29/07/2010 161
OP/C029215 30/07/2008 1400
OP/C029232 05/02/2007 3500
OP/C029212 01/02/2009 1576.4
OP/C029213 01/02/2009 1576.4
OP/C029214 01/02/2007 1576.4
OP/C029209 01/02/2007 3136
OP/C029223 04/02/2008 11872
OP/C029236 05/02/2006 320.6
OP/C029322 13/02/2007 3346
OP/C029227 04/02/2010 217
OP/C029203 01/02/2010 280
OP/C029227 04/02/2010 119
OP/C029227 04/02/2007 147
OP/C029203 01/02/2009 224
OP/C029198 01/02/2009 3255
OP/C029224 04/02/2009 581

Sachin.Nand

2937 Posts

Posted - 2010-04-16 : 03:31:26

quote:
display the results by year by month from data like...

Please post some sample output.

PBUH

enniwesw
Starting Member

8 Posts

Posted - 2010-04-16 : 03:43:12

i am looking for something like this....
Jan-09 Feb-09 Mar-09 Apr-09 May-09 Jun-09 Jul-09 Aug-09 Sep-09
10 3 4 11 6 8 9 34 23

am not necessarily concerned about seeing the invoices but rather how many were done monthly in a particular year...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-04-16 : 05:29:22

Refer this
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

enniwesw
Starting Member

8 Posts

Posted - 2010-04-20 : 10:16:07

I managed to resolve this one the clue is to use the date function, it goes like...

SELECT year([tbl1].dated),
CASE WHEN [tbl1].val <50000 THEN [tbl1].invoice ELSE NULL END AS May,
CASE WHEN [tbl1].val <50000 THEN [tbl1].invoice ELSE NULL END AS Jun,
CASE WHEN [tbl1].val <50000 THEN [tbl1].invoice ELSE NULL END AS Jul,
CASE WHEN [tbl1].val <50000 THEN [tbl1].invoice ELSE NULL END AS Aug,
CASE WHEN [tbl1].val <50000 THEN [tbl1].invoice ELSE NULL END AS Sep,
CASE WHEN [tbl1].val <50000 THEN [tbl1].invoice ELSE NULL END AS Oct,
CASE WHEN [tbl1].val <50000 THEN [tbl1].invoice ELSE NULL END AS Nov,
CASE WHEN [tbl1].val <50000 THEN [tbl1].invoice ELSE NULL END AS Dec,
CASE WHEN [tbl1].val <50000 THEN [tbl1].invoice ELSE NULL END AS Jan,
CASE WHEN [tbl1].val <50000 THEN [tbl1].invoice ELSE NULL END AS Feb,
CASE WHEN [tbl1].val <50000 THEN [tbl1].invoice ELSE NULL END AS Mar,
CASE WHEN [tbl1].val <50000 THEN [tbl1].invoice ELSE NULL END AS Apr
FROM tbl1
GROUP BY year([tbl1].dated);

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-20 : 10:19:00

quote:
Originally posted by enniwesw

I managed to resolve this one the clue is to use the date function, it goes like...

SELECT year([tbl1].dated),
CASE WHEN [tbl1].val <50000 THEN [tbl1].invoice ELSE NULL END AS May,
CASE WHEN [tbl1].val <50000 THEN [tbl1].invoice ELSE NULL END AS Jun,
CASE WHEN [tbl1].val <50000 THEN [tbl1].invoice ELSE NULL END AS Jul,
CASE WHEN [tbl1].val <50000 THEN [tbl1].invoice ELSE NULL END AS Aug,
CASE WHEN [tbl1].val <50000 THEN [tbl1].invoice ELSE NULL END AS Sep,
CASE WHEN [tbl1].val <50000 THEN [tbl1].invoice ELSE NULL END AS Oct,
CASE WHEN [tbl1].val <50000 THEN [tbl1].invoice ELSE NULL END AS Nov,
CASE WHEN [tbl1].val <50000 THEN [tbl1].invoice ELSE NULL END AS Dec,
CASE WHEN [tbl1].val <50000 THEN [tbl1].invoice ELSE NULL END AS Jan,
CASE WHEN [tbl1].val <50000 THEN [tbl1].invoice ELSE NULL END AS Feb,
CASE WHEN [tbl1].val <50000 THEN [tbl1].invoice ELSE NULL END AS Mar,
CASE WHEN [tbl1].val <50000 THEN [tbl1].invoice ELSE NULL END AS Apr
FROM tbl1
GROUP BY year([tbl1].dated);

dont you have to check for MONTH([tbl1].dated) as well?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

enniwesw
Starting Member

8 Posts

Posted - 2010-04-20 : 10:20:20

nop its .....

SELECT year([tbl1].dated),
CASE WHEN [tbl1].val <50000 and month([tbl1].dated) = 5 and month([tbl1].dated) = 5 THEN [tbl1].invoice ELSE NULL END AS May,
CASE WHEN [tbl1].val <50000 and month([tbl1].dated) = 6 THEN [tbl1].invoice ELSE NULL END AS Jun,
CASE WHEN [Jul].val <50000 and month([tbl1].dated) = 7 THEN [tbl1].invoice ELSE NULL END AS Jul,
CASE WHEN [Aug].val <50000 and month([tbl1].dated) = 8 THEN [tbl1].invoice ELSE NULL END AS Aug,
CASE WHEN [Sep].val <50000 and month([tbl1].dated) = 9 THEN [tbl1].invoice ELSE NULL END AS Sep,
CASE WHEN [Oct].val <50000 and month([tbl1].dated) = 10 THEN [tbl1].invoice ELSE NULL END AS Oct,
CASE WHEN [Nov].val <50000 and month([tbl1].dated) = 11 THEN [tbl1].invoice ELSE NULL END AS Nov,
CASE WHEN [Dec].val <50000 and month([tbl1].dated) = 12 THEN [tbl1].invoice ELSE NULL END AS Dec,
CASE WHEN [Jan].val <50000 and month([tbl1].dated) = 1 THEN [tbl1].invoice ELSE NULL END AS Jan,
CASE WHEN [Feb].val <50000 and month([tbl1].dated) = 2 THEN [tbl1].invoice ELSE NULL END AS Feb,
CASE WHEN [Mar].val <50000 and month([tbl1].dated) = 3 THEN [tbl1].invoice ELSE NULL END AS Mar,
CASE WHEN [Apr].val <50000 and month([tbl1].dated) = 4 THEN [tbl1].invoice ELSE NULL END AS Apr
FROM tbl1
GROUP BY year([tbl1].dated);

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-20 : 10:24:14

ok..now it makes sense

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources