April 6, 2006 at 7:16 am
Hi
This is my first post here so please be gentle.
I have a group sp which gets my data like below
| Surname | DateSold | PlotPrice | Comp date | SalesPerson | Percentage | RefundedAmount | Site | SaleID |
|---|---|---|---|---|---|---|---|---|
| Smith | 06/04/2006 | £3,600.00 | 04/04/2006 | Cooper | 5 | £500.00 | Site1 | 945 |
| Smith | 06/04/2006 | £11,600.00 | 06/04/2006 | Cooper | 5 | £500.00 | Site2 | 945 |
The problem is i only want the RefundedAmount to be a value once the rest to be £0
How can I get around this?
Any help would be great
Cheers
JB
April 6, 2006 at 7:52 am
I don't quite understand what you want. Can you post the SP, some sample data and desired results?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 7, 2006 at 1:24 am
Hi
Sorry if i didnt make myself clear. below is the sp
ALTER PROCEDURE dbo.spCommissionsBydate
(@FromDate smalldatetime,
@ToDate smalldatetime)
AS SELECT dbo.tContacts.Surname, MIN(dbo.tSale.DateSold) AS DateSold, SUM(dbo.tSaleDetails.PlotPrice) AS PlotPrice,
dbo.tSaleDetails.CompletionDate AS [Comp date], dbo.tSalesPerson.SalesPerson, MIN(dbo.tCommissions.Percentage) AS Percentage,
MIN(dbo.tSale.RefundedAmount) AS RefundedAmount, dbo.tSite.Site, MIN(dbo.tSale.SaleID) AS Expr1
FROM dbo.tSaleDetails INNER JOIN
dbo.tSale ON dbo.tSaleDetails.SaleID = dbo.tSale.SaleID INNER JOIN
dbo.tCommissions INNER JOIN
dbo.tContacts ON dbo.tCommissions.ContactID = dbo.tContacts.ContactID INNER JOIN
dbo.tSalesPerson ON dbo.tCommissions.SalespersonID = dbo.tSalesPerson.SalesPersonID ON
dbo.tSale.SaleID = dbo.tCommissions.SaleID INNER JOIN
dbo.tSite ON dbo.tSaleDetails.SiteID = dbo.tSite.SiteID
WHERE (dbo.tSaleDetails.CompletionDate BETWEEN CONVERT(DATETIME, @FromDate, 102) AND CONVERT(DATETIME, @ToDate, 102)) AND
(dbo.tSaleDetails.CancelledDate IS NULL) AND (dbo.tSaleDetails.CancelledBY IS NULL)
GROUP BY dbo.tContacts.Surname, dbo.tSaleDetails.CompletionDate, dbo.tSalesPerson.SalesPerson, dbo.tSite.Site
ORDER BY dbo.tSalesPerson.SalesPerson
As you see on the table above it display the RefundedAmount twice for the same SaleID I only want the RefundedAmount Shown once for each SaleID
Hope that helps
Cheers
April 7, 2006 at 2:45 am
Easy enough, move the completion date out of the group by. You'll have to then put a max or min on it in the select clause. Do you want the first completion date, the latest, or something else?
SELECT dbo.tContacts.Surname, MIN(dbo.tSale.DateSold) AS DateSold, SUM(dbo.tSaleDetails.PlotPrice) AS PlotPrice, MAX(dbo.tSaleDetails.CompletionDate) AS [Last Comp date], dbo.tSalesPerson.SalesPerson, MIN dbo.tCommissions.Percentage) AS Percentage, MIN(dbo.tSale.RefundedAmount) AS RefundedAmount, dbo.tSite.Site, MIN(dbo.tSale.SaleID) AS Expr1
FROM ... <same as your query>
GROUP BY dbo.tContacts.Surname, dbo.tSalesPerson.SalesPerson, dbo.tSite.Site
ORDER BY dbo.tSalesPerson.SalesPerson
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 7, 2006 at 3:12 am
Hi
Thank you for your post but i still get the same problem. two RefundedAmounts this is because they are the same SaleID (I think).
After I have done my Select could I then loop through it and remove the RefundedAmount If the SaleID is the same in the previous Row?
Cheers
April 7, 2006 at 3:23 am
Sorry, I missed the field 'site'. Also must come out of the group by and be agregated (or removed) in the select.
No, it's not because they have the same saleID. the point of group by is to rollup stuff with identical values. You'l get multiple values beack from a group by if you have fields in the group by that differ in value.
I would never suggest that you loop through a recordset to remove stuff you don't want. Get the select right and there's no need for time-wasting stuff like that. Besides, what do you mean by 'previous row'? The only ordering you're doing is by the salesperson.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 7, 2006 at 3:35 am
HI
But I need the site showing and the completion date.
I am not to good with Group By as you can see.
Cheers
April 7, 2006 at 3:47 am
I'm going to need some sample data to help out with this, and an example of what you want.
By the looks of things, a saleID can have more than one site and more than one completion date. Is this correct?
If a sale has more 2 sites and 2 completion dates, do you want 1 record or 2?
If 1 record, which site and completion date must be shown?
The refunded amount isn't the problem, it's the number of records that's the problem.
I never said remove the site and completion date. I said take them out of the group by and either max, min or some other aggregation must be applied if you want them in the select. Otherwise you will get more than one record
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 7, 2006 at 3:54 am
Hi
Thankyou for your help so far.
I need to see both records so if a saleID has 2 sites and 2 completion dates then i need to see both sites.
So in a sale there could be 10 sites and 3 completion dates.
Like the table i posted on the first post But here is the problem I only want the RefundedAmount shown on one record. the refundedamount is stored it the SaleID table.
Cheers
April 7, 2006 at 4:13 am
To be quite honest, that's a presentation issue, not a data issue. It should be taken care of where you're presenting the data (excel, or reporting tool) not in the db query. What are you doing with this resultset?
There's nothing wrong with your group by. It's just that you're trying to show repeating and non-repeating data in one recordset. There are various complex hacks to put nulls in the 'repeated' rows, but that's merging data retrieval and data presentation more than is desirable
What I can suggest is, if possible, have one query that fetches the refund amount (and other stuff that shouldn't be duplicated from the sales table, and another to show you the completion dates and sites.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 7, 2006 at 4:17 am
Hi
Will have a play with two sp's and see what I can do not sure that it will work.
The results are being display in Crystal
Cheers for all you help
April 7, 2006 at 4:26 am
Not 2 sps, 2 queries in one sp.
CREATE PROCEDURE ...
AS
SELECT ... FROM Sales
SELECT ... FROM Sales JOIN SalesDetails ... GROUP BY ...
GO
It will work, crystal can handle 2 recordsets coming back from a query.
However Crystal is a powerful reporting tool and should be able to suppress duplicates on the report. I'm no crystal expert, but I worked with it a bit. With just some formatting and grouping on the report you should be able to get the results you want from the single query without needing to split into 2 queries.
Unfortunatly I can't help you with that. I do remember doing it, but I don't remember details. Was some years back.
Good luck.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 10, 2006 at 2:36 am
Hi
Thank you for your help and direction I sorted this out in Crystal Reports.
Cheers
April 10, 2006 at 7:19 am
My pleasure
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply