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
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Partition/Over by Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DP978
Constraint Violating Yak Guru

USA
269 Posts

Posted - 12/11/2012 :  00:55:22  Show Profile  Send DP978 an AOL message  Click to see DP978's MSN Messenger address  Reply with Quote
I have seen it before, but can not get my query to do so...

I have a query that is trying to do two things.

1. Count of Claims per day
2. Count the # of claims in a month
3. Count the Distinct number of days in a month that have claims
(So if 10/4 and 10/18 are the only days with claims in October, show 2)

All while showing the detail...

My attempts...

Select
memid
, startdate
, claimid
, Count(*) over (Partition by memid, startdate) as countbyday
, Count(*) over (Partition by memid, Month(startdate)) as countbymonth
, sum(Count( startdate)) over (Partition by memid, startdate) as DistinctCountofDaysInMonth
From plandata_rpt..claim c
Where 1 = 1
and c.startdate >= '1/1/2012'
Group by 
memid
, startdate
, claimid


Any ideas?


bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 12/11/2012 :  01:04:22  Show Profile  Reply with Quote
Select
memid
, startdate
, claimid
, Count(*) over (Partition by memid, startdate) as countbyday
, Count(*) over (Partition by memid, Month(startdate)) as countbymonth
, sum(Count( startdate)) over (Partition by memid, startdate) as DistinctCountofDaysInMonth
From plandata_rpt..claim c
Where 1 = 1
and c.startdate >= '1/1/2012'
Group by
memid
, startdate
, claimid



--
Chandu
Go to Top of Page

DP978
Constraint Violating Yak Guru

USA
269 Posts

Posted - 12/11/2012 :  07:55:54  Show Profile  Send DP978 an AOL message  Click to see DP978's MSN Messenger address  Reply with Quote
Thanks,

I tried that...but as soon as I remove the group by, it tells me I need to have them...

"Column 'claim.memid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause"

Is there anything else you see?

Thanks again,
-Derek
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 12/11/2012 :  08:12:26  Show Profile  Reply with Quote
Try with c.memid, c.startdate like that

I think this is not your complete query. Is there any other part which is affecting?

--
Chandu

Edited by - bandi on 12/11/2012 08:13:30
Go to Top of Page

DP978
Constraint Violating Yak Guru

USA
269 Posts

Posted - 12/11/2012 :  08:33:24  Show Profile  Send DP978 an AOL message  Click to see DP978's MSN Messenger address  Reply with Quote
Just a few things in the Where clause, but nothing that should be affecting the groupings.

Are my partitions correct?

Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 12/11/2012 :  08:39:24  Show Profile  Reply with Quote
Can you post your complete query? Then we can easily find the problem....

--
Chandu
Go to Top of Page

DP978
Constraint Violating Yak Guru

USA
269 Posts

Posted - 12/11/2012 :  09:01:17  Show Profile  Send DP978 an AOL message  Click to see DP978's MSN Messenger address  Reply with Quote
I changed it slightly since last time. But should still not pose a problem. Here is the code -


Select
memid
, ClaimID
, startdate
, c.totalamt
,(Year(startdate) * 100) + Month(Startdate) as YYYYMM
, sum(c.totalamt) over (Partition by Memid, startdate ) as DailySum
, Sum(c.totalamt) over (Partition by Memid, (Year(startdate) * 100) + Month(Startdate) ) as MonthlySum
, Sum(c.totalamt) over (Partition by Memid, Year(startdate)) as YearlySum
, c.totalamt/Sum(c.totalamt) over (Partition by Memid, (Year(startdate) * 100) + Month(Startdate) ) as PrctOfMonthly
, count(startdate) over (Partition by Memid, startdate ) as DailyCount
, Count(c.totalamt) over (Partition by Memid, (Year(startdate) * 100) + Month(Startdate) ) as MonthlyCount
, Count(c.totalamt) over (Partition by Memid, Year(startdate)) as YearlyCount
--, Want to add the distinct count of days with a claim in a month here
From plandata_rpt..claim c
Where 1 = 1
and memid in ('1','2')
and resubclaimid = ''
and c.status = 'Paid'
and c.startdate >= '1/1/2012'
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 12/11/2012 :  12:25:49  Show Profile  Reply with Quote
Does the startdate data also have a time portion to it? (i.e., values like 2012-12-11 12:21:46.663 rather than 2012-12-11 00:00:00.000)? The following assumes that there is no time portion.
...
   YearlyCount,
   COUNT(DISTINCT startdate) OVER() AS TotalDays,
   COUNT(DISTINCT startdate) OVER(PARTITION BY YEAR(startdate) * 100) + MONTH(Startdate)) AS TotalDaysPerMonth,
   COUNT(DISTINCT startdate) OVER(PARTITION BY Memid, YEAR(startdate) * 100) + MONTH(Startdate)) as PerhapsThis
FROM
....
BTW, I prefer to use CONVERT(VARCHAR(6),GETDATE(),112) to get the year and month portion of a date (because it is shorter, and it always gives you 2 digits for the month)

Edited by - sunitabeck on 12/11/2012 12:27:45
Go to Top of Page

DP978
Constraint Violating Yak Guru

USA
269 Posts

Posted - 12/11/2012 :  14:12:22  Show Profile  Send DP978 an AOL message  Click to see DP978's MSN Messenger address  Reply with Quote
Thank you for the response Sunita,

The query is coming back with the error "Incorrect Syntax Near the word 'Distinct'"

Thought?

Select
memid
, ClaimID
, startdate
, c.totalamt
, COUNT(Distinct startdate) OVER() AS TotalDays
, COUNT(Distinct startdate) OVER(PARTITION BY YEAR(startdate) * 100) + MONTH(Startdate)) AS TotalDaysPerMonth
, COUNT(Distinct startdate) OVER(PARTITION BY Memid, YEAR(startdate) * 100) + MONTH(Startdate)) as PerhapsThis

From plandata_rpt..claim c
Where 1 = 1
and resubclaimid = ''
and c.status = 'Paid'
and c.startdate >= '1/1/2012'

Edited by - DP978 on 12/11/2012 15:53:46
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.39 seconds. Powered By: Snitz Forums 2000