Help With Grouping Query...
Dec 5, 2005
I have a table with the following structure:
main_category| category| sub_category| answer|date
Basically, the data will be along these lines:
Neuro | LOC | Status | answer1|date
Neuro | LOC | Status | answer2|date
Neuro | LOC | Status | answer3|date
Senso| Visi | Clarity | answer1|date
Senso| Visi | Clarity | answer2|date
etc...
I am trying to query the db and present the user with the data in the
following structure:
Main Category
Category
Sub Category
answer1
answer2
answer3
...
Main Category
Category
Etc...
There are literally 3 dozen main categories, categories, and
sub-categories each with distinct answers.
I could really use some help on a query to group the data in this way!
Thanks in advance!!!
Frank
View 4 Replies
ADVERTISEMENT
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
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
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
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
Jan 15, 2004
I have the following information in a table
ACCNORundateTRDCAPTRANQTYDLPRCE NOTEAMNT
27547920031202A-170002150000
27547920031202A-27412150000
27547920031202A-259215000-42501729
I need to create a query that totals TRANQTY and arrives at a result as in the following record.
ACCNORundateTRDCAPTRANQTYDLPRCE NOTEAMNT
27547920031202A-20000215000-42501729
and now for the $1M question...How ? I've tried the following select, but it is not working the way I want it to..
SELECT c2.ACCNO, c2.Rundate, c2.TrdCap, c2.TRANQTY, c2.DLPRCE, c2.NOTEAMNT
FROM CLIENTSHAREDEALS c2 FULL OUTER JOIN
(SELECT c1.ACCNO, c1.SHARENAME, SUM(c1.TRANQTY) AS Expr1
FROM CLIENTSHAREDEALS c1
WHERE (c1.ACCNO = '275479')
GROUP BY c1.ACCNO, c1.RUNDATE, c1.SHARENAME) c1 ON c1.ACCNO = c2.ACCNO AND c1.RUNDATE = c2.RUNDATE
WHERE (c2.ACCNO = '275479')
ORDER BY c1.RUNDATE
Thanks
View 2 Replies
View Related
Feb 26, 2008
HI all, I got a tsql that needs to be simplified.
Select * from Table1 where condition1 and id not in
( Select id from table1 where condition2 and id in
( Select id from Table1 where condition1 )
)
basicly all records thats in condition1 but that doesnt have condition2 but limited to condition1. I'm probably maken this to complicated. but im tired and im losing time just on one stupid query. Thanks for the help.
View 9 Replies
View Related
Jun 13, 2004
I know this has been posted before, but I can't find the previous threads so please bear with me....
I want to grab the very 1st record of each product in a table like this
ID CLIENTID PRODID
1 a 1
2 b 1
3 c 1
4 a 2
5 b 2
6 c 2
7 a 3
8 b 3
9 c 3
so that I'd get a record set like:
ID CLIENTID PRODID
1 a 1
4 a 2
7 a 3
Thanks for the hellp guru's
View 4 Replies
View Related
Jan 5, 2005
Hello, everyone:
I have a table like:
Col1Col2
1A
2B
1D
1P
2F
2W
How to query this table to return by Col1 like
Col1Col2
1A,D,P
2B,F,W
Thanks a lot
ZYT
View 11 Replies
View Related
Aug 22, 2014
I have below data in one of my table:
Calls|Status
4|-6
12|-11
1|0
1|-10
Desired Output required :
Total_calls|Zero|Non-Zero
18|1|17
View 3 Replies
View Related
Nov 8, 2005
Hi,I have data stored as in below sample :-------------------------------+---------------------------------+--------------DateBegin | DateEnd | Rate-------------------------------+---------------------------------+--------------2005-11-13 00:00:002005-11-14 00:00:0063.00002005-11-14 00:00:002005-11-15 00:00:0063.00002005-11-15 00:00:002005-11-16 00:00:0045.00002005-11-16 00:00:002005-11-17 00:00:0045.00002005-11-17 00:00:002005-11-18 00:00:0045.00002005-11-18 00:00:002005-11-19 00:00:0045.00002005-11-19 00:00:002005-11-20 00:00:0045.00002005-11-20 00:00:002005-11-21 00:00:0063.00002005-11-21 00:00:002005-11-22 00:00:0063.0000-------------------------------+---------------------------------+--------------I have to group the select query in this way :-------------------------------+---------------------------------+--------------DateBegin | DateEnd | Rate-------------------------------+---------------------------------+--------------2005-11-13 00:00:002005-11-15 00:00:0063.00002005-11-15 00:00:002005-11-20 00:00:0045.00002005-11-20 00:00:002005-11-22 00:00:0063.0000-------------------------------+---------------------------------+--------------When I run below grouped statement, I get follewed result:SELECT MIN(DateBegin) AS DateBegin, MAX(DateEnd) AS DateEnd,Rate FROM X GROUP BY Rate-------------------------------+---------------------------------+--------------DateBegin | DateEnd | Rate-------------------------------+---------------------------------+--------------2005-11-13 00:00:002005-11-22 00:00:0063.00002005-11-15 00:00:002005-11-20 00:00:0045.0000-------------------------------+---------------------------------+--------------How can I do a query like in 2nd sample from top?best regards,rustam bogubaev
View 2 Replies
View Related
Jul 20, 2005
I'm having much difficulty figuring out how to write the followingquery. Please help!I have this table:EventEventId int Primary KeyPatientId intSeverityLevel intWhat I want returned in my query is a list of all (distinct)PatientIds appearing in Event, with the *most severe* EventId returnedfor each Patient. The higher the value of SeverityLevel, the moresevere that Event is considered to be.The problem I am having is that I can't figure out how to (a) group byPatientId, AND (b) return the EventId of the highest-severity Eventfor *each* PatientId (Order By SeverityLevel Desc).So if my table contained:EventId PatientId SeverityLevel------- --------- -------------1 1 02 1 13 1 54 2 55 2 2I would want my result set to be:PatientId EventId--------- -------1 32 4since events 3 and 4 are the most severe events for patients 1 and 2,respectively.Any help would be greatly appreciated. This seems to be something thatcould be handled easily with a FIRST() aggregate operator (as in MSAccess) but this is apparently lacking in SQL Server. Also note theremay be multiple Events with a given PatientId and SeverityLevel, inthat case I'd want only one of the EventIds (the Max() one).Many thanks,Joel ThorntonDeveloper, Total Living Choices<joelt@tlchoices.com>(206) 709-2801 x24
View 7 Replies
View Related
Apr 13, 2006
Hi,
How can I make a query and group the registries in a interval of 30 seconds...like
for each line I have a datetime field that have all the day, and I need it to return just like
TIME Contador_type1 Contador_type2 Total
01-01-2006 00:00:30.000 2 5 7
01-01-2006 00:01:00.000 3 7 10
It's just an example...but that's the result that I need and my table is
data_hora -- datetime field
tipo - 1 or 2 -- count
nrtelefone - that's is the number dialed.
Thanks
View 16 Replies
View Related
Dec 12, 2007
Hi,
Below is my DB Table..
Owner varchar(500)
contains...
OwnerA
OwnerB
Book varchar(500)
contains values...
Book1
Book2
Book3
Book1 might be owned by OwnerA
and Book2 might be owned by OwnerA and OwnerB
So in the Table is this...
ID Book Owner
1 Book1 OwnerA
2 Book2 OwnerA
3 Book2 OwnerB
How would I output this relationship in sql?
Thanks.
View 1 Replies
View Related
Jul 30, 2007
Hello,
I have a table similar to the following (XYZ). I would like to write a select statement that will return the count of the unique items for each user that also happen to be less than 1 year old. The less than one year old part is rather easy dateadd(year, -1, getdate()), but I seem to be having a hard time figuring out how to get my desired result without using subselects. Any help greatly appreciated. Thanks in advance - Dan.
So my goal results are:
User Count
Dan 2
Dave 1
Table XYZ
ID User Item Value Date
1 Dan 1 20 5/5/2007
2 Dan 1 30 6/5/2007
3 Dave 2 25 6/1/2007
4 Dan 2 22 5/1/2007
5 Dan 3 23 5/1/2006
View 6 Replies
View Related
Jul 29, 2015
I am trying to generate XML path from a SQL Server Table. Table Structure and sample data as below
CREATE TABLE #OfferTransaction
( [OfferLoanAmount1] INT
,[offferid1ProgramName] VARCHAR(100)
,[Offer1LenderName] VARCHAR(100)
,[offerid1LenderNMLSID] INT
[code]....
what changes do I need in my query so that the XML looks like the one above ( DESIRED XML). Is it possible via query changes?
View 3 Replies
View Related
Mar 13, 2012
I have a query where I have customers, date they ordered a swatch, date they ordered an item, and eh date diff between the two. I want to show the MIN date diff for each customer, and also show the swatch date and item date as well. But to use the MIN aggregate, it forces me to group everything, where I just want to group by customer, and have the 2 dates tag along, because i only want one record per customer. What is the easiest way for me to accomplish this?
SAMPLE:
CustKeySwatchDateRugDateDateDiff
10903963126678366
10903963126837525
10903963126960648
10913962286550322
1115886193625764
1129666456646711
1146986229625324
1146986229627647
11469862296667438
1146986656666711
1146986624666743
DESIRED RESULTS:
CustKeySwatchDateRugDateDateDiff
10903963126678366
1115886193625764
1129666456646711
1146986656666711
View 7 Replies
View Related
Mar 5, 2015
I have the query below which produces a succesful output but as there is more than one course date the month appears for example three times where there are three courses in Jan as the example output below how can I change the query to group these
MonthYear CCG AttendedCity CCG DNACity CCG Cancelled
Oct2014010
Jan2015000
Jan2015000
Jan2015100
Feb2015000
Mar2015210
May2015010
SQL QUERY
SELECT CONVERT(char(3), dbo.tblCourses.CourseDate, 0) AS Month, YEAR(dbo.tblCourses.CourseDate) AS Year, SUM(CASE WHEN a.AttendanceStatus IN (9)
THEN 1 ELSE 0 END) AS [City CCG Attended], SUM(CASE WHEN a.AttendanceStatus IN (3) THEN 1 ELSE 0 END) AS [City CCG DNA],
[Code] ....
View 2 Replies
View Related
May 22, 2008
I have a query that gets a supplier, a month, a year, status and sum of recpits.
returning the following. but my problem is I also need a col of totals. i tried to put a sub grouped query
in the select statement but keep getting an error
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
AR162600 ARROW ELECTRONICS 10424 Early 1 2008 2007-12-31 23:59:59.997
AR162600 ARROW ELECTRONICS 516 Late 1 2008 2007-12-31 23:59:59.997
AR162600 ARROW ELECTRONICS 279603 On-Time 1 2008 2007-12-31 23:59:59.997
my qurey is below. how can I get another col called total it will be the same value on each row.
AR162600 ARROW ELECTRONICS 10424 290543 Early 1 2008 2007-12-31 23:59:59.997
AR162600 ARROW ELECTRONICS 516 290543 Late 1 2008 2007-12-31 23:59:59.997
AR162600 ARROW ELECTRONICS 279603 290543 On-Time 1 2008 2007-12-31 23:59:59.997
SELECT ot_ven_num, ot_ven_name, sum(ot_rec_qty) as ot_rec_qty,
ot_rec_stat, datepart(Month,ot_rec_dt) as mth,
datepart(year,ot_rec_dt) as ryear,
DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(mm, 0, ot_rec_dt), 0)) as ot_rpt_date,
(SELECT ot_ven_num, ot_ven_name, sum(ot_rec_qty) as ot_rec_qty, 'Totals' as ot_rec_stat,
datepart(Month,ot_rec_dt) as mth,
datepart(year,ot_rec_dt) as ryear,
DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(mm, 0, ot_rec_dt), 0)) as ot_rpt_date
FROM supplierOT where ot_ven_name = 'ARROW ELECTRONICS' and datepart(year,ot_rec_dt) > 2007
group by ot_ven_num, ot_ven_name, DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(mm, 0, ot_rec_dt), 0)),
datepart(Month,ot_rec_dt),
datepart(year,ot_rec_dt)) as total
FROM supplierOT where ot_ven_name = 'ARROW ELECTRONICS' and datepart(year,ot_rec_dt) > 2007
group by ot_ven_num, ot_ven_name, ot_rec_stat, DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(mm, 0, ot_rec_dt), 0)),
datepart(Month,ot_rec_dt),
datepart(year,ot_rec_dt)
View 9 Replies
View Related
Oct 6, 2015
I have query on grouping data on weekly basis..
1. Week should start from Monday to Sunday
2. It should not consider current week data(suppose user clicks on report on Tuesday, it should display the data for last week).
3. I want output like below
Week1,week2,week3..... week12,AverageofWeek
12 , 10 ,0.........0 12
View 1 Replies
View Related
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
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
