Pass multiple values to case when expression

There may be a time when you want to pass multiple values to a CASE WHEN expression. Here’s an example, take this simple data set below:

CREATE TABLE [Test]
([UserID]   INT, 
 [Approval] VARCHAR(10)
);

INSERT INTO [Test]
VALUES
       (1, 
        'Reject'),
       (2, 
        'Approve'),
       (3, 
        'test'),
       (4, 
        'test2'),
       (5, 
        'Approve'),
       (6, 
        'Reject');

SELECT 
       *
FROM [Test];
Our base data set we will use as an example.

Let’s say I wanted to pass a list of UserIDs to a stored procedure that is using a CASE WHEN statement to update our Test table. I want to update the column Approval and I want to set the statuses of <Reject> to <Rejected> and <Approve> to <Approved> when I pass the [UserID] and [Approval] column to the stored procedure. If I pass a UserID and a value not <Approve> or <Reject>, it should be ignored.

Continue reading “Pass multiple values to case when expression”

T-SQL Tuesday #114 Puzzle Party

This months T-SQL Tuesday comes from Matthew McGiffen who has given us the topic of “Puzzles”, thank you for a great topic!

Puzzles can be fun or frustrating, but I like them because it can be a great way to showcase who you are and how you work. For example, I was given a puzzle from a company that was interested in me but wanted to know how I approached specific problems. They gave me a simple SQL query and three word problems to go along with it. My reply back illustrated how I approach problems, document findings, performance test, and also work to obtain business requirements.

I gave them back a long paper that broke down the primary problem into nine different scenarios for how to interpret their question and I wrote a total of 35 queries spanning all nine scenarios to pick the best option. This included a procedure they could run to test these scenarios and see my work.

You can find the procedure here on my github, you will need to alter line 56 with the options 1-9 to test a scenario.

Continue reading “T-SQL Tuesday #114 Puzzle Party”

Identify and resolve “Adding a value to a ‘date’ column caused an overflow”

This is similar to my other post on how to identify conversion problems, but here’s the catch, we don’t always get a “CASE WHEN” statement or “TRY-CONVERT / CAST” function to help us! This makes it a little bit tricky to identify your problem rows, but we’ll tackle that together.

Continue reading “Identify and resolve “Adding a value to a ‘date’ column caused an overflow””

T-SQL Tuesday #112 Dipping into my Cookie Jar

This month Shane asks us to reach into our “cookie jar” and pull out some life lessons. Thank you for an interesting topic to think and write about!

Shane also describes for us what this term means in our context:

Dipping into the Cookie Jar is about when the going gets tough and you don’t think you can handle anymore, then you think back about your accomplishments and take some sustenance from them. You dip back into that cookie jar and use whatever energy that provides to keep going.

https://nocolumnname.blog/2019/03/05/t-sql-tuesday-112-dipping-into-your-cookie-jar/
Continue reading “T-SQL Tuesday #112 Dipping into my Cookie Jar”

Identify and resolve “Conversion failed when converting the varchar value ‘Something’ to data type int”

If you are seeing an error similar to “Conversion failed when converting the varchar value ‘something’ to data type int.”, I find the easiest method for identifying a solution to the problem is to analyze the data causing the error.

Continue reading “Identify and resolve “Conversion failed when converting the varchar value ‘Something’ to data type int””

Properly reference your columns!

Wait, I should always what?!

If you don’t read the rest of this setup, I want you to take away one thing.

Always reference your tables with your columns when more than one table is involved in the query!

This post is made primarily with SQL Server in mind, but this behavior is actually ANSI SQL and can be replicated in PostgreSQL, MySQL, and Oracle.

Continue reading “Properly reference your columns!”

2018 Wrap Up!

I wanted to take a moment and reflect on 2018, it was an incredibly successful year for me and I want to thank everyone who has helped make that possible. The SQL community is always responsive and helpful, my co-workers are wonderful, and of course; my family and friends are always supportive and understanding. So thank you everyone for helping me hit my goals!

Continue reading “2018 Wrap Up!”

T-SQL Tuesday #110 Automate your Toggl dataset with SSIS

Something I’ve automated* recently was my import of Toggl data! So T-SQL Tuesday #110 seems like a great time to post this solution. Thank you Garry Bargsley for the great idea and being this months host! I put an asterisks next to automated, as obtaining the file from Toggl and placing its location is not … Continue reading “T-SQL Tuesday #110 Automate your Toggl dataset with SSIS”

Something I’ve automated* recently was my import of Toggl data! So T-SQL Tuesday #110 seems like a great time to post this solution. Thank you Garry Bargsley for the great idea and being this months host!

I put an asterisks next to automated, as obtaining the file from Toggl and placing its location is not automatic. They provide a premium feature to allow this or you could work on creating your own web scraper. (Which is probably against their rules of use on their site, since they are offering the file drop as a premium feature.)

So while that piece is not automatic, the ingestion, cleanup, and archive of data from the Toggl file is. Generally when I do automate a task, I will use a combination of Powershell, SSIS, BIML, Python, T-SQL, Bash, and whatever else may be necessary to get the job done.

Steve Jones got me hooked on Toggl back in July and I found it pretty easy to use. Since then I’ve begun creating analytics on tasks at my job and also in my life. I log my study time, blog work, and other extra curricular so I can begin to cultivate a data set about how I use my time.

Another thought, is that you can use Toggl with analytics to give your stakeholders insight into how your time is spent. Maybe you need to be left out of meetings because 25% of your days is spent in them? Maybe you have been doing too many projects for Todd from accounting? This gives your stakeholders a true insight into your day using tools to easily capture and identify your pain points which can be optimized.

Depending on your Toggl setup, you may not want to be very detailed with the information you provide. I personally normalize my data which is input into Toggl so it’s meaningless numbers without my master map which resides on my local computer. I want to keep my clients data secure and this information is hosted on Toggl’s servers. So that’s some food for thought, be mindful of what you are inputting into this online tool and what you’ve signed as far as Non-disclosure agreements.

So onto the SSIS package!

DISCLAIMER!!!

This should be done in a personal or safe environment, never production!

Created with Visual Studios 2017 Version 15.8.5, .NET Framework V 4.7.03056.

DBMS is Microsoft SQL Server 2017 (RTM) – 14.0.1000.169 (X64)

What does this SSIS package do? Well very simply, you export your detailed year view of Toggl (which is typically a pretty small data set, at least from my perspective), place it in a location, set your SSIS parameter for the folder location, create a new database or use an existing one and set it in the project connections, and let it rip.

Here is how you get your data from Toggl:

The home page for Toggl, click the Go to timer button at the top right.

Once you arrive at Timer, you can click on Reports to the top left corner.
Once at Reports, click Detailed at the top of the page towards the left, mosey over to the date drop down option on the right hand side and select "This year" or "Last year" depending on the data set you need to import, then hover over Export just below the year setting and select "Download CSV"
Once at Reports, click Detailed at the top of the page towards the left, mosey over to the date drop down option on the right hand side and select “This year” or “Last year” depending on the data set you need to import, then hover over Export just below the year setting and select “Download CSV”

Here is the final destination of the file I downloaded. You can place the file pretty much anywhere, just notate the location of the file for later.
Here is the final destination of the file I downloaded. You can place the file pretty much anywhere, just notate the location of the file for later.

Here is a link to my Github Project: https://github.com/Jonathanshaulis/Toggl_Import , so you can either fork or download the SSIS files. Place them somewhere on your computer where you want to store SSIS packages.

Once the files are downloaded and stored, you can open up the Solution file by double-clicking it. (Note: You will need Visual Studios installed along with SSDT including SSIS.)

Once the files are downloaded and stored, you can open up the Solution file by double-clicking it. (Note: You will need Visual Studios installed along with SSDT including SSIS.)

Explanation of the SSIS package

The package starts by checking for the existence of the archive and staging schemas, it will create them if they don’t exist. It will then do the same for the tables, we have the primary table we use, the staging table, and the archive table for when you have years of data stockpiled over time.

We’ll truncate staging and then load the staging table immediately after. Using the base data set from Toggl, I found it difficult to analyze the summed durations, so I create a new column on import that takes the duration and turns it into a decimal. This made it more convenient for me to use Power BI to report off of.

Also in the Data Flow (DFT – File to Staging) we have two file counts occurring, one counts errors and the other counts records that flowed to the destination.

Errors are logged to DFTStagingCountErr and the successful imports are logged to DFTStagingCount.

Afterward, we gather a count of the rows in the staging table and assign the count to the variable ESTStagingCount.

There is a condition check after the count that will stop the process if the two variables DFTStagingCountErr and DFTStagingCount added together do not match the count of the variable ESTStagingCount. This is important because it ensures a successful import has occurred before it continues onward and truncates our primary table to perform a full load.

Once we are sure all records made it to our staging table, we truncate our destination and move our file to our “production” table. This gives a complete refresh of the data for the year, but this also serves a conundrum.

What happens when we have two years of data?

  • The final step archives the data set that you’ve imported.
  • The query performs a “merge upsert” with a not matched deletion at the end.
  • If a row matches exactly between the archive table and production table, it will update the Toggl_ID to keep the latest version of that row.
  • If a row is unmatched on all columns, it will interpret that as a new row and insert it.
  • If a row is unmatched on all columns and the date of the row matches the max date found in the production table, it will delete out the row. This logic is to allow for data changes to flow to the archive table making the hosted Toggl data source the golden data source. This means rows in 2018 would not be deleted from the archive source once you import a 2019 data set, but it would delete out a 2019 row if you changed the data and it would import the newly corrected row.

So how do I run it when I have it open?

The SSIS package is open here with two markers, #1 and #2. Down below I give details about how to adjust these two pieces in the SSIS package. After they have been adjusted, you can run the package assuming all other steps were accomplished.
The SSIS package is open here with two markers, #1 and #2. Down below I give details about how to adjust these two pieces in the SSIS package. After they have been adjusted, you can run the package assuming all other steps were accomplished.
#1 This is the properties of the Connection Manager (project)Local.
#1 This is the properties of the Connection Manager (project)Local.

You’ll want to edit the Local connection manager to point to your instance. I would recommend to create a new database, something like “Career”. (I will be uploading more blog posts regarding Brent Ozar’s Salary Survey and creating a database portfolio in SSIS using the Career DB later.)

#2 This is the parameters screen to adjust the file location.
#2 This is the parameters screen to adjust the file location.

The last modification you need to make is to adjust the value for the Parameters Property File_Location. It doesn’t matter if you include the final backslash or not at the end of the string, I have error handling in an expression to make sure it is able to take either variation. Just make sure your file is in that location!

Voila! That’s it!

  1. Download SSIS package / Toggl File.
  2. Place SSIS package and Toggl File in ideal locations.
  3. Open SQL Server, create a new Database called Career or anything else you choose, or just use an already existing database.
  4. Open the SSIS package, adjust the Local connection to your instance and database.
  5. Edit the Parameter for the File_Location and point it to your file.

F.A.Q. and Troubleshooting

If you have questions or run into issues, please let me know and I’ll see what I can do to help troubleshoot. I’ll modify this section as time goes on and as changes are needed.