T-SQL (SS2K8) :: Calculate On Newly Created Columns
Aug 2, 2014
I have a table that I have created a table and desire to do some basic math by adding a few new columns. The problem is that i cant get this to work without create many new select statements. The new columns that I wish to add refer to other newly created columns. Is there a way I can do this with CTW or subqueries? Unless it is a best practice to chain out the logic for the newly created columns
I have an example from AdventureWorksDW since the data is very accessible. I can safely create EMP_TENURE and PTO_REMAINING is this select statement. I would then need to create a new select statement to define 'BONUS' and then another select statement to define 'NEW_COL1' and so on.
Im still pretty new at SQL and am trying to learn how to complete such a task using subqueries or CTE.
SELECT
NAME = [LastName] + ',' + [FirstName]
,HireDate
,Title
,DepartmentName
,BaseRate
,VacationHours
,SickLeaveHours
[Code] ....
View 1 Replies
ADVERTISEMENT
Nov 8, 2007
hello, i have a problem in my sql. im using a stored procedure and i want to get the newly created id number to be used to insert in the other table.it works like:insert into table() values()select @id = (newly created id number) from tableinsert into table1() values(@id) something like that.
View 2 Replies
View Related
Feb 17, 2000
I am trying to execute three dts packages from a Job that I created. When creating the steps I am setting the Type To: Operating System Command(CmdExec).
The Command I am using is: dtsrun /S servername /E /dtsPackageName
For some reason whenever I try to run thes jobs it fails. Within this Job I have three steps. For each step I am using the same job but with a different package name. I have a trusted connection so my command should work just fine. What am I doing wrong. Can some please explain this process.
Thanks
View 1 Replies
View Related
Apr 12, 2015
I am trying to create a simple query like this -
SELECT
Column_1,
Column_2,
Column_3,
10*Column_1 AS Column_4,
10*Column_2 AS Column_5,
-- I am not being able to understand how to do this particular step Column_1*Column_5 As Column_6
FROM Table_1
First 3 Columns are available within the Original Table_1
The Column_4 and Column_5 have been created by me, by doing some Calculations related to the original columns.
Now, when I try to do FURTHER CALCULATION on these newly created columns, then SQL Server does not allows that.
I was hoping that I will be able to use the Newly Created Columns 4 and 5 within this same query to do further more calculations, but that does not seems to be the case, or am I doing something wrong here ?
If I have to create a new column by the name of Column_6, which is actually a multiplication of Original Column_1 and Newly Created Column_5 "I tried this - Column_1*Column_5 As Column_6", then what is the possible solution for me ?
I have tried to present my problem in the simplest possible manner. The actual query has many original columns from Table_1 and many Calculated columns that are created by me.And now I have to do various calculations that involve making use of both these type of columns.
View 2 Replies
View Related
Feb 21, 2006
Hi
I had DTS the Northwind sample database from SqlServer 2000 to 2005. It's went ok and no errors. Then, I created a SP named upGetCustomer, bascially it queries the Customers table and list some of it's fields and order by CustomerId,CustomerName, Country decrementally. SP is so simple and has no errors.
Then, I created a SqlServer project in VS2005 using C#. Add store procedure class as below,
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void Customers(string customerid)
{
using (SqlConnection sqlConn =
new SqlConnection("context connection=true"))
{
sqlConn.Open();
SqlPipe sqlPipe = SqlContext.Pipe;
SqlParameter param = new SqlParameter("@custId", SqlDbType.VarChar, 5);
param.Value = customerid;
SqlCommand sqlCmd = new SqlCommand();
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "upGetCustomer";
sqlCmd.Parameters.Add(param);
SqlDataReader rdr = sqlCmd.ExecuteReader();
SqlContext.Pipe.Send(rdr);
}
}
I had taken method of created SQLCLR from source in Microsoft website, but coding is mine, and hopes it is correct. I used SqlConnection string as 'context connection=true' which I still not quite sure what does it means, hopes it mean current connection I am using on my Windows authentication.
The project did compiled & deployed OK on the VS2005 side.
The problem is that when I tried to locate the assembly on the Sql Server 2005, but can't find it anywhere on Sql Server.
I did try complied and deloyed again, it keeps saying there is an error in deployment as customers assembly is already exist on the database. I then, tried to remove this assembly on the database using SQL script, drop assembly customers in the Northwind database but got error saying it does not exist. So where is it???
Please help...
Thank you
View 1 Replies
View Related
Apr 12, 2015
I am trying to create a simple query like this -
SELECT
Column_1,
Column_2,
Column_3,
10*Column_1 AS Column_4,
10*Column_2 AS Column_5,
-- I am not being able to understand how to do this particular step Column_1*Column_5 As Column_6
FROM Table_1
First 3 Columns are available within the Original Table_1
The Column_4 and Column_5 have been created by me, by doing some Calculations related to the original columns.
Now, when I try to do FURTHER CALCULATION on these newly created columns, then SQL Server does not allows that.
I was hoping that I will be able to use the Newly Created Columns 4 and 5 within this same query to do further more calculations, but that does not seems to be the case?
If I have to create a new column by the name of Column_6, which is actually a multiplication of Original Column_1 and Newly Created Column_5 "I tried this - Column_1*Column_5 As Column_6", then what is the possible solution?
I have tried to present my problem in the simplest possible manner. The actual query has many original columns from Table_1 and many Calculated columns that are created by me. And now I have to do various calculations that involve making use of both these type of columns.
View 4 Replies
View Related
Jun 18, 2008
I created a database called sports and then created a table which I'm going to use as a template for other tables within the same database.
What is the proper way to structure and implement copying this template table?
Should I store the template table under the system tables folder?
How do I copy the template table and create a new table from this template?
Thanks you
Goldmember
View 3 Replies
View Related
Mar 10, 2007
Hi!
I have a piece of code that connects to the master database and uses the simple 'CREATE DATABASE MYDATABASE' statement to create a new database on an sql server 2005 express instance.
I then close that connection and try to open a new connection using my newly created database. I use integrated authentication in both cases. However, I get the following error: Cannot open database "MYDATABASE" requested by the login. The login failed.Login failed for user 'DOMAINNAMEusername'.System.Data.SqlClient.SqlException: Cannot open database "MYDATABASE" requested by the login. The login failed.
I don't get this error all the time. I tried to run my code many times and it some times work. But in some cases it doesn't. The database is always created but some times I cannot connect to it. But again, if I wait a little and try again with the same database, the connection is made.
I know that my connections are opened and closed normally and they don't remain open. So I don't believe I reach the maximum number of available connections.
I also sometimes get this exception : A connection was successfully established with the server, but then an
error occurred during the login process. (provider: Shared Memory
Provider, error: 0 - No process is on the other end of the pipe.
Any clue?
View 5 Replies
View Related
Mar 23, 2008
I created a new database in SQL Server 2000, using the command
"CREATE DATABASE ContactManager ON PRIMARY(NAME = ContactManager,FILENAME ='C:Program FilesMicrosoft SQL ServerMSSQLDataContactManager.mdf',SIZE = 5MB,MAXSIZE = 20MB,FILEGROWTH = 5MB)LOG ON(NAME = ContactManager,FILENAME = 'C:Program FilesMicrosoft SQL ServerMSSQLDataContactManager.ldf',SIZE = 2MB,MAXSIZE = 10MB,FILEGROWTH = 2MB)Go"
Databse was successfully created and I created two tables also in it. Now when i try to create a new connection in Visual Web Developer 2005, I get the error message :
"Directory lookup for the file "C:Program FilesMicrosoft SQL ServerMSSQLDataContactManager.mdf" failed with the operating system error 5 (Acess is denied.).
Cannot attatch the file "C:Program FilesMicrosoft SQL ServerMSSQLDataContactManager.mdf" as 'ContactManager'."
What else do I need to do use the database?
View 7 Replies
View Related
Sep 11, 2014
I have many new tables for which i need to write Insert,Update and delete triggers manually. Is there any way to generate triggers script which takes table name as a input parameter and print/generate trigger's script?
View 1 Replies
View Related
Sep 18, 2015
Why are the user databases that were created in the MSSQLSERVER default instance showing up in the newly created ALPHAONE instance? I'm successfully logging into the alphaone database as it shows as "DASAlphaOne,1xxxPport) at the top of the treeview in ssms. I'm logging in as sa and can edit anything.
The issue here is that all the user databases are shown and can even be edited. I created this instance in an effort to hide databases from whoever is not supposed to see.I was expecting a clean instance with only the system databases..Is there something that can be set to keep each instance's databases private into itself?
View 8 Replies
View Related
Apr 2, 2007
We've been running a mirrored database (using certificates since we don't have a domain) and it's all working well. Last week we decided to add a witness for automatic failovers, but for some reason I just can not get the witness to connect to the Partner2 server.
See screenshot here
Please help me troubleshoot this - I re-created the endpoints / users / certificates but it's still not working. Where can I get more information on what exactly the problem is? Can I test the endpoints somehow?
View 9 Replies
View Related
Jul 20, 2006
Hello dear Forum,
I guess this should be rather simple to answer but i just don't get how to do this. I just want to import some data from an Access mdb file into a SQL Server 2005 Database.
As I dont want to create all tables in Sql Server 2005 beforehand i'd like to create them out of my SSIS Package. Therefore i use the 'Execute SQL Task' in the control flow before stepping into the dataflow where only a oledb-source and an oledb-destination exist...
VERY simple... My problem is, that i can't select the table from the selectionlist in the oledb-destination because it does not exist before executing the package...
If i create the table by hand just to be able to select it, that does not help. because if everything is set up and then i delete the table (because it will be created by the package anyway) an error occurs before executing the package - in the validation phase:
Package Validation Error: "Invalid object name 'myToBeCreatedTable'".
Can't i create tables on the fly which i use then in my dataflow tasks?
Kind regards,
Wolfgang
Hello dear Forum,
I guess this should be rather simple to answer but i just don't get how to do this. I just want to import some data from an Access mdb file into a SQL Server 2005 Database.
As I dont want to create all tables in Sql Server 2005 beforehand i'd like to create them out of my SSIS Package. Therefore i use the 'Execute SQL Task' in the control flow before stepping into the dataflow where only a oledb-source and an oledb-destination exist...
VERY simple... My problem is, that i can't select the table from the selectionlist in the oledb-destination because it does not exist before executing the package...
If i create the table by hand just to be able to select it, that does not help. because if everything is set up and then i delete the table (because it will be created by the package anyway) an error occurs before executing the package - in the validation phase:
Package Validation Error: "Invalid object name 'myToBeCreatedTable'".
Can't i create tables on the fly which i use then in my dataflow tasks?
Kind regards,
Wolfgang
View 2 Replies
View Related
Jul 20, 2006
Hello dear Forum,
I guess this should be rather simple to answer but i just don't get how to do this. I just want to import some data from an Access mdb file into a SQL Server 2005 Database.
As I dont want to create all tables in Sql Server 2005 beforehand i'd like to create them out of my SSIS Package. Therefore i use the 'Execute SQL Task' in the control flow before stepping into the dataflow where only a oledb-source and an oledb-destination exist...
VERY simple... My problem is, that i can't select the table from the selectionlist in the oledb-destination because it does not exist before executing the package...
If i create the table by hand just to be able to select it, that does not help. because if everything is set up and then i delete the table (because it will be created by the package anyway) an error occurs before executing the package - in the validation phase:
Package Validation Error: "Invalid object name 'myToBeCreatedTable'".
Can't i create tables on the fly which i use then in my dataflow tasks?
Kind regards,
Wolfgang
View 1 Replies
View Related
Apr 12, 2015
I am new to SQL Programming. I am learning the basics. I am trying to create a simple query like this -
SELECT
Column_1,
Column_2,
Column_3,
10*Column_1 AS Column_4,
10*Column_2 AS Column_5,
-- I am not being able to understand how to do this particular step Column_1*Column_5 As Column_6
FROM Table_1
First 3 Columns are available within the Original Table_1
The Column_4 and Column_5 have been created by me, by doing some Calculations related to the original columns.
Now, when I try to do FURTHER CALCULATION on these newly created columns, then SQL Server does not allows that.
I was hoping that I will be able to use the Newly Created Columns 4 and 5 within this same query to do further more calculations, but that does not seems to be the case, or am I doing something wrong here ?
If I have to create a new column by the name of Column_6, which is actually a multiplication of Original Column_1 and Newly Created Column_5 "I tried this - Column_1*Column_5 As Column_6", then what is the possible solution for me ?
I have tried to present my problem in the simplest possible manner. The actual query has many original columns from Table_1 and many Calculated columns that are created by me. And now I have to do various calculations that involve making use of both these type of columns.
View 8 Replies
View Related
Jun 17, 2015
I run a query
select col1, col2, col3, col4
from Table
where col2=5
order by col1
I have a primary key on the column.The execution plan showing the clustered index scan cost 30% & sort cost 70%..When I run the query I got missing index hint on col2 with 95% impact.So I created the non clustered index on col2.The total executed time decreased by around 80ms but I didn't see any Index name that is using in the execution plan.After creating the index also I am seeing same execution plan
The execution plan showing the clustered index scan cost 30% & sort cost 70% but I can see the total time is reducing & Logical reads on that table is reducing.I am sure that index is useful but why there is no change in the execution plan?
View 7 Replies
View Related
Sep 29, 2015
I am getting ready to start a project where I am charged with moving out old data from production into a newly created historical DB. We have about 8 tables that are internal audit tables, that are big and full of old data. These tables are barely used and are taking up way too much space and time for maintenance.
I would like to create a way (SSIS?) to look at the date field in each of the 8 tables and copy out anything older than two years into my newly created history DB. Then deleting the older records from the source DB.
I don't know if SSIS is the best method to use. If it is, what containers to use to move over data, then how to do delete from source?
Can I do the mass deletes on my audit source tables without impacting performance/indexes/fragmentation?
View 5 Replies
View Related
Jul 25, 2015
I have a four tables called plandescription, plandetail and analysisdetail. The table plandescription has the columns DetailQuestionID which is the primary and identity column and a QuestionDescription column.
The table plandetail consists of the column PlanDetailID which the primary and identity column, DetailQuestionID which is the foreign key attribute of plandescription table and a planID column.
The third table analysisdetail consists of a analysisID which the primary and identity column, PlanDetailID which is the foreign key attribute of plandetail table and a scenario.
Below is the schema of the three tables
I have a two web form that will insert, update and delete data into these three tables in a two transaction. One web form will perform CRUD operations in plandescription and plandetail table. When the user inserts QuestionDescription and planid in this web form, I will insert the QuestionDescription Value in the plandescription table and will generate a DetailQuestionID value and this value is fed to the plandetail table with the planid. Here I will generate a PlanDetailID.
Once this transaction is done, I will show the second web form in which the user enters the scenario and this will be mapped with the plandescription using the PlanDetailID.
This schema cannot be changes as this is the client requirement. When I insert values I don’t have any problem. However when I update existing data, I need to delete existing PlanDetailID in the plandetail table and recreate PlanDetailID data for that DetailQuestionID and planID. This is because, the user will be adding or deleting a planID associated with the QuestionDescription.
Once I recreate PlanDetailID for that DetailQuestionID and planID, I need to update the old PlanDetailID with the new PlanDetailID in the third table analysisdetail for the associated analysisID.
I created a #Temp table called #DetailTable to insert the values analysisID, planid and old PlanDetailID and new PlanDetailID so that I can have them in update statement once I delete the data from plandetail table for that PlanDetailID.
Then I deleted the plandetailid from the plandetail table and recreate PlanDetailID for that DetailQuestionID. During my recreation I fetched the new PlanDetailID’s created into another temp table called #InsertedRows
After this I am running a while loop to update the temp table #DetailTable with the newly created PlanDetailID for the appropriate planID’s. The problem is here. When I have the same number of planID’s for example 2 planID’s 1,2 I will have only two old PlanDetailID and new PlanDetailID for that planID and analysisID.But When I add a new PlanID or remove a existing planID I am getting null value for that newly added or deleted planID. This is affecting my update statement of analysisdetail table as PlanDetailID cannot be null.
I tried to remove the Null value from the #DetailTable by running the update statement of analysis detail in a while loop however its not working.
DECLARE @categoryid INT = 8
DECLARE @DetailQuestionID INT = 1380
/*------- I need the query to run for the below three data.
Here i'm updating my planids that already exists in my database*/
DECLARE @planids VARCHAR(MAX) = '2,4,5'
[code].....
View 2 Replies
View Related
Jul 15, 2015
I am setting up SQL audit on sql servers in my environment based on requirement. I want to create database specifications ASAP database created. I tried DDL trigger but Audit doesn't support triggers. So I created audit specifications on model database. the only problem with this is every specification created on new database with same name.database specification name includes newly created database name or other methods to create database specifications on newly created databases.
View 6 Replies
View Related
Oct 24, 2015
How can i assign permissions to a newly created users as of an existing user?
View 3 Replies
View Related
Mar 13, 2014
create table cust_details
(
id int ,
city varchar(20),
cust_name varchar(20),
sales int
)
insert into cust_details
values('1','Tamilnadu','101','500'),
values('2','Tamilnadu','102','300'),
values('3','Pondi','103','200'),
values('4','Pondi','104','100')
My expecting o/p:
-----------------
city customer sales
tamilnadu 101 500
102 300
------
total 800
-------
or
customer sales
101 500
102 300
total 800
Like I wanna display separate location?
View 2 Replies
View Related
Oct 8, 2014
I am having below schema:
CREATE TABLE #Attendance(
[ID] [int] IDENTITY(1,1) NOT NULL,
[StudentID] [int] NOT NULL,
[ClassID] [int] NOT NULL,
[DateAdded] [datetime] default getdate() NOT NULL
) ON [PRIMARY]
insert into #Attendance(StudentID,ClassID,DateAdded) values(1,1,'2014-10-07 10:38:02.900')
[Code] ....
DateAdded column in first table is nothing but in and out time.
Now I want to prepare a query where I want to consider MIN DateAdded and max DateAdded and calculate the duration of student present in the class.
Validations i need to consider are:
If class is starting at 10am then student can come at 9:50am, i.e. Dateadded column should consider as student present in that class if value is less that 10 minutes of StartTime from #ClassAttendance table. Class End time i want to calculate depending upon ClassMinutes from #ClassAttendance
Also DateAdded column should be 10 minutes plus compared to calculated endtime. If its more than that consider lower DateAdded time.
And by using this thingIi want to calculate total number of minutes student present in the class and number of minutes absent.
If there is only one DateAdded for class then consider as a absent student.
View 7 Replies
View Related
Feb 6, 2015
I want to show cumulative numbers, but don't know how to calculate them.
Here is an example of the source and the wanted result:
Source:
[Week] [Count]
1 15
2 5
3 6
4 10
(until 52)
Result:
[Week] [Count]
1 15
2 20
3 26
4 36
Is this possible, and how?
TestData:
USE TestDb /*SqlServer 2005*/
CREATE TABLE Test(
[Week] [int] NOT NULL,
[Count] [int] NOT NULL
)
GO
INSERT INTO Test ([Week], [Count]) VALUES (1, 15)
INSERT INTO Test ([Week], [Count]) VALUES (2, 5)
INSERT INTO Test ([Week], [Count]) VALUES (3, 6)
INSERT INTO Test ([Week], [Count]) VALUES (4, 10)
View 8 Replies
View Related
Apr 16, 2015
I have been trying to calculate age and the results either round the age up one year or down one year. I have tried CASE, DATEDIFF, FLOOR functions but nothing works.
View 3 Replies
View Related
Sep 3, 2015
I have 2 tables as defined below. I want to calculate PS1time and Ps2 time.
Table 1
O_IDP_TYPEP_startdateP_enddate
ABCP8/24/2015 13:148/24/2015 13:41
ABCP8/24/2015 14:038/24/2015 15:31
ABCP8/25/2015 12:098/25/2015 13:25
XYZP8/28/2015 13:108/28/2015 21:44
Table 2
O_IDS_TYPES_startdateS_enddate
ABCS28/24/2015 13:148/24/2015 19:22
ABCS28/24/2015 19:228/30/2015 21:34
XYZS28/27/2015 22:228/28/2015 13:10
XYZS28/28/2015 13:108/28/2015 15:34
XYZS18/28/2015 15:348/28/2015 22:44
OUTPUT
O_IDSP_TYPEPS1_starttimePS1_starttime
ABCPS18/24/2015 13:148/24/2015 19:22
XYZPS18/28/2015 15:348/28/2015 21:44
XYZPS28/28/2015 13:108/28/2015 15:34
For Each O_Id How much time spent for Ps1 and PS2. I tried but not able to reach expecting results as mentioned.
View 3 Replies
View Related
Jul 11, 2014
I've the table like
create table accutn_det
(
fs_locn char(50),
fs_accno varchar(100),
fs_cost_center varchar(100),
fs_tran_type char(50)
[Code] .....
Like all location details stored from all months in these table
here Dr=debit,Cr=Credit Formula= 'Dr-Cr' to find the salary wavges of amount
so i made the query to find the amount for may
select
fs_locn,
fs_accno,
amount=sum(case when fs_accno like 'E%' and fs_tran_type='Dr' then fs_amount
when fs_accno like 'E%' and fs_tran_type='Cr' then fs_amount * -1
end
)
from
accutn_det where fs_trans_date between '01-may-2014' and '31-may-2014'
groupby fs_locn,fs_accno
now i need the sum values of all costcenter for the particular account.how to do that?
View 9 Replies
View Related
Nov 4, 2015
I want to display week between two dates as below.
requirement is as:
suppose there are two dates.(which will comes dynamically, so no. of weeks varied)
10/20/2015 and 01/01/2016
Now between this two dates, i want to calculate number of weeks on another date which is coming from table.
Say for example the column date is coming as 10/23/2015 then it will fall in week-1
Same way if 11/01/2015 falls in week2.
View 3 Replies
View Related
Mar 4, 2014
How can I calculate and return the previous Date at 18:00 Hours?
Here is a miserable attempt:
DECLARE @RunDate SmallDateTime
DECLARE @CurrentDate SmallDateTime
DECLARE @RunDateWoTime SmallDateTime
SET @CurrentDate = GETDATE()
SET @RunDate = DATEADD(day,-1,@CurrentDate)-- AS CurrentDate
SELECT @RunDate AS RunDate
-- Desired Result is the following:
-- 2014-03-03 18:00
View 9 Replies
View Related
Sep 30, 2014
What would be the most straight forword to Calculate 90 days and 3 Years ago from an Effective Date in a table?
as in
SELECT EffectiveDate
from FL.CEFHistory
I need to return the effective date - 90 days and 1 year from that.
[URL]
View 6 Replies
View Related
May 5, 2015
I'm trying to write a Stored Procedure that have to calculate the closest date for manufacturing.
What I have:
- The BOM (bill of materials) with the needed quantity for production
DECLARE @BOM TABLE
(
ItemIDINT
,neededQuantityfloat
)
INSERT INTO @BOM (ItemID, neededQuantity)
SELECT 1, 10
UNION ALL SELECT 2, 10
UNION ALL SELECT 3, 5
- a calculated table that told me the availability for each component of the BOM, sorted by date. (each row have a plus or minus of the quantity so it can by summarized)
DECLARE @WhareHouseMovement TABLE
(
ItemIDINT
,Quantityfloat
,DateDATETIME
)
INSERT INTO @WhareHouseMovement (ItemID, Quantity, Date)
SELECT 1, 10, '2015-03-01'
[Code] ....
My question is: how do I check when is the closest date to manufacturing? I have to check that the quantity of ALL the components of the BOM is enough to produce the product, but I can't get how to do it.
If I'm not wrong the example should give the result 2015-03-26.
View 9 Replies
View Related
Aug 27, 2014
I'd like to first figure out the count of how many rows are not the Current Edition have the following:
Second I'd like to be able to select the primary key of all the rows involved
Third I'd like to select all the primary keys of just the rows not in the current edition
Not really sure how to describe this without making a dataset
CREATE TABLE [Project].[TestTable1](
[TestTable1_pk] [int] IDENTITY(1,1) NOT NULL,
[Source_ID] [int] NOT NULL,
[Edition_fk] [int] NOT NULL,
[Key1_fk] [int] NOT NULL,
[Key2_fk] [int] NOT NULL,
[Code] .....
Group by fails me because I only want the groups where the Edition_fk don't match...
View 4 Replies
View Related
Feb 7, 2008
hello i`m rather new in sql and i have to do something simple for u i guess.
i have 3 columns a)date of birth b)current date c)final age.
i have the values of the first 2 columns and i want column c to be calculated automaticaly when i enter the values of the first 2 columns. example.
a)20/03/1972 b)07/02/2008 c)?? (should be 36).
if you could help me i would appriciate very much.
thanks in addvance
View 3 Replies
View Related
Jun 11, 2015
Basically, I'm given a daily schedule on two separate rows for shift 1 and shift 2 for the same employee, I'm trying to align both shifts in one row as shown below in 'My desired results' section.
Sample Data:
;WITH SampleData ([ColumnA], [ColumnB], [ColumnC], [ColumnD]) AS
(
SELECT 5060,'04/30/2015','05:30', '08:30'
UNION ALL SELECT 5060, '04/30/2015','13:30', '15:30'
UNION ALL SELECT 5060,'05/02/2015','05:30', '08:30'
UNION ALL SELECT 5060, '05/02/2015','13:30', '15:30'
[Code] ....
The results from the above are as follows:
columnAcolumnB SampleTitle1 SampleTitle2 SampleTitle3 SampleTitle4
506004/30/201505:30 NULL NULL NULL
506004/30/201513:30 15:30 NULL NULL
506005/02/201505:30 NULL NULL NULL
506005/02/201513:30 15:30 NULL NULL
My desired results with desired headers are as follows:
PERSONSTARTDATE STARTIME1 ENDTIME1 STARTTIME2 ENDTIME2
506004/30/2015 05:30 08:30 13:30 15:30
506005/02/2015 05:30 08:30 13:30 15:30
View 3 Replies
View Related