SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Incorrect Syntax ?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

s_meb
Starting Member

USA
3 Posts

Posted - 11/05/2013 :  12:01:40  Show Profile  Reply with Quote
Hi All,

SQL 2012

Was 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 custId

max

bitsmed
Constraint Violating Yak Guru

320 Posts

Posted - 11/05/2013 :  12:13:08  Show Profile  Reply with Quote
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 a
Group BY custId
Go to Top of Page

s_meb
Starting Member

USA
3 Posts

Posted - 11/05/2013 :  12:19:58  Show Profile  Reply with Quote
Thanks bitsmed - that was it.

Unfortunately I ran into this:
Operand data type datetime is invalid for avg operator.



max
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/05/2013 :  12:45:44  Show Profile  Reply with Quote

SELECT custId, AVG(DATEDIFF(dd,priorDate,invDate)*1.0)
FROM(SELECT custId,invDate,LAG(invDate) OVER (PARTITION BY custId ORDER BY invDate)as priorDate
FROM orders) as a
Group BY custId


the part in blue is required only if you want decimal part in result

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

s_meb
Starting Member

USA
3 Posts

Posted - 11/05/2013 :  15:55:43  Show Profile  Reply with Quote
Thanks visakh16 - worked perfectly . . . .

max
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/06/2013 :  03:14:08  Show Profile  Reply with Quote
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000