![]() The reason I have used MAX in for 'day1' is to return the first date. WHERE a.recday NOT IN (b.recday, day2.recday) ,DATEDIFF(day,MAX(da圓.recday),MAX(day1.recday)) Da圓Diff ,DATEDIFF(day,MAX(day2.recday),MAX(day1.recday)) Day2Diff I'd heavily recommend researching further to see what equivalent functions you have available to you SELECT day1.udid If you did it this way you're going to end up with a massive statement but it will work. With the restrictions you've mentioned and my lack of Amazon specific knowledge I've done the first two values for you below. Really horrible query to get this working. Inserting some data EDIT: now using OP's supplied data VALUES How you replicate this in redshift is another issue, you could look at using UNPIVOT to get the top 7 results into the same row and then running your DATEDIFF equivalent functions on the fields themselves.ĮDIT: Ok, I've designed a really hacky way of getting this working Ĭreate temp table for testing purposes CREATE TABLE #vvdays (udid int, recday datetime) The DATEDIFF is then comparing the first 'recday' to the other lines and returning the number of days between these two dates. The LAG function is getting the second, third, fourth, fifth, sixth and seventh rows of data based upon the udid. Without seeing your data, I'm guessing that your table 'vvdays' contains the two fields 'udid' and 'recday'.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |