Wednesday, May 1, 2013

[T-SQL] is it possible to union all two CTEs?

[T-SQL] is it possible to union all two CTEs?

is it possible to union all two CTEs?

Posted: 30 Apr 2013 11:44 AM PDT

I have two CTEs sharing same column names and I need to combine their results into one result set. Given my DDL below, one would conclude is isn't possible, but I am hoping there is a workaround.In the following you'll see that I am union all-ing two result sets with identical number of columns and column names. But, union all doesn't work. I get error:Msg 102, Level 15, State 1, Line 7Incorrect syntax near ';'.[code="plain"]create table #temp (ID int, Name varchar(20))insert into #tempvalues (10, 'Helen'),(20, 'Joe'),(30, 'Blake');;with cte as(select * from #temp)select * from cteunion all;with cte as(select * from #temp)select * from cte[/code] Is it possible to combine the results of two CTEs?

Top 2 On Child Table

Posted: 30 Apr 2013 11:48 PM PDT

I have two tables, one Master, one Detail. I'm trying to build a query to get the top 2 products by quantity but can't wrap my head around it.Any help is greatly appreciated.[code="sql"]DECLARE @MasterTable TABLE( MasterId int, Descript varchar(100))DECLARE @DetailTable TABLE( DetailId int, DetailDescript varchar(100), ProductQty int, MasterId int)INSERT INTO @MasterTable VALUES (1, 'Descript 1')INSERT INTO @MasterTable VALUES (2, 'Descript 2')INSERT INTO @DetailTable VALUES (1, 'Descript Detail 1', 35, 1)INSERT INTO @DetailTable VALUES (2, 'Descript Detail 2', 25, 1)INSERT INTO @DetailTable VALUES (3, 'Descript Detail 3', 15, 1)INSERT INTO @DetailTable VALUES (4, 'Descript Detail 4', 12, 2)INSERT INTO @DetailTable VALUES (5, 'Descript Detail 5', 43, 2)INSERT INTO @DetailTable VALUES (6, 'Descript Detail 6', 17, 2)SELECT * FROM @MasterTable mt INNER JOIN @DetailTable dt ON mt.MasterId = dt.MasterId[/code]It should return : MasterId DetailId ProductQty1 _ _ _ _ 1 _ _ _ 351 _ _ _ _ 2 _ _ _ 252 _ _ _ _ 5 _ _ _ 432 _ _ _ _ 6 _ _ _ 17Thanks

Add 2 values from 1 table

Posted: 30 Apr 2013 07:53 PM PDT

Morning allGood someone offer the best way to do this.I want to add 2 values from 1 table.Example:Select count (*) as 'Records Updated' from xxxx Where loadedDate > '2013-04-30 10:02:37.220'and Loadflag='update' ... gives say 700 recordsthen want to add output from :Select count (*) as 'New Records' from xxxx Where loadedDate > '2013-04-30 11:00:00.220'and Loadflag is NULL ...gives say 1000 recordsso I want 1 query that would add the values together to give output count as total.many thanks for any advice

XML Question (?)

Posted: 18 Apr 2013 11:51 AM PDT

Hi,Let's say I have the following table with just 1 record:SELECT Field1, Field2, Field3 FROM tblreturns 11, 22, 33What would be a query to return something like: <CustomInformation> <ColumnValue name="Field1">11</ColumnValue> <ColumnValue name="Field2">22</ColumnValue> <ColumnValue name="Field3">33</ColumnValue> </CustomInformation>?Thanks!

varchar to datetime convertion challenge

Posted: 30 Apr 2013 04:22 AM PDT

I have a table w/a varchar(10) column (MoYear) which has the month and year with '/' between (i.e. 01/2013). My task is to insert '01' as the date surrounded by '/' (i.e. 01/01/2013). Finally I need to convert it to datetime (i.e. 2013-01-01 00:00:00.000) but I keep getting result 'Jan 1 2013 12:00AM'.I've tried different convert options but still wrong result. So here's my workaround:1 - ALTER MoYear field to varchar(30)2 - INSERT the day of the month (01) into field by using UPDATE as: UPDATE Table Set MoYear = Substring(MoYear,1,2) + '/01/' + Substring(MoYear,4,4)3 - Update field MoYear as datetime as: UPDATE Table SET MoYear = convert(datetime,convert(varchar,MoYear,101))So, as a workaround I had to add a datetime field (MoYear2 = cast(null as datetime)) to the table. Then I updated this field with the converted value from field MoYear as: UPDATE Table Set MoYear2 = convert(datetime,convert(varchar,MoYear,101))Then I get the desired result 2013-01-01 00:00:00.000My question is, am I taking too many steps in achieving the desired result, or is this the best option?Regards,John

HierarchyID navigation - given a specific node, return the child rows, move up a level, rreturn the child rows, move up a level and repeat

Posted: 30 Apr 2013 06:48 AM PDT

Hi,I have an existing piece of code that navigates through a hierarchy and was trying to figure out if I could write a query that would return a result set rather than having to query one record at a time.I need to be able to begin with a specific node, return the child rows, move up a level, read the child rows, move up a level, read the child rows, move up a level and repeat until I run out of levels to move up. At each level it reads only the immediate child rows (i.e. not traversing down each child's children).I guess I could do this with a stored procedure and use a cursor, but I was wondering if there is a more efficient way.Thanks,Greg

Derived Column with value in previous row + current

Posted: 30 Apr 2013 05:26 AM PDT

I have a data set that has ID and Total fields. Now I need a result set that has a third column, "Adjusted Total". For the first row, "Adjusted Total" will have the same value as Total column. But from the next row onwards, it should have the total of previous "Adjusted Total" + current "Total".Below are screenshots of a sample base data set as well as the desired result set.[b][u]Base Data[/u][/b][img]http://www.sqlservercentral.com/Forums/Attachment13600.aspx[/img][b][u]Desired Result set[/u][/b][img]http://www.sqlservercentral.com/Forums/Attachment13601.aspx[/img]Here is the SQL to setup base data into a table variable. [code]DECLARE @BaseData TABLE (ID INT, Total INT)INSERT @BaseData (ID, Total) VALUES (1, 215), (2, 86), (3, 43), (4, 101), (5, 92), (6, 64), (7, 117), (8, 59), (9, 33), (10, 72), (11, 94), (12, 108), (13, 93), (14, 88), (15, 67)SELECT * FROM @BaseData[/code]Any help is appreciated.Rex

Extract a string between two special characters

Posted: 30 Apr 2013 02:11 AM PDT

Hi Everyone,Can anyone of you please help me to get the T-SQL to extract a string between two special characters.Example: /IT/Management, /PRODUCTION/NameI need to extract IT and PRODUCTION from the above Example. The / is always in the first positionI tried the below and It works but I wanted to use a column from a table rather than a predefined value.select SUBSTRING('/IT/management1234',CHARINDEX('/','/IT/management1234')+1,LEN('/IT/management1234')-charindex('/',REVERSE('/IT/management1234'))-1)Thanks in advanceSrikanth Reddy Kundur

Help with correct syntax to Use a calculated field in a second calculation

Posted: 30 Apr 2013 03:21 AM PDT

Hi, I am pretty good creating access queries, but when our time and attendance software was recently updated, the use of big integers has resutled in me having to re-create all of my queries in Visual Studio 2008. I am NOT a SQL expert, so I apologize now for what is going to be my attempt at a fairly complicated query. I would really appreciate any advice on how to accomplish this. Here goes:Our payroll admin needs to see how many sick, personal, etc days a person has as of today. But the database does not contain that, that would be too easy. Instead, the database contains a transaction table, which contains all balance related transactions, including the date. This is the query that I have so far, that works to calcualte a person's balance, but I need to layer onto it to only factor in transactions that have already occured, not transactions written to the table that are to take place in the future. SELECT employee.emp_fullname AS Name, employee.emp_name AS EmpNumber, employee_balance.empbal_value AS BALANCE, balance.baltyp_id, balance.bal_id AS BalanceID, balance.bal_name AS BalanceName, ent_emp_policy.entemppol_end_date, ( CASE WHEN ent_emp_policy.entpol_id = 10003 THEN Cast ( employee_balance.empbal_value*** / 480 AS DECIMAL(8, 2)) ELSE Cast(employee_balance.empbal_value*** / 510 AS DECIMAL(8, 2)) END ) AS Days --this is b/c we have civilian employees who have 480 minute days, and sworn employees who have 510 minute daysFROM ((employee INNER JOIN ent_emp_policy ON employee.emp_id = ent_emp_policy.emp_id) INNER JOIN employee_balance ON employee.emp_id = employee_balance.emp_id) INNER JOIN balance ON employee_balance.bal_id = balance.bal_id WHERE @balanceID = balance.bal_id AND ent_emp_policy.entemppol_end_date = '01/01/3000' AND ( CASE WHEN ent_emp_policy.entpol_id = 10003 THEN Cast ( employee_balance.empbal_value*** / 480 AS DECIMAL(8, 2)) ELSE Cast(employee_balance.empbal_value*** / 510 AS DECIMAL (8, 2)) END ) > @DaysOver The user will select the balance ID and the days over.Here is what I am trying to add into my query, but I don't know exactly how the pieces fit together. SELECT SUM(CASE WHEN wrks_work_date --this is the date when the transaction occured<= "today" THEN eblog_delta --this is the total minutes of the transaction/could be a deduction or additionELSE 0 END) As TransactionsAssuming that the code above correctly calcualtes the value of transactions, I then want to take Balance-Transactions to get a Current Value.From what I have researched, I think that the last piece of code would fit into my origianl query where I have indicated '***', but I am am stuck on the exact syntax, and if it is even possible for me to calculate a 'transaction' field, and then use it in the same query in another calculation. Any help would be so much appreciated, and I thank you for taking the time to review this post.

Cannot shrink log

Posted: 25 Apr 2013 11:14 AM PDT

My Transaction log is about 3GB and I can't shrink it.It is using a FULL backup and I try over and over again to back and shrink it and it won't do it.DECLARE @DatabaseName VARCHAR(50); SET @DatabaseName = 'myDatabase' SELECT name, recovery_model_desc, log_reuse_wait_desc FROM sys.databases WHERE name = @DatabaseNameThis gives me:LOG_BACKUP in the log_reuse_wait_desc.I have been all over the web looking for an answer but anything I try still leaves it the same size. I tried to change the inital size to 600MB but that didn't work.Any ideas what I am missing?Thanks,Tom
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog