| Author |
 Topic  |
|
|
DP978
Constraint Violating Yak Guru
USA
269 Posts |
 Posted - 12/11/2012 : 00:55:22
|
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
1687 Posts |
Posted - 12/11/2012 : 01:04:22
|
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 |
 |
|
|
DP978
Constraint Violating Yak Guru
USA
269 Posts |
Posted - 12/11/2012 : 07:55:54
|
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 |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1687 Posts |
Posted - 12/11/2012 : 08:12:26
|
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 |
|
|
|
DP978
Constraint Violating Yak Guru
USA
269 Posts |
Posted - 12/11/2012 : 08:33:24
|
Just a few things in the Where clause, but nothing that should be affecting the groupings.
Are my partitions correct?
|
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1687 Posts |
Posted - 12/11/2012 : 08:39:24
|
Can you post your complete query? Then we can easily find the problem....
--
Chandu |
|
|
|
DP978
Constraint Violating Yak Guru
USA
269 Posts |
Posted - 12/11/2012 : 09:01:17
|
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' |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 12/11/2012 : 12:25:49
|
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 |
|
|
|
DP978
Constraint Violating Yak Guru
USA
269 Posts |
Posted - 12/11/2012 : 14:12:22
|
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 |
|
|
| |
Topic |
|
|
|
Partition/Over by Help
