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

Return Unique Count (Two Values)

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

T-SQL (SS2K8) :: How To Return 3 Month Rolling Average Count Per Username

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

T-SQL (SS2K8) :: Auto Generation Of Invoice

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

How To Return An Unique From Two Table With Duplicated Records?

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

T-SQL (SS2K8) :: Joining On A Table With Unique Clustered Index

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

T-SQL (SS2K8) :: Identify Columns Which Will Create Unique Record In A Table

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

T-SQL (SS2K8) :: Return All Values From Table

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

T-SQL (SS2K8) :: Check How Many Rows Delete Updated Per Day And Store It In Another Table?

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

T-SQL (SS2K8) :: Table With 4 Columns - How To Fetch Count

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

T-SQL (SS2K8) :: Multiple Databases - Return A Single Table With Three Columns

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

T-SQL (SS2K8) :: Pivot Table - How To Get Right Count In First Data Mining Replacing Zeros

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

T-SQL (SS2K8) :: Finding Last Cost By Article And Compare With Invoice Line Cost?

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

How To Return SqlDataReader And Return Value (page Count) From SPROC

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

Invoice Table

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

Check / Unique Constraints

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

Unique / Check Constraint?

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

Check For Duplicate Records No Unique ID

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

DB Design :: Unique And Check Constraints

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

How To Get Count Per Day With Unique ID?

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

How To Return Unique Identifier In SP

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

Count Of Unique Combinations

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

T-SQL (SS2K8) :: Generate Unique String ID

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

Help With A Join That Needs To Return Unique Records

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

Query To Return Unique Value From Dataset

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

SQL Statement To Return First Unique Record

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

Help With A Join To Return Unique Values

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

Unique Result For Highest Count

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

Count Unique Rows In Each Field

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

Distinct Count Fall Under Unique ID

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

How To Return Primary Unique Index Key On Insert

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

Count Up Based On Unique Value -- Brain Freeze

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

Transact SQL :: Update Unique Records Count For Subquery?

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







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