Log in  ::  Register  ::  Not logged in

evaluating and combining values from two rows

 Author Message thomasrichardson2000 SSC Rookie Group: General Forum Members Points: 40 Visits: 154 I am running into a bit of trouble with the query below, would be great If you could offer some insight or advice on the best way forward.Basically the two tables in use are Vehicle and Vehicle_Depreciations. There is one row per unitnr in the vehicle table, within the Vehicle_Depreciations table there is two rows per unitnr. These two rows have different monthly depreciation values based on different date periods. The trouble I am having is calculating the depreciation period and depreciation value for cars that have been blocked for sale. The goal is to depreciate the vehicle up until and including the month that the vehicle is blocked(placed) for sale. What I would like to achieve in this statement is-- when vehicle is blocked for sale-- then calculate depreciation up until blocked for sale date(this could be a combination of both depreciation dates eg:48 months on first band at 112.47 and 5 months on second band at 508.61 combine these two values and subtract from the purchase value of the vehicle(vehicle.value)I have tried using subqueries to no avail, got errors because case statement was trying to return more than one value. I have provided some sample table structure and some results from the query. I would appreciate any input as I am working to develop my sql skills. Declare @date as datetimeset @date ='2012-07-31'DECLARE @vehicle TABLE (unitnr int, value int, date_block varchar) DECLARE @Vehicle_Depreciations TABLE (Start_Date datetime, End_Date datetime,Vehicle_Depreciation_Value int,unitnr int)INSERT @VehicleSELECT 724, 9430,2012-07-23 UNION ALLSELECT 725, 12430, nullINSERT @Vehicle_DepreciationsSELECT 2009-05-06, 2012-02-29 ,175,724 UNION ALL SELECT 2012-03-01,2012-08-30 ,284, 724 UNION ALL SELECT 2010-04-30, 2012-02-29,159,725 UNION ALL SELECT 2012-03-01,2012-12-31,183,725 select 'For Sale' as Type, Vehicle.unitnr, Vehicle.platenr,Vehicle.Type_Fleet, Vehicle.value,Vehicle_Depreciations.Start_Date,Vehicle_Depreciations.End_Date,Vehicle.Date_Block ,Vehicle_Depreciations.Vehicle_Depreciation_Value, sum(CASE WHEN Vehicle_Depreciations.END_DATE >@DATE--checks if end_date is greater than the date passed THEN DATEDIFF(MONTH,Vehicle_Depreciations.START_DATE,DATEADD(day,1,@DATE))--determines the months of depreciation from start_date till date passed WHEN Vehicle_Depreciations.END_DATE > Vehicle.Date_Block-- checks if end_date is past Date_Blocked for sale THEN DATEDIFF(MONTH,Vehicle_Depreciations.START_DATE,Vehicle.Date_Block) --if so, then determines the months of depreciation from start_Date till dat_blocked ELSE DATEDIFF(MONTH,Vehicle_Depreciations.START_DATE,Vehicle_Depreciations.END_DATE+1) END * Vehicle_Depreciations.Vehicle_Depreciation_Value )AS depr --determines the months of depreciation between start_date and end_date from Vehicle inner join Vehicle_Depreciations on Vehicle.unitnr = Vehicle_Depreciations.UnitNr WHERE Vehicle.Type_Fleet = 'of' --own fleet vehicles AND Vehicle.Status <> '4' --excludes sold cars and vehicle.Date_Block is not null group by Vehicle.unitnr, Vehicle.platenr, Vehicle.groupid, Vehicle.brandId, Vehicle.modelId, Vehicle.category, Vehicle.kms, Vehicle.value, Vehicle.Type_Fleet,vehicle.Date_Block,Vehicle.purchase_date,Vehicle.Sales_Date,Vehicle.Invoice_Value,Vehicle.Invoice_Tax_Value,Vehicle.Extras_Value,Vehicle.Expenses,Vehicle.Another_Tax,Vehicle.Other_Extras_Value,Vehicle_Depreciations.End_Date,Vehicle_Depreciations.Start_Date,Vehicle_Depreciations.Vehicle_Depreciation_Value TYPE UNITNR PLATENR TYPE_FLEET VALUE START-DATE END_DATE DATE_BLOCKED VEHICLE_DEPRECIATION_VALUE DEPRFor Sale 28 08D12345 OF 10674.18 2008-02-29 2012-02-29 2012-07-24 112.47 5511.03For Sale 28 08D12345 OF 10674.18 2012-03-01 2012-08-30 2012-07-24 508.61 2543.05For Sale 29 08D54321 OF 10607.12 2008-02-29 2012-02-29 2012-02-29 81.53 3913.44For Sale 29 08D54321 OF 10607.12 2012-03-01 2012-08-30 2012-02-29 284.49 1422.45For Sale 30 08D12346 OF 5536.77 2008-02-29 2012-02-29 2012-02-29 7.57 363.36 Luis Cazares SSCrazy Eights Group: General Forum Members Points: 8506 Visits: 18113 Is a good thing that you posted DDL, sample data and expected results. But the problem is it's not matching with each other.You might want to check your definition on date_block field from Vehicle table. Is a varchar with no length defined, that will truncate the values to a lendth of 1.Another thing that had problems is that your query uses more fields than the vehicle table has (that might be a problem caused by simplifying the query to post it here).The last thing, is that you have sample data for vehicles 724 and 725 and expected results for 28, 29 and 30.You seem to be very close to your result, but I'm not sure on what is missing unless you give me the correct expected results. Luis C.General Disclaimer:Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?How to post data/code on a forum to get the best help: Option 1 / Option 2 thomasrichardson2000 SSC Rookie Group: General Forum Members Points: 40 Visits: 154 Hi Luiz, I just posted the ddl as a guide, the values may not be 100%, should of fine tuned them before posting,have no access at this exact time.Basically its the actual case statement im concerned with , I don't know if the actual logic within the sql is correct or if a case statement is even the right way to actually achieve a result, is there another way of doing it? TYPE UNITNR PLATENR TYPE_FLEET VALUE START-DATE END_DATE DATE_BLOCKED VEHICLE_DEPRECIATION_VALUE DEPRFor Sale | 28 | 08D12345 | OF | 10674.18 | 2008-02-29 | 2012-02-29 | 2012-07-24 | 112.47 | 5511.03For Sale | 28 | 08D12345 | OF | 10674.18 | 2012-03-01 | 2012-08-30 | 2012-07-24 | 508.61 | 2543.05here is what i needTYPE| UNITNR| PLATENR| TYPE_FLEET| VALUE| DATE_BLOCKED | DEPRFor Sale| 28| 08D12345 |OF| 10674.18| 2012-07-24 112.47| 7938The key figure here is the result DEPR 7938 .This number comes from the first period of depreciation 2008-02-29 to 2012-02-29 is 48 months which is then * 112.47 this equals 5398. This figure is added to the second period of depreciation 2012-03-01 to (2012-07-24(date vehicle blocked for sale) which is 2540. This adds up to 7938This is where I need to get to, so anything that can keep me moving is appreciated. Lynn Pettis SSC-Insane Group: General Forum Members Points: 24176 Visits: 37943 CELKO (8/17/2012)Aren't you required by law to use the VIN and not invent your own identifier? Or is this just aq skeleton? I have a horror story about a taxi company, VINs, medallions, and tax id numbers …, >> Basically the two tables in use are Vehicle and Vehicle_Depreciation. There is one row per VIN in the vehicle table, within the Vehicle_Depreciation table there is two rows per VIN. These two rows have different monthly depreciation values based on different date periods. <>, vehicle_value DECIMAL (8,2) NOT NULL, CHECK (vehicle_value >= 0.00), vehicle_purchase_date DATE NOT NULL, vehicle_retirement_date DATE, --- null is in use CHECK (vehicle_purchase_date < vehicle_retirement_date, fleet_type ??, something_status ??, etc);Too bad you did not tell us all of the columns you later use in the query. See the collective name, industry standard identifier, correct data type for currency, and proper temporal model? Take a tip from an old SQL guru; 80-95% of the work is done in the DDL, not in the DML.Your table had no keys, not way to enforce the two deprecation rule. New columns magically appear in that query, the names are too vague or are just wrong (brand_id is vehicle_make in this industry, likewise vehicle_model, etc. Category of what? The used of CASE is wrong; you are trying to wrote if-then-else logic and SQL. Use the idiom “some_date BETWEEN start_date AND COALESCE (end_daet, CURRENT_TIMESTAMP) instead. Try this clean up:CREATE TABLE Vehicle_Depreciation_Schedule( vin CHAR(19) NOT NULL PRIMARY KEY REFERENCES Motorpool), depre_start_date DATE NOT NULL, PRIMARY KEY (vin, depre_start_date), depre_end_date DATE NOT NULL, CHECK (depre_start_date < depre_end_date), vehicle_depreciation_value DECIMAL (8,2) NOT NULL CHECK (vehicle_depreciation_value >= 0.00));after this point, your code is so confused I cannot read it (I just had eye surgery). Play with this and post again. I get my right eye done on Monday, so I might not get back to you immediately.My thought was to download the KBB tables and search on (mileage, make, model, year).Mr. Celko, either your spell check isn't working or your eyes are still suffering from the surgery. You may want to take a break. Have a happy Friday and a good weekend. Lynn PettisFor better assistance in answering your questions, click hereFor tips to get better help with Performance Problems, click hereFor Running Totals and its variations, click here or when working with partitioned tablesFor more about Tally Tables, click hereFor more about Cross Tabs and Pivots, click here and hereManaging Transaction LogsSQL Musings from the Desert Fountain Valley SQL (My Mirror Blog) Luis Cazares SSCrazy Eights Group: General Forum Members Points: 8506 Visits: 18113 Mr. Celko,The main problem in here wouldn't be the DDL as we cannot see the original table just a simplified version of it. I'm pretty sure the OP shouldn't be changing the database structure one table at a time, that's just pointless and would imply lots of work on SQL Server and on the front ends. If you can't give a solution (or read the problem), please avoid posting anything. I'm sure that when someone wants to design a database with little knowledge on database design, they can go to the Stairway you wrote.To Thomas,I'm having some trouble with your posts, as there are three main problems I found:1. Your calculations are wrong or have some logic you didn't post (48 times 112.47 equals 5398.56 not 5398 and 508.61 times 5 equals 2543.05 not 2540) it might seem very few difference, but it's still a difference.2. Your values are declared int but you might want to use a numeric/decimal data type to avoid truncation.3. Your dates can have some trouble since DATEDIFF( mm, ' 20080229', '20120229') will give 49 and not 48. (I'm sure this was happening on a 2005 version, but I'm trying to reproduce this problem and won't happen in 2012 but I don't have a 2008 here).Be sure to test each part of your code before using it. And take care of your math.;-) Luis C.General Disclaimer:Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?How to post data/code on a forum to get the best help: Option 1 / Option 2 thomasrichardson2000 SSC Rookie Group: General Forum Members Points: 40 Visits: 154 Mr celko, "They are attributes of the same Vehicle, so why are they split ac cross two rows? This sounds like an example of the design flaw known as attribute splitting. Let's look closer. " This is my problem, if all data was on the same row my job would be easier. I cannot change the underlying table structure, so I have to make the best of it. I understand what you are saying about "vins" etc, but I didn't design the tables so I have to suffer it. Luiz I really need to get this query to work, so if anybody can tell me what they exactly need me to post. I understand the math was not 100% I just put it together in haste as a guide while away with my family the weekend. Will I post exact ddl and some obfuscated results and then The exact results I require,would this help?Thanks again everybody. dwain.c SSCarpal Tunnel Group: General Forum Members Points: 4249 Visits: 6431 Not sure but you may be able to do something like this assuming there is no overlap in dates in your @Vehicle_Depreciations table:`;WITH Tally (n) AS ( SELECT TOP 1000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns)SELECT a.unitnr, depr_date=m, Vehicle_Depreciation_Value --=SUM(Vehicle_Depreciation_Value)--SELECT a.unitnr, Vehicle_Depreciation_Value=SUM(Vehicle_Depreciation_Value)FROM @Vehicle aINNER JOIN @Vehicle_Depreciations b ON a.unitnr = b.unitnrCROSS APPLY ( SELECT n, m=DATEADD(month, n-1, Start_Date) FROM Tally WHERE n BETWEEN 1 AND 1+DATEDIFF(month, Start_Date, End_Date)) cWHERE m <= ISNULL(date_block, CAST('2099-01-01' AS DATETIME))--GROUP BY a.unitnrORDER BY a.unitnr, m`If you need total "to date" depreciation for a vehicle, then comment out the SELECT, uncomment the SELECT below it, uncomment the GROUP BY and remove m from the ORDER BY list. My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables Luis Cazares SSCrazy Eights Group: General Forum Members Points: 8506 Visits: 18113 From what I remember from your query, it was working ok if you made the corrections I told you.If you want to obtain the total devaluation value, you should not include columns from Vehicle_Depreciations table.This might help you, read the comments I made`Declare @date as datetimeset @date ='20120731'DECLARE @vehicle TABLE ( unitnr int, value decimal( 18, 8), --changed the data type date_block datetime, --changed the data type type_fleet char(2), --added column platenr char(9)) --added columnDECLARE @Vehicle_Depreciations TABLE ( Start_Date datetime, End_Date datetime, Vehicle_Depreciation_Value decimal( 18, 8), --changed the data type unitnr int)--Changed ALL dates formatINSERT @VehicleSELECT 724, 9430, '20120723', 'of', '08D12345' UNION ALLSELECT 725, 12430, null, 'of', '23s546321'INSERT @Vehicle_DepreciationsSELECT '20090506', '20120229' ,175,724 UNION ALL SELECT '20120301', '20120830' ,284, 724 UNION ALL SELECT '20100430', '20120229',159,725 UNION ALL SELECT '20120301', '20121231',183,725select 'For Sale' as Type, v.unitnr, v.value, v.platenr, v.Type_Fleet, --vd.Start_Date, --If you uncomment this fields, you'll get a detail for each depreciation period --vd.End_Date, v.Date_Block , --vd.Vehicle_Depreciation_Value, sum(CASE WHEN vd.END_DATE >@DATE--checks if end_date is greater than the date passed THEN DATEDIFF(MONTH,vd.START_DATE,DATEADD(day,1,@DATE))--determines the months of depreciation from start_date till date passed WHEN vd.END_DATE > v.Date_Block-- checks if end_date is past Date_Blocked for sale THEN DATEDIFF(MONTH,vd.START_DATE,v.Date_Block) --if so, then determines the months of depreciation from start_Date till dat_blocked ELSE DATEDIFF(MONTH,vd.START_DATE,vd.END_DATE+1) END * vd.Vehicle_Depreciation_Value )AS depr --determines the months of depreciation between start_date and end_date from @vehicle v inner join @Vehicle_Depreciations vd on v.unitnr = vd.UnitNrWHERE v.Date_Block is not nullgroup by v.unitnr, v.value, v.Date_Block,v.platenr, v.Type_Fleet` Luis C.General Disclaimer:Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?How to post data/code on a forum to get the best help: Option 1 / Option 2 thomasrichardson2000 SSC Rookie Group: General Forum Members Points: 40 Visits: 154 --The problem with your code Luiz is that it is calculating the two periods together, but the date_block (20120406) is between the second period, so I need a way of only calculating up until the date_block (20120406) . The figure of depr I need from the below sql is 3925.58, this figure is made up of 24 months at 151..45 and 2 months at 145.39 . It seems the query is not taking into account the Date_block date.Declare @date as datetimeset @date ='20120731'DECLARE @vehicle TABLE ( unitnr int, value decimal( 18, 8), --changed the data type date_block datetime, --changed the data type type_fleet char(2), --added column platenr char(9)) --added columnDECLARE @Vehicle_Depreciations TABLE ( Start_Date datetime, End_Date datetime, Vehicle_Depreciation_Value decimal( 18, 8), --changed the data type unitnr int)--Changed ALL dates formatINSERT @VehicleSELECT 29, 8057.85, '20120406', 'of', '08D12345' INSERT @Vehicle_DepreciationsSELECT '20100301', '20120229' ,151.45,29 UNION ALL SELECT '20120301', '20140801' ,145.39, 29 select 'For Sale' as Type, v.unitnr, v.value, v.platenr, v.Type_Fleet, --vd.Start_Date, --If you uncomment this fields, you'll get a detail for each depreciation period --vd.End_Date, v.Date_Block , --vd.Vehicle_Depreciation_Value, sum(CASE WHEN vd.END_DATE >@DATE--checks if end_date is greater than the date passed THEN DATEDIFF(MONTH,vd.START_DATE,DATEADD(day,1,@DATE))--determines the months of depreciation from start_date till date passed WHEN vd.END_DATE > v.Date_Block-- checks if end_date is past Date_Blocked for sale THEN DATEDIFF(MONTH,vd.START_DATE,v.Date_Block) --if so, then determines the months of depreciation from start_Date till dat_blocked ELSE DATEDIFF(MONTH,vd.START_DATE,vd.END_DATE+1) END * vd.Vehicle_Depreciation_Value )AS depr --determines the months of depreciation between start_date and end_date from @vehicle v inner join @Vehicle_Depreciations vd on v.unitnr = vd.UnitNrWHERE v.Date_Block is not nullgroup by v.unitnr, v.value, v.Date_Block,v.platenr, v.Type_Fleet

Permissions

 You can't post new topics. You can't post topic replies. You can't post new polls. You can't post replies to polls. You can't edit your own topics. You can't delete your own topics. You can't edit other topics. You can't delete other topics. You can't edit your own posts. You can't edit other posts. You can't delete your own posts. You can't delete other posts. You can't post events. You can't edit your own events. You can't edit other events. You can't delete your own events. You can't delete other events. You can't send private messages. You can't send emails. You can read topics. You can't vote in polls. You can't upload attachments. You can download attachments. You can't post HTML code. You can't edit HTML code. You can't post IFCode. You can't post JavaScript. You can post emoticons. You can't post or upload images.

Select a forum
 SQL Server 2016      SQL Server 2016 - Administration      SQL Server 2016 - Development and T-SQL SQL Server 2014      Administration - SQL Server 2014      Development - SQL Server 2014 SQL Server 2012      SQL 2012 - General      SQL Server 2012 - T-SQL SQL Server vNext      SQL Server 14 - Administration      SQL Server 14 - Development SQL Server 2008      SQL Server 2008 - General      T-SQL (SS2K8)      June 2007 CTP      Working with Oracle      July CTP      SQL Server Newbies      Security (SS2K8)      SQL Server 2008 High Availability      SQL Server 2008 Administration      Data Corruption (SS2K8 / SS2K8 R2)      SQL Server 2008 Performance Tuning Cloud Computing      SQL Azure - Development      SQL Azure - Administration      Amazon AWS and other cloud vendors      General Cloud Computing Questions Reporting Services      Reporting Services      Reporting Services 2005 Administration      Reporting Services 2005 Development      Reporting Services 2008/R2 Administration      Reporting Services 2008 Development      SSRS 2012      SSRS 2014      SSRS 2016 Programming      Connecting      General      SMO/RMO/DMO      XML      Service Broker      Powershell      Testing      TFS/Data Dude/DBPro      SSDT      Continuous Integration, Deployment, and Delivery      R Services and R Language Data Warehousing      Integration Services      Strategies and Ideas      Analysis Services      Data Transformation Services (DTS)      Performance Point      Data Mining      PowerPivot      R language      Machine Learning Database Design      Disaster Recovery      Design Ideas and Questions      Relational Theory      Hardware      Virtualization SQLServerCentral.com      Anything that is NOT about SQL!      Contests!      Editorials      SQLServerCentral.com Announcements      SQLServerCentral.com Website Issues      Suggestions      Tag Issues with Content      Podcast Feedback      SQLServerCentral.com Test Forum      Articles Requested SQL Server 2005      Administering      Backups      Business Intelligence      CLR Integration and Programming.      Data Corruption      Development      Working with Oracle      SQL Server 2005 Compact Edition      SQL Server 2005 General Discussion      SQL Server 2005 Security      SQL Server 2005 Strategies      SS2K5 Replication      SQL Server Express      SQL Server 2005 Performance Tuning      SQL Server 2005 Integration Services      T-SQL (SS2K5)      SQL Server Newbies SQL Server 7,2000      Administration      Backups      Data Corruption      General      Globalization      In The Enterprise      Working with Oracle      Security      Strategies      SQL Server Newbies      Service Packs      SQL Server CE      Performance Tuning      Replication      Sarbanes-Oxley      T-SQL      SQL Server Agent SQL Server and other platforms      MySQL      Oracle      PostgreSQL      DB2      SQL Server and Sharepoint Older Versions of SQL (v6.5, v6.0, v4.2)      Older Versions of SQL (v6.5, v6.0, v4.2) Career      Certification      Employers and Employees      Events      Job Postings      Resumes and Job Hunters      Presentations and Speaking      Retired Members Testing Center      SQL Server Security Skills      Question of the Day (QOD) Microsoft Access      Microsoft Access Products and Books      Third Party Products         SQLCentric         Extreme Technologies.         Innovartis         Embarcadero         SQL Sentry         Sonasoft         Golden Gate Software         Idera         Red Gate Software         Quest Software         ApexSQL         Lumigent         CA      Discussions about Books         Discuss Programming Books          Discuss XML Books          Discuss T-SQL Books          Discuss Data Warehousing Books          Discuss DTS Books          Discuss SQL Server 2000 Books         Discuss SQL Server 7.0 Books Notification Services      Administration Article Discussions Future Versions      SQL 12

Search

 Copyright © 2002-2017 Redgate. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.