I'm really in need of help with this one!!
I am writing a script which will provide me with financial info.
This will then be used to upload to a management system.
I have tried using the following code;
select
year.mem_desc as year,
line.mem_name as line,
unit.mem_name as unit,
ver.mem_name,
version =
Case
when ver.mem_name like 'Actual' then 'Actual Input'
when ver.mem_name like 'Forecast' then 'Actual Input'
when ver.mem_name not like 'Actual' then ver.mem_name
end,
cust1.mem_name as product,
cust2.mem_name as costcentre,
cust3.mem_name geographic_market,
cust4.mem_name as segment,
Jan =
case
when Jan is null and ver.mem_name like 'Actual'
then select Jan from finloc where year.mem_desc like year and line.mem_name like line and
unit.mem_name like unit and ver.mem_name like 'Forecast'
when ver.mem_name like 'Forecast'
then select Jan from finloc where year.mem_desc like year and line.mem_name like line and
unit.mem_name like unit and ver.mem_name like 'Actual'
when Jan is not null then convert(varchar(50),Jan)
end,
This doesnt work!! I need to be able to say that if the columns are blank or null for Actual then take Forecast, a kind of merge statement is needed but I dont know how to write one, can someone please please help!!
as you might have seen, I'm a total newbie to this Site and M$ SQL. I browsed the forum to see if there are already matching answers to my question, with no success.
My question: As a MySQL & PHP user I ask myself if there is a possibility to use M$ SQL the way I use MySQL and PHP!? Is there a way to generate dynamic websites with M$ SQL 2000 and a webserver running PHP sources? A brief yes or no would really help me, if you have any links or resources on this topic, I'd be glad to hear about it.
I am unable to the access on table even after providing the SELECT permission on table.
Used Query by me :
Here Test is schema ; Card is table ; User is Satish
To grant select on Table
GRANT SELECT ON TEST.Card TO satish Even after this it is not working, So provided select on schema also. used query : GRANT SELECT ON SCHEMA::TEST TO Satish.
HiI have a query that is performing very strangely.I f I put a top statement in it returns rows,soSelect top 10 * from .......returns 10 rowsbut without it then no data is returnedSelect * from ..........returns 0 rows.
I had a function like below :Public Sub Getdata2(ByVal query, ByVal db, ByVal name) Dim selectSQL As StringDim con As SqlConnection Dim cmd As SqlCommand Dim reader As SqlDataReader Trycon = New SqlConnection("User ID=xxx;password=xxx;persist security info=True;Initial Catalog=database1;Data Source=xxx.xxx.xxx.xxx.xxx,xxxxx;") Dim username As String username = Request.QueryString("username") selectSQL = "SET DATEFORMAT DMY;Insert into table1(hse_num, st_name, proj_name, unit_num, postal, n_postal, flr_area, flr_sf, flr_rate, flr_ra_sf, land_area, land_sf, land_rate, lnd_ra_sf, prop_code, cont_date, title, sisv_ref, r_date, rec_num, source, username, DGP, Remarks, Sub_Code, caveat, consider, age) select hse_num, st_name, proj_name, unit_num, postal, n_postal, flr_area, flr_sf, flr_rate, flr_ra_sf, land_area, land_sf, land_rate, lnd_ra_sf, prop_code, cont_date, title, sisv_ref, r_date, rec_num, source, '" & username & "', DGP, Remarks, Sub_Code, caveat, consider, age from [yyy.yyy.yyy.yyy,yyyy].database2.dbo.table2 where " & querycmd = New SqlCommand(selectSQL, con) con.Open() reader = cmd.ExecuteReader() lbl.Text = selectSQLCatch ex As Exception lbl.Text = ex.Message Finally If (Not con Is Nothing) Then con.Close() con.Dispose() End If End Try End Sub '------------------------------------------------------------------------------------------------------------------------------------------------------------------------- May i know that how do i retrieve data from [yyy.yyy.yyy.yyy,yyyy].database2.dbo.table2 due to diffrent server, diffrent UID and Password
Hi If i use this code i cant get the data showed, it show nothing."SELECT COUNT(DogImageDate) AS Amount, DogImageDate, DogImageID FROM EnggaardImages WHERE DogImageDate NOT LIKE (SELECT TOP 1 DogImageDate FROM EnggaardImages ORDER BY DogImageDate DESC;) GROUP BY DogImageDate ORDER BY DogImageDate DESC;" But if i use this code i get data showed"SELECT COUNT(DogImageDate) AS Amount, DogImageDate, DogImageID FROM EnggaardImages GROUP BY DogImageDate ORDER BY DogImageDate DESC;" Then i get Images(6)Images(1)Images(1) But i dont want the first to be showed, therefor i use the Select TOP 1, so i get a look like this Images(1)Images(1) But i cant get it to work.
-------------------------- Teams -------------------------- ID | Name | City | State --------------------------
I want to output the Teams in this format:
Name City, State
However, some teams don't have a name, so I want to output them like this:
City, State Team City, State
And some teams don't have a name or a city:
State Team State
And then there are teams who have a name, but not a city:
Name State
I'd like to do this all in one select statement. Is there a way to do something like: SELECT Name IF EXISTS, City IF EXISTS, State IF EXISTS FROM TEAMS
my table ha follow structure..SalaryTable:Value Salary------------Jan 1000Feb 2000Mar 3000Apr 4000i want the o/p as..Jan Feb Mar Apr1000 2000 3000 4000Pls give me a query for thisRegards,Satheesh
I have a table "tbNews" in my database with N_ID, N_Content... eg: N_ID N_Content 1 Hello 3 How are you? 14 Are you there? 23 Can you help me? 32 ABC 33 CDE 36 EFG If I have my N_ID = n (with n=1,3...) , can I select my records with next 1,2 or pre 1,2 eg: I have my N_ID=23, -->can I have got N_ID=3, 14 or N_ID=32, 33 ? N_ID N_Content 3 How are you? 14 Are you there? or 32 ABC 33 CDE Please help me have a Select clause! Thanhs all.
select * from tbl where nmonth between datepart(mm,DATEADD(month, -2, getdate())) and datepart(mm,getdate()) this query is returning my data between 1(jan) and 3 (mar) if i change my query to get all datas between nov to mar select * from tbl where nmonth between datepart(mm,DATEADD(month, -4, getdate())) and datepart(mm,getdate()) this query is not returning data since the month between 11 to 3 Guys,Help me out to correct this syntax.
Hi! Anyone knows how to select only certain data from one data field?
Lets say i have this field which captures Member Name and ID. The ID is in parantheses "()". I only want the Member name. How do I select this from the table?
E.G.: Field: Data MName: John Doe (123)
I need to select only "John Doe". Any help is deeply appreciated. Thank you!!
Hi, i am trying to create a t-sql statement that will retrieve last months data (ie. if i run the query on 9th August, i only want to retrieve Julys data, 1st Sept will retrieve all of Augusts data etc). The query will be used once a month to populate a table, can anyone advise me on the correct where clause to use ?
I have data that sometimes will be modify. when modify the new data will be insert and the old data still remain inside the database. The same item have their own unique id. So I want to query the data that last modified. How?
finding is queries to find duplicate data.Basically I have a picklist table in a database and I have discovered that there are what looks like duplicate data (because the name is the same) but there is a different number on the end, as you can see from an example below.
NO, ENTRY 24, John Doe|26|05768 24, John Doe|26|5768
Do you know if there is a sql query that can be ran against this table that will look through the ENTRY column and select fields that are similar and not duplicates (duplicates can't exist due to PK constraints)
Is there a good way in SQL to select records that have been sent in an excel spreadsheet and import them into a temporary table?
I have a list of order codes that have different seg codes for example:
Order CodeZSeg 7625 ZBL 717009 ZBL 3277 ZCI 8144 ZCI
Each order code is unique and is classifed into a zseq. Depending on this zseg I need to update an existing table with the following field names and values for each order code:
For example for an order code classified as a ZBL I need to insert into an existing table that uses order code as the PK
Value Code: Patrac Value Description: Patient Race Value Type: ST Units: None Template Name: Lab_Master_ Field Name: AOE_BL_Race
There are multiple Value codes for each Zseg that will be inserted for each order code.
Can someone tell me the best way to do something like this?
Hi there, Need help.. I have a table name Changekitlist and have fields name CKNumber,ck_ID,ck_Name, etc.. Now, I plan to see each CKNumber one at a time so that I can put it in an array which I'll use in VB6 program.
However, If I use the command: "SELECT CKNumber from Changekitlist" it displays all the CKNumber list that I have (CK001 to CK500) and I cannot store it in an array properly because it display all CKNumber at once and store all the 500 CKNumbers at once in the array.
What will I do so I can display one CK Number at a time, store in an array and loop so that I'll get the next CKNumber and store it again.. Need your advice please. Thanks!
Suppose you have table with many columns and often you need data from first 15 or 20 columns. In that case you have to specify all the columns in your select statement. This procedure will select top N columns you want. All you have to do is to supply table name and number of columns you want
Here is the procedure
Create procedure TopNcolumns (@tableName varchar(100),@n int) as Declare @s varchar(2000) set @s='' If exists(Select * from information_Schema.tables where table_name=@tablename and table_type='Base Table') Begin If @n>=0 Begin set rowcount @n Select @s=@s+','+ column_name from information_schema.columns where table_name=@tablename order by ordinal_position Set rowcount 0 Set @s=substring(@s,2,len(@s)-1) Exec('Select '+@s+' from '+@tablename) End else Select 'Negative values are not allowed' as Error End else Select 'Table '+@tableName+' does not exist' as Error
How would I write a select statement that would return multiple fields in a records based on a a distinct of one of those fiels.
Example
Table Name : Sales Table Field Name : Name Address Phone Zip Sale Rec1: Peter Smith 12 Market St 999-999-9999 12345 99.99 Rec2: John Jones 73 Broadway 999-999-8888 12345 12.34 Rec3: Charle Brown 42 Peanuts Ave 999-999-7777 12345 34.56 Rec4: Peter Smith 12 Market St 999-999-6666 12345 67.89 Rec5: John Jone 73 Broadway 999-999-5555 12345 36.52
How would I be able to return the columns Name Address and Phone based on the distinct of Name.
Hi all, have a pretty simple query. but am new to workin in multiple tables. This statement shows Items in table1 that exists in table2 that match a filter.
I would like to change this query to update the TOBEDELETED column to 'F' based on the same column and filter match.
SELECT ITEMS.ITEMNO, ITEMS.TOBEDELETED FROM ITEMS INNER JOIN QLOG ON ITEMS.ITEMNO=QLOG.SOURCEID WHERE QLOG.SOURCE= 'ITEMS'
Basically I want to select all of the Products (no duplicates) where EVERY customer(1, 2 & 3) have bought a from a common shop type: i.e 'Trainers' and 'Football' should not be selected as Customer3 has not bought any goods from a sports store.
This is an example of a larger problem where there can be numerous products, customers and shops. Here's a DESCRIBE of the relevent columns in each of the tables:
CUSTOMER: customerID
PRODUCT: productID customerID shopID
SHOP: shopID
This looks like it should be easy but my SQL isn't the best ;-)
Hi I'm trying to select data from different databases, Actually databases have the same structure and tables but different data, so I want to mix data from specific tables of all databases. Any help would be great
I have a table in my database that stores info to process a 'Parts Rush' request, so a user can get there part expedited. I want to have a field in my table named 'Status'.
The four status' are:
Pending
Approved
Declined
Coachback
The question is should I use integers to represent the status (ie. 1 = Pending) or should I set that field to varchar and store the actual word?
I'm just trying to develop my SQL skills and what to exercise good practices. I am using SQL Server 2005 Express.
I posted this question last night and thought I had an answer. I have a date field. I want to be able to filter records by the month of the date. To do this, I pass the integer of the month. But I also want to be able to return all the records if no month integer is passed. So functionally,select * from table where (MONTH([AD ENDS]) = @month)will return all records where [Ad Ends] is in January if @month = 1 and all records if @month is empty. The solution I got last night was to use select * from table where (MONTH([AD ENDS]) = ISNULL(@month, MONTH([AD ENDS]))) If @month is null, all records are returned. I was focused on another aspect of the page when this was posted. It worked in the designer, so I thought I was set. This morning I realized I don't know how to pass a null variable in a querystring. Since a querystring is a string, it probably can't be done. Another suggestions was to change this programmatically. But is there a way to do this in the dataset? I'm using SQL Server 2005, a strongly typed dataset and the designer.Diane The answer i got last night was to
Hi I have a table called Version and its attributes are Version_ID, Project_ID , Hospital_ ID , Date_Created and comments. I want to select the data by Version_ID,Project_ID and Hospital_ID and the selected data is inserted in the same table(Version) as new row . Table: Version (Version_ID(Primary_key), Project_ID(Foreign_Key),Hospital_ID(Foreign_Key),Date_Created,Comments). Iam using Visual Web Developer Express and SQL Server 2005. Iam doing on asp.net 2.0. Could anyone please send me the code asp.net 2.0 for the above problem.
