hi need help from the wonderful people here
i am working on project and convert a OLD DOS program to SQL server on the web
this is the resalt i need to show
I need to send the result of a procedure to an update statement.Basically updating the column of one table with the result of aquery in a stored procedure. It only returns one value, if it didnt Icould see why it would not work, but it only returns a count.Lets say I have a sproc like so:create proc sp_countclients@datecreated datetimeasset nocount onselect count(clientid) as countfrom clientstablewhere datecreated > @datecreatedThen, I want to update another table with that value:Declare @dc datetimeset @dc = '2003-09-30'update anothertableset ClientCount = (exec sp_countclients @dc) -- this line errorswhere id_ = @@identityOR, I could try this, but still gives me error:declare @c intset @c = exec sp_countclients @dcWhat should I do?Thanks in advance!Greg
help i need to check each column and if i don't have the value than add to a new result row
if not show the missing value
if i have in the field A B C than OK (like day1 + day2)
if i don't have the value A B C than than show it like this a|b|c (like day3)
if i don't have the value c than than show it like this -|-|c (like day4)
if i don't have the value a than than show it like this a|-|- (like day5)
if i don't have the value b than than show it like this -|b|- (like day6)
day1 day2 day3 day4 day5 day6 ------------------------------------------------------------------------- * c * a c a * a * a c * a b # b b c b * $ Q c s c # 0 0 0 * ---------------------------------------------------------------------new result row ok ok a|b|c -|-|c a|-|- -|b|- TNX
Hi , I have a table that holds CityName and TownName.This table is usually updated and I dont want the same CityName and TownName to be inserted Again. I have used Primary Key that holds thoose two fields but Primary Key is an index and I know that using check constraint works by spending less performance then indexes .
So , How can I use a check Constraint for two fields ?
in table Databackup company keepmonth ----------------- ------------------- 001 12002 12003 6005 607917 609747 6
I run this query select Max(keepmonth) as keep from Databackup why the result is 6 not 12? I think the max value should 12 , have no idea why it return 6 does my query error? thank you
Hi, I have two FormView, and I need to know if the DataSet contains specific fields to display it in the first FormView or in the Second FormView. Is it possible?
nedd help from the from the wonderful people here i am stuck in ! i need to check each column succession the chacking is for 3 columns (day1+day11+day111) (day2+day22+day222) (day3+day33+day333) (day4+day44+day444) (day5+day55+day555) and if i don't have the value than add to a new result row
if not show the missing value in the result row at END
if i have in the fields columns A B C than
ok-A ok-B ok-C
if i don't have the value A B C than than show it like this
no-A no-B no-Cif i don't have the value c than than show it like this no-C (on the left )
if i don't have the value a than than show it like this no-A (on middle)
if i don't have the value b than than show it like this no-B (on right)
IF have duplicity a or b or c show in the result row at END 2-C=(thre is tow time the C)
Now I need to display the second field which is a #-separated field as individual fields alongwith tghe other fields that are shown on execution of the query. Can this be done? Please guide me on this...
I am using aloha POS and they have the date for every check in separate fields and now I want to calculate the total time for the checks but unable to get the how of it..
The date is DOB and it's datetime but I just need to extra the getdate() from it.The open time is OPENHOUR and OPENMINThe close time is CLOSEHOUR and CLOSEMIN
so basically the open time for the check will be the DATE FROM DOB + OPENHOUR + OPENMIN
And the close time will be DATE FROM DOB + CLOSEHOUR + CLOSEMIN
I have a query to check the records of job has received after 4pm Monday to Friday and it has completed before 9am next day and also weekend it should be Friday after 4pm and before Monday 10am for particular financial year period. I have my job table which has full date but it doesn't show the date exactly Monday to Friday it shows only as 12-12-2014 like that.
So I have been trying to get mySQL query to work for a large database that I have. I have (lets say) two tables Table_One and Table_Two. Table_One has three columns: Type, Animal and TestID and Table_Two has 2 columns Test_Name and Test_ID. Example with values is below:
In Table_One all types come under one column and the values of all Types (Mammal, Fish, Bird, Reptile) come under another column (Animals). Table_One and Two can be linked by Test_ID
I am trying to create a table such as shown below:
This should be my final table. The approach I am currently using is to make multiple instances of Table_One and using joins to form this final table. So the column Bird, Reptile, Mammal and Fish all come from a different copy of Table_one.
For e.g
Select Test_Name AS 'Test_Name', Table_Bird.Animal AS 'Birds', Table_Mammal.Animal AS 'Mammal', Table_Reptile.Animal AS 'Reptile, Table_Fish.Animal AS 'Fish' From Table_One
[Code] .....
The problem with this query is it only works when all entries for Birds, Mammals, Reptiles and Fish have some value. If one field is empty as for Test_Two or Test_Three, it doesn't return that record. I used Or instead of And in the WHERE clause but that didn't work as well.
I am trying to create a stored procedure where.I need to loop through every row of the table and check if there exists a date in either of the column. If date exists in either of the column for every row, I need to update some other stuff.For the above table, it meets my requirement, so I need to update. However if the table is as below, it doesn't meet the requirement, so I don't need to update
I am trying to count the columns of a MDX query but I haven't yet found a solution. Below is the query
SELECT NON EMPTY Hierarchize({DrilldownLevel({[Customer].[Customer].[All]},,,INCLUDE_CALC_MEMBERS)}) ON COLUMNS FROM [XCUBE] WHERE ( [TIME].[Year Quarter Month].[Quarter Name].&[Quarter 1, 2015], [CustomerType].[CustomerType].&[Money], [Measures].[Total X] )
With result as
All r k l j g l 7000 1 7 8 5 4 3
The All counts the total of the [Measures].[Total X] and I want to measure the number of columns that presented at the result. Unfortunately my dimension Customers doesn't connect with time dimension...
Hello guys I have a very simple query that is to be used to verify what users have access to what servers. Here it is:
/* SQL Server & Windows NT login name, security access */ USE master go SELECT name, loginname, sysadmin, securityadmin, serveradmin, setupadmin, @@SERVERNAME FROM syslogins WHERE status != 0 AND ((sysadmin = 1) OR (securityadmin =1) OR (serveradmin =1) OR (setupadmin = 1))
I would like to know how to stop the columns that have a null or 0 value from being returned to me. I don't want to see a bunch of 0s since almost no users do have these privaleges, I'd rather have that be hidden. Do I need to create a view for this? Thank You!
I want to have a query like SELECT SettingKey, SettingValue FROM Settings WHERE SettingGroup='Comms' GROUP BY SettingSubGroup
where the result set has a single row for each distinct value of SettingGroup. I want the result set to look like: SettingSubGroup, Speed, Parity, DataBits, StopBits <--column headings ---------------- Com1, 9600, N, 8, 1 Com2, 2400, E, 7, 2
I've had a look at the PIVOT command but it seems to require an aggregate function... is there a way to simply flip from rows to columns?
I have two tables table1 and table2. I want to check a value from table1 against 4 different columns in table 2. What would be the most optimized way to do this. I came up with this SQL but it runs forever.
select * from table1 a where (a.id in (select orig_id from table2 where exctn_dt >= '01-OCT-14')) or (a.acct_intrl_id in (select benef_id from table2 where exctn_dt >= '01-OCT-14')) or (a.acct_intrl_id in (select send_id from table2 where exctn_dt >= '01-OCT-14')) or (a.acct_intrl_id in (select rcv_id from table2 where exctn_dt >= '01-OCT-14'));
In a query in SQL server 2005 I have a column LastName + ', ' + FirstName.All works fine except when a LastName or FirstName = NULL then the result is NULL.How do I get around this?ThanksA Newbie to SQL.
I have two tables that have no relation. However, both have a column which has a field of nvarchar(50) that I want to retrieve together in one operation and bind to a DropDownList in a sorted fashion. So, what I'm trying to achieve is this: 1. SELECT name FROM table1 2. SELECT name FROM table2 3. Join the two results together and order them alphabetically 4. Return the result set I'm not sure how to do this or even if it's possible. Ideally I'm hoping it can be done in a stored proc.
How can I find the result columns from code? Visual J++ Seems to be able to do that. ADO doesn't want to do it for me, neither does ODBC's SQLProcedureColumns().
I try to search my data and sort the result by importance.
I'm using a MS Access database and my data (table1) looks like this:
Code:
ID NAME TEXT 1 Apples Good red apples 2 Bananas Fine yellow bananas 3 Yellow apples Great yellow apples
I want to search the data and get a result where the column "NAME" is more important than "TEXT". My SQL looks like this:
Code:
SELECT id,name,text,1 AS searchorder FROM table1 WHERE name LIKE '*yellow*' UNION SELECT id,name,text,2 AS searchorder FROM table1 WHERE text LIKE '*yellow*' ORDER BY searchorder
The output is this:
Code:
ID NAME TEXT SEARCHORDER 3 Yellow apples Great yellow apples 1 2 Bananas Fine yellow bananas 2 3 Yellow apples Great yellow apples 2
So far so good - the order by importance works - but I do not get unique columns because of the searchorder column.
Can I fix my SQL so I get unique columns where the last line of "Yellow apples" does not appear or am I lost in space?
Ihave 2 tables...they are basically the same except for the column name in one of them because they deal with 2 different names, though..the data i want is in columns that have the same name.pretty much what i want to do...is .they also need to be distinct so i dont count duplicates...i can get them as separate tables...but i cant get them together..I need them in 1 column because of how it is sent to the C3 code page and how it reads it...the structure has already been previously set..and there are about 5 other statments that are being executed in this one stored procedure like this (also i wasnt the one who set this up).
image 1 is what is currently set up imgur: the simple image sharer top part is what is stored in tables..bottom is more of the result it basically runs this code to get the bottom
DECLARE @id INT; DECLARE @invest nvarchar(50); SET @id = '7633'; SET @invest = ''; SELECT 'a' + CONVERT(nvarchar, orderfindings.risk_rating) AS cat, COUNT(DISTINCT orderfindings.prnt_id) AS stat FROM orderheader, orderaudits, orderfindings WHERE orderheader.id = orderaudits.orderheader_id AND orderaudits.ID = orderfindings.prnt_id AND orderheader.id = @id AND orderfindings.risk_rating > 0 AND orderaudits.Investor_Name LIKE '%' + @invest + '%' GROUP BY orderfindings.risk_rating
If i want agencies instead of findings..just replace it..agencies and findings are the 2 tables..they are the pretty much identical column wise...but i want the result together..i've tried several ways..but i cant seem to get it.
image 2 - the table at the bottom is more what i'm looking for..it combines them both into 1 imgur: the simple image sharer
if an order has a finding or agency or both in it..then it gets marked as a 1 for that risk rating...if it doesnt..then 0 for that risk rating..and then sum them all up to see what i got..
I've been working with it...did this [SQL] compare2 - Pastebin.com ..got it to display 2 columns..but still not the right result...i'm getting a1 = 1...a2 = 1...so its not running through all the orders...or it needs a way to count it...i put a sum at beginning of case statement..erro because of counts...so i took counts out...
When you set the results from a query to text, very often you end up with a format like:
job_id name ---------------------------------------------------------------------- ------------------------------------------------------------------------------------------------
Is there a way to get rid of the extra space between two columns? Sometimes it's more than the width of my screen between columns
I have a case where if the Id field is a specific value, I don't want to allow null in another field, but if the Id value <> a specific value, null is ok.
In the example below, inserting the first record should succeed, the second should succeed, and the 3rd should fail. Right now the 2nd two fail. I gotta be missing something easy, but I can't figure it out.
USE tempdb GO IF OBJECT_ID('tempdb.dbo.CheckConstraintTest') IS NOT NULL DROP TABLE tempdb.dbo.CheckConstraintTest; CREATE TABLE CheckConstraintTest
I've got about 6 formats of flat files coming through data flows & heading into a relational db, then later to a data warehouse. In each file type, I've got about 60-70 columns to perform basically two levels of validations on - first is straight data type conversions, then 2nd is finer level stuff. Some of the data from each file type overlaps in other files, so for instance some lookup codes are maintained for both.
The data is pretty dirty, so I'm keeping everything as varchar coming into the staging area, just so I can get the data in the system, b/c the users demand on some form of of the data making it into the system, no matter the dirtiness.
So then I'm running my two steps - first converting data types from varchar to bit/datetime/int, etc. as applicable. And then , I'll be running finer levels of validation, doing range checks, etc. My question is - with so many columns, what's the most efficient & best way of doing all these checks in the data flow, and recording errors out of each check? Do I put one column check after another with the success constraint, do the lookup/range check/other, and then record the error if an error is encountered, then move on to the next column? Or would it be better to multicast out the stream to 60 flows and do all the needed checks, then union all the good stuff back together at the end? Anything to help save some headache - b/c this data is dirty and there's a lot of it.
I have an SSIS Package. I am using script component to loop through input columns and their values. I am not able to do Null checking. The code is as below. In place of dashes , I want to do null checking but am not able to do. I tried vbNull, IsNull, TypeOf, System.dbNull but nothing is working. I guess am missing something here. Can anyone help me with this.
For Each column In Me.ComponentMetaData.InputCollection(0).InputColumnCollection
I have one table namely consumer with approx 50 columns.
I have created one same table with audit prefix including 2 more column for action n timestamp fields.
My question is that if user change only 10 column data at a time: i want to add only that particular column data rather to add entire row. Currently I am adding entire row in audit table but now scenario is change to update only updated column data.
