MS SQL Server-为变量分配日期 [英] Ms sql server - assigning a date to a variable

问题描述

Declare procursor CURSOR FOR 
SELECT CLAIMNO from RPT_CLAIM_MD_COMBO
DECLARE @myyear Integer
Declare @provar varchar(22)

open procursor

fetch next from procursor into @provar

WHILE(@@FETCH_STATUS = 0)
BEGIN
SET @myyear = (SELECT 
CASE
WHEN CONVERT(INTEGER,BTHDAT) = 0 THEN 0 
WHEN  datepart(DY,convert(date, BTHDAT)) > datepart(DY,'2015/07/01') THEN DATEDIFF(YEAR, convert(date, BTHDAT),'2015/07/01') - 1
ELSE DATEDIFF(YEAR,convert(date, BTHDAT),'2015/07/01') 
END
from RPT_CLAIM_MD_COMBO WHERE CLAIMNO = @provar)


SELECT  SERVICE_GROUP, SERVICE_CATEGORY,
CASE
WHEN @myyear - 2015 <= 0 AND @myyear - 2015 >= 5 THEN 'Early Child'
WHEN @myyear - 2015 <= 6 AND @myyear - 2015 >= 12 THEN 'Child'
WHEN @myyear - 2015 <= 13 AND @myyear - 2015 >= 17 THEN 'Adolescent'
WHEN @myyear - 2015 <= 18 AND @myyear - 2015 >= 21 THEN 'Transitional'
WHEN @myyear - 2015 <= 22 AND @myyear - 2015 >= 64 THEN 'Adult'
WHEN @myyear - 2015 >= 65 THEN 'Geriatric'
ELSE '0'  
END AS Age_desc,
SUM(CONVERT(MONEY, TOPAY)) AS PAID_AMT    
FROM RPT_CLAIM_MD_COMBO
WHERE SERVICE_FY = '2016' and DISTYP = 'P' AND CLAIMNO = @provar 
GROUP BY 
SERVICE_GROUP,SERVICE_CATEGORY,
CASE
WHEN @myyear - 2015 <= 0 AND @myyear - 2015 >= 5 THEN 'Early Child'
WHEN @myyear - 2015 <= 6 AND @myyear - 2015 >= 12 THEN 'Child'
WHEN @myyear - 2015 <= 13 AND @myyear - 2015 >= 17 THEN 'Adolescent'
WHEN @myyear - 2015 <= 18 AND @myyear - 2015 >= 21 THEN 'Transitional'
WHEN @myyear - 2015 <= 22 AND @myyear - 2015 >= 64 THEN 'Adult'
WHEN @myyear - 2015 >= 65 THEN 'Geriatric'
ELSE '0'  
END 

fetch next from procursor into @provar 

END

CLOSE procursor 

我要做什么得到一个从RPT_CLAIM_MD_COMBO表中获取用户。第一检查他们的年龄。如果用户的出生日期DY> 2015年7月1日的DY，则将其年龄减1，否则保持相同的年龄。

What I am trying to do is get all the users from RPT_CLAIM_MD_COMBO table. 1st check for their age. If the DY of birthdate of the user > DY of 2015/07/01 then reduce the his age by 1 else keep the same age.

一旦计算出年龄，我必须按年龄显示支出（即按年龄分组）。
我已经编写了太多代码，但遇到了这个错误
消息329，级别16，状态1，第21行每个GROUP BY表达式必须至少包含一个列引用

Once the age is calculated I have to display the expenditure according to age(i,e group by age). I've coded so much and I am getting this error Msg 329, Level 16, State 1, Line 21 Each GROUP BY expression must contain at least one column reference

推荐答案

您不能分配这样的变量。此外，重点是什么？您在哪里使用该变量？只需删除 set @MYYEAR = ，就可以了。如果您确实想使用该逻辑分配该变量，请在主 SELECT 语句之外进行操作。

You can't assign a variable like that. Besides, what's the point? Where are you using that variable at? Just remove the set @MYYEAR = and you should be fine. If you really want to assign that variable with that logic, do it outside of your main SELECT statement.

declare @MYYEAR int
set @MYYEAR = 
(SELECT  
    CASE
        WHEN CONVERT(INTEGER,BTHDAT) = 0 THEN 0
        WHEN  datepart(DY,convert(date, BTHDAT)) > datepart(DY,'2015/07/01') THEN DATEDIFF(YEAR, convert(date, BTHDAT),'2015/07/01') - 1
        ELSE DATEDIFF(YEAR,convert(date, BTHDAT),'2015/07/01')  
    END
FROM SomeTable)

SELECT  
    SERVICE_GROUP, 
    SERVICE_CATEGORY,
    @MYYEAR
FROM
    SomeOtherTable

这篇关于MS SQL Server-为变量分配日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！