Col1 to Col9 represent the account information, Col10 amount, Col 11 represents the month and Col12 represents year. According to the data there wasn't any activity for the month 1, 2 and 3.
Col1
Col2
Col3
Col4
Col5
Col6
Col7
Col8
Col9
Col10
Col11
Col12
[code]....
I want to take the contents from a table of appointments and insert theappointments for any given month into a temp table where all theappointments for each day are inserted into a single row with a columnfor each day of the month.Is there a simple way to do this?I have a recordset that looks like:SELECTa.Date,a.Client --contents: Joe, Frank, Fred, Pete, OscarFROMdbo.tblAppointments aWHEREa.date between ...(first and last day of the selected month)What I want to do is to create a temp table that has 31 columnsto hold appointments and insert into each column any appointments forthe date...CREATE TABLE #Appointments (id int identity, Day1 nvarchar(500), Day2nvarchar(500), Day3 nvarchar(500), etc...)Then loop through the recordset above to insert into Day1, Day 2, Day3,etc. all the appointments for that day, with multiple appointmentsseparated by a comma.INSERT INTO#Appointments(Day1)SELECTa.ClientFROMdbo.tblAppointments aWHEREa.date = (...first day of the month)(LOOP to Day31)The results would look likeDay1 Day2 Day3 ...Row1 Joe, PeteFrank,FredMaybe there's an even better way to handle this sort of situation?Thanks,lq
View 11 Replies View RelatedI have a big table (> 40,000,000 rows) in SQL Server 2008 R2 database . I added new column of type int to the table. What is the fastest way to populate the column with -1 value. The table can be locked.
View 2 Replies View RelatedI 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 to load on SS2012 hundeds of excel files produced by an application over the last five years, during time few columns have been added to the initial set.I created on SS2012 a table to match with the full set of columns and want to load all the files inside the table leaving the missing cells to NULL. I think SSIS can do the job but every trial failed do far.
View 4 Replies View RelatedHi,I need to convert this anonymous pl-sql block into transact-sql to runin Microsoft SQL Server Query Analyser.Does anyone know what the conversion syntax would be?I know that to declare variables to use the @.PRINT for printing the output.No idea for the LOOP.Any suggestions greatly appreciated.Many thanks.Thiko!____________________________________________SET SERVEROUTPUT ONDECLARErecordcount NUMBER;BEGINDBMS_OUTPUT.ENABLE(5000000); -- Sets buffer size.recordcount := 0;LOOPINSERT INTO RecoverTest VALUES (SYSDATE - (recordcount / 4));COMMIT; -- frees up rollback segment;recordcount := recordcount + 1;DBMS_OUTPUT.PUT_LINE('INSERT: ' || recordcount || ' - 45000 ');EXIT WHEN recordcount > 45000;END LOOP;END;/
View 1 Replies View RelatedI’m working on building a file list from a directory I have and wish to loop through the filenames. I’ve worked out I can get the list using
EXEC
xp_dirtree @path, 10, 1
I’m just not sure out I pass this to my loop 1 element at a time.
E.g. Let’s say there are 10 files in my folder, I wish to loop through each name using my @Filename parameter
DECLARE @Path
varchar(50), @FileName
varchar(20)
SET @Path
= 'C:myFiles'
EXEC
xp_dirtree @path, 10, 1
[Code] ....
I have two tables i have to update table2 using table1 without using while loop.
example given below.
Table1
rid
id
amt
firdate
lastdate
1
1
500
[code]....
-- Create an Employee table.
CREATE TABLE dbo.MyEmployees
(
EmployeeID smallint NOT NULL,
FirstName nvarchar(30) NOT NULL,
LastName nvarchar(40) NOT NULL,
Title nvarchar(50) NOT NULL,
DeptID smallint NOT NULL,
ManagerID int NULL,
CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)
);
-- Populate the table with values.
INSERT INTO dbo.MyEmployees VALUES
(1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)
[code]....
I want to know one small query..
id Name
1 hi
2 how
3 are
4 you
6 can
7 do
8 not
9 did
10 to
I deleted some records now my table have below mentioned rows..
id Name
1 hi
2 how
4 you
6 can
8 not
10 to
I want to know the missing records in my table.
OUTPUT IS. 3,7,9
how can i do that using sql query.
I have one table with many records in the table. Each time a record is entered the date the record was entered is also saved in the table. I need a query that will find all the missing records in the table. So if I have in my table:
ID Date Location
1 4/1/2015 bld1
2 4/2/2015 bld1
3 4/4/2015 bld1
I want to run a query like
Select Date, Location FROM [table] WHERE (Date Between '4/1/2015' and '4/4/2015') and (Location = bld1)
WHERE Date not in
(Select Date, Location FROM [table])
And the results would be:
4/3/2015 bld1
I have table with column having values 1,2,3,5,6,10.
I want to get the missing values in that column between 1 and 10 i.e., min and max... using sql query.
I want to get the values 4,7,8,9.
Am witnessing a very strange issue when i try to insert some records . I get the message in the SSMS like 5,10 etc rows affected. When i check for the same records in the table none of them is present.
This problem then automatically goes away after some time and all get backs to normal. I only have the access to that database no other user have the access. Totally confused about the all of a sudden new behavior of the database.
My insert statement for #Data - I only need to process each @EmployeeID one time, which is why I thought a loop would be sufficient, but I let this process run for 2 hrs and it still had not completed, so I feel I must have set-up something incorrectly!
This is my syntax, I am creating a table of active users, then wanting to get data for each of those active users. But only get the data for each active user 1 time.
Declare @EmployeeID varchar(50)
CREATE TABLE #ActiveUsers
(
ID INT IDENTITY NOT NULL
,EmployeeID varchar(50)
,processed int
)
Create Table #Data
[Code] ....
I have a requirement for the following query which uses a timestamp data type field
[eventdate]Select field1, fileds2, filed3, {min(eventdate) as max(eventdate) per day/date} from sometable
How to add columns to temporary table in loop with sample code.
View 12 Replies View RelatedI want to calculate year to month data for month wise . Ihave start range and end range table like below
Start range and end range table
Start dateEnd date
01-04-201401-11-2014
01-04-201401-12-2014
01-04-201401-01-2015
01-04-201401-02-2015
[Code] ....
Then I want to pass this value one by one in below table for get value month wise
Main table
Branchamountperiod
Branch110201103
Branch22201104
Branch33201401
Branch44201402
Branch58201403
Branch610201404
[Code] ....
The below query is for november month: (Apr to Nov)
select * from maintable
where period between '01-04-2014' and '01-11-2014'
then for December month : (Apr to Dec)
So I want to pass second row
select * from maintable
where period between '01-04-2014' and '01-12-2014'
.....
....
select * from maintable
where period between '01-04-2015' and '01-12-2015'
Like wise I want to get month wise data of YTM data.
My expected output is
201411
201412
201501
201502
201503
201504
201505
[Code] ....
I am trying to query a code where i need to loop a month in a specified date range. Inside the loop I need to return a result of data each month and need to update the table of the returned data. How do I do the update a field inside the loop? Here's my query:
declare @table1 table (
YEAR_EFF int,
MONTH_EFF int,
IDNumber (8),
SUBS_CNT smallint,
MEM_CNT smallint)
declare @StartDate datetime,
[code]....
Others says I need to use exec sp_executesql N'' but how do I use it using my code above?
I have a Stored Procedure, wherein I need to use a while loop. essentially the loop looks like this.
Set @Kount = 1
--TestScript
Select 'TempReqTable', * from @TempReq
WHILE Exists(Select * from @TempReq WHERE TempID = @Kount)
BEGIN
--Do stuff with values from table
Set @Kount += 1
END
The test script confirms that there is only one row in the @TempReq table. However the loop never stops running and @Kount keeps being incremented and incremented until I manually stop the execution of the SP.
Is there some rule that I am not aware of that does not allow the use of an Exists statement in the condition of a While loop?
I have a transaction number in my mapping table. I have a matching transaction number in my PDHist table. Sometimes I have matching transaction numbers in my PD table, but not always. This is causing no records to be returned. I have a One to Many relationship between my mapping table and both PD and PDHist.
Also, I need to check for nulls in my foreign exchange table.I can’t post the SQL because this is a classified project. However, it should be something like this, I think.
IIf(IsNull([Redem]![FX Rate]),([PDHist]![Remaining Balance]+[PD]![Closing Balance(TC)]).
The addition isn’t working. I think with a small push I can get this straightened out.
I have an exact copy of a database on 2 instances of SQL server (dev and test - dev is restored from test).There's a view that I select top 1000 rows from that runs extremely slow on both instances (DEV and TEST) however on DEV the execution plan specifies that I'm missing an index however in TEST the execution plan does not specify that I'm missing an index.
View 3 Replies View RelatedI'm a little bit unsure...
declare @t table
(col1 varchar(10),
col2 varchar(10),
col3 varchar(10),
col4 varchar(10) )
insert into @t values ('A123', 'Test', '','')
insert into @t values ('', 'Test 1', 'Y','N')
[Code] ...
Which comes out as
col1 col2 col3 col4
(No column name)
A123 Test
1
Test 1
Y N 0
Y N
0
A125 Test
1
Test 9
Y N 0
N Y
0
but what I would like is col 1 populated As A123 until it hits A125 then populated A125 etc. I was thinking about using the iff but not getting anywhere fast.
I appear to be having an issue where the @LetterVal and @Numeric variables aren't resetting for each loop iteration, so if no results are found, it just returns the previous loops values since they aren't overwritten. Below is the stored procedure I've created:
ALTER PROCEDURE [dbo].[ap_CalcGrade]
-- Add the parameters for the stored procedure here
@studId int,
@secId int,
@grdTyCd char(2),
@grdCdOcc int,
@Numeric int output,
[Code] ....
And below is the "test query" I'm using:
-- *** Test Program ***
Declare @LetterVal varchar(2), -- Letter Grade
@Numeric int, -- Numeric Grade
@Result int -- Procedure Status (0 = OK)
Execute @Result = dbo.ap_CalcGrade 102, 86, 'QZ', 3,
[Code] ....
This is resulting in an output of:
A+ 97
A+ 97
C- 72
but it should be returning the output below due to the 2nd data set not being valid/found in the sp query:
A+ 97
No Find
C- 72
I'm sure this is sloppy and not the most efficient way of doing this, so whats causing the errant results, and if there is any better way I should be writing it. Below is the assignment requirements:
Create a stored procedure using the STUDENT database called ap_CalcGrade that does the following:
1. Accepts as input STUDENT_ID, SECTION_ID, GRADE_TYPE_CODE, and GRADE_CODE_OCCURRENCE
2. Outputs the numeric grade and the letter grade back to the user
3. If the numeric grade is found, return 0, otherwise return 1
4. You must use a cursor to loop through the GRADE_CONVERSION table to find the letter grade
I have 3 tables: Suppliers, Documents and Verification, each document may have multiple verification where I need to get the last verification according to verification date. So I just need a "Select top 1 result from Documents, Verification where doc_iddoc=ver_iddoc and result='True' order by ver_date desc" so far I get the result of the last verification, but here's the problem:
I need to get a row with the count of documents for each supplier, I mean:
Supplier Name Docs NegativeVerification
Acme Co 10 1
that is I would need to loop for each supplier and each document and get the last verification, if one of any of documents have negative verification then add it to negative results. Is it possible to achieve this with a query or do I have to do it through stored procedure?
I have the following query:
BEGIN TRAN
Declare @StartDt date = '2015-03-15'
Declare @EndDt date = DATEADD(M, 1, @StartDt)
declare @Days int = DATEDIFF(d, @StartDt, @EndDt)
declare @TBLSales as table(SaleDate date, Value money)
DECLARE @Today date
declare @TBLSalesCounts as table( StatusDesc varchar(100), Value money)
[Code] ....
I end up with the following result :
How would I alter my while loop to only insert the sum total of each day, instead of creating duplicates for each day.
E.g.
2015-04-22
1150.00
2015-04-21
785.00
2015-04-20
750.00
How do I get the below scenario:
EmpID DepID
12 2
17 3
17 2
13 3
Every Employee should be in Department 2 and 3 (as example EmpID = 17 has DepID 2 and 3 from above table). But when any of the employees either exists only in any one department (as EmpID = 12 has only DepID = 2), then a new row should be added to the table for that employee with that missing DepID.
Desired Output:
EmpID DepID
12 2
17 3
17 2
13 3
12 3
13 2
In a t-sql 2012 sql update script listed below, it only works for a few records since the value of TST.dbo.LockCombination.seq only contains the value of 1 in most cases. Basically for every join listed below, there should be 5 records where each record has a distinct seq value of 1, 2, 3, 4, and 5. Thus my goal is to determine how to add the missing rows to the TST.dbo.LockCombination where there are no rows for seq values of between 2 to 5. I would like to know how to insert the missing rows and then do the following update statement. Thus can you show me the sql on how to add the rows for at least one of the missing sequence numbers?
UPDATE LKC
SET LKC.combo = lockCombo2
FROM [LockerPopulation] A
JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type
JOIN TST.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber = LKR.number
[Code] ....
I have a dynamic sql query where in I am comparing two tables and loading data for last 15 days. e.g today 2050921 then I am going to load till 20150906.
I pass on 2 variables @currentdate and @currentdate-1 to the query which are in date format 'yyyymmdd'
I need to do this for last 15 days how do I do this using while loop.
Note my date format is YYYYMMDD.
DECLARE @SQL VARCHAR(MAX)
@sql = ' insert into target
select from table_1_currentdate a
LEFT JOIN Table_2_currentdate-1 b
on a.col1=b.col1 where b.col1 is null '
exec(@sql)
I have to use while loop and decrement it every time and load data for last 15 days comparing two tables. I tried so many times I am not getting it right .
In SQL Studio, I can go to the restore window and the click "verify backup media". This would check the restore plan listed in this window and check if some of the file are missed.Is there any way to reach this with TQSL? I know there is a "restore verify" command, but this will only verify one backup/file and not the complete restore path. I'm looking for a TSQL solution which is able to control that all necessary backup files are still present on the file System and not moved or deleted (e.g through cleanup task).
View 3 Replies View RelatedLet's say if the date is 01/01/2015 till 01/01/2016
I want split these dates monthly format wise and then use them in variable in cursors to loop
For an example Monthly date should be 01/01/2015 and 01/31/2015 and use these two values in 2 variables and make a loop until it never ends whole date range which is 01/01/2016
I need to find the missing months in a table for the earliest and latest start dates per ID_No. As an example:
create table #InputTable (ID_No int ,OccurMonth datetime)
insert into #InputTable (ID_No,OccurMonth)
select 10, '2007-11-01' Union all
select 10, '2007-12-01' Union all
select 10, '2008-01-01' Union all
select 20, '2009-01-01' Union all
select 20, '2009-02-01' Union all
select 20, '2009-04-01' Union all
select 30, '2010-05-01' Union all
select 30, '2010-08-01' Union all
select 30, '2010-09-01' Union all
select 40, '2008-03-01'
For the above table, the answer should be:
ID_No OccurMonth
----- ----------
20 2009-02-01
30 2010-06-01
30 2010-07-01
1) don't include an ID column,
2) don't use the start date/end dates in the data or
3) use cursors, which are forbidden in my environment.
difference between FOR LOOP and FOREACH LOOP with example(if possible) in SSIS.
View 4 Replies View RelatedI have a table with RowID(identity). I need to loop though the table using RowID(not using a cursor). Please help me.
Thanks