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.
Author |
Topic |
s_meb
Starting Member
3 Posts |
Posted - 2013-11-05 : 12:01:40
|
Hi All,SQL 2012Was hoping that someone could show me what I am missing. I'm wanting to get the average number of days between orders in my orders tbl - so I've done a search and found the following sql coded that I have modified for my db tbl's and columns. But when I try and parse it - I get 'Incorrect syntax new the keyword Group' - what am I missing.SELECT custId, AVG(invDate - priorDate)FROM(SELECT custId,invDate,LAG(invDate) OVER (PARTITION BY custId ORDER BY invDate)as priorDate FROM orders)Group BY custIdmax |
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2013-11-05 : 12:13:08
|
Try naming (assign alias) to subselect. Something like:SELECT custId, AVG(invDate - priorDate)FROM(SELECT custId,invDate,LAG(invDate) OVER (PARTITION BY custId ORDER BY invDate)as priorDate FROM orders) as aGroup BY custId |
|
|
s_meb
Starting Member
3 Posts |
Posted - 2013-11-05 : 12:19:58
|
Thanks bitsmed - that was it. Unfortunately I ran into this:Operand data type datetime is invalid for avg operator.max |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-05 : 12:45:44
|
[code]SELECT custId, AVG(DATEDIFF(dd,priorDate,invDate)*1.0)FROM(SELECT custId,invDate,LAG(invDate) OVER (PARTITION BY custId ORDER BY invDate)as priorDateFROM orders) as aGroup BY custId[/code]the part in blue is required only if you want decimal part in result------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
s_meb
Starting Member
3 Posts |
Posted - 2013-11-05 : 15:55:43
|
Thanks visakh16 - worked perfectly . . . . max |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-06 : 03:14:08
|
you're welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|