SQL – The Data Queen

Querying XML in SQL

I like to use my blog as a library for myself, to be able to easily find solutions I have used before. Here is a simple example of querying an XML string using SQL.

In this example I have inserted one XML string into a temporary table, with two items called pages, and two reference of type ‘book’. The resulting select statements will pull any and all pages from the XML string, and any ‘book’ references. There can be multiple types of references with varying fields, which you can query in the same way, simply be altering the text in the .nodes in the FROM statement.

    DECLARE @xml as XML
    DECLARE @tblXML AS TABLE  (Response xml )
INSERT INTO @tblXML
SELECT '<response>
  <pages>
    <page>
      <id>1376745</id>
      <name>First Page</name>
     </page>
    <page>
      <id>1376746</id>
      <name>Second Page</name>
     </page>  </pages>
  <references>
    <reference>
      <type>book</type>
      <id>425285</id>
      <name>How I met your mother</name>
      <activestatus>false</activestatus>
    </reference>
    <reference>
      <type>book</type>
      <id>425286</id>
      <name>Covered in Bees</name>
      <activestatus>true</activestatus>
    </reference>
  </references>
</response>' as Response


SELECT @xml = Response from @tblXML

            SELECT 
                ID        =    t.item.value('(id)[1]','int'),
                BookName    =    t.item.value('(name)[1]', 'nvarchar(50)'),
                IsActive    =    case when t.item.value('(activestatus)[1]', 'nvarchar(10)') = 'false' THEN cast(0 as bit) ELSE cast(1 as bit) END
            FROM @xml.nodes('/response/references/reference[type/text() = "book"]') as t(item)


            SELECT ID        =    t.item.value('(id)[1]','int'),
                PageName    =    t.item.value('(name)[1]', 'nvarchar(50)')
            FROM @xml.nodes('/response/pages/page') as t(item)

You can also query a table directly rather than storing the XML in a variable by using CROSS APPLY, like this.

            SELECT ID        =    t.item.value('(id)[1]','int'),
                PageName    =    t.item.value('(name)[1]', 'nvarchar(50)')
            FROM @tblXML x
            CROSS APPLY
            Response.nodes ('/response/pages/page') as t(item)

I hope you find this useful.

Report Manager Subscription Last Day of Month

I have a client who wants to create report subscriptions which run on the last day of each month. I am surprised to discover that this doesn’t come out-of-the-box with Report Manager.

The subscription schedule allows a number of options by hour, day, week, month. The Monthly Schedule option allows for Last week of month, but the user then has to choose the day of the week or calendar day. And when I insert 31 as the calendar day it gives an error message, presumably because some months do not have a 31st day. There is no option for last day of month.

Here is one workarounds to this: One option would be to run the subscription on the first day of the month, defaulting the report to the previous month. For this to be an option you would need : 1. A data parameter in the report and 2a. The report already defaults to the previous month or 2b. An edition of SQL server which supports data driven subscriptions in order to override the default parameter to last month. This is not an option for my client.

The solution that worked was a little different. You need access to be able to create a Shared Schedule on the Report Manager site, and access to the ReportServer database and SQL Server Agent on the report server. What you will do is create a Shared Schedule, which in turn creates a SQL Server Agent job. And then you will edit that job in SSMS to run on the last day of the month. Any report subscriptions can then use that Shared Schedule to run on the last day of the month. You could do something similar for each individual subscription, but why would you, when you can do it only once in a Shared Schedule.

Here are the steps:

1. Create a Shared Schedule in Report Manager

In Report Manager, go to Site Settings and select Schedules. You will need the right permissions to be able to access this. Click on New Schedule. Create a schedule. It doesn’t really matter what you set it to, since you will be changing it on the back end. Note that the front end Schedules list will reflect the changes that you make to the back end, but if you try to edit the schedule your original values will still be there. Give the schedule a descriptive name to identify it. I called it "Last Day of Month".

2. Find out the ScheduleID of the Shared Schedule you have created

Run this query in the ReportServer database to find out the ScheduleID.

SELECT ScheduleID FROM Schedule WHERE Name = 'Last Day of Month'

3. Find the SQL Server Agent Job for that ScheduleID

In SSMS navigate to SQL Server Agent and find the corresponding job.

4. Edit the properties of that job to run on the last day of the month

Make sure the schedule is Enabled.

The Shared Schedule will reflect Last Run and Next Run values correctly in the Schedules pane. Note that the details of the schedule will NOT reflect this, since it is not an available option in the Report Manager interface.

Now a user can choose this schedule when setting up a subscription.

I found this blog by Andreas Halleraker really helpful in finding a good solution.

GP Historical MultiCurrency Receivables Query

This is really specific, but I had to hunt around to find out how to report on historical receivables in Great Plains. This will get the multicurrency receivables, but you can modify it to get all receivables.

I’ve written a stored procedure which goes after a cascade of other stored procedures created for the Great Plains reporting. I chose to use a stored procedure so I can pull the data as it is provided by the GP stored proc using the variables that that stored proc requires, and then adjust the data to meet the needs of my report.

USE [yourdatabase]
GO

/****** Object:  StoredProcedure [dbo].[x_report_HistoricalReceivables_MultiCurrency]    Script Date: 5/12/2014 1:24:37 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[x_report_HistoricalReceivables_MultiCurrency]
@EndDate date
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

DECLARE @RC int
DECLARE @I_dAgingDate datetime
DECLARE @I_cStartCustomerNumber char(15)
DECLARE @I_cEndCustomerNumber char(15)
DECLARE @I_cStartCustomerName char(65)
DECLARE @I_cEndCustomerName char(65)
DECLARE @I_cStartClassID char(15)
DECLARE @I_cEndClassID char(15)
DECLARE @I_cStartSalesPersonID char(15)
DECLARE @I_cEndSalesPersonID char(15)
DECLARE @I_cStartSalesTerritory char(15)
DECLARE @I_cEndSalesTerritory char(15)
DECLARE @I_cStartShortName char(15)
DECLARE @I_cEndShortName char(15)
DECLARE @I_cStartState char(5)
DECLARE @I_cEndState char(5)
DECLARE @I_cStartZipCode char(11)
DECLARE @I_cEndZipCode char(11)
DECLARE @I_cStartPhoneNumber char(21)
DECLARE @I_cEndPhoneNumber char(21)
DECLARE @I_cStartUserDefined char(15)
DECLARE @I_cEndUserDefined char(15)
DECLARE @I_tUsingDocumentDate tinyint
DECLARE @I_dStartDate datetime
DECLARE @I_dEndDate datetime
DECLARE @I_sIncludeBalanceTypes smallint
DECLARE @I_tExcludeNoActivity tinyint
DECLARE @I_tExcludeMultiCurrency tinyint
DECLARE @I_tExcludeZeroBalanceCustomer tinyint
DECLARE @I_tExcludeFullyPaidTrxs tinyint
DECLARE @I_tExcludeCreditBalance tinyint
DECLARE @I_tExcludeUnpostedAppldCrDocs tinyint
DECLARE @I_tConsolidateNAActivity tinyint

--TODO: Set parameter values here.
Set @I_dAgingDate=  @EndDate
Set @I_cStartCustomerNumber=''
Set @I_cEndCustomerNumber='ZZZZZZZZ'
Set @I_cStartCustomerName=''
Set @I_cEndCustomerName='ZZZZZZZZ'
Set @I_cStartClassID=''
Set @I_cEndClassID='ZZZZZZZZ'
Set @I_cStartSalesPersonID=''
Set @I_cEndSalesPersonID='ZZZZZZZZ'
Set @I_cStartSalesTerritory=''
Set @I_cEndSalesTerritory='ZZZZZZZZ'
Set @I_cStartShortName=''
Set @I_cEndShortName='ZZZZZZZZ'
Set @I_cStartState=''
Set @I_cEndState='ZZZZZZZZ'
Set @I_cStartZipCode=''
Set @I_cEndZipCode='ZZZZZZZZ'
Set @I_cStartPhoneNumber=''
Set @I_cEndPhoneNumber='ZZZZZZZZ'
Set @I_cStartUserDefined=''
Set @I_cEndUserDefined='ZZZZZZZZ'
Set @I_tUsingDocumentDate=0
Set @I_dStartDate='1/1/1900'
Set @I_dEndDate=  @EndDate
Set @I_sIncludeBalanceTypes=0
Set @I_tExcludeNoActivity=1
Set @I_tExcludeMultiCurrency=0
Set @I_tExcludeZeroBalanceCustomer=1
Set @I_tExcludeFullyPaidTrxs=1
Set @I_tExcludeCreditBalance=0
Set @I_tExcludeUnpostedAppldCrDocs=1
Set @I_tConsolidateNAActivity=0

CREATE TABLE #AgedReceivables
(
APPLY_AMOUNT NUMERIC(19,5),
AGING_AMOUNT NUMERIC(19,5),
CUSTNMBR char(15),
CUSTNAME char(100),
BALNCTYP int,
USERDEF1 char(50),
CNTCPRSN char(50),
PHONE1 char(50),
SLPRSNID char(50),
SALSTERR char(50),
PYMTRMID char(50),
CRLMTAMT NUMERIC(19,5),
CRLMTPER NUMERIC(19,5),
CRLMTPAM NUMERIC(19,5),
CRLMTTYP int,
CUSTCLAS char(50),
SHRTNAME char(50),
ZIP char(50),
STATE char(50),
CUDSCRIPTN char(50),
AGNGDATE datetime,
CHCUMNUM char(50),
DOCNUMBR char(50),
RMDTYPAL char(50),
DSCRIPTN char(50),
DCURNCYID char(50),
ORTRXAMT NUMERIC(19,5),
CURTRXAM NUMERIC(19,5),
AGNGBUKT char(50),
CASHAMNT NUMERIC(19,5),
COMDLRAM char(50),
SLSAMNT NUMERIC(19,5),
COSTAMNT NUMERIC(19,5),
FRTAMNT NUMERIC(19,5),
MISCAMNT NUMERIC(19,5),
TAXAMNT NUMERIC(19,5),
DISAVAMT NUMERIC(19,5),
DDISTKNAM NUMERIC(19,5),
DWROFAMNT NUMERIC(19,5),
TRXDSCRN char(50),
DOCABREV char(50),
CHEKNMBR char(50),
DOCDATE datetime,
DUEDATE datetime,
GLPOSTDT datetime,
DISCDATE datetime,
POSTDATE datetime,
DINVPDOF datetime,
DCURRNIDX char(50),
DXCHGRATE NUMERIC(19,5),
ORCASAMT NUMERIC(19,5),
ORSLSAMT NUMERIC(19,5),
ORCSTAMT NUMERIC(19,5),
ORDAVAMT NUMERIC(19,5),
ORFRTAMT NUMERIC(19,5),
ORMISCAMT NUMERIC(19,5),
ORTAXAMT NUMERIC(19,5),
ORCTRXAM NUMERIC(19,5),
ORORGTRX NUMERIC(19,5),
DORDISTKN NUMERIC(19,5),
DORWROFAM NUMERIC(19,5),
DDENXRATE NUMERIC(19,5),
DMCTRXSTT NUMERIC(19,5),
Aging_Period_Amount NUMERIC(19,5),
APFRDCNM char(50),
APFRDCTY char(50),
FROMCURR char(50),
APTODCNM char(50),
APTODCTY char(50),
APPTOAMT NUMERIC(19,5),
ACURNCYID char(50),
DATE1 datetime,
POSTED  char(50),
ADISTKNAM  NUMERIC(19,5),
AWROFAMNT NUMERIC(19,5),
PPSAMDED NUMERIC(19,5),
GSTDSAMT NUMERIC(19,5),
ACURRNIDX char(50),
AXCHGRATE  NUMERIC(19,5),
RLGANLOS NUMERIC(19,5),
ORAPTOAM NUMERIC(19,5),
AORDISTKN NUMERIC(19,5),
AORWROFAM NUMERIC(19,5),
ADENXRATE NUMERIC(19,5),
AMCTRXSTT NUMERIC(19,5),
)

INSERT INTO #AgedReceivables
EXECUTE @RC = [dbo].[seermHATBSRSWrapper] 
   @I_dAgingDate
  ,@I_cStartCustomerNumber
  ,@I_cEndCustomerNumber
  ,@I_cStartCustomerName
  ,@I_cEndCustomerName
  ,@I_cStartClassID
  ,@I_cEndClassID
  ,@I_cStartSalesPersonID
  ,@I_cEndSalesPersonID
  ,@I_cStartSalesTerritory
  ,@I_cEndSalesTerritory
  ,@I_cStartShortName
  ,@I_cEndShortName
  ,@I_cStartState
  ,@I_cEndState
  ,@I_cStartZipCode
  ,@I_cEndZipCode
  ,@I_cStartPhoneNumber
  ,@I_cEndPhoneNumber
  ,@I_cStartUserDefined
  ,@I_cEndUserDefined
  ,@I_tUsingDocumentDate
  ,@I_dStartDate
  ,@I_dEndDate
  ,@I_sIncludeBalanceTypes
  ,@I_tExcludeNoActivity
  ,@I_tExcludeMultiCurrency
  ,@I_tExcludeZeroBalanceCustomer
  ,@I_tExcludeFullyPaidTrxs
  ,@I_tExcludeCreditBalance
  ,@I_tExcludeUnpostedAppldCrDocs
  ,@I_tConsolidateNAActivity

SELECT CUSTNMBR, DCURNCYID as CURNCYID, SUM(ORCTRXAM) as ACCTBAL, SUM(CURTRXAM) AS CADEQUIV
FROM #AgedReceivables 
WHERE 
CURTRXAM <> 0 AND
CASE WHEN DCURNCYID = '' THEN 'CAD' ELSE DCURNCYID END <> 'CAD' 
GROUP BY CUSTNMBR, DCURNCYID

DROP TABLE  #AgedReceivables
END

GO

Thanks to Mark Polino for pointing the way in his blog post – http://mpolino.com/gp/weekly-dynamic-ar-hatb-via-SQL/

Generate a Series of Months (or Numbers) in SQL

This little trick has helped me keep SQL code clean when generating data. There are many times where I need to generate a series of months in a sql query. Very often it is needed in order to join two incomplete sets of data by date, or to cross join a set of data to spread it across a series of months. This is a very simple solution which can be adapted for many other needs.

You can start by using a ‘with’ statement to generate the values you need. In this example I will generate the numbers 1 to 12 to represent 12 months.

;with Months AS
(SELECT 1 AS MonthNum
UNION ALL
SELECT MonthNum + 1 as MonthNum FROM Months WHERE MonthNum <12)
SELECT * FROM Months

This is the result set:

Let’s say you have a rates table with one record per year for each Hour Type ID. You want to duplicate this information across months. You start with a transaction like this:

and end up with a dataset like this:

You can do this with dates as well. For example:

DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @StartDate=DATEADD(month,-6, CONVERT(CHAR(10),GETDATE(),121))
SET @EndDate = GETDATE()

;with AllDates AS
(
SELECT @StartDate AS Dates
UNION ALL
SELECT DateAdd(month,1,Dates)
FROM AllDates
WHERE 
Dates<=@EndDate
)

SELECT * FROM AllDates

will result in a series of dates like this:

I hope this helps you.

Loop through dynamic SQL statements in SSIS

This is a situation where the data from a number of tables with the same structure needs to be imported into a single table. Rather than hard coding multiple data flows from source to destination, you can loop through each SQL statement using a single data flow by building a dynamic SQL statement. This simple package loops through each table name to accomplish just that. It also has a secondary loop to pivot some hard coded week numbers in the field names.

The data source is a series of tables with the same structure. Each one holds a different set of planning data. The fields have the fiscal month hard coded into the name, rather than have Week Number as an attribute.

The destination not only needs to map multiple tables to a single table, but it also needs to pivot the fiscal month weeks.

LOOP THROUGH TABLES

To accomplish this I hard coded the table names into the For Each loop, but an object variable could just as easily done this.

LOOP THROUGH FIELD NAMES (WeekNo)

VB SCRIPT TASK TO WRITE THE DYNAMIC SQL STATEMENT

In the script portion, write a simple script which uses the variables and embeds them in the SQL statement you are writing. This outputs to your SQLStatement variable to be used in the data flow.

    Public Sub Main()
        '

        Dim WeekNo As Integer
        Dim TableName As String
        Dim SQLStmt As Object

        WeekNo = Dts.Variables("WeekNo").Value
        TableName = Dts.Variables("TableName").Value
        SQLStmt = "SELECT [Version Code] as Plan_Version, [Fiscal Year] as FiscalYear,[Fiscal Month Sequence] FiscalMonth, " & WeekNo & " as FiscalWeek, convert(numeric(38,20),[SLS NET $ W" & WeekNo & "]) as Sales, FROM " & TableName
        Dts.Variables("SQLStatement").Value = SQLStmt

        Dts.TaskResult = ScriptResults.Success
    End Sub

To set up the data flow, you must first enter a valid SQL Statement in the SQLStatement variable. Your variable will then be replaced with a new one during each loop.

I hope you find this useful.