SQL Server 2012 :: Data Grouping On 2 Levels But Only Returning Conditional Data
May 7, 2014
I think I am definitely thrashing and am not getting anywhere on something I think should be pretty simple to accomplish: I need to pull the total amounts for compartments with different products which are under the same manifest and the same document number conditionally based on if the document types are "Starting" or "Ending" but the values come from the "Adjust" records.
So here is the DDL, sample data, and the ideal return rows
CREATE TABLE #InvLogData
(
Id BIGINT, --is actually an identity column
Manifest_Id BIGINT,
Doc_Num BIGINT,
Doc_Type CHAR(1), -- S = Starting, E = Ending, A = Adjust
Compart_Id TINYINT,
[Code] ....
I have tried a combination of the below statements but I keep coming back to not being able to actually grab the correct rows.
SELECT DISTINCT(column X)
FROM #InvLogData
GROUP BY X
HAVING COUNT(DISTINCT X) > 1
One further minor problem: I need to make this a set-based solution. This table grows by a couple hundred thousand rows a week, a co-worker suggested using a <shudder/> cursor to do the work but it would never be performant.
View 9 Replies
ADVERTISEMENT
Oct 6, 2015
I have query on grouping data on weekly basis..
1. Week should start from Monday to Sunday
2. It should not consider current week data(suppose user clicks on report on Tuesday, it should display the data for last week).
3. I want output like below
Week1,week2,week3..... week12,AverageofWeek
12 , 10 ,0.........0 12
View 1 Replies
View Related
Oct 13, 2014
A DB2 store procedure returns two data sets, when executed from SSMS, using linked server. Do we have any simple way to save the two data sets in two different tables ?
View 1 Replies
View Related
Mar 23, 2014
I have a datagridview bound to a table that is part of an Entity Framework model. A user can edit data in the datagridview and save the changes back to SQL. But, there is a stored procedure that can also change the data, in SQL, not in the datagridview. When I try to "refresh" the datagridview the linq query always returned the older cached data. Here's the code that I have tried using to force EF to pull retrieve new data:
// now refresh the maintenance datagridview data source
using (var context = new spdwEntities())
{
var maintData =
from o in spdwContext.MR_EquipmentCheck
where o.ProdDate == editDate
orderby o.Caster, o.Strand
select o;
mnt_DGV.DataSource = maintData;
}
When I debug, I can see that the SQL table has the updated data in it, but when this snippet of code runs, maintData has the old data in it.
View 0 Replies
View Related
Feb 26, 2014
How do I get my data to show starting at the first row instead of skipping down?
Refer to the attachment.
Code:
CREATE PROCEDURE [dbo].[uspReportData]
-- Add the parameters for the stored procedure here
@Metric1 as varchar(50) = NULL, @Metric2 as varchar(50) = NULL, @Metric3 as varchar(50) = NULL, @Metric4 as varchar(50) = NULL,
@Metric5 as varchar(50) = NULL, @Metric6 as varchar(50) = NULL, @Metric7 as varchar(50) = NULL, @Metric8 as varchar(50) = NULL,
[code].....
View 1 Replies
View Related
Mar 30, 2006
I'm having trouble getting a FOR XML query to get the relationships correct when there are 3 levels of data.
In this example, I have 3 tables, GG_Grandpas, DD_Dads, KK_Kids. As you would expect, the Dads table is a child of the Grandpas table, and the Kids table is a child of the Dads table.
I'm using the Bush family in this example, these are the relationships:
- George SR
--- George JR
------ Jenna
------ Barbara
--- Jeb
------ Jeb JR
------ Noelle
These statements will create and populate the tables for the example with the above relationships:
SET NOCOUNT ON
DROP TABLE KK_Kids, DD_Dads, GG_Grandpas
CREATE TABLE GG_Grandpas ( GG_Grandpa_Key varchar(20) NOT NULL, GG_GrandpaName varchar(20))
CREATE TABLE DD_Dads ( DD_Dad_Key varchar(20) NOT NULL, DD_Grandpa_Key varchar(20) NOT NULL, DD_DadName varchar(20))
CREATE TABLE KK_Kids ( KK_Kid_Key varchar(20) NOT NULL, KK_Dad_Key varchar(20) NOT NULL, KK_KidName varchar(20))
ALTER TABLE GG_Grandpas ADD CONSTRAINT PK_GG PRIMARY KEY (GG_Grandpa_Key)
ALTER TABLE DD_Dads ADD CONSTRAINT PK_DD PRIMARY KEY (DD_Dad_Key)
ALTER TABLE KK_Kids ADD CONSTRAINT PK_KK PRIMARY KEY (KK_Kid_Key)
ALTER TABLE DD_Dads ADD CONSTRAINT FK_DD FOREIGN KEY (DD_Grandpa_Key) REFERENCES GG_Grandpas (GG_Grandpa_Key)
ALTER TABLE KK_Kids ADD CONSTRAINT FK_KK FOREIGN KEY (KK_Dad_Key) REFERENCES DD_Dads (DD_Dad_Key)
INSERT INTO GG_Grandpas VALUES ('GG_GEORGESR_KEY', 'GEORGE SR')
INSERT INTO DD_Dads VALUES ('DD_GEORGEJR_KEY', 'GG_GEORGESR_KEY', 'GEORGE JR')
INSERT INTO DD_Dads VALUES ('DD_JEB_KEY', 'GG_GEORGESR_KEY', 'JEB')
INSERT INTO KK_Kids VALUES ( 'KK_Jenna_Key', 'DD_GEORGEJR_KEY', 'Jenna' )
INSERT INTO KK_Kids VALUES ( 'KK_Barbara_Key', 'DD_GEORGEJR_KEY', 'Barbara' )
INSERT INTO KK_Kids VALUES ( 'KK_Noelle_Key', 'DD_JEB_KEY', 'Noelle' )
INSERT INTO KK_Kids VALUES ( 'KK_JebJR_Key', 'DD_JEB_KEY', 'Jeb Junior' )
So the question is, how do I get it to maintain the proper relationships between the records when I do an FOR XML query? Here is the query I am trying to get to work. Right now it puts all the Kids under a single Dad, rather than having them under their correct dads.
I am getting this, which is not what I want:
- George SR
--- George JR
--- Jeb
------ Jenna
------ Barbara
------ Jeb JR
------ Noelle
SELECT 1 as Tag,
NULL as Parent,
GG_GrandpaName as [GG_Grandpas!1!GG_GrandpaName],
GG_Grandpa_Key as [GG_Grandpas!1!GG_Grandpa_Key!id],
NULL as [DD_Dads!2!DD_DadName],
NULL as [DD_Dads!2!DD_Dad_Key!id],
NULL as [DD_Dads!2!DD_Grandpa_Key!idref],
NULL as [KK_Kids!3!KK_KidName],
NULL as [KK_Kids!3!KK_Dad_Key!idref]
FROM GG_Grandpas
UNION ALL
SELECT 2 ,
1 ,
NULL ,
GG_Grandpa_Key ,
DD_DadName ,
DD_Dad_Key ,
DD_Grandpa_Key ,
NULL ,
NULL
FROM GG_Grandpas, DD_Dads
WHERE GG_Grandpa_Key = DD_Grandpa_Key
UNION ALL
SELECT 3 ,
2 ,
NULL ,
GG_Grandpa_Key ,
NULL ,
DD_Dad_Key ,
NULL ,
KK_KidName ,
KK_Dad_Key
FROM GG_Grandpas, DD_Dads , KK_Kids
WHERE GG_Grandpa_Key = DD_Grandpa_Key
AND DD_Dad_Key = KK_Dad_Key
FOR XML EXPLICIT
I've tried it all different ways, but no luck so far.
Any ideas?
View 5 Replies
View Related
Dec 21, 2007
I Have a table of Data (WikiData)
WikiIDint
ParentIDint
sTitlevarchar(50)
sDescriptionvarchar(MAX)
There will be three levels of data imposed at the Application Layer
Level 1: ParentID = 0
An Item Like Geography
Level 2: ParentID = a Level 1 WikiID
A sub Topic like Volcanoes
Level 3: ParentID = Level 2 WikiID
A bottom Topic like Pyroclastic Flows
I Need a SQL statement that Will Produce the Output where The output will be produced like this:
Level 1
Level 2
Level 2
Level 2
Level 1
Level 2
Level 2
I Built this but its wrong and has no order by Group by Statements
Select * from WikiData where ParentID = 0 or ParentID IN (Select * from WikiData where ParentID = 0)
View 12 Replies
View Related
Oct 12, 2015
We are getting frequently blocking in Report server database.
Is it ok to change isolation level to RCSI for report server database?
View 1 Replies
View Related
Aug 31, 2012
Out of nowhere my derived hierarchies starting showing the following message in the MDS UI
No Level Defined:Â This Derived Hierarchy is incomplete......
As you can see below the structure is defined and still in tact. This message shows up in by the Explorer & System Admin areas. I'm also able to query the subscription view setup without any issue.
This is with 2012 w/ no CUs. Same setup in running in another environment without issue.
View 5 Replies
View Related
Oct 21, 2014
I want to select weekly data from daily data.lets say Today's date-10/23/2014(Thursday) My data is in date time but i want to see only date
output should be from last week Thursday to this week Wednesday. similar for previous dates
Weekly sum(profit)
10/16 - 10/21 - $1000
10/9 - 10/15 - $4100
10/2 - 10/8 - $ 8038
--
--
--
View 2 Replies
View Related
Nov 11, 2014
I have 2 tables in my database.
one is Race table and 2nd one is Age Range.
I want to write a query where I can see all races and age range as column.
TblRace
ID, RaceName
TblAgeRange
ID,AgeRange.
There is no connection between this two table. I need to display result like below.
Race 17-20 21-30 31-40
A
B
I
W
How do i get this kind of empty data set so that I can fill it out in front end or any better solution. The age range will be displayed as many row as they have. It's not static. Above is just an example.
View 1 Replies
View Related
Dec 12, 2014
I have data like this in a table
ID test_date Score
001 4/1/2014 80
001 5/4/2014 85
001 6/1/2014 82
I want to convert the data into a row like this:
ID test_date1 score1 test_date2 score2 test_date3 Score3
001 4/1/2014 80 5/4/2014 85 6/1/2014 82
How can I do that with T-SQL?
View 1 Replies
View Related
Feb 13, 2014
While working with data comparison I faced an issue of mismatched data instead of having same data. Below is Script
CREATE TABLE #TestAA(
[ID] [int] NOT NULL,
[value] [float] NOT NULL
)
CREATE TABLE #TestBB(
[ID] [int] NOT NULL,
[value] [float] NOT NULL
[Code] ....
Or you can use
SELECT ID ,value FROM TestAA
except
SELECT ID, value FROM TestBB
View 3 Replies
View Related
Apr 29, 2015
I have the two following locations.
They're both towns in Australia , State of Victoria
Fitzroy,-37.798701, 144.978687
Footscray,-37.799736, 144.899734
After running geography::Point(Latitude, Longitude , 4326) on the latitude and longitude provided for each location, my Geography column for each row is populated with the following:
Fitzroy, 0xE6100000010C292499D53BE642C0A7406667511F6240
Footscray, 0xE6100000010C89B7CEBF5DE642C02D23F59ECA1C6240
In my SQL Query, I have the following which works out the distance between both towns. Geo being my Geography column
DECLARE @s geography = 0xE6100000010C292499D53BE642C0A7406667511F6240 -- Fitzroy
DECLARE @t geography = 0xE6100000010C89B7CEBF5DE642C02D23F59ECA1C6240 -- Footscray
SELECT @s.STDistance(@t)
The result I get is
6954.44911927616
I then looked at formatting this as in Australia we go by KM so after some searching I found two solutions one for Miles and the other KM
So I changed Select statement to look like this
select @s.STDistance(@t)/1000 -- format to KM
My result is then
6.95444911927616
When I go to google maps and do a direction request between the locations provided above it says 10.2km (depending on traffic)
Now I'm new to this spatial data within SQL, why would I get a different result from google maps?
Also I would like to round this number so its easier to use within my where statement so I'm using Ceiling as shown here:
SELECT CEILING(@s.STDistance(@t)/1000)
Is ceiling the correct way to go?
Reason I need to round this is because we are allowing the end user to search by radius so if they pass in 50km I will then say
Where CEILING(@s.STDistance(@t)/1000) < 50
View 2 Replies
View Related
Apr 11, 2008
Hello,
For the following ADO Connection::Execute() function the "recAffected" is zero, after a successful insertion of data, in SQL Server 2005. In SQL Server Express I am getting the the number of records affected value correctly.
_variant_t recAffected;
connectionObject->Execute(SQL, &recAffected, adCmdText );
The SQL string is
BEGIN
DELETE FROM MyTable WHERE Column_1 IN ('abc', 'def' )
END
BEGIN
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 )
SELECT 'abc', 'data11', 'data12'
UNION ALL
SELECT 'def', 'data21', 'data22'
END
But see this, for SQL Server 2005 "recAffected" has the correct value of 2 when I have just the insert statement.
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 )
SELECT 'abc', 'data11', 'data12'
UNION ALL
SELECT 'def', 'data21', 'data22'
For SQL Server 2005 in both cases the table got successfully inserted two rows and the HRESULT of Execute() function returns S_OK.
Does the Execute function has any problem with a statement like the first one (with delete and insert) on a SQL Server 2005 DB?
Why the "recAffected" has a value zero for the first SQL (with delete and insert) on a SQL Server 2005 DB? Do I need to pass any options to Execute() function for a SQL Server 2005 DB?
When connecting to SQL Server Express the "recAffected" has the correct values for any type of SQL statements.
Thank you for your time. Any help greatly appreciated.
Thanks,
Kannan
View 1 Replies
View Related
Apr 5, 2008
Hello,
For the following ADO Connection::Execute() function the "recAffected" is zero, after a successful insertion of data, in SQL Server 2005. In SQL Server Express I am getting the the number of records affected value correctly.
_variant_t recAffected;
connectionObject->Execute(SQL, &recAffected, adCmdText );
The SQL string is
BEGIN
DELETE FROM MyTable WHERE Column_1 IN ('abc', 'def' )
END
BEGIN
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 )
SELECT 'abc', 'data11', 'data12'
UNION ALL
SELECT 'def', 'data21', 'data22'
END
But see this, for SQL Server 2005 "recAffected" has the correct value of 2 when I have just the insert statement.
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 )
SELECT 'abc', 'data11', 'data12'
UNION ALL
SELECT 'def', 'data21', 'data22'
For SQL Server 2005 in both cases the table got successfully inserted two rows and the HRESULT of Execute() function returns S_OK.
Does the Execute function has any problem with a statement like the first one (with delete and insert) on a SQL Server 2005 DB?
Why the "recAffected" has a value zero for the first SQL (with delete and insert) on a SQL Server 2005 DB? Do I need to pass any options to Execute() function for a SQL Server 2005 DB?
When connecting to SQL Server Express the "recAffected" has the correct values for any type of SQL statements.
Thank you for your time. Any help greatly appreciated.
Thanks,
Kannan
View 5 Replies
View Related
May 25, 2015
Our business get orders through the week with the weekends (Fri & Sat) orders being higher than weekdays. Im wanting to graph this years data with last years and possible the years before but to compare days in such a way that the all the weekdays line up. so comparing 2015 week 1 with 2014 week 1 but with 03/01/2015 (Sat) lining up with 04/01/2014 (Sat) etc.
I'm looking for alternatives to adding or removing days from the dates to solve this issue, i have a date dimension table for the past 5 years that i can use to compare calendar week 201401 with calendar week 201501 but I am finding it a bit inflexable.
View 5 Replies
View Related
Jul 6, 2004
Hi all,
Hope u could help me
I have a table as follows
SupID | Week | ..| ........
-------------------------
234 1/2/03
235 1/2/03
236 2/2/03
237 2/2/03
238 2/2/03
239 3/2/03
and
I need to display theses data like
1/2/03(week)
-------------
234
235
2/2/03(week)
-------------
236
237
238
3/3/03(week)
-------------
239
If i go for Group by clause (SQL) then I can group them by week but cant display the individual rows.
Is there any way to do this (better if in a single query)
Thnx
View 3 Replies
View Related
Mar 11, 2004
some one plz help me.
I had a table with these columns.
Table(Id int,Name varchar,Value Varchar).
I have to group them by ID and each Name becomes column name of the new table
ex:-
Id Name Value
--------------------
1 x a1
2 x a2
3 x a3
1 y b1
2 y b2
3 y b3
1 z c1
2 z c2
3 z c3
I need it in this way
x y z
------------
a1 b1 c1
a2 b2 c2
a3 b3 c3
(no of columns in the new table can't be pre determined)
and which one would be better option to do this
in VB.Net code or in a Storedprocedure?
View 14 Replies
View Related
May 21, 2014
I am joining multiple tables and my result is following:
UserGroup UserName
UG1 Tom
UG1 Harry
UG2 Albert
UG3 Jim
UG3 Sam
I want xml in following format:
<UserGroupsInfo>
<UserGroups>
<UserGroup name="UG1">
<User>
Tom
[Code] ....
I have tried all combinations of for xml path but no success yet.
View 7 Replies
View Related
Mar 26, 2008
I have a set of data that contains duplicates and I am running a group by query to get a unique set of rows.
The group by works upto a certain state, but I need to be able to tell it to take the first appearance of an address field as you can in Access?
current query is:
select ID,Address1,max(value)
from test
group by id,address1
I have tried using First(Address1) but it doesn't recognise that as a function?
View 3 Replies
View Related
Feb 9, 2007
Hi Exports
I have a simple question, but I can't seem to get around it. Please help.
I have a table like this: [select * from Enrollment]
Course ID PersonID Role
BCC110 123 Student
BCC110 321 Student
BCC110 456 teacher
BCC123 457 Student
and I want to have a report like
Course ID Total Students Total Teachers
BCC110 2 1
BCC123 1 0
How do I achieve this?
Sam
View 8 Replies
View Related
May 8, 2007
hi,
I want to group data in matrix column. Lets say i have a field say Weekday which has weekdays from monday to friday. then suppose i have measure "my expenditure".
I will place Weekdays in column field of matrix and "my expenditure" in data field". lets not worry about rows.
Now i want something like this.
Group my expenditure in three categories like
1. My expenditure on monday
2. My expenditure on tuesday
3. My expenditure on days other than monday and tuesday.(means it should show me data for wednesday,thursday,friday and also if no weekday is entered)
what I am doing now is i m writting IIF expression in the column field but with that i get data for monday and tuesday but data for all the other days is not getting clubbed.
how can i do this?
Thanks
rohit
View 2 Replies
View Related
Feb 8, 2008
Hi Everyone,
I am trying to develop a report query that will dynamically group the data by day,week, quarter, month, or year. The user will select the interval from the site and pass it to the query. The query will convert the date field using a function I created. Is this the most efficient way to do this?
QUERY
------------------
SELECT dbo.FormatDate(DateCreated,3) As DateCreated, Count(*) As NumOfCalls
FROM Agent.LeadTracker
WHERE DateCreated BETWEEN '1/1/2008' AND '2/7/2008'
GROUP BY dbo.FormatDate(DateCreated,3)
RESULT
------------------
DateCreated NumOfCalls
2008-02-01 5442
2008-01-01 14150
FUNCTION------------------
CREATE FUNCTION [dbo].[FormatDate]
(
@Date datetime,
@Time int
)
RETURNS datetime
AS
BEGIN
DECLARE @Res datetime
IF @Time = 1 --Day
SELECT @Res = DATEADD(day,DATEDIFF(day,0,@Date),0)
ELSE IF @Time = 2 --Week
SELECT @Res = DATEADD(week,DATEDIFF(week,0,@Date),0)
ELSE IF @Time = 3 --Month
SELECT @Res = DATEADD(month,DATEDIFF(month,0,@Date),0)
ELSE IF @Time = 4 --Quarter
SELECT @Res = DATEADD(quarter,DATEDIFF(quarter,0,@Date),0)
ELSE IF @Time = 5 --Year
SELECT @Res = DATEADD(year,DATEDIFF(year,0,@Date),0)
RETURN @Res
END
View 4 Replies
View Related
Aug 16, 2015
I need to add a join in my select query depending upon a variable @LoggedUser. the Join is to be there if @loggedUser is 1 else i do not need it. Currently I am using two different queries one with join and one without it under If (@LoggedUser check).
the join is like -
JOIN (SELECT CAST(CONVERT(VARCHAR(8),Analyst_Effective_date , 1) AS DATETIME) Analyst_Effective_date
FROM Users us (NOLOCK) JOIN Primary_Analysts (NOLOCK)
ON User_Count_Id = Analyst_Id_fk
WHERE User_Count_Id in ((SELECT VALUE FROM dbo.fParseString(@Analyst, ',')) )) Ana
ON dep.Departure_Code = Ana.Primary_Analyst_Departure_Code_fk
)
Any way that the join can be added conditionally in the query so i do not have to write the whole code again for one join.
View 4 Replies
View Related
Oct 4, 2015
I have two tables tabA (cola1, cola2, cola3) and tabB(colb1, colb2, colb3, colb4) which I need to join on all 3 columns of table A.
Of the 3 columns in tabA, few can be NULL, in that case I want to check the joining condition for the rest of the columns, so its conditional joining. Let me rephrase what I am trying to acheive - I need to check if the columns in joining condition is NULL in my 1st table (tabA), If so I need to check the joining condition for the rest of the two columns, if 2nd column is again NULL, I need to check the joining condition on the third column.
What I am trying to do is as below. Its working, but is very slow when one of the tables is huge. Can I optimize it or rewrite in a better way ?
--- First Create two tables
Create table tabA
(cola1 nvarchar(100), cola2 nvarchar(100), cola3 nvarchar(100))
Insert into tabA values (NULL,'A1','A2')
Select * from tabA
create table tabB
[Code] .....
View 7 Replies
View Related
Feb 25, 2015
I am having some difficulty getting a query to output an alpha numeric range grouping.
I have this data set:
Despatch_id Sample_ID
MIR00831 MCR0005752
MIR00831 MCR0005753
MIR00831 MCR0005754
MIR00831 MCR0005755
MIR00831 MCR0005756
MIR00831 MCR0005757
[code]....
Output:
DESPATCH_ID SAMPLE_ID_FROM SAMPLE_ID_TO
MIR00831 MCR0005752 MCR0005762
MIR00831 MCR0005803 MCR0005806
MIR00831 MCR0005808 MCR0005813
They need to be grouped by range specific to the alpha numeric part, which can vary within the same despatch. I was thinking of using a row over partition after splitting the numeric and alpha part and to check if they are consecutive and build the range. But I am thinking that this approach is an overkill and there may be a better way to achieve this in SQL 2012.
I have included the create table scripts and example data below:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SAMPLE_TABLE](
[DESPATCH_ID] [nvarchar](30) NOT NULL,
[SAMPLE_ID] [nvarchar](30) NOT NULL
[code]....
View 7 Replies
View Related
Sep 15, 2015
I have table like below.
filename col1 col2 col3
ABD Y NULL Y
XYZ Y Y Y
CDZ Y Y Y
I Need a output like this
filename col1 col2 col3 Group
ABD Y NULL Y Group1
XYZ Y Y Y Group2
CDZ Y Y Y Group2
I wanted to group the col1 , col2, col3 and group it as same group.
View 3 Replies
View Related
Apr 17, 2007
Created a stored procedure which returns Selected table from database.
I pass variables, according to conditions
For some reason it is not returning any result for any condition
Stored Procedure
ALTER PROCEDURE dbo.StoredProcedure
(
@condition varchar(20),
@ID bigint,
@date1 as datetime,
@date2 as datetime
)
AS
/* SET NOCOUNT ON */
IF @condition LIKE 'all'
SELECT CllientEventDetails.*
FROM CllientEventDetails
WHERE (ClientID = @ID)
IF @condition LIKE 'current_events'
SELECT ClientEventDetails.*
FROM ClientEventDetails
WHERE (ClientID = @ID) AND
(EventFrom <= ISNULL(@date1, EventFrom)) AND
(EventTill >= ISNULL(@date1, EventTill))
IF @condition LIKE 'past_events'
SELECT ClientEventDetails.*
FROM ClientEventDetails
WHERE (ClientID = @ID) AND
(EventTill <= ISNULL(@date1, EventTill))
IF @condition LIKE 'upcoming_events'
SELECT ClientEventDetails.*
FROM ClientEventDetails
WHERE (ClientID = @ID) AND
(EventFrom >= ISNULL(@date1, EventFrom))
IF @condition LIKE ''
SELECT CllientEventDetails.*
FROM CllientEventDetails
RETURN
Also I would like to find out if I can put only "where" clause in if condition as my select statements are constants
View 2 Replies
View Related
May 30, 2007
Hi,
I have a data flow task and trying to transform datas OLTP to STG db and i have lookup tables.
I do lookuping like this
first a lookup that lookup my table with connected input column parameter
second a derived column is connected to lookup's error output for when lookup can't find the value and this derived column returned "0" or "-1" this means that lookuped value can't find and insert this value to my table
third a union that union lookup and derived column
i want to ask this is there any different solution for doing this, because if i more than 5 or 6 lookup in my ssis package i add all of them derived columns and unions and when i change something i have to change or correct the unions step by step.
thanks
View 1 Replies
View Related
Feb 13, 2012
I am trying to create a group on this data example
RecordNo, Item, Date,NULL
1, ABBB, NULL, 0 << first group
2, ABBB, 01-06-2011,NULL << first group
3, ABBB,NULL,NULL << 2nd group
4, ABBB, 02-01-2011,NULL << 2nd group
5,CAAA,NULL,NULL << 1st group
6,CAAA,NULL,NULL << 1st group
7,CAAA,01-01-2010,NULL << 1st group
8,CAAA,01-01-2011,NULL << 2nd group
9,CAAA,01-05-2012, NULL << 3rd group
Basically 3 groups on date, by item, so for each date change/ per part the group increments been playing with row_number partitions, but cant seem to get what i need.
View 14 Replies
View Related
Jan 16, 2007
I€™m trying to build a report that contains bank account activity. For 3 customers their activity is this:
Deposits
Date Amount
John 1/2/2007 500.00
1/7/2007 250.00
Mary 1/3/2007 100.00
Withdrawals
Date Amount
John 1/3/2007 100.00
Mary 1/2/2007 100.00
1/4/2007 200.00
1/6/2007 50.00
Sam 1/6/2007 50.00
I would like the report to have deposit/withdrawal info, with subtotals, grouped by customer, looking something like this:
Deposit Withdrawal
Cust Date Amount Date Amount
John 1/2/2007 500.00 1/3/2007 100.00
1/7/2007 250.00
750.00 100.00
Mary 1/3/2007 100.00 1/2/2007 100.00
1/4/2007 200.00
1/6/2007 50.00
100.00 350.00
Sam 1/6/2007 50.00
0 50.00
Because the deposit and withdrawal data is only related by customer, I€™ve broken it into 2 datasets.
I€™m totally stumped! Can someone get me going in the right direction on this?
Thanks
View 1 Replies
View Related
Sep 25, 2006
Hello all
I am using SQL Server 2000. I have a table of over 1 million accounting transactions. I need to be able to remove all items that have contra items.
e.g
debit £100 - credit £100 - debit of £125 ( I only want to see the debit of £125)
I can achieve this in MS Access by grouping the key fields, suming the value fields and using the First() or Last() command for columns that I need to display but not group.
How can I achieve this in SQL?
All help appreciated.
View 4 Replies
View Related