Now each table holds different measures e.g. 326, 229 & 278. I would like to subtract the measure_value of 229 from 278 and then add the measure_value from 278 from this. e.g
(326-229) + 278 = new measure_value.
This is for each id-product where the following 3 conditions meet:
the ID_PRODUCT, ID_MARKET AND ID_BUCKET match. Im lost, any help would be great.
thanks
Jay
p.s this what i have started with at the moment as a test however it dont work.
SELECT ID_PRODUCT,
ID_MARKET,
ID_BUCKET,
ID_COLLECTION,
ID_MEASURE,
MEASURE_VALUE
FROM (SELECT TOP 100 PERCENT dbo.DPOUT_EXCEPTIONS_326.ID_PRODUCT,
dbo.DPOUT_EXCEPTIONS_326.ID_MARKET,
dbo.DPOUT_EXCEPTIONS_326.ID_BUCKET,
dbo.DPOUT_EXCEPTIONS_326.ID_COLLECTION,
dbo.DPOUT_EXCEPTIONS_326.ID_MEASURE,
dbo.DPOUT_EXCEPTIONS_326.MEASURE_VALUE + DBO.DPOUT_LSBP_229.MEASURE_VALUE as 'MEASURE_VALUE'
FROM dbo.DPOUT_EXCEPTIONS_326 INNER JOIN dbo.dpout_lsbp_229 on
dbo.dpout_exceptions_326.id_product = dbo.dpout_lsbp_229.id_product
where dbo.DPOUT_EXCEPTIONS_326.ID_PRODUCT = dbo.dpout_lsbp_229.id_product
and dbo.DPOUT_EXCEPTIONS_326.ID_MARKET = DBO.DPOUT_LSBP_229.ID_MARKET
and dbo.DPOUT_EXCEPTIONS_326.ID_BUCKET = DBO.DPOUT_LSBP_229.ID_BUCKET)
I am trying to do a calculation where I show the [YesEmail] / [TotalCustomers]. It wont let me do it unless i do two queries. Is there a way to put this all in this select statment.
Code:
SELECT customer_lifetime_totals.occurrence_1_store_no as [StoreNo], COUNT( case when customer.email_address IS NULL then 1 end) as [NoEmail], COUNT( case when customer.email_address IS NOT NULL then 1 end) as [YesEmail], COUNT(customer.customer_no) AS [TotalNewCustomers]
FROM customer_lifetime_totals INNER JOIN customer ON customer_lifetime_totals.customer_id = customer.customer_id WHERE (occurrence_1_transaction_date > CONVERT(DATETIME, '2006-08-01 00:00:00', 102)) GROUP BY customer_lifetime_totals.occurrence_1_store_no ORDER BY customer_lifetime_totals.occurrence_1_store_no
This works ... but only when i put the previous query in a temp table and then run another query referring to it.
Code:
cast(100*(cast(YesEmail as float) / TotalNewCustomers) as numeric (5,2)) as [Percent]
Hi, Table A member_no - character 5course1 - character 1course2 - character 1 Table Bcourse_no - character 1course_name - character 20I'd like a resultant view that contains A.member_no, A.course1, B.coursename as COURSE1NAME, A.course2, B.course_name as COURSE2NAMEI know how to make the join and pick up the name for course1 but I DON'T know how to pick up both names of course 1 and course 2 at the same time.I'm thinking I have to make a join on one column and then join the result of that a second time to pick up the other column's value? Thanks in advance for any assistance,Stewart
Hi, I am trying to do a simple select statement which returns data from the table. The criteria is only if the count of the records is more than 10. Say, if a vendor called test exists 10 times in the table, I would like to display that vendor only, if less than 10, then that vendor won't be displayed at all. Here is what I have started.
Select vendor_id, vendor_name, Invoice_Date, Invoice_Number from Vendors where Invoice_Date Between '01/01/2007' And '03/03/2008' Group By vendor_id, vendor_name, Invoice_Date, Invoice_Number Having Count(Invoice_Number) > 10 This doesn't seem to work. What am I doing wrong here?
I have worked on developing a project that I am pleased to say is comming along nicely considering I have only been doing programming for a few weeks but I am now stuck and may be going down the wrong path.
I have two tables one that is used for logging via username and password, it also holds a third coloumn realting to the suers account that they do not enter. Once they have logged in I am recalling the users identity and running a query to establish what account the user belongs to. Later on in the script I am trying to run a query on a table called orders that list all orders belonging to that account that was established in the earlier query labelled as 'accountrequesting'. The script I have below returns all orders for all accounts even though I have tried a wide variety of endings on the Sql search on the orders table.
The code may look a little untidy and to advanced programmers may look rough but I am still learning.....please can anyone help.
sub Page_Load(sender as Object, e as EventArgs)
'collecting the user identity Dim user as string user=request("User.Identity.Name")
'running a query to establish what account they have access to all from users table Dim accountrequesting as string accountrequesting = "select account_id from users where username = " & user & "'"
'opening the data source and connection Dim ConnectionString As String = "Data Source=xxxx;Database=xxxx;User ID=xxxxxxx;Password=xxxxxxx" Dim Myconn as New SQLConnection(connectionstring)
'this is the line I am stuck on, the account id in this orders table needs to match against the value gained in accountrequesting above tried a lot of different endings
Dim MySQL as string ="select prodshipped, account_id from orders"
I have a table consisting of two fields, OStype and OSversion, withentries like:OStypeOSversionsolaris 2.5solaris 2.6redhat 6.2redhat 6.2solaris 8redhat AS4solaris 10solaris 10redhat AS2.1redhat AS3redhat AS4I want to create a select statement that returns for each OS type, thetotal number of entries and for each version the total number ofentries.In the example the result would be:OStype OStype Count OSversion OSversion Countsolaris 52.5 12.6 18 110 2redhat 66.2 2AS2.1 1AS3 1AS4 2Thanks in advance for your help.Ian
Hi,I have a need to create a table detailing the ID of all contacts and thelast time they were contacted. This information is stored in 2 tables,'contact' and 'activity' (ID in the 'contact' table links to 'main_contact'in the 'activity' table).I guess I need some sort if iteration to go through each contact and findfind the last activity that took place against each of them (there many bemore than 1 activity against each contact) and then place the output valuesinto the new table.Can anyone show me how to go about this?Thanks!
I have a C# application that calls a stored procedure to query the database (MSSQL 2005). I only have one field/column returned from the query but I need that column ordered.
How do I use the ORDER BY clause without returning the index column which does the sorting? The first example is NOT what I want. I want something that works like the second example which only returns the 'Name' column.
ALTER PROCEDURE [dbo].[MyProcedure]
AS
BEGIN
SELECT DISTINCT A.Name, A.index
FROM ... ... ORDER BY A.[Index], A.Name ASC
END
ALTER PROCEDURE [dbo].[MyProcedure]
AS
BEGIN
SELECT DISTINCT A.Name FROM ... ... ORDER BY A.[Index]
Hello experts! After surfing this forum and reading many articles in the web I still haven't succeeded with a certain calculation that I need to perform. Thus I wanted to address you to ask if you could help me on this one:
I have 3 tables in a SQL Server 2005 DB and want to carry out the following calaculation:
[sum of lmenge/lmenge]* [1/werksmassFM]*p_transpreis
However, whatever I attempt to do, it seems to fail! I am quite a newbe and was never focussed with such comprehensive statements before. I would appreciate any help!
My statement looks like this so far (without the calculation):
SELECT l.bestellnr, l.lfsnr, l.ziel, l.lfs_refnr, l.lfsGuid, CONVERT(NUMERIC(9, 3), l.werksmassFM) AS werksmassFM, ls.lmenge, ls.pguid_ref, p.punktName, p.holzart, CONVERT(NUMERIC(9, 2), p.laenge) AS laenge, CONVERT(NUMERIC(9, 2), p.p_transpreis) AS p_transpreis FROM dbo.lfs AS l INNER JOIN dbo.lfs_sub AS ls ON l.lfsGuid = ls.lfsGuid_ref INNER JOIN dbo.polter AS p ON ls.pguid_ref = p.pguid WHERE (l.lfsnr LIKE '%1253')
The Column bDebit has either value '0' or value '1' in it ('0' being debit - positive amount, and '1' - credit, negative amount).
I would like it to show the net amount for each account. Therefore in plain English I would like to take all GLCodes that are the same (eg 6843000701) and sum all amounts that have debit value of '0' and subtract all amounts that have debit value '1'. Therefore I would only see '6843000701' code once, and the amount would be '0' becase 600 - 600 = 0.
The current query is: SELECT dbo.tbGLTransactions.lLedgerCode, dbo.tbGLTransactions.sGLCode, dbo.tbGLTransactions.bDebit, SUM(curAmount)As TotalSum, dbo.tbLedgerCode.sGLDesc FROM dbo.tbGLTransactions INNER JOIN dbo.tbLedgerCode on dbo.tbGLTransactions.lLedgerCode = dbo.tbLedgerCode.lLedgerCode WHERE dbo.tbGLTransactions.lGLExtractRun = '452' Group By dbo.tbGLTransactions.lLedgerCode, dbo.tbGLTransactions.sGLCode, dbo.tbGLTransactions.bDebit, dbo.tbLedgerCode.sGLDesc Order By dbo.tbGLTransactions.bDebit, dbo.tbLedgerCode.sGLDesc
Is someone able to help me as to how i need to modify this query to get the desired result?
Hi experts,I am working on SQL server 2005 reporting services and i am getting aproblem in writting a query.Situation is given below.There is one table in database Named ChildNow i have to find the All childrens whoes Age is 13 years Base onSome given parameter.If User select Augus 2007 then It has to calculate the Childs who bornin August 1994 And if he select September Then queryshould show only those child Who born in September 1994 and soon..... And use can select another year month also likeAugust 2009 ...I am writting the following querySelect Child_Name, DOb from Childwhere ((CONVERT(DateTime, A.Date_Of_Birth, 103) >= @ Parameter1And (CONVERT(DateTime, A.Date_Of_Birth, 103) <= @Parameter2)If i know already month and year then i can write easily parameter1and parameter2 But since these are comming from user so i m notfinding how to handle this.Now please suggest me what i have to write in Where statement I thinka lot but not getting any idea about it.Any help wil be appriciated.RegardsDinesh
Situation: In this stored procedure, I have to calculate in some manner: Font, FontSize, BoldText, ShowBox and number of characters to see how many lines it will take on a Crystal Report. Wondering if you have seen some like this on Web or have an ideas? Measurements(length, width) and character count seem appropriate. How about a function?
Hallo, for my project I need to calculate the estimated time an SQL statement will be running. The statements will be composed via a GUI and get become very complex. So the user needs a rough time approximation of how long to wait for the answer. A few seconds, ten minutes or several hours!?
My question: Does anyone know good links, articles or other source to get familiar with this topic? I'd also like to start a little discussion. On what does the runtime depend? (Is runtime the right word here?) Number of JOINs Number of different tables Size of the tables Complexity of the WHERE-Condition ("id=5" or rather "name LIKE '%foo%'") Indizes Hardware Number of users on the DBWhat else influences the time and how?
Aside from that I found the "SET SHOWPLAN_XML ON" for SQL Server 2005 to get the execution plan of the query.
But I can't find a reasonable listing of the elements and attributes and their meaning. I think StatementSubTreeCost sound interesting. Is that the time, the statement needs to run or what does the number 0.0033205 say?
Is anyone familiar with the SHOWPLAN_XML? Or can anyone provide me a link about the values of this plan, perhaps in the Microsoft-MSDN area!? The principle is clear. But what about the values in detail. There must be a documentation somewhere!
I am stuck in a situation where I want to use YTD for three different calendars of our company and don't want to create three different YTD calculations. However I want to make this work for any measure not for a particular measure
If I create one YTD and try to use in context of three calendars in SCOPE statements then it does not give my right results. Following is my syntax but It does not work.
I just read this article. The kind of select is called dirty read. So select with nolock might have inaccurate result...? PLEASE COMMENT ON THIS. I am using it to count some huge tables, and has problem on the result..
NOLOCK Using NOLOCK politely asks SQL Server to ignore locks and read directly from the tables. This means you completely circumvent the lock system, which is a major performance and scalability improvement. However, you also completely circumvent the lock system, which means your code is living dangerously. You might read the not-necessarily-valid uncommitted modifications of a running transaction. This is a calculated risk.
For financial code and denormalized aggregates (those little counters of related data that you stash away and try desperately to keep accurate), you should play it safe and not use this technique.
I want to calculate the Total_Cost and get the result of the formula from SELECT Query. Following is the formula of Total_Cost. I have declared variables for formula. How I can create SELECT/RUN query successfully for mathematical calculation to calculate the Total_Cost.
--Formula of Total_Cost = [min (price, Pay1) x Interest1 + max (min (price - Pay1, Pay2), 0) x Interest2 + Max ((price - Pay2), 0) x Interest3, LMT]
declare @price numeric(18,7)=255550 declare @Pay1 numeric(18,7)=645500 declare @Pay2 numeric(18,7)=235000 declare @Interest1 numeric(18,7)=0.05500 declare @Interest2 numeric(18,7)=0.03533 declare @Interest3 numeric(18,7)=1.00000 declare @LMT numeric(18,7)=10000.00 Select [min (@price, @Pay1) x @Interest1 + max (min (@price - @Pay1, @Pay2), 0) x @Interest2 + Max ((@price - @Pay2), 0) x @Interest3, LMT] as Total_Cost
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]);
Hi.. I need help with this SQL statement. I don't know if this can be done with a single SQL statement or not! I have two tables:
tblProducts: ProductID, ProductName tblOrders: OrderID, ProductID I need to "SELECT tblProducts.ProductName FROM tblProducts WHERE (tblProducts.ProductID is not in tblOrders Table)". Thanks in advance...
Hi, In my database i have a column Called EntryDates which has 'Jan1' etc.. stored in it. and i am getting an Xml object and in the object i am getting @EntryDAtesMatch and @EntryDatesPS and they have different months stored in it. So i want to insert into Entrydates or set the Entrydates as to whats been passed in following scenarios If @Entrydates is Not Null then i want @EntryDates to be set as @EntryDates. Suppose @EntryDates is NULL But @EntryDatesMatch OR @entryDAtesPS are not null then i want @EntryDates to be = @EntryDatesMatch..
Suppose is @EntryDates @EntryDatesMatch and null i want @EntryDates to be set as @EntryDatesPS... This is what ihave tried doing.. but doesnt work as the way i want it..
SET @EntryDates = CASE --When @EntryDatesMatch = NULL Then @EntryDates When @EntryDates = NULL and @EntryDatesMatch = NULL Then @EntryDatesPS When @EntryDates = NULL and @EntryDatesPS = NULL then @EntryDatesMatch End Any help will be appreciated.. Regards, Karen
I am having a heck of a time getting an UPDATE statement to work. Can anyone point out what it is I'm doing wrong? Here is my statement...... strSQL = "UPDATE tbl-Pnumber_list SET Project_Title = 'success' WHERE ID = @IDParam" Thanks! Eugh
I have a stored procedure that populates a table. I want to query the table using column names as variables, however the data is in decimal format so I am getting errors converting varchar to numeric. My table consists of columns labeled D1, D2, etc. for every possible day of any month, DOW1, DOW2, etc. for every day of the week. I also have values labeled midDpct and midDOWpct. My query is as follows:
select * from Table where D10 > midDaypct * 2 and DOW6 > 0 union select * from Table where DOW6 >midDOWpct * 2 and D10 > 0
We are targeting a specific day of the month and a specific day of week, day 10 and day of week 6 in this example. I want to make these variables so we can easily change out the target day and dow.
I have a table with two columns. empnum and empname. there are some data with same empnum but different empname. I just want to get the first empname for the same empnum. Is there a way to write a sql stetment to do this?
I need to issue an update statement to the ChronicOther field. If the range below is in the AnyDx field, then I want the ChronicOther field to say "Chronic", if not then I want it to say "Other".
where AnyDx between '25000' and '25091'
or AnyDx between '41400' and '41406'
or AnyDx between '42800' and '42890'
or AnyDx between '40100' and '40291'
or AnyDx between '34200' and '34292'
or AnyDx between '14000' and '19910'
or AnyDx between '49600' and '49600'
or AnyDx between '27240' and '27240')
My problem is what is the best way to look for this range (below) in the AnyDx field without multiple like statements?
example: Update reports.MDC_DRG SET ChronicOther = CASE When AnyDx like ('%,25000,%') Then 'Chronic' Else 'Other' etc., etc.....
I'm trying to get my feet wet with creating a Sql Server project within VS 2005 and I have the using statements below present but when I compile I get errors stating that SqlContext, SQLPipe, and SQLCommand do not exist in the current context. What am I missing? Under the references node I have 3 references present (System, System.Data, and System.XML). What am I missing?
I am looking to modify this Case Statement. Where it says ELSE '' I need it to display the actual contents of the cell. 1 = Yes , 0 = No, (any other integer) = actual value. Right now if the value is anything other than 1 or 0, it will leave the cell blank. CASE dbo.Training.TrainingStatus WHEN 1 THEN 'Yes' WHEN 0 THEN 'No' ELSE '' END AS TrainingStatus Thank You.
Hi, I am trying to insert the Source name and clientId to a table called clientSource...
The User will send in some Dbf File.... So in a particular file called PlanDbf.. I have the following fields PlanNumber, Name, SRC1Name, SRC2Name, SRC3Name.... SRC20Name 170234 Constructions Employee Deferral Employer Discretionary Employer Matching....
And in another table called SourceDBF i have the following fields with data PlanNumber PARTID SOURCE_NUM etc... 170234 123456789 1 170234 123456789 3 170234 451231334 1 So how do i match the Source_NUM with SRCnames when i insert it into the table.. INSERT INTO Statement..ClientSource(@ClientId, SourceName)SELECT s.SOURCE_NUM FROM SourceDBF .. but i am stuck.. any help will be appreciated. Regards Karen
In my Database, I do not have a [Last Visit Date]. I have had to pull it by doing the following:
(select top 1 visit from patientvisit pv where visit >= ISNULL(NULL,'1/1/1900') and visit < dateadd(d, 1,ISNULL(NULL,'1/1/3000')) AND pp.patientprofileid = pv.PatientProfileID and datediff(day, getDate(), visit) < 0 order by visit desc) as [Last Visit Date]
My client would like to have a listing of patients with a visit within the past 2 years and without a visit in the past 2 years. What I would like to do is have a case statement that evaluates like:
Case When dateadd(y,[LastVisitDate],getdate())<2 then 'Less Than 2' When dateadd(y,[LastVisitDate],getdate())>=2 then '2 or more' else 'No detected visit' END
So basically, either your in 2 yrs or your not.
My Current Query:
/* Patient List*/ SET NOCOUNT ON
DECLARE @Zip varchar(40) SELECT @Zip = LTRIM(RTRIM('NULL')) + '%'; WITH cteMedlitsPatientStatus AS ( SELECT * FROM Medlists WHERE TableName = 'PatientProfileStatus' )
SELECT PatientID, RespSameAsPatient=isnull(PatientSameAsGuarantor,0), PatientName=CASE WHEN RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix,'')) <> '' THEN RTRIM(RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix,'')) + ', ' + ISNULL(pp.First,'') + ' ' + ISNULL(pp.Middle,'')) ELSE RTRIM(ISNULL(pp.First,'') + ' ' + ISNULL(pp.Middle,'')) END, PatientAddr1=pp.Address1, PatientAddr2=pp.Address2, PatientCity=pp.City, PatientState=pp.State, PatientZip=pp.Zip, PatientRespName=CASE WHEN RTRIM(pr.Last + ' ' + ISNULL(pr.Suffix,'')) <> '' THEN RTRIM(RTRIM(pr.Last + ' ' + ISNULL(pr.Suffix,'')) + ', ' + ISNULL(pr.First,'') + ' ' + ISNULL(pr.Middle,'')) ELSE RTRIM(ISNULL(pr.First,'') + ' ' + ISNULL(pr.Middle,'')) END, PatientRespAddr1=pr.Address1, PatientRespAddr2=pr.Address2, PatientRespCity=pr.City, PatientRespState=pr.State, PatientRespZip=pr.Zip, FinancialClass=isnull(ml.Description,'none'), Doctor=df.ListName,Facility=df1.OrgName,Balance=isnull(ppa.PatBalance,0)+isnull(ppa.InsBalance,0), pp.DeathDate, Status = ml1.Description, pp.BirthDate, (select top 1 visit from patientvisit pv where visit >= ISNULL(NULL,'1/1/1900') and visit < dateadd(d, 1,ISNULL(NULL,'1/1/3000')) AND pp.patientprofileid = pv.PatientProfileID and datediff(day, getDate(), visit) < 0 order by visit desc) as [Last Visit Date]
FROM PatientProfile pp LEFT JOIN PatientProfileAgg ppa ON pp.PatientProfileID = ppa.PatientProfileID LEFT JOIN Guarantor pr ON pp.GuarantorID = pr.GuarantorID LEFT JOIN MedLists ml ON pp.FinancialClassMID = ml.MedListsID LEFT JOIN DoctorFacility df ON pp.DoctorID = df.DoctorFacilityID LEFT JOIN DoctorFacility df1 ON pp.FacilityId = df1.DoctorFacilityID LEFT JOIN cteMedlitsPatientStatus ml1 ON pp.PatientStatusMId = ml1.MedlistsId
I have a case statement that provides filtering of hours during certain days of the week. An example is the data I want to show on Sunday is different from the rest of the week. I am using....
Code Snippet
WHERE ((CASE WHEN Datepart(dw, TestDateTime) = 1 AND datepart(hh, TestDateTime) BETWEEN 8 AND 22 THEN 1 WHEN Datepart(dw, TestDateTime) >= 2 AND datepart(hh, TestDateTime) BETWEEN 6 AND 23 OR datepart(hh, TestDateTime) BETWEEN 0 AND 2 THEN 1 ELSE 0 END) >= @ShowCore)
Esentially it gives a parameter (@showcore) to where it shows the filtered hours when 1 is selected, and all hours if 0 is selected.
Basically, Sunday I want to show transaction from between 8am and 10pm, All other days would be 12am - 2am and 6am to 11:59:59 when selecting 1 as the parameter.
