Perform Calculation For Only Desired Rows

Sep 6, 2007



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

View 2 Replies


ADVERTISEMENT

Perform Calculation On Responsnse From A Form

Aug 2, 2006

i have a form where I ask a number of questions
The answer for each question is a drop down menu ...
The person chooses one or many from the answer ....

e.g I would like a wrist band for Trip A (cost £6) {WristA}
I would like a wrist band for Trip B (cost £8) {WristB}
I would like wrist band for Trip C (cost £4) {WristC}

etc etc ....
I would like to grab thes responses from the form
and write SQL script to perform the total cost of wristbands...

Any ideas on how I can do this in SQL - the 'tokens' are available in SQL (i.e. {WristA} , {WristB}, {WristC})

View 5 Replies View Related

Simple Simple Linking Tables & Perform Calculation

Mar 22, 2007

found it

View 3 Replies View Related

Getting Sequential Rows From A Calculation

Apr 4, 2008

I am currently trying to get a dataset of sequential decreasing calculations, but they all end up on one row.  I need each of the secondary selects statements to show up as separate rows, any idea?Select

(select Rate * @Premium from ProfitLossTable where rateID =1)
, (select Rate * @Premium - @premium from ProfitLossTable where rateID =1)
,(select @premium - Rate * @Premium from ProfitLossTable where rateID =3)  

View 3 Replies View Related

Transact SQL :: Update Incoming Rows Based On Percentage Calculation

Apr 2, 2015

This is on SQL Server 2008. Please find a detailed description and the file of the data, that I am working on.

Requirements:

1.
If
'Channel'
is
not
equal to "Omnibus"
where
the 'Trans Description'is
equal to "Purchase"
and
"Redemption"
for
one purchase and
one redemption that match on 'System'
,
'Account TA Number'
,
'Product Name'
,
'Settled Date'
,
and
where
the 'Trade Amount'
of the purchase and
redemption is
within 5%,
then
display those set of records.

2.
If
deemed wash trades,
allow user to update the purchase and
redemption pair 'Trans Description'
from
"Purchase"
to "Exchange In"
and
'Trans Description'
from
"Redemption"
with
"Exchange out"

System Channel Dealer Name Firm Name Product Cusip Product Name Product Share Class Trade ID Settled Date Account TA Number Trans Description  Trade Amount 

SCHWABPORTAL US - ASG MILLIMAN MILLIMAN 64128K777 Strategic Income Fund A 29806259 30-Jan-15 000BY00F2RW Redemption  $      25,68,458.15

[Code] .....

View 36 Replies View Related

Transact SQL :: Arithmetic Calculation Between Two Rows And Result In Alias Column?

Jul 12, 2015

I am trying to compare Sales value of year 2015 with sales value of 2016 and the difference stored in alias column as Sales_growth for year 2016 , for year 2015 the alias column be as '1' similarly difference between margin of 2015 and 2016 and result stored in alias column as margin_rate in year 2016for 2015 as 1 but when there is no record for year 2015 and record present in 2016 for a given (month, SM,SG,CUST,SP) then the alias column sales_growth and margin_rate should be 100 

Formula for calculation

SGR = (sales(2015)-sales (2016)) / Sales_growth(2015)
SGR = (3456.05-3603.33) /3456.05 = -0.043
MR =( margin (2015)-margin( 2016) / margin(2015)
MR = (1059.24-1053.07)/1059.24= 0.006
DECLARE @T1 TABLE

[code]....

last record : as there is no record  for year 2015 and record present in 2016 for a given (month, SM,SG,CUST,SP) then the alias column sales_growth and margin_rate should be 100

View 18 Replies View Related

SQL Server 2008 :: Update Incoming Rows Based On Percentage Calculation

Mar 13, 2015

I have below records coming in from source files

ProdName Amount TranType
P1 100 A
P1 100 S
P2 200 A
P2 205 S

In case the ProdName is same, and Amount = or (within +/- 5%) of Amount, I have to update the TranType column as IN/OUT respectively as shown below in the tables.

I am okay with using 2 different tables if needed as in the records comes in one table and then i can reference that table to upload the values in another.

ProdName Amount TranType
P1 100 IN
P1 100 OUT
P2 200 IN
P2 205 OUT

The order of the records coming in can be different order, they need not be subsequent.

This is happening in SQl Server 2008.

View 3 Replies View Related

SP To Perform Query Based On Multiple Rows From Another Query's Result Set

Nov 7, 2007

I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w.  one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included)         INSERT INTO @users             SELECT u.userName,u.userID                 FROM  tableU u                LEFT JOIN tableW w ON w.workgroupID = u.workgroupID                WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is:             SELECT u.userName,u.userID
                FROM  tableU u
                LEFT JOIN tableW w ON w.workgroupID = u.workgroupID
                WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!    

View 1 Replies View Related

Analysis :: SSAS Calculation With Division Combined With A Time Calculation?

Sep 17, 2015

I have created calcalated measures in a SQL Server 2012 SSAS multi dimensional model by creating empty measures in the cube and use scope statements to fill the calculation.

(so I can use measure security on calculations

as explained here  )

SCOPE [Measures].[C];

THIS = IIF([B]=0,0,[Measures].[A]/[Measures].[B]);

View 2 Replies View Related

Converting Oracle Calculation To Sql Server 2005 Calculation

Jul 19, 2007

Hi I am having to convert some oracle reports to Reporting Services. Where I am having difficulty is with the

calculations.

Oracle

TO_DATE(TO_CHAR(Visit Date+Visit Time/24/60/60,'DD-Mon-YYYY HH24:MISS'),'DD-Mon-YYYY HH24:MISS')



this is a sfar as I have got with the sql version

SQLSERVER2005

= DateAdd("s",Fields!VISIT_DATE.Value,Fields!VISIT_TIME.Value246060 )



visit_date is date datatype visit_time is number datatype. have removed : from MI(here)SS as was showing as smiley.



using:

VS 2005 BI Tools

SQLServer 2005



View 5 Replies View Related

How To Get Desired Row Number

Apr 18, 2015

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.

View 1 Replies View Related

Help Me To Get Desired Output

Mar 8, 2007

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

View 7 Replies View Related

Query Help Desired!

Mar 25, 2008

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.

View 4 Replies View Related

Desired Results In One Row

May 6, 2008

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

View 10 Replies View Related

Join Not Getting Desired Result?

Jun 16, 2015

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.

View 5 Replies View Related

Query Not Getting Desired Results

Apr 10, 2008

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

View 5 Replies View Related

Query Output In A Desired Way

Aug 11, 2006

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."

View 2 Replies View Related

Data In Desired Format

May 5, 2008

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

View 8 Replies View Related

Sp_replcmds Not Getting The Desired Output

Jan 16, 2007

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



View 1 Replies View Related

OpenXML Not Returning Desired Results

Aug 31, 2007

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  

View 3 Replies View Related

Why Doesnt BETWEEN Give Me My Desired Result?

Dec 9, 2005

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 :/
 
 

View 6 Replies View Related

How To Join Tables And Get Desired Data...

May 16, 2005

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!!

View 6 Replies View Related

Problem Getting The Desired Result..Need Quick Help Please!!

Feb 4, 2008

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.

View 2 Replies View Related

Sql Query To Get A Desired Format Of Display

May 23, 2008

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.

View 2 Replies View Related

Select Only Fields With Desired Variety

Mar 27, 2014

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 (¦)

View 5 Replies View Related

SELECT Statement Not Giving Me Desired Results

Jun 27, 2006

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

View 2 Replies View Related

How To Insert Data Into Sql Table In Desired Format

Jan 25, 2008

 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 Related

Selecting A Desired Sequence Of Records Without Where Condition

Apr 12, 2001

Dear 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

View 1 Replies View Related

Separating Desired From Undesired Duplicate Records

Nov 20, 2007

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. :)

View 2 Replies View Related

How To Get Employees Attendance As Desired Output From The Input

Jun 27, 2013

I am uploading input sample data and desired output data, can get the desire output.

View 4 Replies View Related

SQL 2012 :: Group By In Sub Query To Get Desired Results

Jun 15, 2015

I 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.

View 8 Replies View Related

T-SQL (SS2K8) :: Get Desired Output Without Hard Coding Values?

Jun 6, 2014

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]....

View 7 Replies View Related

T-SQL (SS2K8) :: How To Use Substring And Charindex To Extract Desired String

Jul 9, 2014

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()

View 9 Replies View Related







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