Wednesday, April 3, 2013

[T-SQL] Calculate number of days missed per term

[T-SQL] Calculate number of days missed per term

Calculate number of days missed per term

Posted: 02 Apr 2013 04:38 AM PDT

Need to update a new column (DaysMissed).Trying to calculate the number of days missed per term (accumulative) based on the term end date.Terms table contains the EndDate of each Term per LocID for each year.TestScores contains the Test taken with the score and what Term it was taken.Attend contains the PeriodNBR and the date (DateABS (date absent)). One day missed equals 8 periods, so the calculation is: DaysMissed = COUNT(DateABS)/8. In other words how many sets of 8 periods missed by the Term EndDate. This table is empty at the begining of a school year.Table creates, ddl, sample data:[code="sql"]CREATE TABLE TestScores( SchoolYR CHAR(9) not null, ID INT not null, LocID CHAR(4) not null, TestName VARCHAR(30) not null, Term TINYINT not null, DaysMissed TINYINT not null, Score decimal(6,2) not null )GOINSERT TestScores (SchoolYR, ID, LocID, TestName, Term, DaysMissed, Score) VALUES('2012-2013', 414, '355', 'CSA 1-2', 1, 0, 21.00),('2012-2013', 414, '355', 'CSA 2-2', 2, 0, 23.00),('2012-2013', 414, '355', 'CSA 3-1', 3, 0, 16.00),('2012-2013', 414, '355', 'CSA 4-1', 4, 0, 16.00),('2012-2013', 414, '355', 'CSA 4-2', 4, 0, 23.00),('2012-2013', 450, '355', 'CSA 1-1', 1, 0, 17.00),('2012-2013', 450, '355', 'CSA 2-3', 2, 0, 16.00),('2012-2013', 450, '355', 'CSA 3-2', 3, 0, 17.00),('2012-2013', 450, '355', 'CSA 3-3', 3, 0, 16.00),('2012-2013', 450, '355', 'CSA 4-1', 4, 0, 15.00),('2012-2013', 450, '355', 'CSA 4-2', 4, 0, 17.00),('2012-2013', 450, '355', 'CSA 4-3', 4, 0, 16.00),('2012-2013', 450, '355', 'CSA 4-4', 4, 0, 23.00),('2012-2013', 450, '355', 'CSA 3-1', 3, 0, 16.00),('2012-2013', 450, '355', 'CSA 1-2', 1, 0, 23.00),('2012-2013', 451, '355', 'CSA 1-1', 1, 0, 15.00),('2012-2013', 451, '355', 'CSA 2-2', 2, 0, 17.00),('2012-2013', 451, '355', 'CSA 2-3', 2, 0, 16.00),('2012-2013', 451, '355', 'CSA 2-4', 2, 0, 23.00),('2012-2013', 451, '355', 'CSA 3-1', 3, 0, 15.00),('2012-2013', 451, '355', 'CSA 3-2', 3, 0, 17.00),('2012-2013', 451, '355', 'CSA 3-3', 3, 0, 16.00),('2012-2013', 451, '355', 'CSA 3-4', 3, 0, 23.00),('2012-2013', 451, '355', 'CSA 4-1', 4, 0, 15.00),('2012-2013', 451, '355', 'CSA 4-2', 4, 0, 17.00),('2012-2013', 451, '355', 'CSA 4-3', 4, 0, 16.00),('2012-2013', 451, '355', 'CSA 4-4', 4, 0, 23.00),('2012-2013', 717, '344', 'CSA 4-1', 4, 0, 15.00),('2012-2013', 717, '344', 'CSA 4-2', 4, 0, 17.00),('2012-2013', 717, '344', 'CSA 4-3', 4, 0, 16.00),('2012-2013', 717, '344', 'CSA 4-4', 4, 0, 23.00),('2012-2013', 922, '344', 'CSA 4-1', 4, 0, 15.00),('2012-2013', 922, '344', 'CSA 4-2', 4, 0, 16.00),('2012-2013', 2735, '344', 'CSA 4-1', 4, 0, 15.00),('2012-2013', 2735, '344', 'CSA 4-2', 4, 0, 21.00),('2012-2013', 2735, '344', 'CSA 4-3', 4, 0, 15.00),('2012-2013', 4343, '355', 'CSA 4-2', 4, 0, 16.00),('2012-2013', 4343, '355', 'CSA 4-3', 4, 0, 23.00),('2012-2013', 6831, '344', 'CSA 4-3', 4, 0, 16.00),('2012-2013', 6831, '344', 'CSA 4-4', 4, 0, 23.00),('2012-2013', 8343, '355', 'CSA 4-1', 4, 0, 16.00),('2012-2013', 8343, '355', 'CSA 4-2', 4, 0, 23.00),('2012-2013', 9831, '344', 'CSA 4-2', 4, 0, 16.00),('2012-2013', 9831, '344', 'CSA 4-3', 4, 0, 23.00)GOCREATE TABLE Terms( SchoolYR CHAR(9) not null, LocID CHAR(4) not null, Term TINYINT not null, EndDate DATE not null)GOINSERT Terms (SchoolYR, Term, LocID, EndDate) VALUES('2012-2013', 1, '355', '2012-10-12'),('2012-2013', 2, '355', '2012-12-20'),('2012-2013', 3, '355', '2013-03-07'),('2012-2013', 4, '355', '2013-05-24'),('2012-2013', 1, '344', '2012-10-12'),('2012-2013', 2, '344', '2012-12-20'),('2012-2013', 3, '344', '2013-03-07'),('2012-2013', 4, '344', '2013-05-24')GOCREATE TABLE Attend( ID INT not null, PeriodNBR Tinyint not null, DateABS SMALLDATETIME not null)GOINSERT Attend(ID, PeriodNBR, DateABS)VALUES(410, 8, '2013-01-03 00:00:00'),(414, 8, '2013-01-03 00:00:00'),(414, 7, '2013-01-03 00:00:00'),(414, 6, '2013-01-03 00:00:00'),(414, 5, '2013-01-03 00:00:00'),(414, 4, '2013-01-03 00:00:00'),(414, 3, '2013-01-03 00:00:00'),(414, 2, '2013-01-03 00:00:00'),(414, 1, '2013-01-03 00:00:00'),(414, 6, '2012-12-19 00:00:00'),(414, 5, '2012-12-19 00:00:00'),(414, 6, '2012-12-12 00:00:00'),(414, 5, '2012-12-12 00:00:00'),(414, 4, '2012-12-12 00:00:00'),(414, 3, '2012-12-12 00:00:00'),(414, 2, '2012-12-12 00:00:00'),(414, 7, '2012-11-14 00:00:00'),(414, 7, '2012-08-15 00:00:00'),(450, 8, '2013-03-21 00:00:00'),(450, 7, '2013-03-21 00:00:00'),(450, 1, '2013-03-15 00:00:00'),(450, 2, '2013-03-14 00:00:00'),(450, 1, '2013-03-14 00:00:00'),(450, 8, '2013-03-12 00:00:00'),(450, 7, '2013-03-12 00:00:00'),(450, 6, '2013-03-12 00:00:00'),(450, 5, '2013-03-12 00:00:00'),(450, 4, '2013-03-12 00:00:00'),(450, 3, '2013-03-12 00:00:00'),(450, 2, '2013-03-12 00:00:00'),(450, 1, '2013-03-12 00:00:00'),(450, 1, '2013-03-04 00:00:00'),(450, 1, '2013-02-19 00:00:00'),(450, 1, '2013-02-04 00:00:00'),(450, 1, '2013-01-31 00:00:00'),(450, 8, '2013-01-29 00:00:00'),(450, 7, '2013-01-29 00:00:00'),(450, 6, '2013-01-29 00:00:00'),(450, 5, '2013-01-29 00:00:00'),(450, 4, '2013-01-29 00:00:00'),(450, 3, '2013-01-29 00:00:00'),(450, 2, '2013-01-29 00:00:00'),(450, 1, '2013-01-29 00:00:00'),(450, 8, '2013-01-28 00:00:00'),(450, 7, '2013-01-28 00:00:00'),(450, 6, '2013-01-28 00:00:00'),(450, 5, '2013-01-28 00:00:00'),(450, 4, '2013-01-28 00:00:00'),(450, 3, '2013-01-28 00:00:00'),(450, 2, '2013-01-28 00:00:00'),(450, 1, '2013-01-28 00:00:00'),(450, 8, '2013-01-25 00:00:00'),(450, 7, '2013-01-25 00:00:00'),(450, 6, '2013-01-25 00:00:00'),(450, 5, '2013-01-25 00:00:00'),(450, 4, '2013-01-25 00:00:00'),(450, 3, '2013-01-25 00:00:00'),(450, 2, '2013-01-25 00:00:00'),(450, 1, '2013-01-25 00:00:00'),(450, 1, '2013-01-24 00:00:00'),(450, 1, '2013-01-22 00:00:00'),(450, 1, '2013-01-14 00:00:00'),(450, 1, '2012-12-18 00:00:00'),(450, 8, '2012-12-14 00:00:00'),(450, 7, '2012-12-14 00:00:00'),(450, 6, '2012-12-14 00:00:00'),(450, 5, '2012-12-14 00:00:00'),(450, 4, '2012-12-14 00:00:00'),(450, 3, '2012-12-14 00:00:00'),(450, 2, '2012-12-14 00:00:00'),(450, 1, '2012-12-14 00:00:00'),(450, 8, '2012-11-30 00:00:00'),(450, 7, '2012-11-30 00:00:00'),(450, 6, '2012-11-30 00:00:00'),(450, 5, '2012-11-30 00:00:00'),(450, 2, '2012-11-16 00:00:00'),(450, 1, '2012-11-16 00:00:00'),(450, 8, '2012-11-09 00:00:00'),(450, 7, '2012-11-09 00:00:00'),(450, 6, '2012-11-09 00:00:00'),(450, 5, '2012-11-09 00:00:00'),(450, 4, '2012-11-09 00:00:00'),(450, 3, '2012-11-09 00:00:00'),(450, 2, '2012-11-09 00:00:00'),(450, 1, '2012-11-09 00:00:00'),(450, 1, '2012-11-01 00:00:00'),(450, 1, '2012-10-30 00:00:00'),(450, 7, '2012-10-24 00:00:00'),(450, 6, '2012-10-24 00:00:00'),(450, 5, '2012-10-24 00:00:00'),(450, 1, '2012-10-24 00:00:00'),(450, 8, '2012-10-16 00:00:00'),(450, 7, '2012-10-16 00:00:00'),(450, 6, '2012-10-16 00:00:00'),(450, 5, '2012-10-16 00:00:00'),(450, 4, '2012-10-16 00:00:00'),(450, 3, '2012-10-16 00:00:00'),(450, 2, '2012-10-16 00:00:00'),(450, 1, '2012-10-16 00:00:00'),(450, 7, '2012-10-10 00:00:00'),(450, 8, '2012-10-09 00:00:00'),(450, 7, '2012-10-09 00:00:00'),(450, 6, '2012-10-09 00:00:00'),(450, 5, '2012-10-09 00:00:00'),(450, 4, '2012-10-09 00:00:00'),(450, 3, '2012-10-09 00:00:00'),(450, 2, '2012-10-09 00:00:00'),(450, 1, '2012-10-09 00:00:00'),(450, 8, '2012-10-08 00:00:00'),(450, 7, '2012-10-08 00:00:00'),(450, 6, '2012-10-08 00:00:00'),(450, 5, '2012-10-08 00:00:00'),(450, 4, '2012-10-08 00:00:00'),(450, 3, '2012-10-08 00:00:00'),(450, 2, '2012-10-08 00:00:00'),(450, 1, '2012-10-08 00:00:00'),(450, 8, '2012-10-05 00:00:00'),(450, 7, '2012-10-05 00:00:00'),(450, 6, '2012-10-05 00:00:00'),(450, 5, '2012-10-05 00:00:00'),(450, 4, '2012-10-05 00:00:00'),(450, 3, '2012-10-05 00:00:00'),(450, 2, '2012-10-05 00:00:00'),(450, 1, '2012-10-05 00:00:00'),(450, 8, '2012-10-04 00:00:00'),(450, 7, '2012-10-04 00:00:00'),(450, 6, '2012-10-04 00:00:00'),(450, 5, '2012-10-04 00:00:00'),(450, 4, '2012-10-04 00:00:00'),(450, 3, '2012-10-04 00:00:00'),(450, 2, '2012-10-04 00:00:00'),(450, 1, '2012-10-04 00:00:00'),(450, 8, '2012-10-03 00:00:00'),(450, 7, '2012-10-03 00:00:00'),(450, 6, '2012-10-03 00:00:00'),(450, 5, '2012-10-03 00:00:00'),(450, 4, '2012-10-03 00:00:00'),(450, 3, '2012-10-03 00:00:00'),(450, 2, '2012-10-03 00:00:00'),(450, 1, '2012-10-03 00:00:00'),(450, 8, '2012-10-02 00:00:00'),(450, 7, '2012-10-02 00:00:00'),(450, 6, '2012-10-02 00:00:00'),(450, 5, '2012-10-02 00:00:00'),(450, 4, '2012-10-02 00:00:00'),(450, 3, '2012-10-02 00:00:00'),(450, 2, '2012-10-02 00:00:00'),(450, 1, '2012-10-02 00:00:00'),(450, 8, '2012-10-01 00:00:00'),(450, 7, '2012-10-01 00:00:00'),(450, 6, '2012-10-01 00:00:00'),(450, 5, '2012-10-01 00:00:00'),(450, 4, '2012-10-01 00:00:00'),(450, 3, '2012-10-01 00:00:00'),(450, 2, '2012-10-01 00:00:00'),(450, 1, '2012-10-01 00:00:00'),(450, 1, '2012-09-18 00:00:00'),(450, 7, '2012-09-05 00:00:00'),(450, 6, '2012-09-05 00:00:00'),(450, 8, '2012-08-24 00:00:00'),(450, 7, '2012-08-24 00:00:00'),(451, 8, '2013-03-21 00:00:00'),(451, 7, '2013-03-21 00:00:00'),(451, 8, '2013-03-18 00:00:00'),(451, 7, '2013-03-18 00:00:00'),(451, 6, '2013-03-18 00:00:00'),(451, 5, '2013-03-18 00:00:00'),(451, 4, '2013-03-18 00:00:00'),(451, 3, '2013-03-18 00:00:00'),(451, 2, '2013-03-18 00:00:00'),(451, 1, '2013-03-18 00:00:00'),(451, 1, '2013-03-15 00:00:00'),(451, 2, '2013-03-14 00:00:00'),(451, 1, '2013-03-14 00:00:00'),(451, 1, '2013-02-19 00:00:00'),(451, 8, '2013-02-11 00:00:00'),(451, 7, '2013-02-11 00:00:00'),(451, 6, '2013-02-11 00:00:00'),(451, 5, '2013-02-11 00:00:00'),(451, 4, '2013-02-11 00:00:00'),(451, 3, '2013-02-11 00:00:00'),(451, 2, '2013-02-11 00:00:00'),(451, 1, '2013-02-11 00:00:00'),(451, 8, '2013-02-04 00:00:00'),(451, 7, '2013-02-04 00:00:00'),(451, 6, '2013-02-04 00:00:00'),(451, 5, '2013-02-04 00:00:00'),(451, 4, '2013-02-04 00:00:00'),(451, 3, '2013-02-04 00:00:00'),(451, 2, '2013-02-04 00:00:00'),(451, 1, '2013-02-04 00:00:00'),(451, 1, '2013-01-31 00:00:00'),(451, 2, '2013-01-28 00:00:00'),(451, 1, '2013-01-28 00:00:00'),(451, 1, '2013-01-24 00:00:00'),(451, 1, '2013-01-22 00:00:00'),(451, 8, '2013-01-16 00:00:00'),(451, 7, '2013-01-16 00:00:00'),(451, 6, '2013-01-16 00:00:00'),(451, 5, '2013-01-16 00:00:00'),(451, 4, '2013-01-16 00:00:00'),(451, 3, '2013-01-16 00:00:00'),(451, 2, '2013-01-16 00:00:00'),(451, 1, '2013-01-16 00:00:00'),(451, 8, '2013-01-15 00:00:00'),(451, 7, '2013-01-15 00:00:00'),(451, 6, '2013-01-15 00:00:00'),(451, 5, '2013-01-15 00:00:00'),(451, 4, '2013-01-15 00:00:00'),(451, 3, '2013-01-15 00:00:00'),(451, 2, '2013-01-15 00:00:00'),(451, 1, '2013-01-15 00:00:00'),(451, 1, '2013-01-14 00:00:00'),(451, 8, '2012-12-14 00:00:00'),(451, 7, '2012-12-14 00:00:00'),(451, 6, '2012-12-14 00:00:00'),(451, 5, '2012-12-14 00:00:00'),(451, 4, '2012-12-14 00:00:00'),(451, 3, '2012-12-14 00:00:00'),(451, 2, '2012-12-14 00:00:00'),(451, 1, '2012-12-14 00:00:00'),(451, 1, '2012-12-04 00:00:00'),(451, 8, '2012-11-30 00:00:00'),(451, 7, '2012-11-30 00:00:00'),(451, 6, '2012-11-30 00:00:00'),(451, 5, '2012-11-30 00:00:00'),(451, 7, '2012-11-28 00:00:00'),(451, 6, '2012-11-28 00:00:00'),(451, 5, '2012-11-28 00:00:00'),(451, 7, '2012-11-27 00:00:00'),(451, 8, '2012-11-26 00:00:00'),(451, 7, '2012-11-26 00:00:00'),(451, 6, '2012-11-26 00:00:00'),(451, 5, '2012-11-26 00:00:00'),(451, 4, '2012-11-26 00:00:00'),(451, 3, '2012-11-26 00:00:00'),(451, 2, '2012-11-26 00:00:00'),(451, 1, '2012-11-26 00:00:00'),(451, 2, '2012-11-16 00:00:00'),(451, 1, '2012-11-16 00:00:00'),(451, 8, '2012-11-02 00:00:00'),(451, 7, '2012-11-02 00:00:00'),(451, 6, '2012-11-02 00:00:00'),(451, 5, '2012-11-02 00:00:00'),(451, 4, '2012-11-02 00:00:00'),(451, 3, '2012-11-02 00:00:00'),(451, 2, '2012-11-02 00:00:00'),(451, 1, '2012-11-02 00:00:00'),(451, 8, '2012-11-01 00:00:00'),(451, 7, '2012-11-01 00:00:00'),(451, 6, '2012-11-01 00:00:00'),(451, 1, '2012-11-01 00:00:00'),(451, 1, '2012-10-30 00:00:00'),(451, 7, '2012-10-26 00:00:00'),(451, 1, '2012-10-24 00:00:00'),(451, 7, '2012-10-10 00:00:00'),(451, 8, '2012-10-09 00:00:00'),(451, 7, '2012-10-09 00:00:00'),(451, 6, '2012-10-09 00:00:00'),(451, 5, '2012-10-09 00:00:00'),(451, 4, '2012-10-09 00:00:00'),(451, 3, '2012-10-09 00:00:00'),(451, 2, '2012-10-09 00:00:00'),(451, 1, '2012-10-09 00:00:00'),(451, 8, '2012-10-08 00:00:00'),(451, 7, '2012-10-08 00:00:00'),(451, 6, '2012-10-08 00:00:00'),(451, 5, '2012-10-08 00:00:00'),(717, 8, '2013-01-03 00:00:00'),(717, 7, '2013-01-03 00:00:00'),(717, 6, '2013-01-03 00:00:00'),(717, 5, '2013-01-03 00:00:00'),(717, 4, '2013-01-03 00:00:00'), (717, 3, '2013-01-03 00:00:00'),(717, 2, '2013-01-03 00:00:00'),(717, 1, '2013-01-03 00:00:00'),(717, 8, '2012-12-19 00:00:00')GO[/code] Here is code I've been trying to combine without any luck:[code="sql"]select ID, DaysMissed = COUNT(DateABS)/8from Attend where DateABS <= '2013-05-24' --the Terms.EndDategroup by IDorder by ID[/code][code="sql"]select s.ID, s.TestName, s.Term, s.Daysmissed,t.EndDatefrom TestScores as sjoin Terms as ton s.SchoolYR = t.SchoolYRand s.Term = t.Termand s.LocID = t.LocIDwhere s.Term in (1, 2, 3, 4)[/code]The days missed is accumulative per the school year.The desired output should look like this:[code="plain"] ID TestName Term Daysmissed EndDate414 CSA 1-2 1 0 2012-10-12414 CSA 2-2 2 1 2012-12-20414 CSA 3-1 3 2 2013-03-07414 CSA 4-1 4 2 2013-05-24414 CSA 4-2 4 2 2013-05-24450 CSA 1-1 1 7 2012-10-12450 CSA 2-3 2 12 2012-12-20450 CSA 3-2 3 16 2013-03-07450 CSA 3-3 3 16 2013-03-07450 CSA 4-1 4 17 2013-05-24450 CSA 4-2 4 17 2013-05-24450 CSA 4-3 4 17 2013-05-24450 CSA 4-4 4 17 2013-05-24450 CSA 3-1 3 16 2013-03-07450 CSA 1-2 1 7 2012-10-12451 CSA 1-1 1 1 2012-10-12451 CSA 2-2 2 6 2012-12-20451 CSA 2-3 2 6 2012-12-20451 CSA 2-4 2 6 2012-12-20451 CSA 3-1 3 11 2013-03-07451 CSA 3-2 3 11 2013-03-07451 CSA 3-3 3 11 2013-03-07451 CSA 3-4 3 11 2013-03-07451 CSA 4-1 4 13 2013-05-24451 CSA 4-2 4 13 2013-05-24451 CSA 4-3 4 13 2013-05-24451 CSA 4-4 4 13 2013-05-24717 CSA 4-1 4 1 2013-05-24717 CSA 4-2 4 1 2013-05-24717 CSA 4-3 4 1 2013-05-24717 CSA 4-4 4 1 2013-05-24922 CSA 4-1 4 0 2013-05-24922 CSA 4-2 4 0 2013-05-242735 CSA 4-1 4 0 2013-05-242735 CSA 4-2 4 0 2013-05-242735 CSA 4-3 4 0 2013-05-244343 CSA 4-2 4 0 2013-05-244343 CSA 4-3 4 0 2013-05-246831 CSA 4-3 4 0 2013-05-246831 CSA 4-4 4 0 2013-05-248343 CSA 4-1 4 0 2013-05-248343 CSA 4-2 4 0 2013-05-249831 CSA 4-2 4 0 2013-05-249831 CSA 4-3 4 0 2013-05-24[/code]Thanks,Sqlraider

