May 17, 2011 at 12:01 pm
I am trying to write a query that will pull a count of the number of parts and the number of tickets, and then create another column showing the count of the parts / the count of the tickets and exclude those that are equal (100%)
with the following tables:
OrderItem
Number
Id
Part
Ticket
Order
Number
DateCreated
So far I have this, but I cannot get the date added from the order table and I am not sure how to create another column comparing the count of a count:
SELECT
OrderItem.Number,
OrderItem.Id,
COUNT (OrderItem.Part),
COUNT (OrderItem.Ticket),
FROM
OrderItem
GROUP BY
OrderItem.Number, OrderItem.Id
May 17, 2011 at 4:00 pm
It would help if you provided schema and data but I do not understand your GROUP BY Clause?
You need a subquery to derive the results needed.
Just though you are not misunderstood exclude those that are 100% of what?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
May 18, 2011 at 3:12 am
Firstly, could do with DDL and sample data 🙂
e.g.
--==Sample Data, please check this is correct==-
DECLARE @OrderItem AS TABLE(Id INT, Number INT, Part INT, Ticket INT)
DECLARE @Order AS TABLE(Number INT IDENTITY, DateCreated DATETIME)
INSERT INTO @OrderItem(Id, Number, Part, Ticket)
SELECT TOP 250000
FLOOR(RAND(CHECKSUM(NEWID())) * 10)+1,
FLOOR(RAND(CHECKSUM(NEWID())) * 10)+1,
FLOOR(RAND(CHECKSUM(NEWID())) * 3)+1,
FLOOR(RAND(CHECKSUM(NEWID())) * 5)+1
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
INSERT INTO @Order(DateCreated)
SELECT '2011-05-18'
UNION ALL SELECT '2011-05-13'
UNION ALL SELECT '2011-05-11'
UNION ALL SELECT '2011-05-10'
UNION ALL SELECT '2011-05-09'
UNION ALL SELECT '2011-05-17'
UNION ALL SELECT '2011-05-15'
UNION ALL SELECT '2011-05-14'
UNION ALL SELECT '2011-05-13'
UNION ALL SELECT '2011-05-13'
Next, you should attempt to get the first part of the query correct.
Here's a query to get you started. I've made lots of assumptions to do with your data and tables - so this may not be correct.
SELECT a.Number, a.Id,
COUNT(a.Part) AS count_part, COUNT(a.Ticket) AS count_ticket,
b.DateCreated
FROM @OrderItem a
LEFT OUTER JOIN @Order b ON a.number = b.number
GROUP BY a.Number, a.Id, b.DateCreated
When you're sure that this part of the statement is correct, you can turn it into a sub query which will allow you to compare the two "counts".
e.g.
SELECT count_ticket-count_part AS ticket_minus_part,
count_ticket+count_part AS ticket_plus_part,
count_part-count_ticket AS ticket_minus_part,
datecreated, number, id
FROM (SELECT a.Number, a.Id,
COUNT(a.Part) AS count_part, COUNT(a.Ticket) AS count_ticket,
b.DateCreated
FROM @OrderItem a
LEFT OUTER JOIN @Order b ON a.number = b.number
GROUP BY a.Number, a.Id, b.DateCreated) sub
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply