SQL Server 2012 :: Alphanumeric Range Grouping?

Feb 25, 2015

I am having some difficulty getting a query to output an alpha numeric range grouping.

I have this data set:

Despatch_id Sample_ID

MIR00831 MCR0005752
MIR00831 MCR0005753
MIR00831 MCR0005754
MIR00831 MCR0005755
MIR00831 MCR0005756
MIR00831 MCR0005757

[code]....

Output:

DESPATCH_ID SAMPLE_ID_FROM SAMPLE_ID_TO
MIR00831 MCR0005752 MCR0005762
MIR00831 MCR0005803 MCR0005806
MIR00831 MCR0005808 MCR0005813

They need to be grouped by range specific to the alpha numeric part, which can vary within the same despatch. I was thinking of using a row over partition after splitting the numeric and alpha part and to check if they are consecutive and build the range. But I am thinking that this approach is an overkill and there may be a better way to achieve this in SQL 2012.

I have included the create table scripts and example data below:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SAMPLE_TABLE](
[DESPATCH_ID] [nvarchar](30) NOT NULL,
[SAMPLE_ID] [nvarchar](30) NOT NULL

[code]....

View 7 Replies


ADVERTISEMENT

SQL Server 2012 :: More Than 1 Alphanumeric Chars In A String

Mar 12, 2014

I have an employee table

Surname GivenName
ABC x.yz
A.BC X.*YZ
A*.BC xyz

The query needs to get me the surname and givenname which have more than 1 non alphanumeric characters. In this case the output will be

a.bc x.*yz
a*.BC xyz

I know how to find non alphanumeric chars in a string but not sure if I can how to find the strings with minimum 2 non alphanumeric chars?

View 9 Replies View Related

SQL Server 2012 :: Auto Incrementing Alphanumeric Field In Table

Oct 1, 2014

I need to auto increment an alphanumeric field in a table.

The process is as follows:

1. First position is static letter C for contact or static letter A for account - field contact_id
2. The following 6 positions are numeric - example of the string would be C004658
3. When new contact is entered, it should look up the highest numeric value (in this case 004658) and increment it by one - resulting in C004659

View 9 Replies View Related

SQL Server 2012 :: Sorting By Alphanumeric Table Column Of NVARCHAR Datatype

Sep 24, 2015

I am trying to sort my sql resultset by an alphanumeric column of a table which is of NVARCHAR datatype. The sample data is given below:

CREATE TABLE #Activities(activityName NVARCHAR(100))

INSERT INTO #Activities VALUES('Field phase S14-04932-01')
INSERT INTO #Activities VALUES('Phase reporting')
INSERT INTO #Activities VALUES('Phase running')
INSERT INTO #Activities VALUES('RD1')

[Code] ....

The output of the query is like this:

A1
A2
A3
A4
E1 0DAA1
E10
E2 0DAA2

[Code] .....

The output what I require is this:

A1
A2
A3
A4
E1 0DAA1
E2 0DAA2

[Code] ....

View 9 Replies View Related

Grouping By Date Range

Mar 14, 2008

My company is unusual in that our accounting periods are not actual months. We have what we call "Red Fridays." These are spaced 3-5 weeks apart. So, my company doesn't care what happened in the month of April, but they care what happened between April 4 and May 2 because these are the Red Fridays.

So, I have created a database with a table called "RedFridays" with the dates for this year. I want to combine this with various tables in our ERP database. I use a Left Outer Join between the Red Friday Dates and the corresponding date in the ERP database.

I need to create a custom grouping formula which accomplishes the following:

1. Subtract a certain number of months from today's date to determine which Red Friday would be the correct starting date.
2. Group records by date between that Red Friday and the second one. This would be listed as something like "Month 1".
3. Continue grouping in this way to the present date and that Red Friday range.

Can anyone point me in the right direction? Any help would be greatly appreciated.

View 5 Replies View Related

Grouping Column Currency By Range

Apr 11, 2007

HI all -



I need to group data in a sql report by currency values. I have a report that shows part numbers and 4 columns making up manufacturing costs and then a 5th column showing total costs. I would like to add a 6th column that groups the total costs by ranges. The example would be, group all costs > 20000, group all costs 1000 > x > 20000 , and finally all costs < 1000.



How can I accomplish this?

View 3 Replies View Related

Transact SQL :: Grouping Records With A Date Range Into Islands

Nov 18, 2015

I tried to ask a similar question yesterday and got shot down, so I'll try again in a different way.  I have been looking online at the gaps and islands approach, and it seems to always be referencing a singular field, so i can't find anything which is clear to get my head around it.In the context of a hotel (people checking in and out) I would like to identify how long someone has been staying at the hotel (The Island?) regardless if they checked out and back in the following day.

Data example:
DECLARE @LengthOfStay TABLE
(
PersonVARCHAR(8) NOT NULL,
CheckInDATE NOT NULL,
CheckOutDATE NULL

[code]...

View 7 Replies View Related

SQL 2012 :: Create Random Alphanumeric Characters For Primary Key Values

Feb 11, 2015

For a new project. I need to create random alphanumeric characters as primary key values when inserting a record.

eg: cmSbXsFE3l8

It can start from 4 digit characters and can grow to 6, 7 as required

The database will involve more than 10000 concurrent users.

I don't want to use guid or auto increment integer or sequence.

View 9 Replies View Related

SQL Server 2012 :: Grouping Columns In Table

Sep 15, 2015

I have table like below.

filename col1 col2 col3
ABD Y NULL Y
XYZ Y Y Y
CDZ Y Y Y

I Need a output like this

filename col1 col2 col3 Group
ABD Y NULL Y Group1
XYZ Y Y Y Group2
CDZ Y Y Y Group2

I wanted to group the col1 , col2, col3 and group it as same group.

View 3 Replies View Related

SQL Server 2012 :: Trying To Add Third Grouping Set To Return Average Aggregate

Feb 16, 2014

i'm building a query to return metrics that will drive 3 seperate pivot tables showing

1. Total count of LineItems per D_Type each month
2. Total count of LineItems per Status each month
3. Avg count of LineItems per Invoice each month

I am able to get the first two, but having hard time with the 3rd.

Here's some representative ddl
create table Remediation
(Invoice nvarchar(10), D_Type nvarchar(20), Status nvarchar(20), RemediationDate datetime);

insert into Remediation values
--this will create data for Jan, 2014
('501', 'Recycle', 'Pass', getdate()-30),
('501', 'Reuse', 'Pass', getdate()-30),
('501', 'Remarket', 'Fail', getdate()-30),

[code]....

how to add the average metric to this query?

View 9 Replies View Related

SQL Server 2012 :: Dynamic Pivot Table Not Grouping

Mar 26, 2014

I have a query

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ',','')
+ QUOTENAME(name)

[Code] ....

and so on.

I've tried putting my group by everywhere but it's not working. I'm missing something but what?

View 9 Replies View Related

SQL Server 2012 :: Modifying Date Range

Jun 27, 2014

I would like changing the date range to be -1 year from today until today

This is the current WHERE, AND

WHERE [entry].[Posting Date] >= DATEADD(YEAR, YEAR(GETDATE()) - 1900, 0)
AND [entry].[Posting Date] < DATEADD(YEAR, YEAR(GETDATE()) - 1899, 0)

View 2 Replies View Related

SQL Server 2012 :: Date Range Calculation

Feb 3, 2015

I have some location assignment data that I need to convert. I need to know how long each account spent in a certain location for each month of it's overall startdate/enddate period.

E.g.
Account 1 stayed in USA for 31 days in January, and 15 days in February.
Account 1 stayed in UK for 13 days in February and 26 days in March.
Etc.

create table #temp(account int, loc varchar(10), startdate datetime, enddate datetime)
insert into #temp select 1,'USA','2014-01-01','2014-02-15'
insert into #temp select 1,'UK','2014-02-16','2014-03-26'
insert into #temp select 1,'AU','2014-03-27','2014-06-07'
insert into #temp select 2,'UK','2014-08-15','2014-09-01'
insert into #temp select 2,'AU','2014-09-02','2014-10-17'
select * from #temp
drop table #temp

View 6 Replies View Related

SQL Server 2012 :: Grouping By Percentage Of Time Worked By Person

Jul 2, 2014

I have a table of People and their ID, the starting month (a fixed number of months, say 10 for this), the ending month, and the percent of work time (0-1 being 0-100%). If they have a % work of 0, I do not want to see anything. But if the % changes, from say .5 to .75, I would need the first and last month they were at .5, and the first and last month they were at .75

The Table:

/****** Object: Table [dbo].[TestProject] Script Date: 02.07.2014 10:15:08 ******/
IF OBJECT_ID('TempDB..#TestProject2','U') IS NOT NULL
DROP TABLE [dbo].[#TestProject2]
GO
CREATE TABLE [dbo].[#TestProject2](
ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

[Code] ....

The data:

--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #TestProject2 ON
INSERT INTO #TestProject2
("ID","PersonID", "PercentLoad","MonthID")
SELECT 1,123456,0,1 UNION ALL

[Code] ....

EXPECTED RESULT:

Person ID StartMonth EndMonth LOADPCT
123456 3 4 .5
123456 5 6 1
654321 1 2 1
654321 4 4 .5
654321 5 6 .75
654321 7 9 .5

View 5 Replies View Related

SQL Server 2012 :: Query On Grouping Data On Weekly Basis

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

SQL Server 2012 :: Range Group Summary Of Particular Column

Jun 3, 2014

I have column which stores People count based on department, Now I want to keep them in the batch of 1000, If the running summary of (No of people) from departments reached 1000 then it should start sum(no of people) from 0 to 1000

Is there any running summary kind of function which can start sum record with in range of 0-1000

For Ex. My Data stored like this
Dept People Count
CSE 200
IT 250
EEE 312
ECE 214
MEC 337

Batch Grouping
Dept People Count BatchSum
CSE 200 200
IT 250 450
EEE 312 762
ECE 214 976
MEC 337 337 (Note here since its crossing 1000, its resetting and starting summary)

I implemented this with While Loop & if condition, But its very slow, is there any other way to achieve it in better way.

View 8 Replies View Related

SQL Server 2012 :: Assigning Numbers For A Range During INSERT

Jun 25, 2014

Given a Table1 with two columns 'Name' with some N rows of data and another Table2 with one column 'SeqNo' with N rows, each of which contains a unique integer which can be ordered monotonically, I want to do an INSERT into some Table3 with two columns 'Name' and 'SeqNo' such that each INSERT'd row gets one of the unique integers.

E.g. -

Table1 contains 'Fred','Tom','Mary','Larry'
Table2 contains 6000978,6000979,6000980,6000981

INSERT INTO Table3
SELECT Table1.Name
Table2.SeqNo
FROM Table1

And I want to get Table3

'Fred',6000978
'Tom'.6000979
'Mary',6000980
'Larry',6000981

How can I reference Table2 so that Table2.SeqNo will 'line up' properly? Note that the ordering of the SeqNo values isn't mandatory as long as each SeqNo is assigned to one and only one row.

On edit: Table2 isn't required, it's just the way I started thinking about it. It would be nicer to just have two integer vars, @StartSeqNo = 6000978 and @EndSeqNo = 6000981 for he example above. Either way is fine.

View 3 Replies View Related

SQL Server 2012 :: Query Result For Sequential Range

Aug 9, 2014

I wanted to know the best way to achieve the following results. I have a table that I need output sequential range of vouchers in a table. For instance I have the following data in a column called vouchers. The output will consist of a years worth of vouchers, so voucher numbers may contain gaps and so the need to have a sequential range that has a From and To output. The query needs to know the min and max within that numerical range and then output the next min and max range until it gets to the end.

The data looks like:
ABCD-001869202
ABCD-001869203
ABCD-001869204
ABCD-001869205
ABCD-001869209
ABCD-0018692010
ABCD-0018692011
ABCD-001869309
ABCD-001869310
ABCD-001869311
ABCD-001869312
ABCD-001869313
ABCD-001869314

Desired out put:

From To
ABCD-001869202 ABCD-001869205
ABCD-001869209 ABCD-0018692011
ABCD-001869309 ABCD-001869314

I have tried the following, but it does not quite do what I need it to do, so not sure if I am taking the right approach:

SELECT voucher vouchers,right(voucher, charindex('-', voucher) + 3) voucher
INTO #tempVoucher
FROM LEDGERJOURNALTRANS
where TRANSDATE between '10/1/2013' and '7/31/2014' and VOUCHER like 'APIN%'

[Code] ...

View 6 Replies View Related

SQL Server 2012 :: Date Range Checking In Table

Mar 16, 2015

I have a table with EmployeeID, StartDate, and EndDate with a PK of EmployeeID, StartDate. How can I check to see that there's no overlap for StartDate and EndDate for a given employee? That is, on any given day there must only be 1 row for an employee where Getdate() is Between StartDate and EndDate. For an active employee their EndDate is set to 06/06/2079.

I've tried it using Row_Number() with Over() but am returning too many rows indicating overlap when none exists.

View 7 Replies View Related

SQL Server 2012 :: Query That Generates Values By Date Range

Dec 12, 2014

I have a table that stores Terminal ID, Product Name, Cost and Effective Date. I need to generate query that will produce record set with start effective date and end date based on terminal and product. Table has over million records. In example below you could see table structure/data and desired outcome.

SELECT fmc_terminal, fmc_date = CAST(d. fmc_date AS DATETIME)
,d.fmc_prodlnk, d. fmc_cost
INTO #TestTable
FROM (
SELECT 1, '2014-12-03 00:04:00.000','A', 2.25 UNION ALL

[Code] ....

Expected outcome
fmc_terminalfmc_prodlnkfmc_cost StartDateEndDate
1A2.25 NULL2014-12-03 00:04:00.000
1A2.26 2014-12-03 00:04:00.0002014-12-03 11:33:00.000

And so on….

View 4 Replies View Related

SQL Server 2012 :: Retrieving Minimum Value Over Rolling Time Range?

Apr 28, 2015

I need to provide a minimum value over a 12 hour time range of data. I'm struggling with performance issues due to the amount of data. Currently I log about 100 devices reporting once per minute into a table. Also about once per minute I need to pull the minimum value reported for each device in the last 12 hours. Currently I'm maintaining a separate table with entries for just the last 12 hours and just performing a Select Min(Temp) Where DeviceID=x, but it already holds about 700,000 records at any given time. The number of devices will increase substantially and this will no longer be viable.

Sample Table
ID DeviceID Temp InsertDate
1 10 55 04-28-2015 8:00 AM
2 65 74 04-28-2015 8:00 AM
3 44 23 04-28-2015 8:00 AM
4 10 87 04-28-2015 8:01 AM
5 65 65 04-28-2015 8:01 AM

View 3 Replies View Related

SQL Server 2012 :: Day Wise And Date Range Calculation With Looping Or Dynamic Data?

May 14, 2015

I am using Sql Server 2012.

This is how I calculate the ratio of failures in an order:

31 Days Table 1 query
sum(CASE
WHEN (datediff(dd,serDATE,'2015-01-21')) >= 31 THEN 31
WHEN (datediff(dd,serDATE,'2015-01-21')) < 0 THEN 0
ELSE (datediff(dd,serDATE,'2015-01-21'))END) as 31days1 .

How do i loop and pass dates dynamically in the Datediff?

31 Failures Table 2 query
SUM(Case when sometable.FAILUREDATE BETWEEN dateadd(DAY,-31,CONVERT(DATETIME, '2015-01-21 23:59:00.0', 102))
AND CONVERT(DATETIME, '2015-01-21 23:59:00.0', 102)Then 1 Else 0 END) As Failures31,31 Day Cal(Formula) combining both Table 1 and Table 2
((365*(Convert(decimal (8,1),T2.Failures31)/T1.31day))) [31dayCal]This works fine when done for a specific order.

I want a similar kind of calculation done for day wise and month wise.

2. what approach should I be using to achieve day wise and month wise calculation?

I do also have a table called Calender with the list of dates that i can use.

View 3 Replies View Related

SQL Server 2012 :: Data Grouping On 2 Levels But Only Returning Conditional Data

May 7, 2014

I think I am definitely thrashing and am not getting anywhere on something I think should be pretty simple to accomplish: I need to pull the total amounts for compartments with different products which are under the same manifest and the same document number conditionally based on if the document types are "Starting" or "Ending" but the values come from the "Adjust" records.

So here is the DDL, sample data, and the ideal return rows

CREATE TABLE #InvLogData
(
Id BIGINT, --is actually an identity column
Manifest_Id BIGINT,
Doc_Num BIGINT,
Doc_Type CHAR(1), -- S = Starting, E = Ending, A = Adjust
Compart_Id TINYINT,

[Code] ....

I have tried a combination of the below statements but I keep coming back to not being able to actually grab the correct rows.

SELECT DISTINCT(column X)
FROM #InvLogData
GROUP BY X
HAVING COUNT(DISTINCT X) > 1

One further minor problem: I need to make this a set-based solution. This table grows by a couple hundred thousand rows a week, a co-worker suggested using a <shudder/> cursor to do the work but it would never be performant.

View 9 Replies View Related

SQL 2012 :: Grouping And Sorting Challenge

Aug 15, 2014

I have an interesting challenge. Imagine I own a pizza restaurant and I have delivery drivers (4). I also have a fixed number of delivery Routes for my restaurant and these routes have varying number of households on the them. I want to sort and assign the routes as evenly as I can to the drivers.

My route data my look like this:

[RouteID]_[RouteName]_[HouseHolds]___[DriverID]
-----------------------------------------------------
1________Route01_____700__________0
2________Route02_____210__________0
3________Route03_____111__________0
4________Route04_____452__________0
5________Route05_____236__________0
6________Route06_____111__________0
7________Route07_____300__________0
8________Route08_____421__________0
9________Route09_____1200_________0
10_______Route10______525_________0

Drivers:
-------------------------
1 - Bob
2 - John
3 - Ricky Bobby
4 - Batman

What I'm TRYING to is write a Stored Proc to loop though the records (routes) and assign them to the drivers as evenly as possible based on households (staying with whole integers).

I've tried a couple of different concepts but keep hitting 'writers block'.

View 9 Replies View Related

SQL 2012 :: Index Was Out Of Range

Oct 1, 2014

observed below error in sqlserver2012.index was out of range. Must be non-negative and less than the size of the collection.

View 2 Replies View Related

SQL Server 2008 :: Generate 8 Char Alphanumeric Unique Sequence

Apr 20, 2015

GENERATE 8 CHARACTER ALPHANUMERIC SEQUENCES

Requirements
• ALPHANUMERIC FORMAT – > AA00AA00………..ZZ99ZZ99
Last 8 bytes will alternate between 2 byte alpha/2 byte numeric
• Generate from Alphabets – A through Z Numbers -0 to 9
• Generate Unique Sequence (No Duplicates).
• Must Eliminate letters I and O

Output Expected
• AA00AA00………..ZZ99ZZ99
• Using 24 alphabets & 10 digits ,
24*24*10*10*24*24 = 3 317 760 000 records

Below is my Sql Function -

CREATE function [dbo].[SequenceComplexNEW]
(
@Id BIGINT
)
Returns char(8)

[Code] .....

View 9 Replies View Related

SQL 2012 :: Report Builder 3 Grouping (1st Row Needs Full Page Width)

Mar 8, 2014

I have a dataset for report builder 3 that has 8 fields. The data for the report needs to be grouped by the first field. The 2nd field is a string that describes the group field and this too needs to be on the same row as the group field. This 2nd field need nearly the full page width. The remaining 6 fields need to end up rendering below the first row. I can't figure out how to get report builder to put the first 2 fields on the first row and use the full page width.

View 0 Replies View Related

SQL 2012 :: Table Partitioning - Switch Out / Merge Range

Mar 6, 2014

If the partitioning MERGE command attempts to drop historic data at the wrong boundary point then data movement between file groups may be necessary before or during the next index rebuild. The script below creates 2 test tables, one using a range right function and the other using range left. The partitioning key is a number between 0 - 59, an empty partition is maintained at the start and end of ranges, 4 partitions contain data in the ranges between 0-14, 15-29, 30-44, 45-59. Data in the lowest range (0 - 14) is switched out and a merge command is run, edit the script to try the different merge boundaries, edit the variables at the start to suit runtime environment 'Data Drive' & 'Log Drive' paths.Variables are redeclared but commented out at the start of code blocks to allow stepping through if desired.

--=================================================================================
-- PartitionLabSetup_20140330.sql - TAKES ABOUT 1 MINUTE TO EXECUTE
-- Creates a test database (workspace)
-- Adds file groups and files
-- Creates partition functions and schema's
-- Creates and populates 2 partitioned tables (PartitionedRight & PartitionedLeft)

[Code] ....

The T-SQL code below illustrates one of the problems caused by MERGE at the wrong boundary point. File Group 3 of the Range Right table is empty according to the data space views, it cannot be dropped though. File Group 2 contains data according to the views but you are allowed to drop it's file.

USE workspace;
DROP TABLE dbo.PartitionedRightOut;

USE master;
ALTER DATABASE workspace
REMOVE FILE PartitionedRight_f3 ;
--Msg 5042, Level 16, State 1, Line 2
--The file 'PartitionedRight_f3 ' cannot be removed because it is not empty.

ALTER DATABASE workspace
REMOVE FILE PartitionedRight_f2 ;

-- Works surprisingly although contains data according to system views.

If the wrong boundary point is used then the system 'Data Space' views show where the data should be (FG2), not where it actually still is (FG3). You can't tell if data movement between file groups is pending and the file group files are not protected from deletion by the OS.

I'm not sure this is worth raising a connect item for but it would be useful knowing where data physically resided after a MERGE RANGE and before an INDEX REBUILD, the data space views reflect the logical rather than the physical location if a data movement is pending.

View 0 Replies View Related

SQL 2012 :: Bulk Insert Records For Date Range And Day

Aug 21, 2014

I have two tables, customers and appointments.

I want to bulk insert records for a date range and a day of the week.

For example, John Smith has an appointment on every monday for the next three months. How do I accomplish this?

View 1 Replies View Related

SQL 2012 :: Sizing Bar Of Horizontal Range Bar Chart In SSRS?

Sep 8, 2014

I need to manage the height of an horizontal range bar chart inside a SSRS 2012 report.

In my report, I've a filter to read a particular data set. So, I could select five data rows or ten data rows or twenty data rows and so on. I'd like to maintain fixed the height of the horizontal bars while the number of data rows changes.

View 1 Replies View Related

SQL Server 2008 :: Query To Select Date Range From Two Tables With Same Date Range

Apr 6, 2015

I have 2 tables, one is table A which stores Resources Assign to work for a certain period. The structure is as below

Name StartDate EndDate
Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000

The table B stores the item process time. The structure is as below

Item ProcessStartDate ProcessEndDate
V 2015-04-01 09:30:10.000 2015-04-01 09:34:45.000
Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000
W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000
A 2015-04-01 16:40:10.000 2015-04-01 16:42:45.000
B 2015-04-01 16:43:01.000 2015-04-01 16:45:11.000
C 2015-04-01 16:47:00.000 2015-04-01 16:49:25.000

I need to select the item which process in 2015-04-01 16:40:00 and 2015-04-01 17:30:00. Beside that I need to know how many resource is assigned to process the item in that period of time. I only has the start date is 2015-04-01 16:40:00 and end date is 2015-04-01 17:30:00. How I can select the data from both tables. There is no need for JOIN, just seperate selections.

Another item process time is in 2015-04-01 10:00:00 and 2015-04-04 11:50:59.

The result expected is

Table A

Name StartDate EndDate
Alan 2015-04-01 16:30:00.000 2015-04-02 00:30:00.000

Table B

Item ProcessStartDate ProcessEndDate
A 2015-04-01 16:30:10.000 2015-04-01 16:32:45.000
B 2015-04-01 16:33:01.000 2015-04-01 16:35:11.000
C 2015-04-01 16:37:00.000 2015-04-02 16:39:25.000

Scenario 2 expected result

Table A

Name StartDate EndDate
Tan 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000
Max 2015-04-01 08:30:00.000 2015-04-01 16:30:00.000

Table B

Item ProcessStartDate ProcessEndDate
Q 2015-04-01 10:39:01.000 2015-04-01 10:41:11.000
W 2015-04-01 11:44:00.000 2015-04-01 11:46:25.000

View 8 Replies View Related

Reporting Services :: SSRS 2012 - Page Break With Column Grouping

Nov 16, 2015

We are facing problem in doing page break with column grouping. Our column group contains years e.g 2011, 2013 . We want to show a complete page for a year. 

Suppose 2011 has 10 records(horizontal) and 2013 has 12 records(horizontal) in column. The output should be 10 records of 2011 in first page, 12 records of 2013 in second page.

We cannot change the report layout to make column to row and vice versa.

View 3 Replies View Related

Query Or Grouping Problem (some Kind Of Parallel Grouping?)

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







Copyrights 2005-15 www.BigResource.com, All rights reserved