Insert into new table based on conditions

Posted: 02 Apr 2013 11:42 PM PDT

Hi all,I'm struggling with a query which runs forever and I can't figure out what the problem is.In attachment you will find a file to create and fill 3 tables to test.I will try to explain what I have to accomplish.I have to select data from 2 tables (Application_Useraccounts and Application_Useraccounts_Status) and insert it into a 3rd table (Tussen_Statussen_Adviseur). The column Useraccount_Status_Duration from the table Application_Useraccounts_Status has to be inserted into the correct column in the table Tussen_Statussen_Adviseur based on the ID in the column Useraccount_Status_ID from the table Application_Useraccounts_Status.For example:If the ID is 1, then the Useraccount_Status_Duration should go into column Duration_UserLogin.If the ID is 2, then the Useraccount_Status_Duration should go into column Duration_PauzeKort.If the ID is 3, then the Useraccount_Status_Duration should go into column Duration_PauzeLang.etc...An example as result can be found in the table Tussen_Statussen_Adviseur that will be created in the test file.At this moment I have the following query, but it runs forever and does not end.[code="sql"]--===Insert the result from the select into the new table.===INSERT INTO Tussen_Statussen_Adviseur (Useraccount_Name, Status_DateTime, Duration_UserLogin, Duration_PauzeKort, Duration_PauzeLang, Duration_Beschikbaar, Duration_Storing, Duration_Werkoverleg, Duration_NietBeschikbaar, Duration_Overdracht, Duration_Beheer) SELECT (COALESCE(dtSums.Useraccount_First_Name, '') + ' ' + COALESCE(dtSums.Useraccount_Last_Name, '')) AS Useraccount_Name, dtSums.Useraccount_Status_DateTime, dtSums.Duration_UserLogin, dtSums.Duration_PauzeKort, dtSums.Duration_PauzeLang, dtSums.Duration_Beschikbaar, dtSums.Duration_Storing, dtSums.Duration_Werkoverleg, dtSums.Duration_NietBeschikbaar, dtSums.Duration_Overdracht, dtSums.Duration_Beheer FROM ( --===Derived table finds the duration in the column Useraccount_Status_Duration and puts it in the desired column based on the ID in the column Useraccount_Status_ID.=== SELECT UA.Useraccount_First_Name, UA.Useraccount_Last_Name, US.Useraccount_Status_DateTime, US1.Useraccount_Status_Duration AS Duration_UserLogin, US2.Useraccount_Status_Duration AS Duration_PauzeKort, US3.Useraccount_Status_Duration AS Duration_PauzeLang, US4.Useraccount_Status_Duration AS Duration_Beschikbaar, US5.Useraccount_Status_Duration AS Duration_Storing, US6.Useraccount_Status_Duration AS Duration_Werkoverleg, US7.Useraccount_Status_Duration AS Duration_NietBeschikbaar, US8.Useraccount_Status_Duration AS Duration_Overdracht, US9.Useraccount_Status_Duration AS Duration_Beheer FROM Application_Useraccounts_Status AS US LEFT OUTER JOIN Application_Useraccounts AS UA ON US.Useraccount_ID = UA.Useraccount_ID LEFT OUTER JOIN Application_Useraccounts_Status AS US1 ON US1.Useraccount_ID = UA.Useraccount_ID AND US1.Useraccount_Status_ID = 1 LEFT OUTER JOIN Application_Useraccounts_Status AS US2 ON US1.Useraccount_ID = UA.Useraccount_ID AND US2.Useraccount_Status_ID = 2 LEFT OUTER JOIN Application_Useraccounts_Status AS US3 ON US1.Useraccount_ID = UA.Useraccount_ID AND US3.Useraccount_Status_ID = 3 LEFT OUTER JOIN Application_Useraccounts_Status AS US4 ON US1.Useraccount_ID = UA.Useraccount_ID AND US4.Useraccount_Status_ID = 4 LEFT OUTER JOIN Application_Useraccounts_Status AS US5 ON US1.Useraccount_ID = UA.Useraccount_ID AND US5.Useraccount_Status_ID = 9 LEFT OUTER JOIN Application_Useraccounts_Status AS US6 ON US1.Useraccount_ID = UA.Useraccount_ID AND US6.Useraccount_Status_ID = 10 LEFT OUTER JOIN Application_Useraccounts_Status AS US7 ON US1.Useraccount_ID = UA.Useraccount_ID AND US7.Useraccount_Status_ID = 11 LEFT OUTER JOIN Application_Useraccounts_Status AS US8 ON US1.Useraccount_ID = UA.Useraccount_ID AND US8.Useraccount_Status_ID = 12 LEFT OUTER JOIN Application_Useraccounts_Status AS US9 ON US1.Useraccount_ID = UA.Useraccount_ID AND US9.Useraccount_Status_ID = 13 ) AS dtSums[/code]Can someone tell what I'm doing wrong and where I have to correct the query to run it in a good way and that will give me the desired result?Thank you very much for your help and assistance.Greetz,Geert

difference betwwn inner join and intersect in sql

Posted: 24 Mar 2013 07:56 PM PDT

I would like to know when to use inner join/outer join and intersect/except as they probably are doing the same job if not mistaken.what is difference while working with joins and using these keyword intersect/except.

how to rename columns that you are unpivoting on?

Posted: 02 Apr 2013 09:05 AM PDT

I've successfully unpivoted Months01 through Months02 with a query that looks like this:[code="plain"]/****** Script for SelectTopNRows command from SSMS ******/SELECT [FullAccount] ,[BusinessUnit] ,[ObjAcct] ,[Sub] ,[LT] ,[Century] ,[FY] ,Period ,sum(Qty) as ValueFROM (SELECT [FullAccount] ,[BusinessUnit] ,[ObjAcct] ,[Sub] ,[LT] ,[Century] ,[FY] ,[Month01] ,[Month02] ,[Month03] ,[Month04] ,[Month05] ,[Month06] ,[Month07] ,[Month08] ,[Month09] ,[Month10] ,[Month11] ,[Month12] ,[Month13] ,[Month14] ,[Month15] ,[Month16] ,[Month17] ,[Month18] ,[Month19] ,[Month20] ,[Month21] ,[Month22] ,[Month23] ,[Month24] ,[FYPlus2] ,[FYPlus3] ,[FYPlus4] ,[Source] ,[Memo1] ,[Memo2] ,[lastchgby] ,[lastchgdate] ,[FiscalYear] ,[ForecastType] ,[CurrentForecast] FROM [ForecastTemplate].[dbo].[Allocations]) T unpivot (Qty FOR Period IN ( [Month01] ,[Month02] ,[Month03] ,[Month04] ,[Month05] ,[Month06] ,[Month07] ,[Month08] ,[Month09] ,[Month10] ,[Month11] ,[Month12] ,[Month13] ,[Month14] ,[Month15] ,[Month16] ,[Month17] ,[Month18] ,[Month19] ,[Month20] ,[Month21] ,[Month22] ,[Month23] ,[Month24] ,[FYPlus2] ,[FYPlus3] ,[FYPlus4]) ) as u group by [FullAccount] ,[BusinessUnit] ,[ObjAcct] ,[Sub] ,[LT] ,[Century] ,[FY] ,Period order by period [/code]How can I rename the columns I am unpivoting on? I tried 'AS' statement in the unpivot portion but got an error....please see if you can offer suggestions without DDL. Otherwise, I will try to post DDL later. Thanks much

TSQL CTE Insert

Posted: 02 Apr 2013 11:35 AM PDT

Hello,I was sure I had asked this before but I can't find that post so my apologies for the repeat question. I need to do an insert into two tables X times. X is TBD from a control table. Let's just say three times. So I need the same record inserted 3 times except for one field that will be whatever X is. Table A & B have a relationship. For example:INSERT TableA (ColumnA, ColumnB, ColumnX)VALUES (ValueA, ValueB, NULL)INSERT TableB (ColumnA, ColumnB, ColumnC)VALUES (SCOPE_IDENTITY, ValueB, ValueC)Now I need to do this three time for our example and I don't want RBAR. I believe you do this with a join or a cte I think. Not sure what to brush up on exactly.So this would get me my loop valuesDECLARE @Loop TABLE (RepeatCount INT)INSERT @Loop SELECT NumRepeats FROM ControlTable WHERE ID = 123So I would then use a CTE to join the inserts with the @Loop correct?JB

LTRIM,RTRIM

Posted: 21 Oct 2011 12:29 AM PDT

hii have this code in where clause RTRIM(LTRIM(ODER_ID))= ' ',WHAT THis do

Stored Procedure Records

Posted: 02 Apr 2013 03:09 AM PDT

I have created a stored procedure that will return approved purchase orders when executed. However, if I have it kickoff several times a day I do not want to return purchase order records that were returned on the previous executions. Any ideas on the best way to set this up? I thought about putting the data over into a temp table to do the comparisons or maybe have it look at time stamp info. This will actually be kicked off from a SharePoint workflow. Any help or suggestions will be greatly appreciated.

bcp output of table variable

Posted: 02 Apr 2013 05:20 AM PDT

DECLARE @tempOutput TABLE (vend_num NVARCHAR(7) NULL ,name NVARCHAR(60) NULL ,pay_type NCHAR(1) NULL ,check_num INT NULL ,date DATETIME NULL ,check_date DATETIME NULL ,)DECLARE @cmd VARCHAR(2048) SET @cmd = 'bcp ' +' "SELECT * FROM @tempOutput WHERE @Severity = 0" ' + ' queryout ' +' "c:\temp\query.txt" ' +' -T -c -t^|' EXEC master..xp_cmdshell @cmd, NO_OUTPUTThis does not create a file.How do i get this to work ?

Improve performance of query involving linked server

Posted: 02 Apr 2013 04:20 AM PDT

I am trying to make improvements to a job that does the following.1) Load a table with about 40,000 rows (Pretty straight forward, so there isn't much to change here)2) Call a stored proc that joins this table's data with big set of data from a linked server. This proc reads data from the above table (where one of the columns is flagged '0'), and does a lot of gimmicks to process it and sets the flag to '1'3) This stored proc is coded such that it takes a parameter for the number of rows to be processed. Currently the call is made with @rowsToProcess as 250. The code looks as below...WHILE EXISTS (SELECT 1 FROM MyTable WHERE MyFlag = 0)BEGIN EXEC dbo.MyProc @Server = 'MYREMOTESQLSERVER', @DBName = 'MYRemoteDBN', @rowsToProcess = 250ENDCan anyone suggest if there is a better idea to run this more efficiently? For now, I only have room to play around how to call this proc to process all rows in "MyTable". In other words, I can't influence how data is organized/arranged in the tables on the linked server.Thanks in advance,Rex

retrieve records by joining history table

Posted: 02 Apr 2013 03:40 AM PDT

HI,Can you help me with the query. Thanks for any help. The below is the example scenario:Master_tablepk status_date status amount1 1/1/13 approved 1002 12/1/12 denied 03 11/1/11 in progress 2004 10/1/12 approved 300Status History tablepk pk_master_table status_date status1 1 11/30/12 in progress3 1 12/10/2 under review5 1 1/1/13 approved2 2 10/1/12 in progress4 2 11/1/12 under review6 2 12/1/12 denied7 3 11/1/11 in progress8 4 9/1/12 in progress9 4 9/15/12 under review10 4 10/1/12 approvedThe query will have an input: Status date, example 11/15/12Output required: All the master_table records on their snap shot status <= 'input date' - As of the input date, what was the status of the recordThe below will be the output of the queryAPPROVED1 record - pk is 4DENIED0 recordIN PROGRESS1 record - pk is 3 - note: pk 4 does not show up in this list as the 'approved date' is the higher, though the history has 'in progress' less than input 11/15UNDER REVIEW1 record - pk is 2Thanks,
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog