[T-SQL] system tables and views |
- system tables and views
- problem with union
- SQL Query Problem: How to aggregate discount percentages?
| Posted: 26 Mar 2013 11:43 PM PDT Does a database diagram exist of SQL Server's system tables? I am looking for documentation that will show me on which columns to join system tables so that I can develop my own investigative queries. I am familiar with many helpful posts that provide queries....however, invariably they require me to rely on someone else's experience to identify the keys on which to join tables and that that the joins are correct. SQL Server documentation of system tables, similar to http://msdn.microsoft.com/en-us/library/ms187997.aspx, but showing mapping between system tables, where primary keys are identified is what I'm looking for. |
| Posted: 26 Mar 2013 07:26 PM PDT Hi all, I have a problem with my "union" query, this a sample of my query. What I'd like to obtain is my 'effectif_enseignant' and my 'effectif_biatss' in two separated columns. Can somebody help me please (I don't know if I'm writting in the good part of this formum so please appologise if it's not the correct area)thank in advance Bastienselect COUNT (perso_id)as 'effectif_enseignant',REGION_NOM,ACAD_NOM,TYPO_NOM,cat_perso_nom,ETAB_RATT_NOM, ZONE_GEO_NOMfrom VIVIERwhere CATEGORIE_PERSONNEL.CAT_PERSO_CODE ='ENS'and FLAG_ACTIVITE=1union select COUNT (perso_id) as 'effectif_biatss',REGION_NOM,ACAD_NOM,TYPO_NOM,cat_perso_nom,ETAB_RATT_NOM, ZONE_GEO_NOMfrom VIVIERwhere CATEGORIE_PERSONNEL.CAT_PERSO_CODE ='BIA' and FLAG_ACTIVITE=1 |
| SQL Query Problem: How to aggregate discount percentages? Posted: 26 Mar 2013 02:30 AM PDT I have a scenario whereby I need to aggregate [i]n[/i] number of discounts to get a total discount %. Each discount must be applied net of the previous discount. [b]For example[/b]: I buy a hat for £100 and I have two valid vouchers. The first gets me 15% off, 100-(100*(15/100)) = £85. And then we have a second voucher worth 5%, 85-(85*5/100) = £80.75. So our total discount is [b]19.25[/b]%.Sequence is important, so a further field records the order in which the discounts are applied.So in our table we might have:[code]SaleID | Discount | Sequence1 15% 1 1 5% 22 5% 13 5% 13 10% 23 25% 3[/code]Is anyone able to put together an elegant query that will give the total discount for each sale using the above table? (I can think of some less elegant ways to do it, but there must be a cleaner way...?). As in the example above, I expect 19.25% for SaleID 1.Many Thanks. |
| You are subscribed to email updates from SQLServerCentral / SQL Server 2008 / T-SQL (SS2K8) To stop receiving these emails, you may unsubscribe now. | Email delivery powered by Google |
| Google Inc., 20 West Kinzie, Chicago IL USA 60610 | |
No comments:
Post a Comment