Transact SQL :: How To Give Rankings Based On GUID Column
May 22, 2015I have the table like below I want to gave Rankings to GUID Column. GUID col has Duplicate rows I am not getting unique ranking.
View 6 RepliesI have the table like below I want to gave Rankings to GUID Column. GUID col has Duplicate rows I am not getting unique ranking.
View 6 RepliesI just started learning HierarchyID and copied the the following code from [URL] .... and Chapter 7 Hierarchical Data and the Relational Database of the book "Programming Microsoft SQL Server 2012", written by L. G. Lobel and A. J. Brust (Published by Microsoft Press):
-- shcHierarchyid_1.sql
-- 19 August 2015 13:15 PM
USE master
GO
IF EXISTS(SELECT name FROM sys.databases WHERE name = 'shcDB')
DROP DATABASE shcDB
GO
CREATE DATABASE shcDB
GO
[Code] ....
I executed the code in my Microsoft SQL Server 2012 Management Studio (SSMS2012). It worked nicely. But, I don't understand the following 2 things:
#1: In the Results, I got:
(No column name) Position Title
1 / CEO
2 /1/ 0x58 Purchase Manager
3 /1/1/ 0x5AC0 Purchase Executive
4 /2/ 0x68 Sales Manager
5 /2/1/ 0x6AC0 Sales Executive
I don't know how to give a column name for the /, /1/, /1/1/, /2/, /2/1/ (NodePath (?) I guessed). How I can give a column name to that column.
#2: The book lists "USE master GO .....". I wonder whether I can do the "hierarchyid" thing in the private database I created!!?? Whether I can do the "hierarchyid" thing in the private database or not.
#EMAIL_ADDRESSES which hold records similar to the following (CREATE code below):
View 6 Replies View Related
How do I update the OrderGUID column in Table B with Values from OrderGUID column in Table A. I have already populated the OrderGUID column in Table A using NEWSEQUENTIALID(). Now I need to populate the OrderGUID column in Table B with Matching GUID values from the OrderGUID Column in Table A.
Does any one have a script to accomplish this task. thanks
I have the following result query :
Which is produce by following SP :
SELECT
P.StoreID,
P.InventoryDate,
P.ProductID,
SUM(PIT.Quantity * P.ItemUnitWeight) AS TotalWeight,
SUM(PIT.UsedQuantity * P.ItemUnitWeight) AS UsedWeight,
[code]....
What is the way to add a calculated columned Named "Stocked" which will represent the sum of "Remaining" column for each rows ?
Below is the data I have in table name
TeamStatus
T 1 Complete or Escalate
T 2 Pick Up
T 2 Resolve Case
T 1 Pick Up
T 1 Complete or Escalate
T 1 Pick Up
T 1 Complete or Escalate
I want to get he group based of Resolve Case value in Status Column. Anything before Resolve case will be considered as Group 1 and after Resolve Case status should be considered as Group 2. Below is desired new Group column,
Group TeamStatus
Group 1 T 1Complete or Escalate
T 2 Pick Up
T 2 Resolve Case
Group 2 T 1Pick Up
T 1Complete or Escalate
T 1 Pick Up
T 1 Complete or Escalate
Here's the my structure and data as follows
>
SELECT tranno ,mrno medrecno ,createdon,createdat,no_of_trans nooftrans
FROM mytab WHERE mrno = 'MR1514' and tranno = 1111
ORDER BY no_of_trans
tranno medrecno
createdon createdat
nooftrans
[Code] ...
Now requirement is : -
tranno medrecno
createdon createdat
nooftrans tranno medrecno
createdon createdat
nooftrans
[Code] ....
I have a query that gives me a result with a column value for example 4.
I now want to repeat this row 4 times with a new column that calculated from 1 - 4.
Or when column value is 3 I want to repeat row 3 times with new column name 1-3
I am trying to calculate the the running total but also tried to reset to reset to zero based on a value of a column.
here I am trying to calculate the value of CalcVal column based on column Flag value...actually it is running total but it reset to zero if Flag value is 0.
Here is the example of data along with required column
MonthId Flag CalcVal
201401 1 1
201402 1 2
201403 1 3
201404 1 4
201405 1 5
and so on .........
Below is the resultset I got using the following SQL statement
SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY create_date DESC) AS RowNum
,ID
,create_date
,NULL AS end_date
FROM dbo.Table_1
Resultset:
RowNum ID
create_date end_date
1 0001
2015-02-18 NULL
2 0001
2014-04-28 NULL
[Code] ....
Now, I want to update the end_date column with the create_date's values for the next row_number. Desired output is shown below:
RowNum ID
create_date end_date
1 0001
2015-02-18 NULL
2 0001
2014-04-28 2015-02-18
[Code] ....
I have the following 2 tables:
Table: classes Columns: classID, hp
Table: char_active Columns: name, classID, hp
The classes table is already populated.
What I want to do is insert a new row into char_active using the name and classID column, and have the HP column auto populate based on the corresponding value in the classes table. This is the trigger I wrote but I'm getting the error
Incorrect syntax near 'inserted'.
I'm new to sql, this is actually the first trigger I've tried writing.
create trigger new_hp on curr_chars.char_active
instead of insert
as
declare @hp tinyint
select @hp=lists.classes.hp from lists.classes where lists.classes.classID=inserted.classID
insert into curr_chars.char_active (name, classID, hp) inserted.name, inserted.classID, @hp
go
I have a scenario to compare previous records based on each ID columns. For each ID, there would be few records, I have a column called "compare", We have to compare all Compare 1 records with Compare 0 Records. If Dt is lesser or equal to comparing DT, then show 0. Else 1
We always only one Compare 0 records in my table, so all compare 1 columns will compare with only one row per ID
My tables look like
Declare @tab1 table (ID Varchar(3), Dt Date, Compare Int)
Insert Into @tab1 values ('101','2015-07-01',0)
Insert Into @tab1 values ('101','2015-07-02',1)
Insert Into @tab1 values ('101','2015-07-03',1)
Insert Into @tab1 values ('101','2015-07-01',1)
Insert Into @tab1 values ('101','2015-06-30',1)
Insert Into @tab1 values ('102','2015-07-01',0)
Insert Into @tab1 values ('102','2015-07-02',1)
Insert Into @tab1 values ('102','2015-07-01',1)
select * from @tab1
1.) In the above scenario for ID = '101', we have 5 records, first record has Compare value 0, which mean all other 4 records need to compare with this record only
2.) If Compare 1 record's Dt is less or equal to Compare 0's DT, then show 0 in next column
3.) If Compare 1 record's Dt is greater than Compare 0's DT, then show 1 in next column
My expected result set should be like ....
I'm trying to Update a column table based on values from another table but I need these values are random.My query looks like this and doesn´t work
DECLARE @Rowini int,
DECLARE @lastrow int
SET @Rowini = 1
SET @Lastrow = 80000
[code]...
I have 3 columns. I would like to update a table based on job_cd and permit_nbr column. if we have same job_cd and permit_nbr, reference number should be same else it should take max(reference number) from the table +1 for all rows where reference_nbr column is null
job_cd permit_nbr reference_nbr
ABC1 990 100002
ABC1 990 100002
ABC1 991 100003
ABC1 992 100004
ABC1 993 100005
ABC2 880 100006
ABC2 881 100007
ABC2 881 100007
ABC2 882 100008
ABC2 882 100008
I have a query running and returning 3 columns, user name, e-mail and device name
SELECT DISTINCT v_R_User.Full_User_Name0 AS 'User full Name', v_R_User.Mail0 AS 'E-Mail', _RES_COLL_DEV00144.Name
FROM v_R_System INNER JOIN
v_R_User ON v_R_System.User_Name0 = v_R_User.User_Name0 INNER JOIN
_RES_COLL_DEV00144 ON v_R_User.User_Name0 = _RES_COLL_DEV00144.UserName INNER JOIN
v_GS_COMPUTER_SYSTEM ON v_R_System.ResourceID = v_GS_COMPUTER_SYSTEM.ResourceID
Where v_R_User.Mail0 <> ''
ORDER BY 'User Full Name'
From here I would like to generate an e-mail to each user (like mail merge) to each user in the table an include their machine name. I can do it with PS, but rather have it run directly from SQL. Is it possible?
I have a requirement where i want to delete the records based on the Date column. I have table which contain the columns like machinename ,lasthardwarescandate
I want to delete the records based on the max(Lasthardwarescandate) i.e. latest one, column where the machine name is duplicate menace it repeats. So how would i remove the duplicate machine names based on the Lasthardwarescandate column(There are multiple entries for the Lasthardwarescandate so i want to fetch the latest date column).
Note: Duplication should be removed based on “Last Hardware Scan” date.
Only latest date should be considered from multiple records for the same system. "
I have 3 different companies that share the same ticket_types(CRMS System). I need to display the Ticket Types and the 3 company's Ticket Count:
Ticket Type | Company A Count | Company B Count | Company C Count
I can get the information individually for each company, but if a company doesn't have a ticket in one of the ticket_types, then it isn't displayed in a row. So, I tried to write the following, which isn't pulling back any data.
DECLARE @startdate date = '20150306'
DECLARE @enddate date = '20151031'
DECLARE @AcctGrp varchar(20) = '111'
;WITH TType
AS
(
SELECT ctp.description as TicketType
[Code] .....
If I run each SELECT individually from above (excluding the last SELECT), it works and I get the following:
TicketType
AR Request Credit
Availability/Rush
Cancel Order
Credit Card Payment
Expedite Order
Freight Quote
[Code] ...
How to get the query results? Am I even close to getting it right?
The data I have is as follows -
ID1 ID2 Date Action
100 500 09/08/14 Open
100 500 09/24/14 Close
101 510 07/10/15 Open
101 510 07/19/15 Close
The output I want in a single result set is -
ID1 ID2 Open_Date Close_Date
100 500 09/08/14 09/24/14
101 510 07/10/15 07/19/15
Any way to do this in T-SQL .
Below is the SQL Query i am currently having
SELECT IG_FinancialTransactionSummary.ClaimNum,IG_FinancialTransactionSummary.TransactionCode,
IG_FinancialTransactionSummary.TransactionDate,IG_FinancialTransactionSummary.Username,
FinancialTransactionSummaryTest.ClaimNum,FinancialTransactionSummaryTest.TransactionAmount,
FinancialTransactionSummaryTest.UserName--,FinancialTransactionSummaryTest.TransactionDate
[Code] ....
And here is the result dataset
ClaimNumTransactionDateUsername ClaimNum TransactionAmountUserName
2000074 20150209jerry.witt 2000074 -10000DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL
2000074 20150626DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL 2000074 -10000DATAFIX INSERTED ON 20150626 AT 162152493 LOCAL
[Code] .....
So,if we look at the result set, we notice 2 conditions where the IG_FinancialTransactionSummary.Username is like 'Data' and if we see the transaction date then sometimes that is the max transaction date or sometimes there are transactions that happened after but that doesn't have like '%data%' in username . So, i need to add a new column to my sql query which should basically verify if the username is like '%data%' and if that is the max(transaction date) or even if there are any transactions after that doesn't have like '%data%' then YES else No.
I have a table that is used to build rules. The rules point to other columns in other tables and usually contain only one value (i.e. ABC). But one of the options is to add a comma-separated list of SSNs (i.e. 123123123,012012012,112231122). I am trying to build a single query that allows me to leverage that list to get multiple rows from another table.
This obviously works:
SELECT * FROM vw_Person_Profile P (NOLOCK)
WHERE P.PrsnPIISSN_Chr IN ('123123123','012012012','112231122')
But this does not:
SELECT * FROM vw_Person_Profile P (NOLOCK)
WHERE P.PrsnPIISSN_Chr IN (
SELECT '''' + REPLACE(CONVERT(VARCHAR(4000),txtFieldValue), ',', ''',''') + ''''
FROM MassProcessing_Rules PR
WHERE PR.intRuleID = 10
)
Hello;
My Memebership table has Guid column as Primary key.
But I would like to add Auto numbering Identity column to this table.
Is this idea OK or it will bring some problems?
Thank you in advance for your help
I'm trying to create a report which would give the latest transaction on a database, which all sit on different servers. I wanted to know if there is an a simple way tracking the latest transactions instead of getting the information from the database tables.
View 3 Replies View RelatedI have some data which is vertical...I want to create a pivot query in SQL that will give me a result that is horizontal like this. I cannot find a way of doing it without lots of IF or CASE statements?
View 10 Replies View RelatedWe created sql alerts on all our sql servers environments. Now, i want to see each sql server which sql alerts so far got fired and which one never occurs. is there any way, we can get this information from any system database?
View 9 Replies View RelatedI've searched through this forum, and am extremely disappointed that something as simple as this can't be done without resorting to a script component (which I refuse to do):
I have a Lookup transformation that returns a GUID value -- or not, so I've configured its error output to ignore failure. That should leave a NULL in the output column, and I should be able to chain a Derived Column transformation that replaces the Lookup output column (e.g. foo) with an expression like:
isnull(foo) ? newid() : foo
But there's no newid() function! Who was the genius that decided SSIS should implement different expressions than Transact SQL? Is there a workaround, like (DT_GUID)GETDATE()?
Or do I have to craft a SQL statement in the Lookup that guarantees a non-NULL result?
Does anyone have a script that can add a GUID Column to multiple Tables in a Database.
The SQL script below adds a column to a single table in a database. But i need a script that can add a column to all the tables in a database. Thanks
USE ProductDB
ALTER TABLE dbo.Employees
Add EmployeeGUID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL DEFAULT NEWSEQUENTIALID()
Hi I want to pas one Derived Column Value is "Null" Default . How to Give the Expression "
For Example"
The Derived Column i gave one Column Name
Derived Column Name Expression Data Type
Price ""
Any one give me the Solution
Hi!
I am designing a dimension table which will include a short name column based on the (full) name column. For example say Product dimension where I will have ProductName and ProductShortName. ProductShortName will be the first 6 characters of ProductName. I could populate ProductShortName using:
Substring in the select when I select from the original system, e.g. SUBSTR(PRODUCT_NAME, 1, 6) AS ProductShortName
Create a derived column in the SSIS flow which does the same thing
Create the ProductShortName column as a computed column which uses substring on ProductName
Create a trigger that populates ProductShortName based on ProductName when a row is inserted or updated
Create a named calculation in the table in the Analysis Services project's data source view
Create a named query in the Analysis Services project's data source view
I usually use 1, and 5 or 6 would only be used if I only will create reports against the cubes. 3 seems easiest to maintain, so I am thinking about using that one, but maybe it is slow for the data flow as I imagine it must be something like using 4, or when is the column "created" at runtime, i.e. when the table is queried?
Which approach(es) do or would you use? Pros and cons?
Thanks!
Hello,
In my table, i've a GUID column type. I insert a new record with NewID() function in Sql request.
Is it possible to retreive the GUID column of this new record (without requerying the table) ?
I'm using EVC, Sql Mobile 3.0 and OLE DB interface.
Thanks in advance.
Does anyone have a script that can add a GUID Column to multiple Tables in a Database.
For example when a table has an Identity column like EmployeeID, a column called EmployeeGUID is added to the table. When a table has an Identity column like CustomerID, a column called CustomerGUID is added to the table. When a table has an Identity column like OrderID, a column called OrderGUID is added to the table.
I intend to implement replication in SQL Server 2005, so i want to add new GUID columns to all the table in my database before implementing replication
The SQL script below adds a column to a single table in a database. But i need a script that can add a column to all the tables in a database.
Thanks
USE ProductDB
ALTER TABLE dbo.Employees
Add EmployeeGUID UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL DEFAULT NEWSEQUENTIALID()
I have a excel file which has a column called "Code" and their values are A,B,C,D,E,F,G,H. I want to create a new column called "status" based on the values of "Code".
Code:
A
B
C
D
E
F
G
H
If A,C,E,G then "status" = "Active" else if B,D,F,H then "Status" = "Inactive". I like to do it using "Derived Column".
The title, I think, accurately illustrates the situation in my report.
Naturally, this result is unwanted.
How do I get the avg-function to ignore empty cells in the column?
Thanks in advance,
Pluggie
I am trying a create views that would join 2 tables:
Table 1: Has all the columns need by a view (
Name: Product
Structure: ID, Attribute 1, Attribute 2, Attribute 3, Attribute 4, Attribute 5 etc
Table 2: Is a lookup table that provides the names of columns
Name: lookupTable
Structure: tableName, ColumnName, columnValue
Values: Product, Attribute1, Color
Product, Attribute2, Size
Product, Attribute3, Flavor
Product, Attribute4, Shape
I want to create a view that looks like
ID, Color, Size, Flavor, Shape