Ms Access Countif equivalent in SQL Server

Question

Post reply

Ms Access Countif equivalent in SQL Server

Briceston

SSCrazy

Points: 2899
More actions
March 14, 2016 at 7:34 am

#399856

Hi,
I'm trying to migrate report data from Ms. Access to sql server. How would I go about employing the equivalent Access query below in sql server?
Sample data:
Reg Area Visitdate RegistrationCD
Hamb 2016-02-01 00:00:00.000 9ZL143
Access query:

SELECT [CLID Visits Step 1].REGISTER_AREA, [CLID Visits Step 1].SUMMARY_CATGRY, Count(IIf([visit_date] Between #2/1/2016# And #2/29/2016#,[registration_cd])) AS [Feb'16], Count(IIf([visit_date] Between #1/1/2016# And #1/31/2016#,[registration_cd])) AS [Jan'16], Count(IIf([visit_date] Between #12/1/2015# And #12/31/2015#,[registration_cd])) AS [Dec'15], Count(IIf([visit_date] Between #11/1/2015# And #11/30/2015#,[registration_cd])) AS [Nov'15], Count(IIf([visit_date] Between #10/1/2015# And #10/31/2015#,[registration_cd])) AS [Oct'15], Count(IIf([visit_date] Between #9/1/2015# And #9/30/2015#,[registration_cd])) AS [Sep'15], Count(IIf([visit_date] Between #8/1/2015# And #8/31/2015#,[registration_cd])) AS Aug15, Count(IIf([visit_date] Between #7/1/2015# And #7/31/2015#,[registration_cd])) AS Jul15, Count(IIf([visit_date] Between #6/1/2015# And #6/30/2015#,[registration_cd])) AS June15,

Viewing 6 posts - 1 through 5 (of 5 total)

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

ZZartin SSC-Dedicated Points: 30920 More actions · Answer 1

Without being super familiar with Access you could probably just change the

Count(IIf([visit_date] Between #2/1/2016# And #2/29/2016#,[registration_cd]))

To SUM(CASE WHEN visit_date >= '2/1/2016' AND visit_date <= '2/29/2016' THEN 1 ELSE 0 END)

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

I actually like to keep the COUNT. It will eliminate NULL values and the intention is clear.

Count(CASE WHEN [visit_date] Between '20160201' And '20160229' THEN [registration_cd] END) AS [Feb'16],

Count(CASE WHEN [visit_date] >= '20160101' And [visit_date] < '20160201' THEN [registration_cd] END) AS [Jan'16]

I Included 2 options. The first one takes in consideration the date portion only. If it's a datetime column and has a value in time in the last day of the range, it will be skipped.

The second option is safer for any date data type.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Briceston SSCrazy Points: 2899 More actions · Answer 3

Thanks for your insight fellas. The below example got me the result I was looking for.

Much appreciated!

Count(CASE WHEN visit_date Between '2/1/2016' AND '2/29/2016' THEN 1 ELSE 0 END)

djj SSCoach Points: 18831 More actions · Answer 4

Briceston (3/15/2016)
Thanks for your insight fellas. The below example got me the result I was looking for.
Much appreciated!
Count(CASE WHEN visit_date Between '2/1/2016' AND '2/29/2016' THEN 1 ELSE 0 END)

Remember if visit_date = '2/29/2016 10:00' then the above will not count it.

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 5

Briceston (3/15/2016)
Thanks for your insight fellas. The below example got me the result I was looking for.
Much appreciated!
Count(CASE WHEN visit_date Between '2/1/2016' AND '2/29/2016' THEN 1 ELSE 0 END)

Unless I'm misunderstanding your requirement, I don't think that does what you want. COUNT just counts the rows that have a non-null value for the expression being counted.

In this case, the expression is always non-NULL (because of the ELSE; Luis' examples left that out so rows not meeting the criteria would return NULL for the expression and not get counted), so you'll just get a count of every row in the result set. The above CASE would do what you're expecting if you switched from COUNT to SUM for the aggregate.

Cheers!