Summarize Data Using Single Query

May 31, 2007



I recieve duplicate customer bill data which I want to consolidate using SQL query. How to summarize data using single query...both Qty and Price. If either one of price or qty is -ve then both should be -ve.



Here is example data for one customer.



1 €“ Only Qty is negative

Product Qty Price

Shirt 2 6.00

Shirt -1 6.00

-------------------------------------------------------------------------------------------------

Shirt 1 6.00 Result



2 - Only price is negative

Product Qty Price

Pant 2 6.00

Pant 1 -6.00

-------------------------------------------------------------------------------------------------

Pant 1 6.00 Result



Thanks for help!!

Pintoo

View 1 Replies


ADVERTISEMENT

Summarize Data Over Partitions

Oct 17, 2007

Hi champs!
The data i have is like this:

nr date value

------- -------------- --------
1 2007-10-03 45
1 2007-10-05 5
1 2007-10-11 -1
1 2007-10-30 23
2 2007-03-03 3
2 2007-03-13 -5
2 2007-03-03 6
3 2007-10-03 42
3 2007-10-03 11

.....

I want to summerize the value in each group and set the date to the 1'st og that month
i.e.
nr date value

------- -------------- --------
1 2007-10-01 72
2 2007-03-01 4
3 2007-10-01 53


any help is much appreciated.
thanks

View 3 Replies View Related

Creating A Stored Procedure That Will Summarize Data In A Table Into A Table Reflecting Period Data Using An Array Type Field

Sep 20, 2007

I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.

Any help would be greatly appreciated.

Current Table

Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours
---------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20
Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35
Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40
Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40

Proposed Table

Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year
---------------------------------------------------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007
Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008

Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008
Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008

Thanks,
Mike Misera

View 6 Replies View Related

Data Access :: Query On View To Get A Single Batched Data

Nov 25, 2015

I have a view thatΒ  give me the data of all the batched. Now I am using a query on view to get a single batched data. when I am using direct query it was taking 0 sec but when I am using Through view "select *Β  from myView where batched=2" then its taking 30 mnt.

View 3 Replies View Related

Get Data From Query In Single Row

Jun 23, 2008

Hello All,

I have following type of table with data.


Year id value
01-01-2008 123 10
01-01-2008 131 11
01-01-2008 139 132
01-01-2007 153 112
01-01-2007 154 134
01-01-2007 155 164

I want to write query such that I should get value of particular year in single row as follows


Date Col 1 Col 2 Col 3
01-01-2008 10 11 132
01-01-2007 112 134 164

How can I write this type of query ? Please do let me know if anyone know how to write it ?

Thanks in advance.



--kneel

View 5 Replies View Related

Write A Query To Replace Data From A Single Field

Feb 15, 2008

Hi I have a table named  UserMaster having a column Sex for male and female.Sex MaleMaleFemaleFemaleFemale I want to replace all Male with Female and all Female with Male with a single query. Can any one please help me out 

View 4 Replies View Related

How To Delete Unwanted Data From Multiple Different Tables With One Single SQL Query?

Mar 18, 2008

This a microsoft SQL 2000 server.
I have a DB with mutliple tables that have a column called "Date_stamp", which is used as a primary ID.
Here is my problem:
Some of tables have a bad datetime entry for the "Date_stamp". The bad entry is '2008-3-18". I need to delete this entry from every single table that has a name similary to 'Elect_Sub%Daily'.

I know how to get the user table names from the DB as follows:

SELECT name
FROM dbo.sysobjects
WHERE xtype = 'U' and name like 'Elect_Sub%Daily'

What I need to do is have a query that will basically scroll through the tables name produced by the above query and search and delete the entries that read '2008-3-18".

delete from tableName where Date_Stamp = '2008-3-18'

View 7 Replies View Related

Transact SQL :: Passing Multi-valued Data As Single Parameter In Query

Jul 2, 2015

I have a spΒ whereΒ query is asΒ below.

DECLARE @ServerCIName varchar(5000)
SELECT * FROMΒ  dbo.INC
WHERE Status in ('Assigned','In Progress','Pending')
and Description like '%' + (Select * from SplitDelimiterString(@ServerName,',')) + '%'
and (select DATEADD(dd, DATEDIFF(dd, 0, (Submit_Date)), 0)) = (select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0))
In place of "and Description like '%' + (Select * from SplitDelimiterString(@ServerName,',')) + '%' ", if I use "and Description like '%' + @ServerName + '%' " and pass a single value, it works.

But @ServerName contains multiple values and it is dynamic (not constant).

How do I query the data?

View 6 Replies View Related

Query Fails When Returning A Single Record With Numeric Data In NVarChar(16) Column In Device App

Mar 12, 2008

If a Select is done on a column whose data type is nvarchar(16) and contains only numerals (UPC numbers) the select does not return the record.

1. Query with numerals in nvarchar column works as long as multiple records are returned (LIKE '012%')
2. Numeric (INT only one tested) columns works as expected
3. String columns with alpha data works as expected
4. Problem only exist when running in Device Emulator and/or actual device.
5. Same test on desktop app runs as expected.
6. Windows Mobile 6, Vista Ultimate
7. Same results when when connection to device from SSMS
8. SQL Servers comes on

Previous thread discussion of this problem (I thought that Parameters corrected problem, but not in all cases???)

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.sqlserver.ce&mid=0cd9cd3a-f9b0-477f-b1e7-c27eb76158ae

Here is the complete code:


SqlCeConnection _conn = null;

_conn = new SqlCeConnection(@"Data Source=program FilesTestResultSetevsoft.sdf;");

_conn.Open();


// DOES NOT WORK *** This statement does not return the record (it exist)

string _sql = "SELECT * FROM Product where RegDescr='0123456' ";




// works correctly

string _sql = "SELECT * FROM PRODUCT where ProdNum = 6523 ";


// works correctly *** as long as multiple records are returned

string _sql = "SELECT * FROM PRODUCT where RegDescr LIKE '01%' ";

// works correctly


string _sql = "SELECT * FROM PRODUCT where RegDescr='BACARDI SILVER RAZZ'";

SqlCeCommand _cmd = _conn.CreateCommand();

SqlCeDataReader _rdr;

_cmd.CommandText = _sql;

_cmd.CommandType = CommandType.Text;

// Same results using ExecuteResultSet or ExecuteReader

//_rdr = _cmd.ExecuteResultSet(ResultSetOptions.Scrollable | ResultSetOptions.Updatable);

_rdr = _cmd.ExecuteReader();



listBox1.Items.Add("In the while loop");

while (_rdr.Read())

{

listBox1.Items.Add(_rdr.GetValue(1) + " / " + _rdr.GetValue(3));

}

listBox1.Items.Add("Done");

View 1 Replies View Related

Summarize Unique Changes Of Registration

Aug 21, 2006

I've been trying to solve this problem for better of 4 days:We summarize registrations of students on a daily basis, however theyare net changes.Example:A student registers one class for the first time for the Fall quarteron a Monday. A report would reflect that change for Monday.Next, the same student adds another class on Tuesday. Since the studentwas already counted on Monday, I dont want the student to count onTuesday.On Wednesday, the student decides to drop both classes. Since thestudent no longer has any classes, I wish to decrement the studentcount on Wednesday for that one student.If the same student adds a new class on Friday, then they would counton Friday since their previous classes net to zero.After the end of the session, I would be able to sum up the dailybalance of adds and drops and it would net out to be equal with thetotal number of unique students registered for the quarter.- Students can add and or drop classes on the same day, or on differentdays.- I need to know when the net effect when a student is changed andreflect that quantity on the date for the quarter (SESSION).We have reports on our legacy system written in IBM's Universe (its abusiness basic). Its pretty straight forward as we would traverse thedata using a basic program. However, trying to something in batch inSQL has eluded me.What I am looking to select:What I am looking to select:SESSION DATE STUDENT_ADDS STUDENT_DROPS200602 2005-07-18 1 0200602 2005-08-23 1 02006002 2005-09-30 0 1TIARob(I thought of getting the first registration, and last drop (if any),but it wont work as there can be adds and drops in between)(I ran the DDL this time, and it returned what I expected when Iselected it)CREATE TABLE "DBO"."REGTRACK"("STUDENT_SKEY" INT NOT NULL,"SESSION_ID" CHAR(6) NOT NULL,"FULL_CLASS_ID" CHAR(15) NOT NULL,"ACTIVITY_CODE" CHAR(1) NOT NULL,"ACTIVITY_DT" DATETIME NOT NULL,"ACTIVITY_COUNT" INT)INSERT INTO REGTRACKVALUES(250,'200602','MAT100001024','A',CONVERT(DAT ETIME,'2005-08-23'),1)INSERT INTO REGTRACKVALUES(250,'200602','ENG200001024','A',CONVERT(DAT ETIME,'2005-08-23'),1)INSERT INTO REGTRACKVALUES(260,'200602','BUS100002011','A',CONVERT(DAT ETIME,'2005-07-18'),1)INSERT INTO REGTRACKVALUES(260,'200602','CIS105001011','A',CONVERT(DAT ETIME,'2005-07-18'),1)INSERT INTO REGTRACKVALUES(260,'200602','CIS105002011','A',CONVERT(DAT ETIME,'2005-07-19'),1)INSERT INTO REGTRACKVALUES(260,'200602','CIS105001011','D',CONVERT(DAT ETIME,'2005-07-19'),1)INSERT INTO REGTRACKVALUES(260,'200602','CIS105002011','D',CONVERT(DAT ETIME,'2005-09-30'),1)INSERT INTO REGTRACKVALUES(265,'200602','PAD500001024','A',CONVERT(DAT ETIME,'2005-08-26'),1)INSERT INTO REGTRACKVALUES(266,'200602','CIS110001006','A',CONVERT(DAT ETIME,'2005-09-19'),1)INSERT INTO REGTRACKVALUES(267,'200602','ECO100001004','A',CONVERT(DAT ETIME,'2005-09-07'),1)INSERT INTO REGTRACKVALUES(267,'200602','BUS520012016','A',CONVERT(DAT ETIME,'2005-09-07'),1)INSERT INTO REGTRACKVALUES(267,'200602','BUS520012016','D',CONVERT(DAT ETIME,'2005-10-10'),1)INSERT INTO REGTRACKVALUES(267,'200602','ECO100001004','D',CONVERT(DAT ETIME,'2005-10-10'),1)INSERT INTO REGTRACKVALUES(275,'200602','ITB300001016','A',CONVERT(DAT ETIME,'2005-08-17'),1)INSERT INTO REGTRACKVALUES(275,'200602','BUS310006016','A',CONVERT(DAT ETIME,'2005-08-31'),1)INSERT INTO REGTRACKVALUES(288,'200602','FIN100002016','A',CONVERT(DAT ETIME,'2005-07-28'),1)INSERT INTO REGTRACKVALUES(288,'200602','POL300003016','A',CONVERT(DAT ETIME,'2005-07-28'),1)INSERT INTO REGTRACKVALUES(288,'200602','FIN100002016','D',CONVERT(DAT ETIME,'2005-08-30'),1)INSERT INTO REGTRACKVALUES(288,'200602','MKT200002016','A',CONVERT(DAT ETIME,'2005-08-30'),1)INSERT INTO REGTRACKVALUES(321,'200602','CIS105004010','A',CONVERT(DAT ETIME,'2005-08-03'),1)INSERT INTO REGTRACKVALUES(321,'200602','BUS100005010','A',CONVERT(DAT ETIME,'2005-08-03'),1)INSERT INTO REGTRACKVALUES(321,'200602','CIS105004010','D',CONVERT(DAT ETIME,'2005-11-15'),1)INSERT INTO REGTRACKVALUES(321,'200602','BUS100005010','D',CONVERT(DAT ETIME,'2005-11-28'),1)INSERT INTO REGTRACKVALUES(243172,'200602','ENG102001001','A',CONVERT( DATETIME,'2005-09-21'),1)INSERT INTO REGTRACKVALUES(243172,'200602','CIS105023016','A',CONVERT( DATETIME,'2005-09-21'),1)INSERT INTO REGTRACKVALUES(243172,'200602','ACC100002010','A',CONVERT( DATETIME,'2005-09-21'),1)INSERT INTO REGTRACKVALUES(334,'200602','MAT300009016','A',CONVERT(DAT ETIME,'2005-08-29'),1)INSERT INTO REGTRACKVALUES(334,'200602','CIS111009016','A',CONVERT(DAT ETIME,'2005-08-29'),1)INSERT INTO REGTRACKVALUES(256542,'200602','CIS460002016','A',CONVERT( DATETIME,'2005-09-28'),1)INSERT INTO REGTRACKVALUES(256542,'200602','CIS500019016','A',CONVERT( DATETIME,'2005-09-28'),1)INSERT INTO REGTRACKVALUES(349,'200602','CIS500001003','A',CONVERT(DAT ETIME,'2005-09-22'),1)INSERT INTO REGTRACKVALUES(255713,'200602','BUS520008016','A',CONVERT( DATETIME,'2005-08-30'),1)INSERT INTO REGTRACKVALUES(359,'200602','BUS531001029','A',CONVERT(DAT ETIME,'2005-09-01'),1)INSERT INTO REGTRACKVALUES(359,'200602','CIS514001029','A',CONVERT(DAT ETIME,'2005-09-01'),1)INSERT INTO REGTRACKVALUES(367,'200602','ENG102005001','A',CONVERT(DAT ETIME,'2005-09-16'),1)INSERT INTO REGTRACKVALUES(367,'200602','ENG102005001','D',CONVERT(DAT ETIME,'2005-10-26'),1)INSERT INTO REGTRACKVALUES(367,'200602','ENG102005001','A',CONVERT(DAT ETIME,'2005-11-08'),1)INSERT INTO REGTRACKVALUES(368,'200602','CIS110003016','A',CONVERT(DAT ETIME,'2005-08-16'),1)INSERT INTO REGTRACKVALUES(368,'200602','HUM300001016','A',CONVERT(DAT ETIME,'2005-08-16'),1)INSERT INTO REGTRACKVALUES(369,'200602','BUS530011016','A',CONVERT(DAT ETIME,'2005-09-13'),1)INSERT INTO REGTRACKVALUES(381,'200602','BUS100026016','A',CONVERT(DAT ETIME,'2005-08-02'),1)INSERT INTO REGTRACKVALUES(381,'200602','ECO405001016','A',CONVERT(DAT ETIME,'2005-08-02'),1)INSERT INTO REGTRACKVALUES(385,'200602','BUS100002008','A',CONVERT(DAT ETIME,'2005-07-27'),1)INSERT INTO REGTRACKVALUES(385,'200602','BUS107001008','A',CONVERT(DAT ETIME,'2005-07-27'),1)INSERT INTO REGTRACKVALUES(249922,'200602','ECO405008016','A',CONVERT( DATETIME,'2005-09-12'),1)INSERT INTO REGTRACKVALUES(249922,'200602','POL300011016','A',CONVERT( DATETIME,'2005-09-12'),1)INSERT INTO REGTRACKVALUES(249922,'200602','HUM100022016','A',CONVERT( DATETIME,'2005-09-12'),1)INSERT INTO REGTRACKVALUES(249922,'200602','HUM100022016','D',CONVERT( DATETIME,'2005-10-03'),1)INSERT INTO REGTRACKVALUES(395,'200602','HUM400011016','A',CONVERT(DAT ETIME,'2005-08-17'),1)INSERT INTO REGTRACKVALUES(395,'200602','CIS499001016','A',CONVERT(DAT ETIME,'2005-08-17'),1)INSERT INTO REGTRACKVALUES(395,'200602','CIS499002016','A',CONVERT(DAT ETIME,'2005-09-21'),1)INSERT INTO REGTRACKVALUES(395,'200602','CIS499001016','D',CONVERT(DAT ETIME,'2005-09-21'),1)INSERT INTO REGTRACKVALUES(395,'200602','CIS499001015','A',CONVERT(DAT ETIME,'2005-09-22'),1)INSERT INTO REGTRACKVALUES(395,'200602','CIS499002016','D',CONVERT(DAT ETIME,'2005-09-22'),1)INSERT INTO REGTRACKVALUES(397,'200602','ENG095001001','A',CONVERT(DAT ETIME,'2005-09-19'),1)INSERT INTO REGTRACKVALUES(397,'200602','ENG096001001','A',CONVERT(DAT ETIME,'2005-09-19'),1)INSERT INTO REGTRACKVALUES(397,'200602','ENG097001001','A',CONVERT(DAT ETIME,'2005-09-19'),1)INSERT INTO REGTRACKVALUES(398,'200602','HUM200005016','A',CONVERT(DAT ETIME,'2005-08-05'),1)INSERT INTO REGTRACKVALUES(398,'200602','HUM400004016','A',CONVERT(DAT ETIME,'2005-08-05'),1)INSERT INTO REGTRACKVALUES(398,'200602','CIS427001016','A',CONVERT(DAT ETIME,'2005-08-05'),1)INSERT INTO REGTRACKVALUES(406,'200602','ECO550008016','A',CONVERT(DAT ETIME,'2005-08-01'),1)INSERT INTO REGTRACKVALUES(406,'200602','MAT540004016','A',CONVERT(DAT ETIME,'2005-08-01'),1)INSERT INTO REGTRACKVALUES(406,'200602','MAT540004016','D',CONVERT(DAT ETIME,'2005-11-14'),1)INSERT INTO REGTRACKVALUES(429,'200602','POL300006016','A',CONVERT(DAT ETIME,'2005-08-03'),1)INSERT INTO REGTRACKVALUES(429,'200602','SOC300006016','A',CONVERT(DAT ETIME,'2005-08-03'),1)INSERT INTO REGTRACKVALUES(429,'200602','ACC403003016','A',CONVERT(DAT ETIME,'2005-09-01'),1)INSERT INTO REGTRACKVALUES(429,'200602','SOC300006016','D',CONVERT(DAT ETIME,'2005-09-01'),1)INSERT INTO REGTRACKVALUES(433,'200602','ACC560001021','A',CONVERT(DAT ETIME,'2005-09-19'),1)INSERT INTO REGTRACKVALUES(433,'200602','MAT540001021','A',CONVERT(DAT ETIME,'2005-09-19'),1)INSERT INTO REGTRACKVALUES(433,'200602','BUS531001021','A',CONVERT(DAT ETIME,'2005-09-19'),1)INSERT INTO REGTRACKVALUES(433,'200602','ACC560001021','D',CONVERT(DAT ETIME,'2005-09-27'),1)INSERT INTO REGTRACKVALUES(433,'200602','ENG102001021','A',CONVERT(DAT ETIME,'2005-09-28'),1)INSERT INTO REGTRACKVALUES(433,'200602','BUS533001021','A',CONVERT(DAT ETIME,'2005-09-28'),1)INSERT INTO REGTRACKVALUES(433,'200602','ACC560001021','A',CONVERT(DAT ETIME,'2005-09-28'),1)INSERT INTO REGTRACKVALUES(433,'200602','BUS531001021','D',CONVERT(DAT ETIME,'2005-09-28'),1)INSERT INTO REGTRACKVALUES(433,'200602','MAT540001021','D',CONVERT(DAT ETIME,'2005-09-28'),1)INSERT INTO REGTRACKVALUES(433,'200602','ENG102001021','D',CONVERT(DAT ETIME,'2005-09-29'),1)INSERT INTO REGTRACKVALUES(448,'200602','ENG102013016','A',CONVERT(DAT ETIME,'2005-09-27'),1)INSERT INTO REGTRACKVALUES(448,'200602','HUM101013016','A',CONVERT(DAT ETIME,'2005-09-27'),1)INSERT INTO REGTRACKVALUES(459,'200602','HUM101002010','A',CONVERT(DAT ETIME,'2005-08-25'),1)INSERT INTO REGTRACKVALUES(459,'200602','BUS310001010','A',CONVERT(DAT ETIME,'2005-08-25'),1)INSERT INTO REGTRACKVALUES(466,'200602','HUM100004016','A',CONVERT(DAT ETIME,'2005-07-18'),1)INSERT INTO REGTRACKVALUES(466,'200602','CIS111003016','A',CONVERT(DAT ETIME,'2005-07-18'),1)INSERT INTO REGTRACKVALUES(479,'200602','BUS100050016','A',CONVERT(DAT ETIME,'2005-09-20'),1)INSERT INTO REGTRACKVALUES(253486,'200602','ENG102001012','A',CONVERT( DATETIME,'2005-10-05'),1)INSERT INTO REGTRACKVALUES(253486,'200602','MAT105001012','A',CONVERT( DATETIME,'2005-10-05'),1)INSERT INTO REGTRACKVALUES(490,'200602','BUS532001003','A',CONVERT(DAT ETIME,'2005-09-20'),1)INSERT INTO REGTRACKVALUES(509,'200602','ENG102021016','A',CONVERT(DAT ETIME,'2005-10-01'),1)INSERT INTO REGTRACKVALUES(509,'200602','MAT100021016','A',CONVERT(DAT ETIME,'2005-10-01'),1)INSERT INTO REGTRACKVALUES(511,'200602','LEG100001012','A',CONVERT(DAT ETIME,'2005-08-29'),1)INSERT INTO REGTRACKVALUES(556,'200602','LEG100013016','A',CONVERT(DAT ETIME,'2005-09-24'),1)INSERT INTO REGTRACKVALUES(556,'200602','SOC304001003','A',CONVERT(DAT ETIME,'2005-09-24'),1)INSERT INTO REGTRACKVALUES(576,'200602','ACC100002026','A',CONVERT(DAT ETIME,'2005-08-30'),1)INSERT INTO REGTRACKVALUES(576,'200602','BUS100043016','A',CONVERT(DAT ETIME,'2005-08-30'),1)INSERT INTO REGTRACKVALUES(581,'200602','CIS288001010','A',CONVERT(DAT ETIME,'2005-09-08'),1)INSERT INTO REGTRACKVALUES(581,'200602','CIS450001002','A',CONVERT(DAT ETIME,'2005-09-08'),1)INSERT INTO REGTRACKVALUES(581,'200602','CIS286001002','A',CONVERT(DAT ETIME,'2005-09-08'),1)INSERT INTO REGTRACKVALUES(583,'200602','BUS490001017','A',CONVERT(DAT ETIME,'2005-08-09'),1)INSERT INTO REGTRACKVALUES(583,'200602','SOC300004016','A',CONVERT(DAT ETIME,'2005-08-09'),1)INSERT INTO REGTRACKVALUES(583,'200602','BUS490001017','D',CONVERT(DAT ETIME,'2005-09-07'),1)

View 2 Replies View Related

Analysis :: DAX - How To Use Ranks On Top Of Summarize

Jun 2, 2015

I have a query where I'm using summarize to return aggregated tabular data.

EVALUATE
(
SUMMARIZE (
CALCULATETABLE (
'Inscricoes',
'Year'[ID] = VALUE(26)

[code]...

I want to add a measure that ranks them 1,2,3,4. In this case it could be the rownumber, but I'm failing to use RANKX under summarize.

View 6 Replies View Related

Combine Data In Single Row From Single Table

Apr 4, 2006

How can i combine my data in single row ? All data are in a single table sorted as employeeno, date


Code:

Employee No Date SALARY
1 10/30/2006 500
1 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000
4 11/01/2006 8000


Should Appear


Code:

EmployeeNo Date1 OLDSALARY Date2 NEWSALARY
1 10/30/2006 500 11/30/2006 1000
2 10/25/2006 800
3 10/26/2006 900
4 10/28/2006 1000 11/01/2006 800

PLEASE HELP I REALLY NEED THE RIGHT QUERY FOR THIS OUTPUT.

THANKS IN ADVANCE

View 3 Replies View Related

Analysis :: Tabular - DAX - SUMMARIZE And RANKX

Sep 2, 2015

According to the book "Microsoft SQL Server 2012 Analysis Services - The BISM Tabular Model" (pages 276 and 277) the following DAX query should work. However, I'll get a rank of 1 returned for every product name. My impression is that ALL doesn't block the context transition for product name. Because there is nothing in the errata, I might do something wrong!? The second query is just a workaround giving the correct result.

EVALUATE
CALCULATETABLE(
Β Β Β  SUMMARIZE(
Β Β Β Β Β Β Β  'Internet Sales',
Β Β Β Β Β Β Β  Product[Product Name],
Β Β Β Β Β Β Β 
[code]....

View 2 Replies View Related

Querying DateTime Field And Summarize Just Using Date

May 9, 2012

I have 2 tables that I would like to summarize a couple of columns for a full day of production(12:00:00 AM to 11:59:59 PM) based on passed variables. Here are my Tables:

Order_Details_tbl
PlantID – IngredientID – AmountBatched – DateTime
1 – 8 – 1000 – 4/30/2012 1:23:12 PM
1 – 8 – 1000 – 4/30/2012 4:23:12 PM
1 – 8 – 1000 – 5/1/2012 1:23:12 PM
1 – 8 – 1000 – 5/1/2012 10:23:12 PM
1 – 8 – 4500 – 5/3/2012 1:23:12 PM
1 – 8 – 11000 – 5/7/2012 1:23:12 PM
1 – 8 – 1000 – 5/7/2012 10:23:12 AM
1 – 8 – 1000 – 5/7/2012 1:23:12 PM
1 – 8 – 1000 – 5/7/2012 1:23:12 PM
1 – 8 – 1000 – 5/8/2012 9:23:12 AM
1 – 8 – 1000 – 5/8/2012 4:23:12 PM
1 – 8 – 1000 – 5/8/2012 2:23:12 PM

Order_Details_Details_tbl
PlantID – IngredientID – AmountBatched – DateTime
1 – 8 – 100 – 4/30/2012 1:23:12 PM
1 – 8 – 11000 – 5/4/2012 11:23:12 PM
1 – 8 – 11000 – 5/7/2012 11:23:12 PM
1 – 8 – 1000 – 5/8/2012 11:23:12 AM
1 – 8 – 1000 – 5/8/2012 1:23:12 AM
1 – 8 – 1000 – 5/8/2012 11:23:12 PM
1 – 8 – 1000 – 5/8/2012 5:23:12 PM
1 – 8 – 1000 – 5/8/2012 2:23:12 PM

I will pass in the @PlantID int, @IngredientID int, and @Days int. I want to sum the AmountBatched from both tables and display the total for each given day. The @Days will indicate the number of days to query off of previous to the current date. I would also like to eliminate weekends from the results. For example when stored procedure is run passing the following values @PlantID = 1, @IngredientID = 8, and @Days = 14. If date procedure is run is 5/9/2012, would like to summarize for 4/25/2012 to 5/8/2012 excluding weekends if possible.

Results
Date – AmountBatched
4/25/2012 – 0
4/26/2012 – 0
4/27/2012 -- 0
4/30/2012 -- 2100
5/1/2012 -- 2000
5/2/2012 -- 0
5/3/2012 -- 4500
5/4/2012 -- 11000
5/7/2012 -- 25000
5/8/2012 – 8000

Notice 4/28, 4/29, 5/5, and 5/6 are eliminated from the results, which are weekends. Is this possible in a sql stored procedure? I am writing an app in vb .net and am hoping to get the results I need in a single call to sql server and not have to make several calls back. I have not worked with advanced datetime methods in sql server before.

View 6 Replies View Related

Stored Procedure That Needs To Loop Through DataSet And Summarize Based On TypeCode

Apr 27, 2004

Help! I'm very tired (and new at this) and have looked for a solution in many places. I have an Employee table with a one to many Revenue table. All revenue types are in this table. I need the goals and actuals (two different revenue types) for a datagrid.

This is the result. Because I am looking at two revenue types, the result is providing 2 rows of data instead of one. what is the best way to combine this.

Region FullName SHARP Year Ann Goal YTD Goal YTDActual
Region1 Doe10, John X 2003 20400 5100 0 Select
Region1 Doe10, John X 2003 0 0 3987 Select
Region1 Doe11, John X 2003 29645 7411.25 0 Select
Region1 Doe11, John X 2003 0 0 5377 Select

Here's my stored procedure:

CREATE PROCEDURE spFilterRegion

@RIDsent As Integer,
@StatusSent As Integer,
@SelectedRegion As NVARCHAR (50) Output

AS
SELECT Region.CountryID,
Employee.RegionID,
Employee.StatusID,
Employee.SHARP,
CASE
When Employee.SHARP = 1 THEN "X"
ELSE ""
END AS SHARPresult,
Employee.LastName,
Employee.FirstName,
Employee.LastName + ', ' + FirstName AS FullName,
Employee.EmployeeID,
Region.RegionName,
ProducerRevenue.RevenueTypeID,
CASE
When ProducerRevenue.RevenueTypeID = 1 Then
SUM(ProducerRevenue.Revenue)
ELSE 0
END AS AnnGoal,
CASE
When ProducerRevenue.RevenueTypeID = 1 Then
SUM(ProducerRevenue.Revenue)/DATEPART(mm, GETDATE())
ELSE 0
END AS YTDGoal,
CASE
When ProducerRevenue.RevenueTypeID = 2 Then
SUM(ProducerRevenue.Revenue)
ELSE 0
END AS Actual,
ProducerRevenue.YearID
FROM Employee
LEFT OUTER JOIN ProducerRevenue
ON Employee.EmployeeID = ProducerRevenue.EmployeeID AND
ProducerRevenue.YearID = DATEPART(yy, GETDATE()) - 1 AND
ProducerRevenue.MonthID < DATEPART(mm, GETDATE()) AND
ProducerRevenue.StatusID = 1 AND
ProducerRevenue.RevenueTypeID <= 2
LEFT OUTER JOIN Region
ON Employee.RegionID = Region.RegionID
WHERE Employee.StatusID = @StatusSent AND
Employee.RegionID = @RIDsent AND
Employee.RoleID = 1
GROUP BY Region.CountryID,
Employee.RegionID,
Region.RegionName,
Employee.RoleID,
Employee.StatusID,
Employee.SHARP,
Employee.LastName,
Employee.FirstName,
Employee.EmployeeID,
ProducerRevenue.RevenueTypeID,
ProducerRevenue.YearID
ORDER BY Region.CountryID,
Employee.RegionID,
Employee.RoleID,
Employee.StatusID,
Employee.SHARP,
Employee.LastName,
Employee.FirstName,
Employee.EmployeeID,
ProducerRevenue.RevenueTypeID,
ProducerRevenue.YearID

View 1 Replies View Related

Trans Replication With Updating Subscriber On Sql2000 (single Quote In The Data As Char Data Type)

Nov 17, 2006

Hi,

I am trying to setup Trans Replication with updating subscriber on sql2000. One column on few tables got data with single quote (').

How do I handle in this case? Did any one come across such case?

Can I Change default QUOTED IDENTIFIER from ' (single quote) to something else (@@@) on SQL2000?

If yes, how to do?

Thanks
mka

View 1 Replies View Related

Error Writing Data To Same Destination In Single Data Flow

May 12, 2006

I am getting the following error running a data flow that splits the input data into multiple streams and writes the results of each stream to the same destination table:

"This operation conflicts with another pending operation on this transaction. The operation failed."

The flow starts with a single source table with one row per student and multiple scores for that student. It does a few lookups and then splits the stream (using Multicast) in several layers, ultimately generating 25 destinations (one for each score to be recorded), all going to the same table (like a fact table). This all is running under a transaction at the package level, which is distributed to a separate machine.

Apparently, I cannot have all of these streams inserting data into the same table at one time. I don't understand why not. In an OLTP system, many transactions are inserting records into the same table at once. Why can't I do that within the same transaction?

I suppose I can use a UnionAll to join them back together before writing to a single destination, but that seems like an unnecessary waste and clutters the flow. Can anyone offer a different solution or a reason why this fails in the first place?

Thanks in advance.

View 3 Replies View Related

Selecting A Single Data Source From Multiple Data Sources

Aug 4, 2006

Hi,

I am pretty new to SSIS. I am trying to create a package which can accept data in any of several formats. i.e. CSV, Excel, a SQL Server database/table and import the data into my destination database.

So far i've managed to get this working OK. However I am now TOTALLY stuck. I'm currently trying to just concentrate on the data sources being a CSV (using a Flat File Data Source) and/or an Excel Spreadsheet.

I can get the data in and to my destination using a UNION ALL component and mapping the data sources to it so long as both the CSV file and the Excel spreadsheet exist.

My problem is that I need my package to handle the possibility that only the CSV file might exist and there is no Excel spreadsheet. In which case i'd like the package to ignore the Excel datasource completely. Currently either of my data sources do not exist I get errors and the package terminates.

Is there any way in SSIS that I can check all my data sources to see which ones exist (i.e. are valid). If they exist I want to use them. If it doesn't exist i'd like to disgard it (without error - as long as there is a single datasource the package should run)

I've tried using the AcquireConnection method in a script task on each of my connections, hoping that it would error if the file/datasource did not exist. It doesn't though (in the case of an Excel datasource it just creates a empty excel file for me).

The only other option I can come up with are to have seperate packages depending on the type of data we want to import and then run a particular package depending on the format of the source data. This seems a bit long winded. I am pretty sure I must be able to do what I want to achieve but I can't work out how.

I'll be grateful to anyone who can send me any tips/hints/links on how I can achieve this.

Many thanks

Rob Gibson

View 5 Replies View Related

Single Report With The Data From Multiple Data Sources

Jan 9, 2007

Hi,

In my project i want a report. In that report data is getting from more than one data sources(systems). While creating data source view i used named query for both primary and secondary data source. But at the time of crating "Report Model" i am getting below error.

An error occurred while executing a command.
Message: Invalid object name 'Table2'.
Command:
SELECT COUNT(*) FROM (SELECT SerialNum, ModelNum AS com_model
FROM Table2) t



Is there any way to create a report with multiple data sources?

View 8 Replies View Related

Need A Single Query

Jun 14, 2007

Hi,
I have received a text file in the following format:
MthYear                  Customer                     Quantity           Price           Total
Apr2003                 Allan                              100                 5                500
 ---------                  Austin                             25                  2                  50
 ---------                 George                           1500              1               1500
 ----------                Jessy                                200               2                 400
Apr2004                Jerry                             600                 3                 1800
 ---------                 Stella                            250                 2                   500
June2005            XXXX                               50                  5                    250
 
I am exporting this text file in Sql Server database table. After exporting I need to Update the MtnYear field marked as ------- to appropriate year. ie
The MthYear field for Austin, George,Jessy should be updated with Apr2003,  
The MthYear field for Stella should be updated with Apr2004
iiily,  for other records. Let me inform if this is possible with the help of a single query or any better way of doing it. The records that i need to update is more than 2 lakhs. I am sorry if u feel this questionis not apropriate.

View 3 Replies View Related

Single Query

Jan 7, 2007

Hello

I have a sql query that shows the top N numbers of records and also wanted to show the total numbers of this records.


Code:


.
.
.
rs_1.Open "SELECT top 5 msg_id, message, topic_id, last_post_by, id, sdate FROM forum_table",conn,1,3

rs_2.Open "SELECT msg_id, message, topic_id, last_post_by, id, sdate FROM forum_table",conn,1,3 “

Top number to show: <%=rs_1.recordcount%>
Total number of records in database: <%=rs_2.recordcount%>
.
.
.



As you can see, it is only on the same database that I am querying. How do I make a single sql query out of this?

View 3 Replies View Related

Single Query

Jul 6, 2004

i hav a table of the form


table EMP
(
EMPNAME varchar(50)
)

and the data in the table is

record #1 abc
record #2 abc
record #3 abc
record #4 abc
record #5 abc
record #6 abc

can i use a single query so as to retain a single record in the table and remove the rest ?

View 14 Replies View Related

Can A Single Query Do This?

Mar 13, 2008

I have the following table:

id geounit parentgeounit

Here's some sample data:
1 Northeast null
2 Southeast null
3 New Jersey 1
4 Florida 2

Basically I need to return the parentgeounit's name, for instance for record #4 I'd want to return

4 Florida Southeast
Instead of:
4 Florida 2

Can this be accomplished?

View 3 Replies View Related

How To Take Single Value From Sql Query For Repeater ?

Oct 29, 2006

So I have code to fill my repeater with a data: Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load        Dim cnn As Data.SqlClient.SqlConnection = New Data.SqlClient.SqlConnection(System.Web.Configuration.WebConfigurationManager.ConnectionStrings("db_ConnStr").ConnectionString)        Dim cmd1 As Data.SqlClient.SqlDataAdapter = New Data.SqlClient.SqlDataAdapter("SELECT aspnet_Users.UserName, COUNT(forum_posts.post_id) AS IlePost, forum_posts_1.post_content, forum_posts_1.topic_id, forum_posts_1.post_id, forum_posts_1.post_date FROM aspnet_Users INNER JOIN forum_posts ON aspnet_Users.uID = forum_posts.user_id INNER JOIN forum_posts AS forum_posts_1 ON aspnet_Users.uID = forum_posts_1.user_id GROUP BY aspnet_Users.UserName, forum_posts_1.post_content, forum_posts_1.topic_id, forum_posts_1.post_id, forum_posts_1.post_date HAVING (forum_posts_1.topic_id = @topic_id) ORDER BY forum_posts_1.post_date ASC", cnn)        cmd1.SelectCommand.Parameters.AddWithValue("@topic_id", Request.QueryString("ID"))        Dim ds As Data.DataSet = New Data.DataSet        cnn.Open()        cmd1.Fill(ds, "posts")        Repeater1.DataSource = ds.Tables("posts")        Page.DataBind()        cnn.Close()    End Sub And I want to take the value from column which I bolded - IlePost. I want take this out at compare with some condition. For example:Dim label as label = Repeater1.Findcontrol("Label1")If COLUMN < 10 thenlabel.text = "Less than 10"elselabel.text = "More than 10"I hope you understand me :-) 

View 1 Replies View Related

Single Update Query

Apr 29, 2008

Table Name:-emp
table structure:-  Name           Gender
                         A                  Male
                         B                  Male
                         C                  FeMale
                         D                  FeMale
I want only one update query to update this table like this
table structure:- Name           Gender
                         A                  FeMale
                         B                  FeMale
                         C                  Male
                         D                  Male
   please help me?                  

View 1 Replies View Related

I Am Really Confusing Please Help Me About A Single Query

Sep 1, 2005

Hi Guys,This is my Problem.A table contain following desing for handling different level of categories, bu it is dynamic int_categoryid,int_parent_categoryid,int_categorylevel,str_categoryname,bit_activethatall.I want to list data from table as following ordercategorry_parent11               category_child12                        category_child13                        category_child23               category_child22categorry_parent21.............................................................................................like this..ie we can insert parent category and sub category to n level dynamically without adding a new tableplease mail me for another clarification....please help meregardsAbdul

View 2 Replies View Related

Query On A Single Word

Aug 30, 2005

Hi i am using Access as my database (i know its an MS SQL forum but that's the closest i could find, and maybe its more of an sql question).

I am trying to select all rows that contain i.e. the word "Art"

SELECT * FROM table1 WHERE column1 LIKE %Art%

as you can see column1 contain some words and not just "ART"
however i am getting also the following:

CART , ARTI, DARTS - since all has arts.

( i can use '=' since the column consist of other words)
The query should also load : Art. Art, Art! Art? and "ART"

Is there an easy solution or i need to create a huge condition ?

View 2 Replies View Related

Need Single Select Query

Feb 8, 2007

Hello Everyone,

Consider the below table

DocNbr Cheques
00001 101010
00002 101010
00002 102020
00003 103030
00004 103030
00004 104040
00005 105050

I just need the single select to result the docnbr which is repeating the values. In the above case I want my result like below where the DocNbr 00002 and 00004 repeated their values.

DocNbr Cheques
00002 101010
00002 102020
00004 103030
00004 104040

Thanks in advance,

Senthil .G

Note: I do not consider if the cheque contain repeated values

View 3 Replies View Related

AND Query In Single Column

Jul 23, 2005

Hi groupI have a rather peculiar question, and I really don't know how to solvethis within an SQL statement:Given a view (v), that results in:IDX-----------------1a1b2a2c3aI'd like to query the view with something like:SELECT ID FROM v WHERE (X='a' AND X='b') which would result in:ID-----------------1or in another case:SELECT ID FROM v WHERE (X='a' OR X='c')would give:ID-----------------123how can this be done?TIAbernhard--www.daszeichen.chremove nixspam to reply

View 14 Replies View Related

2 Group By In A Single Query

Jul 20, 2005

Say the following are the columns of a tableA B C D E FCan a aggregate function like sum be applied to A like sum(a) and thenorder by b and c similarly aggregate function on d and group by e andf using a single query...Regards,Jai

View 2 Replies View Related

Select 1-20 In Single Query.

Oct 2, 2007


Hello,

I have a question which was given to me in a test. Question is :: print value from 1-20 in a single column using single select query without using any table.
If any one have solution, then please help me.

View 18 Replies View Related

I It Possible To Get This Result By Single Query ?

Oct 13, 2007

hello
i am a beginner
i it possible to get this result by single query ?

i got a int column and a datetime column
is it possible to get the sum(int column) for all the year 2007 and by the april2007 and by the month n year by the person name Tony

so the end result will look like this

year 2007 | current_month | tonys_group
-----------------------------------------------
$ 222 $22 $2

View 2 Replies View Related

Can I Update Two Tables In A Single Query

Jun 14, 2008

hi friends

in my sqlserver 2005 i want to update two tables in a single query.

is it possible? or is it possible by creating a view?

thanks in advance.

shweta

View 3 Replies View Related







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