Transact SQL :: Get Max Occurring Value Using GROUP BY

May 14, 2015

I have 3 columns of data CustomerNum, Newsletter, and NumSent.

I need to return the only the CustomerNumber and Newsletter combinations having the Max(NumSent) So it should be a unique customernumber with the newletter having the most numsent.

For the data below, my result set will be:

0000000000000000101 Healthcare
0000000000000000102 Construction-Environ Svcs

How can I do this easily with GROUP BY , Max or subselect?

Sample Data:
0000000000000000101 Healthcare                       19
0000000000000000101 Construction-Environ Svcs 11
0000000000000000101 Manufacturing                   8               

0000000000000000101 Homecare                        5
0000000000000000101 Daycare                          4
0000000000000000102 Healthcare                       9
0000000000000000102 Construction-Environ Svcs 21
0000000000000000102 Manufacturing                   5              

0000000000000000102 Homecare                        11
0000000000000000102 Daycare                          1

View 6 Replies


ADVERTISEMENT

Transact SQL :: Check No Inserts Occurring In Tables

Sep 14, 2015

I want to check that no inserts are occurring in 5 tables that are depending on each other and then drop and create those 5 tables. I have scripts to drop and recreate the tables. How do I check that no inserts are happening in these 5 tables?

Table A
Table B dependant on
Table A
Table C dependant on
Table B
Table D dependant on
Table C
Table E dependant on
Table D

View 9 Replies View Related

Transact SQL :: Query To Give Results Of All Configured Alerts Occurring History

May 14, 2015

We created sql alerts on all our sql servers environments. Now, i want to see each sql server which sql alerts so far got fired and which one never occurs. is there any way, we can get this information from any system database?

View 9 Replies View Related

Transact SQL :: Flag Yes Or No Within ID Group

May 20, 2015

I have a table with 4 columns,let say ID, WorkId , HireDate ,TerminateDate

My source date

ID   WorkId   HireDate    TerminateDate
1     123    2006-01-05   2013-01-13
1     456    2006-02-23   2013-04-13
1     789    2005-12-12   2013-06-13
2     913    2004-12-15   2012-01-13
2     406    2006-04-13   2014-04-13
3     113    2009-11-15   2011-01-13
3     106    2010-04-13  
4     513    2002-11-15  
4     168    2011-10-23  
5     342    2007-01-05   2009-01-13      
5     416    2008-02-23                   
5     159    2008-12-12   2010-06-13   
6     542    2005-01-03   2007-01-13      

Rule -1:  set flag = yes

-Whenever terminatedate is not null
-Also with in the ID group if one record terminatedate is null then set flag=yes

Rule-2: Set flag=No
- with in the ID group Whenever terminatedate is null

My output should look like this..

ID   WorkId   HireDate    TerminateDate     Flag
1     123    2006-01-05   2013-01-13        yes ( Rule-1: set flag = yes within the group for ID =1 because all the 3 records terminatedate is not null)
1     456    2006-02-23   2013-04-13        yes
1     789    2005-12-12   2013-06-13        yes
 2     913    2004-12-15   2012-01-13       yes (Rule-1: set flag = yes within the group for ID =2 because all the 2 records terminatedate is not null)

[Code] .....

View 3 Replies View Related

Transact SQL :: Row Over Partition - How To Group It

Sep 8, 2015

If I have the first two columns from a SQL query and would like to create the third (row):

personid new_commsstream row
1 0 1
1 0 1
1 0 1
1 1 2
1 0 2
2 0 1
3 0 1
4 0 1
5 0 1
5 1 2
5 1 3

Is this possible? I have tried using row over partition but I'm not sure how group it correctly, so basically every time there is a new 1 in new_commsstream within a personid the row number goes up by one.

View 3 Replies View Related

Transact SQL :: How To Show Value In Only One Group

Nov 3, 2015

I have a table with "Project", "Demo" and "Value". Here is a sample. Is there a way to show the "Value" in just one Demo after a I use group by?

Project  Demo Value

Project1 Adult 100
Project1 Kids 100
Project1 Adult 100
Project2 Adult 200
Project2 Adult 200
Project2 Kids 200
Project2 Kids 200

That is after I ran the SQL: I will get only: (see Project1's Kids's value is null now.

Project  Demo Value

Project1 Adult 100
Project1 Kids
Project2 Adult 200
Project2 Kids 200

View 12 Replies View Related

Transact SQL :: Get Sum From 2 Table By Group

Sep 3, 2015

I have 2 table (Employee T0 & Employee Absense T1), T0 & T1 use Employee Code to connect.

In T0, I have EmployeeCode, Location Code, Shift Code & Hour Rate....,

T1 has EmployeeCode, Date, Quantity ...,

How to write a query to get summary of

T1.[Quantity] * T0.[Hour Rate] as AbsHR group by T0.[Location], T0.[Shift] ?

so the result has [Location], [Shift], [Date], [AbsHR] 4 column.

View 2 Replies View Related

Updates Not Occurring When Using Execute

Apr 12, 2005

Create Procedure UpdateGameIsOverlappingFlagByIds (@gameDateIds as varchar(200) = '') as
begin
    if (@gameDateIds = '')
        raiserror('UpdateGameIsOverlappingFlagByIds: Missing parameters', 16,1)
    else
        begin
            Execute
('update games Set IsOverlapping = 1 where gameDateId IN (' +
@gameDateIds + ')')

        end
end   
return
--------------------------------------------
That is the sproc that doesn't seem to update the date when called from
asp.net but does work when done through the query analyzer.  Am I
missing something?

Thanks for any help you can give me.

View 4 Replies View Related

Replication - Several Transactions Occurring

Feb 8, 2012

I have configured a transactional replication between 2 MSSQL 2005 instances. It is set to replicate every 15 minutes. Most replications take less than a minute with about 10 - 50 transactions being replicated.

However, 3 times during the day, the replication takes about 25 minutes to complete with 500-700 transactions being replicated. This also causes the application that is accessing the DB being replicated to become unresponsive when it attempts to retrieve rows from tables in the DB.

Might there be a setting that causes the transactional replication to accumulate transactions during the day?

View 1 Replies View Related

Exception Repeately Occurring

Jul 23, 2005

HiI have a strange problem with my SQL server 2k instllation - every 10mintutes when I have the Profiler trace with the entire "Errors" Eventcategory selected, the following 5 exceptions show up in the trace andthat too for the same SPID.Error: 16955, Severity: 16, State: 2Error: 16945, Severity: 16, State: 1Error: 16955, Severity: 16, State: 2Error: 16945, Severity: 16, State: 1Error: 16955, Severity: 16, State: 2Error: 16945, Severity: 16, State: 1I have no clue why this is occurring - I tried running a trace with theSP:StmtCompleted event on, but no other stored procedures show up withthe same spid close to the time where this exception is logged.Does anyone have a clue as to why this error is occurring ?Rahul

View 1 Replies View Related

FTP Task Error Occurring

Apr 16, 2007

This is strange... just started getting this today, while it's worked for weeks. But alas, that is programming.

The error I'm getting is

[Connection manager "FTP DQ Connection"] Error: An error occurred in the requested FTP operation. Detailed error description: 200 Type set to I. 200 Command okay. 550 /usfsr/DFE_INPUT/FATest_DQ.txt: No such file or folder. .

I'm uploading a file (FATest_DQ.txt) to the folder /usfsr/DFE_INPUT/. I eventually just uploaded the file manually using IE6 to make sure the connection properties were still the same.

Any ideas?

View 1 Replies View Related

Group By Month And Year Transact SQL

Feb 13, 2001

I want to set up stored procedures that let me group data into months. I use the data to produce charts so I need to be able to group into month and year like '01, 2000', '02, 2000'. What is the best way to produce data grouped into month and year from a date field? Using the Month and Year functions I get data like 1,2000 and 11,2000 which don't stand up to a text sort.

View 1 Replies View Related

Transact-SQL Help - CASE Statement And Group By

Jul 20, 2005

I've always been mistified why you can't use a column alias in the group byclause (i.e. you have to re-iterate the entire expression in the group byclause after having already done it once in the select statement). I'mmostly a SQL hobbiest, so it's possible that I am not doing this in the mostefficient manner. Anyone care to comment on this with relation to thefollowing example (is there a way to acheive this without re-stating theentire CASE statement again in the Group By clause?):Select 'Age' =CaseWHEN(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'ELSE cast(SubmittedOn as varchar(22))end,max(SubmittedOn), COUNT(SCRID) AS NbrSCRsFrom SCRViewWHERE(StatusSort < 90) ANDCustomerID = 8 andUserID = 133group byCaseWHEN(SubmittedOn >= DATEADD(dd, - 30, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 0, GETDATE()))THEN '0-30 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 60, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 31, GETDATE())) Then '31-60 Days Old'WHEN(SubmittedOn >= DATEADD(dd, - 90, GETDATE())) AND (SubmittedOn <=DATEADD(dd, - 61, GETDATE())) Then '61-90 Days Old'WHEN(SubmittedOn <= DATEADD(dd, - 91, GETDATE())) THEN '91+ Days Old'ELSE cast(SubmittedOn as varchar(22))endOrder by max(submittedon) descThanks,Chad

