Thursday, May 5, 2016

Get Quarterly or Annual Sales with SQL Server PIVOT

A customer of mine handles transactions in their database for jewelry purchases, and today I was asked 'is there a way to know how many sales we've done each quarter, or year?'  Why yes, there is!  This is a very quick post showing a couple different ways to summarize sales or transactions, using SQL Server's PIVOT operator.  

This first example is 'static', where I have to tell SQL which values I am looking for.  In this case, the quarters for which I want the sale counts:

     SELECT
         [Year],
         [1] Qtr1,
         [2] Qtr2,
         [3] Qtr3,
         [4] Qtr4
     FROM (
           SELECT YEAR([PostingDate]) [Year],
           DATEPART(QUARTER, [PostingDate]) [Quarter]
           FROM dbo.YourTable
        ) SourceTable
     PIVOT(COUNT([Quarter])

     FOR [Quarter] IN ([1], [2], [3], [4])) PivotTable




As you can see, our rows come back as columns for each of the quarters in my query.  It's 'static' because this requires you to manually input the values you wish to query.  All very nice, but what if you don't know exactly what's in the table?

This next example does not require you to know, or input your values ahead of time.  In this case, the annual transactions, but I don't know exactly which years of data are in the table.  This is a 'dynamic' pivot:

     DECLARE
          @pivotquery NVARCHAR(MAX),
          @column NVARCHAR(MAX)

     SELECT YEAR([Posting Date]) [Year], COUNT(1) [Transaction Count] 
     INTO #Transactions
     FROM dbo.YourTable WITH (NOLOCK) ---- NOLOCK maybe necessary 
     GROUP BY YEAR([Posting Date])

     -- distinct values for pivot colunn
     SELECT @column = ISNULL(@column + ',','') + QUOTENAME([Year])
     FROM (SELECT DISTINCT [Year] FROM #Transactions) Years

     -- now collect the @columns via dynamic sql
     SET @pivotquery = 'SELECT ' + @column + '
                        FROM #transactions
                        PIVOT(SUM( [Transaction Count]   )
                        FOR [Year] IN (' + @column + ')) AS PVTTable'
     -- execute your query
     EXEC sp_executesql @pivotquery










In many cases, I think that 2nd approach may be more useful, because you don't have to know your queried values ahead of time.

Try it out yourself and let me know what you think!  I believe you should also take a look a this for a little more information regarding the use of PIVOT:




Friday, April 1, 2016

"How do I know if the database is configured for SQL Server Replication?"

I answered that question for one of my customers today, and thought I'd share it here as well. Just a quick post, I've given you a couple different ways to verify whether a database is configured for SQL Server Replication.  Hope it helps!

 -- just a select on any of the replication flags      
 SELECT
    name [Database]
 FROM
    sys.databases
 WHERE
    databases.NAME = DB_NAME()
    AND databases.is_published = 1
    OR databases.is_merge_published = 1     
    OR databases.is_subscribed = 1
    OR databases.is_distributor = 1

You will see this output if a distribution database exists:











This next one is what I use to return the status of all user databases on an instance, indicating whether any are used in SQL Server Replication.

 /* check if any db is used in replication */
 SELECT
   name [Database],
   CASE is_published WHEN 0 THEN 'No' ELSE 'Yes' END [IsPublished],
   CASE is_merge_published WHEN 0 THEN 'No' ELSE 'Yes' END [IsMergePublished],
   CASE is_distributor WHEN 0 THEN 'No' ELSE 'Yes' END [IsDistributor],
   CASE is_subscribed WHEN 0 THEN 'No' ELSE 'Yes' END [IsSubscribed]
 FROM
   sys.databases
 WHERE
   database_id > 4

Here we see the cpriogbp is being published, and the distributor database is shown as well.














That's it!  See these for much ore detail regarding Microsoft SQL Server Replication:




Wednesday, March 16, 2016

Identify all SQL Server tables that have XML columns

Managing the XML data type, and manipulating XML data in SQL Server is a tremendous topic.  Or a 'whole new can of worms', per se.  But, it can be done.  This post only skims the surface, but it's very simple method of listing all of the tables and columns in your database that are the XML data type.  Super easy, but very helpful as well.  I used it myself just today.

   USE yourDBname;
   SELECT
         TABLE_CATALOG [Database],
         TABLE_NAME [Table],
         COLUMN_NAME [Column]
   FROM
         INFORMATION_SCHEMA.COLUMNS
   WHERE
         DATA_TYPE = 'XML'


Your results:













As I said, it only skims the surface, but it's super quick, super easy, and it works.  I'll try to get back here soon with some more involved details on the XML datatypes and manipulations with SQL Server.  Until then....

More details regarding the SQL Server XML data type:

Parse a delimited list into multiple records with tSQL

This is great!  I have always used a table-valued function to go in and split a delimited string into multiple values.  Not a bad method, but I learned something today that is much easier!! Check it out:

   -- table variable for the delimited strings
   DECLARE @source TABLE (ConstituentID INT, ParishByConCodes VARCHAR(255))
   INSERT @source (ConstituentID,ParishByConCodes)
   VALUES (123,Null),
          (234,'0101'),
          (234,'0201;0301'),
          (567,'0401;0501;0691'),;

Now take a look at what we just wrote: 

   SELECT * FROM @source











Good, good... Now let's split each of those Parish codes out into separate values. Comment out the SELECT from @source, and add this in there, directly after the insert into the table variable:

   SELECT
      ConstituentID,
      LTRIM(RTRIM(a.b.value('.[1]','VARCHAR(255)'))) Parish
   FROM
     (
      SELECT ConstituentID,CAST('<XMLRoot><RowData>' +   
      REPLACE(ParishByConCodes,';','</RowData><RowData>') + '</RowData></XMLRoot>' AS XML) x
      FROM  @source)t CROSS APPLY x.nodes('/XMLRoot/RowData')a(b)

Run the whole statement again, and look at our results:













Isn't that the best?!  I certainly think so.  Kudos to Pinal Dave at    http://blog.sqlauthority.com/.

Sunday, March 6, 2016

Recover lost SQL Server SSMS scripts

This is a good one.  I'm working a problem earlier today in SSMS, and I didn't close a WHILE LOOP properly.  Rather than telling me this, SQL just runs, and runs, and runs... I couldn't kill it. I couldn't cut/paste/copy my code. I couldn't do anything. So I take a screenshot and kill it in Task Mgr. But - it's not just my bad code.  I lose my entire SSMS session.  Crap.  

I remember seeing something like this before, and I check this directory:



Nadda.  Then I check this one:



Again, nothing. I put a lot of time into this; even w/the bad WHILE LOOP, I still wanted my code! Then it hit me --- the cache! I've worked with sys.dm_exec_query_stats recently, which contains the cached plans. Wouldn't it be in there?  Yep!!  It was, and I was able to scrape my code back together. SSMS crashes, or you close it too quickly without saving your code.  You can get it back!

     USE DBA; --- change this to your database
     SELECT
        execquery.last_execution_time [Date],
        execsql.text [Script]
     FROM
        sys.dm_exec_query_stats AS execquery
           CROSS APPLY sys.dm_exec_sql_text(execquery.sql_handle) execsql
     ORDER BY
        execquery.last_execution_time DESC  

Your results will return any statements that have been cached, like this:

Not bad at all.  This DMV is very useful for query plan analysis and performance troubleshooting.  Definitely worth a look.

How to dis-aggregate summarized data in SQL Server ?

Now that is just weird to me.  Why would you want to 'dis-aggregate' data? Who wants to look at the details?  If you're not an accountant, I mean... For example, instead of this:







You want to see this:











Turns out it's fairly common to need to see the details behind a summarized report. This is a quick example for dis-aggregating data. 

-- load your summarized data
CREATE TABLE #aggregated (
  [filename] VARCHAR(25),downloads INT,downloaded date,data1 VARCHAR(15),
  data2 VARCHAR(15),data3 VARCHAR(15),processed bit
)
INSERT #aggregated ([filename],downloads,downloaded,data1,data2,data3,processed)
VALUES
       ('filename1.txt',5,'11/15/2015','md1','md2','md3',0),
       ('filename1.txt',79,'12/31/2015','md1','md2','md3',0),
       ('filename1.txt',24,'1/15/2016','md1','md2','md3',0),
       ('filename2.xlsx',49,'10/15/2015','md5','string','string2',0),
       ('filename2.xlsx',25,'2/15/2016','md6','string','string3',0),
       ('filename2.xlsx',39,'12/15/2015','md7','string','string4',0),
       ('filename2.xlsx',8,'1/15/2016','md','string','string5',0);

-- table for the disaggregated details
CREATE TABLE #disaggregated (
  [filename] VARCHAR(25),[action] CHAR(8),downloaded date,data1 VARCHAR(15),
  data2 VARCHAR(15), data3 VARCHAR(15)
)

-- set based approach (rather than cursor)
WHILE EXISTS(SELECT 1 FROM #aggregated WHERE processed = 0)
BEGIN
   DECLARE
      @next INT = (SELECT TOP 1 downloads FROM #aggregated WHERE processed = 0),
      @count INT = 0
      
      WHILE @count < @next
      BEGIN
         INSERT #disaggregated ([filename],[action],downloaded,data1,data2,data3)
         SELECT [filename],'download',downloaded,data1,data2,data3
         FROM #aggregated
         WHERE downloads = @next

         SELECT @count = @count + 1
     END

     UPDATE #aggregated SET processed = 1 WHERE downloads = @next
END

Take a look at the details in your your #disaggregated table:
















Or just run this to see that the count of what we just disaggregated corresponds to the numeric 'downloads' value in the aggregated data set.

--  query to show the counts of what was inserted
SELECT a.[filename],a.downloads,count(b.[action]) [details]
FROM #aggregated a INNER JOIN #disaggregated b
  ON a.[filename] = b.[filename]
  AND a.downloaded = b.downloaded
  AND a.data1 = b.data1
  AND a.data2 = b.data2
  AND a.data3 = b.data3
GROUP BY
  a.[filename],
  a.downloads

Should return this:











Hope it helps!  Let me know what you think.

Thursday, February 18, 2016

How to use wildcards with the SQL Server LIKE operator

I answered a question today in one of my groups... a whole lot of LIKE operator and wildcard conditions. Great subject matter! This is just a few different tips for using SQL's LIKE operator with wildcards.  Be sure to look at the data you're loading into the variable, and read my comments for each of the sample statements.  

   /*   table variable */
   DECLARE @DBVersions TABLE (
       DatabaseVersion VARCHAR(25) NOT NULL,
       VersionDate DATETIME NOT NULL,
       ModifiedDate DATETIME NOT NULL )

   /*  load some test data  */
   INSERT @DBVersions (DatabaseVersion,VersionDate,ModifiedDate)
   VALUES ('ssAsxb567','2016-01-14','2015-03-14'),
          ('ss%sxb567','2016-01-14','2015-03-14'),
          ('ABXB23','2016-01-14','2015-03-14'),
          ('aBxb234','2016-01-14','2015-03-14');

   /* just look at the raw data  */
   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions

   /*
     7 different conditions with wildcard
   #1 return anything that contains OR begins with the letter 'A'
   #2 return anything beginning with 'A', but does not contain it elsewhere
   #3 return anything that contains the letter 'A', but does not begin with it
   #4 return anything LIKE the given DatabaseVersion, but is in ALL CAPS
   #5 return anything that LIKE the given DatabaseVersion, regardless of CASE
   #6 return anything that has 3 digits the given DatabaseVersion
   #7/8 return anything that has the % literally within the DatabaseVersion      */

   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions
   WHERE DatabaseVersion LIKE '%A%'

   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions
   WHERE DatabaseVersion LIKE 'A%'

   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions
   WHERE DatabaseVersion LIKE '%A%'
   AND DatabaseVersion NOT LIKE 'A%'

   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions
   WHERE DatabaseVersion LIKE 'ABX%' COLLATE Latin1_General_CS_AS; --only UPPERCASE

   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions
   WHERE DatabaseVersion LIKE 'ABX%' -- you'll see we get them both back on this one

   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions
   WHERE DatabaseVersion LIKE 'ABX___' -- now only one
 
   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions
   WHERE (DatabaseVersion) LIKE '%' -- why do we get them all?!

   SELECT DatabaseVersion,VersionDate,ModifiedDate FROM @DBVersions
   WHERE (DatabaseVersion) LIKE '%[%]%' -- use the brackets as an escape character
                                        -- first and last %s are the WILDCARD
                                        -- the middle one is treated as a literal
                                        -- good stuff  

That's all I've got for now, but I may add more later. Take a look at this for more information, and more examples: