Is there a way to do this in the current query I have?
With labtestcheck (labtestcode)
as
(
selecta.labtestcode
fromEMR_temp_labtest_configupdate a
WhereNot Exists (Select * From lab_test_add_conf b where b.labtest_key = a.labtestcode)
)
selectt.labtestcode,b.zsegcode,b.valuecode,b.valuedesc,b.valuetype,b.units,b.tablename,b.fieldname
fromEMR_temp_labtest_configupdate a
inner joinemr_zseg_code_descriptions b on a.zsegcode = b.zsegcode
inner joinlabtestcheck t on t.labtestcode = a.labtestcode
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.
SELECT COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END) AS [New Visitors], COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END) AS [Returning Visitors] FROM content_hits_tbl WHERE (hit_date BETWEEN DATEADD(mm, - 1, GETDATE()) AND GETDATE())
=======================
How do I add up both COUNT/CASE columns? Would it be: SUM([New Visitors] + [Returning Visitors]) AS Total
I tried this and it doesn't work. I get invalid column names error for both.
I have even tried: SUM([COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END)] + [COUNT(DISTINCT CASE WHEN visit_type = 0 THEN visitor_id END)]) AS Total
You would think that there would be some gui functionality in VS08 that would do this...
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
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?
If I just use a simple select statement, I find that I have 8286 records within a specified date range.
If I use the select statement to pull records that were created from 5pm and later and then add it to another select statement with records created before 5pm, I get a different count: 7521 + 756 = 8277
Is there something I am doing incorrectly in the following sql?
DECLARE @startdate date = '03-06-2015' DECLARE @enddate date = '10-31-2015' DECLARE @afterTime time = '17:00' SELECT General_Count = (SELECT COUNT(*) as General FROM Unidata.CrumsTicket ct
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.
HiI am new to SQL and am having a problem. I need to fix my query to do the following...2) get a total of the number of rows returned. DECLARE @StartDate varchar(12)DECLARE @EndDate varchar(12)DECLARE @Region varchar(20) SET @StartDate = '01/01/2002'SET @EndDate = '12/31/2008'SET @Region = 'Central' SELECTA.createdon,A.casetypecodename,A.subjectidname,A.title,A.accountid,A.customerid,A.customeridname,B.new_Region,B.new_RegionName FROM dbo.FilteredIncident AINNER JOIN dbo.FilteredAccount B ON A.customerid = B.accountid WHERE (A.createdon >=@StartDate AND A.createdon <= @EndDate)AND (B.new_RegionName = @Region)AND (A.casetypecode = 2)
With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("", MyConnection) Dim i As Integer Dim fBeginTransCalled As Boolean = False 'messagetype 1 =internal messages Try ' ' Start transaction ' MyConnection.Open() cmd.CommandText = "BEGIN TRANSACTION" cmd.ExecuteNonQuery() fBeginTransCalled = True Dim obj As Object For i = 0 To MessageIDs.Count - 1 bSuccess = False 'delete userid-message reference cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID" cmd.Parameters.Add(New SqlParameter("@UserID", UserID)) cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() 'then delete the message itself if no other user has a reference cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1" cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString)) obj = cmd.ExecuteScalar If ((Not (obj) Is Nothing) _ AndAlso ((TypeOf (obj) Is Integer) _ AndAlso (CType(obj, Integer) > 0))) Then 'more references exist so do not delete message Else 'this is the only reference to the message so delete it permanently cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2" cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() End If Next i ' ' End transaction ' cmd.CommandText = "COMMIT TRANSACTION" cmd.ExecuteNonQuery() bSuccess = True fBeginTransCalled = False Catch ex As Exception 'LOG ERROR GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message) Finally If fBeginTransCalled Then Try cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection) cmd.ExecuteNonQuery() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
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.
 set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales") set saleset(buyerset) set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4} set finalset as exists(salest,custdimensionfilter,"Sales") Set ProdIP as dimproduct.dimproduct.prod1 set Othersset as (cyears,ProdIP) (exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count
