{"id":227,"date":"2013-03-09T16:44:23","date_gmt":"2013-03-09T14:44:23","guid":{"rendered":"http:\/\/wintrscada.com\/?p=227"},"modified":"2021-12-18T13:30:52","modified_gmt":"2021-12-18T11:30:52","slug":"rapor-fark-hesabi","status":"publish","type":"post","link":"https:\/\/www.fultek.com.tr\/scada-yardim\/2013\/03\/rapor-fark-hesabi\/","title":{"rendered":"Rapor Fark Hesab\u0131"},"content":{"rendered":"

G\u00fcnl\u00fck \u00fcretim ve benzeri verilerin hesaplanmas\u0131nda Database\u2019e kay\u0131tl\u0131 verilerin farklar\u0131n\u0131 elde etmemiz gerekir. A\u015fa\u011f\u0131da a\u00e7\u0131klayaca\u011f\u0131m SQL sorgusu ile bu bilgilere ba\u011fl\u0131 rapor olu\u015fturabilirsiniz. Bu sorgular WinTr scada<\/a> rapor mod\u00fcl\u00fcne uygun olarak haz\u0131rlanm\u0131\u015ft\u0131r.<\/p>\n

\u0130lk sorgumuz scada rapor mod\u00fcl\u00fc taraf\u0131ndan standart olarak \u00fcretilen belli tarihler aras\u0131ndaki kay\u0131tlar\u0131 getiren sorgudur.<\/p>\n

SELECT [DateTime],[Tag_1],[Tag_2],[Tag_3] FROM Table1 WHERE [DateTime] >= \u2018StartDate\u2019 And [DateTime] <= \u2018EndDate\u2019<\/p>\n

Bu sorguyu k\u0131saca a\u00e7\u0131klayal\u0131m. Select komutundan sonra datebase\u2019deki kolon isimlerini yaz\u0131yoruz. From ifadesi ise database\u2019deki tablo ismidir. WinTr standart olarak Table1 isimli tabloyu olu\u015fturur. Where komutu ise belli tarih aral\u0131\u011f\u0131n\u0131 getirmek i\u00e7in sorguya eklenen bir k\u0131s\u0131tlamad\u0131r. Where komutunu kullanmayd\u0131k t\u00fcm database\u2019i getirecekti. StartDate ve EndDate tarih se\u00e7imi yapabilmemiz i\u00e7in internal scada taglar\u0131d\u0131r.<\/p>\n

Yukar\u0131daki sorgu ile tablomuz olu\u015fturuldu. Bu tablonun alt\u0131na yeni bir sat\u0131rda tablodaki ilk de\u011fer ile son de\u011fer aras\u0131ndaki fark\u0131 getirece\u011fiz. \u00d6ncelikle UNION ALL komutunu anlayal\u0131m. Bu komut Tablonun alt\u0131nda yeni bir tablo olu\u015fturmak i\u00e7in kullan\u0131l\u0131r. Her iki tablo scada i\u00e7ersinde tek tablo olarak g\u00f6r\u00fcn\u00fcr. Her iki tablodada kolon tipleri ayn\u0131 olmal\u0131d\u0131r. \u00d6rne\u011fin ilk tablomuzun ilk kolonu Datetime tipinde oldu\u011fu i\u00e7in yeni tablodada Datetime tipinde bir de\u011fer bulunmal\u0131. \u0130lk sorgumuzun alt\u0131na a\u015fa\u011f\u0131daki sat\u0131rlar\u0131 ekleyelim.<\/p>\n

UNION ALL<\/p>\n

SELECT (select Top 1 DateTime from Table1 ORDER BY DateTime DESC)<\/p>\n

Yukar\u0131da g\u00f6r\u00fcld\u00fc\u011f\u00fc gibi UNION ALL ile ikinci bir tablo olu\u015fturuldu ve ilk kolon DateTime i\u00e7in olu\u015fturuldu. Bu k\u0131s\u0131mda Top 1 komutu ile ilk veri getirilicektir. Ancak sorgu sonuna \u201cORDER BY DateTime DESC\u201d komutunu ekleyerek son kay\u0131d\u0131n getirilmesi sa\u011fland\u0131. \u015eimdi as\u0131l hedefimiz olan belli tarihler aras\u0131ndaki ilk kay\u0131t ile son kay\u0131t aras\u0131nda ki fark\u0131 bulan sorguyu ekleyelim.<\/p>\n

(select Top 1 Tag_1 from Table1 WHERE [DateTime] >= \u2018StartDate\u2019 And [DateTime] <= \u2018EndDate\u2019 ORDER BY Tag_1 DESC) \u2013 (select Top 1 Tag_1 from Table1 WHERE [DateTime] >= \u2018StartDate\u2019 And [DateTime] <= \u2018EndDate\u2019 )<\/p>\n

Epey uzun bir sorgu oldu, sorgunun tamam\u0131 \u00e7ok daha uzun. Bu sorgu eksi(-) i\u015fareti ile son de\u011ferden ilk de\u011feri \u00e7\u0131kart\u0131yor. \u0130lk ve son de\u011ferler belirtti\u011fimiz tarih aral\u0131\u011f\u0131n\u0131n ilk ve son de\u011ferleridir. Sorgunun sonunda parantez\u2019den \u00f6nce bir bo\u015fluk b\u0131rak\u0131lmas\u0131 zorunludur. A\u015fa\u011f\u0131da sorgunun tamam\u0131 birlekte olarak verilmi\u015ftir.<\/p>\n

SELECT [DateTime],[Tag_1],[Tag_2],[Tag_3] FROM Table1 WHERE [DateTime] >= \u2018StartDate\u2019 And [DateTime] <= \u2018EndDate\u2019 UNION ALL SELECT (select Top 1 DateTime from Table1 ORDER BY DateTime DESC),(select Top 1 Tag_1 from Table1 WHERE [DateTime] >= \u2018StartDate\u2019 And [DateTime] <= \u2018EndDate\u2019 ORDER BY Tag_1 DESC) \u2013 (select Top 1 Tag_1 from Table1 WHERE [DateTime] >= \u2018StartDate\u2019 And [DateTime] <= \u2018EndDate\u2019 ),(select Top 1 Tag_2 from Table1 WHERE [DateTime] >= \u2018StartDate\u2019 And [DateTime] <= \u2018EndDate\u2019 ORDER BY Tag_2 DESC) \u2013 (select Top 1 Tag_2 from Table1 WHERE [DateTime] >= \u2018StartDate\u2019 And [DateTime] <= \u2018EndDate\u2019 ),(select Top 1 Tag_3 from Table1 WHERE [DateTime] >= \u2018StartDate\u2019 And [DateTime] <= \u2018EndDate\u2019 ORDER BY Tag_3 DESC) \u2013 (select Top 1 Tag_3 from Table1 WHERE [DateTime] >= \u2018StartDate\u2019 And [DateTime] <= \u2018EndDate\u2019 )<\/p>\n

Biraz dah basit olmas\u0131 i\u00e7in ba\u015flang\u0131\u00e7 ve biti\u015f tarihleri bulunmayan sorguyuda a\u015fa\u011f\u0131ya ekliyorum.<\/p>\n

SELECT [DateTime],[Tag_1],[Tag_2],[Tag_3] FROM Table1 UNION ALL SELECT Null,(select Top 1 Tag_1 from Table1 ORDER BY Tag_1 DESC) \u2013 (select Top 1 Tag_1 from Table1),(select Top 1 Tag_2 from Table1 ORDER BY Tag_2 DESC) \u2013 (select Top 1 Tag_2 from Table1),(select Top 1 Tag_3 from Table1 ORDER BY Tag_3 DESC) \u2013 (select Top 1 Tag_3 from Table1)<\/p>\n","protected":false},"excerpt":{"rendered":"

G\u00fcnl\u00fck \u00fcretim ve benzeri verilerin hesaplanmas\u0131nda Database\u2019e kay\u0131tl\u0131 verilerin farklar\u0131n\u0131 elde etmemiz gerekir. A\u015fa\u011f\u0131da a\u00e7\u0131klayaca\u011f\u0131m SQL sorgusu ile bu bilgilere ba\u011fl\u0131 rapor olu\u015fturabilirsiniz. Bu sorgular WinTr scada rapor mod\u00fcl\u00fcne uygun olarak haz\u0131rlanm\u0131\u015ft\u0131r. \u0130lk sorgumuz scada rapor mod\u00fcl\u00fc taraf\u0131ndan standart olarak \u00fcretilen belli tarihler aras\u0131ndaki kay\u0131tlar\u0131 getiren sorgudur. SELECT [DateTime],[Tag_1],[Tag_2],[Tag_3] FROM Table1 WHERE [DateTime] >= \u2018StartDate\u2019 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28],"tags":[139],"class_list":["post-227","post","type-post","status-publish","format-standard","hentry","category-scada-report","tag-database-verileri-arasindaki-fark"],"yoast_head":"\nRapor Fark Hesab\u0131 - SCADA E\u011fitim Sitesi<\/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:\/\/www.fultek.com.tr\/scada-yardim\/2013\/03\/rapor-fark-hesabi\/\" \/>\n<meta name=\"twitter:label1\" content=\"Yazan:\" \/>\n\t<meta name=\"twitter:data1\" content=\"Salih\" \/>\n\t<meta name=\"twitter:label2\" content=\"Tahmini okuma s\u00fcresi\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 dakika\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.fultek.com.tr\\\/scada-yardim\\\/2013\\\/03\\\/rapor-fark-hesabi\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.fultek.com.tr\\\/scada-yardim\\\/2013\\\/03\\\/rapor-fark-hesabi\\\/\"},\"author\":{\"name\":\"Salih\",\"@id\":\"https:\\\/\\\/www.fultek.com.tr\\\/scada-yardim\\\/#\\\/schema\\\/person\\\/b439f08807fdc9e273a233976c5eb1be\"},\"headline\":\"Rapor Fark Hesab\u0131\",\"datePublished\":\"2013-03-09T14:44:23+00:00\",\"dateModified\":\"2021-12-18T11:30:52+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.fultek.com.tr\\\/scada-yardim\\\/2013\\\/03\\\/rapor-fark-hesabi\\\/\"},\"wordCount\":620,\"commentCount\":1,\"keywords\":[\"Database Verileri Aras\u0131ndaki Fark\"],\"articleSection\":[\"Rapor\"],\"inLanguage\":\"tr\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.fultek.com.tr\\\/scada-yardim\\\/2013\\\/03\\\/rapor-fark-hesabi\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.fultek.com.tr\\\/scada-yardim\\\/2013\\\/03\\\/rapor-fark-hesabi\\\/\",\"url\":\"https:\\\/\\\/www.fultek.com.tr\\\/scada-yardim\\\/2013\\\/03\\\/rapor-fark-hesabi\\\/\",\"name\":\"Rapor Fark Hesab\u0131 - SCADA E\u011fitim Sitesi\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.fultek.com.tr\\\/scada-yardim\\\/#website\"},\"datePublished\":\"2013-03-09T14:44:23+00:00\",\"dateModified\":\"2021-12-18T11:30:52+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.fultek.com.tr\\\/scada-yardim\\\/#\\\/schema\\\/person\\\/b439f08807fdc9e273a233976c5eb1be\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.fultek.com.tr\\\/scada-yardim\\\/2013\\\/03\\\/rapor-fark-hesabi\\\/#breadcrumb\"},\"inLanguage\":\"tr\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.fultek.com.tr\\\/scada-yardim\\\/2013\\\/03\\\/rapor-fark-hesabi\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.fultek.com.tr\\\/scada-yardim\\\/2013\\\/03\\\/rapor-fark-hesabi\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.fultek.com.tr\\\/scada-yardim\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Rapor Fark Hesab\u0131\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.fultek.com.tr\\\/scada-yardim\\\/#website\",\"url\":\"https:\\\/\\\/www.fultek.com.tr\\\/scada-yardim\\\/\",\"name\":\"SCADA E\u011fitim Sitesi\",\"description\":\"SCADA program\u0131 hakk\u0131nda bilmek istedikleriniz.\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.fultek.com.tr\\\/scada-yardim\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"tr\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.fultek.com.tr\\\/scada-yardim\\\/#\\\/schema\\\/person\\\/b439f08807fdc9e273a233976c5eb1be\",\"name\":\"Salih\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"tr\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/71953beeb6b97dd8eeecb5d9b86339eedb7662215ebdf0761d17ac26bc31f94b?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/71953beeb6b97dd8eeecb5d9b86339eedb7662215ebdf0761d17ac26bc31f94b?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/71953beeb6b97dd8eeecb5d9b86339eedb7662215ebdf0761d17ac26bc31f94b?s=96&d=mm&r=g\",\"caption\":\"Salih\"},\"sameAs\":[\"http:\\\/\\\/www.fultek.com.tr\"],\"url\":\"https:\\\/\\\/www.fultek.com.tr\\\/scada-yardim\\\/author\\\/salih\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Rapor Fark Hesab\u0131 - SCADA E\u011fitim Sitesi","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:\/\/www.fultek.com.tr\/scada-yardim\/2013\/03\/rapor-fark-hesabi\/","twitter_misc":{"Yazan:":"Salih","Tahmini okuma s\u00fcresi":"3 dakika"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.fultek.com.tr\/scada-yardim\/2013\/03\/rapor-fark-hesabi\/#article","isPartOf":{"@id":"https:\/\/www.fultek.com.tr\/scada-yardim\/2013\/03\/rapor-fark-hesabi\/"},"author":{"name":"Salih","@id":"https:\/\/www.fultek.com.tr\/scada-yardim\/#\/schema\/person\/b439f08807fdc9e273a233976c5eb1be"},"headline":"Rapor Fark Hesab\u0131","datePublished":"2013-03-09T14:44:23+00:00","dateModified":"2021-12-18T11:30:52+00:00","mainEntityOfPage":{"@id":"https:\/\/www.fultek.com.tr\/scada-yardim\/2013\/03\/rapor-fark-hesabi\/"},"wordCount":620,"commentCount":1,"keywords":["Database Verileri Aras\u0131ndaki Fark"],"articleSection":["Rapor"],"inLanguage":"tr","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.fultek.com.tr\/scada-yardim\/2013\/03\/rapor-fark-hesabi\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.fultek.com.tr\/scada-yardim\/2013\/03\/rapor-fark-hesabi\/","url":"https:\/\/www.fultek.com.tr\/scada-yardim\/2013\/03\/rapor-fark-hesabi\/","name":"Rapor Fark Hesab\u0131 - SCADA E\u011fitim Sitesi","isPartOf":{"@id":"https:\/\/www.fultek.com.tr\/scada-yardim\/#website"},"datePublished":"2013-03-09T14:44:23+00:00","dateModified":"2021-12-18T11:30:52+00:00","author":{"@id":"https:\/\/www.fultek.com.tr\/scada-yardim\/#\/schema\/person\/b439f08807fdc9e273a233976c5eb1be"},"breadcrumb":{"@id":"https:\/\/www.fultek.com.tr\/scada-yardim\/2013\/03\/rapor-fark-hesabi\/#breadcrumb"},"inLanguage":"tr","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.fultek.com.tr\/scada-yardim\/2013\/03\/rapor-fark-hesabi\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.fultek.com.tr\/scada-yardim\/2013\/03\/rapor-fark-hesabi\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.fultek.com.tr\/scada-yardim\/"},{"@type":"ListItem","position":2,"name":"Rapor Fark Hesab\u0131"}]},{"@type":"WebSite","@id":"https:\/\/www.fultek.com.tr\/scada-yardim\/#website","url":"https:\/\/www.fultek.com.tr\/scada-yardim\/","name":"SCADA E\u011fitim Sitesi","description":"SCADA program\u0131 hakk\u0131nda bilmek istedikleriniz.","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.fultek.com.tr\/scada-yardim\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"tr"},{"@type":"Person","@id":"https:\/\/www.fultek.com.tr\/scada-yardim\/#\/schema\/person\/b439f08807fdc9e273a233976c5eb1be","name":"Salih","image":{"@type":"ImageObject","inLanguage":"tr","@id":"https:\/\/secure.gravatar.com\/avatar\/71953beeb6b97dd8eeecb5d9b86339eedb7662215ebdf0761d17ac26bc31f94b?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/71953beeb6b97dd8eeecb5d9b86339eedb7662215ebdf0761d17ac26bc31f94b?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/71953beeb6b97dd8eeecb5d9b86339eedb7662215ebdf0761d17ac26bc31f94b?s=96&d=mm&r=g","caption":"Salih"},"sameAs":["http:\/\/www.fultek.com.tr"],"url":"https:\/\/www.fultek.com.tr\/scada-yardim\/author\/salih\/"}]}},"_links":{"self":[{"href":"https:\/\/www.fultek.com.tr\/scada-yardim\/wp-json\/wp\/v2\/posts\/227","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.fultek.com.tr\/scada-yardim\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.fultek.com.tr\/scada-yardim\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.fultek.com.tr\/scada-yardim\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.fultek.com.tr\/scada-yardim\/wp-json\/wp\/v2\/comments?post=227"}],"version-history":[{"count":2,"href":"https:\/\/www.fultek.com.tr\/scada-yardim\/wp-json\/wp\/v2\/posts\/227\/revisions"}],"predecessor-version":[{"id":1060,"href":"https:\/\/www.fultek.com.tr\/scada-yardim\/wp-json\/wp\/v2\/posts\/227\/revisions\/1060"}],"wp:attachment":[{"href":"https:\/\/www.fultek.com.tr\/scada-yardim\/wp-json\/wp\/v2\/media?parent=227"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.fultek.com.tr\/scada-yardim\/wp-json\/wp\/v2\/categories?post=227"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.fultek.com.tr\/scada-yardim\/wp-json\/wp\/v2\/tags?post=227"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}