Hi all
I have the following table schema and their details as follows:
Code Block
CREATE TABLE #Tbl_Project
(
ID varchar(40),
Name varchar(50)
)
GO
CREATE TABLE #Tbl_User_Project
(
ID int,
ProjectID varchar(40),
Role varchar(20)
)
GO
INSERT INTO #Tbl_Project VALUES('PRJ1','Project 1')
INSERT INTO #Tbl_Project VALUES('PRJ2','Project 2')
INSERT INTO #Tbl_Project VALUES('PRJ3','Project 3')
GO
INSERT INTO #Tbl_User_Project VALUES(1,'PRJ1','PM')
INSERT INTO #Tbl_User_Project VALUES(2,'PRJ1','TL')
INSERT INTO #Tbl_User_Project VALUES(3,'PRJ1','TM')
INSERT INTO #Tbl_User_Project VALUES(4,'PRJ2','PM')
INSERT INTO #Tbl_User_Project VALUES(5,'PRJ2','TL')
INSERT INTO #Tbl_User_Project VALUES(6,'PRJ3','PM')
INSERT INTO #Tbl_User_Project VALUES(7,'PRJ3','TL')
INSERT INTO #Tbl_User_Project VALUES(8,'PRJ3','TL')
GO
--Required Output
--ProjectID ProjectName PM:TL:TM Ratio HeadCount
--PRJ1 Project 1 1:1:1 3
--PRJ2 Project 2 1:0:1 2
--PRJ3 Project 3 1:0:2 3
DROP TABLE #Tbl_Project,#Tbl_User_Project
This is just sample of the values. The ratio obtained should be as follows:
Assuming for a Project 1 there are 2 - PM's , 6-TL's , 15 - TM's then the ratio should be 1:3:7.5 . So please help me in this regard.
I am cutting my teeth on star schema design. I have a simple star schema I am building for Headounct analysis at work. I have a factless fact table where a row represents a head in the company. Each head is toed to a particulat week in a Date dimension tabel. There are additional dimensions for things like gender, ethnicity, marital status, age, etc. Now in my department dimension - it's hierarchical. In the DimDepartmnet there is a department which belongs to a company. Comapnies belong to divisions. Now the fun part. Each division has a headcount target for each year. Up to this point I am in a perfect star schema (no snow flaking). How would I integrate in this concept of a headcount target for each division for a given year?
We are using cognos on top of this star schema to provide reporting and analysis services if that is relevant. From the Star Schema design stand point... any thoughts?
SQL question here: I am setting up a blog, and I would like the data list to default to the most recent post ONLY. How would I define the filter to get the most recent post? I have a publishing date in my table, and I can sort the post, but I have no clue how to select just one row to display.
I can get it to work by adding a dropdown list the is bound to the publishing date column, then retreving the post based on the control, but I don't want to use the control. (Making the control not visible is not an option.
I have an app that needs to retrieve the list of tables in an SQL database. I see that there is a way to do this with the OLEdb provider (GetOleDbSchemaTable() ), but I can't find a sibling method in SQL. IS there one/ Any Ideas?
I am storing images within and image field in sql, however when I go to get the data I have two issues.
the first issue is that my image is linked to information on another table but the only way that i have seen getting the image back is to do a:
response.binarywrite(mydatareader.item("image"))
but i am hoping to be able to find a way to use the template functionality with datagrid to align the picture with the information on the table. i am storing the image type and size with the image as well.
the second problem might be related to the load, i am not sure. sometimes, for some images and it just shows the frame of the image with the red x in the middle. The load appears to work (no errors) but since we cannot view them properly we dont know.
Our application wants to store all data in sql so that we can secure it and relate it to projects. in addition, we hope to be able to use this function to upload all sorts of binary documents from jpg, gif, doc, xls, visio, and others as we can. any issues with that process???
we would like to be able to display them directly in the browser where we can but all users have standard office.
any suggestions on either and hopefully both of these situations? we have sort of hit a brick wall.
hi. I am at a new stage in our project and need to figure out how to insert/retreive MP3/Wav files. The way we want to do this is have the file stored on the harddrive while a link to its location is stored in the database.
I have posted this search in other newsgroups and have gotten a lot of philosophical responses, but what i really am looking for is code examples. Please, what i would like to see is "how" this id done in code. If you have done this on your site, i'd really appreciate your posting code here or emailing me examples. Or, if you know of a really good explainaition of how to go about storing/retreiving audio files in the way i mentioned using MS SQL Sever 2000, I'd be equally indebted.
I hope I have explained myself clearly. Thank you for your assistance!
Last week I discovered a server runing a sql database for a vendor application. Last year sometime one of our former employees created a dts to populate some data in peoplesoft from this database. But, over this last weekend, the vendor came in and uninstalled and reinstalled sql server and only restored their specific database. All I have is backups of msdb and master and I'm not sure how to restore our dts package. Any clues would be appreciated.
I have a serious problem struggling with sql query the problem is i have a table below
Sno CarId carcharacterstics Charactersticvalue
1. Mercides01 Color Red 2. Mercides01 EngineType Desile 3. Mercides01 Speed/Hr 350KM 4. Maruthi01 Color Red 5. Maruthi01 EngineType Desile 6. Maruthi01 Speed/Hr 250KM 7. BMW Gears Automatic 8. BMW EngineType Desile 9. BMW Speed/Hr 250KM
Now i need to retrieve the carid whose color is red and Engine type is desile the query i have written is
select carid from carchar where ((characterstics='Color' and charactersticvalue=Red) and (characterstics='EngineType' and charactersticvalue=Desile))
But I am not able to retrieve the values. I can retrieve the values correctly if i mention only one characterstics and its value but i cant retrieve if i mention more than that
Hi, im trying to insert values into 2 different tables. The primary key in the first table is an auto increment field, which is then the foreign key in the 2nd table.Is there any way to retrieve the autogenerated value before performing the second insert. I have done some searching on the matter, but to no avail.I tried using LAST_INSERT_ID() but i get the error message that it is not a recognized function name. The other option is using @@identity but i cant see how to put that into my SQL statement.Can anyone help please.Thanks in advanceGareth
fields: assassinid int, identity, primary key assassinname nvarchar assassincallsign nvarchar knifekills int sniperkills int stealthkills int fibergarotekills int
supposed i want to retreive only those records whose type of kills is on a given list, how do i do that? the list is actually dynamic since the kill types is actually coming from a checkbox on my page: [] Knife Kills [] Sniper Kills [] Stealth Kills [] Fibergarrote Kills
supposed i will only check Knife Kills, it will only retreive those records whose knifekills > 0
or if i check Snipers Kills and Stealth Kills, then those records whose sniperkills and stealthkills is > 0.
We are having a Windows Installer Program (32 bit) which calls the SQLDMO to get the list of installed instances in a 64 bit 2005 sql server machine. This fails as the windows installer tries opens up the 32 bit sqldmo and there is no instance of 32 bit sql server on this machine. Is there any way by which sqldmo can return all 64 bit sql server instance when called from a 32 bit Application?
Due to circumstances beyond my control, I have to restore a full database backup from October 1, 2005 and then over 4400 transaction logs to bring a database up to Jan 01, 2006 on a development server (storage team lost all daily full backups from 10/02/2005 to 01/01/2006). Since the backups were not taken on this machine originally, the information I would pull from the sysjobhistory tables to get the transaction backup names is not available. Does anyone have a link or a script to pull the filenames from the backup directory into sql server so I can build a dynamic script to apply the tran logs instead of running all 4400+ of them by hand? Thanks, Tom
I'm trying to figure out how to retreive connection strings from the machine.config instead of storing the conn strings in a *.dtsconfig file. The reason that I'm trying to use the machine.config is for the simple reason that I'll be able to manage all of the conn strings in one location on each server. (Instead of mutliple dtsconfig files.) Is it posible to utilize the machine.config in SSIS? If so, could someone explain how, or better yet, have an example?
The same as following Tree as you can see 1__ | 2__ | 4__ | 6 | __ 3__ 5
I need a query to return the following Result. I think it is possible only through Nested sub-Queries But i don't know how to do that Could any one help me.?
Could someone tell me how to get the data from all tables of the database in the form of insert script? We are moving our databse from SQL Server 2000 to SQL Server 2005. The scripts for the Database, Tables, Views , Procedures, Functions have been obtained and it is only the data that is remaining. Some are small tables with 5 to 6 columns but there are some with 50 odd columns. A friend of mine told me about a procedure that returns a dataset with INSERT statements by passing a table name as a parameter. Such procedure would be of great help.
I am having one querry regarding the same line. In my stored procedure i am fetching the data from one table containing upto 5 to 6 million rows I made use of index in my database but then also I cant optimise my execution time of that sp. Please help me out of this problem.
hi,i have a database where there is 10 feilds one of which is of datetimeI have to select a row from the table which has two search ConditionsFrist i have to select from the table who has ( say GroupId =1) and From these rows( whose GroupId=1) I have to select at the row that is last entered Max(Date) Thanks InAdvance
I am facing a problem in writing the stored procedure for multiple search criteria.
I am trying to write the query in the Procedure as follows
Select * from Car where Price=@Price1 or Price=@price2 or Price=@price=3 and where Manufacture=@Manufacture1 or Manufacture=@Manufacture2 or Manufacture=@Manufacture3 and where Model=@Model1 or Model=@Model2 or Model=@Model3 and where City=@City1 or City=@City2 or City=@City3
I am Not sure of the query but am trying to get the list of cars that are to be filtered based on the user input.
I am trying to create a report using Reporting Services.
My problem right now is that the way the table is constructed, I am trying to pull 3 seperate values i.e. One is the number of Hours, One is the type of work, and the 3rd is the Grade, out of one column and place them in 3 seperate columns in the report.
I can currently get one value but how to get the information I need to be able to use in my reports.
So far what I've been working with SQL Reporting Services 2005 I love it and have made several reports, but this one has got me stumped.
Any help would be appreciated.
Thanks.
I might not have made my problem quite clear enough. My table has one column labeled value. The value in that table is linked through an ID field to another table where the ID's are broken down to one ID =Number of Hours, One ID = Grade and One ID= type of work.
What I'm trying to do is when using these ID's and seperate the value related to those ID's into 3 seperate columns in a query for using in Reporting Services to create the report
As you can see, I'm attempting to change the name of the same column 3 times to reflect the correct information and then link them all to the person, where one person might have several entries in the other fields.
As you can see I can change the names individually in queries and pull the information seperately, it's when roll them altogether is where I'm running into my problem
Thanks for the suggestions that were made, I apoligize for not making the problem clearer.
Here is a copy of what I'm attempting to accomplish. I didn't have it with me last night when posting.
--Pulls the Service Opportunity
SELECT cs.value AS "Service Opportunity"
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE ca.name = 'Service Opportunity'
--Pulls the Number of Hours
SELECT cs.value AS 'Number of Hours'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Num of Hours'
--Pulls the Person Grade Level
SELECT cs.value AS 'Grade'
FROM Cstudent cs
INNER JOIN cattribute ca ON ca.attributeid =cs.attributeid
WHERE ca.name ='Grade'
--Pulls the Person Number, First and Last Name and Grade Level
SELECT s.personnumber, s.lastname, s.firstname, cs.value as "Grade"
FROM student s
INNER JOIN cperson cs ON cs.personid = s.personid
INNER JOIN cattribute ca ON ca.attributeid = cs.attributeid
WHERE cs.value =(SELECT cs.value AS 'Grade'
WHERE ca.attributeid = cs.attributeid AND ca.name='Grade')
I have a requirement where in i have around 15 different flat files , filenames are fixed but folder path can be changed(i think i should use a variable for folder path). These 15 files data should go to their respective tables in the database.
Whether I need to create separate data flow task for each file or separate package? In addition to these, example : while importing product data into product table, if product ID already exists, we need to ignore it and upload only the new records.
I am in the process of creating a Report, and in this, i need ONLY the row groups (Parents and Child).I have a Parent group field called "Dept", and its corresponding field is MacID.I cannot create a child group or Column group (because that's not what i want).I am then inserting rows below MacID, and then i toggle the other rows to MacID and MacID to Dept.
I'm trying to create an email report which gives a result of multiple results from multiple databases in a table format bt I'm trying to find out if there is a simple format I can use.Here is what I've done so far but I'm having troble getting into html and also with the database column:
I'm trying to get some XML data into SQL Server but i ran into problem when inserting the data (multiple orders with multiple order details) using a single sproc. Is it possible, or do I have to do in some other way? :confused:
I simplified my example to this: ----------------------------- --CREATE PROCEDURE sp_InsertOrders AS
DECLARE @docHandle INT, @xmlDoc VARCHAR(4000), @orderID INT
--DROP TABLE #Orders CREATE TABLE #Orders ( OrderId SMALLINT IDENTITY(1,1), FkCustomerID SMALLINT NOT NULL, OrderDate DATETIME NOT NULL )
--DROP TABLE #OrderDetails CREATE TABLE #OrderDetails ( OrderDetailsId SMALLINT IDENTITY(1,1), FkOrderID SMALLINT NOT NULL, ProductID SMALLINT NOT NULL, UnitPrice SMALLINT NOT NULL )
INSERT INTO #Orders (FkCustomerID, OrderDate) SELECT CustomerID, OrderDate FROM OpenXML(@docHandle, 'Orders/Order', 3) WITH ( CustomerID INTEGER, OrderDate DATETIME )
SET @OrderID = @@IDENTITY;
--INSERT INTO #OrderDetails (@OrderID, ProductID, UnitPrice) SELECT @OrderID AS OrderID, ProductID, UnitPrice FROM OpenXML(@docHandle, 'Orders/Order/OrderDetails', 3) WITH ( ProductID INTEGER, UnitPrice INTEGER ) -----------------------------
All orders are inserted first which makes the use of @@IDENTITY incorrect (it works fine if you insert a single order with multiple order details). Since it was quite some time since I last worked with SQL I am not sure if am doing it the right way... :confused: :confused: Anybody out there who knows how to solve the problem?
I concatenate multiple rows from one table in multiple columns like this:
--Create Table CREATE TABLE [Person].[Person_1]( [BusinessEntityID] [int] NOT NULL, [PersonType] [nchar](2) NOT NULL, [FirstName] [varchar](100) NOT NULL, CONSTRAINT [PK_Person_BusinessEntityID_1] PRIMARY KEY CLUSTERED
[Code] ....
This works very well, but I want to concatenate more rows with different [PersonType]-Values in different columns and I don't like the overhead, of using the same table in every subquery ([Person_1]). Is there a more elegant way to do this, without using a temp table or something else?
I need to update multiple columns in a table with multiple condition.
For example, this is my Query
update Table1 set weight= d.weight, stateweight=d.stateweight, overallweight=d.overallweight from (select * from table2)d where table1.state=d.state and table1.month=d.month and table1.year=d.year
If table matches all the three column (State,month,year), it should update only weight column and if it matches(state ,year) it should update only the stateweight column and if it matches(year) it should update only the overallweight column
I can't write an update query for each condition separately because its a huge select
I'm trying to create a database that takes specific information from a number of databases on different servers to make some reporting that we have much easier.
I'm pretty new to SQL so I'm not sure of the best way to proceed. I read an article that suggested I use the OPENROWSET command. The problem is, the version of SQL that came with one of the programmes we use is limited and will not allow you to turn on the allow "Ad Hoc distributed Queries" so the SLQ statement will not execute.
I'm confused why it won't let me to connect through ODBC as I've created a web page that selects data from this database with no problems!
Here is the SQL statement that I've written to make sure it is the correct one (on the msdn library page it said that this was the ODBC connection):
SELECT a.* FROM OPENROWSET('MSDASQL','DRIVER=(SQL Server);SERVER=APPOLOACT7;UID=sa;PWD=***************', 'SELECT * FROM MDCTestAndDev.dbo.TBL_CONTACT') AS a
I've also created the ODBC connection using the tool on Administration Tools>Data Sources ODBC
Any help would be greatly appreciated (also any ways of selecting from one database and inserting it into another will be helpfull)
After hitting limitations in the SQL CLR world that bar us from invoking COM objects we are forced to use windows services to read the messages off the Service Broker Queues. Unfortunately we loose the auto activation feature in the Queues, but we can still read messages and perform the SQL work under one transaction.
We are going to attempt to take N messages simultaneously from the Queue, though N instances of a windows service. If the messages send to the queue are one message per conversation, will we be able to achieve having N readers take messages off simultaneounsly?
Thank you very much,
Lubomir
P.S. if anyone has a better approach to obtaining the message in "out of sql code" or invoking external (not assemblies stores in SQL server) code libraries, that would be etremely nice to hear. I have thought about invoking a web service through CLR, but that is probably too much overhead - MSMQ seems much more appealing than a web service;
