Horizoental Rows Needed
Apr 28, 2008
Hi ,
I am running the following query and getting results in horizental but i need results in one row of one member even if has 2 or 3 beneficieries/dependents.
SELECT
m.Empid,m.Fname,m.Surname,m.Division , d.lastname BENEF_Lastname,d.firstname BENEF_Firstname,
MAX(CASE WHEN m.type IN ('OLIF') THEN m.vol ELSE 0 END) as Employee_OLIF_Vol,
MAX(CASE WHEN m.type IN ('OLIF') THEN m.cat ELSE ' ' END) as Employee_Category,
MAX(CASE WHEN d.benefit IN ('EADD') THEN d.percentage ELSE 0 END) as BENEF_EADD_Percent,
MAX(CASE WHEN d.benefit IN ('ELIF') THEN d.percentage ELSE 0 END) as BENEF_ELIF_Percent,
from member m
join beneficiary d
on m.empid=d.empnumber
group by m.Empid,m.Fname,m.Surname,m.Division , d.lastname,d.firstname
I am getting result like
Empid Fname Surname BENEF_LName BENEF_FName OLIF_Vol
4500232 ED MISKIMMIN MISKIMMIN JENNIFER 119000
4500232 ED MISKIMMIN MISKIMMIN SHAUN 119000
But I need all result in one row.
View 3 Replies
ADVERTISEMENT
Apr 28, 2008
Hi there, I have a simple data flow from OLE DB to OLE DB that writes data from a view into a table. Is it possible to delete the table or the rows within the table before wirting the new data???
Thanks in advance!
View 10 Replies
View Related
Feb 2, 2008
Hi, Please help me with an SQL Query that fetches all the records from the three tables but a unique record for each forum and topicid with the maximum lastpostdate. Please provide separate solutions for SqlServer2000/2005.
I have four tables namely – Forums,Topics,Threads and Messages in SQL Server2000 (scripts for table creation and insertion of test data given at the end). Now, I have formulated a query as below :-
SELECT Forums.forumid AS ForumID,Topics.topicid AS TopicID,Topics.name AS TopicName,LastPosts.date as LastPostDate,LastPosts.author AS Author,
(select count(threadid) from Threads where Threads.topicid=Topics.topicid) as NoOfThreads
FROM Forums JOIN Topics ON Forums.forumid=Topics.forumid
LEFT OUTER JOIN
(
SELECT Topics.forumid,Threads.topicid,Messages.date,Messages.author FROM Topics
INNER JOIN Threads ON Topics.topicid = Threads.topicid
INNER JOIN Messages ON Threads.threadid=Messages.threadid
WHERE Messages.date=(SELECT MAX(date) FROM Messages WHERE Messages.threadid = Threads.threadid)
) as LastPosts
ON LastPosts.forumid = Topics.forumid AND LastPosts.topicid = Topics.topicid
Whose result set is as below:-
forumid
topicid
name
LastPostDate
author
NoOfThreads
1
1
Java Overall
2008-02-02 13:06:06.267
l@m.com
2
1
1
Java Overall
2008-01-27 14:46:41.000
c@b.com
2
1
2
JSP
NULL
NULL
0
1
3
EJB
NULL
NULL
0
1
4
Swings
2008-01-27 15:12:51.000
p@q.com
1
1
5
AWT
NULL
NULL
0
1
6
Web Services
NULL
NULL
0
1
7
JMS
NULL
NULL
0
1
8
XML,HTML
NULL
NULL
0
1
9
Javascript
NULL
NULL
0
2
10
Oracle
NULL
NULL
0
2
11
Sql Server
NULL
NULL
0
2
12
MySQL
NULL
NULL
0
3
13
CSS
NULL
NULL
0
3
14
FLASH/DHTLML
NULL
NULL
0
4
15
Best Practices
NULL
NULL
0
4
16
Longue
NULL
NULL
0
5
17
General
NULL
NULL
0
I want the result set as follows:-
forumid
topicid
name
LastPostDate
author
NoOfThreads
1
1
Java Overall
2008-02-02 13:06:06.267
l@m.com
2
1
2
JSP
NULL
NULL
0
1
3
EJB
NULL
NULL
0
1
4
Swings
2008-01-27 15:12:51.000
p@q.com
1
1
5
AWT
NULL
NULL
0
1
6
Web Services
NULL
NULL
0
1
7
JMS
NULL
NULL
0
1
8
XML,HTML
NULL
NULL
0
1
9
Javascript
NULL
NULL
0
2
10
Oracle
NULL
NULL
0
2
11
Sql Server
NULL
NULL
0
2
12
MySQL
NULL
NULL
0
3
13
CSS
NULL
NULL
0
3
14
FLASH/DHTLML
NULL
NULL
0
4
15
Best Practices
NULL
NULL
0
4
16
Longue
NULL
NULL
0
5
17
General
NULL
NULL
0 I want all the rows from the Forums,Topics and Threads table and the row with the maximum date (the last post date of the message) from the Messages table as shown above.
When I use the query by using only three tables namely Forums,Topics and Threads, I get the correct result. The query is as:-
SELECT Topics.forumid AS ForumID,Forums.name AS ForumName,Topics.topicid as TopicID,Topics.name as TopicName,
LastPosts.author AS Author,LastPosts.lastpostdate AS LastPost,
(select count(threadid) from threads where Threads.topicid=Topics.topicid) as NoOfThreads
FROM Forums JOIN Topics ON Forums.forumid=Topics.forumid LEFT OUTER JOIN (
SELECT Topics.forumid, Threads.topicid, Threads.lastpostdate, Threads.author FROM Threads
INNER JOIN Topics ON Topics.topicid = Threads.topicid
WHERE Threads.lastpostdate IN (SELECT MAX(lastpostdate) FROM threads WHERE Topics.topicid = Threads.topicid)
) AS LastPosts
ON LastPosts.forumid = Topics.forumid AND LastPosts.topicid = Topics.topicid
Whose result set is as:-
ForumID
ForumName
TopicID
TopicName
Author
LastPost
NoOfThreads
1
Developers
1
Java Overall
x@y.com
2008-01-27 14:48:53.000
2
1
Developers
2
JSP
NULL
NULL
0
1
Developers
3
EJB
NULL
NULL
0
1
Developers
4
Swings
p@q.com
2008-01-27 15:12:51.000
1
1
Developers
5
AWT
NULL
NULL
0
1
Developers
6
Web Services
NULL
NULL
0
1
Developers
7
JMS
NULL
NULL
0
1
Developers
8
XML,HTML
NULL
NULL
0
1
Developers
9
Javascript
NULL
NULL
0
2
Database
10
Oracle
NULL
NULL
0
2
Database
11
Sql Server
NULL
NULL
0
2
Database
12
MySQL
NULL
NULL
0
3
Desginers
13
CSS
NULL
NULL
0
3
Desginers
14
FLASH/DHTLML
NULL
NULL
0
4
Architects
15
Best Practices
NULL
NULL
0
4
Architects
16
Longue
NULL
NULL
0
5
General
17
General
NULL
NULL
0
The scripts for creating the tables and inserting test data is as follows in an already created database:-
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Topics__forumid__35BCFE0A]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__35BCFE0A
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__34C8D9D1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__34C8D9D1
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Messages__thread__33D4B598]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Messages] DROP CONSTRAINT FK__Messages__thread__33D4B598
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Forums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Forums]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Topics]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Threads]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Messages]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Messages]
GO
CREATE TABLE [dbo].[Forums] (
[forumid] [int] IDENTITY (1, 1) NOT NULL ,
[name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Topics] (
[topicid] [int] IDENTITY (1, 1) NOT NULL ,
[forumid] [int] NULL ,
[name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Threads] (
[threadid] [int] IDENTITY (1, 1) NOT NULL ,
[topicid] [int] NOT NULL ,
[subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[replies] [int] NOT NULL ,
[author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[lastpostdate] [datetime] NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Messages] (
[msgid] [int] IDENTITY (1, 1) NOT NULL ,
[threadid] [int] NOT NULL ,
[author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[date] [datetime] NULL ,
[message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[Forums] ADD
PRIMARY KEY CLUSTERED
(
[forumid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Topics] ADD
PRIMARY KEY CLUSTERED
(
[topicid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Threads] ADD
PRIMARY KEY CLUSTERED
(
[threadid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Messages] ADD
PRIMARY KEY CLUSTERED
(
[msgid]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Topics] ADD
FOREIGN KEY
(
[forumid]
) REFERENCES [dbo].[Forums] (
[forumid]
)
GO
ALTER TABLE [dbo].[Threads] ADD
FOREIGN KEY
(
[topicid]
) REFERENCES [dbo].[Topics] (
[topicid]
)
GO
ALTER TABLE [dbo].[Messages] ADD
FOREIGN KEY
(
[threadid]
) REFERENCES [dbo].[Threads] (
[threadid]
)
GO
------------------------------------------------------
insert into forums(name,description) values('Developers','Developers Forum');
insert into forums(name,description) values('Database','Database Forum');
insert into forums(name,description) values('Desginers','Designers Forum');
insert into forums(name,description) values('Architects','Architects Forum');
insert into forums(name,description) values('General','General Forum');
insert into topics(forumid,name,description) values(1,'Java Overall','Topic Java Overall');
insert into topics(forumid,name,description) values(1,'JSP','Topic JSP');
insert into topics(forumid,name,description) values(1,'EJB','Topic Enterprise Java Beans');
insert into topics(forumid,name,description) values(1,'Swings','Topic Swings');
insert into topics(forumid,name,description) values(1,'AWT','Topic AWT');
insert into topics(forumid,name,description) values(1,'Web Services','Topic Web Services');
insert into topics(forumid,name,description) values(1,'JMS','Topic JMS');
insert into topics(forumid,name,description) values(1,'XML,HTML','XML/HTML');
insert into topics(forumid,name,description) values(1,'Javascript','Javascript');
insert into topics(forumid,name,description) values(2,'Oracle','Topic Oracle');
insert into topics(forumid,name,description) values(2,'Sql Server','Sql Server');
insert into topics(forumid,name,description) values(2,'MySQL','Topic MySQL');
insert into topics(forumid,name,description) values(3,'CSS','Topic CSS');
insert into topics(forumid,name,description) values(3,'FLASH/DHTLML','Topic FLASH/DHTLML');
insert into topics(forumid,name,description) values(4,'Best Practices','Best Practices');
insert into topics(forumid,name,description) values(4,'Longue','Longue');
insert into topics(forumid,name,description) values(5,'General','General Discussion');
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'About Java Tutorial',2,'a@b.com','1/27/2008 02:44:29 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'Java Basics',0,'x@y.com','1/27/2008 02:48:53 PM');
insert into threads(topicid,subject,replies,author,lastpostdate) values (4,'Swings',0,'p@q.com','1/27/2008 03:12:51 PM');
insert into messages(threadid,author,date,message) values(1,'a@b.com','1/27/2008 2:44:39 PM','Where can I find Java tutorials.');
insert into messages(threadid,author,date,message) values(1,'c@d.com','1/27/2008 2:45:28 PM','Please visit www.java.sun.com');
insert into messages(threadid,author,date,message) values(1,'c@d.com','1/27/2008 2:46:41 PM','Do a Google serach for more tutorials.');
insert into messages(threadid,author,date,message) values(2,'x@y.com','1/27/2008 2:48:53 PM','Please provide some Beginner tutorials.');
insert into messages(threadid,author,date,message) values(3,'p@q.com','1/27/2008 3:12:51 PM','How many finds of layout managers are there?');
insert into messages(threadid,author,date,message) values(2,'l@m.com','2/2/2008 1:06:06 PM','Search on Google.');
View 5 Replies
View Related
Mar 29, 2008
I am trying to create a SSRS report that needs to show average for both parent and child rows. In the example below i need to show an average of ErrorRecAge column for Company, then for cic_Group and one grand total/avg. How can this be done from single query (as below) and using SSRS built in functions/codes? I am also curious to know if this can be done from TSQL directly (ex. something similar to running sum/total). I appreciate the help.
Select
CustomerCorrection.Id As ParentId,
cc_company As cc_company2,
ccError.Id As ErrorId,
COALESCE(cic_Group,'Other Errors') As cic_Group,
COALESCE(cic_Code,'Unknown') As ErrorCode,COALESCE(cic_Description,'Unknown') As ErrorDescription,
DateDiff(hh,cc_entrydt,getdate())/24.00 AS ErrorRecAge
From
CustomerCorrection Inner Join CCError On
CustomerCorrection.Id = CCError.CustomerCorrectionId
Left Outer Join CustomerImportControl On
cic_code = ce_errno
Where cc_company = 'IWA' And CustomerCorrection.Id In (129,1004,3228)
Order By CustomerCorrection.Id
That Returns
129 IWA 992 Other Errors Unknown Unknown 399.500000
129 IWA 1089 Other Errors Unknown Unknown 399.500000
129 IWA 1760 Other Errors Unknown Unknown 399.500000
1004 IWA 952 Other Errors Unknown Unknown 365.333333
1004 IWA 1853 Other Errors Unknown Unknown 365.333333
3228 IWA 10 Other Errors Unknown Unknown 329.375000
Here is my temp workaround:
Select a.*,b.*
From
(
Select
cc_Company, Count(Distinct CustomerCorrection.Id) as RecCount, Avg(DateDiff(hh,cc_entrydt,getdate()))/24.00 As RecAge
From
CustomerCorrection
Group By cc_Company
) As a
Inner Join
(
Select
cc_company As cc_company2,
COALESCE(cic_Group,'Other Errors') As cic_Group,
Count(Distinct ccError.Id) As ErrorRecCount,
AVG(DateDiff(hh,cc_entrydt,getdate()))/24.00 As ErrorRecAge
From
CustomerCorrection Inner Join CCError On
CustomerCorrection.Id = CCError.CustomerCorrectionId
Left Outer Join CustomerImportControl On
cic_code = ce_errno
Group By cc_company, COALESCE(cic_Group,'Other Errors')
) as b
On a.cc_company = b.cc_company2
Order By cc_Company,cic_group
View 2 Replies
View Related
Sep 19, 2006
This one isn't so simple.I have a list of training modules, training complete dates and a list of employees in separate tables. I'll give an good example in a second. The problem I am having is that I need to generate a select statement that will generate a kind of 'spreadsheet' that will list the employees in the rows, and columns containing the results in the fields (the training module may or may not have been completed, and thus may or may not be in the result box. I think the example explains it fairly well (note, I did not design the database structure but have to work with it).Employees table:empNameJane DoeAlton BrownJohn DoeTrainingCourse table:courseNameWeldingBrain SurgeryScuba DivingResults table:empName: courseName: completeDate:Jane Doe Welding 2/2/2002Jane Doe Brain Surgery 3/7/2005Alton Brown Scuba Diving 9/23/2004Alton Brown Welding 11/4/2004John Doe Brain Surgery 6/14/2003End result of select statement: Welding Brain Surgery Scuba DivingJane Doe 2/2/2002 3/7/2005 Alton Brown 11/4/2004 9/23/2004John Doe 6/14/2003 Thanks a million to anyone with insight into this. I'm still trying to figure out a way to do this, but after a few days haven't come up with or found anything. Most things I've found online are too simplistic.
View 8 Replies
View Related
Apr 8, 2014
Table:
CREATE TABLE [dbo].[KPI](
[SVP] [varchar](20) NULL,
[Wk1] [int] NULL,
[Wk2] [int] NULL,
[Wk3] [int] NULL,
[Wk4] [int] NULL,
[Wk5] [int] NULL,
[Y] [int] NULL,
[Q] [int] NULL,
[Wk] [int] NULL
)
To generate sample data:
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 1,0,0,0,0,2014,1,1)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,2,0,0,0,2014,1,2)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,0,3,0,0,2014,1,3)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,0,0,4,0,2014,1,4)
insert into KPI (SVP, Wk1, Wk2, Wk3, Wk4, Wk5, Y, Q, Wk) Values ('SVP', 0,0,0,0,0,2014,1,5)
[Code] ....
Current result:
SVPWk1Wk2Wk3Wk4Wk5YQWk
SVP10000201411
SVP02000201412
SVP00300201413
SVP00040201414
[Code] ....
Expected result:
SVPWk1Wk2Wk3Wk4Wk5YQ
SVP1234020141
SVP30 1 2 6 9 20142
SVP103226820143
SVP17233141120144
I surely can loop each row and insert the needed value into the result, I want to know if there is a better way to generate the result.
View 2 Replies
View Related
Oct 28, 2014
I have 3 tables...
JobRequirements (A)
JobID int
QualificationTypeID int
EmployeeQualifications (B)
EmployeeID int
QualificationTypeID int
Employee (C)
EmployeeID int
EmployeeName int
I need to return a list of all employees fit for a specific job ... The criteria is that only employees who have all the JobRequirements are returned. So if a job had 3 requirements and the employee had just 2 of those qualifications, they would not be returned. Likewise, the employee might have more qualifications than the job requires, but unless the employee has all the specific qualifications the job requires they are not included. If an employee has all the job qualifications plus they have extra qualifications then they should be returned...
How to only return those records where all the child records are present in the other table..
View 5 Replies
View Related
Nov 8, 2007
Hi All,
I am using sql server 2005. I stuck out in a strange problem.
I am using view in my stored procedure, when I run the stored procedure some of the rows get skipped out means if select query have to return 10 rows then it is returning 5 rows or any other but not all, also the records displyaing is randomly coming, some time it is displaying reords 12345 next time 5678, other time 2468.
But if I run seperately the querys written in SP then it returns all the rows. Please give me solution why it is happening like this.
There are indexes in the tables.
Once I shrink the database and rebuild the indexes, from then this problem is happening. I have rebuild the indexes several time, also updated the statistics but nothing improving.
But nothing is improving
View 7 Replies
View Related
Feb 20, 2008
Hi,
When expoting data from excel to sql server table, using SSIS package, after exporting is done, how would i check source rows are equal to destination rows. If not to throw an error message.
How can we handle transactions in SSIS
1. when some error/something happens during export and the # of rows are not exported fully to destination, how to rollback the transaction in SSIS.
Any sort of help would be highly appreciated.
Thanks,
View 2 Replies
View Related
Sep 11, 2015
I have a conditional split in an SSIS package - one split is where if rows are returned according to a specific rule, then insert those rows into to a Recordset Destinationm which points to a variable of Object type.
How I can use this variable to email fellow users. For example, what I would like is if ANY rows are returned to the Object variable (1 or more), then I would like to execute an email SP that we have on our server.
View 4 Replies
View Related
Feb 20, 2008
Hi,
When expoting data from excel to sql server table, using SSIS package, after exporting is done, how would i check source rows are equal to destination rows. If not to throw an error message.
Any sort of help would be highly appreciated.
Thanks,
View 1 Replies
View Related
Dec 25, 2005
Hello,
I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row:
user1 answer1user1 answer2user1 answer3user2 answer1user2 answer2user2 answer3
For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so that each user will be on a single row with all of that user's answers on that single row (A column for each answer):
user1 answer1 answer2 answer3user2 answer1 answer2 answer3
How can this be done? How can all answers of a user appear on a single row
Thanx,Danny.
View 1 Replies
View Related
Aug 17, 2007
Hi i tried designing a SSIS package which loads only those rows which were different from existing rows in the table , i need to timestamp the existing row with an inactive date when a update of that row is inserted (ex: same studentID )
and the newly inserted row with a insert time stamp
so as to indicate the new row as currently active, in short i need to maintain history and current rows in same table , i tried using slowly changing dimension but could not figure out, anyone experience or knowledge regarding the Data loads please respond.
example of Data would be like
exisiting data
StudentID Name AGE Sex ADDRESS INSERTTIME UPDATETIME
12 DDS 14 M XYZ ST 2/4/06 NULL
14 hgS 17 M ABC ST 3/4/07 NULL
New row to insert would be
12 DDS 15 M DFG ST 4/5/07
the data should reflect
StudentID Name AGE Sex ADDRESS INSERTTIME UPDATETIME
12 DDS 14 M XYZ ST 2/4/06 4/5/07
12 DDS 15 M DFG ST 4/5/07 NULL
14 hgS 17 M ABC ST 3/4/07 NULL
Please provide your input as much as you can even though it might not be a 100% solution.
View 4 Replies
View Related
Mar 12, 2007
I had created a trigger which sees that whether a database is updated if it is its copy the values of the updated row into another control table now I want to read the content of control_table into BIzTalk and after reading I want to delete it.Can any one suggest the suitable ay to do this?
View 3 Replies
View Related
May 8, 2008
I have the following variables VehicleID, TransactDate, TransactTime, OdometerReading, TransactCity, TransactState.
VehicleID is the unique vehicle ID, OdometerReading is the Odometer Reading, and the others are information related to the transaction time and location of the fuel card (similar to a credit card).
The records will be first grouped and sorted by VehicleID, TransactDate, TransactTime and OdometerReading. Then all records where the Vehicle ID and TransactDate is same for consecutive rows, AND TransactCity or TransactState are different for consecutive rows should be printed.
I also would like to add two derived variables.
1. Miles will be a derived variable that is the difference between consecutive odometer readings for the same Vehicle ID.
2. TimeDiff will be the second derived variable that will categorize the time difference for a particular vehicle on the same day.
My report should look like:
VehID TrDt TrTime TimeDiff Odometer Miles TrCity TrState
1296 1/30/2008 08:22:42 0:00:00 18301 000 Omaha NE
1296 1/30/2008 15:22:46 7:00:04 18560 259 KEARNEY NE
Can someone please help me here?
Thanks,
Romakanta
View 1 Replies
View Related
Apr 6, 2006
Environment:
Running this code on my PC via VS 2005
.Net version 2.0.50727 on the server (shown in IIS)
Code is in ASP.NET 2.0 and is a VB.NET Console application
SSIS 2005
Problem & Info:
I am bringing in an Excel file. I need to first strip out any non-detail rows such as the breaks you see with totals and what not. I should in the end have only detail rows left before I start moving them into my SQL Table. I'm not sure how to first strip this information out in SSIS specfically how down to the right component and how to actually code the component to do this based on my Excel file here: http://www.webfound.net/excelfile.xls
Then, I assume I just use a Flat File Source coponent or something to actually take the columns in the Excel and split into an OLE DB Datasource to shove each column into a corresponding column in my SQL Server Table. I have used a Flat File Source in the past to do so with a comma delimited txt file but never tried with an Excel.
Desired Help:
How to perform
1) stripping out all undesired rows
2) importing each column into sql table
View 1 Replies
View Related
Oct 13, 2015
I am facing an issue that Data flow task failing after loading 29000 rows out of 2lakhs rows.
I am loading data from .csv file to OLE DB Destination.
This data flow task is placed inside For each loop container.
is this issue because of any performance issue in SSIS packages such as buffer size.
find the error below:
DFT Load Data from FlatFile:Error: The conditional operation failed.
DFT Load Data from FlatFile:Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.
The "DER Add Calc Columns" failed because error code 0xC0049063 occurred, and the error row disposition on "DER Add Calc Columns.Outputs[Derived Column Output].Columns[M_VALUE_NUM]" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure.
DFT Load Data from FlatFile:Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "DER Add Calc Columns" (48) failed with error code 0xC0209029 while processing input "Derived Column Input" (49). The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure.
[code]....
View 8 Replies
View Related
Aug 5, 2014
I managed to transpose rows into columns.
;WITH
ctePreAgg AS
(
select top 500 act_reference "ActivityRef",
row_number() over (partition by act_reference order by act_reference) as rowno,
t3.s_initials "Initials"
from mytablestuff
order by act_reference
[code]...
But what I would love to do next is take each of the above rows - and return the initials either in one column with all the nulls and duplicate values removed, separated by a comma ..
ref, initials
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW
OR the above but using variable number of columns based on the maximum number of different initials for each row.this is not strictly required, but maybe neater for further work on the view
ref, init1,init2
Ag-4xYS
Ag-6xYS,BL
Ap-1xKW
At-2x SAS,CW
At-3x SAS,CW
View 6 Replies
View Related
Jul 24, 2015
I have a SQL script to insert data into a table as below:
INSERT into [SRV1INS2].BB.dbo.Agents2
select * from [SRV2INS14].DD.dbo.Agents
I just want to set a Trigger on Agents2 Table, which could delete all rows in the table , before carry out any Insert operation using above statement.I had below Table Trigger on [SRV1INS2].BB.dbo.Agents2 Table as below: But it did not perform what I intend to do.
USE [BB]
GO
/****** Object: Trigger Script Date: 24/07/2015 3:41:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
[code]....
View 3 Replies
View Related
Mar 30, 2008
Dear All
I have a table with the following structure in sql server 2005
create table app(
sno int,
name varchar(50),
add varchar(50),
city varchar(50),
state varchar(50)
)
it contains the follwing data
------------------------------------------
sno name add city state
------------------------------------------
1 mark street no1 newcity newstate
2 mark street no1 newcity newstate
3 mark street no1 newcity newstate
4 mark street no1 newcity newstate
5 mark street no1 newcity newstate
6 mark street no1 newcity newstate
7 mark street no1 newcity newstate
8 mark street no1 newcity newstate
9 mark street no1 newcity newstate
10 mark street no1 newcity newstate
11 mark street no1 newcity newstate
12 mark street no1 newcity newstate
13 mark street no1 newcity newstate
14 mark street no1 newcity newstate
15 mark street no1 newcity newstate
16 mark street no1 newcity newstate
17 mark street no1 newcity newstate
18 mark street no1 newcity newstate
19 mark street no1 newcity newstate
20 mark street no1 newcity newstate
----------------------------------------
I want to retrive previous 5 records, next 5 records and the record that meet the where condition of a select query.
When I run
select sno,add,name,city,state from app where sno=7
I want the following result
------------------------------------------
sno name add city state
------------------------------------------
2 mark street no1 newcity newstate |
3 mark street no1 newcity newstate |
4 mark street no1 newcity newstate | -- previous 5 records
5 mark street no1 newcity newstate |
6 mark street no1 newcity newstate |
7 mark street no1 newcity newstate --- searched record
8 mark street no1 newcity newstate |
9 mark street no1 newcity newstate |
10 mark street no1 newcity newstate |--- next 5 records
11 mark street no1 newcity newstate |
12 mark street no1 newcity newstate |
----------------------------------------
if there is a method to get the above result set, kindly post the query.
View 14 Replies
View Related
Apr 2, 2008
Hi, I need to write a query which I have never attempted before and could do with some help.... I have a Groups table and a Users_Groups look up table. In this model, users can only be assigned to 1 group. If a group is deleted, a trigger should fire and delete any rows in User_Groups having a matching Groups.Ref. Unfortunately, the trigger hasn't been firing and I now have a load of defunct rows in Users_Groups relating users to groups which do not exist.I now need to find all of these defunct rows in Users_Groups so that I can delete them. How can I find rows in Users_Groups where the parent rows and refs in Groups are null? I've tried searching the net for something similar but don't even know how to word the search properly to get any half relevant results. Cheers PS, I do realise I need to tighten the constraints on my database
View 5 Replies
View Related
Mar 25, 2002
I'm stuck. I have a table that I want to pull some info from that I don''t know how to.
There are two colomuns, one is the call_id column which is not unique and the other is the call_status column which again is not unique. The call_status column can have several values, they are ('1 NEW','3 3RD RESPONDED','7 3RD RESOLVED','6 PENDING','3 SEC RESPONDED','7 SEC RESOLVED').
i.e example, this is the existing data.
Call_id Call_Status
555555 3 3RD RESPONDED
235252 7 SEC RESOLVED
555555 7 3RD RESOLVED
325252 6 PENDING
555555 6 PENDING
325235 3 SEC RESPONDED
555555 1 NEW
This is the data I want...
Call_id Call_Status
555555 3 3RD RESPONDED
555555 6 PENDING
555555 7 3RD RESOLVED
The call_id could be any number, I only want the 6 PENDING rows where there are other rows for that call_id which have either 3 3RD RESPONDED or 7 3RD RESOLVED. If someone knows how it would be a great help.
Cheers,
Chris
View 1 Replies
View Related
Nov 28, 2007
Dear Gurus,I have table with following entriesTable name = CustomerName Weight------------ -----------Sanjeev 85Sanjeev 75Rajeev 80Rajeev 45Sandy 35Sandy 30Harry 15Harry 45I need a output as followName Weight------------ -----------Sanjeev 85Rajeev 80Sandy 30Harry 45ORName Weight------------ -----------Sanjeev 75Rajeev 45Sandy 35Harry 15i.e. only distinct Name should display with only one value of Weight.I tried with 'group by' on Name column but it shows me all rows.Could anyone help me for above.Thanking in Advance.RegardsSanjeevJoin Bytes!
View 4 Replies
View Related
Mar 30, 2007
helo..
I have 100,000 rows in the database and I want to read results for eg: from 5000 to 5050 by DataReader.
I wrote this code to do this but its too slow:
Dim SlctStr As String = "select * from topicstbl where partID like '" & PagePartID & "'"
Dim ReadCom As New SqlClient.SqlCommand
ReadCom.CommandText = SlctStr
ReadCom.Connection = MainLib.MyConnection
Dim MyReader As SqlClient.SqlDataReader = ReadCom.ExecuteReader()
Dim StartTNum As Long = 5000
For IR As Long = 0 To StartTNum - 1
MyReader.Read()
Next
Do While MyReader.Read
StartTNum += 1
If StartTNum > 5500 Then Exit Do
'''''''''''''''''''
Loop
MyReader.Close()
is there another way to do the same thing better off than this code?
View 3 Replies
View Related
Apr 4, 2008
Dear Friends,
Is there any way to display a table data separately like odd rows and even rows?I dont know this is possible or not?If it is possible means how can i achieve it?Please guide me a proper way.
Thanks all!
kiruthika
http://www.ictned.eu
View 3 Replies
View Related
Jul 25, 2007
Hi All,
I have the following Table
Type Name Value
x M1 5
x M2 10
x M3 20
y M1 10
y M2 15
y M3 30
Now, i need to add four more rows to the table
Type Name Value
x M1 5
x M2 10
x M3 20
y M1 10
y M2 15
y M3 35
z1 Total 15 (xM1+XM2)
z1 Diff 5 (xM3-xM1+XM2)
z2 Total 25 (yM1+yM2)
z2 Diff 10 (yM3-yM1+yM2)
Please help me.
Many Thanks,
View 2 Replies
View Related
Jul 20, 2005
I know this table is designed wrong for what I am doing but I hope Ican do it. I have a table like this.Prod_A_Jan, Prod_A_Feb, Prod_B_Jan, Prod_B_FebI want a query that returns data like this (two rows of data)"ProdA", Prod_A_Jan, Prod_A_Feb"ProdB", Prod_B_Jan, Prod_B_FebI know two queries can get it but I want one. Any Help would begreat!!!Sheila T.
View 3 Replies
View Related
Mar 7, 2008
Hi I'm having a bit troubble by creating a SQL-sentence which substract the newest row from the second newest row in the same column. The table looks like this:
Pricecalcid Date Price Itemid
2000 2006-12-12 3000 100
2488 2007-10-11 2800 100
3100 2008-08-07 2500 100
What I need is that the largest "Pricecalcid" that is 3100 equals "Price" 2500 and the second largest "Pricecalcid" eqauls 2800 results in a pricecalculation that substracts 2500 from 2800 for "Itemid" 100.
How do I do that?
Thanks
Morten
View 9 Replies
View Related
Dec 12, 2014
I run the following statement and it will not update beyond 7 million plus rows and I have about 38 million to complete. I keep checking updated row counts and after 1/2 day it's still the same so I know something is wrong because it was rolling through no problem when I initiated it. I need to complete ASAP so it's adding to my frustration. The 'Acct_Num_CH' field is an encrypted field (fyi).
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
WHILE @@ROWCOUNT > 0
BEGIN
SET rowcount 10000
UPDATE [dbo].[CC_Info_T]
SET [Acct_Num_CH] = 'ayIWt6C8sgimC6t61EJ9d8BB3+bfIZ8v'
WHERE [Acct_Num_CH] IS NOT NULL
END
SET rowcount 0
View 5 Replies
View Related
Jun 4, 2007
Stepping thru the code with the debugger shows the dataset rows being deleted.
After executing the code, and getting to the page presentation. Then I stop the debug and start the
page creation process again ( Page_Load ). The database still has the original deleted dataset rows.
Adding rows works, then updating works fine, but deleting rows, does not seem to work.
The dataset is configured to send the DataSet updates to the database. Use the standard wizard to create the dataSet.
cDependChildTA.Fill(cDependChildDs._ClientDependentChild, UserId); rowCountDb = cDependChildDs._ClientDependentChild.Count; for (row = 0; row < rowCountDb; row++) { dr_dependentChild = cDependChildDs._ClientDependentChild.Rows[0]; dr_dependentChild.Delete(); //cDependChildDs._ClientDependentChild.Rows.RemoveAt(0); //cDependChildDs._ClientDependentChild.Rows.Remove(0); /* update the Client Process Table Adapter*/ // cDependChildTA.Update(cDependChildDs._ClientDependentChild); // cDependChildTA.Update(cDependChildDs._ClientDependentChild); }
/* zero rows in the DataSet at this point */ /* update the Child Table Adapter */ cDependChildTA.Update(cDependChildDs._ClientDependentChild);
View 1 Replies
View Related
May 1, 2014
Is there any way to control this scenario, I know trick to put 10 on each row ,but I need to split them unevenly, 10 on first page and the rest on second page. is it possible ?
=Ceiling((RowNumber(Nothing)) / 10)
View 3 Replies
View Related
Sep 24, 2006
Im new to stored procedure, but here's what I want, if someone can get me started and provide this as an example it would be VERY welcome!I have the following, a SP with parameter IsMale. This parameter may be empty. If it's not empty I want to add some text to my selection query: AND IsMale=paramvalueHere's the SP so far:ALTER PROCEDURE [dbo].[spFindUsersAdvanced] --declare parameters here@IsMale bitASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SELECT DISTINCT BirthDate,IsMale, FROM aspnet_Users INNER JOIN tblUserData ON aspnet_Users.UserId = tblUserData.UserID WHERE tblUserData.Username<>''IF @IsMale THENBEGIN AND IsMale='True'ENDELSEBEGIN AND IsMale='False'ENDEND IFEND1. How do I check if the parameter is empty?2. How do I add the text to my selection query?
View 3 Replies
View Related
Oct 30, 2007
Hi all,I am trying to write a stored procedure, which has two insert statements.the first insert statement, is simple which inserts data into a table and returns the primary key for the new row added. using this primary key, i am writing another insert statement, which passes a list of elements which would be then entered into another table, with the primary key of the first table.Is this possible to do in a single stored procedure? I have implemented this using two different sp, but am wondering if it can be done other way?thanks for your help!
View 10 Replies
View Related
