Custom Grouping Problem

Aug 23, 2007

I am having some trouble with grouping. I have a custom function that returns the field to group on depending on the parameters provided by the user. This is not working, however if I select the same value from the grouping drop down list that is returned by the custom function the grouping works. My custom funtion is as follows:

Function OrganisationGroup() As String
Dim Group As String
Group = ""
Select Case Report.Parameters!EnterpriseOption.Value
Case "1"
Group = Group1Value()
Case "2"
Group = "DepartmentDesc"
Case "3"
Group = "OrganizationDesc"
Case "4"
Group = "OrganizationDesc"
End Select

If Group = "" Then
Return "0"
Else
Return "Fields!" & Group & ".Value"
End If
End Function

Function Group1Value() As String
Dim Group As String
Group = ""
Select Case Report.Parameters!ReportType.Value
Case "1"
Group = "StockFamilyDesc"
Case "2"
Group = "GroupDesc"
Case "3"
Group = "Saleable"
Case "4"
Group = "Saleable"
Case "5"
Group = "SaleableGLCodeDesc"
Case "6"
Group = "Saleable"
End Select
Return Group
End Function

Does anyone have any ideas on why the grouping would not be working with the custom function?

Thanks in advance.

View 1 Replies


ADVERTISEMENT

Grouping And Custom Code

Jun 25, 2007

Hello everyone,



I've got an issue where I want to sum the group values and not the details, the reason is because I am hiding duplicate records. Here's how my Layout is setup.




TH

GH1 (hidden)

GH2 (hidden)

Det (hidden)

GF2 =Code.AddValue(Fields!Quantity.Value * Fieds!Cost.Value)

GF1 =Code.ShowAndResetSubTotal()

TF =Code.GrandTotal



I have the following in my Code window.




Dim Public SubTotal as Decimal

Dim Public GrandTotal as Decimal

Function ShowAndResetSubTotal() as Decimal

ShowAndResetSubTotal = SubTotal

SubTotal = 0

End Function

Function AddValue(newValue as decimal) as Decimal

SubTotal += newValue

GrandTotal += newValue

AddValue = newValue

End Function



This gives me incorrect results and I can't figure out why. Here's how it shows on my report:
















Part Number
Quantity

Cost
Regular Subtotal Method
Using Custom Code

Part 1
4,000

1.49
$5,947.20



Customer 1



$11,894.40
$0.00

Part 2
10

1.01
$10.07



Customer 2



$50.34
$5,947.20

Part 3
1

0.44
$0.44


Part 4
6,050

0.25
$1,530.41


Part 5
0

1.25
$0.00


Part 6
0

1.23
$0.00



Customer 3



$42,851.86
$10.07

Part 7
16,250

0.24
$3,922.59



Customer 4



$19,612.94
$1,530.85

Part 8
17,250

0.38
$6,544.82


Part 9
27,225

0.20
$5,380.20



Customer 5



$66,891.69
$3,922.59










Grand Total



$141,301.23
$0.00



The issues brought up from the duplicates is shown in the "Regular Subtotal Method" column (there are 2 detail records for Customer 1-Part 1, which is why it is doubled). I can't use a distinct on the SQL query because there are other fields (not shown) on the report that are different.



As you can see, the GF1 (Customer #) shows the subtotal from the previous group, and the Table Footer (Grand Total) shows 0. Why is this?



Jarret

View 2 Replies View Related

T-SQL (SS2K8) :: Select Statement That Needs To Use Custom Grouping

Aug 10, 2015

I am trying to SELECT data based on custom groups of that data. For example and in its simplest form:

SELECT COUNT(*)
FROMdbo.People
WHERE Current_Status = ‘A’
GROUP BY People_Code

The People_Code is the difficult part. The code represents the building that they work in. However, some buildings have multiple People_Codes. Kind of like multiple departments within a building.

For example:

Building NamePeople_CodeEmployee Count
Building A617535
Building B985665
Building C529212
Building C529932
Building C419816
Building D326974
Building D781024
Building E25365

Each building has a main People_code which, for this example, could be any one of the codes for the building. For example: Main code for building C can be 5292 and for building D it can be 7810.

Applying a variation (which is what I cannot figure out) of the SELECT statement above to this table, the result set for Building C must be the combined employee count of all three People_codes and must be represented by the main code of 5292 as a single row. Building D would have a row using code 7810 but will combine the employee count of codes 7810 and 3269.

I built a conversion table that would match up the main code with all of its related codes but just couldn’t seem to make it dance the way I want it to.

People_CodeNameGroupNameGroupPeopleCode
6175Building ABuilding A6175
9856Building BBuilding B9856
5292Building CBuildingCGroup5292
5299Building C AnnexBuildingCGroup5292
4198Building C Floor6BuildingCGroup5292
Etc…

The whole query is much more involved than just the simple SELECT statement used here, but if I can get this to work, I’m sure I can apply it to the full query.

View 5 Replies View Related

Grouping By Custom Datepart For Shift Patterns

Apr 27, 2006

My application has a log table with a timestamp field which represents the time when the record was inserted.

We would like query the the table and group the results into date units based on the value of the timestamp.

The grouping specification is held in another table but only specified for a single week.

For example

WeekDay Start End

1 06:00:00 14:00:00

2 08:10:00 17:00:00

What this specification means is that when the log table is queried records with a timestamp that fall on a Monday between 06:00:00 and 14:00:00 should be grouped together, on Tuesday the group is records between 08:10:00 and 17:00:00 and so on...

The only way I can think of doing this is to generate a temporary table when the log table is queried that has a unique record for each time period for the span of the query and then attempt to join this table to the log table using the timestamp and then group based on the temporary table unique ID.

I'm not sure how to generate such a temporary table from the specification table so any help would be appreciated.



Thanks in advance.

View 5 Replies View Related

Displaying Custom Properties For Custom Transformation In Custom UI

Mar 8, 2007

Hi,

I am creating a custom transformation component, and a custom user interface for that component.

In
my custom UI, I want to show the custom properties, and allow users to
edit these properties similar to how the advanced editor shows the
properties.

I know in my UI I need to create a "Property Grid".
In
the properties of this grid, I can select the object I want to display
data for, however, the only objects that appear are the objects that I
have already created within this UI, and not the actual component
object with the custom properties.

How do I go about getting the properties for my transformation component listed in this property grid?

I am writing in C#.

View 5 Replies View Related

Query Or Grouping Problem (some Kind Of Parallel Grouping?)

Nov 26, 2007

I'm really stumped on this one. I'm a self taught SQL guy, so there is probobly something I'm overlooking.

I'm trying to get information like this in to a report:

WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Detail #etc
WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Parts #etc

I'm unable to get the grouping right on this. Since the line details and line parts both are children of the line #, how do you do "parallel groups"?

There are 4 tables:

Work Order Header
Work Order Line
Work Order Line Details
Work Order Line Requisitions

The Header has a unique PK.
The Line uses the Header and a Line # as foreign keys that together are unique.
The Detail and requisition tables use the header and line #'s in addition to their own line number foreign keys. My queries ends up looking like this:

WO WOL WOLR WOLD
226952 10000 10000 10000
226952 10000 10000 20000
226952 10000 10000 30000
226952 10000 10000 40000
226952 10000 20000 10000
226952 10000 20000 20000
226952 10000 20000 30000
226952 10000 20000 40000
399999 10000 NULL 10000
375654 10000 10000 NULL
etc


Hierarchy:
WO > WOL > WOLD
WO > WOL > WOLR

It probobly isn't best practice, but I'm kinda new so I need some guidance. I'd really appreciate any help! Here's my query:

SELECT [Work Order Header].No_ AS WO_No, [Work Order Line].[Line No_] AS WOL_No,
[Work Order Requisition].[Line No_] AS WOLR_No, [Work Order Line Detail].[Line No_] AS WOLD_No
FROM [Work Order Header] LEFT OUTER JOIN
[Work Order Line] ON [Work Order Header].No_ = [Work Order Line].[Work Order No_] LEFT OUTER JOIN
[Work Order Line Detail] ON [Work Order Line].[Work Order No_] = [Work Order Line Detail].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Line Detail].[Work Order Line No_] LEFT OUTER JOIN
[Work Order Requisition] ON [Work Order Line].[Work Order No_] = [Work Order Requisition].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Requisition].[Work Order Line No_]

View 1 Replies View Related

Expression Editor On Custom Properties On Custom Data Flow Component

Aug 14, 2007

Hi,

I've created a Custom Data Flow Component and added some Custom Properties.

I want the user to set the contents using an expression. I did some research and come up with the folowing:





Code Snippet
IDTSCustomProperty90 SourceTableProperty = ComponentMetaData.CustomPropertyCollection.New();
SourceTableProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
SourceTableProperty.Name = "SourceTable";






But it doesn't work, if I enter @[System:ackageName] in the field. It comes out "@[System:ackageName]" instead of the actual package name.

I'm also unable to find how I can tell the designer to show the Expression editor. I would like to see the elipses (...) next to my field.

Any help would be greatly appreciated!

Thank you

View 6 Replies View Related

Expression Issue With Custom Data Flow Component And Custom Property

Apr 2, 2007

Hi,



I'm trying to enable Expression for a custom property in my custom data flow component.

Here is the code I wrote to declare the custom property:



public override void ProvideComponentProperties()

{


ComponentMetaData.RuntimeConnectionCollection.RemoveAll();

RemoveAllInputsOutputsAndCustomProperties();



IDTSCustomProperty90 prop = ComponentMetaData.CustomPropertyCollection.New();

prop.Name = "MyProperty";

prop.Description = "My property description";

prop.Value = string.Empty;

prop.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;



...

}



In design mode, I can assign an expression to my custom property, but it get evaluated in design mode and not in runtime

Here is my expression (a file name based on a date contained in a user variable):



"DB" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + "\" + (DT_WSTR, 4)YEAR( @[User::varCurrentDate] ) + RIGHT( "0" + (DT_WSTR, 2)MONTH( @[User::varCurrentDate] ), 2 ) + ".VER"



@[User::varCurrentDate] is a DateTime variable and is assign to 0 at design time

So the expression is evaluated as: "DB189912189912.VER".



My package contains 2 data flow.

At runtime,

The first one is responsible to set a valid date in @[User::varCurrentDate] variable. (the date is 2007-01-15)

The second one contains my custom data flow component with my custom property that was set to an expression at design time



When my component get executed, my custom property value is still "DB189912189912.VER" and I expected "DB200701200701.VER"



Any idea ?



View 5 Replies View Related

Adding Custom Property To Custom Component

Aug 17, 2005

What I want to accomplish is that at design time the designer can enter a value for some custom property on my custom task and that this value is accessed at executing time.

View 10 Replies View Related

Custom Task - Custom Property Expression

Aug 16, 2006

I am writing a custom task that has some custom properties. I would like to parameterize these properties i.e. read from a varaible, so I can change these variables from a config file during runtime.

I read the documentation and it says if we set the ExpressionType to CPET_NOTIFY, it should work, but it does not seem to work. Not sure if I am missing anything. Can someone please help me?

This is what I did in the custom task

customProperty.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;

In the Editor of my custom task, under custom properties section, I expected a button with 3 dots, to click & pop-up so we can specify the expression or at least so it evaluates the variables if we give @[User::VaraibleName]

Any help on this will be very much appreciated.

Thanks

View 3 Replies View Related

Enable Error Handling When Writing Custom Source Component /custom Error Handling Component.

Apr 21, 2006

1) We are writing a custome Source component for Oracle with OCI calls, Could some one please let me know how to Enable Error Handling for the Same,

2) Is it possible to write Custome Error Handeling Component for SSIS? if yes could you please help me on how to write it.

Thanks in advance.

View 1 Replies View Related

Grouping Twice

May 11, 2008

Hi there, i have an SQL query which select the number of products and display them per sub_cat_id e.g.
 7  french dressers3   spanish dressers1   english dresser
 However what i want to do is then group those under the the main category (cat_id)
All descriptions are held in tbl_cat and and main category and sub category can be found by the cat_cat colum, if there is an M in this colum it means it is a main category e.g. beds, tables, if there is the name of a M category then it is a sub cat e.g. double beds, single beds etc.
Can anybody help me with my grouping nightmare?
this is the code i have so far
SELECT     COUNT(tbl_stock.Stock_ID) AS TOTALOFSTOCK, tbl_stock.sub_cat_id, tbl_cat.Cat_DescriptionFROM         tbl_stock INNER JOIN                      tbl_cat ON tbl_stock.sub_cat_id = tbl_cat.Category_IDGROUP BY tbl_cat.Cat_Description, tbl_stock.sub_cat_id

View 1 Replies View Related

Need Some Help With Grouping (I Think...)

Jun 13, 2008

I have a table which lists manufacturers' products. Users are allowed to vote for the most popular item belonging to the manufacturer. Moderators assign the products to manufacturers so on occassion they may accidently (or deliberately) get it wrong. For this reason, the table also acts as the ChangeLog so if choose, I could instantly decide whether all changes made by a specific user should be dismissed. Product | Manufacturer | NoOfVotes | ChangedBy | ChangedTime
============ ============== =========== =========== =============
XBOX 360 Microsoft 25* Dic 05/03/2008
XBOX 360 Sony 1 Tom 04/03/2008
Playstation Sony 100* Dic 03/03/2008
XBOX 360 Microsoft 25* Dic 02/03/2008
XBOX 360 Sony 1 Harry 01/03/2008
 

 I'm using ID fields for Product, Manufacturer and ChangedBy which are obtained from different tables but have simplified the table to make it easier to read.
I need to retrieve each product in the table, the last Manufacturer assigned to it, with a total of the votes where the product-manufacturer match. The results should be: Product | Manufacturer | TotalVotes | LastChanged
============ ============== ============ =============
XBOX 360 Microsoft 50 05/03/2008
Playstation Sony 100 03/03/2008
 
Any help would be greatly appreciated.
Thanks, Pete.

View 3 Replies View Related

HAVING / GROUPING Help

Jul 10, 2007

Hello, All,

Here's the query I'm attempting to execute:


Code:

SELECT LEA, Count(SSN)
FROM WorkData2003_04.dbo.Students --Database and Table Name
WHERE Grade_Level='10' AND SSN NOT IN(

SELECT SSN
FROM WorkData2003_04.dbo.Students --Database and Table Name
WHERE Grade_Level='09')



However, SQL returns the following error:
Msg 8120, Level 16, State 1, Line 1
Column 'WorkData2003_04.dbo.Students.lea' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


I'm trying to generate a count of SSNs by LEA (LEA is the same thing as a school) that exist one school year but do not exist in the next school year (essentially students that transfer OUT of a school district).

Could someone provide assistance? I think I need to incorporate the HAVING clause but I'm not for sure...

Thanks!
Cody

View 6 Replies View Related

Grouping

Jun 22, 2004

I'm trying to write a report and need to group columns that are alike.
For example.

DeptID JobTitle

40344 Sales Clerk
1st Assistant
Store Manager

40666 Sales Clerk
2nd Assistant
Store Manager


Sorry, it will not output correctly, I hope you get what I'm trying to do.
I can get the results above but it puts the deptid for each job title. I do not want it. Any help will be appreciated. Thanks in advance.

View 6 Replies View Related

Need Help With Grouping !!!

Nov 15, 2005

Hi !

I have a log table like this :

UserID LoginDate
------ --------------
31 2005-11-01
31 2005-11-05
8 2005-11-02
31 2005-11-02
7 2005-11-04


I need to get the LAST login date for each user as the following result :

7 2005-11-04
8 2005-11-02
31 2005-11-05

Can someone help me please ?

Thanks !

View 2 Replies View Related

Grouping

Apr 19, 2008

I have 2 columns

RecordDate(DateTime)
Quantity(Int)

I would like to group the data quarter wise like,

Quarter 1 500
Quarter 2 340
Quarter 3 450
Quarter 4 400

Note: Say quarters are fixed from Jan-Mar,Apr-Jun etc.,



Prakash.P
The secret to creativity is knowing how to hide your sources!

View 2 Replies View Related

Grouping 1 Off

Feb 23, 2006

I am writing a report from an historical database that uses posting_times. The way the posting works is if something has a posting time of 01/01/97 00:00:00 it means that the record covers from 12/31/96 00:00:00 to 01/01/97 00:00:00.

This is where my problem is at. The report i am writing needs to be grouped by Month. Before i figured out how the posting dates worked i was using ... DATEPART(month, posting_time) AS monthDate ... for grouping purposes in the report.

Now that i have figured out how the posting dates work i am at a total loss as how to group this. I am using SQL Reporting Services 2000 to write this report.
Any help would be greatly appreciated.
Thanks
Mitch

View 5 Replies View Related

Top N From Each Grouping?

Mar 5, 2007

Is it possible to return just the top row of each group within a query result?

Par example, if I have a query that returns a list of products, grouped by order date and product name and ordered by date, name desc

How can I just get back the top row within each date grouping so that I can list the latest prod order per prod?

Thank you for any tips!

--PhB

View 20 Replies View Related

Not Grouping Right

Sep 19, 2007

its showing the promotion group not grouped right can you help


--#ttTransaction
SELECT ShipToID, Trans.StoreID, TransactionNumber, BatchNumber, [Time], CustomerID, CashierID,
CASE WHEN Trans.StoreID IN (172,173) THEN Total*CONVERT(FLOAT,(SELECT Value FROM DelSol.dbo.Parameter WHERE ID = 4))
ELSE CASE WHEN Trans.StoreID = 113
THEN CASE WHEN [Time] < '5/21/2005' THEN Total*CONVERT(FLOAT,(SELECT Value FROM DelSol.dbo.Parameter WHERE ID = 4))
ELSE Total END
ELSE Total END END AS Total,
CASE WHEN Trans.StoreID IN (172,173) THEN SalesTax*CONVERT(FLOAT,(SELECT Value FROM DelSol.dbo.Parameter WHERE ID = 4))
ELSE CASE WHEN Trans.StoreID = 113
THEN CASE WHEN [Time] < '5/21/2005' THEN SalesTax*CONVERT(FLOAT,(SELECT Value FROM DelSol.dbo.Parameter WHERE ID = 4))
ELSE SalesTax END
ELSE SalesTax END END AS SalesTax,
Comment, ReferenceNumber,
Status, ExchangeID, ChannelType, RecallID, RecallType,
CASE WHEN Trans.[Time] >= SO.ShiftDate THEN 'Same' ELSE 'New' END AS SalesType
INTO #ttTransaction
FROM QSCHQ.dbo.[Transaction] Trans
LEFT JOIN Reporting.dbo.RetailSalesComparison_StoreOpenings SO ON Trans.StoreID = SO.StoreID
WHERE YEAR([Time]) >= YEAR(GETDATE())-1



--#ttTransactionEntry
SELECT TE.Commission,
CASE WHEN TE.StoreID IN (172,173) THEN TE.Cost*CONVERT(FLOAT,(SELECT Value FROM DelSol.dbo.Parameter WHERE ID = 4))
ELSE CASE WHEN TE.StoreID = 113
THEN CASE WHEN [Time] < '5/21/2005' THEN TE.Cost*CONVERT(FLOAT,(SELECT Value FROM DelSol.dbo.Parameter WHERE ID = 4))
ELSE TE.Cost END
ELSE TE.Cost END END AS Cost,
CASE WHEN TE.StoreID IN (172,173) THEN TE.FullPrice*CONVERT(FLOAT,(SELECT Value FROM DelSol.dbo.Parameter WHERE ID = 4))
ELSE CASE WHEN TE.StoreID = 113
THEN CASE WHEN [Time] < '5/21/2005' THEN TE.FullPrice*CONVERT(FLOAT,(SELECT Value FROM DelSol.dbo.Parameter WHERE ID = 4))
ELSE TE.FullPrice END
ELSE TE.FullPrice END END AS FullPrice,
TE.StoreID, TE.[ID], TE.TransactionNumber, TE.ItemID,
CASE WHEN TE.StoreID IN (172,173) THEN TE.Price*CONVERT(FLOAT,(SELECT Value FROM DelSol.dbo.Parameter WHERE ID = 4))
ELSE CASE WHEN TE.StoreID = 113
THEN CASE WHEN [Time] < '5/21/2005' THEN TE.Price*CONVERT(FLOAT,(SELECT Value FROM DelSol.dbo.Parameter WHERE ID = 4))
ELSE TE.Price END
ELSE TE.Price END END AS Price,
TE.PriceSource, TE.Quantity, TE.SalesRepID, TE.Taxable, TE.DetailID, TE.Comment,
TE.AutoID, TE.DiscountReasonCodeID, TE.ReturnReasonCodeID, TE.TaxChangeReasonCodeID,
TE.SalesTax, TE.QuantityDiscountID
INTO #ttTransactionEntry
FROM QSCHQ.dbo.TransactionEntry TE
INNER JOIN #ttTransaction Trans
ON TE.StoreID = Trans.StoreID
AND TE.TransactionNumber = Trans.TransactionNumber
WHERE YEAR([Time]) >= YEAR(GETDATE())-1



--#ttTransactionWithPesoConversionAndCustomerAccount
SELECT Trans.StoreID, QSTStore.StoreGroupID, Trans.TransactionNumber, Trans.[Time], Customer.AccountNumber, Customer.Company,
Trans.Total, Trans.SalesTax, Trans.CashierID, Trans.SalesType
INTO #ttTransactionWithPesoConversionAndCustomerAccount
FROM #ttTransaction Trans
INNER JOIN QSCHQ.dbo.Customer Customer ON Trans.CustomerID = Customer.ID AND 1 = Customer.GlobalCustomer
INNER JOIN QSCHQ.dbo.QSTStore QSTStore ON Trans.StoreID = QSTStore.StoreID

UNION ALL

SELECT Trans.StoreID, QSTStore.StoreGroupID, Trans.TransactionNumber, Trans.[Time], Customer.AccountNumber, Customer.Company,
Trans.Total, Trans.SalesTax, Trans.CashierID, Trans.SalesType
FROM #ttTransaction Trans
INNER JOIN QSCHQ.dbo.Customer Customer ON Trans.CustomerID = Customer.ID AND Trans.StoreID = Customer.StoreID AND 1 <> Customer.GlobalCustomer
INNER JOIN QSCHQ.dbo.QSTStore QSTStore ON Trans.StoreID = QSTStore.StoreID

UNION ALL

SELECT Trans.StoreID, QSTStore.StoreGroupID, Trans.TransactionNumber, Trans.[Time], Customer.AccountNumber, Customer.Company,
Trans.Total, Trans.SalesTax, Trans.CashierID, Trans.SalesType
FROM #ttTransaction Trans
LEFT JOIN QSCHQ.dbo.Customer Customer ON Trans.CustomerID = Customer.ID AND Trans.StoreID = Customer.StoreID
INNER JOIN QSCHQ.dbo.QSTStore QSTStore ON Trans.StoreID = QSTStore.StoreID
WHERE Customer.[ID] IS NULL
AND Trans.CustomerId < 100000

UNION ALL

SELECT Trans.StoreID, QSTStore.StoreGroupID, Trans.TransactionNumber, Trans.[Time], Customer.AccountNumber, Customer.Company,
Trans.Total, Trans.SalesTax, Trans.CashierID, Trans.SalesType
FROM #ttTransaction Trans
LEFT JOIN QSCHQ.dbo.Customer Customer ON Trans.CustomerID = Customer.ID
INNER JOIN QSCHQ.dbo.QSTStore QSTStore ON Trans.StoreID = QSTStore.StoreID
WHERE Customer.[ID] IS NULL
AND Trans.CustomerId > 100000



--#ttRetailSales_ByStore_ByCustomer_ByDay
SELECT CONVERT(DATETIME, CONVERT(CHAR, [Time], 101)) AS [Date],
Trans.StoreID, Trans.StoreGroupID,
Trans.AccountNumber, Trans.Company,
SUM(TE.Price*TE.Quantity) AS Sales,
COUNT(DISTINCT Trans.TransactionNumber) AS TRXCount,
SUM(TE.Quantity) AS UnitsSold, Trans.SalesType
INTO #ttRetailSales_ByStore_ByCustomer_ByDay
FROM #ttTransactionWithPesoConversionAndCustomerAccount Trans
INNER JOIN #ttTransactionEntry TE
ON Trans.StoreID = TE.StoreID AND Trans.TransactionNumber = TE.TransactionNumber
WHERE (Trans.Total - Trans.SalesTax) <> 0
GROUP BY Trans.AccountNumber, Trans.StoreGroupID, CONVERT(DATETIME, CONVERT(CHAR, Trans.[Time], 101)),
Trans.StoreID, Trans.Company, Trans.SalesType

-- DROP TABLE #ttTransaction
-- DROP TABLE #ttTransactionEntry
-- DROP TABLE #ttTransactionWithPesoConversionAndCustomerAccount

-- ***** #ttTransGroup *****
SELECT [Date], PromotionGroupID,AccountNumber, SUM(Total) AS Total, SUM(TRXCount) AS TRXCount, SalesType
INTO #ttTransGroup
FROM
(SELECT [Date],
QSTStore.PromotionGroupID, Trans.AccountNumber,
Sales AS Total,
TRXCount, SalesType
FROM #ttRetailSales_ByStore_ByCustomer_ByDay Trans
INNER JOIN QSCHQ.dbo.QSTStore QSTStore ON Trans.StoreID = QSTStore.StoreID
WHERE Trans.AccountNumber LIKE 'C-%'

UNION ALL

SELECT TransactionDate AS [Date], QSTStore.PromotionGroupID, AccountNumber, SUM(Amount) AS Total, 0 AS TRXCount,
CASE WHEN QuickSellSalesAdjustment.[TransactionDate] >= SO.ShiftDate THEN 'Same' ELSE 'New' END AS SalesType
FROM DelSol.dbo.QuickSellSalesAdjustment QuickSellSalesAdjustment
INNER JOIN QSCHQ.dbo.QSTStore QSTStore ON QuickSellSalesAdjustment.StoreID = QSTStore.StoreID
LEFT JOIN Reporting.dbo.RetailSalesComparison_StoreOpenings SO ON QuickSellSalesAdjustment.StoreID = SO.StoreID
WHERE YEAR(TransactionDate) >= YEAR(GETDATE())-1
GROUP BY AccountNumber, QSTStore.PromotionGroupID, TransactionDate,
CASE WHEN QuickSellSalesAdjustment.[TransactionDate] >= SO.ShiftDate THEN 'Same' ELSE 'New' END) ttTemp
GROUP BY AccountNumber, PromotionGroupID, [Date], SalesType
HAVING SUM(ttTemp.Total) <> 0


-- ***** #ttCSVF *****
SELECT CSVFlag.TransactionDate, CSVFlag.StoreGroupID, CSVFlag.AccountNumber, CSVFlagType.[Description]
INTO #ttCSVF
FROM DelSol.dbo.CruiseShipVisitFlag AS CSVFlag
INNER JOIN DelSol.dbo.CruiseShipVisitFlagType AS CSVFlagType ON CSVFlagType.ID = CSVFlag.FlagTypeID



-- ***** Main *****
SELECT ttTransGroup.[Date] as TransactionDate,
QSTPromoStoreGroup.[ID] AS PromoGroupID, QSTPromoStoreGroup.StoreGroup AS PromoGroup,
ttTransGroup.AccountNumber AS AccountNumber, CruiseShip.ShipName AS ShipName,
CruiseShipGroup.[Description] AS ShipLine, CruiseShipCompany.[Description] AS PortLecturerCompany,
Sum(ttTransGroup.Total) AS GroupSales,
(CSVP.AdjustedProjection) AS Projection,
CruiseShip.Capacity, CASE WHEN CruiseShip.Capacity = 0 THEN 0 ELSE ttTransGroup.Total/CruiseShip.Capacity END AS ASPP,
CASE WHEN CONVERT(DECIMAL, CruiseShip.Capacity) = 0 THEN 0 ELSE CONVERT(DECIMAL, ttTransGroup.TRXCount)/CONVERT(DECIMAL, CruiseShip.Capacity) END AS Yield,
ttTransGroup.TRXCount, CASE WHEN ttTransGroup.TRXCount=0 THEN 0 ELSE ttTransGroup.Total/ttTransGroup.TRXCount END AS ADPT,
CruiseShipPortLecturer.[Name] AS PortLecturer,
CSVF.[Description] AS Flag, ttTransGroup.SalesType
FROM QSCHQ.dbo.QSTPromoStoreGroup QSTPromoStoreGroup
INNER JOIN #ttTransGroup ttTransGroup ON QSTPromoStoreGroup.[ID] = ttTransGroup.PromotionGroupID
INNER JOIN DelSol.dbo.CruiseShip CruiseShip ON ttTransGroup.AccountNumber = CruiseShip.AccountNumber
INNER JOIN DelSol.dbo.CruiseShipCompany CruiseShipCompany ON CruiseShip.PromotionCompany = CruiseShipCompany.[ID]
INNER JOIN DelSol.dbo.CruiseShipGroup CruiseShipGroup ON CruiseShip.GroupID = CruiseShipGroup.[ID]
LEFT JOIN DelSol.dbo.CruiseShipPortLecturer CruiseShipPortLecturer
ON CruiseShip.AccountNumber = CruiseShipPortLecturer.AccountNumber
AND ttTransGroup.[Date] BETWEEN CruiseShipPortLecturer.StartDate
AND CASE WHEN CruiseShipPortLecturer.EndDate IS NULL THEN '12/31/2099' ELSE CruiseShipPortLecturer.EndDate END

LEFT JOIN DelSol.dbo.CruiseShipVisitProjection CSVP
ON CSVP.TransactionDate= ttTransGroup.[Date]
AND CSVP.StoreGroupID = QSTPromoStoreGroup.[ID]
AND CSVP.AccountNumber = ttTransGroup.AccountNumber
LEFT JOIN #ttCSVF CSVF
ON CSVF.TransactionDate = ttTransGroup.[Date]
AND CSVF.StoreGroupID = QSTPromoStoreGroup.[ID]
AND CSVF.AccountNumber = ttTransGroup.AccountNumber
WHERE QSTPromoStoreGroup.ReceivesCruiseShips = 1
AND CSVF.[Description] IS NULL
Group By ttTransGroup.AccountNumber, QSTPromoStoreGroup.[ID],QSTPromoStoreGroup.StoreGroup,
ttTransGroup.[Date],
CruiseShip.ShipName,
CruiseShipGroup.[Description], CruiseShipCompany.[Description],
(CSVP.AdjustedProjection),
CruiseShip.Capacity, CASE WHEN CruiseShip.Capacity = 0 THEN 0 ELSE ttTransGroup.Total/CruiseShip.Capacity END,
CASE WHEN CONVERT(DECIMAL, CruiseShip.Capacity) = 0 THEN 0 ELSE CONVERT(DECIMAL, ttTransGroup.TRXCount)/CONVERT(DECIMAL, CruiseShip.Capacity) END,
ttTransGroup.TRXCount, CASE WHEN ttTransGroup.TRXCount=0 THEN 0 ELSE ttTransGroup.Total/ttTransGroup.TRXCount END,
CruiseShipPortLecturer.[Name],
CSVF.[Description], ttTransGroup.SalesType
Order by QSTPromoStoreGroup.StoreGroup

View 3 Replies View Related

Grouping

Jan 20, 2008

SQL2000. I have a table as follows. Table shows a log of solutions posted by agents. For example, Ticket#5 was replied once by each agent A,B and C.
==========================
ticket_number || agent || solution
==========================

5 || A || test
5 || B || test
5 || C || test
7 || B || test
7 || C || test
8 || C || test
9 || B || test

I need to find out number of all ticket solutions/counts (how many ticket was touched by all agents) but in format related to the single agent:

AGENT TICKET_NUMBER COUNT
A 5 3
B 5 3
C 5 3
B 7 2
C 7 2
.....

How could I achieve this?

Thank you much!
Julia

View 7 Replies View Related

Grouping

Jan 6, 2008



Hi

I have created a table and linked it to a dataset and grouped the data based on a field called SYSTEM.

Now in the group footer I have to display summary values for each system from another dataset.

But it always gives me the First Value in that. That is for all the groupings I get the summary value for the first system

only. How do I go about this.

Thanks

Sai

View 2 Replies View Related

Grouping

Dec 21, 2006

Hi,

I have a stored procedure which is returning results in the following format:

No.of substances Count

1 29

2 89

3 876

.. ..

15 56

16 89

Now i need to display like this:

No.of substances Count %

1 20

2 89

.. ..

>=9 8766

Total

All the substances which are >=9 have display no.of substances =>9 and Count is sum of all counts which have the no.of substances>=9

How to achieve this

Thanks in advance



View 1 Replies View Related

Grouping/Sum

Jun 28, 2007

The report I'm designing has a number of records in it, but two main fields that I'm concerned with Budget, and spent.



for example



Budget Spent


75 5
75 10
75 8________________
Total 225 23

The spent records are all unique, but the budget record is the same field repeating itself over and over. I've used a grouping to eliminate the repetition of the Budget amount, but the total still comes out to be incorrect. It now looks like this


Budget Spent
75
5
10
8________________
Total 225 23

Is there a way which I can add the values that are displayed rather than the underlying values?

Please Help!
Thanks,
-Shahin

View 4 Replies View Related

Grouping By Problems

Oct 16, 2006

I'h having some throughput problems, and so, I decided to create a view with the info I need. The problem now is this: I have to select all the days where there were associations in my clients website in this format: dd/MM/yyyy (xx) where XX is the number of associations on that day. Here is the first code, wich worked but resulted in timeout:SELECT DISTINCT (CONVERT(varchar, buy_date, 103) + ' (' + CONVERT(varchar(10), (SELECT COUNT(*) FROM user_plan up2 WHERE CONVERT(datetime, CONVERT(varchar, up2.buy_date, 101)) = CONVERT(datetime, CONVERT(varchar, up1.buy_date, 101)))) + ')') AS 'text',CONVERT(datetime, CONVERT(varchar, buy_date, 101)) AS 'value'FROM user_plan up1WHERE CONVERT(varchar, buy_date, 101) <= CONVERT(varchar, getdate(), 101)ORDER BY value DESCThen I tried to create a view in wich I intented to save the number of associations to avoid the n² complexity of my query... This is the create view script: CREATE VIEW quadro_social AS  SELECT COUNT(1) AS total,  CONVERT(VARCHAR, buy_date, 103) as buy_date,  CONVERT(datetime, CONVERT(varchar, buy_date, 101)) AS 'value'    FROM user_planGROUP BY buy_dateBut what happens is, becaus the "buy_date" column is datetime, they are not beign grouped because they have different times, but the same days... How can I group the registers with the same date (dd/MM/yyyy) ignoring the hour, minutes and seconds??? Thanks a lot!Guilherme Bertini Boettcher

View 2 Replies View Related

SQL Query - Need Help With Grouping

Oct 22, 2007

Hi,
I have this query...
cmd = New SqlCommand("SELECT name, webd_category_desc.category_id, (name + cast(webd_category_desc.category_id as nvarchar)) as CNameID, link_id FROM webd_category_desc left outer join webd_link_category on webd_category_desc.category_id = webd_link_category.category_id where display = 'True' order by CNameID, link_id ;", SqlConnection1)
It produces the following output (trunctated by me for this post example).




name
category_id
CNameID
link_id

Accounting/Bookkeeping
2
Accounting/Bookkeeping2
7

Accounting/Bookkeeping
2
Accounting/Bookkeeping2
22

Accounting/Bookkeeping
2
Accounting/Bookkeeping2
24

Accounting/Bookkeeping
2
Accounting/Bookkeeping2
40

Accounting/Bookkeeping
2
Accounting/Bookkeeping2
45

Accounting/Bookkeeping
2
Accounting/Bookkeeping2
89

Accounting/Bookkeeping
2
Accounting/Bookkeeping2
134

Accounting/Bookkeeping
2
Accounting/Bookkeeping2
137

Architecture
5
Architecture5
37

Architecture
5
Architecture5
90
I would like it to display instead (where 8 and 2 are the counts):
Accounting/Bookkeeping   8
Architecture   2
Seeing that I had to join a few tables to get the above output, how can I now group on it to get the name, count(name) output I desire.I'm using ADO.NET in a VB.NET/ASP.NET 2.0 webapp.  The data is in SQL Server 2000.  I was hoping to do it in one SqlCommand statement if possible.  I guess I can drop it into a view and then run my group by query on the view if I had to.
I am getting a variety of 'field in select list must be used in a function or aggregate' errors in the attempts I have tried so far.
Thanks in advance,
Stewart
 
 

View 6 Replies View Related

Grouping With SQL Query

Oct 29, 2007

Hi folks. Hopefully this is a simple question. What's the easiest and most efficient way to group by a dateTime field in an SQL query? Here is exactly what I'm trying to do. I have a database table that contains transactions from an email maillog, so there are dateTime entries every second or so. I'm trying to build a query that will group a count of messages per hour for a given day. How can I make an hourly grouping of the total number of messages?SELECT count(*)
FROM emailTable
WHERE (delivDate >= '2007-10-03 00:00' AND delivDate < '2007-10-03' 01:00)
Thanks, Chris

View 6 Replies View Related

Grouping Problems

Dec 29, 2003

Hello all,

I am having a problem. See, I have a small table that tracks users visits to my site. And I want to have it show all the people visiting first on the bottom of the page, people just visiting on top. I can solve that with a simple ORDER BY DateVisited clause. My problem is that I also want to group peoples IP addresses together too. If I don't, when two people visit the website around the same time, browse around, I get overlapping of IPs.

Example how it is now:
1. 127.0.0.1 /default.asp 12:15PM
2. 215.394.293.494 /default.asp 12:16PM
3. 127.0.0.1 /contact/default.asp 12:17PM

Example of how I would like it:
1. 215.394.293.494 /default.asp 12:16PM
2. 127.0.0.1 /default.asp 12:15PM
3. 127.0.0.1 /contact/default.asp 12:17PM

I can ORDER BY IpAddress first, but then my dates are wacked out and the people with lower initial IP numbers appear first, which is meaningless. Any advise?

View 14 Replies View Related

Grouping Result

Jan 25, 2005

Here's my query:

SELECT col_1, col_2, col_3, col_4 FROM my_table
WHERE (col_1 = @col_1) AND (col_2 = @col_2)
ORDER BY col_1

I want my result rows to have an uniqe value in col_3. How can I exclude rows (but one) that have the same value in col_3?

Thanx

/sf

View 3 Replies View Related

Grouping Datetime

Apr 11, 2000

Hello,
I am trying to group the date by minutes. The date is in
datetime format. My problem is when grouping the seconds
throws me off.
eg. id date
1 03-02-1999 10:23:12.000
2 03-02-1999 10:23:11.000
what I want is when I run count(date) and group by
date the above example should give me 2. In short it
should group 1 & 2 because I want to ignore the seconds.
How can I do that. Everything else is working except
this is kicking my brains. Any and all help aprreciated.
It is also sorts urgent.
Thanks in advance.
HP

View 3 Replies View Related

Grouping Or SUM Of Numbers

Apr 12, 2006

Hi All,

After working out the basics and getting a report to give me back rows based upon a dropdown list, i know want to ask how do i show the sum of values for a customer.

So the out put would look something like this

Customer Total
0001 12234.56
0002 232.98
0003 456.78

I have two tables "customer" and "trades" i all ready have them linked and giving me all the individual items, oh and i have got it doing BETWEEN to dates, but i would really like to know how to show just the TOTAL trades for each cutomer on each line not every single trade for the customer.

Many thanks for any help with this

Dave C :confused:

View 1 Replies View Related

Grouping Question

Nov 5, 2006

A simple query I can’t get my brain around:

I have a table that looks like this:

Col1Col2Col3
1 a1
1b2
1c3

Assume there are many more rows with different values for all columns. I want to return Col1 and Col2 based on the max value of Col3 grouped by Col1. I don’t want to aggregate Col2 but just want the value that corresponds to the aggregate max of Col3.

“SELECT Col1, Col2, MAX(Col3) GROUP BY Col1, Col2” doesn’t give me what I want and “SELECT Col1, Col2, MAX(Col3) GROUP BY Col1” isn’t allowed because Col2 isn’t aggregated or listed in the GROUP BY clause. How do I get the values of Col1 and Col2 based on the max of Col3 for the group of Col1?

View 3 Replies View Related

Grouping By Sub Query

Jan 3, 2007

is grouping by sub query possible?

ie.

Code:


select
(select fieldx from tabley where pk = tz.fk) as field1,
field2
from
tablez tz
group by
field1



this doesn't work..i get an error that field1 is not valid...so is there a way to do this that does work?

please realize that the above example is exactly that..and had i needed to do something that easy, join would be the easy choice..what i'm trying to do requires a sub query

View 14 Replies View Related







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