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

SQL Server 2012 :: Query On Grouping Data On Weekly Basis

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

SQL Server 2012 :: DB2 Store Procedure Returning Two Data Sets

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

SQL 2012 :: Entity Framework Returning Cached Data

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

Results Are Returning On Different Row Levels?

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

FOR XML Query With More Than 2 Levels Of Data

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

Contents Sort 3 Levels Of Data

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

SQL 2012 :: Blocking In Report Server Database And Changing Isolation Levels?

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

Master Data Services :: Derived Hierarchy - No Levels Defined

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

SQL Server 2012 :: Getting Custom Week Data From Daily Data?

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

SQL Server 2012 :: Get Empty Data Set For Inserting Data?

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

SQL Server 2012 :: Convert Column Data Into Row Data?

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

SQL Server 2012 :: Getting Mismatched Data Instead Of Having Same Data

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

SQL Server 2008 :: Spatial Data Not Returning Correct Distance

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

ADO - Connection::Execute() Returning Zero Records Affected After Successful Insertion Of Data In SQL Server 2005

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

ADO - Connection::Execute() Returning Zero Records Affected After Successful Insertion Of Data In SQL Server 2005

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

SQL Server 2012 :: Compare Current Year Data With Last Year Data - Day Adjusted

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

Need Help In Grouping The Data

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

Grouping Data

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

Grouping XML Data

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

Grouping Data

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

Grouping Data

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

Data Grouping

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

Grouping Data

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

SQL Server 2012 :: Adding A Conditional Join?

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

SQL Server 2012 :: Conditional Join Between Two Tables

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

SQL Server 2012 :: Alphanumeric Range Grouping?

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

SQL Server 2012 :: Grouping Columns In Table

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

Conditional Dynamic SQL In Stored Procedure, Not Returning Any Result

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

Conditional Lookup &&amp; Returning Undefined Values On Error

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

Grouping On Data Change

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

Grouping Dissimilar Data

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

Grouping Data Problem

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







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