T-SQL (SS2K8) :: Query To Show Only Top Or Main Team For Team Member

Nov 2, 2014

Team members appear twice or more if they belong to more than one team. I need to be able to show their name and main team. Team is not important at the moment but I just like to include all team members and display a team name against them.This is what I have at the moment:

SELECT SystemUser.systemuserid, FullName, TeamMembership.TeamID, TeamName
FROM Team
RIGHT OUTER JOIN TeamMembership ON Team.teamid = TeamMembership.teamid
LEFT OUTER JOIN SystemUser ON TeamMembership.systemuserid = SystemUser.systemuserid
order by FullName

View 3 Replies


ADVERTISEMENT

Power Pivot :: Unable To Refresh Report Data When Send To Other Member In Team

May 28, 2015

I am using excel 2010 and SQL server as a data source. When I create a report with the User name "demo_user" which has db_writer access, and mail it to the colleague , he could not refresh the data with the "demo_user" credentials and apparently its throwing user name or password invalid. The one who created only could be able to refresh the data.

What should I do If other people want to refresh the data? How could I fix this issue? We are using SQL server 2012 and excel 2010 powerpivot. 

View 2 Replies View Related

Group By Team In SQL DataSource?

May 19, 2008

Is it possible to group by "Team" in the following SQL Datasource?
Each row has a numbers in their respective columns and need to be added up and only one row per team is shown showing the total?
 SELECT HomeTeam AS Team, 1 AS Pld, CASE WHEN HomeScore > AwayScore THEN 1 ELSE 0 END AS Won,
CASE WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Draw, CASE WHEN HomeScore < AwayScore THEN 1 ELSE 0 END AS Lost,
HomeScore AS Scored, AwayScore AS Against, HomeScore - AwayScore AS Agg,
CASE WHEN HomeScore > AwayScore THEN 3 WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Pts
FROM tblFixtures
WHERE (CompID = 1) AND (HomeScore IS NOT NULL)
UNION ALL
SELECT AwayTeam AS Team, 1 AS Pld, CASE WHEN HomeScore < AwayScore THEN 1 ELSE 0 END AS Won,
CASE WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Draw, CASE WHEN HomeScore > AwayScore THEN 1 ELSE 0 END AS Lost,
AwayScore AS Scored, HomeScore AS Against, AwayScore - HomeScore AS Agg,
CASE WHEN HomeScore < AwayScore THEN 3 WHEN HomeScore = AwayScore THEN 1 ELSE 0 END AS Pts
FROM tblFixtures AS tblFixtures_1
WHERE (CompID = 1) AND (HomeScore IS NOT NULL) 

View 1 Replies View Related

I Need Help With The Following (SQL Team Cross Post)

Apr 13, 2004

I am trying to setup a shape, shape attributes and calculate the cross
sectional area using the formula specified in the tbShapes.Formula field.
See the code below.

What this does is convert the formula

(Width * Flange) + (((Height - Flange) * Leg) * Count)

to

(108 * 4) + (((36 - 4) * 5) *2)


Now I need to calculate the expression above, but the
expression is a varchar string.

Any help?


USE NORTHWIND
GO

