Partition/Over by Help - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Partition/Over by Help

Author

Topic

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2012-12-11 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-11 : 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

269 Posts

Posted - 2012-12-11 : 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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-11 : 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

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2012-12-11 : 08:33:24

Just a few things in the Where clause, but nothing that should be affecting the groupings.

Are my partitions correct?

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-11 : 08:39:24

Can you post your complete query? Then we can easily find the problem....

--
Chandu

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2012-12-11 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-12-11 : 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)

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2012-12-11 : 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'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources