UNION with CASE statement
-
Newbi (6/30/2016)
I don't know how to display well enough here according to the guidelines of the forum. :crying:try this article for future reference:
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
however in this case it may not be rlevant to have the data, cos as already posted....what you are asking for can already be done in SSRS (I dont know SSRS though)
if it is absoultely necessary to do this in TSQL...then we do need the aforementioned set ups.
good luck
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
You can't have make custom page without SQL when you have more than one group. Cannot use row limit or whatsoever, as far as I have researched. Easily can limit/or page break if you don't need to have multiple groups. Same goes to adding the rows below the tablix after the data ended, If you don't have total rows, you can achieve easily too.
Yup it will be so great to complete all of those requirements in report level, I am hoping too. But cannot be done, in reality. Getting frustrated, I have been trying in SSRS several weeks ago.:crying:
-
Newbi (6/30/2016)
You can't have make custom page without SQL when you have more than one group. Cannot use row limit or whatsoever, as far as I have researched. Easily can limit/or page break if you don't need to have multiple groups. Same goes to adding the rows below the tablix after the data ended, If you don't have total rows, you can achieve easily too.Yup it will be so great to complete all of those requirements in report level, I am hoping too. But cannot be done, in reality. Getting frustrated, I have been trying in SSRS several weeks ago.:crying:
ok...I do feel your frustration......so lets have some data (that includes all possibilities and outcomes) and your expected results.
if you are lucky you may well get answers for both SSRS and TSQL.....
remember we cant see over your shoulder ....you must make the effort to provide us with something we can work with.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
Thank you.
I am aware that I should be providing the data in correct way, if I want to be helped.
I am trying using the links inside the page. Still trying. Why complicated? :crazy:
-
Newbi (6/30/2016)
Thank you.I am aware that I should be providing the data in correct way, if I want to be helped.
I am trying using the links inside the page. Still trying. Why complicated? :crazy:
when you post you will see at the lefthand side there is list of "IFCode Shortcuts" such as code=sql - /code (in square brackets) ..... if you look carefully these are in pairs
to use these to post TSQL code for example , highlight the relevant text that is TSQL and click on the code=sql - /code "pair"...you will see the code brackets surrounding your highlighted text.
Alternatively...click the code pair...they will automatically appear in your post, and then paste between them
Have a play to see how each pair effects the formatting by previewing your post (Click preview button at bottom of page)....you dont have to post it to see results
ps...always use the "url pair" for links to other webpages / urls
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
CREATE Table Data_Details(
HallNo INT,
ItemCode nvarchar(255),
ItemName nvarchar(255),
Location nvarchar(255),
LotNumber nvarchar(255),
Zone nvarchar(50),
Quantity INT,
Remark nvarchar(255),
GrossWeight decimal(6,3),
Zone_Abbr nvarchar(50)
);
INSERT into Data_Details VALUES (8520376,'7BRUSUP15X-1E','MonaLisa','#OU','1302150016','002/1','1','Auction',0.756,'S');
INSERT into Data_Details VALUES (8520376,'7BRUSUP1XX-1E','The Night Watch','#OU','1302150015','002/1','1','Auction',0.504,'S');
INSERT into Data_Details VALUES (8520376,'7BRUSUP1XX-1E','The Night Watch','#OU','1302150015','001/1','1','Auction',0.504,'S');
INSERT into Data_Details VALUES (8520376,'TIOABASE1X-5L','Girl with a Pearl Earring','2AE03','7066455','002/1','1','',7.750,'S');
INSERT into Data_Details VALUES (8520376,'VLOX5048XX-1L','Van Gogh ','1BM09','7065503','001/1','2','NIL',3.1,'S');
INSERT INTO Data_Details VALUES (8520376,'TIOABASE2X-5L','Portrait of a man in red chalk','3AF11','7066181','001/1','3','',23.25,'S');
INSERT INTO Data_Details VALUES (8520376,'7NA9HB9102-4L5','Whistler''s mother','1BR01','2001914','002/1','4','NIL',31,'S');
INSERT INTO Data_Details VALUES (8520376,'7NA9HARDXX-0L5','Self-portrait','2BQ13','2001572','002/1','4','',3.2,'S');
INSERT INTO Data_Details VALUES (8520376,'VL0X5048XX-5L','Self-portrait','1AG03','7066048','002/1','5','',38.75,'S');
INSERT INTO Data_Details VALUES (8520376,'VLOX5062XX-5L','American Gothic','1AH05','7066158','002/1','5','',38.75,'S');
INSERT INTO Data_Details VALUES (8520376,'VLOX5068XX-5L','American Gothic','1AH05','7066160','001/1','5','',38.75,'S');
😎 it is easy to use @
The following image is the result page I need to produce.
https://This image host is not supported, please use another/image/k6idu0jwn/"> https://This image host is not supported, please use another/image/k6idu0jwn/
-
The following image is the result page I need to produce.
https://This image host is not supported, please use another/image/k6idu0jwn/
sorry...cant see your expected results
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
-
Hope this time it is working.
-
So far I have managed to have the groupings correct (without SQL edit, done in report level only) , but I have no idea how to add blank lines to fill the short tablix, in some pages. Any work around or idea?
P.S: I have my table warped inside List. But, the page-break set in the list is still not working correctly.
-
Newbi (7/1/2016)
So far I have managed to have the groupings correct (without SQL edit, done in report level only) , but I have no idea how to add blank lines to fill the short tablix, in some pages. Any work around or idea?P.S: I have my table warped inside List. But, the page-break set in the list is still not working correctly.
Good, Nice to hear that for empty rows you can do the following:
Note: you can set the @PageSize manually if your page size is fixed. Currently is working the maximum of a single zone from the data.
USE tempdb
GO
IF Object_ID('Data_Details') is not null
drop table Data_Details
GO
CREATE Table Data_Details(
HallNo INT,
ItemCode nvarchar(255),
ItemName nvarchar(255),
Location nvarchar(255),
LotNumber nvarchar(255),
Zone nvarchar(50),
Quantity INT,
Remark nvarchar(255),
GrossWeight decimal(6,3),
Zone_Abbr nvarchar(50)
);
INSERT into Data_Details VALUES (8520376,'7BRUSUP15X-1E','MonaLisa','#OU','1302150016','002/1','1','Auction',0.756,'S');
INSERT into Data_Details VALUES (8520376,'7BRUSUP1XX-1E','The Night Watch','#OU','1302150015','002/1','1','Auction',0.504,'S');
INSERT into Data_Details VALUES (8520376,'7BRUSUP1XX-1E','The Night Watch','#OU','1302150015','001/1','1','Auction',0.504,'S');
INSERT into Data_Details VALUES (8520376,'TIOABASE1X-5L','Girl with a Pearl Earring','2AE03','7066455','002/1','1','',7.750,'S');
INSERT into Data_Details VALUES (8520376,'VLOX5048XX-1L','Van Gogh ','1BM09','7065503','001/1','2','NIL',3.1,'S');
INSERT INTO Data_Details VALUES (8520376,'TIOABASE2X-5L','Portrait of a man in red chalk','3AF11','7066181','001/1','3','',23.25,'S');
INSERT INTO Data_Details VALUES (8520376,'7NA9HB9102-4L5','Whistler''s mother','1BR01','2001914','002/1','4','NIL',31,'S');
INSERT INTO Data_Details VALUES (8520376,'7NA9HARDXX-0L5','Self-portrait','2BQ13','2001572','002/1','4','',3.2,'S');
INSERT INTO Data_Details VALUES (8520376,'VL0X5048XX-5L','Self-portrait','1AG03','7066048','002/1','5','',38.75,'S');
INSERT INTO Data_Details VALUES (8520376,'VLOX5062XX-5L','American Gothic','1AH05','7066158','002/1','5','',38.75,'S');
INSERT INTO Data_Details VALUES (8520376,'VLOX5068XX-5L','American Gothic','1AH05','7066160','001/1','5','',38.75,'S');
---- Added new row
INSERT into Data_Details VALUES (8520376,'7BRUSUP1XX-1E','The Night Watch','#OU1','2302150016','001/1','1','SampleRow#1',1.756,'S');
GO
DECLARE @PageSize int
;
----- Get the Page size from the data.
Select @PageSize = Max(rowncount)
from
(
Select Zone, count(1) as rowncount
from Data_Details
Group by Zone
) A
;
WITH CTE
AS
(
SELECT zone, ItemCode, ItemName, Location, LotNumber, Quantity, GrossWeight, Remark, 0 as IsEmptyRow
FROM Data_Details
union all
Select zone, null as ItemCode, null as ItemName, null as Location, null as LotNumber, null as Quantity, null as GrossWeight, null as Remark, 1 as IsEmptyRow
from
(
select zone, count(1) as rwcnt from Data_Details group by zone
)a
Cross apply
(
SELECT TOP (@PageSize - rwcnt) ROW_NUMBER() OVER(ORDER BY tally.number) as rownbr
from master.dbo.spt_values tally
where tally.type = 'P'
) b
)
, cData_Details
AS
(
select row_Number() over(partition by zone order by IsEmptyRow, ItemCode) AS ZoneSet , *
from cte
)
select * from cData_Details
-
Hi
I am getting error when trying to run your script.
Help pls.
-
Newbi (7/1/2016)
HiI am getting error when trying to run your script.
Help pls.
...
Why are you attempting to include the sample data setup in your report definition? It's only in TwinDevil's post so that his code has something to run against.
IMHO your best help at this point would be a strong coffee.
“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 -
😀 Thanks for highlighting.
Viewing 15 posts - 31 through 45 (of 48 total)
You must be logged in to reply to this topic. Login to reply