SET NOCOUNT ON
CREATE TABLE [dbo].[tbProductCodes] (
[ProductCode] [int] NOT NULL ,
[fkAccountID] [int] NOT NULL ,
[Product] [varchar] (50) NOT NULL ,
[fkShapeID] [int] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO tbProductCodes (ProductCode, fkAccountID, Product, fkShapeID)
SELECT 2001, 1, 'New Product', 1
GO

CREATE TABLE [dbo].[tbProductTemplateAttributeValues] (
[fkTemplateID] [int] NOT NULL ,
[fkAttributeID] [int] NOT NULL ,
[AttributeValue] [float] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO tbProductTemplateAttributeValues (fkTemplateID, fkAttributeID, AttributeValue)
SELECT 1, 1, 108 UNION ALL
SELECT 1, 2, 36 UNION ALL
SELECT 1, 3, 4 UNION ALL
SELECT 1, 4, 5 UNION ALL
SELECT 1, 5, 2
GO

CREATE TABLE [dbo].[tbProductTemplates] (
[TemplateID] [int] NOT NULL ,
[fkProductCode] [int] NOT NULL ,
[Template] [varchar] (50) NOT NULL ,
[fkMixID] [int] NULL
) ON [PRIMARY]
GO

INSERT INTO tbProductTemplates (TemplateID, fkProductCode, Template, fkMixID)
SELECT 1, 2001, 'ProductTemplate', 1
GO

CREATE TABLE [dbo].[tbShapeAttributes] (
[AttributeID] [int] NOT NULL ,
[fkShapeID] [int] NOT NULL ,
[Attribute] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO

INSERT tbShapeAttributes (AttributeID, fkShapeID, Attribute)
SELECT 1, 1, 'Width' UNION ALL
SELECT 2, 1, 'Height' UNION ALL
SELECT 3, 1, 'Flange' UNION ALL
SELECT 4, 1, 'Leg' UNION ALL
SELECT 5, 1, 'Count'
GO

CREATE TABLE [dbo].[tbShapes] (
[ShapeID] [int] NOT NULL ,
[Shape] [varchar] (50) NOT NULL ,
[Formula] [varchar] (100) NULL
) ON [PRIMARY]
GO

INSERT INTO tbShapes (ShapeID, Shape, Formula)
SELECT 1, 'Double T', '(Width * Flange) + (((Height - Flange) * Leg) * Count)'
GO

CREATE PROCEDURE usp_shapes_GetCrossSection

@iTemplate int,
@cResult varchar (500) OUTPUT

AS

declare @cAttribute varchar(50),
@fAttribute float

-- Get the formula for the templates shape
SELECT @cResult = s.Formula
FROM tbShapes AS s INNER JOIN tbProductCodes AS pc
ON s.ShapeID = pc.fkShapeID
INNER JOIN tbProductTemplates AS pt
ON pc.ProductCode = pt.fkProductCode
WHERE pt.TemplateID = @iTemplate

SELECT @cResult AS Formula

DECLARE AttributeCursor CURSOR FOR
SELECT sa.Attribute,
av.AttributeValue
FROM tbProductTemplateAttributeValues AS av INNER JOIN tbShapeAttributes AS sa
ON av.fkAttributeID = sa.AttributeID
WHERE av.fkTemplateID = @iTemplate

OPEN AttributeCursor
FETCH NEXT FROM AttributeCursor INTO @cAttribute, @fAttribute
while(@@FETCH_STATUS = 0)
BEGIN
SELECT @cResult = REPLACE(@cResult, @cAttribute, CAST(@fAttribute AS VarChar))
FETCH NEXT FROM AttributeCursor INTO @cAttribute, @fAttribute
END

SELECT @cResult AS NewFormula

CLOSE AttributeCursor
DEALLOCATE AttributeCursor
GO

-- Test stored proc

declare @iTemplate int, @fResult float

SET @iTemplate = 1
EXECUTE usp_shapes_GetCrossSection @iTemplate, @fResult OUTPUT
SELECT @fResult AS Result
GO

drop table [dbo].[tbProductCodes]
GO

drop table [dbo].[tbProductTemplateAttributeValues]
GO

drop table [dbo].[tbProductTemplates]
GO

drop table [dbo].[tbShapeAttributes]
GO

drop table [dbo].[tbShapes]
GO

DROP PROCEDURE usp_shapes_GetCrossSection
GO



Mike B

View 3 Replies View Related

Downloads From The SSIS Team

Dec 16, 2005

Hi, Just a quick note for all that the SSIS team has started to post fresh content for download.

We will be adding some links to various pages so you can easily see things when looking at the the SSIS portal on MSDN  http://msdn.microsoft.com/SQL/bi/integration/default.aspx

For now if you search for "ssis" on the microsoft downloads site you will find the current content.  Sample Logging reports, jump start training, Meta Data info, and sample components.  More coming over the next few weeks...
http://www.microsoft.com/downloads/search.aspx?displaylang=en

Thank you and I hope everyone enjoys winding down 2005, or blowing it out, depending on your likes :)

SSIS team

 

View 1 Replies View Related

SSIS Team Development

May 15, 2006

In the past I wrote DTS transforms entirely by myself. With SSIS, our team of several developers now wants each member to develop a piece of the same package.

Do SSIS packages support this type of simultaneous multi-developer creation or is it a "one developer at a time" type product?



TIA,



Barkingdog

View 1 Replies View Related

Dev. Team - Keeping Three Tiers In Sync

Jul 23, 2005

This may not be a MSSQL-specific question, butI wanted to ask it here first, in case there'sa MSSQL and/or SourceSafe solution that will help.Our dev team is having some difficulty withkeeping the nightly builds in sync with thestored proc mods. I'm wondering if there aresome good case studies on how to avoid this"drift". Something like genning a new DB fromchecked-in SPs, etc. alongside each regular build,then always have a paired enterprise app/databaseduo that is tagged and added to a history.FWIW, we have a 3-tier .NET/C# app, andADO.NET is throwing exceptions every otherday.If the suggestion is to whip the DB guys, thatworks for me as well. ;-)Nah, there's much love there.Thanks in advance,~swooz

View 2 Replies View Related

A Player Team Tables Design?

Jul 20, 2005

i am a beginner of database design, could anyone please help me tofigure out how to make these two tables work.1) a "players" table, with columns "name", "age"2) a "teams" table, which can have one OR two player(s)a team also has a column "level", which may have values "A", "B",or "C"how do you build the "teams" table (the critical question is "do ineed to create two fields" for the maximum two possible players?")how do you use one query to display the information with the followingcolumns:"name", "age", "levelA", "levelB", "levelC" (the later three columnsare integer type, showing how many teams with coresponding level thisplayer is in).now suppose i don't have any access to sql server, i save the datainto xml, and load it into a dataset. how could you do the selectionwithin the dataset? or ahead of that, how do you specify the relationsbetween "players" and "teams".the following is the schema file i am trying to make (I still don'tknow if i need to specified the primary key... and how to buildrelation between them):<code><?xml version="1.0" ?><xs:schema id="AllTables" xmlns=""xmlns:xs="http://www.w3.org/2001/XMLSchema"xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"><xs:element name="AllTables" msdata:IsDataSet="true"><xs:complexType><xs:choice maxOccurs="unbounded"><xs:element name="Players"><xs:complexType><xs:sequence><xs:element name="PlayerID" msdata:AutoIncrement="true"type="xs:int" minOccurs="0" /><xs:element name="Name" type="xs:string" minOccurs="0" /><xs:element name="Age" type="xs:int" minOccurs="0" /></xs:sequence></xs:complexType></xs:element><xs:element name="Teams"><xs:complexType><xs:sequence><xs:element name="TeamID" msdata:AutoIncrement="true"type="xs:int" minOccurs="0" /><xs:element name="Level" type="xs:string" minOccurs="0" /><xs:element name="Player1" type="xs:int" minOccurs="0" /><xs:element name="Player2" type="xs:int" minOccurs="0" /></xs:sequence></xs:complexType></xs:element></xs:choice></xs:complexType></xs:element></xs:schema></code>

View 2 Replies View Related

SQL Reports In Sharepoint Team Services

Mar 6, 2007

I have installed SP2 on my report server and configured for Sharepoint access.



This is all ok.



I am able to use the webpart to create a report view but it wants the report. Is there a way to manage the report server (they way I did with reportmanager) in sharepoint. If so I have not found out how.



any ideas or links to show how I can have teh reports listed in Sharepoint?



Thanks

View 1 Replies View Related

SSIS Setup For Team Work

Jun 22, 2006

I would like to setup SSIS project so that multiple developers can work on same project. I am having issues with protectionlevel properties while another developer opens the package created by other developer.

Can anyone guide me on setting up project so that multiple developers could open the package and run (not simaltaneously though). Also tips on setting up source safe or team foundation will be appreciated!

Thanks

Mahesh

View 3 Replies View Related

SSIS And Team Foundation Server

Dec 14, 2006

First let me say, I play to try and write up something more formal with some details of my recent frustrations of moving my SSIS packages into Team Foundation Server, I'm curious though if I'm the only one doing this.

Its seems that TFS is severely lacking some functionality to make it truly useful for SSIS. First and foremost the lack of it's ability to have an offline working mode and its inability to merge DTSX files (coupled with the fact there is no "Check-out but keep local copy" ability)

Has any one else been using TFS and SSIS, are you finding it to be adequate? I'm hoping to find out where the major issues are so we can bring it to the attention of MS for some updates. I fear this combination might not be extensively used and as a result support will be overlooked by MS in upcoming service packs.

-Dan

View 1 Replies View Related

Team Development Issue When Debugging Applications

Feb 19, 2007

Hi,

At the moment I am working on an asp.net 2.0 application where we use sql server 2005 as database server. We are building this web application with 5 developers in paralel and we noticed that when one developer is debugging the application (hanging in a breakpoint), other developers get a sql time-out exception.

Yes we are using Transactions (System.Transactions namespace) but we have totally no idea why this side-effect is occuring.

Any suggestions or tip is more than welcome !

Grtz

View 3 Replies View Related

Team Foundation Server Vs Source Safe

Apr 3, 2006

Can someone give some comments on which program to use with SSIS ?

View 2 Replies View Related

Giving Members Of A Team Copies Of A Database

Nov 25, 2007

I'm working in a team, and need to share a database between the group - we don't have the luxury of sharing a server and connecting to it.

So What we'd like to do is make a copy of the database schema, and then share that through our repository.

I figured I just need to copy the .mdf file from the sql server database folder, and put it in the visual studio directory to work on it?

So I've tried this, but when I try to create a TableAdapter to the database through visual studio, It gives me an "access denied". I figure this is probably something to do with security settings?

Do you think I'll need to change my Database connection from windows authentication to sql authentication?

I don't really know exactly how to do what I'm trying to do, so any help will be appreciated.

View 1 Replies View Related

For SSIS Dev Team: Is It Possible To Execute Packages Asynchronously?

Mar 28, 2007

Question is in the subject.



The reason I'm asking is because I want a workaround to a problem that a guy is having here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1404349&SiteID=1



-Jamie

View 1 Replies View Related

Power Pivot :: Include Team In Results

Jul 16, 2015

Power Pivot ....  I have a model established that has a date table, Order table, Sales rep table, and a financials table that all feed each other nicely.  

The issue is that some of the individual sales reps are also on sales "Teams" of 2-4 people, and I am not sure how to account for that when I am building pivot tables without relying on slicers and CTRL clicking.  

I also don't want to resort to programmatically duplicating rows via SQL.

View 5 Replies View Related

Contact SQL Serve Database Engine Team

Aug 14, 2007

Is there any web sit or weblog for contacting SQL Serve Database Engine Team?

View 4 Replies View Related

SQL Server Admin 2014 :: Send Email To Team When Job Run Completes?

Mar 10, 2015

I need to send email to the team when the job run completes.

On that server we have two instances one is default (2008R2) and another is 2014 From 2008r2 it will send email to the team.

I tried the profile account using anonymous and enter my company email & password but still the status is showing failed.

I do have smtp account details, I use the same port number but still failing?

View 1 Replies View Related

Visual Studio Team System And SSMS - Anyone Got The Plugin To Work?

May 30, 2006

We have a large number of SPs in our databases and decided it was time to get some source control. I have VSTS team explorer installed and working in visual studio, but I cannot get the plugin for SSMS to work - it just says none in the current source control plug-in dialogue box - nothing when I drop it down either.

Any ideas what I am doing wrong?

View 1 Replies View Related

Email User That New Work Item Added To Team Programatically.

Apr 23, 2008


Hi Team Expert,

I am working on an ASP.NET and c# project. I am creating a page that allow user to add new Workitem to TFS and successfully do so. Now I would like to implement the way to email to notify the user that the new WI is added €¦. Does anyone know how to do this or have an example of how to implement that.
Any help would be greatly appreciated.

Thanks so much

Ddee

View 1 Replies View Related

Analysis :: How To Structure Source Control Folders In Team Foundation Server

Jun 11, 2015

How to integrate current artifacts with TFS ?

For example : We have multiple applications X, Y, Z  each has  its own SSIS solution , SQL Script, MSTR report and documentation.

We move code from Dev to Test and then to PROD

How to structure the folders which will be easy to manage and easy to release ?

View 3 Replies View Related

Live Webcast Tomorrow Essential Team System For Database Developers

Dec 27, 2006

Live Webcast tomorrow

Essential Team System for Database Developers

1 PM Central time 12/28

https://www.clicktoattend.com/invitation.aspx?code=112602

View 2 Replies View Related

T-SQL (SS2K8) :: Union Of Two Tables Returned Back To Main Query

Aug 18, 2014

Currently I have a standard query with a join to several tables. There are two additional tables MAS_CTB and MAS_STB. I would like to do a union between those two tables to get FIELDVALUE which will exist in either the CTB table or the STB table and then have that value be returned with the results of the original query.

I can of course write a UNION from the main query to the CTB and then to the STB table, however it's about 80 dummy fields I would have to replicate in the union which is why I was wondering if there was a more simple way.

Main Query:
SELECT Field1, Field2...Field80
From Table1
Join Table2

Union Portion:
Select FieldValue <------
From MAS_CTB
UNION
Select FieldValue <-----Return to main query above
From MAS_STB

View 3 Replies View Related

Team Project In TFS - SSIS Project Sets Itself As The Startup Project

May 29, 2008



For some reason in a Team Foundation Team Project that has multiple project types (SSRS, SSIS, WebSite, C# Business DLL...), the SSIS project makes itself the startup project to the team project. If I explicitly set another project as the startup project to the team project and then select an SSIS package in the SSIS project in the team project, the SSIS project becomes the startup project automatically.

I am using Visual Studio 2005 SP1.

View 3 Replies View Related

T-SQL (SS2K8) :: How To Show ED / CESS In Query

Nov 5, 2014

Detail:

create table detail
(
grn_no varchar(55),
po_no varchar(50),
vendor_no numeric(10,2),
Formul_id varchar(50),
Adjust_code varchar(50),
adjus_value float
)

insert into detail values

('Grn/0001/14-15','po/00011/14-15/','5000','ED12','ED12','500')
values ('Grn/0001/14-15','po/00011/14-15/','5000','CST','CST12','50')
values ('Grn/0001/14-15','po/00011/14-15/','5000','CES6','CES6','5')
values ('Grn/0001/14-15','po/00011/14-15/','5000','VAT','VAT','0')
values ('Grn/0001/14-15','po/00011/14-15/','5000','HCES2','HCES2','2.50')

i wanna display ED ,CESS ,HCESS,VAT in separate columns in output How to that?

View 4 Replies View Related

T-SQL (SS2K8) :: Query To Show Table Products With Its Modes

Nov 28, 2014

Query that show me a Products Tables and its models, 1 product could have 2 or more models

Example Table Products

id Name
1 Product 1
2 Product 2
3 Product 3

Example Table Models

idmodel idproduct model
1 1 Model 1
2 1 Model 2
3 1 Model 3
4 2 Model 1
5 2 Model 2
6 3 Model 1

And I want to show:

Product Model
Product 1 Model 1
Model 2
Model 3
Product 2 Model 1
Model 2
Product 3 Model 1

How can I query to show that?

View 5 Replies View Related

T-SQL (SS2K8) :: Query To Show Database Objects Last Change

Mar 17, 2015

If I skip those objects that were deleted, is there a query that I can run off Master/MSDB that will show all database objects and when they were last modified, or create date if it has not been modified?

View 7 Replies View Related

T-SQL (SS2K8) :: How To Show Multiple Values In Column Of Join Query

Nov 6, 2014

in my table i ve the column of item code which contains '1000' ,'2000' ,'3000' series i jus wanna display the output of item codes '1000','2000'series and some of ('3000019','3000020','3000077','3000078').

i tried in my join query

these code left(itemcode,4) in ('1000','2000') or itemcode in ('3000019','3000020','3000077','3000078')

its taking so much of time how t o solve ?

View 1 Replies View Related

Subreport Doesn't Show On The Main Report

Jun 5, 2007

Here's my problem. I have a subreport that shows data and labels if data is returned. However, if no data is returned, the subreport doesn't show at all. I have a message on the subreport I would like to show if no data is returned, but the subreport won't show at all. Any ideas??



Thanks

View 1 Replies View Related

Change Display Message When Else Is 0 To Show (Non Active Member)

Feb 11, 2015

I want to change the display message when else is 0 to show "non active member", how to edit this query for this result .

---------------------------------------------------------------
Current Query

cast(sum(case when trx_date > dateadd(month, -3, getdate()) then 1 else 0 end) as varchar(10))+ ' Active Member := '+ user_member
---------------------------------------------------------------

I try this query but not work and prompt error.

cast(sum(case when trx_date > dateadd(month, -3, getdate()) then 1 else "non active" end) as varchar(10))+ ' Active Member := '+ user_member
----------------------------------------------------------------

View 1 Replies View Related

T-SQL (SS2K8) :: Creating A Query To Show Data Side By Side

Nov 26, 2014

I am working to create a phone list that will contain Last Name, First Name, and Phone Number sorted by last name. For printing purposes I would like to have three columns of data instead of the standard of one column.

Is it possible to create a query to present data in three columns showing the data side by side?

View 9 Replies View Related

Show Changes Between Records In Main Table And Audit Table

Aug 2, 2006

Hallo
i have two tables, MainTable and MainTableAudit: the second one keeps DML auditing via triggers.
I'm trying to build a query to highlight changes occurred to fields between the MainTable record and its audited records in MainTableAudit, for example, let's suppose i entered an item with some wrong attributes, and edited it three times:
MainTable record contains the latest and current version
ID002|Hitchhikers Guide to the Galaxy|Sci-fi|240 pages
MainTableAudit contains edited ID002 versions
ID002|Hitchhikers Guide to the Galaxy|Sci-fi|232 pages|2006-07-08 08:32:12
ID002|Hitchhikers Guide to the Galaxy|Sci-fi|212 pages|2006-05-08 10:54:02
ID002|Hitchhikers Guide to Galaxy|Sci-fi|222 pages|2006-07-04 11:42:16

I would like to build a report like this:
first insertion: Hitchhikers Guide to Galaxy|Sci-fi|222 pages
modified on 2006-07-04 11:42:16: field "Title" changed from "Hitchhikers Guide to Galaxy" to "Hitchhikers Guide to the Galaxy", field "PageNo" changed from "222" to "212"
modified on 2006-05-08 10:54:02: field "PageNo" changed from "212" to "232"
modified on 2006-07-08 08:32:12: field "PageNo" changed from "232" to "240"
current version: Hitchhikers Guide to the Galaxy|Sci-fi|240 pages

i'd prefer to use T-SQL and keep all into a single place (a view or storedprocedure), or at least to use reporting services; btw i would like to avoid coding web pages or hosted applications.

View 1 Replies View Related

T-SQL (SS2K8) :: How To Show Total Sales

Mar 10, 2014

My Table struct

create table cust
(
cust_id int ,
city varchar(20),
pincode int,
sales int,
latitude float,
longitude float

[code]....

like i wanna display each pincodes how to make a code?

View 6 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved