COUNT Depending On Data
Jul 20, 2005
Hello.
I have one serious problem with COUNT in TSQL.
I use MS SQL Server 2000.
I would like to count rows depending on data in it.
What I meat is:
I have fields like:
region | month | year | some_count |
-------|-------|------|-------------|
LA | 1 | 2003| 4 |
LA | 2 | 2003| 2 |
LA | 3 | 2003| 1 |
LA | 4 | 2003| 6 |
VV | 1 | 2003| 3 |
VV | 2 | 2003| 7 |
VV | 4 | 2003| 20 |
VV | 6 | 2003| 3 |
BB | 2 | 2002| 1 |
etc...
And what I would like to get from it is:
region | jan2003 | feb2003 | mar2003 | apr2003 | may2003 | june2003 | etc...
-------|----------|---------|-----------|----------|----------|-----------|-
-
LA | 4 | 2 | 1 | 6 |
0 | 0 | 0
VV | 3 | 7 | 0 | 20 | 0
| 3 | 0
BB | 0 | 1 | 0 | 0 |
0 | 0 | 0
etc...
Is it possible to do it in views? if yes then how?
I could use temp tables for each month but it is not solution when I have 3
years
and more later then it will be round 40 temporary tables.
Thanks for any response.
Regards,
Casper
View 3 Replies
ADVERTISEMENT
Jun 25, 2013
I need to update the result depending upon the count of distinct entries.
Example
ID Employee Region State
1 ABC AMEA MI
2 DEF AMEA MI
3 XYZ APAC TX
I want the result as below
ID Employee Region State
1 ABC AMEA MI-1
2 DEF AMEA MI-2
3 XYZ APAC TX
since the count of Region is 2
I tried using DECLARE @intFlag INT and stuff but wasn't able to get the solution.
View 2 Replies
View Related
Nov 2, 2007
Hi,
I know it sounds very simple with the subject line but for me it's not.
BACK GROUND
My Client is using Crystal Reports 8.5 and SQL Server.
a View is bound to this report and they use Select Expert of CR 8.5 IDE to generate Reports manually.
now there are 3 tables in Question
Billing_Revenue, Billing_Revenue_History and Billing_Revenue_Revision
and the Fields in Question are Interstate_Revenue and International_Revenue.
Every end of the Quarter the Billing_Revenue data is moved to Billing_Revenue_History.
Billing_Revenue_Revision has the revised data if the "Filer" has submited the changed/Revised Data again to the Company.
Bill Run = is the Process they call when they generate the Reports for a perticular Billing Period.
they do it every month. But sometimes they need to generate Invoices for the OLD Periods.
if we put the problem in simple words,
they want to get the Revenue Columns from Billing_Revenu if they run the "Bill Run" in the Current Quarter (Billing Cycle),
else if the Billing Cycle is older than current Quarter (cycle), then they want to first check in the Revision Table and if that Filer has submitted the revised data in that Cycle then get it from there
else
look into History Table and if the filer is found there for that cycle than get the data from there
here's the requirement given to me by my system Analyst.
User enters list of filer ID=USER_FILER_ID and cycle ID=USER_CYCLE_ID
Get CURRENT_CYCLE= Max CYCLE_ID from Billing_Cycle
Get USER_PERIOD_ID = Period ID from Billing_Cycle using USER_CYCLE_ID
Get PERIOD_FIRST_CYCLE= Min Cycle ID for USER_PERIOD_ID from Billing_Cycle
For each USER_FILER_ID
If USER_CYCLE_ID=CURRENT_CYCLE
Pull revenue from BILLING_REVENUE /*Process stops here*/
ELSE
{
If USER_PERIOD_ID exists in BILLING_REVENUE_REVISIONS BRR for USER_FILER_ID
Get the max (cycle_id) from BRR to pull revenue columns with most recent filing of USER_PERIOD_ID from BRR /*one filer may have more than one revision for the same period id*/
ELSE
IF PERIOD_FIRST_CYCLE exists in BILLING_REVENUE_HISTORY for USER_FILER_ID
Pull revenue information
}
here's the View being used which is the First Case which is happening currently
SELECT dbo.BILLING_PROVIDER.CONT_NAME_FIRST, dbo.BILLING_PROVIDER.CONT_NAME_LAST, dbo.BILLING_PROVIDER.ADD_STREET_LINE2,
dbo.BILLING_PROVIDER.ADD_STREET_LINE1, dbo.BILLING_PROVIDER.ADD_STREET_LINE3, dbo.BILLING_PROVIDER.ADD_CITY,
dbo.BILLING_PROVIDER.ADD_STATE, dbo.BILLING_PROVIDER.ADD_ZIP, dbo.BILLING_PROVIDER.CONTACT_TEL,
dbo.BILLING_SUMMARY.INVOICE_NUM, dbo.BILLING_SUMMARY.BALANCE_DUE, dbo.BILLING_SUMMARY.PREVIOUS_BALANCE,
dbo.BILLING_SUMMARY.SUM_TRANS, dbo.BILLING_SUMMARY.M_BASE, dbo.BILLING_SUMMARY.M_CONTRIBUTION,
dbo.BILLING_SUMMARY.ADJ_Q_CONTRIBUTION_BASE, dbo.BILLING_SUMMARY.EST_Q_CONTRIBUTION,
dbo.BILLING_SUMMARY.PRE_DM_ADJ_Q_CONTRIBUTION_BASE, dbo.BILLING_SUMMARY.Q_CONTRIBUTION_BASE,
dbo.BILLING_SUMMARY.Q_COMB_REVENUE, dbo.BILLING_SUMMARY.Q_LIRE_PERCENATAGE, dbo.BILLING_SUMMARY.Q_LIRE_STATUS,
dbo.BILLING_SUMMARY.A_DM_STATUS, dbo.BILLING_SUMMARY.A_COMB_REVENUE, dbo.BILLING_SUMMARY.Q_DM_STATUS,
dbo.BILLING_SUMMARY.A_LIRE_PERCENATAGE, dbo.BILLING_SUMMARY.A_LIRE_STATUS, dbo.BILLING_DETAILS.TRANSACTION_TYPE,
dbo.BILLING_DETAILS.LINE_ITEM_TYPE, dbo.BILLING_DETAILS.SUPPORT_MECHANISM_TYPE, dbo.BILLING_DETAILS.LINE_ITEM_NUMBER,
dbo.BILLING_DETAILS.LINE_ITEM_AMT, dbo.BILLING_DETAILS.TRANSACTION_DATE, dbo.BILLING_DETAILS.REASON_CODE,
dbo.BILLING_CYCLE.STATEMENT_DT, dbo.BILLING_CYCLE.PAYMENT_DUE_DT, dbo.BILLING_CYCLE.MAILING_DT,
dbo.BILLING_REVENUE.INTERSTATE_REVENUE, dbo.BILLING_REVENUE.INTERNATIONAL_REVENUE, dbo.BILLING_SUMMARY.CYCLE_ID,
dbo.BILLING_SUMMARY.FILER_ID, dbo.BILLING_PROVIDER.CARRIER_NAME, dbo.BILLING_CYCLE.PERIOD_ID,
dbo.BILLING_PERIOD.PERIOD_TYPE_ID, dbo.ACCT_LINE_ITEM_TYPE.LINE_ITEM_DESCR, dbo.BILLING_PERIOD.FCC_PERCENTAGE_ACTUAL,
dbo.BILLING_PERIOD.FCC_PERCENTAGE_EST, dbo.BILLING_PERIOD.HC_PERCENTAGE, dbo.BILLING_PERIOD.SL_PERCENTAGE,
dbo.BILLING_PERIOD.RHC_PERCENTAGE, dbo.BILLING_PERIOD.LI_PERCENTAGE, dbo.BILLING_SUMMARY.U_CONTRIBUTION_BASE,
dbo.BILLING_PERIOD.FCC_CIRCULARITY_FACTOR, dbo.BILLING_SUMMARY.A_LIRE_REVENUE, dbo.BILLING_SUMMARY.ANNUAL_BASE,
dbo.BILLING_REVENUE.RECEIVED_DT
FROM dbo.ACCT_LINE_ITEM_TYPE RIGHT OUTER JOIN
dbo.BILLING_DETAILS RIGHT OUTER JOIN
dbo.BILLING_PERIOD INNER JOIN
dbo.BILLING_CYCLE INNER JOIN
dbo.BILLING_SUMMARY ON dbo.BILLING_CYCLE.CYCLE_ID = dbo.BILLING_SUMMARY.CYCLE_ID ON
dbo.BILLING_PERIOD.PERIOD_ID = dbo.BILLING_CYCLE.PERIOD_ID ON dbo.BILLING_DETAILS.FILER_ID = dbo.BILLING_SUMMARY.FILER_ID AND
dbo.BILLING_DETAILS.CYCLE_ID = dbo.BILLING_SUMMARY.CYCLE_ID ON
dbo.ACCT_LINE_ITEM_TYPE.LINE_ITEM_TYPE = dbo.BILLING_DETAILS.LINE_ITEM_TYPE LEFT OUTER JOIN
dbo.BILLING_PROVIDER LEFT OUTER JOIN
dbo.BILLING_REVENUE ON dbo.BILLING_PROVIDER.FILER_ID = dbo.BILLING_REVENUE.FILER_ID ON
dbo.BILLING_SUMMARY.FILER_ID = dbo.BILLING_PROVIDER.FILER_ID
WHERE (dbo.BILLING_SUMMARY.FILER_ID NOT IN
(SELECT DISTINCT BILLING_DETAILS.FILER_ID
FROM dbo.BILLING_DETAILS
WHERE (dbo.BILLING_DETAILS.CYCLE_ID = dbo.BILLING_CYCLE.CYCLE_ID) AND (BILLING_DETAILS.LINE_ITEM_TYPE IN ('SLDADJ', 'SLDC',
'RHCDC', 'RHCDADJ'))))
Current Situation.
1. They use CR 8.5 IDE to enter the parameters
and this version of CR doesn't allow using Stored PRocedures.
2. I'm not very good with the Database side and Complex Queries always leave me in the middle of nowhere.
But i really really need to do this
3. please give me some clue.. some idea how to resolve this.
here's the primary database diagram
http://deepak.palkar.googlepages.com/Billing2.jpg
thanks a lot..
Deepak
View 3 Replies
View Related
Sep 10, 2007
Hi Guys,
I'm wondering if an idea I'm playing with is feasible and if so, how you would recommend implementing it.
Let's say I have a Dictionary table, 2 columns:
Word | Definition
And I have a string - "The cat sat on the dog"
If there's a definition for "cat" in the dictionary table, I want to alter the string so it becomes "The >>cat<< sat on the dog"
At the same time, if there's also a definition for "dog" then my string now becomes "The >>Cat<< sat on the >>Dog<<"
The idea being that when I manipulate the data in my ASP I can replace() the >><< with specific HTML code. (I'm trying to recreate the "in text" advertising thing that lots of people seem to be using - but not doing adverts, just information for our users - Someone hovers over a highlighted word, and with a little bit of Ajax, I can pull the definition out...
I'm not sure (but I'm suspecting) that it would make more sense to do this as I'm storing the string in a table, rather than as I'm pulling it out ready for use (don't want to be slowing my end users down )
Any ideas?
Thanks in advance
-Craig
View 4 Replies
View Related
Jul 15, 2004
Hi there,
Can anyone help? I currently have this query that imports distinct data into a prices table with a couple of contraints. This table became to large so I have now split this down into yearly tables (dbo.price --> dbo.price2001, dbo.price2002 etc). I get the data each day in another table that may contain data for different years so I need to be able to look at this data and insert into the right yearly tables.
E.g. present query:
Insert Prices
select distinct M.ids, C.zdateT, 1.0,0 from mapid as M,datacorrect as C
where M.asset = 'money'
and C.zDate not in (select zdate from price where sid >=15)
So I need to run this query for each yearly table with a date listed in the datacorrect table (insert prices(yr) yr = 2002,2003,2004 etc)
Any ideas would be appreciated!!
Thanks
S
View 3 Replies
View Related
Feb 14, 2008
Hello,
I have a matrix report where I drill down from a business' divisional level to an office level. A subreport is generated by clicking on the relevant data field. However, when at divisional level, clicking on the data field simply generates the data from the first office row. I want it so that when the column is collapsed to divisional level, it shows the data for the entire division.
So... is there any way I can write an expression that creates different subreports based on whether a matrix column is expanded or collapsed?
Cheers,
Peter Marshall
View 4 Replies
View Related
Feb 26, 2015
I am writing a query to return some production data. Basically i need to insert either 1 or 2 rows into a Table variable based on a decision as to does the production part make 1 or 2 items ( The Raw data does not allow for this it comes from a look up in my database)
I can retrieve all the source data i need easily but when i come to insert it into the table variable i need to insert 1 record if its a single part or 2 records if its a twin part. I know could use a cursor but im sure there has to be an easier way !
Below is the code i have at the moment
declare @startdate as datetime
declare @enddate as datetime
declare @Line as Integer
DECLARE @count INT
set @startdate = '2015-01-01'
set @enddate = '2015-01-31'
[Code] .....
View 1 Replies
View Related
Aug 6, 2006
With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("", MyConnection) Dim i As Integer Dim fBeginTransCalled As Boolean = False
'messagetype 1 =internal messages Try ' ' Start transaction ' MyConnection.Open() cmd.CommandText = "BEGIN TRANSACTION" cmd.ExecuteNonQuery() fBeginTransCalled = True Dim obj As Object For i = 0 To MessageIDs.Count - 1 bSuccess = False 'delete userid-message reference cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID" cmd.Parameters.Add(New SqlParameter("@UserID", UserID)) cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() 'then delete the message itself if no other user has a reference cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1" cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString)) obj = cmd.ExecuteScalar If ((Not (obj) Is Nothing) _ AndAlso ((TypeOf (obj) Is Integer) _ AndAlso (CType(obj, Integer) > 0))) Then 'more references exist so do not delete message Else 'this is the only reference to the message so delete it permanently cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2" cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() End If Next i
' ' End transaction ' cmd.CommandText = "COMMIT TRANSACTION" cmd.ExecuteNonQuery() bSuccess = True fBeginTransCalled = False Catch ex As Exception 'LOG ERROR GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message) Finally If fBeginTransCalled Then Try cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection) cmd.ExecuteNonQuery() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
View 5 Replies
View Related
Dec 29, 2014
my data is like this.
---------code----------------name
ACT706022774CCI0---------CCI
ACT706022774CCI0---------CCI
CDF892782322CCI4---------CCI
CDF892782322CCI4---------CCI
CDF892782322CCI4---------CCI
i want a query that show data like this.
---------code--------------------name
---------ACT ----------------------2
---------CDF-----------------------3
i want to count ACT code and cdf code
View 2 Replies
View Related
Jul 3, 2014
I have a table having code
code
12345678
21345678
45789612
12345678
21345678
21345678
12347586
I want result on the basis of first 2 digit the repeatation of code is count as 1.
code count
12 2
21 1
45 1
View 4 Replies
View Related
Oct 8, 2007
Hello,
I am very new to SQL and ran into a problem using Access. I hope you can help me here.
The question is the following:
I have to tables:
Table 4x4: CommodityCode(string)/NeedofBU(high,low)
Table ASDBComplete: CommodityCode/Manufacturer/Rating(red,green,yellow)
What I want to get as a result is to display all ComodityCodes that have a 'red' rating and a 'high' NeedOfBU. So far no problem. But now there usually is more than one manufacturer for one CommodityCode. What I need now is that the CommodityCode is not displayed if there actually exist a Manufacturer with 'green' or 'yellow' rating.
How would you do that?
What I have so far is:
SELECT [4x4].[CommodityCode], [4x4].NeedofBU, ASDBComplete.CommodityCode, ASDBComplete.Rating, ASDBComplete.Manufacturer
FROM 4x4 LEFT JOIN ASDBComplete ON [4x4].[CommodityCode] = ASDBComplete.CommodityCode
WHERE ((([4x4].NeedofBU)="high") AND ((ASDBComplete.Rating)="red"));
But this gives me all the Commodities with red ratings. Even if there is a supplier with a green or yellow rating.
I would need somting like:
if exists 'commodityCode.XY with 'manufacuturer rating = green OR yellow' do not display commodityCode.xy
I really appreciate your help
View 8 Replies
View Related
Feb 25, 2008
hello
sorry to distub you again
in fact i'm a beginner and i don't really see how i can do some tasks
when i display a report i want the reader to choose a client, and when he chooses a client, he can choose in the second parameter list, the missions that correspond to THIS client
so i have 3 datasets
one in which i get all my data needed
and i have a filter in where part of query
Code Snippet
where client=@client
and mission=@mission
and i filter on these 2 values
in the second dataset , i gather all the clients i have by doing this query
Code Snippet
select distinct name from client
and i have this third dataset where i gather all the missions corresponding to one client
and here i write
Code Snippet
select mission_label from client
where name=@client
the two fields are in the same time and in this third dataset i apply a filter on Parameters!client.value
but when i execute the report, the second parameter doesn't work, it gives me no choice of mission for a client, it doesn't return anything while when i execute the query in the dataset i have these values
how can i do this please?
thanks a lot in advance
and sorry or bothering with such questions
View 4 Replies
View Related
Jan 16, 2007
I have x amount of tests for a person on a particular day. There are criteria to invalidate a test. I can filter all the bad tests away, but now I need to only accept 2 valid tests per day, even if there are more tests. The tests are filtered according to person ID and then put in order by date. I need to accept the first 2 valid test based on the criteria and reject the others. Any suggestions
View 2 Replies
View Related
Jan 29, 2014
I am trying to get counts of contacts.
So how do I subset, to gain a the accounts with the products then query to gain the contact counts from those accounts?
But for some reason it's giving me counts of products.
select distinct
a.region__c as [Region],
a.SFTX_ID as [SFTRX ID],
count(c.Contact_ID) as [Contact Count]
from vwContact c
[Code] .....
View 1 Replies
View Related
Aug 21, 2015
I want to know that how many ways we have to count the data in sql.
I know only one way..that is. using count keyword.. are their any ways to find out the other ways..
View 2 Replies
View Related
Apr 30, 2004
I'm trying to execute a different SELECT statement depdning on a certain condition (my codes below). However, Query Analyzer complains that 'Table #Endresult already exists in the database', even though only one of those statements would be executed depending on the condition. Any ideas as to a work around? I need the result in an end temporary table.
IF @ShiftPeriod = 'Day'
SELECT * INTO #EndResult FROM #NursesAvailable WHERE CanWorkDays = 1
ELSE IF @ShiftPeriod = 'Night'
SELECT * INTO #EndResult FROM #NursesAvailable WHERE CanWorkNights = 1
ELSE IF @ShiftPeriod = 'Evenings'
SELECT * INTO #EndResult FROM #NursesAvailable WHERE CanWorkEvenings = 1
ELSE
SELECT * INTO #EndResult FROM #NursesAvailable
View 1 Replies
View Related
Apr 23, 2008
Would like to have Identity Inserted wrt a date column
Eg:
ProdDate ID Details
============================
2008.04.01 1 afafafaf
2008.04.01 2 GAFSGHFGF
2008.04.02 1 GAGJAGSDH
2008.04.02 2 QYTYTT
2008.04.03 3 QYTWRRT
At present it is not an Auto increment , I check for the Maximum ID and add One to the next Inserted record. Is there any simpler method?
View 13 Replies
View Related
Jun 4, 2008
I want to insert the rows automatically depending on the cell value in column from another table.
Like if the value of cell "blabla" is 4 it automatically insert the 4 rows in my table with values.
Is it possible in TSQL?
View 13 Replies
View Related
Oct 5, 2007
Anybody knows how to apply an inflation factor depending on the date to a measure
The Inflation Factor is (Current Month Rate / X Month Rate)
So lets say for current year the rates are
Jan 121.64
Feb 121.98
March 122.244
April 122.171
For example
If I want to calculate for March I would show a column for January February and March, the column for January would be multiplied by (122.244/121.64) the column for February would be multiplied by (122.244/121.98) and march by one (122.244/122.244)
But in April the factors would be different I would have 4 columns, January February March and April, January amounts would be multiplied by (122.171/121.64) February by (122.171/121.98) March by (122.171/122.244) and April by one (122.171/122.171)
Im using SSAS 2005
Thanks in advance
Isaac
View 1 Replies
View Related
Jul 23, 2005
Hello all,I have two tables - Projects and ProjectStructTable Projects contains master records of the projects, ProjectStructallows to define a project herarchie and contains the fieldsPrjStructId, ProjectId, PrjStructName, ..., ParentIdPrjStructParent contains a reference to the parent or to itselves ifrecord is top-level-record for a project.I try to create a trigger on table Projects (INSERT) whichautomatically creates the top-level-entry in ProjectStruct but Ididn't succed.Tried to use (several variations similar to)INSERT INTO ProjectStruct (ProjectId, PrjStructName, ParentId)SELECT prjProjectId, 'top-level',IDENT_CURRENT('ProjectStruct'))FROM INSERTEDbut this inserts a reference to the last inserted record. Why thishappens is pretty clear to me, but I found no way to get the referenceto the identity column of the record currently inserted.Is there a way to do this?
View 7 Replies
View Related
Dec 3, 2007
In a package we have statements pointing to 2(or more) different databases on the server. When moving between environments, is there an easy way to change statements like:
UPDATE t1
SET t1.name = t2.name,
t1.age = t2.age
FROM DB1..Person t1
INNER JOIN DB2..PersonToo t2
ON t1.PersonID = t2.PersonID
I can think only of building the statements replacing the database names with variables, but that's not an easy way. I do not know how to use package variables in this situation.
1 way of doing this may be by using a Lookup or Conditional Split and use the resulting dataflow in a SP or such to update, using parameters...
It all sounds very messy, and I still don't know how
Any ideas?
TIA,
View 7 Replies
View Related
Apr 14, 2008
Is it possible to merge cells within several columns depending on the row number?
e.g.
colA colB colC colD
1 x a b c
2 y -------------------------
3 z -------------------------
for row # 2, #3, colB colC colD are merged.
View 7 Replies
View Related
May 20, 2006
Hi all, I am trying to sum a column into different variables depending on another column. Let me explain my self better with an example
DECLARE @Initial decimal(18,2), @incomings decimal(18,2), @outgoings decimal(18,2)
SELECT
@initial = CASE WHEN type = 1 THEN SUM(amount) END,
@incomings = CASE WHEN type = 2 THEN SUM(amount) END,
@outgoings = CASE WHEN type = 3 THEN SUM(amount) END,
FROM Transactions
WHERE date = '05/14/2006' AND STATION = 'apuyinc'
GROUP BY type, amount
What I am trying to do is to sum all of the incomings transactions into @incomings, all of the outgoing transactions into @outgoings and the initial transaction into @initial where
The incoming transactions is type 2,
outgoing transactions is type 3
Thanks for the help
@puy
View 5 Replies
View Related
Sep 26, 2007
Hi all,
I have a table with artikels and count, sample:
Art Count
------------
12A 3
54G 2
54A 4
I would like to query this table and for each 'count' retrieve one row:
query result:
Art Count
------------
12A 3
12A 3
12A 3
54G 2
54G 2
54A 4
54A 4
54A 4
54A 4
Is this possible?
Thanks, Perry
View 3 Replies
View Related
Apr 17, 2008
Hello All,
I have an Execute SQL Task and a SendMail in the control flow of my package.
If the Execute SQL Task inserts records in the Database, I want the SendMail task to be executed.
But if Execute SQL Task does not inserts even a single record in the Database, I dont want the SendMail task to be executed.
How can I achieve this...
Thanks,
Kapadia Shalin P.
View 1 Replies
View Related
Nov 10, 2004
Hi all,
I'm in a situation where I need to accumulate values from several columns in each of the rows returned in a result set and to then display each accumulation value for each row at the end of each row.
For example, if I had the following...
------------------------------------------------------
name - car - van - bus - total
------------------------------------------------------
Terry - 55 - 34 - 12 - 101
John - 01 - 23 - 05 - 029
etc etc
------------------------------------------------------
Where 'total' column is an accumuliation of the 'car', 'van', and 'bus' columns. The values for the columns 'car', 'van', and 'bus' are all values that have been derived from COUNT function. So what I want is to somehow genarate the values for the 'total' column (car + van + bus).
Now I have tried the following SQL, but to no avail
Code:
SELECT pveh.csign, pveh.name1 AS [Driver], COUNT(pveh.name1) AS Car, COUNT(pveh.name2) AS Van, SUM(COUNT(pveh.name1) + SUM(COUNT(pveh.name2))
FROM planvehicles AS pveh
... and I also tried...
SELECT pveh.csign, pveh.name1 AS [Driver], COUNT(pveh.name1) AS Car, COUNT(pveh.name2) AS Van, SUM(Car + Van)
FROM planvehicles AS pveh
Thanks
Tryst
View 12 Replies
View Related
Nov 8, 2007
Hi,
I have one data flow control. Source is SQL server and destination is flat file destination. I have one derived column placed in between these two. This functionality works fine. I would like to sum one column data and count total no. of columns and put it in global variable. How can I achieve it?
Thanks,
View 7 Replies
View Related
Apr 6, 2006
Hello,Is it possible to set a comparison operator using a parameter value?The code below shows what I'm after;declare @co char(1)declare @date datetimeset @co = '<'set @date = '02/02/2002'select * from recipe where date @co @dateI would use an if statement perform two seperate statements depending on the value of co, but this is only one of 13 statements where i need to have different combinations of comparision operators.thanks
View 2 Replies
View Related
Jul 27, 2004
Hi there,
Is there a quick way to list all the tables in a DB that contain a certain column name?
Thanks
S
View 5 Replies
View Related
Sep 30, 2015
I need to build an update query for all my article beginning with '0.%' for my varchar field refkey, but depending on some conditions.
For example.
Case st.base <> '' and st.qttbase <> 0
Theo refkey = B
Case st.cos <>'' and st.qttcos <> 0
Then refkey = C
Case st.refo <> '' and st.qttrefo <> 0
Then refkey = R
But my problem are That i can have Many combination on refkey, for example:
B,BC,BCR,C,CB,CBR,R...and so on.
Also i need to separate the letters with comma, like:
B
B,C
B,C,R
.....
View 6 Replies
View Related
Apr 21, 2008
H, need help please!
I have two tables on a reports, each having it's own dataset.
I want to set the visibility of the table depending on the filter selection.
So if the user selects: show open then show only the table with the open dataset.
I don't now how to this using the expression in the visibility expression.
Please Assist!
Regards
View 2 Replies
View Related
Feb 26, 2007
Hi there,
I'm having trouble inserting a conditional format to a specific column.
e.g.: Matrix within the rows the "weeks" (1, 2, 3, ... , 52), and in the column a "lastyear revenu", "thisyear revenue" and a difference between them, "delta %", in percent grouped by the stores.
I added a subtotal to it so I get in the latest column the "total lastyear revenue", the "total thisyear revenue" and a difference between them in percent for all stores, "total delta %", for a specific week.
Problem: I want to colour the "delta %" column green when it is greater then the "total delta %" value.
I thougt this would be quite easy, but it really is a pain in the *** because, in the background expression dialog box, I can't refer to the subtotal cells ...
View 3 Replies
View Related
Jul 5, 2007
Hi, Everyone.
i am wondering if there is some way to hide a table row depending on the page number.
I have tried to find a way to access the global page number to use it in an expression for the visibility property on the table row, but i havent found anything useful.
View 14 Replies
View Related
