IIf statement help - 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

General SQL Server Forums

New to SQL Server Programming

IIf statement help

Author

Topic

Briceston
Yak Posting Veteran

54 Posts

Posted - 2013-08-06 : 16:54:39

How would I write the below Ms.Access code into SQL? I am mainly having an issue with the IIf portion.

Thanks.

SELECT dbo_factDrugClaims.IncurredCCYYMM, IIf(([dbo_factDrugClaims]![TierInd]="1") Or ([dbo_factDrugClaims]![TierInd]="2") Or ([dbo_factDrugClaims]![TierInd]="3") Or ([dbo_factDrugClaims]![TierInd]="4") Or ([dbo_factDrugClaims]![TierInd]="A") Or ([dbo_factDrugClaims]![TierInd]="Y") Or ([dbo_factDrugClaims]![TierInd]="Z") Or IsNull([dbo_factDrugClaims]![TierInd]),"D",[dbo_factDrugClaims]![TierInd]) AS [Drug Category], dbo_factDrugClaims.MemberId INTO [t_Envision Drug Claims - members]
FROM dbo_factDrugClaims
WHERE (((dbo_factDrugClaims.BusinessUnitCode)="EP"))
GROUP BY dbo_factDrugClaims.IncurredCCYYMM, IIf(([dbo_factDrugClaims]![TierInd]="1") Or ([dbo_factDrugClaims]![TierInd]="2") Or ([dbo_factDrugClaims]![TierInd]="3") Or ([dbo_factDrugClaims]![TierInd]="4") Or ([dbo_factDrugClaims]![TierInd]="A") Or ([dbo_factDrugClaims]![TierInd]="Y") Or ([dbo_factDrugClaims]![TierInd]="Z") Or IsNull([dbo_factDrugClaims]![TierInd]),"D",[dbo_factDrugClaims]![TierInd]), dbo_factDrugClaims.MemberId
HAVING (((dbo_factDrugClaims.IncurredCCYYMM) Between DFirst("StartMonth","[t_Dates]") And DFirst("[EndMonth]","[t_dates]")));

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-08-06 : 17:29:40

What version of SQL Server are you using?

Briceston
Yak Posting Veteran

54 Posts

Posted - 2013-08-06 : 17:35:48

Sql server 2008

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-08-06 : 17:47:14

I don't know the "DFirst" function, so cannot translate the "having" section. But the rest could be translated to something like:


select IncurredCCYYMM
      ,case when TierInd in ('1','2','3','4','A','Y','Z') then 'D' else isnull(TierInd,'D') end as DrugCategory
      ,MemberId
  into [t_Envision Drug Claims - members]
  from dbo_factDrugClaims
 where BusinessUnitCode='EP'
 group by IncurredCCYYMM
         ,case when TierInd in ('1','2','3','4','A','Y','Z') then 'D' else isnull(TierInd,'D') end as DrugCategory
         ,MemberId

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-06 : 17:47:54

[code]DECLARE @FromDate DATE,
@ToDate DATE;

SELECT @FromDate = MIN(StartMonth),
@ToDate = MAX(EndMonth)
FROM dbo.[t_dates];

INSERT dbo.[t_Envision Drug Claims - members]
(
IncurredCCYYMM,
[Drug Category],
MemberId
)
SELECT DISTINCT IncurredCCYYMM,
CASE
WHEN TierInd IN ('1', '2', '3', '4', 'A', 'Y', 'Z' THEN 'D'
WHEN TierInd IS NULL THEN 'D'
ELSE TierInd
END AS [Drug Category],
MemberId
FROM dbo.factDrugClaims
WHERE BusinessUnitCode = 'EP'
AND IncurredCCYYMM BETWEEN @FromDate AND @ToDate;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Briceston
Yak Posting Veteran

54 Posts

Posted - 2013-08-07 : 09:04:19

Thank you both for chiming in on this, the insight and knowledge gained is greatly appreciated.

SwePeso, your query worked best for me, however, I would like to group by, but I get an error near As when grouping. How do I resolve this?

Group by
IncurredCCYYMM,
CASE
WHEN TierInd IN ('1', '2', '3', '4', 'A', 'Y', 'Z') THEN 'D'
WHEN TierInd IS NULL THEN 'D'
ELSE TierInd
END AS [Drug Category],
Member id

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-08-07 : 11:21:40

Remove 'AS [Drug Category]'

quote:
Originally posted by Briceston

Thank you both for chiming in on this, the insight and knowledge gained is greatly appreciated.

SwePeso, your query worked best for me, however, I would like to group by, but I get an error near As when grouping. How do I resolve this?

Group by
IncurredCCYYMM,
CASE
WHEN TierInd IN ('1', '2', '3', '4', 'A', 'Y', 'Z') THEN 'D'
WHEN TierInd IS NULL THEN 'D'
ELSE TierInd
END ~~AS [Drug Category]~~,
Member id

Briceston
Yak Posting Veteran

54 Posts

Posted - 2013-08-07 : 11:58:36

Thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-07 : 12:07:21

There is NO need to group by if you don't have an aggregate.
DISTINCT is the same thing as GROUP BY in that case.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources