I am trying to select records based upon last name
WHERE (Last_Name BETWEEN 'A%' AND 'C%')
When I run this I get only last names starting with A and B--no C. ?!?
This is confusing to me....I ran it with lastname >= 'A%' and lastname <='C%'
and it returned only names starting with B. Why does SQL ignore the "="
I hope this isnt obvious :/
I have a right join query where i have a table where all employees attendance entry are registered and another table where pay master table where al employees details.
I need a query where i get all employee names from right side (pay master table) and the employees in out timings for all employees who punched or not punched their card.
means for selected date i need all the employees details suppose 10 employees who punched or not with empty values even he resigned.
I used right or left join it gives all dates value but when i select particular date i get only the punched details.
Hi,
I want to write a query that would display data from the database on the basis of ethnicity in the last 3 years. Not only that, but I also want to see the number of starters, retention and achievement
e.g.
Year 05 Year06 Year 07
Ethnicity 1 Starters
Retention
Achievement
Ethnicity 2 Starters
Retention
Achievement
The problem is that there are multiple ethnicities and I cannot write separate queries for each of them and there are 3 years and I cannot write separate queries for them either. E.g. if i have 5 ethnicities and 3 years, in current situation, I am ending up with writing 15 queries for each of them because I can't write a cross tab query for these.
When I do write the query as follows:
create table #temp_et (
[Year]int NOT NULL,
[Ethnicity] varchar(20),
[Starts]float(4),
[Success]float(4),
[Retention]float(4),
[Achievement]float(4)
)
--Year 05/06
insert into #temp_et
select
CAST(LEFT(pg_expendyrid,2) AS int)[Year],
pg_ethnicgroupname[Ethinicity],
sum([pvstart]) [Starts],
(case when sum(pvstart) = 0 THEN 0 Else convert(decimal(6,3),(sum(pvach)*1.00)/(sum(pvstart)*1.00)*100)end)[Success],
(case when sum(pvstart) = 0 THEN 0 Else convert(decimal(6,3),(sum(pvcomp)*1.00)/(sum(pvstart)*1.00)*100)end)[Retention],
(case when sum(pvstart) = 0 THEN 0 Else convert(decimal(6,3),(sum(pvach)*1.00)/(sum(pvcomp)*1.00)*100)end)[Achievement]
--into temp_et
from pvmd
where pg_expendyrid = '05/06'
group by
pg_ethnicgroupname,
pg_expendyrid
order by
pg_ethnicgroupname
--Year 06/07
insert into #temp_et
select
CAST(LEFT(pg_expendyrid,2) AS int)[Year],
pg_ethnicgroupname[Ethinicity],
sum([pvstart]) [Starts],
(case when sum(pvstart) = 0 THEN 0 Else convert(decimal(6,3),(sum(pvach)*1.00)/(sum(pvstart)*1.00)*100)end)[Success],
(case when sum(pvstart) = 0 THEN 0 Else convert(decimal(6,3),(sum(pvcomp)*1.00)/(sum(pvstart)*1.00)*100)end)[Retention],
(case when sum(pvstart) = 0 THEN 0 Else convert(decimal(6,3),(sum(pvach)*1.00)/(sum(pvcomp)*1.00)*100)end)[Achievement]
--into temp_et
from pvmd
where pg_expendyrid = '06/07'
group by
pg_ethnicgroupname,
pg_expendyrid
order by
pg_ethnicgroupname
--Year 07/08
insert into #temp_et
select
CAST(LEFT(pg_expendyrid,2) AS int)[Year],
pg_ethnicgroupname[Ethinicity],
sum(pvstart) [Starts],
(case when sum(pvstart) = 0 THEN 0 Else convert(decimal(6,3),(sum(pvach)*1.00)/(sum(pvstart)*1.00)*100)end)[Success],
(case when sum(pvstart) = 0 THEN 0 ELSE convert(decimal(6,3),(sum(pvcomp)*1.00)/(sum(pvstart)*1.00)*100)end)[Retention],
(case when sum(pvcomp) = 0 THEN 0 ELSE convert(decimal(6,3),(sum(pvach)*1.00)/(sum(pvcomp)*1.00)*100)end)[Achievement]
--into temp_et
from pvmd
where pg_expendyrid = '07/08'--Expected end year
group by
pg_ethnicgroupname,
pg_expendyrid
order by
pg_ethnicgroupname
SELECT
(CASE [Year] WHEN 05 THEN [Starts] ELSE 0 END) AS [Starts],
(CASE [Year] WHEN 05 THEN [Retention] ELSE 0 END) AS [Retention],
(CASE [Year] WHEN 05 THEN [Achievement] ELSE 0 END) AS [Achievement],
(CASE [Year] WHEN 06 THEN [Starts] ELSE 0 END) AS [Starts],
(CASE [Year] WHEN 06 THEN [Retention] ELSE 0 END) AS [Retention],
(CASE [Year] WHEN 06 THEN [Achievement] ELSE 0 END) AS [Achievement],
(CASE [Year] WHEN 07 THEN [Starts] ELSE 0 END) AS [Starts],
(CASE [Year] WHEN 07 THEN [Retention] ELSE 0 END) AS [Retention],
(CASE [Year] WHEN 07 THEN [Achievement] ELSE 0 END) AS [Achievement]
from #temp_et
This is the closest I could get to the output as in the end I want to put it into a report in Visual Studio .Net i.e. Linking that report through a stored procedure in SQL Server. If I copy all this code to a SP, and dont put the data in a temporary table, the SP will only return the top rows and not the data from year 06/07 and 07/08.
All i can think of is creating a crosstab query, which I cant. So help would be much appreciated.
P.S. The data for Ethnic Group is coming from a view and that is joined which I forgot to mention here in the code.
How do I automatically assign a new cardcode-number? (according to the following formula: highest existing number + 1)
Scenario:
-There are two types of business partners: Customers and Suppliers.
-Customers have the value 'C' in the colomn CardType.
-Suppliers have the value 'S' in the colomn CardType.
-Customers have the following syntax 'C123456' in the colomn CardCode.
-Suppliers have the following syntax 'S123456' in the colomn CardCode.
-Existing CardCode-values in the DB for the Customers: C000001 - C100599.
-Existing CardCode-values in the DB for the Suppliers: S000001 - S200199.
The idea is that when a user creates a new business partner, the CardCode should be automatically filled when a new assigned number (highest existing number + 1), according to the value that is selected in CardType (either the letter 'C' or 'S').
What's been done so far:
SELECT top 1
(CASE
WHEN CardType='C' THEN (SELECT top 1 'C' + '' + cast((substring(T0.CardCode, 2, 7) + 1) as varchar) as [nummer]
FROM OCRD T0
WHERE T0. CardCode like 'C%' AND T0. CardType='C'
order BY T0.CardCode desc FOR BROWSE)
WHEN CardType='S' THEN (SELECT top 1 'S' + '' + cast((substring(T0.CardCode, 2, 7) + 1) as varchar) as [nummer]
FROM OCRD T0
WHERE T0. CardCode like 'S%' AND T0. CardType='S'
order BY T0.CardCode desc FOR BROWSE)
END)
FROM OCRD T0
The current result:
The result that it gives is 'C100600'.
The problem however is that it always gives this result and does not take into account what has been selected in CardType.
When I add the following: "order BY T0.CardCode desc FOR BROWSE" it gives the result 'S200200'.
So, what does work is that it takes the highest existing value and adds 1. But what doesn't work is the taking account what value is selected in CardType.
Does anyone know how I can make this work?
I have a table with plant types and plant names. Certain plants are grouped on a custom field, currently called Field. I am trying to create a query that will give me a result set containing the primary order on Type, but need items with the same 'Field' value grouped by each other.For example, the following shows a standard query result with "order by Type", ie select * from plants order by Type
Code:
ID Type Name Field
1 Type1Name1(group1)
2 Type2Name2(group2) -group2
3 Type3Name3(group3)
4 Type4Name4(group4)
5 Type5Name5(group2) -group2
6 Type6Name6(group6)
But I want it to look like this, with fields of the same value located next to each other in the result set (but still initially ordered by Type)
Code:
1 Type1Name1(group1)
2 Type2Name2(group2) -group2
5 Type5Name5(group2) -group2
3 Type3Name3(group3)
4 Type4Name4(group4)
6 Type6Name6(group6)
I have some data which is vertical...I want to create a pivot query in SQL that will give me a result that is horizontal like this. I cannot find a way of doing it without lots of IF or CASE statements?
View 10 Replies View RelatedSELECT * FROM
( SELECT TOP 15 * FROM
(SELECT TOP 15 CMDS.STOCKCODE AS CODE,CMDS.STOCKNAME AS NAME,CMDS.Sector AS SEC, CMD7.REFERENCE AS REF,T1.HIGHP AS HIGH,
T1.LOW,T1.B1_CUM AS 'B/QTY', T1.B1_PRICE AS BUY,T1.S1_PRICE AS SELL,
T1.S1_CUM AS 'S/QTY', T1.D_PRICE AS LAST,T1.L_CUM AS LVOL,T1.Chg AS CHG,T1.Chgp AS CHGP, T1.D_CUM AS VOLUME,substring(T1.ST,7,6) AS TIME,
CMDS.SERIAL as SERIAL FROM CMD7,CMDS,CMD4 AS T1 WHERE T1.ST IN
(SELECT max(T2.ST) FROM CMD4 AS T2 ,CMDS WHERE
T1.SERIAL=T2.SERIAL
AND CMDS.SERIAL=T2.SERIAL
AND T2.sd='20060821'
AND CMDS.sd='20060821'
AND T2.L_CUM < '1900'
AND CMDS.sector >='1'
AND CMDS.sector <='47')
AND CMDS.SERIAL=T1.SERIAL AND
CMDS.SERIAL=CMD7.SERIAL AND
CMDS.sd='20060821' AND
CMD7.sd='20060821' AND
T1.sd='20060821' AND
T1.L_CUM < '1900' AND
CMDS.sector >='1' AND
CMDS.sector <='47' ORDER BY T1.D_CUM desc)
AS TBL1 ORDER BY VOLUME asc) AS TBL1 ORDER BY VOLUME desc;
I have a question with a row number. Below I have 2 columns that I want to Put a row number with.
ID. Quantity DesiredRowNumber
123 1. 1
123. 1 1
123. 1. 1
123. 3 2
123. 3. 2
[code]...
How would I get the desired row number.
Dear Friends, I've created one table, with the following attributes.
everything is ok, but while retrieving data, the question numbers should be starting from 1,2.......... but i'm not getting that result. please suggest me to the output like that.
--create table question_code_level (qno int,code varchar(10),level varchar(10),question varchar(500),option1 varchar(200),option2 varchar(200),option3 varchar(200),option4 varchar(200),option5 varchar(200))
select * from question_code_level
1htmlbeginerwhat is clr?somethingsomethingsomethingsomethingsomething
2c#beginerwhat is clr?somethingsomethingsomethingsomethingsomething
3c#expertwhat is clr?somethingsomethingsomethingsomethingsomething
4c#intermediawhat is clr?somethingsomethingsomethingsomethingsomething
5c#beginerwhat is clr?somethingsomethingsomethingsomethingsomething
6vbbeginerwhat is clr?somethingsomethingsomethingsomethingsomething
7htmlexpertwhat is clr?somethingsomethingsomethingsomethingsomething
8sqlserverintermediawhat is clr?somethingsomethingsomethingsomethingsomething
10oraclebeginerwhat is clr?somethingsomethingsomethingsomethingsomething
11javabeginerwhat is clr?somethingsomethingsomethingsomethingsomething
12javaexpertwhat is clr?somethingsomethingsomethingsomethingsomething
13sqlserverbeginerwhat is clr?somethingsomethingsomethingsomethingsomething
--select * from question_code_level where code='c#' and level='beginer'
2c#beginerwhat is clr?somethingsomethingsomethingsomethingsomething
5c#beginerwhat is clr?somethingsomethingsomethingsomethingsomething
Vinod
Hi all,
The query below, as it stands, does a search for strings like 'Fema', that exist in the word 'Female', and returns 'female'. Now, if the word 'Acc' stands on it's own, I want to know how to return it as 'Accessories' in the result set. And yes, I've tried the obvious (When 'Acc' Then 'Accessories') but that returns a Null instead of 'Accessories'!!
Here's the query:
SELECT CategoryID, Category, CASE substring(category, 1, 4)
when 'fema' then 'female' WHEN 'Male' THEN 'Male' WHEN 'Gift' THEN 'Female' END AS CategoryGroup
FROM dbo.InventoryCategory
Thanks all,
Jaybee.
Hello,
I am trying to get results in one row from the following function but all records does not come from the following function.
I have 9 records of the same empid but results not showing all records. Can anybody help me to get all records.
CREATE FUNCTION dbo.GetBenefString5
(
@Empid INT
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @ret VARCHAR(8000)
SELECT @ret = ''
SELECT
@ret = @ret + CASE WHEN LEN(@ret) > 0 THEN ',' ELSE '' END + FName + ' ' + Benefittype + ' ' + BenefitPercentage
From Beneficiary
Where Empid = @Empid
RETURN @ret
END
SELECT
Empid,
dbo.GetBenefString5(Empid)
FROM pf25eaton_work.dbo.eaton_chr_benef_05052008
where EmployeeNumber='4500498'
GROUP BY Empid
Result from the above function query:
Empid ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1773 CHARLENE OLIF 33 ,CHARLENE EADD 33 ,CHARLENE ELIF 33 ,TIMOTHY EADD 33 ,TIMOTHY
(
Records are in the table:
FName Benefittype BenefitPercentage
---------------------------------------- ----------- -----------------
CHARLENE OLIF 33
CHARLENE EADD 33
CHARLENE ELIF 33
TIMOTHY EADD 33
TIMOTHY ELIF 33
BRADLEY ELIF 33
TIMOTHY OLIF 33
BRADLEY OLIF 33
BRADLEY EADD 33
Desired Result in one row:
Charlene OLIF 33, Charlene EADD 33, Charlene ELIF 33, TIMOTHY OLIF 33, TIMOTHY EADD 33, TIMOTHY ELIF 33, BRADLEY OLIF 33, BRADLEY EADD 33, BRADLEY ELIF 33
Hi,
I have a query that I am trying to run, but I discovered I am not getting the desired results.
This query here goes across the tables as seen below. One thing that I notice about this query as I am trying to figure out the source of the problem is that it is bringing back the same results even when I change the @userID parameter.
As long as the @userID I pass exists in "tblUserDetails" then we get results back. When it doesnt exist we dont get any records.
Basically to explain whats going on. I am passing the userID of the person who has "photocomments"
Since the table "tblPhotoComments" doesnt have a column that represents who the photocomment was for, we must join
tblExtraPhotos.counterID = tblPhotoComments.photoCounterID
This way we can get details of the photo, including the owner (tblextraphotos.userID)
Any help is much appreciated..
thanks again,
mike123
CREATE PROCEDURE [dbo].[select_Photocomments_ManageMy]
(
@userID int
)
AS SET NOCOUNT ON
SELECT TOP 500 photoCommentID, PC.active, comment, commentFromID,
UD1.nameOnline as commentFrom_NameOnline, PC.commentDate, PC.photoCounterID
FROM tblPhotoComments PC
JOIN tblUserDetails UD1 on PC.CommentFromID = UD1.userID
JOIN tblExtraPhotos EP on EP.counterID = PC.photoCounterID
JOIN tblUserDetails UD2 on EP.userID = UD1.userID
WHERE UD2.userID = @userID AND deletedByRecipient = 0
ORDER by PC.commentDate DESC
CREATE TABLE [dbo].[tblUserDetails](
[UserID] [int] IDENTITY(1,1) NOT NULL,
[NameOnline] [varchar](15) NULL
)
GO
CREATE TABLE [dbo].[tblExtraPhotos](
[counterID] [int] IDENTITY(1,1) NOT NULL,
[photoID] [tinyint] NOT NULL,
[userID] [int] NOT NULL,
[photoDate] [smalldatetime] NOT NULL,
[caption] [varchar](50) NULL,
[status] [tinyint] NOT NULL
GO
CREATE TABLE [dbo].[tblPhotoComments](
[photoCommentID] [int] IDENTITY(1,1) NOT NULL,
[photoCounterID] [int] NOT NULL,
[CommentFromID] [int] NOT NULL,
[Comment] [varchar](1000) NOT NULL,
[commentDate] [smalldatetime] NOT NULL,
[Active] [tinyint] NOT NULL,
[deletedbySender] [tinyint] NOT NULL,
[deletedbyRecipient] [int] NOT NULL,
[IP] [varchar](15) NOT NULL
) ON [PRIMARY]
GO
Gopinath writes "Let us consider we have the following data in a table,
Column1 Column2
John orange
John apple
John Grape
Steve orange
Steve watermelon
Steve pineapple
in the above table, i want to query and get output in the below format
Column1 Column2
John orange
apple
Grape
Steve orange
watermelon
pineapple
is it possible? if yes, kindly let me know the query.
Thanks in advance.
Regards,
Gopi."
Query:
Select convert(CHAR(45),surname+','+fname) Name from beneficiary
Output from the above query:
Name
---------------------------------------------
BICKFORD ,ROSA
KOCH ,ERIC
I am desired Results as follows: Please help
Name
---------------
BICKFORD,ROSA
KOCH,ERIC
hi,
I fired the replication command sp_replcmds on my publisher and it returned the fillowing output.
"Another log reader is replicating the database."
can anybody out there explain why is that so and how can i get the output.
Thanks in advance
Jacx
Hi all,I have a SQL job where I do the following -I check for new rows in my Table "DumpResults", every now and then and get the new rows to be inserted into table "CleanTable". I use OPENXML() to get the new data to be inserted but for some reason I don't get the right data through OPENXML() - DECLARE @intDoc INTDECLARE @xmlDoc VARCHAR(8000)IF(SELECT COUNT(*) FROM DumpResults WHERE DumpResults.C1 NOT IN (SELECT CleanTable.C1 FROM CleanTable)) > 0BEGINSET @xmlDoc = (SELECT * FROM DumpResults WHERE DumpResults.C1 NOT IN (SELECT CleanTable.C1 FROM CleanTable) FOR XML RAW) SET @xmlDoc = '<TABLE>' + @xmlDoc + '</TABLE>'
PRINT @xmlDocEXEC sp_xml_preparedocument @intDoc OUTPUT, @xmlDoc--INSERT INTO CleanTable(C1, C2, C3, C4, C5)
SELECT C1, C2, C3, C4, C5, C6 FROM OPENXML(@intDoc,'/row',1)WITH (C1 INT, C2 CHAR(3), C3 CHAR(3) , C4 FLOAT, C5 INT)EXEC sp_xml_removedocument @intDoc
END
ELSE Output that I get is - <TABLE><row C1="1" C2="AAA" C3="BBB" C4="1.000000000000000e+000" C5="2"/></TABLE>(0 row(s) affected) SO "PRINT @xmlDoc" is returning back the xml data (new results) it collected from the "DumpResults" table which isn't there in "CleanTable" but the "Select... FROM OPENXML(...)" doesn't return any result. why so? If anyone knows please reply If anyone has any better method to do it, inputs are welcome. Thanks
I have a problem...I have two tables, patient_tran and patient_plan. Patient_tran has case_entry_date and patient_id,patient_plan has patient_id, plan_Id, plan_eff_date and plan_term_date. I need to join these two tables and get the patient_id, plan_id but the plan_id should be the plan_id where the entry_date falls between plan_eff_date and plan_term_date. If doesn't match the criteria then pick up the plan_id where the plan_term_date is null, if there's no null plan_term_date then pick up the plan_id with the most recent plan_term_date. In patient_plan table, there's could be more than one plan per patient. How can I do this? Can anyone please help, will be most appreciated.
Thanks in advance!!
Hi Guys,
Below is example of the current structure of table1 I have at run time:
------------------------
GroupName Resourcename Week1 Week2 ..cont.. dynamically
Associates A1 0 80 ......
Assocaites A2 20 40 ......
Associates A3 50 100 ......
Principal P1 20 100 ......
Principal P2 0 0 ......
Principal P3 0 100 ......
------------------------
I want to change the above to something like below table2:
---------------------------
GroupName Status Week1 Week2 ....cont
Associates Assigned 2 3
Associates NotAssigned 1 0
Principal Assigned 1 1
Principal NotAssigned 2 1
---------------------------
I will try to explain how I am deriving table2 from table1. I have to count the number of Resource name against each Groupname for a particular week column(Weeki i 1 to n dynamic) where value of Week column is 0, then use this numbber against NotAssigned and the complementary number to be stored as Assigned.
The table formaating is lost in HTML view but just consider any gaps between fields as next column value.
Am I clear in what i am asking , if not please ask me.
Any help will be highly appreciated.
In order to ask my question on a SQL query I will use a simplified version of a table I was struggling with...
¦¦¦¦SHOP¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦CARS
KILBURN MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes
KILBURN MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦BMW
KILBURN MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦FIAT
BRIXTON AUTOMOBILE¦¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes
BRIXTON AUTOMOBILE¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦BMW
WEST HAMPSTEAD CARS¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes
CAMDEM MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦FIAT
NORTHERN LONDON CAR-STORE¦¦¦¦¦¦¦¦¦¦¦BMW
NORTHERN LONDON CAR-STORE¦¦¦¦¦¦¦¦¦¦¦FIAT
So my question is: how can I select only SHOPs which have no CARS variety (e.g. a variety =1)?
In other words I am looking for a Query that would give me this outcome:
¦¦¦¦SHOP¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦CARS
WEST HAMPSTEAD CARS¦¦¦¦¦¦¦¦¦¦¦¦¦Mercedes
CAMDEM MOTORS¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦¦FIAT
...because WEST HAMPSTEAD CARS, selling Mercedes only, has a CARS variety equal to 1 as well as Camdem Motors which sells only FIAT.
I tried with this query:
SELECT DISTINCT SHOP, CARS
FROM CAR_SELLERS
GROUP BY SHOP, CARS
HAVING COUNT(CARS) = 1
But it doesn't work.
In addition I also would like to know for example how to create a similar query for a different desired CARS variety (e.g. 2, 3...)
How to do the tabs or attach images in the Post New Topic Message box... I wanted to attach a screen shot of the table but I was not able to and so, in order to post my question, I prepared a simplified version of the table plus I had to do the tabs/spaces manually with the symbol (¦)
hi all, i wasnt quite sure where to look to answer my particular problem so i am posting up this thread in the hopes that someone can point me in the right direction. in my report, i am showing sales figures for an area. i added a table to display sales from this year, sales from last year, and then the comparable percentage('this' divided by 'last' and then minus one). to account for some ppl who didnt have sales last year, i was able to use an IIF expression to return "N/A" in the textbox. my problem is i want the compared percentage to show for the area total, but not including ppl who were an 'N/A'. i am assuming that some sort of expression will be needed for the area total row, i.e. i want the area total to sum up this year and last year and then get the percentage, but i need to filter out the individuals who didnt have data for last year.
as an example
nsty nsly percentage
total: 15 7 X
A: 5 4 25%
B: 5 3 66%
C: 5 0 N/A
the percentage for total, X, should be (10/7)-1, with p0 as the format; right now it is summing rows A, B, and C.. how can i exclude row C from the calculation?
am i on the right track by researching filters and expressions, or is this a matter for the query
thanks in advance for any help
I have the following SELECT statement attached to a dropdown box:
SELECT [Workshop] FROM [Workshops] WHERE Workshop <> (SELECT Workshop FROM Workshop_Registration WHERE FullName = @FullName AND Completed = @Completed) ORDER BY [Workshop]
I am trying to get all workshops (50 or more) from the WORKSHOPS table that the logged in user is not already registered for. This works perfectly as long as the student is registered for at least 1 class. It populates the dropdown with all of the other classes. If they aren't registered for a class then it doesn't list any classes. The problem is definitely the subquery, but how do I make it to where if the subquery doesn't return any results (student not registered for anything), I get all of the workshops in the dropdown? Any help is appreciated!
MikeD
Hello,I have a problem the scenario is :I have data in an excel file and now I am reading data from that file and insert that data into sql database. this is well.but the problem is that I have few fields with date time data in excel sheet. In my database I have varchar type data type for these data columns.I want to read these data columns from the excel sheet and insert only time into the data base.how can I do this I am using the following line of code for selelcting only time from the excel file. string qry = "Select CONVERT(CHAR(5),datetime,114) from [" + objStr[0] + "];";this gives me error message.help me to read the data from excel file and insert it into the sql table in desired format. Thanks in advance, junior
View 1 Replies View RelatedDear Friend,
How to select a desired sequence of records without where condition?
For example I want to select the first 100 records I am using
SELECT TOP 100 * FROM EMPLYEETB;
Like this I want to select the records starts from 101 to 200.
Is it possible? If yes how?
Thanks in advance.
Lovingly,
Meyyarasu
I know of several methods to remove duplicate records but I recently encountered a unique situation where some duplicate records were actually acceptable.
Here is my situation:
I have a table that contains records of individuals who have children so 1 person can have 3 children with different birthdates; but there is also a field that has a specified language. The challenge arises when an individual may have only 1 child but has entered a single record twice. Once with a specifed language and again without which produces a default value of UNKNOWN.
I need to be able to remove this record without affecting records that may have a record entered twice as well; having 2 children for instance but they may also have a specified language in one record but a default value of UNKNOWN for their second record.
So I can't eliminate the unwanted duplicates by filtering out records that have UNKNOWN because I would also remove individuals that I need.
EX:
firstN | lastN | address | lang | childs birthdate
John Doe 210 Somewhere Ave ENG 1993-10-09
John Doe 210 Somewhere Ave UNK 1993-10-09
Jane Doe 210 Anywhere Ave ENG 1969-12-23
Jane Doe 210 Anywhere Ave UNK 1958-04-15
How could you remove the duplicate for John in this example without affecting Janes duplicate record which is actually ok because she apparently has 2 children with different birthdates whereas John's duplicate record is obviously created because it was entered twice; once without entering
a language and the second time specifying the language?
I have tried a number of things short of creating a cursor which isn't really the best way to resolve this issue since there are millions of rows.
Anyone out there have any input that be helpful? Or has anyone ever had this similar issue? I would be interested in knowing how you addressed the problem.
Any help is appreciated. :)
I am uploading input sample data and desired output data, can get the desire output.
View 4 Replies View RelatedI have to use a Group By in Sub query that will give me my desired results.
SELECT count(hc), CS, S_W, (SELECT COUNT(Reg_Temp)
FROM CS_S
WHERE Reg_Temp = "Reg") as [RegOnly]
FROM CS_S
GROUP BY S_W, CS
ORDER By CS, S_W
The query I have above counts ALL the Reg, I'd want it to give only the count that I'd get by using Group By clause i.e. S_W and CS.
See DDL and sample data below. What would be the easiest way to get the desired output without hard coding the values? Data in both tables may change over time.
DECLARE @num AS TABLE (
Id INT IDENTITY(1, 1)
,Price MONEY
)
DECLARE @range AS TABLE (
Id INT IDENTITY(1, 1)
,Rng MONEY
[code]....
I have a column that contains the follwoing string I need to compare.
ek/df/cv/
ek/df/cv/f
All fields bfore the third / are not fixed but behind the third/ is eiter nothing or one letter I need a function to extract all the fields before the third / to compare if they are equal.
I can't do it by using the combination of Substring() and charindex() and Len()
[Microsoft follow-up]
Hello,
I think I've found a bug.
I have an expression set on the 'Disable' property of a data-flow task. When the expression evaluates to true the data-flow still executes. That seems wrong to me.
I ahve a demo package here: http://folders.live.com/self.aspx/VQ9oHa1TJjc/Public/SSIS%20Stuff/ExamplePackages/20070717%20Expression%20on%20Disable%20property%20has%20no%20effect/Package1.zip
Please reply letting me know whether this is indeed a bug or not.
I'm on SP1 by the way.
Regards
Jamie
Can we bulk insert only the desired column from a flat file to a table?
I am using SSIS to bulk insert from a file with more than 200 columns. I am trying to find a way I can bulk insert them to multiples table through SSIS.
The one way I can think is pre map the columns from the file to the destination tables. Build numerous Bulk Insert tasks to achieve that. But not sure if SSIS will let me do that.
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
View 4 Replies View Related
I have an Execute SQL Task that executes "select count(*) as Row_Count from xyztable" from an Oracle Server. I'm trying to assign the result to a variable. However when I try to execute I get an error:
[Execute SQL Task] Error: An error occurred while assigning a value to variable "RowCount": "Unsupported data type on result set binding Row_Count.".
Which data type should I use for the variable, RowCount? I've tried Int16, Int32, Int64.
Thanks!
Hi all,
I copied the following code from Microsoft SQL Server 2005 Online (September 2007):
UDF_table.sql:
USE AdventureWorks;
GO
IF OBJECT_ID(N'dbo.ufnGetContactInformation', N'TF') IS NOT NULL
DROP FUNCTION dbo.ufnGetContactInformation;
GO
CREATE FUNCTION dbo.ufnGetContactInformation(@ContactID int)
RETURNS @retContactInformation TABLE
(
-- Columns returned by the function
ContactID int PRIMARY KEY NOT NULL,
FirstName nvarchar(50) NULL,
LastName nvarchar(50) NULL,
JobTitle nvarchar(50) NULL,
ContactType nvarchar(50) NULL
)
AS
-- Returns the first name, last name, job title, and contact type for the specified contact.
BEGIN
DECLARE
@FirstName nvarchar(50),
@LastName nvarchar(50),
@JobTitle nvarchar(50),
@ContactType nvarchar(50);
-- Get common contact information
SELECT
@ContactID = ContactID,
@FirstName = FirstName,
@LastName = LastName
FROM Person.Contact
WHERE ContactID = @ContactID;
SELECT @JobTitle =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN (SELECT Title
FROM HumanResources.Employee
WHERE ContactID = @ContactID)
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN (SELECT ct.Name
FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE ContactID = @ContactID)
ELSE NULL
END;
SET @ContactType =
CASE
-- Check for employee
WHEN EXISTS(SELECT * FROM HumanResources.Employee e
WHERE e.ContactID = @ContactID)
THEN 'Employee'
-- Check for vendor
WHEN EXISTS(SELECT * FROM Purchasing.VendorContact vc
INNER JOIN Person.ContactType ct
ON vc.ContactTypeID = ct.ContactTypeID
WHERE vc.ContactID = @ContactID)
THEN 'Vendor Contact'
-- Check for store
WHEN EXISTS(SELECT * FROM Sales.StoreContact sc
INNER JOIN Person.ContactType ct
ON sc.ContactTypeID = ct.ContactTypeID
WHERE sc.ContactID = @ContactID)
THEN 'Store Contact'
-- Check for individual consumer
WHEN EXISTS(SELECT * FROM Sales.Individual i
WHERE i.ContactID = @ContactID)
THEN 'Consumer'
END;
-- Return the information to the caller
IF @ContactID IS NOT NULL
BEGIN
INSERT @retContactInformation
SELECT @ContactID, @FirstName, @LastName, @JobTitle, @ContactType;
END;
RETURN;
END;
GO
----------------------------------------------------------------------
I executed it in my SQL Server Management Studio Express and I got: Commands completed successfully. I do not know where the result is and how to get the result viewed. Please help and advise.
Thanks in advance,
Scott Chang