question

thenewguy avatar image
thenewguy asked

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
countdistinct
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
ThomasRushton avatar image
ThomasRushton answered
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.
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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 !
0 Likes 0 ·
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
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.