Posted: 04 Oct 2013 06:23 AM PDT

What I need to do is create a loop that loops through the data to produce an email to send out. the following is sample data. I need to do it in a way to send one email to a person istead of in the case below up to 3. I want to sumarize the informaiton then send out email. I could do with a cursor easy but I am dealing with thousands of records and a cursor would have negative performance on tables. My attempt would be to pick the issuedID first then put in a cursor and loop around the records conatating to a temp table then selecting from here. Just wna to know if there is an easier way to do what I want. ThanksISSUEDID EMAIL OVERDUE DtISsued ST ITem3631 john.doe@mail 168 2013/04/19 DG5N4G3 DELL PRECISION T35004324 john.doe2@mail 38 2013/08/27 CWJ0181145Y EPSON PROJECTOR EMP 504413 hjane.doe@mail 161 2013/04/26 JDKXVV4 DELL PRECISION T35004413 hjane.doe@mail 161 2013/04/26 2Z1BLN5 DELL LATITUDE D6304413 hjane.doe@mail 161 2013/04/26 10Z2VJ6 DELL LATITUDE E640013581 henry.doe@mail 246 2013/01/31 125R4L4 DELL LATITUDE E6400

Posted: 04 Oct 2013 02:20 AM PDT

I am having a join issue because of duplicate value in my join keys. I cannot change the data source or the structure of tables. So how can I get my required output as described below.[code="sql"]CREATE TABLE Table1( Id INT, Name VARCHAR(50));CREATE TABLE Table2( Name VARCHAR(50), Code VARCHAR(10));INSERT INTO Table1 VALUES (1, 'Pen');INSERT INTO Table1 VALUES (2, 'Pen');INSERT INTO Table2 VALUES ('Pen', 'p1');INSERT INTO Table2 VALUES ('Pen', 'p2');[/code]Here is my current query[code="sql"]SELECT DISTINCT a.Id, a.Name, b.CodeFROM Table1 a INNER JOIN Table2 b ON a.Name = b.Name[/code]Here is the current outputId Name Code1 Pen p11 Pen p22 Pen p12 Pen p2But here is what i wantId Name Code1 Pen p12 Pen p2So basically i want to join the first record from table1 to first matching record in table2, and second record (with same name) from table1 should match to the second record (with same name) from table2 and so on.

Group on predominant fuzzy time

Posted: 04 Oct 2013 03:22 AM PDT

Ok, this will get you started with sample data:[code="sql"]create table #groupings (ID int,EventName varchar(255), Venue varchar(255),EventDate datetime)insert into #groupings values (1,'Football','Arena','2013-10-04 09:58:42.670'), (2,'Football','Arena','2013-10-04 09:58:42.670'), (3,'Football','Arena','2013-10-04 10:45:42.670'), (4,'Football','Arena','2013-10-04 09:58:42.670'), (1,'Soccer','Field','2013-10-04 09:58:42.670'), (2,'Soccer','Field','2013-10-04 09:38:42.670'), (3,'Soccer','Field','2013-10-04 09:58:42.670'), (4,'Soccer','Field','2013-10-04 09:28:42.670')select min(EventName) EventName, Venue, EventDate, stuff((select ',' + cast(ID as varchar) from #groupings where Venue = g.venue and Eventdate = g.eventdate for xml path ('')),1,1,'') IDsfrom #groupings ggroup by Venue, EventDateorder by venue, eventdate drop table #groupings[/code]This gives me:[quote]EventName Venue EventDate IDsFootball Arena 2013-10-04 09:58:42.670 1,2,4Football Arena 2013-10-04 10:45:42.670 3Soccer Field 2013-10-04 09:28:42.670 4Soccer Field 2013-10-04 09:38:42.670 2Soccer Field 2013-10-04 09:58:42.670 1,3[/quote]and what I need is:[quote]EventName Venue EventDate IDsFootball Arena 2013-10-04 09:58:42.670 1,2,3,4Soccer Field 2013-10-04 09:58:42.670 1,2,3,4[/quote]I know, group on EventName and Venue instead of time but that's not possible as the Event names in my real life data are all different and I have no option but to group on Venue and DateTime (thus the min(EventName) to homogenize them).What I don't know how to do is how to do something like:group by Venue, fuzzy-time(EventDate +/- an hour). Oh, and if I really want the data to be correct I need the incorrect times to yield to the majority. If there are two identical times and one unique time I need the result to be the time the "majority" agrees on.Ideas?Erin

Sql samples, sql tips

Saturday, October 5, 2013

[T-SQL] Create loop to send out emails

[T-SQL] Create loop to send out emails

No comments:

Post a Comment

Search This Blog