UNION with CASE statement
-
It will b better of with looking up UNION and UNION ALL first.
I really am not aware of importance of order in group by. Those are not intentionally order that way.
Anyway Thank you.
-
Newbi (6/28/2016)
It will b better of with looking up UNION and UNION ALL first.I really am not aware of importance of order in group by. Those are not intentionally order that way.
Anyway Thank you.
Itemcode and ItemName are switched in the unioned queries.
“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail ShawFor fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden -
Finally I can ,sort of , get it work. But not exactly what I want, yet.
When I run the query , I am getting the duplicate of subtotal by Products. If there are two items with same ItemCode, I want to have combined Subtotal, instead of getting separately. As an instance, at line 3 and 4, there should be one subtotal at line 5 with total GrossWt. But I am getting two results for this. I would rather want one subtotal at line5 with result 1.008 GrossWt. How can I make it to achieve what I want? Please show me the way. Thanks
-
Its always better to share the query in text instead of a image it make things a lot easier for the folks.
Following will achieve the result:
SELECT
Zone, ItemCode, [Products], GrossWt
FROM
(
SELECT
Zone, ItemCode,ItemName as [Products], GrossWeight AS GrossWt
FROM
PickLists_Details
UNION ALL
SELECT
NULL AS Zone, ItemCode, 'Subtotal' AS ItemName, SUM(GrossWeight) AS GrossWt
FROM
PickLists_Details
GROUP BY
ItemCode
) source
ORDER BY
ItemCode, (CASE WHEN [Products] = 'Subtotal' then 1 else 0 END), [Products]
Hope it helps.
-
Thank you very much. Now it is resulting exactly what I want.:-):-):-)
-
hi again,
How can I make it sort-by Zone, I mean I want to have subtotal of each item, but wanna order ascending by Zone.
As in,
001/1 xxx1 DEF-3 0.504 1
-null- -null- Subtotal 0.504 1
001/1 xxx4 YKK-20 3.100 2
-null- -null- Subtotal 3.100 2
...
002/1 xxx1 ABC-2 0.756 1
-null- -null- Subtotal 0.756 1
002/1 xxx2 DEF-3 0.504 1
-null- -null- Subtotal 0.504 1
etc...
(if there are two items under xxx1, it will have to be like:
001/1 xxx1 DEF-3 0.504 1
-null- -null- DEF-4 0.200 2
-null- -null- Subtotal 0.704 3
...)
How to write the query for that ? I still am having incorrect query result.
Thanks
-
(if there are two items under xxx1, it will have to be like:
001/1 xxx1 DEF-3 0.504 1
-null- -null- DEF-4 0.200 2
-null- -null- Subtotal 0.704 3
...)
What do mean by this? need to share some detail. and please share you Sample Data as well Your Desired output in a proper way as mentioned in the earlier.
Please read How to post data/code on a forum to get the best help[/url] before sharing any further details. Thanks
How to write the query for that ? I still am having incorrect query result.
Whats was wrong the solution provided earlier?? What have you done so far to be able to get this result.
-
Your result is giving me total of item and gross wt. I was applying inside my SSRS and cannot sort-by Zone, that's why I want to make a sort by Zone in SQL. I tried to play around with the query you have provided, but my query is going crazy. Actually what I did was I just try to group-by with Zone in those queries,inner and outer. I don't know what function to use inorder to achieve my requirement.
SELECT Zone, ItemCode, [Products], GrossWt,Quantity FROM
( SELECT Zone, ItemCode,ItemName as [Products], GrossWeight AS GrossWt,Quantity FROM PickLists_Details
UNION ALL
SELECT NULL AS Zone, ItemCode, 'Subtotal' AS ItemName, SUM(GrossWeight) AS GrossWt,SUM(Quantity) AS TotalQ
FROM PickLists_Details
GROUP BY ItemCode) source
ORDER BY ItemCode, (CASE WHEN [Products] = 'Subtotal' then 1 else 0 END), [Products]
I just added in one more column to your given script.
The result that I wanna get(to be able to apply well for SSRS) is as follow:
--001/1 xxx1 DEF-3 0.504 1
---null- -null- Subtotal 0.504 1
--001/1 xxx4 YKK-20 3.100 2
---null- -null- Subtotal 3.100 2
--...
--002/1 xxx1 ABC-2 0.756 1
---null- -null- Subtotal 0.756 1
--002/1 xxx2 DEF-3 0.504 1
---null- -null- Subtotal 0.504 1
--etc...
--(if there are two items under xxx1, it will have to be like:
--001/1 xxx1 DEF-3 0.504 1
---null- -null- DEF-4 0.200 2
---null- -null- Subtotal 0.704
I have attached the result image of your first query.
-
Newbi (6/30/2016)
Your result is giving me total of item and gross wt. I was applying inside my SSRS and cannot sort-by Zone.My question would be, if you are working on SSRS, why you even need a subtotal in a query ??? Its a fairly basic requirement in a report you can achieve it in the SSRS very easily so why doing it in query ????
-
Because SSRS designing alone cannot fulfill my requirements. Of course I will be more than happy if I can design there to meet my report requirements. That's why I am thinking the alternatives. I have been struggling with that problem many weeks already :rolleyes:
-
Newbi (6/30/2016)
Because SSRS designing alone cannot fulfill my requirements.Shed some light on it.
-
twin.devil (6/30/2016)
Newbi (6/30/2016)
Because SSRS designing alone cannot fulfill my requirements.Shed some light on it.
and please post some sample data so that we can all work on the same data set (see my earlier post on this thread for an example)
thanks
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
I need to have [1] 2 groupings (group by zone and itemcode), [2] Totals( subtotal by each itemCode, and Grand total at the end of the tablix), [3]custom page break for every page, (the purpose is to maintain the same length of the tablix),[4] add blank row(s) if the data-driven tablix is shorter compared to the previous pages'.
🙁
-
Newbi (6/30/2016)
I need to have [1] 2 groupings (group by zone and itemcode), [2] Totals( subtotal by each itemCode, and Grand total at the end of the tablix), [3]custom page break for every page, (the purpose is to maintain the same length of the tablix),[4] add blank row(s) if the data-driven tablix is shorter compared to the previous pages'.🙁
Every single point you have mentioned, none of them related to t-sql, All this functionality is done Should be done at report level.
Irrespective of which reporting tool (for your case SSRS).
Hope it helps.
-
I don't know how to display well enough here according to the guidelines of the forum. :crying:
Viewing 15 posts - 16 through 30 (of 48 total)
You must be logged in to reply to this topic. Login to reply