开发者

Change @prmMax from asp.net into SQL-server Query?

(Editor's note, I have attempted to translate and format more appropriately. See below for the original)

I want to make an SQL query to get those "ACTIVATED WITHIN 28 DAYS AFTER REGISTRATION" and those "ACTIVATED ABOVE 28 DAYS AFTER REGISTRATION". How can I fix this query?

Below this query is for GET THOSE "ACTIVATED WITHIN 28 DAYS AFTER REGISTRATION"

 select Month(reg_Date) as RegMonth, datediff(day, reg_date, reg_activationdate) as RegDiff,
 count(*) as RegCount from dailyregistration
 where datediff(day, reg_date, reg_activationdate) <= @prmMax
 group by Month(reg_Date), datediff(day, reg_date, reg_activationdate)
 order by Month(reg_Date), datediff(day, reg_date, reg_activationdate)

Unfortunately, I get the following error:

 Msg 137, Level 15, State 2, Line 3
 Must declare the scalar variable "@prmMax".

How can I fix it?

Here is an example row in the DB: (slightly unintelligible)

 #month##Register#        #activation elapsed time#                 #Activated##Unactivated##port out#
             #<0##0##1##2##3##4##5##6##7##14##21##28##>28    
 #March##1360#

Original Below


I HAD TO MAKE IT INTO SQL-SERVER QUERY F开发者_高级运维ORM TO GET THOSE "ACTIVATED WITHIN 28 DAYS AFTER REGISTRATION" AND ALSO "ACTIVATED ABOVE 28 DAYS AFTER REGISTRATION". HOW CAN I CHANGE IT INTO SQL-SERVER QUERY? BEFORE THIS I HAD TRY FOR IT IN SQL SERVER,

Below this query is for GET THOSE "ACTIVATED WITHIN 28 DAYS AFTER REGISTRATION"

select Month(reg_Date) as RegMonth, datediff(day, reg_date, reg_activationdate) as RegDiff, count(*) as RegCount from dailyregistration where datediff(day, reg_date, reg_activationdate) <= @prmMax group by Month(reg_Date), datediff(day, reg_date, reg_activationdate) order by Month(reg_Date), datediff(day, reg_date, reg_activationdate)

BUT IT PROMPT OUT WITH THE ERROR Msg 137, Level 15, State 2, Line 3 Must declare the scalar variable "@prmMax".

HOW CAN I CHANGE IT INTO CORRECT ? SInce i had to make it as tabel Can you please copy the below data into NOTEPAD and then you will see what i need, sorry that i can't attact any picture in.

  Total            Activation Elapsed Time (in Days)                                                              Total           Total     Total

Month Registered < 0 0 1 2 3 4 5 6 7 14 21 28 > 28 Activated Unactivated Port Out March 1360 0 571 142 56 42 28 21 22 10 76 37 15 109 1129 231 0 April 13601 0 7577 1260 381 259 200 139 134 138 433 277 233 947 11978 1623 227 May 14216 0 6726 1397 479 333 212 187 150 180 565 332 254 1004 11819 2397 412 June 9738 0 5551 999 340 214 132 112 87 77 365 168 107 435 8587 1151 354 July 10527 0 6528 1225 342 190 130 88 94 61 293 139 86 373 9549 978 474 August 15214 1 10115 1353 384 255 184 136 97 108 397 224 143 298 13695 1519 369 September23724 0 13499 3671 1230 893 328 329 498 226 1969 112 64 27 22846 878 204 October 8383 0 5673 1692 323 77 30 20 8 8 14 0 0 0 7845 538 237


Very simple : your query is using a parameter @prmMax and you have obviously not declared that parameter - simply declare it and give it a value and all your problems are gone! I don't know if you're calling this inside a stored procedure (does the stored proc have a parameter called @prmMax?) or whatever - the variable just simply doesn't exist and that's your problem.

 SELECT 
    MONTH(reg_Date) as RegMonth, 
    DATEDIFF(day, reg_date, reg_activationdate) as RegDiff,
    COUNT(*) as RegCount 
 FROM 
    dbo.dailyregistration
 WHERE 
    DATEDIFF(day, reg_date, reg_activationdate) <= @prmMax
                                                  ^^^^^^^^^^
 GROUP BY
    MONTH(reg_Date), 
    DATEDIFF(day, reg_date, reg_activationdate)
 ORDER BY 
    MONTH(reg_Date), 
    DATEDIFF(day, reg_date, reg_activationdate)


I'll answer, but only because of the OP's comment that they still can't get this to work, even after the correct answer from marc_s, where the need to declare @prmMax and initialize @prmMax is pointed out. To make the query run add the DECLARE and SET before your query, like here:

DECLARE @prmMax int
SET @prmMax=28

--your query here--
select Month(reg_Date) as RegMonth, datediff(day, reg_date, reg_activationdate) as RegDiff,
 count(*) as RegCount from dailyregistration
 where datediff(day, reg_date, reg_activationdate) <= @prmMax
 group by Month(reg_Date), datediff(day, reg_date, reg_activationdate)
 order by Month(reg_Date), datediff(day, reg_date, reg_activationdate)
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