Transact SQL :: Select (sum) Statements With Two Table?
Jun 5, 2015
I have a database with two tables (Table1 and Table2)
looking for something like:
Table1:
+----+-------+----------+
| id | Name | email |
+----+-------+----------+
| 1 | name1 | mail1 |
| 2 | name2 | mail2 |
| 3 | name3 | mail3 |
| 4 | name4 | mail4 |
| 5 | name5 | mail5 |
| 6 | name6 | mail6 |
+-- +-------+----------+
Table2:
+----+------------+---------+------+
| id | table1_ID | fee | type |
+---+-------------+---------+------+
| 1 | 1 | 20000 | 1 |
| 2 | 3 | 1000 | 1 |
| 3 | 1 | 10000 | 1 |
| 4 | 3 | 1000 | 1 |
| 5 | 5 | 500 | 1 |
| 6 | 5 | 500 | 2 |
| 7 | 1 | 60000 | 2 |
| 8 | 2 | 1000 | 2 |
+----+------------+---------+-------+
i want the query that return:
+--------+-------+---------------------------------+---------------------------------+--------+
| name | email | a:sum(fee) where type=1 | b:sum(fee) where type=2 | b/10 |
+--------+-------+---------------------------------+---------------------------------+--------+
|name1 | mail1 | 30000 | 60000 | 6000|
|name2 | mail2 | 0 | 1000 | 100 |
|name3 | mail3 | 2000 | 0 | 0 |
|name4 | mail4 | 0 | 0 | 0 |
|name5 | mail5 | 500 | 500 | 50 |
|name6 | mail6 | 0 | 0 | 0 |
+--------+-------+---------------------------+---------------------------------------+---------+
View 4 Replies
ADVERTISEMENT
Feb 13, 2014
I am not sure if i am looking correctly at the deadlocks but i see deadlocks between two select statements.These statements are being run through an application. Below is the table schema from where the select is being performed
CREATE TABLE [dbo].[CMS_LOCKS7](
[PARENTID] [int] NOT NU, --we have a non clustered index on this column
[CHILDID] [int] NOT NULL, --we have a non clustered index on this column
[ISMEMBER] [int] NOT NULL, -- we have a non clustered index on this column
[ORDINAL] [int] NULL,-- we have a non clustered index on this column
[Code] ....
View 12 Replies
View Related
Oct 22, 2015
I had to enable identity_insert on a bunch of tables and I have already done that. Now I need to modify my insert into select * from statements to include column list names along with identity columns for select as well as insert statements. The DDL is same but they are both different databases.There are almost 100 tables that it needs to be modified. Is there a way we can generate scripts for insert and select for each individual table along with their column lists including the identity column?
View 7 Replies
View Related
Sep 29, 2015
I cannot find the problem with this function.
ALTER function [Event].[DetermineTrackTime](@TrialID varchar(max)) returns int as
begin
Declare @ret int;
Declare @EnterVolumeTime int;
Declare @ExitVolumeTime int;
Declare @StartTrackTime int;
[code]....
I am getting the following error on line 75:
Select statements included within a function cannot return data to a client.
This is happening when declaring TrackUpdateCursor
The compiler has no problem with the VolumeTimesCursor. What is causing this and what can I do about it?
View 20 Replies
View Related
Oct 13, 2015
I am trying to generate a script to drop 15 tables which have dependencies across the database. Is there a script that could generate the drop stataments based on the child table first , parent table last strategy?
View 3 Replies
View Related
May 30, 2013
I have a database which will be generated via script. However there are some tables with default data that need to exist on the database.I was wondering if there could be a program that could generate inserts statments for the data in a table.I know about the import / export program that comes with SQL but it doesnt have an interface that lets you copy the insert script and values into an SQL file (or does it?)
View 7 Replies
View Related
Apr 1, 2004
Hi,
newbie here.
Im trying to perform the following two select statements on the one table. I have been trying innerjoins etc but keep getting errors. The basis of what im trying to do is this.
SELECT column1
FROM table1
WHERE column2 = (select column2 from table1 where column3 = 14)
Any ideas greatly appreciated.
View 1 Replies
View Related
Apr 29, 2008
Hi,
OK, trying to return the results from two SQL statements into a DataSet using SqlDataAdapter. The SELECT statements query the same table but are looking for different records based on the date that the records were inserted - the 1st query looks for records fro the current month and the 2nd one looks at the same records but for the previous month. The goal is to be able to do some math within a repeater and get the difference between the two records.
Sounds easy enough and it has worked for me in different variations of the same idea but not this time - here's the code:
Protected Sub buildPartsReport(ByVal varHC) objConn.Open() sSQL = "SELECT ap.id,item_model,item_sn,aircraft_id,item_loc=item_type+ ' on ' +(SELECT tnum FROM T_Aircraft WHERE id=aircraft_id),apt.tot_time As endTimes,apt.tot_cycles as endCycles FROM T_Aircraft_Parts ap, T_Aircraft_Parts_Totals apt WHERE ap.id=apt.part_id AND report_date= '" & rD & "' AND item_type LIKE 'engine%';SELECT ap.id,apt.part_id,apt.tot_time as startTimes,apt.tot_cycles as startCycles FROM T_Aircraft_Parts ap, T_Aircraft_Parts_Totals apt WHERE ap.id=apt.part_id AND report_date= '" & oldRD & "' AND item_type LIKE 'engine%'" Dim objCommand As New SqlDataAdapter(sSQL, objConn) DS = New DataSet() objCommand.Fill(DS) Repeater1.DataSource = DS Repeater1.DataBind() DS.Dispose() objCommand.Dispose() objConn.Close() End Sub
Sorry if it wrapped a bit. The "rD" and "oldRD" are the variables for the date ranges (currently set to static numbers for testing). I'm getting the following error when I run this on an ASP.Net page:
System.Web.HttpException: DataBinding: 'System.Data.DataRowView' does not contain a property with the name 'startTimes'.
The code works fine when run via the Query Tool on the SQL server (SQL 2005 Std) though it produces two distinct "tables" which I'm guessing is the problem. I've tried variations on the code including creating a 2nd dataset and then attempting a merge (no joy) and I've tried the ".TableName" route but it complains about trying to add the tablename twice.
Thoughts? I need to get this to work - it is part of a reporting component for an application that I'm developing and I'm stuck. Thanks as always...
View 5 Replies
View Related
May 18, 2015
How Can I select Table Dynamically from in Side SQL Query
i.e.,
Select * from (Here I want Select the Dynamically from other Query)
View 6 Replies
View Related
Jul 10, 2015
DECLARE @Query varchar(8000)
Create Table
#tempCountRichard (Status varchar(50), Number Varchar(1000)
Set @Query = 'Insert Into #tempCountRichard
Select Count(Status),
[code]...
With the following SQL. When I select from the temp table I return the right count but my second column doesn't return anything.
Count Status
1010 2000
1111 2222
When I run the query that is being inserted into the the temp table I return the correct results
Count Status
1010 Pass
2000 Pass with Obs
1111 Fail
2222 None
how to select the data from the temp table exactly the same way it was inserted. As I need to select the exact same data from the insert.
View 23 Replies
View Related
Nov 2, 2015
<g class="gr_ gr_54 gr-alert gr_gramm Grammar multiReplace" data-gr-id="54" id="54">I want</g> to get row number of last inserted into employee table?
How can i get it @@identity function returns null.
is there any way to do it?
View 9 Replies
View Related
Jul 9, 2015
I basically want to select all GRNID's from one table but they have to be between dates in another table.So I want all GRN's between two dates found in the ABSPeriodEndDate table. To find out the start date for the between clause I need to find the MAX Period then minus 1 and the max year. To find the end date of the between clause I want I need to find both the max period and year. But I want the DateStamp column to return the results for the between clause. My query is below:
SELECT tblGRNItem.GRNID
FROM tblGRNItem
INNER JOIN ABSPeriodEndDates ON tblGRNItem.DateCreated = ABSPeriodEndDates.DateStamp
WHERE tblGRNItem.DateCreated BETWEEN
(SELECT ABSPeriodEndDates.DateStamp FROM ABSPeriodEndDates WHERE ABSPeriodEndDates.DateStamp = (SELECT
[code]....
View 6 Replies
View Related
Sep 15, 2015
I want to select custom format from table?
View 7 Replies
View Related
Jul 31, 2015
I have a table that has for each shop a value that can change over time.For example
BK_POS 1 --> Segment A
BK_POS 1 --> Segment /
What I would like to achieve is to get all distinct Shops (BK_POS) from the table above, but if for that specific pos a row exists where the segment = "/" then I do not want to take this BK_POS in my select query.More concrete, the for example above I do not want to select BK_POS 1 because he has one row where the segment = "/".
View 3 Replies
View Related
Dec 4, 2015
I'm trying to fill a temp table whose columns are the same as another table plus it has one more column. The temp table's contents are those rows in the other table that meet a particular condition plus another column that is the name of the table that is the source for the rows being added.
Example: 'permTable' has col1 and col2. The data in these two rows plus the name of the table from which it came ('permTable' in this example) are to be added to #temp.
Data in permTable
col1 col2
11, 12
21, 22
Data in #temp after permTable's filtered contents have been added
TableName, col1 col2
permTable, 11, 12
permTable, 21, 22
What is the syntax for an insert like this?
View 2 Replies
View Related
Jun 17, 2015
I have a SQL query like this
select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans] from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date) group by TransactionCode, CurrencyCode,TransactionAmount order by CurrencyCode
As per this query I got the result like this
CurrencyCode TransactionCode TransactionAmount No.OfTrans
AED BNT 1 1
AED BNT 12 1
AED SCN 1 1
AED SNT 1 3
[Code] ....
But I wish to grt result as
CurrencyCode TransactionCode TransactionAmount No.OfTrans
AED BNT 13 2
AED SCN 1 1
AED SNT 11 7
AFN BPC 8 6
[Code] ....
I also tried this
select CurrencyCode,TransactionCode,TransactionAmount,COUNT(TransactionCode) as [No. Of Trans]
from TransactionDetails where CAST(CurrentTime as date)=CAST(GETDATE()as date)
group by TransactionCode order by CurrencyCode
But of course this codes gives an error, but how can I get my desired result??
View 5 Replies
View Related
May 12, 2015
I am using SQL SERVER 2008R2, not Denali, so I cannot use OFFSET FETCH Clause.
In my stored procedure, I am doing a SELECT INTO #tblTemp FROM... Working fine. This resultset is going to be used in an SSIS package which will generate a pipe-delimited .txt file... Working fine.
For recoverability sake, I am trying to throttle back on the commit chunks to 1000 rows per commit until there are no more rows. I am trying to avoid large rollbacks.
Q: Am I supposed to handle the transactions (begin/commit/rollback/end trans) when the records are being inserted into the temp table? Or when they are being selected form the temp table?
Q: Or can I handle this in my SSIS package for a flat file destination? I don't see option for a flat file destination like I do for an OLE DB Destination (like Rows per batch, Maximum insert commit size).
View 6 Replies
View Related
May 12, 2015
I need to breakdown some information with just initials (sometimes 2, sometimes 3 if the initials are already used) SQL Server throws the error of:
Msg 125, Level 15, State 4, Line 1
Case expressions may only be nested to level 10.
What should I alter, or how should I write this query so I can use all of the needed case statements? (their are actually about 24 when statements, but this is just to get a working example to display)
Select
case
when employee_name Like 'Jorge Jones' Then 'JJ'
when employee_name Like 'Mike Mikes' Then 'MM'
when employee_name Like 'Albert Alvarez' Then 'AA'
when employee_name Like 'Hernandez-Sotata' Then 'HS'
[code]....
View 5 Replies
View Related
Sep 29, 2015
I have two databases DB1 and DB2 DB1 has a source table named 'Source' I have created a login 'Test_user' in DB2 with Public access. I have also created a view named 'Test_view' in DB2 which references data from DB1.dbo.Source
How can I do the following: AS A Test_user
SELECT * FROM DB2.dbo.Test_view --Should work
SELECT * FROM DB1.dbo.Source --Should Not work
View 3 Replies
View Related
Aug 2, 2006
I have SQL server Express. How can you import a database, using transact sql code, onto a server from your local PC.
View 11 Replies
View Related
Jun 3, 2015
So I'm thinking if I can have multiple statements within the CASE-THEN..or do I have to CASE out each individually? Kind of like this....
CASE
WHEN [AddressType] = 'M'
THEN [MailingAddress].[Address1]
[MailingAddress].[Address2]
[MailingAddress].[City]
[MailingAddress].[State]
[MailingAddress].[Zipcode]
WHEN [AddressType] = 'D'
THEN [DefaultAddress].[Address1]
[DefaultAddress].[Address2]
[DefaultAddress].[City]
[DefaultAddress].[State]
[DefaultAddress].[Zipcode]
View 3 Replies
View Related
May 12, 2015
I have two tables, D and C, whose INTERSECT / EXCEPT values I'd like to insert in the following table I've created
CREATE TABLE DinCMatch (
servername varchar(50),
account_name varchar (50),
date_checked datetime DEFAULT getdate()
)
The following stmt won't work...
INSERT INTO DinCMatch
select servername, account_name from D
intersect
select servername, account_name from C
... as the number of supplied values does not match table definition.
View 2 Replies
View Related
Jun 17, 2015
STEP1:
CREATE TABLE Trace(Statement VARCHAR(MAX))
INSERT INTO Trace
VALUES('select * from Account'),('select * from Account') ,('Select LastUpdated,Lastdeleted,LastInserted from History'),
('Insert into Account Select lastUpdated from History'),('Delete from OldAccount where LastUpdatedId=3'),('Delete from OldAccount where LastDeletedId=3'),('Delete from OldAccount where LastInserted=3'),('DROP TABLE BMP')
[code]....
now,when i run step3 ; i wanted to see if there is actually a delete or insert or select or update happens but as i used like %% (matching characters) i am getting all names matching with the % % , example row 7 in above is there a way i can use any wildcards and only find if there is actual delete, actual insert, actual select, actual update statement happening.
View 12 Replies
View Related
Aug 5, 2015
I have a table that keeps track of all changes that were performed in an application. There is a column called "old value" and column called "new value". There are some values in the application that don't require data therefore the "old value" or "new value" values can be empty. These columns are an nvarchar data type because the value can be text or numbers or dates. An example is "ReceivedDate". There is a report that is generated based on this table.
What is happening is the query in the report dataset is adding dates when it should be displaying empty. They query is using "CASE/WHEN/THEN". What I need is "When the column is "RecievedDate" and it is not null then convert it to a date". This is for formatting purposes.
This is an example of the table:
UpdateColumn
Old Value
New Value
ReceivedDate
7/8/2015 5:00:00 AM
ReceivedDate
7/8/2015 12:00:00 AM
7/9/2015 5:00:00 AM
ReceivedDate
7/9/2015 12:00:00 AM
So, the first time it was updated there was no value but it was replaced with July 8, 2015 and so on.This is what the report is displaying
This is the query:
CASE UpdateColumn
... WHEN 'ReceivedDate' THEN (replace(convert(varchar(11),CONVERT ( date, oldvalue ), 106), ' ', '-') )
...
I tried adding
CASE UpdateColumn
...
WHEN 'ReceivedDate' IS NOT NULL
THEN (replace(convert(varchar(11),CONVERT ( date, oldvalue ), 106), ' ', '-') )
...
But it did not like the "IS NOT NULL".
View 9 Replies
View Related
Jun 25, 2015
We are not allowed to write delete statement in a function. It will give the below error.
"Invalid use of a side-effecting operator 'DELETE' within a function."
We will get the same error for truncate/drop statements.
What is the reason behind this error?
View 8 Replies
View Related
Jun 15, 2015
I am working on an app that getting quite a few deadlocks due to delete statements. I have turned on the sql trace flags and pulled the offending delete statements out of the ERRORLOG and trying to mesh those up with the indexes defined on the table, etc. looking to see if there is anything that can be done strictly from the db side (no app code change) to reduce/eliminate these deadlocks. I have ran some tests/played around with RCSI and even disabling lock escalation but neither have improved my results.
What I have done is to search the errorlog file for DELETE FROM Tablename, output those matching lines, then sort of normalize the literal values to # or XYZ, open in Notepad++, removed trailing whitespace + dups and sort to come up with these results for the unique list of offending T-SQL statements (a LOT easier to read in text editor so sending screen cap.
Open this url in new tab [URL] ....
View 7 Replies
View Related
Aug 20, 2015
I have some old code consisting of over 30 Union statements, unioning 7/1/15-8/1/15 data according to the below code. Each block is the same except for the date. How could I rewrite these 30 union statements into a more concise query?
Each client (people_id) has a distinct agency_id_number. So the code below totals the number of distinct clients/day and groups them by programs and facilities.
Sample data that you can use to test this report:
IF OBJECT_ID(N'tempdb..#sample_census') IS NOT NULL drop table #sample_census
create table #sample_census
(
start_date datetime,
program_name varchar(25),
facility_name varchar(25),
agency_id_number varchar(25)
)
[Code] ....
View 3 Replies
View Related
Jun 19, 2015
I want to include GO statements to execute a large stored procedure in batches, how can I do that?
View 9 Replies
View Related
Jun 15, 2015
I need to list out all the procedures which have select queries and does not use NOLOCK hint. Please note that there are more than 2000 sps and some sps have single select some have multiple, some does not have select queries at all. I need to find out only the ones which does not use NOLOCK hint in a select query.
View 8 Replies
View Related
Jan 16, 2007
Hello, how can i merge together several select statements?
I have something like this:
CREATE PROCEDURE Forum_GetThreads @ID int,@AscDesc bitASBEGINSET NOCOUNT ON;SELECT * FROM forum_ansageSELECT * FROM forum_topics WHERE (status = 0) ORDER BY (created) DESCIF (@AscDesc = 0)BEGIN SELECT * FROM forum_topics WHERE (status > 0) ORDER BY (created) DESCENDELSEBEGIN SELECT * FROM forum_topics WHERE (status > 0) ORDER BY (created) ASCENDHere i want to merge them all together and return only one SELECT statement with all the data
View 5 Replies
View Related
May 12, 2008
Both of these work fine separately; How do I join these two Select Statements?
SELECT MPI.CREATE_DT,MPI.MPI_NBR, MPI.LAST_NM,MPI.FIRST_NM,
MPI_CURRENT_ADDR.ADDR_NBR, MPI_CURRENT_ADDR.ADDRESS_1, MPI_CURRENT_ADDR.ADDRESS_2,
MPI_CURRENT_ADDR.CITY,MPI_CURRENT_ADDR.STATE_CD,MPI_CURRENT_ADDR.ZIP_CD,MPI_CURRENT_ADDR.PHN_NBR,
MPI_PERSON.BIRTHDAY,MPI_PERSON.SEX
FROM MPI,MPI_CURRENT_ADDR,MPI_PERSON
WHERE (MPI.MPI_NBR=MPI_PERSON.MPI_NBR) AND (MPI.ADDR_NBR=MPI_CURRENT_ADDR.ADDR_NBR)
AND
MPI.CREATE_DT>=20070101
ORDER BY MPI.CREATE_DT
SELECT PATIENT.PAT_NBR,PATIENT.PHYS_NBR, PHYSICIAN.FIRST_NM,PHYSICIAN.LAST_NM,PHYSICIAN.DE_NBR, PHYSICIAN.SALUTATION_CD
FROM PATIENT, PHYSICIAN
WHERE PATIENT.PHYS_NBR=PHYSICIAN.PHYS_NBR
Thanks!
Lisa
View 1 Replies
View Related
Sep 13, 2007
I have a table that list Canadian provinces and American States it looks something like this:
ID | ProvState
Under ID 1-13 lists the Canadian provinces and everything over 13 lists the American states. I want to create 1 query that will list the Canadian provinces first in alphabetical order then the States in alphabetical order.
I have tried using UNION but it's not returning what I want and it does not allow me to use order by for the first statement.
SELECT * FROM SPProvince WHERE ID < 14 ORDER BY ProvState
UNION
SELECT * FROM SPProvince WHERE ID > 13 ORDER BY ProvState
Anyone have any suggestions to this problem?
View 4 Replies
View Related
May 13, 2008
Arnie and All others. Thanks for your help.
The previous case became lenghty and then just mixed up a lot.
To make it easy I have created two temp tables and wrote to test select statement .
You will notice that I tired two select statement but they are giving different set of result however the 2nd Select statement not giving the result as should be looking at the following requirement.
--Selected record must RaType='b'
-- PlanID='H321'
-- Gender='0'
--
--And not to include in select if any one of these meets:
-- Hosp='1' in other words it has to be 0
-- ESRD='1' or Rafctor Type in ('g','f') in other words ESRD should be 0 and rafctorType in ('h','i')
-- Dod is not null in other words Dod has to be null
--
--copy from here
GO
Create table #MyTable
( RowID int IDENTITY,
RD varchar(10),
RAType varchar(5),
History varchar(15)
)
INSERT INTO #MyTable VALUES ( '1', 'A', '1111' )
INSERT INTO #MyTable VALUES ( '2', 'S','2222' )
INSERT INTO #MyTable VALUES ( '3', 'D', '2345')
INSERT INTO #MyTable VALUES ( '4', 'I2','1234' )
INSERT INTO #MyTable VALUES ( '5', 'C','3333' )
INSERT INTO #MyTable VALUES ( '1', 'B','4444' )
INSERT INTO #MyTable VALUES ( '2', 'X','5555' )
INSERT INTO #MyTable VALUES ( '1', 'D' ,'66666')
GO
Go
Create Table #MYTable2
(
RowID int IDentity,
RD varchar(10),
RaType varchar(5),
History varchar(15),
PlanID varchar(6),
Hosp varchar(2),
ESRD varchar(2),
RafctorType varchar(3),
gender varchar(5),
dod varchar (5) NULL
)
INSERT INTO #MyTable2 VALUES ( '1', 'A', '1111', 'H321', '0','0', 'g', '0' ,NULL)
INSERT INTO #MyTable2 VALUES ( '2', 'b', '2222', 'H321', '0','0', 'e', '0',NULL )
INSERT INTO #MyTable2 VALUES ( '2', 'b', '3333', 'H321', '0','0', 'f', '0',NULL )
INSERT INTO #MyTable2 VALUES ( '2', 'b', '4444', 'H321', '0', '0','d', '0',NULL )
Go
Select #MYtable2.History from #MYTable2
INNER JOIN #mytable on #myTable2.History=#mytable.history
Where #MyTable2.RaType='b' And PlanID='H321' And Gender='0' And Hosp<>'1' And ESRD<>'1' AND RafctorType Not in ('g','f') AND Dod is NULL
Select #Mytable2.History from #MyTable2
INNER JOIN #mytable on #mytable2.History=#mytable.history
where #mytable2.Ratype='b' AND PlanID='H321' AND Gender='0' AND(Hosp<>'1') or ((ESRD<>'1') or (RafctorType Not in ('g','f')) OR DOD is NULL)
Go
drop table #Mytable2
Drop table #MYtable
View 5 Replies
View Related
