March 14, 2016 at 7:34 am
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,
March 14, 2016 at 7:45 am
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)
March 14, 2016 at 7:50 am
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.
March 15, 2016 at 7:29 am
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)
March 15, 2016 at 7:55 am
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.
March 15, 2016 at 9:29 am
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!
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy