Tag: sql

How to get a inserted id in other table from inside a trigger?

I have 3 tables tbl_Users, tbl_Protocol and tbl_ProtocolDetails and inside of my trigger on Users, I have to inserted into Protocol and then insert into ProtocolDetails, but I don’t know how work the inserted scope. Something like that: CREATE TRIGGER tg_Users ON tbl_Users AFTER INSERT, UPDATE AS BEGIN DECLARE @UserId = Int DECLARE @ProtocolId = […]

How to combine rows if the first group by clause is satisfied

I have a simple query that pulls sets by employee for each location. Let’s say I have the following data: Username Store_number Invoice_amt TJ1 1 5 TJ1 1 5 TJ1 2 5 RB2 3 5 RB2 3 5 DD1 4 5 DD1 4 5 I have a query: Select username, Sum(invoice_amt) as Total, Store_number I’ll […]

How to prevent timeout in query?

SELECT C.CompanyName, B.BranchName, E.EmployerName, FE.EmployeeUniqueID, pcr.EmployerUniqueID, Case when FE.Status_id= 1 then 1 else 0 end IsUnPaid, Case when re.EmployeeUniqueID IS NULL OR re.EmployeeUniqueID= ” then 0 else 1 end AS ‘EmployeeRegistration’, FE.IncomeFixedComponent, FE.IncomeVariableComponent, Convert(varchar(11), Fe.PayStartDate, 106) as PayStartDate, Convert(varchar(11), Fe.PayEndDate, 106) as PayEndDate, S.StatusDescription, FE.IsRejected, FE.ID ‘EdrID’, Convert(varchar(20), tr.TransactionDateTime, 113) as TransactionDateTime, tr.BatchNo, tr.IsDIFCreated, Convert(varchar(20),tr.DIFFileCreationDateTime,113) […]

How can i use int32 variables Expression builder in SSIS

In the expression builder, my query looks something like this. SELECT * FROM TABLE_A WHERE COLOMN_A_ID IN (SELECT COLOMN_A_ID FROM TABLE_B JOIN EXTRACT_STATUS ON EXTRACT_DTTM > TO_DATE(‘” + (DT_WSTR, 30) @[User::LastTransferDT] + “‘, ‘mm/dd/yyyy hh:mi:ss AM’) AND TABLE_B.Scheduled_DT BETWEEN EXTRACT_STATUS.RANGE_BEGIN_DTTM AND EXTRACT_STATUS.RANGE_END_DTTM AND F_MPOG_EXTRACT.extract_num =”+@[User::RowNumber]+ “)”` I keep getting the same error again and again. […]

Multi Join Result Set

I have three tables which I want to join together and produce an output of missing translations. —————————– Table 1 – Languages —————————– LanguageID Name —————————– Table 2 – Phrase —————————– PhraseID Text ———————————————————- Table 3 – Transaltion ———————————————————- TranslationID PhraseID LanguageID Text I have tried to create a Sql Query which returns all the […]

How to get the Avg from a column of Time(7) datatype?

OK the issue is, I’m trying to figure out time(7) and do AVG function this is what i have so far and its not working. SELECT AVG(Sunset) AS AverageSunset FROM Weather

How to simplify query with joins and similar where clauses

I have multiple Stored Procedures that have joins with similar where clauses, in fact the only difference is the FROM table. I would like to remove the duplication if possible. This is a working example: SELECT x.Month_Name , x.Invoice_Amt , y.Payment_Amt FROM ( SELECT d.Month_Name , d.Month , sum(i.Amount) AS Invoice_Amt FROM Invoice i INNER […]

Inserting values in temporary table from real table by using stored procedure

How do I insert values in temporary table from real table by using stored procedure? When I’m executing this code in SQL Server it is showing me the correct result: SELECT Toy_Image, Toy_Name, Anime_Image, Anime_Name, Toy_Distributor_Image, Toy_Distributor INTO #Temp FROM Toy But when I’m trying to execute this code in SQL Server through stored procedure, […]

Extract date from date time in the format 2015-01-01-15:00:02:30

I am trying to extract only date from a timestamp of the following type: 2015-01-01-15:00:02:30 I tried only using cast() but it gave me an error: ‘:’ is undefined symbol Can I get some assistance here? Help is greatly appreciated.

SQL statement: when all taks completed for a given ID

I have am querying an application in MS SQL server where I monitor when individual tasks are completed and I would like to query adding a column to identify if all individual tasks have been completed as shown below tasks, completion_date, additional_column task_1 15-Jan-17 task_1 task_2 21-Jan-17 COMPLETE task_2 06-Jan-17 COMPLETE task_2 12-Dec-16 COMPLETE task_3 […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.