June 30, 2015 at 8:48 am
The script is failing at this point "DATEADD(mm, RowNum, salesdate) subscriptionrowdate" dont know exactly where i am going wrong.
This is my code
SELECT *, CAST(viasatsubscriptionid as char(8)) +'_'+LTRIM(STR(RowNum))subscriptionrowlog, DATEADD(mm, RowNum, salesdate) subscriptionrowdate
FROM (
SELECT viasatsubscriptionid, firstproductregistrationdate, salesdate, baseenddate,
ROW_NUMBER() over(Partition by viasatsubscriptionid order by salesdate)-1 RowNum
FROM stage_viasatsubscription
)a
June 30, 2015 at 9:01 am
Nothing in the code jumps out as being the cause of the problem. What is the actual data type of salesdate?
June 30, 2015 at 9:10 am
this causes an overflow.
So find out what the max ranking value is.
declare @date date = cast(getdate() as date)
select dateadd(mm,200000,@date) --OVERFLOW
SELECT max(rownum) --WHAT IS THIS VALUE?
FROM (
SELECT viasatsubscriptionid, firstproductregistrationdate, salesdate, baseenddate,
ROW_NUMBER() over(Partition by viasatsubscriptionid order by salesdate)-1 RowNum
FROM stage_viasatsubscription
)a
Added comments into code
June 30, 2015 at 11:32 am
May be the rownum is so high it goes beyond the date datatype of Salesdate. Is salesdate date / datetime?
Good Luck 🙂 .. Visit www.sqlsaga.com for more t-sql code snippets and BI related how to articles.
June 30, 2015 at 2:54 pm
I also think that rownum is high. No, salesdate is not devideby datetime. I should doing something like this
rownumber <= datediff (day,salesdate,baseenddate) but i dont know how to implement it.
June 30, 2015 at 4:10 pm
mandania (6/30/2015)
I also think that rownum is high. No, salesdate is not devideby datetime. I should doing something like thisrownumber <= datediff (day,salesdate,baseenddate) but i dont know how to implement it.
case when RowNum <= datediff(day, salesdate, baseenddate)
then DATEADD(mm, RowNum, salesdate)
else 0 end
Don Simpson
June 30, 2015 at 5:06 pm
Out of curiosity, what is stage_viasatsubscription? A base table or a view? Why would it have 95,0000+ rows for any singe viasatsubscriptionid?
July 2, 2015 at 8:03 am
stage_viasatsubscription is a base table.
July 2, 2015 at 8:26 am
This is what i am looking for, to come out of the loop once the datediff days are zero. RowNum is a counter that i am using to concatenate to my subscriptionid and adds a month to my subscriptionrowdate until the datediff is zero but at the moment it continuos to generate the rownum and causes it to overflow.
How can i incorporate this code :
case when RowNum <= datediff(day, salesdate, baseenddate)
then DATEADD(mm, RowNum, salesdate)
else 0 end
into this?
SELECT TOP 1000*
,CAST(viasatsubscriptionid as char(8)) +'_'+LTRIM(STR(RowNum))subscriptionrowlog
, DATEADD(mm, RowNum, salesdate) subscriptionrowdate
FROM (
SELECT viasatsubscriptionid
,firstproductregistrationdate
,salesdate
,baseenddate,
ROW_NUMBER() over(Partition by viasatsubscriptionid order by salesdate)-1 RowNum
FROM stage_viasatsubscription --where RowNum <=datediff(day,salesdate,baseenddate)
)viasatsub
July 2, 2015 at 8:50 am
mandania (7/2/2015)
This is what i am looking for, to come out of the loop once the datediff days are zero. RowNum is a counter that i am using to concatenate to my subscriptionid and adds a month to my subscriptionrowdate until the datediff is zero but at the moment it continuos to generate the rownum and causes it to overflow.How can i incorporate this code :
case when RowNum <= datediff(day, salesdate, baseenddate)
then DATEADD(mm, RowNum, salesdate)
else 0 end
into this?
SELECT TOP 1000*
,CAST(viasatsubscriptionid as char(8)) +'_'+LTRIM(STR(RowNum))subscriptionrowlog
, DATEADD(mm, RowNum, salesdate) subscriptionrowdate
FROM (
SELECT viasatsubscriptionid
,firstproductregistrationdate
,salesdate
,baseenddate,
ROW_NUMBER() over(Partition by viasatsubscriptionid order by salesdate)-1 RowNum
FROM stage_viasatsubscription --where RowNum <=datediff(day,salesdate,baseenddate)
)viasatsub
It would simply replace the DATEADD function you have in the SELECT, but...
You need to try running your subquery and finding out the maximum value of RowNum, and also, the values for salesdate. This should reveal where the grief is coming from.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 2, 2015 at 9:09 am
July 2, 2015 at 9:28 am
mandania (7/2/2015)
This is what i am looking for, to come out of the loop once the datediff days are zero. RowNum is a counter that i am using to concatenate to my subscriptionid and adds a month to my subscriptionrowdate until the datediff is zero but at the moment it continuos to generate the rownum and causes it to overflow.How can i incorporate this code :
case when RowNum <= datediff(day, salesdate, baseenddate)
then DATEADD(mm, RowNum, salesdate)
else 0 end
into this?
SELECT TOP 1000*
,CAST(viasatsubscriptionid as char(8)) +'_'+LTRIM(STR(RowNum))subscriptionrowlog
, DATEADD(mm, RowNum, salesdate) subscriptionrowdate
FROM (
SELECT viasatsubscriptionid
,firstproductregistrationdate
,salesdate
,baseenddate,
ROW_NUMBER() over(Partition by viasatsubscriptionid order by salesdate)-1 RowNum
FROM stage_viasatsubscription --where RowNum <=datediff(day,salesdate,baseenddate)
)viasatsub
SELECT TOP 1000 *,
CAST(viasatsubscriptionid as char(8)) + '_' + LTRIM(STR(RowNum)) as subscriptionrowlog,
case when RowNum <= datediff(day, salesdate, baseenddate)
then DATEADD(mm, RowNum, salesdate)
else 0 end as subscriptionrowdate
FROM ( ...
Don Simpson
July 3, 2015 at 1:43 am
I get this error.Operand type clash: int is incompatible with date.
My subscription table has around more that 20,000 subscriptionids. I have rownum so that i can concatenate this rownumber to my subscription id i.e 213123_1,213123_2 etc..etc.. and same with subscriptionrowdate i add a month for everysubscriptionid 2007-01-01, 2007-02-01,2007-03-01...2039-04-01 but this is not what i want. Currently my code is generating this date based on rownum which is wrong, i want to break when
datediff(day, salesdate, baseenddate) is reaches 0 and move on to next subscriptionid.
July 3, 2015 at 1:43 am
sorry my mistake, not looping. i have explained in a new post what i am trying to achieve.
July 3, 2015 at 11:17 am
mandania (7/3/2015)
I get this error.Operand type clash: int is incompatible with date.My subscription table has around more that 20,000 subscriptionids. I have rownum so that i can concatenate this rownumber to my subscription id i.e 213123_1,213123_2 etc..etc.. and same with subscriptionrowdate i add a month for everysubscriptionid 2007-01-01, 2007-02-01,2007-03-01...2039-04-01 but this is not what i want. Currently my code is generating this date based on rownum which is wrong, i want to break when
datediff(day, salesdate, baseenddate) is reaches 0 and move on to next subscriptionid.
My bad. I used some lazy programming expecting datetime instead of date. Change the "0" to '1900-01-01' or some representation on missing/invalid data.
else '1900-01-01' end as subscriptionrowdate
Don Simpson
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply