Sql server 2005 Query 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)

Sql server 2005 Query Help.

Author

Topic

DaveC11
Starting Member

43 Posts

Posted - 2008-06-04 : 04:23:51

Hi,

I'm having a problem with the below query. I'm trying to show the total amount of placements(sales) group by each month. I also want to show how much of the placements(sales) have been invoiced again groped by each month. The problem I'm having is some placements(sales) have more two invoices linked to them so the placementfee for that placement is being doubled.

How do I write the below code so that it only sums placementfee based on a distinct placementid?

is it possible to but something like this in the where clause -

where p.placementid = distinct/unique

Thanks in advance

Dave,


select 
DATENAME(MONTH, p.createdon) AS theMonth,
sum(p.placementfee) as 'Amount Boarded', 
count(distinct p.placementid) 'Number of Deals'
,SUM(i.netsum) as 'Amount Invoiced',
SUM(p.placementfee)- SUM(i.netsum)as 'Waiting to invoice'

FROM placements p
left outer join placementinvoices pp on pp.placementid = p.placementid 
left join invoices i on i.invoiceid = pp.invoiceid

WHERE	p.createdon >= '20080101'
	AND p.createdon < '20150101'

GROUP BY DATENAME(MONTH, p.createdon),
	 DATEPART(MONTH, p.createdon)

ORDER BY DATEPART(MONTH, p.createdon)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-04 : 04:50:52

May be this:-

select 
DATENAME(MONTH, createdon) AS theMonth,
sum(CASE WHEN RowNo=1 THEN placementfee ELSE 0 END) as 'Amount Boarded', 
count(distinct placementid) 'Number of Deals'
,SUM(netsum) as 'Amount Invoiced',
SUM(CASE WHEN RowNo=1 THEN placementfee ELSE 0 END)- SUM(netsum)as 'Waiting to invoice'
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY p.placementid ORDER BY i.invoiceid) AS RowNo,*
 FROM placements p
left outer join placementinvoices pp on pp.placementid = p.placementid 
left join invoices i on i.invoiceid = pp.invoiceid

WHERE	p.createdon >= '20080101'
	AND p.createdon < '20150101')tmp

GROUP BY DATENAME(MONTH, tmp.createdon),
	 DATEPART(MONTH, tmp.createdon)

ORDER BY DATEPART(MONTH, tmp.createdon)

DaveC11
Starting Member

43 Posts

Posted - 2008-06-04 : 05:09:51

Thanks for the reply but I'm not sure how your code works and I get the below error message when I run it

The column 'PlacementID' was specified multiple times for 'tmp'.

Thanks
Dave,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-04 : 05:12:52

quote:
Originally posted by DaveC11

Thanks for the reply but I'm not sure how your code works and I get the below error message when I run it

The column 'PlacementID' was specified multiple times for 'tmp'.

Thanks
Dave,

thats because you've same column present in more than 1 table. replace * with columns you require and try


select 
DATENAME(MONTH, createdon) AS theMonth,
sum(CASE WHEN RowNo=1 THEN placementfee ELSE 0 END) as 'Amount Boarded', 
count(distinct placementid) 'Number of Deals'
,SUM(netsum) as 'Amount Invoiced',
SUM(CASE WHEN RowNo=1 THEN placementfee ELSE 0 END)- SUM(netsum)as 'Waiting to invoice'
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY p.placementid ORDER BY i.invoiceid) AS RowNo,p.createdon,p.placementfee,p.placementid,i.netsum
 FROM placements p
left outer join placementinvoices pp on pp.placementid = p.placementid 
left join invoices i on i.invoiceid = pp.invoiceid

WHERE	p.createdon >= '20080101'
	AND p.createdon < '20150101')tmp

GROUP BY DATENAME(MONTH, tmp.createdon),
	 DATEPART(MONTH, tmp.createdon)

ORDER BY DATEPART(MONTH, tmp.createdon)

DaveC11
Starting Member

43 Posts

Posted - 2008-06-04 : 05:17:14

Thanks alot that hads worked,

Dave,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources