Tag: join

Duplicate record after joining two tables

I am currently having trouble with my query after joining two tables I am getting duplicated records. What i want to do is get the details from table 1 and the payment record from table 2 with same date.. here is my query. SELECT dbo.tblautoipekonek.ipno, dbo.tblautoipekonek.awbno, dbo.tblautoipekonek.ipamount, dbo.tblautoipekonek.orno, dbo.tbladdfunds.username, dbo.tbladdfunds.newctfbal, dbo.tbladdfunds.addedctfamt, dbo.tbladdfunds.oldctfbal, dbo.tbladdfunds.newipbal, dbo.tbladdfunds.addedipamt, dbo.tbladdfunds.oldipbal, […]

SQL Server: Sum(column) and count(column) in Select and Join statement

I’m trying to query how many transaction each loyaltyID column has in AnthemTxns_Jr and what the total of all of their transactions adds up to. Right now, when I run this script, it says, Cannot find either column “dbo” or the user-defined function or aggregate “dbo.AnthemTxns_JR.count“, or the name is ambiguous Code: Select DISTINCT dbo.ANTHEM_IDS_JR.loyaltyID, […]

Join two tables, only display unique values and maxium date

I am trying to join two tables, but only display the unique rows and the latest date. Looking online I have discovered various ways to achieve or the joining of two tables, or the selection of unique rows (and latest date) in a single table. How would I go about combining these two? Joining two […]

SQL Server Query: Daily Data Snapshot Comparison (Counting Delta Occurrences)

I am working towards counting customer subscription (“package”) changes. To do this, I am selecting all data from my package table once, every day. I am calling the daily query results “snapshots” (approx 500k rows). I then load the snapshot data into a new table. After 10 days I have a total of 5 million […]

joining tables without using index or rownum

Update: Regarding performance I found what I was not managing to remember when I wrote this question. It is ROWID in PL/SQL and %%physloc%% in T-SQL. But this can not be used in the case of my original post. Regarding convenience Oracles NATURAL JOIN does the trick cleanly – at least in this case. But […]

Eliminate duplicate rows from query output

I have a large SELECT query with multiple JOINS and WHERE clauses. Despite specifying DISTINCT (also have tried GROUP BY) – there are duplicate rows returned. I am assuming this is because the query selects several IDs from several tables. At any rate, I would like to know if there is a way to remove […]

Datediff SQL-server query for statistical data with count & group by

So what I’m trying to attain is to count how many Users trigger EventCode 90 relative to when they last recieved a Notification. Source tables are the following: ServiceOne UserNr RegisteredUntil NotificationMonth 532091985 2016-05-15 00:00:00.000 5 950628185 2016-03-15 00:00:00.000 3 561007126 2016-09-15 00:00:00.000 9 Notifications UserNr NotificationNr NotificationDate Service 532091985 134567 2013-04-16 00:00:00.000 1 532091985 […]

SQL Server – A query to generate a JOINs path from one table to another?

If I know I need to JOIN Table 4 onto Table 1, but they have no columns in common, it takes some effort to find a Table 2 that has Column A in common with Table 1, then a Table 3 that has Column B in common with both Table 2 and Table 4, and […]

Getting the correct Joins

Okay, what I am trying to do is I am trying to get all the Accounts that are ENROLLED. This is my first actual report outside from school so please bare with me because I am not experienced. I have 4 tables. The tAuditTracking has all of the users info as follow: ID Contract_ID IP […]

Select and Update are returning/updating different number of records with same join

Pardon my long question but I am more looking for troubleshooting advice. Both A.DR_CR and B.DR_CR has distinct values ‘D’ or ‘C’ I wanted to audit the number of records in table A where the value will be changed (D to C or vice versa) hence I wrote the following select before update statement. The […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.