August 13, 2008 at 3:57 am
Is it possible to insert a summary into the following sql statment? I'd ideally like to sum all the sales and group them by the month they were boarded.
select convert(varchar(11),createdon, 106),
username + ' ' + surname as 'Consultant',DATENAME(MONTH, invoiceissueddate) AS theMonth,
fileas as 'Applicant',
(CASE WHEN RowNo=1 and placementtypeid not in('6','5','19','26','28')THEN placementfee ELSE 0 END )+ (CASE WHEN RowNo=1 THEN isnull(feereduction,0) ELSE 0 END) as 'Placement Fee' ,
(CASE WHEN RowNo=1 and placementtypeid in('23','17') THEN placementfee ELSE 0 END)+ (CASE WHEN RowNo=1 and placementtypeid in ('24','27','18') THEN isnull(feereduction,0) ELSE 0 END) as 'Drop Outs',
convert(varchar(11),invoiceissueddate, 106)'Invoice Issued Date'
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY p.placementid ORDER BY p.placementid) AS RowNo,p.createdon,p.placementfee,p.placementid, p.placementtypeid, ps.feereduction, o.fileas, u.username, u.surname, i.netsum,i.invoiceissueddate, p.startdate
FROM placements p
left join PlacementSectorDefinedColumns ps on ps.placementid = p.placementid
join placementconsultants pc on pc.placementid = p.placementid
join users u on u.userid = pc.userid
join objects o on o.objectid = p.applicantid
left outer join placementinvoices pp on pp.placementid = p.placementid
left join invoices i on i.invoiceid = pp.invoiceid
WHERE p.createdon >= '20080601'
AND p.createdon < '20090701'
AND p.createdon < '20150101' and p.placementtypeid not in ('6','26','28')) tmp
order BY tmp.createdon
Thanks in advance
Dave
August 13, 2008 at 7:50 am
You can create a new query and use this as a sub-query within. You can GROUP BY this new query on 'TheMonth' and incluse a SUM on the 'Sales' column.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply