Godlike Productions

Godlike ProductionsGodlike Productions
  • Dark Heaven Studio
    • Dark Heaven Studio
    • Music
      • The Bands
      • Times of The Sines
        • Singles
        • Man v Machine
        • The First Sign
      • LD 120
        • Albums
        • Wise Words
        • Consume
        • Slowdive
        • The Citadel of Fear (Live)
        • EP's
        • Trancefer
        • Singles
        • The Mall
        • Bitted
        • Atlas My Mark
        • Featured On
        • Semikazi Compilation 2
        • So Who Likes Fashion Anyhow
        • Remixes
        • Lyrics
          • Cliques
          • Atlas My Mark
          • Shadow of Death
      • The X Lander
        • Albums
        • Waiting In The Wings
        • Featured On
        • Unholy Kings On A Black Horizon
      • LOST
        • Albums
        • In Translation
        • Singles
        • Lifeforms
        • Shattered
        • Lyrics
          • Shattered
      • Schlerb
      • Sensory Overload
      • The Studio
      • Dark Heaven Studio
      • Podcast
      • Podcast Help
      • Soundcloud
      • Weekly Beats
    • Software
      • PC3 Envelope Controller
      • Jupiter 8 Morph
      • Requiem Roller
      • PC3 Bank Decoder
    • Synth Support
      • Kurzweil
      • Yamaha
      • mLAN
        • mLAN Resources
        • mLAN Chipsets
      • Studio Connections
      • 8PortSE
      • Max 4 Live
    • Sounds and Patches
      • Bravo
      • LiquidT
      • KSP8
    • News
      • Times of The Sines - The First Sign Released
      • Times of The Sines - Golden Door released through Lightarmour Editions
      • Kurzweil RSP8 Studio Connections Module Released
      • Times of the Sines Joins Lightarmour Editions
      • LD 120 - Melt Through The Mobius of Time Video
      • Times of the Sines Man v Machine Released
      • The X Lander - Waiting In The Wings
      • Times of the Sines - Pulsecodes Video
      • LOST Performs at Noisemachine Virtual Gig
      • LOST releases Shattered
      • Times of the Sines - Live at Psychedelicious
      • Times of the Sines - Live at Good Shepherd
      • Times of the Sines - Live at The Artifactory
      • Mikro Tutorials
        • Mikro Tutorial #10 Released
        • Mikro Tutorial #9 Released
        • Mikro Tutorial #8 Released
        • Mikro Tutorial #7 Released
        • Mikro Tutorial #6 Released
        • Mikro Tutorial #5 Released
        • Mikro Tutorial #4 Released
        • Mikro Tutorial #3 Released
        • Mikro Tutorial #2 Released
        • Mikro Tutorial #1 Released
      • Programming With The Puppeteer
        • PWP1 - Virtual Analog
        • PWP2 - Pads
        • PWP3 - PWM
      • Video Tutorial
        • Tutorial 1 - PC3 101
        • Tutorial 2 - Effects
        • Tutorial 3 - Envelopes
        • Tutorial 4 - Controllers
        • Tutorial 5 - MIDI Sync
        • Tutorial 6 - FUNS
        • Tutorial 7 - Riffs
        • Tutorial 8 - Arps
        • Tutorial 9 - MIDI Sync 2
        • Tutorial 10 - Wrap
        • Tutorial 11 - Live Mode
        • Tutorial 13 - Forte and Deluge
    • Forums
  • Forums
  • Picture Framing
    • Glass Cutting
  • Tech Tools
    • Wonderware Historian Current Date
    • SMF Downloads Pro php 7 Fix
    • Just News Template
    • Missing Lightbox Buttons
    • Image Spacing
    • nbo_podcast for External Servers
    • tt_news Formatting
    • Typo3 Page Statistics
    • Direct Mail Categories
    • Pond Volumes
Tech Tools » Wonderware Historian Current Date
    • Dark Heaven Studio
    • Forums
    • Picture Framing
    • Tech Tools
      • Wonderware Historian Current Date
      • SMF Downloads Pro php 7 Fix
      • Just News Template
      • Missing Lightbox Buttons
      • Image Spacing
      • nbo_podcast for External Servers
      • tt_news Formatting
      • Typo3 Page Statistics
      • Direct Mail Categories
      • Pond Volumes

Wonderware Historian Current Date


WideHistory Query to Current Date in Wonderware Historian

Querying stuff in narrow format for Wonderware Historian is easier, but the formatting sucks, as you need to select your tag or description and effectively filter out the tag you are searching for, from a long list.

In PowerBI, this just plain sucks, as you essentially need to duplicate columns the filter them for each tag you are searching for.

Wide format is much nicer, it gives you DateTime as your rowid's and a tag per column.

The only problem is that you pull the wide format you need to use OpenQuery, which essentially means you need to provide a string to parse via ODBC. Which is all good and all, until you need to do a dynamic query.

Why would you need this. Let say you want to query all the data from a certain date, until now, or the results from the last 48 hours, so you can provide a daily report.  You know, all the most common tasks that recent data is important for.

Fortunately, there is a function in the wonderware SQL database to acheive this, that can be quoted.  The important bit is getdate(). It's case sensitive (ie the old GetDate() doesn't work)

Here's my query - that works.  I've anonymized the tags. Mine won't be any use to you, unless you work on my site, in which case, come and find me.

 

SET QUOTED_IDENTIFIER OFF
SELECT * FROM OPENQUERY(INSQL, "SELECT DateTime = convert(nvarchar, DateTime, 21), [Site.TAG1_PV], [Site.TAG2_PV], [Site.TAG3_PV], [Site.TAG4_PV], [Site.TAG5_PV], [Site.TAG6_PV], wwResolution
 FROM WideHistory
 WHERE wwRetrievalMode = 'Cyclic'
 AND wwResolution = 60000
 AND wwQualityRule = 'Extended'
 AND wwVersion = 'Latest'
 AND DateTime >= '20200910 12:00:00.000'
 AND DateTime <= getdate()
")

 

For completeness, here's the narrow version of the same query.

 

SET NOCOUNT ON
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = '20200910 00:00:00.000'
SET @EndDate =  CONVERT(date,GetDate()+1)
SET NOCOUNT OFF
SELECT temp.TagName ,Description ,DateTime ,Value ,vValue ,Unit = ISNULL(Cast(EngineeringUnit.Unit as nVarChar(20)),'N/A') ,StartDateTime From (
SELECT  * 
 FROM History
 WHERE History.TagName IN ('Site.TAG1_PV', 'Site.TAG2_PV', 'Site.TAG3_PV', 'Site.TAG4_PV', 'Site.TAG5_PV', 'Site.TAG6_PV')
 AND wwRetrievalMode = 'Cyclic'
 AND wwResolution = 60000
 AND wwQualityRule = 'Extended'
 AND wwVersion = 'Latest'
 AND DateTime >= @StartDate
 AND DateTime <= @EndDate) temp
LEFT JOIN Tag ON Tag.TagName =temp.TagName
LEFT JOIN AnalogTag ON AnalogTag.TagName =temp.TagName
LEFT JOIN EngineeringUnit ON AnalogTag.EUKey = EngineeringUnit.EUKey
 WHERE temp.StartDateTime >= @StartDate

 

Oh - Added bonus.  The Wide query runs significantly faster than the narrow version.  Each row effectively runs as a query.  In my case there are 1/6 as many rows to query. Therefore 1/6 the time.


  • © 1997 -2021 Godlike Productions. All Rights Reserved
  • |
  • About Us
  • |
  • Contact
  • |
  • Privacy Policy
  • |
  • Site Map
Godlike Productions is on Facebook Godlike Productions is on Twitter Godlike Productions is on Soundcloud Godlike Productions is on Youtube Godlike Productions is on Bandcamp