SQL 2000: Query Question - Input Data Within Two Tables And 'between' Table
May 1, 2008
Hello specialists.
Maybe this is the wrong formum but I've got a question for which you probably have the answer, i hope.
Situation
------------
John is member of Group_A and Group_B
Bill is member of Group_B and Group_C
Allison is member of Group_A and Group_E
How can I create a query to input Allisons username into table 1 and groupmembership into table 2. Also updating the relationship within table3 must be done automaticaly. I want to avoid duplicate records.
The final situation I want is given in red text.
The relationships between the tables are as follows
-------------------------------------------------------------
Table1 (PK)ID-Userinfo [ONE] <------------> [MANY] Table3 ID-Userinfo
Table3 (PK)ID-GroupInfo [MANY] <------------> [ONE] Table2 (PK)ID-GroupInfo
Table1: UserInfo
------------------------------
(PK)ID-Userinfo UserName
1 John
2 Bill
3 Allison
Table2: GroupInfo
------------------------------
(PK)ID-GroupInfo GroupName
1 Group_A
2 Group_B
3 Group_C
4 Group_E
Table3: MemberOf
------------------------------
(PK)ID-MemberOf ID-UserInfo ID-GroupInfo
1 1 1
2 1 2
3 2 2
4 2 3
5 3 1
6 3 4
I hope you can help me cracking this nut.
Thx in advance. Greetings Fred
View 3 Replies
ADVERTISEMENT
May 1, 2008
Hello specialists.
Maybe this is the wrong formum but I've got a question for which you probably have the answer, i hope.
Situation
------------
John is member of Group_A and Group_B
Bill is member of Group_B and Group_C
Allison is member of Group_A and Group_E
How can I create a query to input Allisons username into table 1 and groupmembership into table 2. Also updating the relationship within junction-table3 must be done automaticaly. I want to avoid duplicate records.
The final situation I want is given in red text.
The relationships between the tables are as follows
-------------------------------------------------------------
Table1 (PK)ID-Userinfo [ONE] <------------> [MANY] Table3 ID-Userinfo
Table3 (PK)ID-GroupInfo [MANY] <------------> [ONE] Table2 (PK)ID-GroupInfo
Table1: UserInfo
------------------------------
(PK)ID-Userinfo UserName
1 John
2 Bill
3 Allison
Table2: GroupInfo
------------------------------
(PK)ID-GroupInfo GroupName
1 Group_A
2 Group_B
3 Group_C
4 Group_E
Table3: MemberOf
------------------------------
(PK)ID-MemberOf ID-UserInfo ID-GroupInfo
1 1 1
2 1 2
3 2 2
4 2 3
5 3 1
6 3 4
I hope you can help me cracking this nut.
Thx in advance. Greetings Fred
View 7 Replies
View Related
Jul 15, 2014
I have a cluster hosting multiple GP databases and a second for my data-warehouse I am playing around with (personal project).
I have scripts that pull data from all the DB's to input into the DW's tables(Customers,Reps,Hub....)
An example of my branch script :
select interidas BranchID,
cmpnynamas BranchDesc,
address1as BranchAddressLine1,
address2as BranchAddressLine2,
address3as BranchAddressLine3,
zipcodeas PostalCode
from dynamics..SY01500
Where interid in ('comp1', 'comp2', 'comp4', 'comp5')
what would be the best way to using these scripts pull the data to my testDW and not have duplicate data issues?
I was thinking of using a staging DB on the GP cluster and then building an import data package to run nightly. the issue i had was how do i avoid duplicate data ?
View 0 Replies
View Related
Sep 30, 2015
I have a delimited text file with 650+ columns. The sum of the column lengths of a single row, if fully populated, exceeds 30K bytes. The "killer" fields lengthwise are the "Description" fields. If they were removed from the input file, the remainig columns would occupy about 5000 bytes, which is within SQL max row length.
Can SSIS be used to created these two tables? (one without description fields, the other with those field but arranged vertically in the table rows).
The fundamental issue is I can not import a single file row into a sql table because that row length could exceed the max byte count for a row.
View 8 Replies
View Related
Nov 14, 2007
Hi,
I have Table A . we already have 80 columns . we have to add 65 more columns.
we are populating this table from oracle .and we need to populate those 65 columns again from the same table.
Is it a better idea to add those new 65 columns to the same table or new table.
If we go for the same table then loading time will be double, If I go for new table and If i am able to run both the packages which loads table data from same oracle server to difffrent sql tables then we should be good. But if we run in to temp space issues on oracle server . Then i have to load the two tables separately which consumes the same time as earlier one.
I was thinking if there is a way in SSIS where I can pull data from same oracle table in to two diff sql tables at same time?
View 1 Replies
View Related
Jul 17, 2007
Is it possible to have a replicated table that you can open from and input data manually?
That is, I've got a table that I am replicating data to and I want to add some manual data to this table, every now and then.
Will this break replication?
www.beyonder422.com
View 4 Replies
View Related
Jun 4, 2015
One people created a word input file (15 pages, including check boxes, text boxes, drop down lists...). Is it possible to save data in word input file to SQL table?
View 1 Replies
View Related
May 26, 2007
I have a table that I am basically reduplicating a couple of times for each part of this database that I want to create.Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.How can I make this an easier way of creating these similar tables without retyping all these columns over and over again?Thanks in advance.
View 4 Replies
View Related
Feb 9, 2012
I'm trying to insert data into a table from two tables into a single table along with a hard coded value.
insert into TABLE1
(THING,PERSONORGROUP,ACCESSRIGHTS)
VALUES
((select SYSTEM_ID from TABLE2 where
AUTHOR IN (select SYSTEM_ID from TABLE2 where USER_ID
=('USER1'))),(select SYSTEM_ID from TABLE2 where USER_ID
=('USER2')),255)
I get the following-
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Do I need to use a cursor?
View 5 Replies
View Related
Feb 2, 2012
I am trying to export the data from a number of MSSQL 2000 tables to UTF8 csv files.
Using the bcp that came with MSSQL 2000 didn't work (UTF8 related), so I am now accessing the 2000 database from within MSSQL 2008 R2 Management Studio and use its Import and Export wizard that does make UTF8 exports possible.
But it fails. This is the error I got for one table.
Code:
- Validating (Error)
Messages
Error 0xc00470d4: Data Flow Task 1: The code page on input column "cmsGUID" (74) is 1252 and is required to be 65001. (SQL Server Import and Export Wizard)
Error 0xc00470d4: Data Flow Task 1: The code page on input column "PublicationStatus" (79) is 1252 and is required to be 65001. (SQL Server Import and Export Wizard) ....
What do I have to do?
View 2 Replies
View Related
Dec 11, 2007
Hi,
I have a Users table in Oracle database and same table (Users) in SQL Server 2000 database. I want to create a DTS Package through which I can copy the data from Oracle database to SQL Server 2000 database. This package should run automatically at mid-night daily so that if there are some entries done in Oracle database then it get copied in SQL Server 2000 database. Also is there any way to copy only those entries from Oracle database which are not present in SQL Server 2000 database. Please help me in this regard as I am new to DTS.
Thanks
Rohit
View 1 Replies
View Related
Oct 25, 2004
I'm running asp.net on an XP machine with MSDE 2000 as the database. I want to create a user table with a userid and password. I have a second table that contains details about the user such as home address and phone number etc. When the user first signs up, I want their userid to go into the user table and the user detail table. In my SQL insert command, I can't get @userId to go into both tables. So my question is how do I get the userID to go into both tables or is there a better way of doing this altogether?
Thanks,
Tom
View 1 Replies
View Related
Aug 6, 2007
Hello,
I need to create a view which will include an additional table every week of our financial year.
Example: Every week we create a table called Sales_YYYY_WW where YYYY is the Year and WW is the Week, so after the first week we have the table Sales_2007_01 and after the second week we have Sales_2007_01 and Sales_2007_02 etc.. These tables are created evey week and don't exist at the start of the financial year.
I'd like to create a view at the beginning of each year to encompass each table evey week as it is created. If all of the tables were there at the start of the year it would be simple:
CREATE VIEW VIEW_SALES_2007
AS
SELECT * from Sales_2007_01
UNION ALL
SELECT * from Sales_2007_02
etc.
But I don't know how to achieve this when the tables don't already exist. I don't want to have to edit the view each week to add in the new table.
Can anyone out there help me?
View 3 Replies
View Related
Jun 4, 2008
Hi All,
I have a Problem while updating one table data from another table's data using sql server 2000.
I have 2 tables named TableA(PID,SID,MinForms) , TableB(PID,SID,MinForms)
I need to update TableA with TableB's data using a single query that i have including in a stored procedure.
View 2 Replies
View Related
Aug 11, 2005
Hi:
I have three tables in my db. One is clients, other calls, and other visits.
I want to get all the calls and visits of all clients in my db every row in each table separate in one row in the results table.
How can I do it?
View 3 Replies
View Related
Jul 23, 2005
Hello,I'm not an expert in SQL, if you could help me for that littleproblem:I had tree simple tables with their fields:[Client] IdClient, Param[Sale] IdSale, IdClient, Param[Param] IdParam, ValueHow can I retrieve a recordset with this columns ?IdClient, IdSale, ValueOfParamClient, ValueOfParamSaleThe problem is that I can retrieve a Param for one table (Client orSale) like this request :SELECT Client.IdClient, Sale.IdSale, Param.ValueFROM(ClientINNER JOIN SaleON Sale.IdClient = Client.IdClient)LEFT JOIN ParamON Param.IdParam = Sale.ParamBut how can I retrieve the Param of the another table in a simplequery ? (because I would also like that it works for access)Thank for your help,Marc
View 4 Replies
View Related
Nov 19, 2014
I'm using SS 2012.
I started with an inline table returning function with a hard coded input table name. This works fine, but my boss wants me to generalize the function, to give it in input table parameter. That's where I'm running into problems.
In one forum, someone suggested that an input parameter for a table is possible in 2012, and the example I saw used "sysname" as the parameter type. It didn't like that. I tried "table" for the parameter type. It didn't like that.
The other suggestion was to use dynamic sql, which I assume means I can no longer use an inline function.
This means switching to the multi-line function, which I will if I have to, but those are more tedious.
Any syntax for using the inline function to accomplish this, or am I stuck with multi-line?
A simple example of what I'm trying to do is below:
Create FUNCTION [CSH388102].[fnTest]
(
-- Add the parameters for the function here
@Source_Tbl sysname
)
RETURNS TABLE
AS
RETURN
(
select @Source_Tbl.yr from @Source_Tbl
)
Error I get is:
Msg 1087, Level 16, State 1, Procedure fnTest, Line 12
Must declare the table variable "@Source_Tbl".
If I use "table" as the parameter type, it gives me:
Msg 156, Level 15, State 1, Procedure fnTest, Line 4
Incorrect syntax near the keyword 'table'.
Msg 137, Level 15, State 2, Procedure fnTest, Line 12
Must declare the scalar variable "@Source_Tbl".
The input table can have several thousand rows.
View 9 Replies
View Related
Jun 9, 2004
I am trying to QA data being put into a SQL database by an outside source (from Excel) and therefore need to compare two (for the sake of simplicity) tables within a database to one another.
The two tables should contain the same data, and the QA process is meant to find and report any discrepancies. The column names are slightly different.
My question then is, is it possible to write a simple SQL query which will compare the data from the two tables and select only those rows where the data in any given column does not match? My data is mostly text, not numerical.
I'm very new at using SQL and my knowledge of the query syntax is very basic.
Thanks for any help in advance!
~Lacy
View 2 Replies
View Related
Jul 20, 2005
I need to run a query which will pull data from two tables and appendit as one when it displays the result. The data are in two tables. Butthe result set will be identical in terms of number of columns. I wantto display it one set below the other.This is for pay history. From 2003 we have a new payroll system. Till2002 we used to have a different system. But I need to run a querywhich will pull the data for an employee for the last one year. So theinformation is spread out between these two tables. Both these tablesare in SQL Server databases.I want to write a Stored Procedure. I can use Shape/Append but I thinkit doesnt work on QA/Stored Proc. It needs an OLEDB.How can I write the query. I dont want to use temporary tables and doinserts.ThanksGIRISH
View 1 Replies
View Related
Feb 18, 2015
I have three tables A, B, C respectively. Table C is used to map table A and B. Three tables are below:
Table A:
Table B:
Table C:
So what query do I need write to have table like below?
View 3 Replies
View Related
Jul 8, 2013
I have 2 requests for desperate Hélio..
1) is there any way to run a query over a query without having to create a table with the results of the first query? (would drop table work? If so, how?
2) how can i define input variables the same way i do in excel? I am trying to run a couple of simulations based on 2 core inputs (in excel i would just do a data table)
View 7 Replies
View Related
Jul 14, 2007
Dear FriendsI need to get the data in the required format. Please help----------------------Table TripSheet----------------------Id,TSNo.,JourneyDate(ddmmyy),CustName,RateType,VehicleNo., BillId, journey hours, journey kmDATA
1, 234, 1-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 2 hrs, 60 KM2, 235, 2-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 3 hrs, 30 KM3, 236, 3-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 2 hrs, 60 KM4, 237, 4-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 4 hrs, 40 KM5, 238, 5-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 2 hrs, 60 KM
6, 239, 1-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 2 hrs, 60 KM7, 240, 2-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 3 hrs, 30 KM8, 241, 3-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 2 hrs, 60 KM9, 242, 4-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 4 hrs, 40 KM10, 243,5-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 2 hrs, 60 KM----------------------------Table BillTripSheet-----------------------------Id,BillId,VehicleNo., TotalJourneyHours,TotalJourneyKM,SlabApplied, MinAmt, ExtAmtDATA
1, 234, Vehi45, 13hrs, 250km, SlabA, 500, 502, 234, Vehi99, 13hrs, 250km, SlabA, 500, 50
I need to display data to be displayed in the following format in report(grouping based on vehicle no.)
1, 234, 1-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 2 hrs, 60 KM2, 235, 2-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 3 hrs, 30 KM3, 236, 3-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 2 hrs, 60 KM4, 237, 4-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 4 hrs, 40 KM5, 238, 5-1-2007, Samson Pvt.Ltd, BPORates, Vehi45, 234, 2 hrs, 60 KM----------------------------------------------------------------------------------------------------------1, 234, Vehi45, 13hrs, 250km, SlabA, 500, 50---------------------------------------------------------------------------------------------------------
6, 239, 1-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 2 hrs, 60 KM7, 240, 2-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 3 hrs, 30 KM8, 241, 3-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 2 hrs, 60 KM9, 242, 4-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 4 hrs, 40 KM10, 243,5-1-2007, Samson Pvt.Ltd, BPORates, Vehi99, 234, 2 hrs, 60 KM----------------------------------------------------------------------------------------------------------2, 234, Vehi99, 13hrs, 250km, SlabA, 500, 50----------------------------------------------------------------------------------------------------------
The Calculation logic is lengthly and the number of records involved is huge.also, only the manager has authority to generate the Bill and rest of the team can view the bill report
So i cannot do the calculations while fetching the records, Bill has to be generated and stored. How do i write the query to fetch the data from the two tables "TripSheet" and "BillTripSheet" ? Please Help
ReagardsSara
View 6 Replies
View Related
Sep 17, 2015
Table 1 has "Gender" field with "Male" and "Female" in it, table 2 has "Gender" field with "M" and "F" in it. a query to compare data and list the differences.
View 4 Replies
View Related
Apr 21, 2015
I want to retrieve the data from table "Document" and i need to check the below condition using 3 tables.
Document.ID=Project.ID=Group.ID
Here Project and Group is an another table.
Query : Select Document.Name from Document, Group, Project where
Document.ID = Group.ID and Document.ID= Project.ID.
is this right a way?
View 3 Replies
View Related
Mar 8, 2012
I would like to run the same query on multiple tables. So say I have a list of tables
@tableList = a|b|c|d
And then I have my query looping through the tables
for (@table in tableList)
{
update from @table
set = ''
}
Is there a simple way to do this in an mssql query, if so how do I get to loop through the query switching the table name?
View 4 Replies
View Related
Jan 19, 2006
Greetings,
Iam new to SQLl2005. Iam using DTS to transfer data from my source to the warehouse. I have a couple of tables in my source whein I have to join these to tables fields and insert the same in teh warehouse fact table. I have used a Join query in my Oledb source component, What other component needs to be used to insert the data into the fact table.
I also need to extract same data with aggregation and insert the same into an another Fact table.
Kindly help.
View 21 Replies
View Related
Apr 10, 2008
Hello All,
I am migrating data from one database to another. I am using Multicast to seperate (legal street,legal mail and legal city) and (mail_street,mail_state,mail_zip,mail_city) also later after UNION of the above I am doing two lookups as I had to get contact ID and Customer ID from other two tables. In UNION i am matching (Mail street legal street) and so on.
I am getting double the data in the output. my input data is 1000000 and im gettin 2000000.
What could be the reasons. Please help me out.
Thank You
View 5 Replies
View Related
Feb 21, 2005
What's the best way to convert a large set of records from a simple schema where all fields are in one table to a schema where fields are split across two tables? The two table setup is necessary for reasons not worth getting into here.
Doing this via cursor is pretty straightforward, but is there a comparable set-based solution?
Here are sample create table commands. Obviously, the example below is simplified for discussion purposes.
-- One record from here will produce a record in TargetParentRecords and a record in TargetChildRecords for a total of two records.
CREATE TABLE OriginalSingleTableRecords (
ID INT IDENTITY (1, 1) NOT NULL,
ColumnA VARCHAR(100) NOT NULL,
ColumnB VARCHAR(100) NOT NULL,
CONSTRAINT PK_OriginalSingleTableRecords PRIMARY KEY CLUSTERED (ID)
)
CREATE TABLE TargetParentRecords (
ParentID INT IDENTITY (1, 1) NOT NULL,
ColumnA VARCHAR(100) NOT NULL,
CONSTRAINT PK_TargetParentRecords PRIMARY KEY CLUSTERED (ParentID)
)
-- Each row in this table must link to a TargetParentRecords row
CREATE TABLE TargetChildRecords (
ID INT IDENTITY (1, 1) NOT NULL,
ParentID INT NOT NULL, -- References TargetParentRecords.ParentID
ColumnB VARCHAR(100) NOT NULL,
CONSTRAINT PK_TargetChildRecords PRIMARY KEY CLUSTERED (ID)
)
View 5 Replies
View Related
Feb 19, 2008
hi iam new to writting stored procedures
so anyone please help me out.
i have to transfer data from one table to three other new tables.
and if there are any duplicates in original table i have to send them to
duplicates table.the remaining data should be send to three other tables.
so can anyone help writting stored procedure for this.
thanks for your suggestions and answers
regards
ramya.
View 20 Replies
View Related
Oct 15, 2007
Hello,
I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.
I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.
This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.
So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?
Thanks in advance,
Andy
Select * from
(
declare @years table (years int);
insert into @years
select
CASE
WHEN month(getdate()) in (1) THEN year(getdate())-1
WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())
END
select
u.fullname
, sum(tx.Dm_Time) LastMonthBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-1
and
year(dm_date) = (select years from @years)
and tx.dm_billable = 1
group by u.fullname
) as A
left outer join
(select
u.FullName
, sum(tx.Dm_Time) Billhours
, ((sum(tx.Dm_Time))
/
((day(getdate()) * ((5.0)/(7.0))) * 8)) perc
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
tx.Dm_Billable = '1'
and
month(tx.Dm_Date) = month(GetDate())
and
year(tx.Dm_Date) = year(GetDate())
group by u.fullname) as B
on
A.Fullname = B.Fullname
Left Outer Join
(
select
u.fullname
, sum(tx.Dm_Time) TwomosagoBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-2
group by u.fullname
) as C
on
A.Fullname = C.Fullname
View 1 Replies
View Related
May 24, 2008
Hi friendsI have little problem here.I am creating data tables dynamically.I want to filter it using sql query.Suppose,
I have four data tables with the same structure but records may be
different.There are two fields ServiceMethod and Rates.Now I want to filter all tables and want to get match records with the ServiceMethod.Suppose,four records in First table,three records in other three tables,and only two records(Service method) are same in all tables.I
want to that two records by filtering all tables and sum of rates and
want to add matched records in new table and bind dropdownlist.Can any guide me how to filter more than one tables using sql query if data tables are created dynamically?Thanks in advance.
View 2 Replies
View Related
Dec 17, 2013
I have a set of tables that look like what I have shown below. How I can achieve the desired output ?
CREATE TABLE #ABC([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductA INT);
CREATE TABLE #DEF([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductB INT);
CREATE TABLE #GHI([Year] INT, [Month] INT,Customer Varchar(10), SalesofProductC INT);
INSERT #ABC VALUES (2013,1,'PPP',1);
INSERT #ABC VALUES (2013,1,'QQQ',2);
INSERT #ABC VALUES (2013,2,'PPP',3);
[Code] ....
I have a query currently that looks like this . @Month and @Year are supplied as parameters
SELECT
-- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run)
(SELECT SUM(SalesofProductA) FROM #ABC WHERE [Year]=T.[Year] AND [Month]=T.[Month]) AS [Sum_SalesofProductA]
[Code] ...
Right now I see an output like this : for a particular value of @Month and @Year
SalesofProductA, SalesofProductB, SalesofProductC What I would like to see is :
[Customer],SalesofProductA, SalesofProductB, SalesofProductC
How it can be done ?
View 2 Replies
View Related
Feb 12, 2014
I need to make a query that counts installed developer software for all our developers (from the sccm database), for licensing purposes. The trick here is that a license should only be counted once per. developer and that should be the highest version. But in the database, the developers can have different versions of the software installed (upgrades) on the same computer and they often use several computers with different software versions.
So for example: A source table with two developers
-------------------------------------------------------------------
| dev1 | comp1 | Microsoft Visual Studio Ultimate 2013
| dev1 | comp1 | Microsoft Visual Studio Professional 2010
| dev1 | comp2 | Microsoft Visual Studio Premium 2010
| dev2 | comp3 | Microsoft Visual Studio Professional 2010
| dev2 | comp4 | Microsoft Visual Studio Premium 2012
--------------------------------------------------------------------
I want the result to be:
-----------------------------------------------------
| dev1 | Microsoft Visual Studio Ultimate 2013
| dev2 | Microsoft Visual Studio Premium 2012
------------------------------------------------------
I have created a query using cursors that give me the correct result, but it's way to slow to be acceptable (over 20 min..). I also toyed with the idea of creating some sort of CRL proc or function in C# that does the logic, but a SCCM consultant from MS said that if I create any kind of custom objects on the SCCM SQL Server instance, we loose all support from them. So I'm basically stuck with using good old fashioned T-SQL queries.
My idea now, is to use a CTE table and combine it with a Temp table with the software and a rank. I feel that I'm on the right track, but I just can't nail it properly.
This is how far I have come now:
IF OBJECT_ID('tempdb..#swRank') IS NULL CREATE TABLE #swRank(rankID int NOT NULL UNIQUE, vsVersion nvarchar(255))
INSERT INTO #swRank(rankID, vsVersion)
VALUES
(1, 'Microsoft Visual Studio Ultimate 2013'),
(2, 'Microsoft Visual Studio Ultimate 2012'),
[Code] ....
View 4 Replies
View Related