{"id":15476,"date":"2022-10-11T17:48:43","date_gmt":"2022-10-11T12:18:43","guid":{"rendered":"https:\/\/jassweb.com\/solved\/solved-sql-server-2008-calculate-date-and-price-change\/"},"modified":"2022-10-11T17:48:43","modified_gmt":"2022-10-11T12:18:43","slug":"solved-sql-server-2008-calculate-date-and-price-change","status":"publish","type":"post","link":"https:\/\/jassweb.com\/solved\/solved-sql-server-2008-calculate-date-and-price-change\/","title":{"rendered":"[Solved] SQL Server 2008 : calculate date and price change"},"content":{"rendered":"<p> [ad_1]<br \/>\n<\/p>\n<div id=\"answer-43944847\" class=\"answer js-answer accepted-answer js-accepted-answer\" data-answerid=\"43944847\" data-parentid=\"43944497\" data-score=\"1\" data-position-on-page=\"1\" data-highest-scored=\"1\" data-question-has-accepted-highest-score=\"1\" itemprop=\"acceptedAnswer\" itemscope itemtype=\"https:\/\/schema.org\/Answer\">\n<div class=\"post-layout\">\n<div class=\"votecell post-layout--left\"><\/div>\n<div class=\"answercell post-layout--right\">\n<div class=\"s-prose js-post-body\" itemprop=\"text\">\n<p>Something like: <\/p>\n<pre><code>DECLARE @artSales TABLE (artid int, dt date, price money);\n\nINSERT @artSales\nVALUES \n(1, '20170102', 10), (1, '20170108', 10), (1, '20170112', 8.50), (1, '20170115', 8.50),\n(2, '20170102', 20), (2, '20170109', 20), (2, '20170112', 35), (2, '20170116', 40),\n(3, '20170101', 500), (3, '20170111', 500), (3, '20170130', 500);\n\nSELECT \n  artid,\n  dt,\n  oldPrice = price,\n  PriceChange =\n  CASE \n    LAG(price, 1, price) OVER (PARTITION BY artid ORDER BY dt) \n    WHEN price THEN 0 ELSE 1\n  END,\n  NewPrice = LAG(price, 1, price) OVER (PARTITION BY artid ORDER BY dt)\nFROM @artSales;\n<\/code><\/pre>\n<p>Results:<\/p>\n<pre><code>artid       dt         oldPrice              PriceChange NewPrice\n----------- ---------- --------------------- ----------- ---------------------\n1           2017-01-02 10.00                 0           10.00\n1           2017-01-08 10.00                 0           10.00\n1           2017-01-12 8.50                  1           10.00\n1           2017-01-15 8.50                  0           8.50\n2           2017-01-02 20.00                 0           20.00\n2           2017-01-09 20.00                 0           20.00\n2           2017-01-12 35.00                 1           20.00\n2           2017-01-16 40.00                 1           35.00\n3           2017-01-01 500.00                0           500.00\n3           2017-01-11 500.00                0           500.00\n3           2017-01-30 500.00                0           500.00\n<\/code><\/pre>\n<p><strong>UPDATE &#8211; FOR PRE-2012 SYSTEMS:<\/strong><\/p>\n<pre><code>WITH prev AS\n(\n  SELECT rn = ROW_NUMBER() OVER (PARTITION BY artid ORDER BY dt), *\n  FROM @artSales\n)\nSELECT \n  as1.artid,\n  as1.dt,\n  OldPrice    = as1.price,\n  PriceChange = CASE WHEN as1.price &lt;&gt; as2.price THEN 1 ELSE 0 END,\n  NewPrice    = ISNULL(as2.price, as1.price)\nFROM prev as1\nLEFT JOIN prev as2 ON as1.artid = as2.artid AND as1.rn = as2.rn+1;\n<\/code><\/pre>\n<\/p><\/div>\n<div class=\"mt24\"><\/div>\n<\/div>\n<p>            <span class=\"d-none\" itemprop=\"commentCount\">2<\/span> <\/p><\/div>\n<\/div>\n<p>[ad_2]<\/p>\n<p>solved SQL Server 2008 : calculate date and price change <\/p>\n","protected":false},"excerpt":{"rendered":"<p>[ad_1] Something like: DECLARE @artSales TABLE (artid int, dt date, price money); INSERT @artSales VALUES (1, &#8216;20170102&#8217;, 10), (1, &#8216;20170108&#8217;, 10), (1, &#8216;20170112&#8217;, 8.50), (1, &#8216;20170115&#8217;, 8.50), (2, &#8216;20170102&#8217;, 20), (2, &#8216;20170109&#8217;, 20), (2, &#8216;20170112&#8217;, 35), (2, &#8216;20170116&#8217;, 40), (3, &#8216;20170101&#8217;, 500), (3, &#8216;20170111&#8217;, 500), (3, &#8216;20170130&#8217;, 500); SELECT artid, dt, oldPrice = price, PriceChange &#8230; <a title=\"[Solved] SQL Server 2008 : calculate date and price change\" class=\"read-more\" href=\"https:\/\/jassweb.com\/solved\/solved-sql-server-2008-calculate-date-and-price-change\/\" aria-label=\"More on [Solved] SQL Server 2008 : calculate date and price change\">Read more<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[320],"tags":[500,1009],"class_list":["post-15476","post","type-post","status-publish","format-standard","hentry","category-solved","tag-sql-server","tag-tsql"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v26.5 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>[Solved] SQL Server 2008 : calculate date and price change - JassWeb<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/jassweb.com\/solved\/solved-sql-server-2008-calculate-date-and-price-change\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"[Solved] SQL Server 2008 : calculate date and price change - JassWeb\" \/>\n<meta property=\"og:description\" content=\"[ad_1] Something like: DECLARE @artSales TABLE (artid int, dt date, price money); INSERT @artSales VALUES (1, &#039;20170102&#039;, 10), (1, &#039;20170108&#039;, 10), (1, &#039;20170112&#039;, 8.50), (1, &#039;20170115&#039;, 8.50), (2, &#039;20170102&#039;, 20), (2, &#039;20170109&#039;, 20), (2, &#039;20170112&#039;, 35), (2, &#039;20170116&#039;, 40), (3, &#039;20170101&#039;, 500), (3, &#039;20170111&#039;, 500), (3, &#039;20170130&#039;, 500); SELECT artid, dt, oldPrice = price, PriceChange ... Read more\" \/>\n<meta property=\"og:url\" content=\"https:\/\/jassweb.com\/solved\/solved-sql-server-2008-calculate-date-and-price-change\/\" \/>\n<meta property=\"og:site_name\" content=\"JassWeb\" \/>\n<meta property=\"article:published_time\" content=\"2022-10-11T12:18:43+00:00\" \/>\n<meta name=\"author\" content=\"Kirat\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Kirat\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-server-2008-calculate-date-and-price-change\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-server-2008-calculate-date-and-price-change\/\"},\"author\":{\"name\":\"Kirat\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\"},\"headline\":\"[Solved] SQL Server 2008 : calculate date and price change\",\"datePublished\":\"2022-10-11T12:18:43+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-server-2008-calculate-date-and-price-change\/\"},\"wordCount\":26,\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"keywords\":[\"sql-server\",\"tsql\"],\"articleSection\":[\"Solved\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-server-2008-calculate-date-and-price-change\/\",\"url\":\"https:\/\/jassweb.com\/solved\/solved-sql-server-2008-calculate-date-and-price-change\/\",\"name\":\"[Solved] SQL Server 2008 : calculate date and price change - JassWeb\",\"isPartOf\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#website\"},\"datePublished\":\"2022-10-11T12:18:43+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-server-2008-calculate-date-and-price-change\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/jassweb.com\/solved\/solved-sql-server-2008-calculate-date-and-price-change\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/jassweb.com\/solved\/solved-sql-server-2008-calculate-date-and-price-change\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/jassweb.com\/solved\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"[Solved] SQL Server 2008 : calculate date and price change\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/jassweb.com\/solved\/#website\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"name\":\"JassWeb\",\"description\":\"Build High-quality Websites\",\"publisher\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/jassweb.com\/solved\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/jassweb.com\/solved\/#organization\",\"name\":\"Jass Web\",\"url\":\"https:\/\/jassweb.com\/solved\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"contentUrl\":\"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png\",\"width\":693,\"height\":132,\"caption\":\"Jass Web\"},\"image\":{\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31\",\"name\":\"Kirat\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586\",\"contentUrl\":\"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586\",\"caption\":\"Kirat\"},\"sameAs\":[\"http:\/\/jassweb.com\"],\"url\":\"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"[Solved] SQL Server 2008 : calculate date and price change - JassWeb","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/jassweb.com\/solved\/solved-sql-server-2008-calculate-date-and-price-change\/","og_locale":"en_US","og_type":"article","og_title":"[Solved] SQL Server 2008 : calculate date and price change - JassWeb","og_description":"[ad_1] Something like: DECLARE @artSales TABLE (artid int, dt date, price money); INSERT @artSales VALUES (1, '20170102', 10), (1, '20170108', 10), (1, '20170112', 8.50), (1, '20170115', 8.50), (2, '20170102', 20), (2, '20170109', 20), (2, '20170112', 35), (2, '20170116', 40), (3, '20170101', 500), (3, '20170111', 500), (3, '20170130', 500); SELECT artid, dt, oldPrice = price, PriceChange ... Read more","og_url":"https:\/\/jassweb.com\/solved\/solved-sql-server-2008-calculate-date-and-price-change\/","og_site_name":"JassWeb","article_published_time":"2022-10-11T12:18:43+00:00","author":"Kirat","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Kirat","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/jassweb.com\/solved\/solved-sql-server-2008-calculate-date-and-price-change\/#article","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-server-2008-calculate-date-and-price-change\/"},"author":{"name":"Kirat","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31"},"headline":"[Solved] SQL Server 2008 : calculate date and price change","datePublished":"2022-10-11T12:18:43+00:00","mainEntityOfPage":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-server-2008-calculate-date-and-price-change\/"},"wordCount":26,"publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"keywords":["sql-server","tsql"],"articleSection":["Solved"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/jassweb.com\/solved\/solved-sql-server-2008-calculate-date-and-price-change\/","url":"https:\/\/jassweb.com\/solved\/solved-sql-server-2008-calculate-date-and-price-change\/","name":"[Solved] SQL Server 2008 : calculate date and price change - JassWeb","isPartOf":{"@id":"https:\/\/jassweb.com\/solved\/#website"},"datePublished":"2022-10-11T12:18:43+00:00","breadcrumb":{"@id":"https:\/\/jassweb.com\/solved\/solved-sql-server-2008-calculate-date-and-price-change\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/jassweb.com\/solved\/solved-sql-server-2008-calculate-date-and-price-change\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/jassweb.com\/solved\/solved-sql-server-2008-calculate-date-and-price-change\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/jassweb.com\/solved\/"},{"@type":"ListItem","position":2,"name":"[Solved] SQL Server 2008 : calculate date and price change"}]},{"@type":"WebSite","@id":"https:\/\/jassweb.com\/solved\/#website","url":"https:\/\/jassweb.com\/solved\/","name":"JassWeb","description":"Build High-quality Websites","publisher":{"@id":"https:\/\/jassweb.com\/solved\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/jassweb.com\/solved\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/jassweb.com\/solved\/#organization","name":"Jass Web","url":"https:\/\/jassweb.com\/solved\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/","url":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","contentUrl":"https:\/\/jassweb.com\/wp-content\/uploads\/2021\/02\/jass-website-logo-1.png","width":693,"height":132,"caption":"Jass Web"},"image":{"@id":"https:\/\/jassweb.com\/solved\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/65c9c7b7958150c0dc8371fa35dd7c31","name":"Kirat","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/jassweb.com\/solved\/#\/schema\/person\/image\/","url":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586","contentUrl":"https:\/\/jassweb.com\/solved\/wp-content\/litespeed\/avatar\/1261af3c9451399fa1336d28b98ea3bb.jpg?ver=1776403586","caption":"Kirat"},"sameAs":["http:\/\/jassweb.com"],"url":"https:\/\/jassweb.com\/solved\/author\/jaspritsinghghumangmail-com\/"}]}},"_links":{"self":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/15476","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/comments?post=15476"}],"version-history":[{"count":0,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/posts\/15476\/revisions"}],"wp:attachment":[{"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/media?parent=15476"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/categories?post=15476"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jassweb.com\/solved\/wp-json\/wp\/v2\/tags?post=15476"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}