Here is my table data:
CREATE TABLE
#TestTable (
Pk INT,
GroupID INT,
Enabled BIT
[code]..
I need to write a select query that will retrieve any GroupID in which every record has an Enabled value of 1.In the example I've provided, only GroupID 1 and 3 will be returned since GroupID 2 has a record with an Enabled value of 0.What would be the most efficient way to write such a query?
I have a table T (a1, ..., an, time, id). I need to select those rows that have different id (GROUP BY id), and from each "id group" the row that has the latest field 'time'. Something like SELECT a1, ..., an, time, id ORDER BY time DESC GROUP BY id. This is the wrong syntax and I don't know how to handle this.
View 3 Replies View RelatedHello
Im searching for a solution to set all matrix row or cell the same height.
it schoud looks like this example:
This is a simple matrix
test a
text b
text c
text d
text e
text f
text g
This is a matrix with all the same row-height.
test a
text b
.
text c
.
.
text d
text e
text f
text g
.
.
Thx you a lot
Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt
View 2 Replies View RelatedI have a group of records like this;
NAME| RUN START| RUN END| LK1| LK2| DESC
Graeme Brown|2012-02-23 07:20:00|2012-02-23 07:50:00|2213|2244|AK1/4/PI2
Graeme Brown|2012-02-23 08:00:00|2012-02-23 09:25:00|2244|2052|AK1/4/PI2
Graeme Brown|2012-02-23 09:30:00|2012-02-23 11:05:00|2052|917|AK1/4/PI2
Graeme Brown|2012-02-23 12:15:00|2012-02-23 13:55:00|917|2052|AK1/4/PI2
Graeme Brown|2012-02-23 14:05:00|2012-02-23 15:40:00|2052|1111|AK1/4/PI2
They are grouped on the last column [DESC].
I want to get;
NAME| RUN START| RUN END| LK1| LK2| DESC
Graeme Brown| 2012-02-23 07:20:00| 2012-02-23 15:40:00| 2213| 1111| AK1/4/PI2
So what it needs to do is combine the ;
earliest RUN START and corresponding LK1 with
latest RUN END and corresponding LK2.
I've tried creating temp tables with the mins and maxs - but then I can't combine them with the LK1 and Lk2 fields...
sample query results:
cid cno cvalue
--- --- -------
835201add edit
835201add edit
836202with VAT
836202with VAT
how can i filter this more into this:
835201add edit
836202with VAT
Hi,
can anyone help me to group by Top 2 records by ID and Date?
My table contains following data
create table #test (id int, code varchar(10),TestDate datetime)
insert into #test (id,code,TestDate) values (12,'ABC','01/11/2007')
insert into #test (id,code,TestDate) values (12,'ABC','01/11/2007')
insert into #test (id,code,TestDate) values (12,'BC','02/18/2007')
insert into #test (id,code,TestDate) values (12,'BC','02/18/2007')
insert into #test (id,code,TestDate) values (12,'BC12','10/01/2007')
insert into #test (id,code,TestDate) values (12,'BC11','10/11/2007')
insert into #test (id,code,TestDate) values (12,'BC11','01/25/2007')
insert into #test (id,code,TestDate) values (12,'ABC','01/11/2007')
insert into #test (id,code,TestDate) values (14,'YZ123','02/11/2007')
insert into #test (id,code,TestDate) values (14,'YZ123','02/12/2007')
insert into #test (id,code,TestDate) values (14,'YZ123','02/12/2007')
insert into #test (id,code,TestDate) values (14,'YZ123','02/11/2007')
insert into #test (id,code,TestDate) values (14,'YZ123','02/11/2007')
select * from #test order by TestDate
--Result Set
id code TestDate
12 ABC 2007-01-11 00:00:00.000
12 ABC 2007-01-11 00:00:00.000
12 ABC 2007-01-11 00:00:00.000
12 BC11 2007-01-25 00:00:00.000
14 YZ123 2007-02-11 00:00:00.000
14 YZ123 2007-02-11 00:00:00.000
14 YZ123 2007-02-11 00:00:00.000
14 YZ123 2007-02-12 00:00:00.000
14 YZ123 2007-02-12 00:00:00.000
12 BC 2007-02-18 00:00:00.000
12 BC 2007-02-18 00:00:00.000
12 BC12 2007-10-01 00:00:00.000
12 BC11 2007-10-11 00:00:00.000
--I want to get only duplicate records, so SQL should eliminate uniq recod.Total will --get total number of records per ID and date, but want to display only two reacords by --Id and Testdate.
--my result set should contain following data
--Total of first byID and by Date
----id code TestDate Total
----12 ABC 2007-01-11 00:00:00.000 3
----12 ABC 2007-01-11 00:00:00.000
----14 YZ123 2007-02-11 00:00:00.000 3
----14 YZ123 2007-02-11 00:00:00.000
----14 YZ123 2007-02-12 00:00:00.000 2
----14 YZ123 2007-02-12 00:00:00.000
----12 BC 2007-02-18 00:00:00.000 2
----12 BC 2007-02-18 00:00:00.000
If anyone have any idea,, plz help me out..
Thanks....
Dear All
I need to cerate a SP that SELECTS all the records from a table WHERE the first letter of each records starts with 'A' or 'B' or 'C' and so on. The letter is passed via a parameter from a aspx web page, I was wondering that someone can help me in the what TSQL to use I am not looking for a solution just a poin in the right direction. Can you help.
Thanks Ross
1.Create the tables with insert queries
2. provide the result as required in an temp table
3. Display the expected result
======================================================================
CREATE TABLE and Insert Data
======================================================================
use master
CREATE TABLE [dbo].[Travel_Master](
[Load_Id] [int] NULL,
[Mode_Id] [nchar](2) NULL,
[Mode_Info] [nchar](10) NULL,
[Has_Nodes] [nchar](3) NULL
) ON [PRIMARY]
[Code] .....
Hi all, I am relatively new to sql and have an issue I am trying to solve. I have a table with several records with the same id:
id amount date
-- ------ ------
1 100 01/01/2006
1 2000 06/01/2005
2 200 01/01/2005
2 500 06/01/2007
how would I get the data for the record with the newest date?
So I would return:
id amount date
-- ------ ----------
1 100 01/01/2006
2 500 06/01/2007
Thanks in advance for the help.
Hi,
I've a problem with the following stored procedure Select . It does compile and run, but doesn't return the sorted result I wanted, which was to have the records from tblPieces (alias Pcs) sorted by (in order) Pcs.fkBatchId, Pcs.fkProfileCode, Pcs.Colour. What happens instead, I think, is that the correct records are selected, but in the record creation order. CREATE PROCEDURE dbo.LoadOneBatch
(
@BatchId int, -- the pkBatchId of the batch required
@OnlyNew bit -- If true, only consider batches that haven't ever been loaded.
)
AS
SET NOCOUNT OFF
SELECT Bat.pkBatchId,
Pcs.*
FROM tblBatches AS Bat
JOIN (
SELECT Bat1.pkBatchId,
Pcs1.fkProfileCode,
Pcs1.Colour
FROM tblBatches AS Bat1
JOIN tblPieces AS Pcs1 ON Pcs1.fkBatchId = Bat1.pkBatchId
WHERE Bat1.pkBatchId = @BatchId
GROUP BY Bat1.pkBatchId, Pcs1.fkProfileCode, Pcs1.Colour
) SubQ ON SubQ.pkBatchId = Bat.pkBatchId
JOIN tblPieces AS Pcs ON Pcs.fkBatchId = Bat.pkBatchId
WHERE (@OnlyNew = 1 And Bat.IsLoaded = 0 And
Bat.IsCompleted = 0 And Bat.pkBatchId = @BatchId) Or
(@OnlyNew = 0 And Bat.pkBatchId = @BatchId)
GO
EXEC LoadOneBatch @BatchId = 1, @OnlyNew = 0
The DDL for the two tables is:CREATE TABLE [tblBatches](
[Stamp] timestamp NOT NULL,
[pkBatchId] int IDENTITY(1,1) NOT NULL,
[BatchNo] varchar(50) NULL,
[SubmitTime] [datetime] NULL,
[FinishTime] [datetime] NULL,
[IsLoaded] bit NULL,
[IsCompleted] bit NULL,
PRIMARY KEY ( [pkBatchId] ASC )
)
CREATE TABLE [tblPieces](
[Stamp] timestamp NOT NULL,
[pkPieceId] int IDENTITY(1,1) NOT NULL,
[fkBarId] int NULL,
[fkBatchId] int NULL,
[PieceNo] varchar(12) NOT NULL,
[Descrip] varchar(48) NULL,
[Position] real NULL,
[LeadPrep] char(1) NOT NULL,
[TailPrep] char(1) NOT NULL,
[Length] real NOT NULL,
[fkProfileCode] varchar(10) NOT NULL,
[Colour] varchar(5) NOT NULL,
PRIMARY KEY ( [pkPieceId] ASC )
)
The data records output are roughly:pkBatchId Stamp pkPieceIdfkBarIdfkBatchIdPieceNo Descrip PositionLeadPrepTailPrepLengthfkProfileCodeColour
1 0x00000000000036B21 NULL1 00000000000118960 /003/F>1 N2NULL / 913 6000 wht
1 0x00000000000036B32 NULL1 00000000000218960 /003/F<1 N2NULL / 913 6000 wht
My Table
ID,Customer,Type,Date
records
1,XXX,AAA,Date
2,ZZZ,BBB,Date
3,QQQ,BBB,Date
I group them with the following query
Select Source,Count(*) from table where date=month(getdate()) group by Type order by 2 desc
the result looks like that
AAA,1
BBB,2
------------
Also there are another table for this results (Totals)
fields
Type,Quantity
--------------
AAA,45
CCC,76
(attention, there are no BBB record currently in this table)
I want that
the results of the first query goes to Totals table.
what I need like this
Type,Quantity
--------------
AAA,45 + AAA,1
CCC,76
BBB,2
I don't know how to do
if there is a LOOP solution in sql server , I would like to know
thanks in advance
Problem 1: I have the following table which shows the location of a person at 1 hour intervals
IdEntityIDEntityNameLocationIDTimexdelta
11MickeyClub house03001
21MickeyClub house04001
31MickeyPark05002
41MickeyMinnies Boutique06003
51MickeyMinnies Boutique07003
61MickeyClub house08004
71MickeyClub house09004
81MickeyPark10005
91MickeyClub house11006
The delta increments by +1 every time the location changes.
I would like to return an aggregate grouped by delta as per example below.
EntityNameLocationIDStartTimeEndTime
MickeyClub house03000500
MickeyPark05000600
MickeyMinnies Boutique06000800
MickeyClub house08001000
MickeyPark10001100
MickeyClub house11001200
I am using the following query (which works fine):
select
min(timex) as start_date
,end_date
,entityid
,entityname
,locationid
[code]....
However I would like to not use the delta (it takes effort to calculate and populate it); instead I am wondering if there is any way to calculate it as part / whilst running the query.
Problem 2:I have the following table which shows the location of different people at 1 hour intervals
IdEntityIDEntityNameLocationIDTimexDelta
11MickeyClub house09001
21MickeyClub house10001
31MickeyPark11002
42DonaldClub house09001
52DonaldPark10002
62DonaldPark11002
73GoofyPark09001
83GoofyClub house10002
93GoofyPark11003
I would like to return an aggregate grouped by person and location.
For example
EntityIDEntityNameLocationIDStartTimeEndTime
1MickeyClub house09001100
1MickeyPark11001200
2DonaldClub house09001000
2DonaldPark10001200
3GoofyPark09001000
3GoofyClub house10001100
3GoofyPark11001200
What modifications do I need to the above query (Problem 1)?
select
DayRank = ROW_NUMBER() OVER(ORDER BY a.datedel DESC),
a.order,a.line,a.datedel,a.recpt,b.status,
b.item,b.t_sup
from historytbl a
inner join order b
on a.order = b.order
and a.line = b.line
and a.status =4
group by a.order,line,a.datedel,a.recpt,b.status,b.item,b.sup
The query is returned the results below.
Rank OrderLineDateDelrecptitemsup
----- -------------------------------
1aaa102014-18-08rc1zzz1231122
2bbb202014-08-08rc2zzz1231122
3ccc302014-04-08rc3zzz1231122
4ddd902014-08-11rc6yyy123333
5eee102014-05-11rc7yyy123333
5fff90 2014-02-11rc8yyy123333
6ggg102014-05-10rc9qqq123444
7hhh502014-04-10rc0qqq123444
8iii102014-04-10rc5rrr123555
However, I want to have the query only show most recent two records for each group of item and sup, please see the results I want below.
Rank OrderLineDateDelrecptitemsup
----- -------------------------------
1aaa102014-18-08rc1zzz1231122
2bbb202014-08-08rc2zzz1231122
4ddd902014-08-11rc6yyy123333
5eee102014-05-11rc7yyy123333
6ggg102014-05-10rc9qqq123444
7hhh502014-04-10rc0qqq123444
I'm bulk loading employees into an etl table, each employee has a unique ID number, but they have multiple records in the data. Sometimes their name or birthdate will change and I want to identify those records and only insert the newest version into production. I can do this with a series of temp tables, but I'm sure there's a better way. The SQL below updates the etl table with the flag I want to mark the inserts, but it seems convoluted. (Jon's birthday changes, Jane's birthday changes, Bill's gender changes, Amy nothing changes(I handle those inserts later))
DECLARE @Records TABLE(
firstname varchar(50), lastname varchar(50), birthdate date, sex char(1), IDNum varchar(15), moddate date, opflag char(1))
INSERT INTO @Records
VALUES
('JON','SMITH','20000101','M','12345','20140101','I'),
('JON','SMITH','20000101','M','12345','20140201','I'),
[code]....
hi all,i got is table:Id StartDate EndDatea 19/03/2001 18/03/2002a 19/03/2002 18/04/2002*b 13/08/2000 12/08/2001b 13/08/2001 12/08/2002b 13/08/2002 10/07/2002*Sort command and groupins i am ok but i need to select only the records thathas the latest enddate. (See *)any ideas? thanks in advancerashid
View 1 Replies View RelatedI 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)
Hi all,
I have a report which is grouped by a field called R_ID, which gives me a list of records for each R_ID. So here is the thing, I want to get only top 3 records for each R_ID. Is there any way to do this thing in the report level. I tried it from the query level, but the result is not like what I wanted.
Please let me know if some body has any idea.
Thx.
Hi,
I have the need to group records in an ador.recordset.
Is this possible and in that case how?
I am fairly new to transact SQL and I am having difficulty retrieving the set of records I require given the data shown below. I want to be able to filter the records just to return the records that have the minimum securityorder for each unique secsyscode. I suspect I need to use min or group by to achieve the desired affect but cannot seem to get it right
any help would be appreciated
eg in the following
secsyscode, securitytypecode and securityorder are integers and securityCode is a char(16).
secsyscode
securityCode
securitytypecode
securityorder
1
Special
1
2
2
Total Fund
999
17
3
PerfInd
995
14
3
PerformanceIndex
999
17
4556
93152
1
2
4556
10815-0
4
1
4557
558372
1
2
4557
12137-0
4
1
4558
656113
1
2
4558
13154-0
4
1
4559
53673
1
2
4559
13672-0
4
1
I only want the following records to be returned.
secsyscode
securityCode
securitytypecode
securityorder
1
Special
1
2
2
Total Fund
999
17
3
PerfInd
995
14
4556
10815-0
4
1
4557
12137-0
4
1
4558
13154-0
4
1
4559
13672-0
4
1
SELECT Top 10 Name, Contact AS DCC, DateAdded AS DateTimeFROM NameTaORDER BY DateAdded DESC
I'm trying to right a sql statement for a gridview, I want to see the last ten records added to the to the database. As you know each day someone could add one or two records, how can I write it show the last 10 records entered.
I have a pretty complex query that returns three records. For simplicity sake, the results can be simulated with this query:
Select 5 AS InternalAuditTeamEmployeeID, 1 as InternalAuditTeamID
UNION ALL
Select 11, 2
UNION ALL
Select 14, 3;
I want to take this result and update the Flag field to true in my table tblInternalAuditTeamEmployee (CREATE statement below) for any InternalAuditTeamEmployeeID that is less than or equal to the ones in the results above, but by group. My results would look something like this using the data below and the results above.
InternalAuditTeamEmployeeIDInternalAuditTeamIDEmployeeIDFlag
1 1 619 1
218581
316041
425181
517161
639661
711910
819400
92391
1012340
1129541
1228910
1329500
143321
1539450
I was thinking I could somehow use ROW_NUMBER(PARTITION BY InternalAuditTeamID ORDER BY InternalAuditTeamEmployeeID DESC), but not sure how to get the results of "WHERE <= InternalAuditTeamEmployeeID For each particular group".
CREATE TABLE STATEMENT:
CREATE TABLE [tblInternalAuditTeamEmployee](
[InternalAuditTeamEmployeeID] [int] IDENTITY(1,1) NOT NULL,
[InternalAuditTeamID] [int] NOT NULL,
[EmployeeID] [int] NOT NULL,
[Code] ......
Looking to see if thier is a better way to find the last record entered in a group of records.
What I'm doing now is finding the max for the secound column and then doing a sub query to find the max of the third column having the second columns equal.
Table example using simplied data.
PolId
CoveragId
EffDate
Status
Limit1
2
1
9/7/2007
a
10000
2
2
9/7/2007
a
150000
2
2
10/1/2007
a
200000
3
1
9/7/2007
a
10000
The parent program addes a row every time the data is changed. To make things worst; the records arn't always in sqenal order like the above table and some time edits the row instead.
The current query returns a single value. from a single table.
Current code used in the select protion on a larger query. bpi = basicpolicyInformation.
( Select c1.limit1
From AFW_Coverage as c1
Where c1.PolId=bpi.PolId
and c1.CoverageId = (select max(CoverageId) as CoverageId
From AFW_Coverage as c
where c.PolId = c1.PolId
and c.CoverageCode = 'Dwelling'
and status <> 'D'
)
and c1.effDate = (select max(Effdate) as Effdate
From AFW_Coverage as c
where c.PolId = c1.PolId
and c.CoverageID = c1.CoverageId
)
Explain the current code. It uses the two sub queries to find the correct record ID that has the data needed.
I have groups of records in a table, and I would like to set a necessary condition on each group. The condition is that EXACTLY ONE of the records in each group has a flag field set to True (bit = 1). I can naturally write triggers for update, insert and delete events that test for such a condition.
Something along the lines of this condition:
(select count(ClovekAutoID)
from TableOfClovekNames tCN
where JeHlavni = 1
group by ClovekAutoID
having COUNT(JeHlavni ) > 1) = 1In fact,
I tried this just on whim, but naturally, the SS engine told me to go roll my hoop, that subqueries are not allowed in constraint expressions.
I have a query that brings back the data below. I need to divide the BudgetTotal by the Count. Then I need to go to the records that make up those €œgroups€? and enter a Budget value = BudgetTotal/Count.
How could I write this in a stored procedure or a SQL statement if possible?
Thanks.
Kevin
SELECT TOP 100 PERCENT dbo.ReportTable.ProjectNo, dbo.ReportTable.Category, dbo.ReportTable.Type, COUNT(dbo.ReportTable.ProjectNo) AS count,
dbo.ReportTable.Budget, dbo.OracleDownloadBudget.Budget AS Expr1
FROM dbo.ReportTable INNER JOIN
dbo.OracleDownloadBudget ON dbo.ReportTable.Category = dbo.OracleDownloadBudget.Category AND
dbo.ReportTable.ProjectNo = dbo.OracleDownloadBudget.Project AND dbo.ReportTable.Type = dbo.OracleDownloadBudget.Type
GROUP BY dbo.ReportTable.ProjectNo, dbo.ReportTable.ProjectName, dbo.ReportTable.Category, dbo.ReportTable.Type, dbo.ReportTable.Budget, dbo.OracleDownloadBudget.Budget
HAVING (dbo.ReportTable.Budget < 1)
ORDER BY dbo.ReportTable.ProjectNo
ProjectNo
Category
Type
Count
Budget
BudgetTotal
100143
Travel
Travel, Meals, No Report IRS
2
0
300.27
100146
Travel
Travel Costs, Training (all)
1
0
300.27
100164
Supplies & Materials
Supplies, Educational
1
0
300.27
100167
Equipment
Eq NonCapital Desktop Comp
1
0
300.27
100170
Faculty Salaries
FB, Faculty
11
0
300.27
100170
Faculty Salaries
Salary, Faculty, T&R FT
11
0
300.27
100170
Wages
Wages, Student
2
0
300.27
100171
Faculty Salaries
FB, Faculty
19
0
300.27
100171
Faculty Salaries
Salary, Faculty, T&R FT
19
0
300.27
100176
Scholarships & Fellowships
Fell, Assist, Out, Grad
1
0
300.27
100177
Scholarships & Fellowships
Fell, Assist, In, Grad
1
0
300.27
I need to write a query to group records based on speed (specific value of zero). Consider the following scenario:
Table - Vehicle_Event
Vehicle_Id____Date_Time______________Speed
C1____________2012-06-28_10:10:00____5
C1____________2012-06-28_10:11:00____0
C1____________2012-06-28_10:12:00____0
C1____________2012-06-28_10:13:00____4
C1____________2012-06-28_10:14:00____3
[code].....
OUTPUT_Required:
Vehicle_Id____Date_Time___________________________ __________Speed
C1____________2012-06-28_10:10:00___________________________5
C1____________2012-06-28_10:11:00_to_2012-06-28_10:12:00____0
C1____________2012-06-28_10:13:00___________________________4
C1____________2012-06-28_10:14:00___________________________3
C1____________2012-06-28_10:15:00_to_2012-06-28_10:18:00____0
[Code] .....
I need the start and end time of consecutive records of the same vehicle with 0 speed ordered by date_time. If there is more than one consecutive record with zero speed it needs to be grouped together.
I need to group up the records randomly into ‘n’ number of batches. That can be done by NTILE, but I want group up similar records in single group.
Say for example, following is the list of records I have in my table which I want to group into 5 batches
A123
A124
A124
A123
A127
After Ntile I will get the below,
Desired output is, Need output like Ntile but all same id should reside in single batch
Even if I n=5, maximum possibility of batches are 3 only.
I've inherited a table of members that has the following structure:
CREATE TABLE [dbo].[dimMember](
[dimMemberId] [int] IDENTITY(1,1) NOT NULL,
[dimSourceSystemId] [int] NOT NULL CONSTRAINT [DF_dimMember_dimSourceSystemId] DEFAULT ((-1)),
[MemberCode] [nvarchar](50) NOT NULL,
[FirstName] [nvarchar](250) NOT NULL,
[LastName] [nvarchar](250) NOT NULL,
[Code] ....
Based on the way the data loads into the table there's a possibility of some records being near duplicates of each other. For example, we can have a member that has records that have the same first name, last name, SSN, but different addresses, membercodes, subscribercode etc... This can happen in pretty much any variation thereof.
What I want to do, is add a new column and use that to group the similar records under based on comparing on several columns. By this I mean, if a member matches 4 of the 7 values below with another member, we would group these:
First Name (1st 3 characters)
Last Name
DOB
CurrentAddress1
MemberCode
SSN
SubscriberCode
I'm at a loss of how to structure the SQL to update the new column in the table.
Hi!
I'am new to this forum and would apreciate any feedback on my problem.
I have a quarry that returns the count of former customers with average cell-phone usage between 200 and 299.
The ressult is grouped in year and week with group by. The dates are represented by the closingdate of the customers subscription.
The ressult is used for reporting purposses, but I need my selection to return '0' on weeks where there are "no reccords found".
CODE:
SELECT '200-299' AS ARPU, year AS YEAR, week AS WEEK, COUNT(nummer) AS Antall
FROM
(SELECT SERGEL_PREPAID.SP_Mobilenumber AS nummer, DATEPART(yyyy, TRANSLOG.TRL_TIMESTAMP) AS year, DATEPART(ww, TRANSLOG.TRL_TIMESTAMP) AS week,
ROUND(AVG(SERGEL_PREPAID.SP_Sum), 0) AS average
FROM SERGEL_PREPAID INNER JOIN
TRANSLOG ON SERGEL_PREPAID.SP_Mobilenumber = TRANSLOG.TRL_MOBILE
WHERE (TRANSLOG.TRL_STATUS = 'NP_FERD')
GROUP BY SERGEL_PREPAID.SP_Mobilenumber, DATEPART(yyyy, TRANSLOG.TRL_TIMESTAMP), DATEPART(ww, TRANSLOG.TRL_TIMESTAMP)
HAVING (AVG(SERGEL_PREPAID.SP_Sum) BETWEEN 200 AND 299)) AS derivedtbl_1
GROUP BY uke, all aar
NB: Using SQL Server 2005. Any tip or solution will be a big help
Best regards Gard S
How to identify different fields with in a group of records?
Example:
create table #test
(ID int, Text varchar(10))
insert into #test
select 1, 'ab'
union all
select 1, 'ab'
[Code] ...
I want to show additional field as Matched as ID 1 has same Text field on both the records, and for the ID 2 I want to show Unmatched as the Text fields are different but with the same ID.
I am trying to use SQL to pull unique records from a large table. The table consists of people with in and out dates. Some people have duplicate entries with the same IN and OUT dates, others have duplicate IN dates but sometimes are missing an OUT date, and some don’t have an IN date but have an OUT date.
What I need to do is pull a report of all Unique Names with Unique IN and OUT dates (and not pull duplicate IN and OUT dates based on the Name).
I have tried 2 statements:
#1:
SELECT DISTINCT tblTable1.Name, tblTable1.INDate
FROM tblTable1
WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#))
GROUP BY tblTable1.Name, tblTable1.INDate
ORDER BY tblTable1.Name;
#2:
SELECT DISTINCT tblTable1.Name, tblTable1.INDate
FROM tblTable1
WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#))
UNION SELECT DISTINCT tblTable1.Name, tblTable1.INDate
FROM tblTable1
WHERE (((tblTable1.Priority)="high") AND ((tblTable1.ReportDate)>#12/27/2013#));
Both of these work great… until I the OUT date. Once it starts to pull the outdate, it also pulls all those who have a duplicate IN date but the OUT date is missing.
Example:
NameINOUT
John Smith1/1/20141/2/2014
John Smith1/1/2014(blank)
I am very new to SQL and I am pretty sure I am missing something very simple… Is there a statement that can filter to ensure no duplicates appear on the query?
I have an unusual problem. I am using VB.Net 2003 and sqlexpress using .NET dataset to insert records into an timecards table. After inserting several records I tried a 'Select * from timecards' and the inserted records where not selected. if I 'select * from timecards order by employee' ( or any other field) the inserted records are selected! The table was created by an Access Upsize command.
Any suggestions?
Thanks!
GordonG
hello,
i have two tables: Pictures and UserComments, both have PictureID column
in second table i store each comment made by users at a specific picture like so: CommentID, UserName, PictureID, Comment, Date
i am trying to make a stored procedure with @UserName input parameter witch returns Distinct Pictures whereon that user has commented (sorry for that whereon expression, is from dictionary and i don't know if it express what i want to mean) SELECT Pictures.OwnerName AS 'Owner', Pictures.Name AS 'Name of picture', COUNT(UserComments.PictureID) AS 'Comments made', Pictures.Image1
FROM Pictures INNER JOIN
UserComments ON Pictures.PictureID = UserComments.PictureID
WHERE (UserComments.UserName = @UserName)
GROUP BY UserComments.UserName, Pictures.Name, Pictures.OwnerName, Pictures.Image1
if i use this statement it shows me the picture details whereon that user has commented and if he commented more than once on the same picture the result isn't duplicated
but with that statement i can't order by UserComments.Date because i have to use GROUP BY UserComments.Date and the results will not be shown in pairs
How can i order the results by Date Desc?
sorry for my bad english, if you don't understand please tell me and i'll try to explain by examples
please help me, thanks