Recent Discussions
SQL Help : Time difference for events within a specific group that resets based on an anchor event
Hi All, Need help with an SQL query to find the time difference between events that happen in sequence, where an anchor event resets the calculation. Below is the sample data and the expected output. thanks
Error 0x80004005 during SSIS package execution with SSAS source
Hi. I've created a SSIS package utilizing a SSAS source (VS 2022 Developer Edition, SSAS 2019 15.0.35.48). I can confirm that I can connect to my SSAS source through OLE DB connector and run MDX queries: When I run the package, "Connection Test" step passes (it runs a simple MDX query against the SSAS source to ensure we can connect to source): But data flow task fails on OLE DB Source as follows: My problem is that there is nothing in error message which can help me where is the exact problem. I've seen many posts on this problem, but none of them has worked for me as each focus on a part of error message following the above message, which doesn't exist in my case (e.g. "Communication link failure" or "TCP Provider: The specified network name is no longer available"). Please help. Thanks in advance.
compare cell 2 with cell 1 and show result
Dear All, I need your help. I have below sample data and want to compare Cell F (column Snap_Id) with previous row value. e.g. row_num 2 should compare with row_num 1 and if it is matching it should say "No" and if it is not matching like row_num 3 compare with row_num 2 then it should say "Yes". Can you please help me to write SQL statement to achieve it. I tried self join but then it is returning me 12 records with incorrect data. The final output I am expecting is as below, Hope you can help me with this. Thanks in advance. Regards, Abhishek
Make Organization
I have a table with organizational structure. It has an ID column called ID. The table also has a column called MOTHER_ID. This refers to the level (MOR) above in the organizational structure. It also has a row that says which level in the organization the row belongs to. We can have 6 levels. Table looks like this: ID Name MOTHER_ID Level 10020027 Level 1 name NULL 1 10020028 Level 2 name 10020027 2 10020032 Level 3 Name 10020028 3 10020035 Level 4 Name 10020032 4 10020166 Level 5 Name 10020035 5 10020038 Level 6 Name 10020166 6 10020083 Level 3 Name 10020028 3 10020188 Level 4 Name 10020083 4 I want to "flatten it out" so it has this structure: Level 1 ID Level 1 Name Level 2 ID Level 2 Name Level 3 ID Level 3 Name Level 4 ID Level 4 Name Level 5 ID Level 5 Name Level 6 ID Level 6 Name And the result should be like this: Level 1 ID Level 1 Name Level 2 ID Level 2 Name Level 3 ID Level 3 Name Level 4 ID Level 4 Name Level 5 ID Level 5 Name Level 6 ID Level 6 Name 10020027 Level 1 name 10020027 Level 1 name 10020027 Level 1 name 10020027 Level 1 name 10020027 Level 1 name 10020027 Level 1 name 10020027 Level 1 name 10020028 Level 2 name 10020028 Level 2 name 10020028 Level 2 name 10020028 Level 2 name 10020028 Level 2 name 10020027 Level 1 name 10020028 Level 2 name 10020032 Level 3 Name 10020032 Level 3 Name 10020032 Level 3 Name 10020032 Level 3 Name 10020027 Level 1 name 10020028 Level 2 name 10020032 Level 3 Name 10020035 Level 4 Name 10020035 Level 4 Name 10020035 Level 4 Name 10020027 Level 1 name 10020028 Level 2 name 10020032 Level 3 Name 10020035 Level 4 Name 10020166 Level 5 Name 10020166 Level 5 Name 10020027 Level 1 name 10020028 Level 2 name 10020032 Level 3 Name 10020035 Level 4 Name 10020166 Level 5 Name 10020038 Level 6 Name 10020027 Level 1 name 10020028 Level 2 name 10020083 Level 3 Name 10020083 Level 3 Name 10020083 Level 3 Name 10020083 Level 3 Name 10020027 Level 1 name 10020028 Level 2 name 10020083 Level 3 Name 10020188 Level 4 Name 10020188 Level 4 Name 10020188 Level 4 Name TThe rows that are on level 6 should have a different id in each column. But rows that is in higher levels are going to mirror the value on the last level into all the missing levels. Example Level 1 row should mirror Level1 values into all columns. Level 2 rows will look like this (Level 1 on top, then level 2 on the rest): Level 1 ID Level 1 Name Level 2 ID Level 2 Name Level 3 ID Level 3 Name Level 4 ID Level 4 Name Level 5 ID Level 5 Name Level 6 ID Level 6 Name 10020027 Level 1 name 10020028 Level 2 name 10020028 Level 2 name 10020028 Level 2 name 10020028 Level 2 name 10020028 Level 2 name Level 3rows will look like this (Level 1 on top, then level 2 and then level 3 on the rest): Level 1 ID Level 1 Name Level 2 ID Level 2 Name Level 3 ID Level 3 Name Level 4 ID Level 4 Name Level 5 ID Level 5 Name Level 6 ID Level 6 Name 10020027 Level 1 name 10020028 Level 2 name 10020032 Level 3 Name 10020032 Level 3 Name 10020032 Level 3 Name 10020032 Level 3 Name I tried a code like this: WITH OrgCTE AS ( SELECT ID AS Level1_ID, Name AS Level1_Name, ID AS Level2_ID, Name AS Level2_Name, ID AS Level3_ID, Name AS Level3_Name, ID AS Level4_ID, Name AS Level4_Name, ID AS Level5_ID, Name AS Level5_Name, ID AS Level6_ID, Name AS Level6_Name, ID, MOTHER_ID, Level FROM Tabell1 WHERE Level = 1 UNION ALL SELECT c.Level1_ID, c.Level1_Name, CASE WHEN p.Level = 2 THEN p.ID ELSE c.Level2_ID END, CASE WHEN p.Level = 2 THEN p.Name ELSE c.Level2_Name END, CASE WHEN p.Level = 3 THEN p.ID ELSE c.Level3_ID END, CASE WHEN p.Level = 3 THEN p.Name ELSE c.Level3_Name END, CASE WHEN p.Level = 4 THEN p.ID ELSE c.Level4_ID END, CASE WHEN p.Level = 4 THEN p.Name ELSE c.Level4_Name END, CASE WHEN p.Level = 5 THEN p.ID ELSE c.Level5_ID END, CASE WHEN p.Level = 5 THEN p.Name ELSE c.Level5_Name END, CASE WHEN p.Level = 6 THEN p.ID ELSE c.Level6_ID END, CASE WHEN p.Level = 6 THEN p.Name ELSE c.Level6_Name END, p.ID, p.MOTHER_ID, p.Level FROM Tabell1 p INNER JOIN OrgCTE c ON p.MOTHER_ID = c.ID ) SELECT Level1_ID, Level1_Name, Level2_ID, Level2_Name, Level3_ID, Level3_Name, Level4_ID, Level4_Name, Level5_ID, Level5_Name, Level6_ID, Level6_Name FROM OrgCTE ORDER BY Level1_ID, Level2_ID, Level3_ID, Level4_ID, Level5_ID, Level6_ID; But this did not mirror the lowest ID for the row. It just put Level1 ID for the missing levels. Like this: Suggestions?
Recent Blogs
- The SQL Tools team is excited to announce the upcoming release of SQL Server Management Studio (SSMS) 21 Preview 1, and the upcoming Private Preview of Copilot in SSMS.Dec 20, 2024
- The SQL Tools team, in partnership with Visual Studio team, is thrilled to announce the release of SQL Server Management Studio 21 Preview 1, which is based on Visual Studio 2022 (17.13.0 Previe...Dec 19, 2024
