x

count several thing

I am new to Sql, please help me

I have Table with underwriting requests, its about 100 columns, but to simplify:

Underwriting_Team(dubai or texas) | Pending_since (dd/mm/yyyy) | Prospect_or_not (0or 1)

I want to get a table that gives

Distinct_Team |num pending less than one week and not prospect for this team|pending more than one week and not prospect for this team|num of prospect for this team

more ▼

asked Apr 13, 2013 at 12:00 PM in Default

avatar image

thenewguy
10 1 1 3

https://files.secureserver.net/0szMMkdj3DbDd5

for excel sample tables, please help

Apr 13, 2013 at 12:09 PM thenewguy
(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Hi

The technique you're looking for is something like this:

 SELECT
     Underwriting_Team,
     SUM(CASE WHEN Prospect = 0 AND Pending_Since >= DATEADD(day, -7, getdate()) THEN 1 ELSE 0 END),
     SUM(CASE WHEN Prospect = 0 AND Pending_Sinze < DATEADD(day, -7, getdate()) THEN 1 ELSE 0 END),
     SUM(Prospect)
 FROM
     MyUnderwritingTable
 GROUP BY
     Underwriting_Team

What we're doing is taking each of your rules about whether something is or isn't to be counted, and wrapping them up in a way that evaluates to 1 or 0. These can then be summed to give you the appropriate counts.

more ▼

answered Apr 13, 2013 at 12:16 PM

avatar image

ThomasRushton ♦♦
41.8k 20 50 53

seems like you got what i need, i am a bit slow so i will test and get back here soon, but many thanks for the quick answer !

Apr 13, 2013 at 12:20 PM thenewguy

We are getting somewhere, I asked the question in SQL, but really what I am using is this:

Provider=Microsoft.ACE.OLEDB.12.0;

seems the when is not supported, any solution...

I have my data loaded into vb.net datagrid view is there is a workaround.

Many thanks

Apr 13, 2013 at 12:33 PM thenewguy
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x49
x22

asked: Apr 13, 2013 at 12:00 PM

Seen: 672 times

Last Updated: Apr 13, 2013 at 12:33 PM

Copyright 2017 Redgate Software. Privacy Policy