June 9, 2017 at 8:13 am
I have an orders table that i want to display with some aggregate derived columns , and i want to count the instances a value shows up per customerid. One derived column [DOCKED] will have a join into another table via the customerid
TABLE dbo.orders --o
(
OrderID (pk, int, NOT NULL),
CustomerID varchar(5) NOT NULL,
Sourceid int NOT NULL,
Status varchar(50) null,
ordershipped datetime NULL,
orderarrived datetime NULL
CONSTRAINT PK_OrderID PRIMARY KEY CLUSTERED (OrderID ASC)
);
TABLE dbo.Ordermessages --om
(
OmID (pk,varchar(5), NOT NULL),
Sourceid int NOT NULL,
dockcode varchar(5),
dockdate datetime NULL,
location nvarchar(50) not null
CONSTRAINT PK_customerID PRIMARY KEY CLUSTERED (OmID ASC)
);
SELECT coalesce (Customerid,'Total') AS CUSTID
[READY] = sum (case when o.status = 'Confirmed' then 1 else 0 end)
,[DOCKED] = sum (case when o.status = 'Confirmed' and om.dockcode ='z' AND om.dockdate >= GETDATE() -1 then 1 else 0 end)
FROM Orders o
join Ordermessages om on o.sourceid = om.sourceid
where o.Customerid <> ''
GROUP BY ROLLUP (Customerid);
Thanks in advance
Zo
June 9, 2017 at 8:54 am
What is the issue? Please see the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 9, 2017 at 9:59 am
Thanks - the problem lies within the sourceid will appear multiple times in both tables that are associated with the customerid , and need to devise a JOIN between the Orders and Ordermessages with a condition perhaps tied to the ON sourceids , or a subquery that ties in the customerid to respective sourceid, and the Group By will represent the total for the derived columns for each Customerid
June 9, 2017 at 10:04 am
Zososql - Friday, June 9, 2017 9:59 AMThanks - the problem lies within the sourceid will appear multiple times in both tables that are associated with the customerid , and need to devise a JOIN between the Orders and Ordermessages with a condition perhaps tied to the ON sourceids , or a subquery that ties in the customerid to respective sourceid, and the Group By will represent the total for the derived columns for each Customerid
That didn't make sense to me. Could you provide some sample data that is representative of the problem and what you expect for output?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 9, 2017 at 8:44 pm
Sean Lange - Friday, June 9, 2017 10:04 AMZososql - Friday, June 9, 2017 9:59 AMThanks - the problem lies within the sourceid will appear multiple times in both tables that are associated with the customerid , and need to devise a JOIN between the Orders and Ordermessages with a condition perhaps tied to the ON sourceids , or a subquery that ties in the customerid to respective sourceid, and the Group By will represent the total for the derived columns for each CustomeridThat didn't make sense to me. Could you provide some sample data that is representative of the problem and what you expect for output?
Agreed. An example would probably help a lot.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply