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 .
I have conducted a thorough search in the forums and cannot quite find my answer. I have a date field called open_date. If the open_date is more than 30 days old, I need to count it. I have started with the following code:
SELECT 'Older_Than_30Days' =
CASE
WHEN open.date >= 30 THEN '1'
ELSE '0"
END
My problem is the WHEN.
I'm trying extract a column from the table based on certain Conditions: This is for PowerPivot.
Here is the scenario:
I have a table "tb1" with (project_id, month_end_date, monthly_proj_cost ) and table "tb2" with (project_id, key_member_type, key_member, start_dt_active, end_dt_active).
I would like to extract Key_member where key_member_type="PM" and active as of tb1(month_end_date).
Is this possible using DAX ?
I have a simple following table which is having only one date column.
CREATE TABLE TEST_DATE
(
InputDate DATE
)
GO
INSERT INTO TEST_DATE VALUES('01-01-2015')
INSERT INTO TEST_DATE VALUES('06-25-2015')
INSERT INTO TEST_DATE VALUES('11-23-2014')
GO
SELECT * FROM TEST_DATE;
And the expected out put would be as follows:
I want to derive a Four Quarter End Date based on Date selected.
For Example if i select 01-01-2015 then
First Quarter End Date would be Previous Quarter End Date
Second Quarter End Date would be Current Quarter End Date
Third Quarter End Date would be Next Quarter End Date
Fourth Quarter End Date would be Next +1 Quarter End Date Like that
I have to extract a specific part of a string from a column in a sql server table. Following are the details and I have given the sample table and the sample strings.
I have 2 columns in my table [dbo].[StringExtract] (Id, MyString)
The row sample looks like the following
I have to extract the Id and a part of the column from mystring.
Id MyString
1 ABC|^~&|BNAME|CLIENT1||CLIENT1|20110609233558||BIC^A27|5014589635|K|8.1|
ABC1|^~&|BNAME1|CLIENT1||CLIENT1|20110609233558||CTP^A27|5014589635|I|7.1|
DEF||5148956598||||Apprised|Bfunction1||15|LMP|^^^201106101330|
alloys3^ally^crimson^L||||alloys3^ally^crimson^L||||alloys3^ally^crimson^L|||||Apprised|
[Code] ....
The part I want to extract is in the line "ZZZ" and the string part that i want to extract is between the 5th and 6th pipes (|). So my output looks like the following
Id DesiredString
1 Extracts^This^String1
2 Extracts^This^String2
3 Extracts^This^String3
Is there a way to extract this either using TSQL or SSIS.
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StringExtract]') AND type in (N'U'))
DROP TABLE [dbo].[StringExtract]
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[StringExtract]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[StringExtract](
[Id] [int] NULL,
[Code] ....
I’m trying to figure out a way to sum columns for similar IDs, based on the contents of a single field. For example, if I’m calculating attendance percentages for students, and codes P and T count as Present, and codes A and E count as Absent, I would want to total Present and Absent codes separately, in their own columns. I would then like to use those totals to calculate percentage, but I can do that. It’s the SUM based on column value (by ID) that is giving issue.
If I have the following view:
IDLNFNCDTotal
123456MearsBenP12
123456MearsBenA2
123456MearsBenT6
234567NortonSusanP15
234567NortonSusanA2
234567NortonSusanT2
234567NortonSusanE1
I would like something like this:
IDLNFNPresentAbsentPercentage
123456MearsBen18290.0
234567NortonSusan17385.0
I’ve been playing around with nested queries, but nothing’s working. This is a glimpse of the mess that I’ve created trying to sort this out. Many errors.
I just noticed that I used a simpler example than the SQL I included, so I modified it a bit. There are additional fields that I'll need to include, but I want to get the logic working correctly. From there, I can handle the rest. So here's a more appropriate code example showing the direction I'm trying to go with this.
SELECT ID, [Last Name], [First Name], CD, Present, Absent, CAST(LEFT(Present / (Absent + Present) * 100, 5) AS varchar) + '%' AS Percentage
FROM (SELECT ID, CD, TotalAHD, CAST
((SELECT SUM(TotalAHD) AS Expr1
FROM SumAHDforAttndPercentages AS p
[Code] ...
#EMAIL_ADDRESSES which hold records similar to the following (CREATE code below):
View 6 Replies View RelatedI have data:
Ticket User Priority
A ME 1
B ME 1
C ME 2
C ME 3
D ME 2
E YOU 2
F YOU 1
G ME 3
H YOU 2
H YOU 3
I ME 1
Essentially if Ticket and User are the same I just want the min priority returned.
SO:
Ticket User Priority
A ME 1
B ME 1
C ME 2
D ME 2
E YOU 2
F YOU 1
G ME 3
H YOU 2
I ME 1
I've tried partition and rank but can't get it to return the right output.
I have a requirements to make a single column using a date from multiple columns. below my DDL and sample.
Create table #Mainsample
(ItemNum nvarchar(35), ItemDate datetime, OPType int)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('M9000000000020510095','2015-05-01 18:38:48.840',5)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('M9000000000020510094','2015-05-02 20:38:40.850',5)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('M9000000000020510092','2015-05-02 21:40:42.830',5)
Insert into #Mainsample(ItemNum,ItemDate, OPType) values ('353852061764483','2015-05-02 09:25:10.800',5)
[code]....
Deciding whether or not to use a CTE or this simple faster approach utilizing system tables, hijacking them.
SELECT s.ORDER_NUMBER, s.PRODUCT_ID, 1 AS QTY, s.VALUE/s.QTY AS VALUE
FROM @SPLITROW s
INNER JOIN master.dbo.spt_values t ON t.type='P'
AND t.number BETWEEN 1 AND s.QTY
Just wanted to know if its okay to use system tables in a production environment and if there are any pit falls of using them ?
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
I have the following two tables...
tblServer
-serverID
-serverName
-serverLocation
tblSite
-siteID
-serverID
-siteName
-siteIpAddress
I need to write a select query that gets the values of all columns but only returns unique sites because some sites are load balanced across several servers and where this is the case I don't want the site to appear multiple times in the list.
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 build a DIM table using a source table that has the following setup...
CREATE TABLE [dbo].[APPL_STATUSES](
[APPLICATIONS_ID] [varchar](10) NOT NULL,
[POS] [decimal](10, 0) NOT NULL,
[APPL_STATUS] [varchar](5) NULL,
[APPL_STATUS_DATE] [datetime] NULL,
[APPL_APPLICANT] [varchar](10) NULL)
GO
[code]....
What I am trying to do is to break out the APPL_STATUS_DATE into a STATUS_START_DATE and an STATUS_END_DATE in a new table. I also need to be able to update the STATUS_END_DATE based on the previous day's date. Like so...
CREATE TABLE [dbo].[APPL_APPLICANT_STATUSES](
[APPLICATIONS_ID] [varchar](10) NOT NULL,
[POS] [decimal](10, 0) NOT NULL,
[APPL_STATUS] [varchar](5) NULL,
[STATUS_START_DATE] [datetime] NULL,
[STATUS_END_DATE] [datetime] NULL,
[APPL_APPLICANT] [varchar](10) NULL)
GO
[code]...
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 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 Replies View RelatedWe run std 2008 r2. I haven't looked at my friend's function closely yet bur he showed me that when he selects from the function with one column and the same where clause he uses on same func with select *, he gets no data under the column he requested.
But when he selects * he gets a single row.
I took a peek and see a bunch of left joins followed by a bunch of outer applies in his func. I suppose (thinking out load) if anything random like the order of rows returned or sql decisions on how query runs can affect his function, that might explain it.
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?
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 an issue accumulating a value based on certain conditions:
I need to sum up the days column as you can see table below. The sum is based on the StartNewFlag.
When the flag is 1, I need to start a new sum from zero, else I need to accumulate to the previous rows.
CREATE TABLE #foo
(
ItemID int,
StartDate datetime,
EndDate datetime,
StartNewFlag bit,
[Code] ....
My Results must look like one of the following tables (either one would work, however the second one is preferred)
Result 1:
ItemID StartDate EndDate StartNewFlag Days TotalDays
1 2015-07-01 00:00:00.000 2015-07-05 00:00:00.000 1 4 5
1 2015-07-08 00:00:00.000 2015-07-09 00:00:00.000 0 1 5
1 2015-07-11 00:00:00.000 2015-07-15 00:00:00.000 1 4 11
1 2015-07-18 00:00:00.000 2015-07-21 00:00:00.000 0 3 11
1 2015-07-22 00:00:00.000 2015-07-26 00:00:00.000 0 4 11
OR
Result 2:
ItemID EndDate TotalDays
1 2015-07-09 00:00:00.000 5
1 2015-07-26 00:00:00.000 11
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
)
hii all
i am havie a database with given fileds like projectid,projectcode,projectname,startdat,enddate,etc....
my problem is to show all months and year between each projects startdate and enddate in a datagrd..and the table dont have month, year field..
i need to use sql quiery..
if anybody can help me in this matter.. it will be greatful..
thanks in advance..binoy
I need to update the status of a client when they make a payment of a certine amount. My problem is this, the two pieces of information needed to do this are comming from two tables. For example;
@ClientID Int,
@PmtAmt Money
IF @PmtAmt >= tblSettings.TopAmt THEN
Update tblClients
SET
ClientStatus='High'
WHERE ClientID=@ClientID
ELSE
Update tblClients
SET
ClientStatus='Medium'
WHERE ClientID=@ClientID
ENDIF
How do I do this in a stored procedure? I need to select the TopAmt from the table tblSettings and then update the table tblClients.
Hi! I have a table Tbl1 has to columns:
A B
_________
Ibm Me
Sony Me
Me Bob
Me Frank
I'd like to select all rows where B=ME and A=Me Thanks for the help