My kids and I love tongue twisters. We say tongue twister loudly in the car and they giggle and laugh loudly. I love to hear them giggle and laugh.
As I work full time as a SQL developer, I write and read SQL code much more often than when I was a SQL blogger. When I see SQL some code, I think: this is good. But, can you find a better way? Furthermore, what is the best way? These questions tickle my brain and when I find some answer, my brain giggles and laughs. I love that feeling.
Today, I want to share two SQL brain twisters (that comes from my work) with you.
Suppose there is a table called Bill_Table, with 4 columns, Charge_Bill_ID, Charge_Bill_Year, Charge_Bill_Month, Charge_Bill_Amount. This is applicable to monthly bill, like credit car, utility bill, daycare charge… … With sql, how to you show past 12 month of charge. Wait, if today is between 1st and 9th of the month, then, the list should end with last month for 12 month. If today is between 10 to end of this month, the list should end with this month.
This is the good SQL
SELECT * FROM Bill_Table WHERE (Charge_Bill_Year*100+Charge_Bill_Month BETWEEN CASE WHEN DAY(GETDATE())<10 ANS MONTH(GETDATE())=1 THEN (YEAR(GETDATE())-2*100+12 WHEN DAY(GETDATE())<10 ANS MONTH(GETDATE())>1 THEN (YEAR(GETDATE())-1*100+Mont(GETDATE()) -1 ELSE (YEAR(GETDATE())-1)*100+Mont(GETDATE()) END AND CASE WHEN DAY(GETDATE())<10 ANS MONTH(GETDATE())=1 THEN (YEAR(GETDATE())-1*100+11 WHEN DAY(GETDATE())<10 ANS MONTH(GETDATE())=2 THEN (YEAR(GETDATE())-1*100+12 WHEN DAY(GETDATE())<10 ANS MONTH(GETDATE())>2 THEN (YEAR(GETDATE())*100+MONTH(GETDATE())-2 WHEN DAY(GETDATE())>=10 ANS MONTH(GETDATE())=1 THEN (YEAR(GETDATE())-1*100+12 ELSE (YEAR(GETDATE()))*100+Month(GETDATE())-1 END)
I admire this where clause because it is very logical and solve the problem perfectly.
I came up with a short Where clause, and a parameter way to choose past 12 month (12, 0), with @StartPastMonth =12, and @EndPastMonth=0
Where DATEFROMPARTS(Charge_Bill_Year, Charge_Bill_Month, 10) BETWEEN DATEADD(MONTH, (-1)*@StartPastMonth, GETDATE()) and DATEADD(MONTH, (-1)*@EndPastMonth, GETDATE())
I consider this is a better SQL because it is easier to read and understand. But, it is not the best one because it runs very slow. This where clause can not utilize any index or seek, the functions at both side make the scan row by row and the query runs very slow.
Would you please twist your brain, and come up with the best SQL?
How do you merge select result into one set side by side.
When we mention Merge in SQL, it is merge two select results into one but with same column number. The row number increase by sum the results sets (UNION ALL). However, my situation need the result set side by side, wheich means column number increase but row number same.
I have not test my idea yet. I am thinking to add column at set_ID, then Merge Sets as normal and then PIVOT with Set_ID. I will write another post to unveil the code (TWIST: my code will be for DB2, not for SQL SERVER, since this project of mine is a DB2 database. Yeah!!)
Bonus 1: I find the answer to a error message of SSIS dataflow, when EXCEL is used as either data source or destination. Data Conversion, then change mapping. I might write about it later too.
Bonus 2: Starting from today, the SQL code in my post looks very professional because I used SyntaxHighlighter Evolved plugin. If you want the code examples in your blog look professional, read this web page and try to use this plugin too. I think you will like the looks.