I use SQL 2000 I have a Column named Bool , the value in this Column is 0�0�1�1�1 I no I can use Count() to count this column ,the result would be "5" but what I need is "2" and "3" and then I will show "2" and "3" in my DataGrid as the True is 2 and False is 3 the Query will have some limited by a Where Query.. but first i need to know .. how to have 2 result count could it be done by Count()? please help. thank you very much
SQL 2000I have a table with 5,100,000 rows.The table has three indices.The PK is a clustered index and has 5,000,000 rows - no otherconstraints.The second index has a unique constraint and has 4,950,000 rows.The third index has no constraints and has 4,950,000 rows.Why the row count difference ?Thanks,Me.
What is the syntax for adding a column where you are adding a year to a date in a date format? For example adding a column displaying a year after the participation date in date format?
The following query returns a value of 0 for the unit percent when I do a count/subquery count. Is there a way to get the percent count using a subquery? Another section of the query using the sum() works.
Here is a test code snippet:
--Test Count/Count subquery
declare @Date datetime
set @date = '8/15/2007'
select -- count returns unit data Count(substring(m.PTNumber,3,3)) as PTCnt, -- count returns total for all units
(select Count(substring(m1.PTNumber,3,3))
from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@Date) and @Date) as TotalCnt, -- attempting to calculate the percent by PTCnt/TotalCnt returns 0 (Count(substring(m.PTNumber,3,3)) /
(select Count(substring(m1.PTNumber,3,3))
from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@Date) and @Date)) as AUPct -- main select
from tblVGD1_Master m
left join tblVGD1_ClassIII v on m.SlotNum_ID = v.SlotNum_ID
Where left(m.PTNumber,2) = 'PT' and m.Denom_ID <> 9
and v.Act = 1 and m.Active = 1 and v.MnyPlyd <> 0
and not (v.MnyPlyd = v.MnyWon and v.ActWin = 0)
and v.[Date] between DateAdd(dd,-90,@Date) and @Date
I would like to AUTOMATICALLY count the event for the month BEFORE today
and
count the events remaining in the month (including those for today).
I can count the events remaining in the month manually with this query (today being March 20):
SELECT Count(EventID) AS [Left for Month], FROM RECalendar WHERE (EventTimeBegin >= DATEADD(DAY, 1, (CONVERT(char(10), GETDATE(), 101))) AND EventTimeBegin < DATEADD(DAY, 12, (CONVERT(char(10), GETDATE(), 101))))
Could anyone provide me with the correct syntax to count the events for the current month before today
and
to count the events remaining in the month, including today.
hey i have a very simple form here: www.syscpupower.com I would like to have the information from the form saved to a sql db with a time stamp. here is the code i have for the page below:"C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>
I have a details view with several parameters set up in my asp.net 2.0 code, I want to add a parameter before the sql parameter is executed. I need to use the find control of the details view because I am using items/edit item templates in my details view control. I tried this(see below) as well as the detailsview item command event args to no avail. It doesn't see the other parameters that have already been declared in my asp.net code. I don't want to have to declare all my varibles that are already in my asp.net code. I just want to add another parameter. Sub InsertNew(ByVal sender As Object, ByVal e As DetailsViewInsertEventArgs) _ Handles dvEvents.ItemInserting Dim dvr As DetailsViewRow For Each dvr In dvEvents.Rows Dim CatIDup As Integer = CType(dvr.FindControl("ddlCat"), DropDownList).SelectedValue sdsevents.InsertParameters.Add("evCatID", CatIDup)sdsevents.Insert()
Looking for any help someone can offer on adding an image to SQL.Working on what will hopefully be quite a big site and the user will be asked for some details, one of which will be an image (jpg, gif or bmp).Rather than add the image to a folder on the server, a folder which could end up being quite large, I was hoping someone could give me some help, suggestions or even point me towards a tutorial on how to store the image in an SQL database.Any helps appreciated, thanks :)
Hi Everyone, I have a page with a textbox and a dropdown list. The user will enter a company name in the text box and select a number from 1 - 20 (number of delegates for that company) in the dropdown list. I've got the text box and dropdown writing to tblCompany but I would also like it to write to tblUsers at the same time. The reason for this is that i need it to set up the number of users that have been selected in the dropdown list. Here is the codebehind file:Imports System.Data.SqlClient Imports System.Web.Configuration
Partial Class cms_Management_Company_NewCompany Inherits System.Web.UI.Page
Dim companyName As String Dim companyActive As Boolean Dim companyArchived As Boolean Dim companyDelegates As Integer
Dim userForeName As String Dim userSurname As String Dim userEmail As String Dim userUsername As String Dim userPassword As String Dim userActive As Boolean Dim userTypeID As Integer Dim companyID As Integer
Dim i As Integer Dim NoLoops As Integer
Protected Sub btnSave_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btnSave.Click Dim conString As String = WebConfigurationManager.ConnectionStrings("General").ConnectionString
Dim con As New SqlConnection(conString) Dim cmd As New SqlCommand("INSERT INTO tblCompany (CompanyName, CompanyActive, CompanyArchived, CompanyDelegates) VALUES (@CompanyName, @CompanyActive, @CompanyArchived, @CompanyDelegates)", con)
Using con con.Open() cmd.ExecuteNonQuery() con.Close() End Using
Dim con2 As New SqlConnection(conString) Dim cmd2 As New SqlCommand("INSERT INTO tblUsers (UserForeName, UserSurname, UserEmail, UserUsername, UserPassword, UserActive, UserTypeID, CompanyID) VALUES (@UserForeName, @UserSurname, @UserEmail, @UserUsername, @UserPassword, @UserActive, @UserTypeID, @CompanyID)", con2)
cmd2.Parameters.AddWithValue("@UserForeName", userForeName) cmd2.Parameters.Item("@UserForeName").Value = "First Name - Delegate 1"
Using con2 con2.Open() For i = 1 To NoLoops cmd2.ExecuteNonQuery() Next i con2.Close() End Using
Response.Redirect("~/cms/Management/Company/Company.aspx") End Sub
End ClassThe other thing I am not sure of is getting the ID of the new company and assiging it to the delegates in tblUsers (to associate them with the new company)I hope this makes sense.Thank you very much guys.Scott.
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'.
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
Hi all, I have a database setup with a few rows and i would like to allow a user to create a new row of data. After some googling, I've deduced that I need to somehow use an onclick command to perform an insert command. here is my code as of right now: protected void ItemAdd_Click(object sender, EventArgs e) {int newnum = 4;int POnum = newnum ++;
whenever I run this, I receive the following error and am not sure how to correct it:
System.Data.SqlClient.SqlException: Must declare the scalar variable "@Item".
If someone could please explain to me the process here of what is going ion it would be greatly appreciated. My understanding is the @item is just a variable if you will so i'm not sure why its stating i need to declare it.
Hi Guys, I know this is a little off the subject of ASP.net but I am using some code behind in C#. I am a real novice at this and so it may appear to be a simple question but Im trying to add a number to the database from a textbox but it is having issues as the Storenum is an int and it wants a string. Can anyone help? I know I need to write .tostring somewhere but can't work out where. My code is below. Thanks in advance. =) public partial class _addstore : System.Web.UI.Page {protected string Location; protected string Telephone;protected string Prefix; protected int StoreNum;protected string myConnectionString;protected void Page_Load(object sender, EventArgs e) { Location = "default";Telephone = "default"; Prefix = "default";StoreNum = "0";myConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\inetpub\wwwroot\HOF\App_Data\HOF.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True;";
I am trying to create a sql database that will have a table with the following parameters. ID, Name, Picture. So i create the table and set the ID to an int the Name to an nvchar and the Picture to an image. My question is now how to i add a picture to this database. When i rightclick on the table and click show table data and attempt to add an image all i can type in is text. Thank you for your help
Hi,I have an application up and running. I need to add a new column to one of the tables which is currently being used - would adding a new column change or cause errors in the current application? e.g. if the table is being accessed by selecting * from table, will adding a new column cause an error? If there is any circumstance where an error would be caused by adding a new column, I will have to create an entirely new table. If I have to do this - how do I get a column in the new table to have the same values as a column in the old table? Can I create a computed column where column=oldcolumn? Thanks,Jon