View 4 Replies View Related

Transact SQL :: Way To Keep Duplicates Without Removing Group By

May 6, 2015

I'm trying to pull records from a source/staging table where there is a duplicate row in it.I don't need that as the requirement is to garbage in /garbage out.when I do that from mart and use joins btw fact and dimensions, Im not getting this duplicate record as Im using distinct/group by. If I removed it, then it returns more than 3000 rows which is not correct. Is there a way I can keep these duplicates without removing group by...Im using correct joins and filters.

View 5 Replies View Related

Transact SQL :: Group By With Non-aggregate Columns

Aug 5, 2015

How can I aggregate this result into 1 row? (I got it from a UNION ALL)

Article         Assort1    Assort2
50095811    K1             NULL
50095811    NULL          K3

I would like to have

Article         Assort1    Assort2
50095811    K1             K3

View 3 Replies View Related

Transact SQL :: Show Particular Records Within Same Group?

Aug 13, 2015

I have a table with 5 columns, let say ID,PersionID, Date, Type,Qty  and source data looks like this

ID   PersonID    Date            Type       Qty  

1      1        01/01/2011       Accept      5          
2      1        01/01/2011       Accept      5  
3      2        02/01/2010       Accept      10             
4      2        02/01/2010       Deny        20  
5      3        02/01/2012       Accept      15

[Code] .....

Output should look like this..look for only Type=Accept until deny is reached. After Deny,if there is a Accept ignore it.

ID  PersonID    Date            Type         Qty
1    1        01/01/2011       Accept        5      (show only one Accept row=1 becoz Type is Accept and date is same,Qtyis
same)
3    2        02/01/2010       Accept        10     (show Accept row=3,ignore deny row)

5    3        02/01/2012       Accept        15     (show Accept row=5)

6    4        05/05/2012       Accept        25     (show Accept rows=6,7 and ignore Deny & Accept rows = 8,9)

7    4        07/08/2012       Accept        20
        
11   6        01/01/2011       Accept        5      (show Accept rows=11,12 because Qty is different)  

12   6        01/01/2011       Accept        15

Create Sample Table (ID int null, PersonID Int null, Date Datetime null , Type varchar(10) null, Qty int null)

Insert into sample values (1 ,1,'01/01/2011','Accept',5),

(2,1,'01/01/2011','Accept',5),  
(3,2,'02/01/2010','Accept',10),             
(4,2,'02/01/2010','Deny',20),  
(5,3,'02/01/2012','Accept',15),  
(6,4,'05/05/2012','Accept',25),  
(7,4,'07/08/2012','Accept',20), 
(8,4,'07/08/2012','Deny',5),
(9,4,'09/23/2012','Accept',23),
(10,5,'09/08/2012','Deny',12),
(11,6,'01/01/2011','Accept',5),          
(12,6,'01/01/2011','Accept',15)

View 4 Replies View Related

Transact SQL :: Group By And Pivot Several Columns

Oct 2, 2015

I have a table which I would like to group on several columns, and for the Contract number, I'd like a maximum of four different columns which would contain pivoted information.

Here is my DDL:

CREATE TABLE [dbo].[SV00403](
[CUSTNMBR] [char](15) NOT NULL,
[ADRSCODE] [char](15) NOT NULL,
[Contract_Number] [char](11) NOT NULL,
[WSCONTSQ] [int] NOT NULL,

[Code] ....

Here is my select statement:

SELECT[CUSTNMBR]
,[ADRSCODE]
,[Contract_Number]
,[WSCONTSQ]
,[Equipment_ID]

[Code] ...

Here are the results of the select statement:

And here is what the result set is that I would like to achieve:

The yellow indicates the group by columns. How do I pivot the contract number into the four columns noted above ?

View 8 Replies View Related

Transact SQL :: Display 0 With COUNT And GROUP BY

Aug 25, 2015

How can I display 0 when using COUNT and GROUP BY?I'm using SELECT CASE in my query. I was trying to use COALESCE but no result, COUNT result = 1. (there should be 0).

COALESCE((COUNT(DISTINCT (CAST((CASE
WHEN CurrStat = @Stat AND LogDate = @LogDate THEN Enumber ELSE 0 END) AS int)))), 0) AS InTrack,

View 5 Replies View Related

Transact SQL :: Difference Between Data With Group By

Jul 28, 2015

I have a SQL table like this

Events time endTime
Tram 2014-11-28 12:35:50.390 2014-11-28 12:43:19.120
Re-Entry 2014-11-28 12:43:19.120 2014-11-28 12:56:07.040
Tram 2014-11-28 12:56:07.040 2014-11-28 13:15:25.060 // EndDate Before dump
Dump 2014-11-28 13:15:25.060 2014-11-28 13:50:07.233
Tram 2014-11-28 13:50:07.233 2014-11-28 13:55:17.473
Load 2014-11-28 13:55:17.473 2014-11-28 14:06:55.063
Tram 2014-11-28 14:06:55.063 2014-11-28 14:37:12.100
Dump 2014-11-28 14:37:12.100 2014-11-28 14:37:12.100

I want to calculate the Difference between 2 dates like endtime before Dump -time..I am expecting output like this

ROW1   ROW2
 1    00:39:34
 2    23:12:55

You can find the details in SQL Fiddle here.

View 4 Replies View Related

Transact SQL :: Sum And / Or Group Not Working Properly

Oct 21, 2015

I have a query taken from a Crystal Report, and I've been working to modify it for a slightly different purpose.  The initial report was designed to take an input of an end date, go back to the last day of the previous month, get Actuals (sales totals), then add up all sales, costs, and purchases up to the end date, then display assorted data.

The new query needs to take a begin date and and end date, go back to the last day of the previous month for actuals, add all sales, costs, and purchases to those actuals until it gets to the begin date.  That is now the new Actual, and we need to sum up all transactions from then to the end date.

My problem lies in the fact that I can't get the query to add up the numbers.

The query is here:

SELECT DailyReport.Store, DailyReport.Report_Date, sum(dailyreport_Detail.sales) as Sales, sum(dailyreport_detail.actual) as Actual, sum(dailyreport_detail.cost) as Cost, CompanyGroups.Category, DailyReport_Detail.Product, CompanyGroups.Retail_Inv
FROM (`DailyReport` `DailyReport`
INNER JOIN `DailyReport_Detail` `DailyReport_Detail` ON

[Code] ...

The return data looks like this:

3212,2015-07-31,126.35,1781.20,0.00,Fountain,Hot Dsp Bev,0
3212,2015-07-31,149.17,1311.94,0.00,Fountain,Cold Dsp Bev,0
3212,2015-07-31,666.63,4930.02,0.00,Food Service,My Deli,0
3212,2015-08-01,88.67,0.00,0.00,Fountain,Hot Dsp Bev,0
3212,2015-08-01,109.62,0.00,0.00,Fountain,Cold Dsp Bev,0

[Code] ....

I want it to look like this:

3212,2015-08-04,595.53,1311.94,400.76,Fountain,Cold Dsp Bev,0
3212,2015-08-04,485.85,1781.20,165.66,Fountain,Hot Dsp Bev,0
3212,2015-08-04,2762.05,4930.02,1388.00,Food Service,My Deli,0

View 8 Replies View Related

Transact SQL :: SUM Of Multiple Rows With Where And Group By

Apr 30, 2015

I have a table Transaction that looks something like the following :

TransactionID          

Currency        Credit             Debit
1     USD      500               0
2     Afcu          6000              0

[Code] ....

I write query like this

select SUM(credit)-SUM(Debit)as [Balance] ,Source from Transaction group by Source

And it came like

Balance Source           
1500                        USD
6000                        Afcu
6800                        INR
7000                        Pfc
-200                         AUD

But I also want to add  Afcu , Pfc with USD and  want output like

Balance Source
14500                        USD
6800                          INR
-200                           AUD

View 3 Replies View Related

Number Events Occurring Within A Period

Mar 17, 2008

I have TableA with the following structure and Data.


CaseID ActionID StartDate
------ ------------ --------------
A232/21/2007
A212/8/2007
B2212/4/2007
B1310/12/2007
B1710/7/2007
C617/6/2007
D3311/2/2007
D5610/22/2007
D267/29/2007
E226/21/2007
E585/25/2007
E874/8/2007
E293/23/2007

Expected Results:
I want to create TableB with the following structure and data .

CaseIDX YZ
----- - - -
A001
B011
C000
D001
E111


X = 1 when a case has 4 or more actions within 180 days , 0 otherwise
Y= 1 when a case has 3 or more actions within 90 days , 0 otherwise
Z= 1 when a case has 2 or more actions within 30 days , 0 otherwise

Any help will be welcomed

View 12 Replies View Related

Transact SQL :: Adding Percentage To A Group Within A Query

May 6, 2015

Below is my SQl which just counts the number of appointments and grouped by clinic. This is great but what I'd like to add is the percentage within each clinic.

For example Clinic BRESRAD1 has a total of 61 appointments, of which 75.41% are Normal Appointments and 24.59% are Diagnostic, Ideally I would like the percentage in the next column.

BRESRAD1 Normal Appointment 46
BRESRAD1 Diagnostic Appointment 15
BRESRAD2 Normal Appointment 17
BRESRAD2 Diagnostic Appointment 12
BRESRAD3 Normal Appointment 34
BRESRAD3 Diagnostic Appointment 43

My SQL is as follows:

SELECT ClinicCode,
CASE WHEN [ApptTypeDesc] LIKE '%Diag%' THEN 'Diagnostic Appointment' ELSE 'Normal Appointment' END AS [Diagnostic Appt],
COUNT(OPAppointmentID) AS CountOfOPAppointmentID
FROM dbo.OP_APPOINTMENT
WHERE (AttendStatusNatCode IN ('5', '6'))
AND (ApptFinYr = '2014/15')
GROUP BY ClinicCode,
CASE WHEN [ApptTypeDesc] LIKE '%Diag%' THEN 'Diagnostic Appointment' ELSE 'Normal Appointment' END
ORDER BY ClinicCode

View 13 Replies View Related

Transact SQL :: Add Group Number To Similar Rows?

Jun 9, 2015

How can I add a group number to the following query?

For example, I want to be able to have all rows that have Category = 'Field Sales' and Division = 'CA BDM' to be given a unique group number (GN):

RN                   ReportDate Category                       Division                       TotalBalance
-------------------- ---------- ------------------------------ ------------------------------ ---------------------
1                    2015-06-08 Field Sales                    CA BDM                         299743154.3912
2                    2015-06-07 Field Sales                    CA BDM                         299765954.0354
3                    2015-06-01 Field Sales                    CA BDM                         297902654.4172
1                    2015-06-08 Key Accounts                   Life Office                    49954981.74
2                    2015-06-07 Key Accounts                   Life Office                    50016989.22
3                    2015-06-01 Key Accounts                   Life Office                    50169967.26
4                    2015-05-31 Key Accounts                   Life Office                    50169918.01

Becomes

GN   RN                   ReportDate Category                       Division                TotalBalance
-------------------------- ---------- ------------------------------ ------------------------------ ---------------------
1      1                    2015-06-08 Field Sales                    CA BDM                     299743154.3912
1      2                    2015-06-07 Field Sales                    CA BDM                     299765954.0354
1      3                    2015-06-01 Field Sales                    CA BDM                     297902654.4172
2      1                    2015-06-08 Key Accounts                   Life Office                    49954981.74
2      2                    2015-06-07 Key Accounts                   Life Office                    50016989.22
2      3                    2015-06-01 Key Accounts                   Life Office                    50169967.26
2      4                    2015-05-31 Key Accounts                   Life Office                    50169918.01

i.e. each combination of Category+Division results in a new GN.

The query is:

selectROW_NUMBER() over (partition by Category, Division order by ReportDate desc) 'RN'
, ReportDate
, Category
, Division
, sum(BalanceGBP) as 'TotalBalance'
FROM FlowsAndOpenings
group by ReportDate, Category, Division
order by Category, Division, RN

View 2 Replies View Related

Transact SQL :: Syntax To Group Items In One Line?

Nov 20, 2015

I have records that I get in this format:

ID                       Customer               Type                 TypeNUm
100                      Tiger                    Item                   T100  
100                      Tiger                     Item                   T200
100                      Tiger                     Item                   T300
100                      Tiger                     Shiper                  SAAA 
100                       Tiger                    PO                       POAAA
200                       Panera                  GL                   WE
200                       Panera                  PO                   POBBB

The reftypes are not always the same, what I need is to get it in this form

ID                       Customer               Type              TypeNUm
100                      Tiger                     Item                   T100,T200, T300 
100                      Tiger                     Shiper                  SAAA 
100                       Tiger                    PO                       POAAA
200                       Panera                  GL                      WE
200                       Panera                  PO                     POBBB

View 6 Replies View Related

Transact SQL :: Find A Count On Group Of Member IDs

Aug 20, 2015

I am trying to find a count on group of our memberid`s who were active within a year since 2010 till today for particular memberships in my table I have memberid int effectivedate datetime termdate datetime Membershiptype varchar(10) ='GOLD','Silver' and 'Platinum'. I haven't used sql in a long time..

View 6 Replies View Related

Transact SQL :: How To Select Columns That Are Not In GROUP BY And Get COUNT

Jul 3, 2015

I am using SQL 2012.  I have a GROUP BY and I want to select two other fields from my table at the same time: One column that is a string (account_code) and one that I need to perform a count on (customer_number).  I know the code COUNT(DISTINCT customer_number) works for getting that.   I need to select both of those fields on top of what I have.  I have the following:

DECLARE @Providers TABLE (ID INT IDENTITY(1,1),
Provider_Name VARCHAR(20),
Uniq_Id VARCHAR(10),
Total_Spent MONEY,
Total_Earned MONEY)
INSERT INTO @Providers (Provider_Name, Uniq_Id,Total_Spent,
Total_Earned)

[Code] .....

View 21 Replies View Related

Transact SQL :: Group Results Into Single String

Jun 4, 2015

I have a query that pulls back task and user assigned. Each task can have multiple users assigned. I want to pull back the single task and all the users assigned in one row. 

Current Query:

select
t.Name 'Task',
d.FirstName + d.LastName 'User'
from [dbo].[Tasks_TemplateAssignTo] a join
Task_Template t on a.template_id = t.ID join
Doctor d on d.id = a.provider_id

Results from query above:

TaskUser
Call CustomerJohn Smith
Call CustomerBetty White
Call CustomerTammy Johnson
Order suppliesGreg Bullard
Order suppliesJosephine Gonzalez

Expected Results:

TaskUser
Call CustomerJohn Smith, Betty White, Tammy Johnson
Order SuppliesGreg Bullard, Jospehine Gonzalez

View 4 Replies View Related

Transact SQL :: Getting Last Register From A Query That Use A Group By Clause

Aug 13, 2015

I've got this set of registers (just an example) after ordering by the first 3 columns:

value_A value_B value_C ID date
1 2 3 YVIR 29/08/2015
1 2 3 ANTE 27/04/2015
1 2 3 REGO 20/02/2015

I need to get as a final result:

value_A value_B value_C ID date
1 2 3 REGO 29/08/2015

In other words, I need to get, after ordering the result by the date field, the most recent date but at the same time the oldest ID in the list.

I've been trying to do this with the group by clause:

select
value_A, value_B, value_C, min(ID), max(date) -- or max(ID)
from table
group by value_A, value_B, value_C

But in the field ID I'm getting the wrong result because this value is been associated with the alphabetic order.

In access this query involves the function LAST, but in SQL I have not found a good way to perform this. And I am asking because I have seen some possible solution but almost all of them involving the UNION operation, but my problem is, this table can have more than 350.000 registers.

This table is update by some one else, I just can access the information and use it as a source.

View 3 Replies View Related

Transact SQL :: Notification When A Group Of Jobs Complete

Apr 23, 2015

I have a large number of jobs that I run using a cursor (All jobs start with PS_ and each job loads a single table from ORACLE) so they can run in parallel.  I want to be able to be notified when the last job completes.  Should this done using a trigger?  Or should I just add a loop statement to the stored procedure that fires this job?  I know which individual takes the longest and I could simply just add a notification to send email on success but I want it to be more robust.  Below is what I am using to trigger the jobs.

BEGIN
SET NOCOUNT ON;
DECLARE @year CHAR(4)
DECLARE @month CHAR(2)
DECLARE @day CHAR(2)
SET @year = CAST(DATEPART(YEAR, GETDATE()) AS VARCHAR)

[Code] .....

View 6 Replies View Related

Transact SQL :: Getting Random Rows From Table / Two From Each Group

Sep 2, 2015

I am using Sql Server 2008 R2.I have a existing query that basically says

Select Top 50 Subscriber_ID,  Member_Name, Group_ID
from my_table
order by rand(checksum(newid()))

However the client now wants to have at least two from each group_id. There are 17 different groups.  When I run this as is I get about six of the 17 groups in the results.  How can I change this to get at least two results from each group_id?

View 6 Replies View Related

Transact SQL :: How To GROUP BY And Get Aggregate Where A Certain Condition Is True

Jul 16, 2015

I am using SQL 2005.  I have some data from an old application that did not follow the rules for normalization.  The table is for Invoices, and the table allows for 13 purchase items per record.  So in each row of my table I have a non-unique integer field itemID, itemID1, itemID2 ... itemID12.  For each itemID I also have "lbs_total" and "line_total" (which is price * lbs_total) - so itemID, lbs_total, line_total ... itemID1, lbs_total1, line_total1 ... etc.  It's a mess, I know.Each row has a unique Customer Number ("cno") and an Invoice Date ("inv_date").  My proc needs to allow for params for the item number, and a start date and end date for BETWEEN on the inv_date.I also need to get the aggregate for the lbs_total and the line_total.

View 15 Replies View Related

Transact SQL :: GROUP By Field Syntax Error

Oct 8, 2015

I have the following querry:

SELECT APHIST.ReturnDate AS ATDATE
,API_HIST.[ActionPlanItemID]
,API_HIST.[ActionPlanID]
,PIT.[ProductItemID]
,PIT.ProductItemCode
,PIT.Name,

[Code] ....

That query is suppose to add to calculation field OutStock and InStock based on the value of n

When executing this  query I get the following message :

Column 'Sales.ActionPlan_History.ReturnDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved