Adding Value In A Query

Aug 25, 2007

I am trying to insert a value numeric + 1 in to db table but i get error when i do this

this is the code

Const SQL As String = "INSERT INTO [PageHits] ([DefaultPage]) VALUES (@defaultP)"Dim myCommand As New Data.SqlClient.SqlCommand(SQL, myConnection)myCommand.Parameters.AddWithValue("@DefaultP" + "1", DefaultP.Text.Trim())

myConnection.Open()

myCommand.ExecuteNonQuery()

myConnection.Close()

The Error:  
Must declare the variable '@defaultP'.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Must declare the variable '@defaultP'.
 

 

View 1 Replies


ADVERTISEMENT

SQL Server 2012 :: Adding Count To Query Without Duplicating Original Select Query

Aug 5, 2014

I have the following code.

SELECT _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,

[code]...

However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.

View 2 Replies View Related

Transact SQL :: Adding Results Of Query To Another Query Via Dynamically Added Columns

Jul 30, 2015

For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?

I want my output to be

CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc

Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.

I want to do it using SELECT. Is it possible?

View 13 Replies View Related

Adding In A Query

Sep 14, 2007

i want to add vales in a query
this is my scenario (how can i add the price column values and display as one record) ex. 222  17
table = table1
tep1  price (column)
222     5
222     2
222     10
 

View 2 Replies View Related

Adding Numbers While Doing Query

Mar 28, 1999

I used this with an access DB and would like to re-write it to work with
sql.

sql = "SELECT funds.itemdisc, Sum(IIf(funds.status='Credit'"
sql = sql & ",CDbl(funds.fundamount),0,)) AS income, Sum(IIf funds.status='Debt'"

sql = sql & ",-1*CDbl(funds.fundamount),0)) AS outgo"
sql = sql & " FROM funds "
sql = sql & "GROUP BY funds.itemdisc"

What I am doing here is collecting the columns and using status as
credit or debt, if its debt I am subtracting from outgo if it is credit
I am adding to credit. This also will only select records so that
there are not any double listings. Here is an example.

DB
desc status fundamount
bill debt 10.00
bill debt 15.00
in credit 10.00
bills debt 5.00
in credit 5.00
paper debt 5.00


When I do the query I should have this

desc fundamount
bill -25.00
bills - 5.00
paper - 5.00
in 15.00

Something like that...

What do you think?
Thanks in advance.

View 1 Replies View Related

Adding Table To Query

Apr 16, 2008

I am new to SQL and have an issue I can not figure out. I have a query with several JOINS, but know my boss is asking for me to add an entire table to my query to view results. Is it possible to add a table to a query with joins? I have tried subquery but I get an EXIST error message. The only way I was able to get the information was join the table and enter every single column to my query. I hope there is an easier and more efficent way of doing this.

Thanks,

View 2 Replies View Related

Adding A SUM Column To Query

Oct 29, 2013

I have a pretty simple SQL query that has two columns that contains only numbers. Is there a way i can add an extra column that simply subtracts the number in one column from number in the other column.I query the data tables rather than have a live link so the .csv file gets overwritten every time i run the query.

View 4 Replies View Related

Help On Adding Another Filter Statement On The Query

May 27, 2008

Hi everybody have this query that list all the duplicate records meaning the occurence of pubinfid in more than one row. This gets me fine. Iwant thought to insert a where clause that further filters the result where pubid between 30 and 33. I tried placing it after the FROM CLAUSE BUT DOESN'T GIVE me what I want it still includes records other than the between clause. Also I tried placing it after the HAVING clause but it includes records which has only one count.. Where could I place the where clause or is ther an alternative for this.



SELECT a.pubinfid, a.pubid, a.pubcount
FROM pubssubscribe AS a INNER JOIN
(SELECT pubinfid
FROM pubssubscribe
GROUP BY pubinfid
HAVING (COUNT(*) > 1)) AS b ON a.pubinfid = b.pubinfid



thanks

View 8 Replies View Related

Adding Query Results To A Tbale

Jan 13, 2004

hello everybody,

I have a small problem and was hipeing someone could help. Want to run a query, where I sellect results from two tables. Once this is done I want to takes those results and update a table that is already created with those results. How would I do this? Thw eonly way I know how to do this is by writing this:

select CompanyName, ContactName, Address, City, Region, ProductName, UnitPrice INTO TABLE2
From Suppliers, Products

But the problem with this is that I can only input the query results into a new table that I would name above. I can't insert it into a table that has already been created.

This is the erroe that I receive:
There is already an object named 'TABLE2' in the database.

Please Help!

thanks

View 8 Replies View Related

Adding Row Number To A Query Result

May 22, 2008

Is there any way I can add record number to my query result?

Exp:

Name Add tel
A 1 st 123456
B H ave. 987456


I need:
No Name Add tel
1 A 1 st 123456
2 B H ave. 987456

Thanks

View 9 Replies View Related

Automatically Adding Numbers To Query

Jun 4, 2008

Hello everyone!

I have a question for you. I have a product database with a lookup that pulls by product number. All my product numbers are made up the same way. IE. N59840, N00951, N00951. ect.

I have a stored procedure that looks up by that product number with a "LIKE" statement that looks like this.

WHERE ([Product#] LIKE '%' + @PRODUCTNUM + '%')

Which has this problem if someone types in "852" it returns

N00852
N05852
N98852

Is there anyway that I can have SQL put in zeros to fill up the 5 number spots so "852" brings up "00852" or "5852" brings up "05852"

I hope this makes sense.

Thank you for your help!!!

View 4 Replies View Related

Adding A Count Clause To A Query

Jun 5, 2008

I have the following query where I select records from Active_Activities_temp which do not match on cde_actv in the table ACTIVITY_CORE_LISTING:
SELECT Active_Activities_temp.*
FROM Active_Activities_temp LEFT JOIN
ACTIVITY_CORE_LISTING ON
Active_Activities_temp.cde_actv=ACTIVITY_CORE_LISTING.cde_actv
WHEREACTIVITY_CORE_LISTING.cde_actv is null
ORDER BY prtcpnt_id
So for example, if a participant has a cde_actv=38 (which doesn't exist in ACTIVITY_CORE_LISTING), that record would appear as the query is currently.

The issue is that participants can have multiple records in Active_Activities_temp and if a participant has a record that does exist in ACTIVITY_CORE_LISTING, no records for that participant should appear in this query result. For example, if a participant has two records in Active_Activities_temp, one with a cde_actv 38 (which does not appear in ACTIVITY_CORE_LISTING) and one with a cde_actv 33 (which does appear in ACTIVITY_CORE_LISTING), no records for that participant should appear in the result. Currently the record with cde_actv=38 does appear.

What code can I implement to do what I need to do? Thanks so much.

View 5 Replies View Related

Adding Columns To A Query From Results

Apr 14, 2014

I have a table called approval_levels in which I have 2 columns:

|LEVEL_ID |NAME |
|1 |app_level_1 |
|2 |app_level_2 |
|3 |app_level_3 |

I have a second table called requests, in which I have 3 columns:

|REQUEST_ID |PRODUCT_NAME |MANUFACTURER |
|1 |wd-40 |Acme |
|2 |Windex |Acme |
|3 |Propane |Acme |

I have a third table which links the two called request_approvals which has 2 columns: REQUEST_ID, LEVEL_ID. When a request is approved for a specific level, I insert a value into this table. So, for example, lets say request 1 has been approved for all 3 levels, request 2 has been approved for only level 1, and request 3 has been approved for level 3, the table would show something like this.

|REQUEST_ID |LEVEL_ID |
|1 |1 |
|1 |2 |
|1 |3 |
|2 |1 |
|3 |3 |

Ok, so here the challenge: I need to show in a report all the requests, create a columns for each level and show whether or not that request is approved for that level. The end result has to be something like this:

|REQUEST_ID |PRODUCT_NAME |MANUFACTURER |app_level_1 |app_level_2 |app_level_3 |
|1 |wd-40 |Acme |X |X |X |
|2 |Windex |Acme |X | | |
|3 |Propane |Acme | | |X |

Keep in mind that if another value is added to the approval_levels table (ie app_level_4), I need to add another column to the table call app_level_4 dynamically.Now, how in the world do I do something like this?

View 1 Replies View Related

Adding Sum Function To Query To Sum 12 Records

Sep 23, 2014

I'm trying to add a sum function to my query to sum the 12 records I get back in order to see if my total charges match the amounts within a different database table. Unfortunately, now when I run the query using the sum function, I am getting very large numbers back for the charge amt and I can't figure out why. Here is the query I've written:

IF OBJECT_ID('TEMPDB..#TMP1C') IS NOT NULL DROP TABLE #TMP1C
IF OBJECT_ID('TEMPDB..#TMP2C') IS NOT NULL DROP TABLE #TMP2C

select prin_sbb, sub_acct_no_sbb, res_name_sbb, ext_stat_sbb, cur_bal_sbb, VIP_FLG_SBB
INTO #TMP1C
from Vantage.dbo.SBB_BASE (NOLOCK)
where PRIN_SBB in (6000,7500)

[Code] .....

Not entirely sure what I am doing wrong, or why the numbers are coming back so high.

View 1 Replies View Related

Adding A Column Based On A Query

Mar 27, 2015

I have 2 independent queries which run just fine, the 2nd one returns a dollar value. I need this dollar value as a column in my first query so that I can return it in a reporting program. The 2 queries are pasted below.

SELECT dbo.vw_tphillip_Customer_Inv.InvoiceNumber AS "InvoiceNumber",
dbo.vw_tphillip_Customer_Inv.TransactionType AS "TransactionType",
dbo.vw_tphillip_Customer_Inv.Date AS "Date",
dbo.vw_tphillip_Customer_Inv.DueDate AS "DueDate",
dbo.vw_tphillip_Customer_Inv.CustomerName AS "CustomerName",

[Code] ....

View 6 Replies View Related

Paging Query Bugs Out When Adding A Where Clause

Oct 10, 2007

I am getting incorrect results from my paging query, where the same results are being returned multiple times. Here are two queries I have found to bring the same results:select top 20 * from lookupdocuments_dbv where catname_cst='MyCategoryName' and (docid_cin not in (select top 620 docid_cin from lookupdocuments_dbv where catname_cst='MyCategoryName'))select top 20 * from lookupdocuments_dbv where catname_cst='MyCategoryName' and (docid_cin not in (select top 640 docid_cin from lookupdocuments_dbv where catname_cst='MyCategoryName'))When I remove the catname_cst where clause it brings back results properly (i.e. records 622-642 and 643-663). What is wrong with my where clause that is causing identical data to be returned? 

View 8 Replies View Related

Problem Adding More Then One Table In The Query Builder

Jun 6, 2006

Hi
i have a database with several tables and i want to create a Insert query. and i do this by adding the tables to a dataset and create the relations between them and now i click on the table i want to create a query for and select Add Query. and then the TableAdapter Query configuration Wizard pops-up. but the problem is that in the query builder i can add ONLY ONE table, why? i need to add more than one table in order to make my query.
This is how this problem accured: At first i had to two databases, the first database i created myself and there i had all my tables and the second database was the one that contained all the membership info(this database is the one that visual studio creates automaticly for you when u are using the membership thing ASPNETDB.MDF). so i decided to only have one database and i re-created all my tables in the aspnetdb.mfd database, that is where this problem accured, that i can add only one table in the query builder.
appreciate any help :) tnx

View 2 Replies View Related

Adding Columns Of Data In An Aggregate Query

Mar 14, 2001

Ok so I need to write a query that finds the lowest grade out of a group of students (by a class number), and identifies it by a student id #.

Here's what I'm trying:

select min(gr.grade), gr.stu_id
from dbo.class cl, dbo.grade gr
where (cl.class = 2) and (abs(cl.stu_id) = abs(gr.stu_id))
group by gr.stu_id

but unfortunetly it returns the lowest grade for each student in class 2, and not the single lowest grade in class 2 and the stu_id (student ID).

I'm new to SQL and could really use the help.

Thanks

View 1 Replies View Related

Adding Spaces To Query Output -- Really Stuck!!

May 10, 2001

I want to add 3 spaces at the end of each row for the result of a query.

The initial query being output to a text file (USING DTS) is:

select colA, colB =
CONVERT( CHAR(15), TableX.ColB )
from TableX
------------------------------------------------------------------------

BUT WHEN I EXPORT THE FOLLOWING (USING DTS), I GET THE SAME RESULTS

select colA, colB =
CONVERT( CHAR(18), TableX.ColB )+char(32)+char(32)+char(32) -- ' '
from TableX

Replacing the char(32) with ' ' (3 spaces in quotes) doesn't help.

Any ideas of how to append the 3 spaces after each record when exporting to
a text file using DTS??

Thanks

View 2 Replies View Related

Adding Autonumber Column To A Query Result

Apr 23, 2004

I have a query that returns a list of results from the database and insert the results into a datatable. Is there any way to add a column to the query result that has autonumbers in it. for example the first record would have 1 the next 2, the next 3, and so on?

View 4 Replies View Related

Adding Case Statement To Existing Query

Apr 3, 2008

I was asked to add an additional column to an existing query. I'm using Microsoft Query with a MS SQL 2000 server, and don't have much knowledge of SQL in general. Here's the existing query:



SELECT A.COMPANYCODE,
A.INVOICENUMBER,
A.LINENUMBER,
A.SONUMBER,
A.CUSTOMERCODE,
A.SHIPPERNUMBER,
A.INVOICEDATE,
A.ITEMCODE,
A.QUANTITYINVOICED,
A.UNITPRICE AS 'InvPrice',
A.QUANTITYINVOICED * A.UNITPRICE AS 'ExtInvPrice',
INVENTORY.UNITPRICE AS 'StdPrice',
INVENTORY.STANDARDCOST,
A.QUANTITYINVOICED * INVENTORY.STANDARDCOST AS 'ExtCost',
(A.QUANTITYINVOICED * A.UNITPRICE) - (A.QUANTITYINVOICED * INVENTORY.STANDARDCOST) AS 'GM$',
(INVENTORY.UNITPRICE - A.UNITPRICE) * -1 AS 'PriceListDiff'
FROM ABW.DBO.SALESANALYSISHISTORY A,
ABW.DBO.INVENTORY INVENTORY
WHERE INVENTORY.COMPANYCODE = A.COMPANYCODE
AND INVENTORY.ITEMCODE = A.ITEMCODE
AND ((A.COMPANYCODE = 'csp')
AND (A.QUANTITYINVOICED <>$ 0)
AND (A.INVOICEDATE BETWEEN '03/1/08' AND '03/31/08'))
ORDER BY A.INVOICEDATE,
A.ITEMCODE



They want a column added to the current query where if A.Unitprice is greater than or equal to Inventory.UnitPrice then populate the column with A.QuantityInvoiced*A.UnitPrice. I posted on another forum, and the advice I got was to add this:


SELECT NewColumn = CASE
WHEN A.Unitprice >= Inventory.UnitPrice THEN A.QuantityInvoiced * A.Unitprice
ELSE 'null'
END,
FROM ABW.DBO.SALESANALYSISHISTORY A,
ABW.DBO.INVENTORY INVENTORY

I'm not sure how to integrate it to the current query, so I've tried running it by itself, and I get the error: Didn't expect 'A' after the SELECT column list.

Any help would be greatly appreciated to modify the current query to display the new column.

View 2 Replies View Related

Query Design For Adding / Copying And Updating

Jul 8, 2013

I have a rather complex query (to me at least) that I need to create but I am unsure of where to start. The query requires me to copy existing data into a new row (which will then create a new ID) as well as update all existing records with the newly created id. More specifically, I need to separate the data associated with LocationID 219 from it's parent, CompanyID 992.

Ideally I want to copy the data associated with LocationId 219 and then make a new CompanyId with the copied data (which will also create a new LocationID). Since this new record is no longer going to be associated with CompanyID 992 I will want to remove/delete/drop it from that record.

Finally, and perhaps most difficult of all, I need to update all tables that reference the old ID's together (992 / 219) to reflect the newly created Company ID and Location ID.

View 3 Replies View Related

Transact SQL :: Adding Percentage To A Group Within A Query

May 6, 2015

Below is my SQl which just counts the number of appointments and grouped by clinic. This is great but what I'd like to add is the percentage within each clinic.

For example Clinic BRESRAD1 has a total of 61 appointments, of which 75.41% are Normal Appointments and 24.59% are Diagnostic, Ideally I would like the percentage in the next column.

BRESRAD1 Normal Appointment 46
BRESRAD1 Diagnostic Appointment 15
BRESRAD2 Normal Appointment 17
BRESRAD2 Diagnostic Appointment 12
BRESRAD3 Normal Appointment 34
BRESRAD3 Diagnostic Appointment 43

My SQL is as follows:

SELECT ClinicCode,
CASE WHEN [ApptTypeDesc] LIKE '%Diag%' THEN 'Diagnostic Appointment' ELSE 'Normal Appointment' END AS [Diagnostic Appt],
COUNT(OPAppointmentID) AS CountOfOPAppointmentID
FROM dbo.OP_APPOINTMENT
WHERE (AttendStatusNatCode IN ('5', '6'))
AND (ApptFinYr = '2014/15')
GROUP BY ClinicCode,
CASE WHEN [ApptTypeDesc] LIKE '%Diag%' THEN 'Diagnostic Appointment' ELSE 'Normal Appointment' END
ORDER BY ClinicCode

View 13 Replies View Related

Dataset Becomes Empty After Adding Query Parameter

Mar 13, 2008



Hi all,

hope anyone can help me on this one.

I have a parameterized report based on a MDX query (so no drag and drop of filters and fields).
Now i have to ad a parameter. So i open the query parameter dialog box and i ad my parameter.
when i click ok , my dataset becomes empty , resulting in an error on my report saying that the fields on the report cannot by found and should be in the scope of the dataset.

Has anyone experienced this problem before, or am i forgetting something here?

thanks in advance

Steven J

View 1 Replies View Related

Adding Default Value To An Already Created Table Using Query Analyzer

Aug 9, 2004

Hello,

How can I give default value to a field in a table which is already created, i.e. there is a table test and it have field test1 which is int(4). Now, I want to give a default value 0 to this field. As I am not able to access Enterprise Manager, I want to do it using Query Analyzer. How can I do this using Query Analyzer?

Thanks in advance,
Uday.

View 6 Replies View Related

Advance Joins - Adding Another Field To Existing Query

Jan 16, 2015

I have this already existing query and want to add another field to it but its failing. Below is the current query

REPLACE(CONVERT(CHAR(10), A.BATDAT, 1), '/', '') + RIGHT('00000' + CONVERT(VARCHAR, A.BATSEQ), 5
RIGHT('000' + CONVERT(VARCHAR, A.SEQNBR), 3) AS CLIENT_TRACK_NO

I want to add a field from a table name APTTL and the filed name is DTYYY char (2)

View 2 Replies View Related

SQL Server 2014 :: Error In Adding Condition In Query

Apr 4, 2015

Following is a working code

declare @dte as datetime='2015-04-01'
declare @StDt as Datetime = DATEADD(mm,DATEDIFF(mm,0,@dte), 0)
declare @EnDt as datetime = DATEADD( DD, -1, DATEADD(mm,DATEDIFF(mm,0,@dte) + 1, 0));
DECLARE @query AS NVARCHAR(MAX);
create table #bus_master(bus_id int,bus_name varchar(50),uname varchar(50))
insert into #bus_master values(100,'A','lekshmi')

[Code] ...

I am getting the output correctly. My requirement is i want to write a condition here
JOIN busdetails b ON m.bus_id = b.bus_id

I want to write this statement as
JOIN busdetails b ON m.bus_id = b.bus_id and m.uname='lekshmi'

When I tried this code I am getting error.

View 3 Replies View Related

Need Help With Adding A Duplicate Record Count Column To Query

Jul 23, 2005

I am attempting to create a simple recordset that would return thenumber of duplicates that exist in a table with a single column. Forexample if I had a table like the following:ID Reference Amount1 123456 1.002 123456 2.003 123 1.00I would like to get the following result:ID Reference Amount RecCount1 123456 1.00 22 123456 2.00 23 123 1.00 1Please help!Thanks,Shawn

View 2 Replies View Related

Adding Staggered Running Total And Average To Query

Jul 20, 2005

Hi,I am trying to add a staggered running total and average to a queryreturning quarterly CPI data. I need to add 4 quarterly data pointstogether to calculate a moving 12-month sum (YrCPI), and then tocomplicate things, calculate a moving average of the 12-month figure(AvgYrCPI).Given the sample data:CREATE TABLE [dbo].[QtrInflation] ([Qtr] [smalldatetime] NOT NULL ,[CPI] [decimal](8, 4) NOT NULL) ON [PRIMARY]GOINSERT INTO QtrInflation (Qtr, CPI)SELECT '1960-03-01', 0.7500 UNIONSELECT '1960-06-01', 1.4800 UNIONSELECT '1960-09-01', 1.4600 UNIONSELECT '1960-12-01', 0.7200 UNIONSELECT '1961-03-01', 0.7100 UNIONSELECT '1961-06-01', 0.7100 UNIONSELECT '1961-09-01',-0.7000 UNIONSELECT '1961-12-01', 0.0000 UNIONSELECT '1962-03-01', 0.0000 UNIONSELECT '1962-06-01', 0.0000 UNIONSELECT '1962-09-01', 0.0000 UNIONSELECT '1962-12-01', 0.0000 UNIONSELECT '1963-03-01', 0.0000 UNIONSELECT '1963-06-01', 0.0000 UNIONSELECT '1963-09-01', 0.7100 UNIONSELECT '1963-12-01', 0.0000 UNIONSELECT '1964-03-01', 0.7000 UNIONSELECT '1964-06-01', 0.7000 UNIONSELECT '1964-09-01', 1.3900 UNIONSELECT '1964-12-01', 0.6800 UNIONSELECT '1965-03-01', 0.6800 UNIONSELECT '1965-06-01', 1.3500 UNIONSELECT '1965-09-01', 0.6700 UNIONSELECT '1965-12-01', 1.3200I am trying to return the following results:Qtr CPI YrCPI AvgYrCPI-------- ----- ----- --------1-Jun-60 1.481-Sep-60 1.461-Dec-60 0.721-Mar-61 0.71 4.371-Jun-61 0.71 3.601-Sep-61 -0.70 1.441-Dec-61 0.00 0.72 2.531-Mar-62 0.00 0.01 1.441-Jun-62 0.00 -0.70 0.371-Sep-62 0.00 0.00 0.011-Dec-62 0.00 0.00 -0.171-Mar-63 0.00 0.00 -0.181-Jun-63 0.00 0.00 0.001-Sep-63 0.71 0.71 0.181-Dec-63 0.00 0.71 0.361-Mar-64 0.70 1.41 0.711-Jun-64 0.70 2.11 1.241-Sep-64 1.39 2.79 1.761-Dec-64 0.68 3.47 2.451-Mar-65 0.68 3.45 2.961-Jun-65 1.35 4.10 3.451-Sep-65 0.67 3.38 3.601-Dec-65 1.32 4.02 3.74Note, 4 data points are required to calculate a moving sum of CPI(YrCPI) and 4 calculate YrCPI figures are required calculate theannual average of YrCPI (AvgYrCPI), giving a staggered effect to thefirst 7 resultsThis sad effort is about as far as I've got:SELECT I.Qtr, I.CPI, SUM(S.CPI) AS YrCPIFROM QtrInflation IJOIN (SELECT TOP 4 Qtr, CPIFROM QtrInflation) SON S.Qtr <= I.QtrGROUP BY I.Qtr, I.CPIORDER BY I.Qtr ASCCan anyone suggest how do achieve this result without having to resortto cursors?Thanks,Stephen

View 5 Replies View Related

SQL Server 2012 :: Adding 2 COUNT Statements Results In Heavy Query

Jan 28, 2014

These separate COUNT queries are very fast:

SELECT COUNT(id) as viewcount from location_views WHERE createdate>DATEADD(dd,-30,getdate()) AND objectid=357
SELECT COUNT(id)*2 as clickcount FROM extlinks WHERE createdate>DATEADD(dd,-30,getdate()) AND objectid=357

But I want to add the COUNT statements, so this is what I did:

select COUNT(vws.id)+COUNT(lnks.id)*2 AS totalcount
FROM location_views vws,extlinks lnks
WHERE (vws.createdate>DATEADD(dd,-30,getdate()) AND vws.objectid=357)
OR
(lnks.createdate>DATEADD(dd,-30,getdate()) AND lnks.objectid=357)

Turns out the query becomes immensely slow. There must be something I'm doing wrong here which results in such bad performance, but what is it?

View 7 Replies View Related

Transact SQL :: Adding Case When Statement With Group By Query Doesn't Aggregate Records

Aug 28, 2015

I have a a Group By query which is working fine aggregating records by city.  Now I have a requirement to focus on one city and then group the other cities to 'Other'.  Here is the query which works:

Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars' 
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]

Here is the result:

St. Louis 1000
Kansas City 800
Columbia 700
Jefferson City 650
Joplin 300

When I add this Case When statement to roll up the city information it changes the name of the city to 'Other Missouri City' however it does not aggregate all Cities with the value 'Other Missouri City':

Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars' 
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]

Here is the result:

St. Louis 1000
Other Missouri City 800
Other Missouri City 700
Other Missouri City 650
Other Missouri City 300

What I would like to see is a result like:

St. Louis 1000
Other Missouri City 2450

View 5 Replies View Related

Reporting Services :: Adding All Columns To Table Without Adding One By One

Sep 3, 2015

Is there any way or option to get the all columns of dataset added to table when we add a table in data region. It will take lot of time to add one by one and also there are chances to add one column ore than once.

View 7 Replies View Related

Query Based Off Primary Key Of Parent Table - Adding Child Table

Jan 28, 2012

I need to add a child table that will tell us who the participants counselor is, what I did was I did a Make Table query based off the primary key of the Parent table and made that the link (foreign key) for the People_tbl and the Counselor_tbl, so if the counselor changes then the user adds the record to the counselor tbl and then puts in the Effective date. The problem is that when I run a report it doesn't show the present counselor always shows the old counselor?

Code:
SELECT Student_ind.StudentFirstName, Student_ind.StudentLastName, Student_ind.[Student ID], People_tbl.[Family ID], People_tbl.FirstName,
People_tbl.LastName, People_tbl.[Parent ID]
FROM People_tbl RIGHT OUTER JOIN
Student_ind ON People_tbl.[Family ID] = Student_ind.[Family ID]
WHERE (People_tbl.LastName = @Enter_LastName) AND (People_tbl.FirstName = @Enter_FirstName)

View 5 Replies View Related







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