Grouping Without GROUP BY?
Mar 14, 2007
Hi everyone! I'm new to the this forum, but have a little SQL background. Looking forward to learning a lot from everyone here.
I have a report grouping question, but I don’t think the GROUP BY command will work. Look at the sample table below:
Person FavColor
------ --------
David Red
Mike Black
John Red
Steve Red
Sam Black
Adam Purple
Currently, the above temp table is created via a SQL sproc and returned to a Cold Fusion web page and displayed as a simple table. What I’d like to do is have the display look like this:
BLACK
Mike
Sam
PURPLE
Adam
RED
David
John
Steve
My question is, is it possible to program SQL to output the above so I can just dump the output into the Web page, or do I have to program the Web page to take my current result set and somehow iterate through the table to pull out and display group headings?
I don’t need to show subtotals or grand totals, so I'm thinking GROUP BY will not work here. I’m using SQL 2000 and do NOT have Reporting Services.
Any help would be greatly appreciated. Thanks!
David
View 11 Replies
ADVERTISEMENT
Jan 23, 2007
Hi, I am currently trying to create a report the dynamicaly groups from parameters. The grouping part works fine but I need to show the parameter label name or the field referenced by a parameter.
=iif(Parameters!Param1.Value="P",Parameters!Param2.Label,Fields(Parameters!Param1.Value).Value)
This is the expression I have written to do this. The problem seems to be is with the true part of the iif statement. The false displays fine in the group header. The Parameters!Param2.Label also displays if used on its own.
This is the error that is fires back when I run the report.
The Value expression for the textbox €˜textbox4€™ contains an error: The expression referenced a non-existing field in the fields collection.
I am quite new to SSRS and I am using VS2005 pro.
Thanks
D
View 1 Replies
View Related
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
Sep 29, 2015
I have an SSRS 2012 table report with groups; each group is broken ie. one group for one page, and there are multiple groups in multiple pages.
'GroupName' column has multiple values - X,Y,Z,......
I need to group 'GroupName' with X,Y,Z,..... ie value X in page 1,value Y in page 2, value Z in page 3...
Now, I need to display another column (ABC) in this table report (outside the group column 'GroupName'); this outside column itself is another column header (not a group header) in the table (report) and it derives its name partly from the 'GroupName' Â values:
Example:
Value X for GroupName in page 1 will mean, in page 1, column Name of ABC column must be ABC-X Value Y for GroupName in page 2 will mean, in page 2, column Name of ABC column must be ABC-Y Value Z for GroupName in page 3 will mean, in page 3, column Name of
ABC column must be ABC-Z
ie the column name of ABC (Clm ABC) Â must be dynamic as per the GroupName values (X,Y,Z....)
Page1:
GroupName          Clm ABC-X
X
Page2:
GroupName          Clm ABC-Y
Y
Page3:
GroupName          Clm ABC-Z
Z
I have been able to use First(ReportItems!GroupName.Value) in the Page Header to get GroupNames displayed in each page; I get X in page 1, Y in page 2, Z in page 3.....
However, when I use ReportItems (that refers to a group name) in the Report Body outside the group,
I get the following error:
Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope
I need to get the X, Y, Z ... in each page for the column ABC.
I have been able to use this - First(Fields!GroupName.Value); however, I get ABC-X, ABC-X, ABC-X in each of the pages for the ABC column, instead of ABC-X in page 1, ABC-Y in page 2, ABC-Z in page 3, ...
View 4 Replies
View Related
Nov 27, 2007
Hi!
I've posted a feedback with Microsoft to see if we can get them to fix the issue described below, but so far no one from Microsoft has commented to let us know what they're doing about this problem! I'm posting this here to see if maybe we can get more people to rate this feedback or chime in on what a pain it is! Please feel free to add your own comments or how you had to work around this issue and whether or not you think this is something Microsoft should be addressing NOW.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=311679
Provide Individual Page Numbering per Group and Total Pages per Group
Currently in a Reporting Services report, you can't readily reset the page number for each group in a table, nor can you display the total number of pages per group. For example, if I'm printing invoices and each invoice is a separate group, I'd like to be able to print "Page 1 of 5" , "Page 2 of 5" etc. for the first invoice, then "Page 1 of 3" when the next invoice begins, and so on. This was easy in Crystal Reports. I realize that Crystal Reports has a two-pass process that enables that kind of pagination. However, this is REALLY important functionality that's just missing from Reporting Services and I'm hoping you'll provide it REALLY SOON! Yeah, I know there are work-arounds if you can know exactly how many rows of information there are on each page. But gosh! That's not practical, especially if you have second level groups inside the main group or text blocks in rows that can 'grow' to more than one line. I've read a couple of work-arounds, but none of them works correctly and consistently when more than one user is running the same report or when you print the report while you're looking at it on the screen. I still may need access to the overall report page number and the overall total number of pages, so don't get rid of that. It's just that if you're doing this already for the entire report, I don't see why you can't do it per group! Lots of people have been asking for this for years, and I don't understand why it hasn't been implemented.
I've read a few articles on this topic, but no one has come up with a decent work around. My theory is that Microsoft should be addressing this immediately. This is major functionality that's just plain missing from SSRS and should have been there from the start. If anyone from Microsoft can let us know what's going on with this issue or if anyone would like for me to clarify this further, feel free to let me know.
Thanks!
Karen
View 1 Replies
View Related
Nov 6, 2015
I have an SSRS report with groups that when exported to excel contains drill-in's (plus marks on left side). The issue I have is that for all the groups in the drill-in, those cells become merged. I want to keep the group drill-in but have the cells UNMERGED. I have heard this can be done with the RDL XML but I don't know what to modify to accomplish this.Â
View 4 Replies
View Related
Jan 10, 2014
I'd like to ask how you would get the OUTPUT below from the TABLE below:
TABLE:
id category
1 A
2 C
3 A
4 A
5 B
6 C
7 B
OUTPUT:
category count id's
A 3 1,3,4
B 2 5,7
C 2 2,6
The code would go something like:
Select category, count(*), .... as id's
from TABLE
group by category
I just need to find that .... part.
View 3 Replies
View Related
Sep 25, 2003
I have a user in SQL Server with a NT login of Mike
I changed his NT account to Mikel in User Manager
Now when I try to add Mikel, Im getting error 15401.
Do I need to delete NT login in SQL Server 'Mike' account first ?..before adding 'Mikel' ?
Can I go into the Master database and just change Mike login to Mikel ?
Thank you
View 3 Replies
View Related
Feb 28, 2014
I'm having a fight with Reporting Services at the minute when trying to compute an average at the row group level for a value summed in a column group.I have the following column groups:
Year
Month
Date
And the following row groups:
Region
Product
SubType (hidden, data at the date level is summed to Product)
At the moment I'm computing the average for SubType for each Date at the Product level (giving a decimal value), so for each day I end up with a nice average, that works. However I am unable to average that average over the whole Year for a Product. The issue being that I'm trying to combine Row Groups (Product) and Column Groups (Date/Year)
View 0 Replies
View Related
May 1, 2014
select top 15 count(*) as cnt, state from table
group by state
order by cnt desc
[code[...
Can the above three queries be combined into one and still be fast, if so how?What i am trying to go is an item count, by group, similar to ones Inbox in Outlook.
View 9 Replies
View Related
Nov 21, 2007
I have a need to show a row inside a table group to simulate a header row for the data rows inside the group. The table will not have a real header or footer. Thanks for the help.
View 1 Replies
View Related
Jan 5, 2008
I have one domoain in the forest. The domain level is set to Windows 2000 native mode and forest level is set to mixed mode. My SQL server 2005 server joined to this domain. I added a brand new domain local group and add a normal user account to this domain local group. I login to the SQL server 2005 server and make a query "SELECT * FROM sys.login_token". I cannot see my domain local group in sys.login_token. However, if I add my account to a global group, I can see it there.
Then, I setup another forest. This time, I have domain level set to Windows 2003 mode and forest level is set to Windows 2003 native mode. I do the same testing. This time, I can see my domain local group in sys.login_token.
Why does SQL server 2005 has this limitation? Is it a bug?
View 1 Replies
View Related
Feb 6, 2008
HiI am new to SQL and am having a problem. I need to fix my query to do the following...2) get a total of the number of rows returned.
DECLARE @StartDate varchar(12)DECLARE @EndDate varchar(12)DECLARE @Region varchar(20)
SET @StartDate = '01/01/2002'SET @EndDate = '12/31/2008'SET @Region = 'Central'
SELECTA.createdon,A.casetypecodename,A.subjectidname,A.title,A.accountid,A.customerid,A.customeridname,B.new_Region,B.new_RegionName
FROM dbo.FilteredIncident AINNER JOIN dbo.FilteredAccount B ON A.customerid = B.accountid
WHERE (A.createdon >=@StartDate AND A.createdon <= @EndDate)AND (B.new_RegionName = @Region)AND (A.casetypecode = 2)
View 1 Replies
View Related
Jan 29, 2007
I'm new to MSSQL 2005 and want to get a summary of a log table. I want to count all the rows for each date based on a DATETIME field called 'post_date' that holds the date and time of each record's creation.
this is the best I can come up with:
Code:
SELECT
DISTINCT(LEFT(post_date,11)) AS post_date, COUNT(DISTINCT(LEFT(post_date,11))) AS total_posts
FROM log_directory_contacts
GROUP BY post_date
The results show each date but the count column ('total_posts') returns '1' for every row even when I know their are more than 1 record on that date.
What am I doing wrong? Thanks!
View 9 Replies
View Related
May 28, 2007
Hi folks,
I have a Projects , each project have many tasks now i want to display tasks replated to each project:
for example:
Project1-------------------->task1
task2
task3
task4
Project2----------------------->task4
task5
task6
.............................................projectN.....................
how to write query for this
i have 2 tables:
Project .......>columns are projectid
Task------------->columns are projectid, taskid
|
View 4 Replies
View Related
Jun 6, 2007
I have a matrix with two row groups and one column group with about 6 items in it. I have about 2100 rows at the lowewst row group level. This report was built solely for excel export. The first row group has about 20 items and controls the visibility of the other group. When I toggle the visibility of the second row group, how can I make the the header of the first row group copy down for each row of the other row group? The first row group is the Section and the second is Mnemonic.
Example:
Now:
Code Snippet
Column Column
Section1 -
Mnemonic
Mnemonic
Mnemonic
Mnemonic
Section2 -
Mnemonic
Mnemonic
Mnemonic
Mnemonic
Should be:
Code Snippet
Column Column
Section1 -
Section1 Mnemonic
Section1 Mnemonic
Section1 Mnemonic
Section2 -
Section2 Mnemonic
Section2 Mnemonic
Section2 Mnemonic
View 2 Replies
View Related
Feb 12, 2015
I have a code like this:
SELECT Node_ID,Day,Operation, AA,BB
FROM
(SELECT
CASE
WHEN Operation LIKE 'NOTIFY' THEN SUM(Total_request) ELSE 0 END AS AA,
CASE WHEN OPERATION LIKE 'SEARCH' THEN SUM(Total_requests) ELSE 0 END AS BB,Node_ID,DAY,Operation
[code]....
So i want to make two columns by the name of operation. in the real code AA and BB are calculates with many counters. My code doesn't work, I have an error: "not a single-group group function" .....
View 1 Replies
View Related
Jul 23, 2005
Hi,I was hoping someone may be able to help me with a tricky T-SQLproblem.I need to come up with a SELECT statement that does basically thefollowing:Select RCRD_REFNO, MAX(MODIF_DTTM) as MODIF_DTTM from[StageDb].[dbo].tblPersonInfo group by RCRD_REFNOHowever, I need to select ONLY the TOP 1 of each group (i.e. only 1record for each unique RCRD_REFNO). The problem is of course that if Iadd 'top 1' after select, it only brings back 1 record full stop,rather than 1 for each group!Now, I have previously come up with a similar query that DOES do thissuccessfully, but it relies on a criteria (such as a unique identifier)-unfortunately, the nature of the table I'm using for this currentjob means that it actually doesn't have a primary key, as it'ssimply a staging area for raw data, and can even have completelyidentical records in it. I think the only way I'm going to be able todo it is to literally use the 'TOP' command somehow, but am notsure how to adapt the above to implement it...I'd be very gratefulfor any advice.Many thanks
View 6 Replies
View Related
Sep 6, 2007
Here is my Table Structure ( from Oracle database)
Team | Customer Code | Amount | Credit Limit
1 , a, 100, 1000
1 , a , 200, 1000
1 , b, 100, 100
1, b, 1000, 100
1, b, 2000, 100
2, a, 100, 2000
For the Report, I want to group the Team and Sum each customer total Amount and Show the Exceed limit amount.
Here I want to present
Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 3000
2 a 100 2000 0
Team Total 100 0
Total 3400 3000
BUT it turn out..
Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 2300 ( Problem here a )
2 a 100 2000 0
Team Total 100 0 ( Problem here a )
Total 3400 2400 ( Problem here b)
I Grouped the Custoer Code and Team I can preform the sum
however I can't Do the Exceed total
becoz the value should be
iif (Sum(amount)>(Creditlimt) , Sum(amount)-First(Creditlimt), 0)
but for the team total in team 1 the result is 2300 ( 3300 - customer a 's limit) not add from exceed amount
And the finial total it turns out 2400 (3400 - 1000)
I have tried use the coding to sum up the exceed
but I found that the group total is sumup first than the sum up the detail :
Team Customer Code Amount Credit Limit Exceed
1 a 300 1000 0
1 b 3100 100 3000
Team Total 3300 0
2 a 100 2000 0
Team Total 100 3000 ( The Total from Team 1 ! )
Total 3400 0 ( Problem here b)
this situration , I can't change the query statement
I can do the good result for CR report
but for reporting service 2005, I can't to the first report result
Any one can help me ??
thank you
View 9 Replies
View Related
Nov 20, 2015
need to migrate a cluster with an AG dtabases to new data center cluster with AG.
I was wondering if is possible to do mirroring on top of the AG configuration? or what other options could be to migrate a cluster that has 3 nodes and setup the ag databases to a new datacenter.
View 17 Replies
View Related
May 19, 2015
partition with single file group or multiple file group which one best.
we have some report running from partition table, few reports don't have any partition Key and after creating 400 partition with 400 file group it is slow.what is best practices to crate 400 file group or single file group.
View 9 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
