T-SQL (SS2K8) :: Return A Count Per Unique Check In Invoice Table?
May 12, 2014
We have a table that has customers invoices and payment records. In some cases a customer has 10 lines with 10 different invoice numbers but may have paid 2 or more invoices with one check. I need to know how many unique payments were made per customer.
Cust# Inv# Chk#
1 109 101
1 110 101
1 111 102
3 112 10003
2 113 799
2 114 800
1 115 103
3 116 10009
2 117 799
1 118 103
So I need the statement to update the customer table with the annual payments
Customer Table
Cust# Payments
1 3
2 2
3 2
I get close but just not getting it to sort itself out.
View 9 Replies
ADVERTISEMENT
Sep 6, 2013
I need a query to return two values. One will be the total units and the other will be total unique units. See exmaple data below. It does not have to be one query. This will be in SP, so I can keep it seperate if I have to.
ID | ID_UNIT
1 | 01
1 | 01
1 | 02
1 | 03
1 | 03
1 | 04
1 | 04
I need two results.
Total Units = 7 - easy to do by using count()
Total unique units = 4 - I cannot use group by as it would return multiple results for each unit, which is not what we want.
View 3 Replies
View Related
Mar 30, 2015
how to return the 3 month rolling average count per username? This means, that if jan = 4, feb = 5, mar = 5, then 3 month rolling average will be 7 in April. And if apr = 6, the May rolling average will be 8.
Columns are four:
username, current_tenure, move_in_date, and count.
DDL (create script generated by SSMS from sample table I created, which is why the move_in_date is in hex form. When run it's converted to date. Total size of table 22 rows, 4 columns.)
CREATE TABLE [dbo].[countHistory](
[username] [varchar](50) NULL,
[current_tenure] [int] NULL,
[move_in_date] [smalldatetime] NULL,
[Cnt_Lead_id] [int] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
[code]....
View 9 Replies
View Related
Nov 2, 2015
I have a requirement to generate Invoice system for 1000 customers. how to create a report or SSIS Package to auto generate invoices everyday as per the start date of the customer.
View 1 Replies
View Related
Sep 27, 2005
Hello, everyone:
I have two tables with some duplicated records like,
ZZZTest:
C_IDC1C2C3
10AAA
20BBB
30AAA
40BBB
ZZZTestTable:
D_IDC11C22C33
1AAA
2AAA
3BBB
4BBB
5AAA
6AAA
7BBB
I wand to get the unique records by SELECT / JOIN statement. Now I used a query,
SELECT * FROM ZZZTest t
INNER JOIN ZZZTestTable tt
ON t.Col1=tt.Col11 AND t.Col2=tt.Col22 AND t.Col3 = tt.Col33
and got the records like,
C_IDC1C2C3D_IDC11C22C33
10AAA1AAA
30AAA1AAA
10AAA2AAA
30AAA2AAA
20BBB3BBB
40BBB3BBB
20BBB4BBB
40BBB4BBB
10AAA5AAA
30AAA5AAA
10AAA6AAA
30AAA6AAA
20BBB7BBB
40BBB7BBB
What I am expecting is,
C_IDC1C2C3D_IDC11C22C33
10AAA1AAA
30AAA2AAA
20BBB3BBB
40BBB4BBB
Any suggestion will be great appreciated.
Thanks
ZYT
View 3 Replies
View Related
Mar 7, 2015
I have three sprocs and three tables. I was told to use a clustered index in the first table and a unique clustered index on the second table. I never asked about the third table and the person I need to ask is on vacation. Most of the contents of the first table will be joined with all of the contents of the second table into the third table. Do I need to have a unique clustered index on the third table too?
The clustered index in the first sproc is on a unique key that I had created using by concatenating several columns together.
CREATE CLUSTERED INDEX IX_UNIQUE_KEY ON MRP.Margin_Optimization_Data (UNIQUE_KEY);
CREATE NONCLUSTERED INDEX IX_DATE ON MRP.Margin_Optimization_Data (PERIOD);
CREATE NONCLUSTERED INDEX IX_ODS_ID ON MRP.Margin_Optimization_Data
(GL_SEG1_COMPANY_ODS_ID, GL_SEG2_PROFIT_CTR_ODS_ID, GL_SEG3_LOB_ODS_ID, GL_SEG4_PRODUCT_DEPT_ODS_ID, GL_SEG5_ACCOUNT_ODS_ID);
The second sproc with the unique clustered index is on the unique key from the first table and a date attribute.
CREATE UNIQUE CLUSTERED INDEX IX_UNIQUE_KEY ON MRP.[MGN_OPT_KPI_SOURCE] (UNIQUE_KEY, PERIOD);
In the third sproc, I'll have a nonclusted index on the ODS_ID attributes, but I'm unsure of how to go about the clustered index situation.
CREATE NONCLUSTERED INDEX IX_ODS_ID ON MRP.MGN_OPT_KPI_VALUES
(GL_SEG1_COMPANY_ODS_ID, GL_SEG2_PROFIT_CTR_ODS_ID, GL_SEG3_LOB_ODS_ID, GL_SEG4_PRODUCT_DEPT_ODS_ID, GL_SEG5_ACCOUNT_ODS_ID);
View 4 Replies
View Related
Sep 15, 2014
I am looking to create a script that will go through a table a pick out the necessary columns to create a unique record. Some of the tables that I am working with have 200 plus columns and I am not sure if I would have to list every column name in the script or if they could be dynamically referenced. I am working with a SQL server that has little next to no documentation and everytime I type to mere some tables, I get too many rows back.
View 4 Replies
View Related
Oct 1, 2015
I have a small problem with a join clause, because i need to return all values from my table BL:
my code is:
SELECT cast(0 as bit) as 'Escolha',data, contado , ollocal ,origem, ousrdata,ousrhora
FROM
(
SELECT noconta,banco, u_area
FROM BL
[code]....
In fact, i need to return 2 accounts (16,35) - x.NOCONTA IN (16,35), but I know that the problem is on the WHERE clause.How can do that, because i need all the condition on WHERE clause regarding my table OL, but also, i need to return my two accounts (16,35).
View 2 Replies
View Related
May 8, 2015
how to track how many rows updated or deleted per day in a single table and load the information in another table .
View 7 Replies
View Related
Apr 30, 2014
I have one table say A and in which 4 columns are there. Out of 4 , one columns stores the queries like
'select * from table xyz' etc(Only select queries). I am writing a procedure in which I have to fetch this column and execute the query and wants to check whether query i.e. "select * from table xyz" contains any record or not. If yes , I am updating the table B with value as Pass , else Fail.
I used execute @queryfromvariable but it does not gives me count..
View 7 Replies
View Related
Jan 13, 2015
I have multiple databases in the server and all my databases have tables: stdVersions, stdChangeLog. The stdVersions table have field called DatabaseVersion which stored the version of the database. The stdChangeLog table have a field called ChangedOn which stored the date of any change made in the database.
I need to write a query/stored procedure/function that will return all the database names, version and the date changed on. The results should look something like this:
DatabaseName DatabaseVersion DateChangedOn
OK5_AAGLASS 5.10.1.2 2015/01/12
OK5_SHOPRITE 5.9.1.6 2015/01/10
OK5_SALDANHA 5.10.1.2 2014/12/23
The results should be ordered by DateChangedOn.
View 4 Replies
View Related
Mar 24, 2014
I run this code:
SELECT
Gruppo_Assegnatario,
[100] as stato1, [101] as stato2, [102] as stato3
FROM
(
select
[Code] ...
That extracts only zeros (columns "stato1", "stato2", "stato3"):
Gruppo_Assegnatariostato1stato2stato3
SDB_BE Vita Antiriciclaggio0 00
SDB_BE Vita Assistenza clienti000
SDB_BE Vita Emissione000
SDB_BE Vita Gestione Rendite000
SDB_BE Vita Liquidazioni000
[Code] ....
Unlike the "SourceTable":
select
CASE_ID_,
Stato,
Gruppo_Assegnatario
FROM TicketInevasiPerGruppoEStato
extracts
CASE_ID_ Stato Gruppo_Assegnatario
HD0000003736734 AssegnatoSDB_GBS Variazione
HD0000003736739 AssegnatoSDB_GBS Variazione
HD0000003736743 AssegnatoSDB_GBS Variazione
HD0000003736783 AssegnatoSDB_GBS Variazione
HD0000003736806 SospesoSDB_BE Vita Selezione
[Code] ....
How can I get the right count in the first data mining replacing the zeros (columns "stato1", "stato2", "stato3")?
View 5 Replies
View Related
May 28, 2015
I need to build TSQL query to return the Last unit Cost from my table of movement of goods SL (on CTE) but the MAX(Datalc) must be Less or Equal to my HeaderInvoice.
This is my script:
With MaxDates as (
SELECT ref,
MAX(epcpond)[Unitcostprice],
MAX(datalc) MaxDate
FROM sl
[code]....
the problem I have right now is that the Unitcostprice of my table of goods movements has a top date greather than the date of my bill.
Example:
invoice date : 29.01.2015 unitcost on invoice line = 13,599722
Maxdate (CTE) : 19.03.2015 unitCost from my table of movement of goods = 14,075
That ´s not correct because the MAxdates > invoice date and the unitCost of 14,075 is the cost on 19.03.2015 and not just before my invoice date.
View 4 Replies
View Related
Jan 2, 2006
This is my function, it returns SQLDataReader to DATALIST control. How
to return page number with the SQLDataReader set ? sql server 2005,
asp.net 2.0
Function get_all_events() As SqlDataReader
Dim myConnection As New
SqlConnection(ConfigurationManager.AppSettings("..........."))
Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
Dim parameterState As New SqlParameter("@State", SqlDbType.VarChar, 2)
parameterState.Value = Request.Params("State")
myCommand.Parameters.Add(parameterState)
Dim parameterPagesize As New SqlParameter("@pagesize", SqlDbType.Int, 4)
parameterPagesize.Value = 20
myCommand.Parameters.Add(parameterPagesize)
Dim parameterPagenum As New SqlParameter("@pageNum", SqlDbType.Int, 4)
parameterPagenum.Value = pn1.SelectedPage
myCommand.Parameters.Add(parameterPagenum)
Dim parameterPageCount As New SqlParameter("@pagecount", SqlDbType.Int, 4)
parameterPageCount.Direction = ParameterDirection.ReturnValue
myCommand.Parameters.Add(parameterPageCount)
myConnection.Open()
'myCommand.ExecuteReader(CommandBehavior.CloseConnection)
'pages = CType(myCommand.Parameters("@pagecount").Value, Integer)
Return myCommand.ExecuteReader(CommandBehavior.CloseConnection)
End Function
Variable Pages is global integer.
This is what i am calling
DataList1.DataSource = get_all_events()
DataList1.DataBind()
How to return records and also the return value of pagecount ? i tried many options, nothing work. Please help !!. I am struck
View 3 Replies
View Related
Nov 8, 2006
Please i have created some tables Delivary with this columns (DelivaryId,DelivaryNo,QtyRecieved,DelivaryDate,ProductId) and Product with this columns (ProductId,ProductCode,ProductName,ProductPrice) as you can see the product table keeps record of products whlie the delivary table keeps record of stock supplied. I will like to create another table that will keep record of stock sold out (Invoice Table) based on the qty recieved from the delivaries table
Please help
View 4 Replies
View Related
Apr 18, 2001
Hello, I want to write a unique constraint that applies to more than one column. What I mean is that the uniqueness should be that if column A is 5 and column B is 3 no other row where A and B has those values can exist.
Do I write this as a check constraint ? Or how do I do it ?
Also, is there anyone who knows some good reading on how to use Link Tables (many to many relations) in MS SQL Server ?
View 1 Replies
View Related
Jun 19, 2008
I have a table called tblImages with the following columns:
ImageID [int]
UserID [int]
MainImg [bit]
what i need to ensure is that only one MainImage can be = 1 (true) for each userId at any one time.
any idea what i need to do?
alex
View 1 Replies
View Related
Mar 17, 2014
Using SSE 2012 64-bit...How can I check all fields for duplicate records?I tried OVER PARTITION..But that is returning an error message
Code:
USE db
SELECT ROW_NUMBER() OVER (PARTITION BY all fields)
ORDER BY ID --Not unique) AS RowNumber
The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator
View 4 Replies
View Related
Sep 3, 2015
I have one table like below Test table. My requirement is to create constraints to confirm <g class="gr_ gr_331 gr-alert gr_gramm Grammar only-ins replaceWithoutSep" data-gr-id="331" id="331">uniqueness</g> of STID value 101 with LN.
like
ID - LN - STID
1 - 'ABC' - 101 ---- Valid Row
2 - 'ABC' - 202 --- Valid Row
3 - 'ABC' - 202 --Valid Row (as I want only unique when LN = 'ABC' with STID = 101)
4 - 'ABC' - 101 -- Invalid Row (As I want uniqueness base on LN and STID = 1011)
create table dbo.Test
(
ID int identity,
LN varchar(50),
STID bigint
)
Is this possible with constraints as I don't want to use <g class="gr_ gr_1041 gr-alert gr_gramm Grammar only-ins doubleReplace replaceWithoutSep" data-gr-id="1041" id="1041">trigger</g>.
View 4 Replies
View Related
Apr 10, 2008
have a table hits with following fields with sample Data
Code: CreatedOn: HitId
4 2008-04-01 2
4 2008-04-01 2
4 2008-04-02 2
5 2008-04-02 4
5 2008-04-02 4
5 2008-04-02 20
5 2008-04-03 5
4 2008-04-03 6
3 2008-04-04 9
5 2008-04-04 12
____________________________________________________________
i want to create a query to get count of the code per day. And Count Having unique HitId
__________________________________________________________
Code: Date: Total_Count: UniqueHit_Count
3 2008-04-04 1 1
4 2008-04-01 2 1
4 2008-04-02 1 1
4 2008-04-03 1 1
5 2008-04-02 3 2
5 2008-04-03 1 1
------------------------------------------------------------
How can i do this to count code per day and uniqueHit count in a single query?
I am using sql server 2005.
Muhammad Saifullah
View 1 Replies
View Related
Oct 18, 2013
I have trouble to get the uniqueidentifier I just inserted out.
---sp
CREATE PROCEDURE dbo.FAC_Ins_USR
@LAST_NAME AS nvarchar(60)
,@FIRST_NAME AS nvarchar(60)
,@NewID uniqueidentifier output
AS
BEGIN
[Code] ....
The new data went into the table, and the print @myErr shows 0.
But print @myID shows nothing.
---here is the part of the table
CREATE TABLE [dbo].[USERS](
[USER_ID] [uniqueidentifier] DEFAULT NEWID() NOT NULL,...
View 11 Replies
View Related
Oct 26, 2007
Let's say I have a table MyTable with two colums, One and Two. If I wanted to return unique combinations of these two fields I can do
select distict One, Two from MyTable
How do I return the count of such unique combinations? The following of course does not work but you get the idea.
select count(distinct One, Two) from MyTable
Thanks.
View 6 Replies
View Related
Jun 30, 2015
I need to generate unique string Id from the range AAAAA to PZZZZ. This id will be unique for each Users for e.g.
User ---- TransactionNo ---- UniqueId
--------------------------------------------
12001 --- 101 --- AAAAA
12001 ---- 102 --- AAAAB
12001 --- 103 --- AAAAC
12001 ---- 104 --- AAAAD
13001 --- 105 --- AAAAA
13001 ---- 106 --- AAAAB
13001 --- 107 --- AAAAC
13001 ---- 108 --- AAAAD
more the records of each user, unique should be the ID as displayed above.. Optimized way to generate the same...
View 3 Replies
View Related
Apr 28, 2008
Hi All,
I need a bit of help with a join. I have 2 tables :
TradeSummary
has fields : SymbolID, CurrentPrice, TotalValue
Trades
has fields : SymbolID, TradeID, ExecutionTime, TradeValue
TradeSummary has one entry for each SymbolID, while Trades contains one or more entries per SymbolID
and what I want to retreive is :
For every item in TradeSummary get CurrentPrice, TotalValue from TradeSummary
and also get TradeValue from Trades for the record for max(ExecutionTime)
tables are joined on TradeSummary.SymbolID = Trades.SymbolID
Every attempt of mine so far returns multiple rows for each SymbolID - I want only one row per SymbolID
thanks in advance
View 11 Replies
View Related
Feb 13, 2013
I am trying to create a query to return the latest record from a dataset. The code created so far returns multiple records, where I need a single record to be returned.
Please see attached .pdf for full explanation....
View 2 Replies
View Related
Jan 19, 2007
I've been searching and trying out SQL statements for hours and I'mstill having a problem.I'm querying 3 tables... and I only want the first unique recordreturned.Currently, the data returned looks like this..............New York New York ANew York New York BNew York New York CLos Angeles California ALos Angeles California BLos Angeles California CI want the select statement to return this instead...New York New York ALos Angeles California AI'm using MS SQL server.please help?thanks for your help.
View 4 Replies
View Related
Apr 28, 2008
Hi All,
I need a bit of help with a join. I have 2 tables :
TradeSummary
has fields : SymbolID, CurrentPrice, TotalValue
Trades
has fields : SymbolID, TradeID, ExecutionTime, TradeValue
TradeSummary has one entry for each SymbolID, while Trades contains one or more entries per SymbolID
and what I want to retreive is :
For every item in TradeSummary get CurrentPrice, TotalValue from TradeSummary
and also get TradeValue from Trades for the record for max(ExecutionTime)
tables are joined on TradeSummary.SymbolID = Trades.SymbolID
Every attempt of mine so far returns multiple rows for each SymbolID - I want only one row per SymbolID
thanks in advance
View 7 Replies
View Related
Jul 9, 2015
I've got the following query
SELECT MAX(CountCategories.countOfCategory) AS maxCategory, CountCategories.phoneNumber, CountCategories.category
FROM
(SELECT Categories.phoneNumber, Categories.category, Count(Categories.category) AS CountOfcategory
FROM Categories
GROUP BY Categories.phoneNumber, Categories.category) AS CountCategories
GROUP BY CountCategories.phoneNumber, CountCategories.category;
As you can see I have a sub query that is counting categories for the same phoneNumber and category. I then create a Select query about this as I wanted to only return the highest result. However the above query is returning the same as the subquery.It isn't showing me the unique result for the highest count.
View 1 Replies
View Related
Jul 24, 2013
I have a table in Access 2007 that has about 30 field names and I want to have a count of how many unique rows there are in each field. I want to have these results put into another table that will just have the field name and then the count of how many unique rows there are.
I have code in VBA that will loop through my SQL and change out the field name, but I can't seem to get the SQL right before I can start looping it. For just one field name this would be what I have to count the unique names...
So far I have this:
INSERT INTO newtable
COUNT(*) FROM (SELECT Raw_Table.FieldName, COUNT(Raw_Table.FieldName) AS CountOfFieldName
FROM Raw_Table
GROUP BY Raw_Table.FieldName);
And its not going too well.
View 1 Replies
View Related
Feb 19, 2014
Aim- Need to count how many [FDMSAccountNo] fall under a unique parentid
This is my query
SELECT [FDMSAccountNo]
,ParentID
FROM [FDMS].[dbo].[Dim_Outlet]
where ParentID = '878028271889'
Which produces the following, The number of fdmsaccountno under parentid is two. Its two because one of the fdmsaccountno is = to parentid
FDMSAccountNo ParentID
878028271889878028271889
878028272887878028271889
878028273885878028271889
Desired result
Parentid #_of_outlets
878028271889 2
View 5 Replies
View Related
May 27, 2002
Hi,
I am making a program in Visual Basic .NET with SQL Server 2000.
I have a table "MyTable" with a primary key named "Id". The primary key is 'Create Unique' checked and 'Index' selected. When I insert all the fields required, except "Id" of course, I need the new record's "Id" in my VisualBasic program, but I don't know how...
I must do one of them, but don't know how either of them:
-Create a trigger on insertion that will send to the user that sended the insert command the "Id" of the record just created.
or
-get the command in Visual Basic that will send the Insert command with a return field ("Id")
Thanks in advance,
Sebastien Anselmo
View 6 Replies
View Related
Apr 17, 2008
Ok... I know this is something that I've seen and probably done before....
I have a value that will show up multiple times in a table. I want to order the table by that value and then count up from 1 to however many items that it shows up as.
The example below shows the results I want. Value1 and Value2 are in the table, LineNumber is what I need to generate.
Value1
Value2
LineNumber
1
Hey
1
1
Can
2
1
Someone
3
1
Help
4
1
With
5
1
This
6
2
Very
1
2
Simple
2
2
Issue
3
2
As
4
2
I
5
3
Have
1
3
A
2
3
Brain
3
3
Freeze
4
--Thanks--
View 4 Replies
View Related
Sep 2, 2015
updating the # of Payer from below query to match with the # of rows for each payer record. See the Current and desired results below. The query is currently counting the # of rows for all payers together and updating 3 as # of payers. I need it to count # of rows for each payer like shown inDesired result below. It should be showing 1 for first payer and 2 for 2nd & 3rd based on # of times each payer is repeated..
SELECT b.FILING_IND, b.PYR_CD, b. PAYER_ID, b. PAYER_NAME,a.CLAIM_ICN,
(Select Count(*) From MMITCGTD.MMIT_CLAIM a, MMITCGTD.MMIT_TPL b , MMITCGTD.MMIT_ATTACHMENT_LINK c where a.CLAIM_ICN_NU =
c.CLAIM_ICN and b.TPL_TS = c.TPL_TS and a.CLAIM_TYPE_CD = 'X'
[Code] ....
Current Result
FILING_IND
PYR_CD
PAYER_ID
PAYER_NAME
CLAIM_ICN
#_OF_PAYER
[code]....
View 4 Replies
View Related