Dynamic # Of Records In An Artificial Grouping
May 12, 2008
I have a report that requires SubTotals for each page.
To do this successfully, I had to introduce the following grouping:
Code Snippet=Int((RowNumber(Nothing)-1)/5
This groups every 5 records.
Unfortunately, the length of data in my records is not uniform. For example, certain records have long(over 4 lines) comment field while others are short(1 line).
This means that even though I have an artificial grouping, the group may still be split up over two or more pages.
Is there a way to Group records PER PAGE dynamically?
View 5 Replies
ADVERTISEMENT
Jul 5, 2006
I'm currently running the following statement that is used in a CrystalReport. Basically, a record is returned when the T_PAYMENT.amounthas a record in the database based on the value of the T_MULTILIST.codefield. Currently, if there is no record returned, there is no listingin the report for the given T_MULTILIST.code.The user now wants a record to be displayed on the report when there isno record in the database - she wants it to display a value of '$0'for the given T_MULTILIST.code record. I tried to explain the fact thatis not possible the way things stand at the moment. Basically I needsome typeof case statement that says 'if there is no record returned, create asingle record and set T_PAYMENT.amount = 0' AFTER each query has beenexecuted.Anyone have any idea how to accomplish this?SELECT DISTINCT'English Language Arts, Grade 1' as Rec_Type, 'English Language Arts(Consumable)' as Super_Type, '' as Other_Type, 'Continuing Contracts'as Proc_Type,T_MULTILIST_GRADE.grade, T_MULTILIST.description, T_MULTILIST.code,T_PAYMENT.amountFROM (T_MULTILIST T_MULTILIST INNER JOIN (T_PAYMENT T_PAYMENTINNER JOIN T_SHIPPING_DETAILT_SHIPPING_DETAIL ONT_PAYMENT.transaction_id=T_SHIPPING_DETAIL.transac tion_id)ON T_MULTILIST.code=T_SHIPPING_DETAIL.multilist_code) INNER JOINT_MULTILIST_GRADE T_MULTILIST_GRADE ONT_MULTILIST.code=T_MULTILIST_GRADE.multilist_code, T_ORDER, T_REQUISITION, T_REQUISITION_DETAILWHERET_ORDER.id = T_SHIPPING_DETAIL.order_id ANDT_REQUISITION.id = T_ORDER.requisition_id ANDT_REQUISITION_DETAIL.requisition_id = T_REQUISITION.id ANDT_REQUISITION_DETAIL.latest_record_flag = 1 ANDT_REQUISITION.latest_record_flag = 1 ANDT_ORDER.latest_record_flag = 1AND (T_MULTILIST.code='1040')AND (T_MULTILIST.expiration_year >= '2006' )AND (T_REQUISITION.requested_shipment_date >= '2006' + '0601'AND T_REQUISITION.requested_shipment_date < dateadd(YY, 1,'2006' + '0601' ) )UNIONSELECT DISTINCT'English Language Arts, Kindergarten' as Rec_Type, 'EnglishLanguage Arts (Consumable)' as Super_Type,'' as Other_Type, 'Continuing Contracts' as Proc_Type,T_MULTILIST_GRADE.grade, T_MULTILIST.description,T_MULTILIST.code, T_PAYMENT.amountFROM (T_MULTILIST T_MULTILIST INNER JOIN (T_PAYMENT T_PAYMENTINNER JOIN T_SHIPPING_DETAIL T_SHIPPING_DETAILON T_PAYMENT.transaction_id=T_SHIPPING_DETAIL.transac tion_id)ON T_MULTILIST.code=T_SHIPPING_DETAIL.multilist_code) INNER JOINT_MULTILIST_GRADE T_MULTILIST_GRADE ONT_MULTILIST.code=T_MULTILIST_GRADE.multilist_code, T_ORDER, T_REQUISITION, T_REQUISITION_DETAILWHERET_ORDER.id = T_SHIPPING_DETAIL.order_id ANDT_REQUISITION.id = T_ORDER.requisition_id ANDT_REQUISITION_DETAIL.requisition_id = T_REQUISITION.id ANDT_REQUISITION_DETAIL.latest_record_flag = 1 ANDT_REQUISITION.latest_record_flag = 1 ANDT_ORDER.latest_record_flag = 1AND (T_MULTILIST.code='0040')and (T_MULTILIST.expiration_year >= '2006' )AND (T_REQUISITION.requested_shipment_date >= '2006' + '0601'AND T_REQUISITION.requested_shipment_date < dateadd(YY, 1,'2006' + '0601' ) )Up to 40 more UNION statements follow the above 2.
View 3 Replies
View Related
Feb 13, 2008
Hi All,
I have a report that can be used by two different groups of people. If a local manager runs the report the facility is set by the web application and the parameter is hidden from the user. If regional manager runs the report they can multi-select from the locations in there region. In this case I want to have a group for each location selected. I was hoping that I could just add an expression to the group to hide it base on the location parameter, however if I hide the group everything in the group is hidden as well. Is there a way to dynamically add or remove or show and hide this group and keep all the detail data?
After looking at this a bit more I will also need to change the toggle item if the group is present or not. Tried this as an expression and it did not work.
View 3 Replies
View Related
May 14, 2007
Hi,
I have a basic report that has some transaction info, eg
Name Type
AAA Buy
AAA Buy
AAA Sell
BBB Buy
BBB Buy
BBB Buy
BBB Sell
Etc
I want to group all the BBB's together, but I don't want to group ANY others together... Ie, I want to group the transactions together if the name is BBB, else I don't want to group at all.
Is this possible?
I hope I have given enough info.
Cheers
Rob
View 3 Replies
View Related
Feb 26, 2007
I've got this working -- I allow users to have 2 groups, and choose what they want to group by. I'd like to add one extra bit of functionality -- for the inner grouping, I would like my users to have the option "None" -- i.e. don't have an inner group.
I've tried setting the group expression of the second (inner) group to "" when the user chooses the "None" option but the report errors out. Any suggestions as to how to dynamically get rid of the inner group?
Thx
Helen
View 3 Replies
View Related
Jan 23, 2007
Hi, I am currently trying to create a report the dynamicaly groups from parameters. The grouping part works fine but I need to show the parameter label name or the field referenced by a parameter.
=iif(Parameters!Param1.Value="P",Parameters!Param2.Label,Fields(Parameters!Param1.Value).Value)
This is the expression I have written to do this. The problem seems to be is with the true part of the iif statement. The false displays fine in the group header. The Parameters!Param2.Label also displays if used on its own.
This is the error that is fires back when I run the report.
The Value expression for the textbox €˜textbox4€™ contains an error: The expression referenced a non-existing field in the fields collection.
I am quite new to SSRS and I am using VS2005 pro.
Thanks
D
View 1 Replies
View Related
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
May 14, 2008
Hi Guys,
I want to group the records on the time difference
declare @tbl as table(id int,intid int,val int,dt datetime)
insert into @tbl
select 1,1,10,'03/31/2006 15:05:22' union all
select 2,1,12,'03/31/2006 15:10:22' union all
select 3,1,15,'03/31/2006 15:15:22' union all
select 4,1,12,'03/31/2006 15:25:22' union all
select 5,1,8,'03/31/2006 15:30:22' union all
select 6,1,6,'03/31/2006 15:35:22' union all
select 7,1,4,'03/31/2006 15:40:22' union all
select 8,1,3,'03/31/2006 15:45:22' union all
select 9,1,10,'03/31/2006 15:50:22'
declare @tbl1 as table(intid int,Tm int,val int)
insert into @tbl1
select 1,5,10
I want a output such that when the val in @tbl goes below the val in @tbl1 for the Tm mentioned in @tbl1
then the time difference should be shown.For example record 1 it starts with 10 the records remain more than 10 till
record number 5.From 5 the records remains lower than 10 till record number 9.So I need to show the the time
difference from record number 5 till 9.
But there is a catch.In @tbl1 there is column named Tm.The time difference sould be calculated only if the diff
more than Tm value in @tbl1.
For example if the value of Tm is changed to say 25 then the there is no need to show the time difference since the
time difference value from record 5 to record 9 is less than 25.
Hope I am clear.
Please help me out.
Regards
View 3 Replies
View Related
Jul 20, 2005
All,Given multiple records with identical values in all fields except asingle varchar field, is there an efficient query that will group therecords into a single record and concatenate the aforementionedvarchar field into a single field with each of the source records'values separated by commas?Example:Record 1 'Doug' , '1'Record 2 'Doug' , '2'Output record 'Doug' , '1,2'Thanks in advance,Doug
View 2 Replies
View Related
Jun 16, 2007
In the "tblEmailGroupLink" table...
I need to find all records with the "UnSubscribed" field having a "True" value. All these records will have a corresponding "Emailid" field.
In the "tblEmailAddress" table...
The same "Emailid" field has a corresponding "EmailAddress" field.
What needed is all the email addresses found in the "EmailAdddress" field of these records.
I'm very new at this so I hope I explained this right. I'd really appreciate any help I can get.
Thanks,
Bill
View 2 Replies
View Related
Jul 27, 2015
I have results from a query that have anywhere from 1-4 results. I have a subid of 1-4 which is grouped by a certain criteria. so for example...
id subid text
1 processing A records
2 1000 records processing
3 importing A records
4 1000 records processed
1 processing B records
2 500 records processing
3 importing B records
4 1000 records processed
Here is what my desired output is giving each group of subids 1-4 an id to be grouped together.
id subid text
1 1 processing A records
1 2 1000 records processing
1 3 importing A records
1 4 1000 records processed
2 1 processing B records
2 2 500 records processing
2 3 importing B records
2 4 1000 records processed
View 1 Replies
View Related
Jul 20, 2005
I need to group records and assign a setid to the group. I have atable with data that looks like thisColA ColB94015 0106594016 0106594015 0108594015 0108633383 0091232601 00912I need to create a resultset using just sql to look like thisColA ColB GRP94015 01065 194016 01065 194015 01085 194015 01086 133383 00912 232601 00912 2The tricky part is resolving the many to many issue. A value in ColAcan belong to multiple values in ColB and a value in ColB can havemultiple values in ColA.
View 6 Replies
View Related
May 18, 2006
I have tried to process > 3 million Fuzzy grouping records on two different servers with no success. 3 mill works but anything above 4 mill doesn't. Some background:
We are trying to de-dup our customer table on: name (.5 min), address1 (.5 min), city (.5 min), state (exact). .8 overall record min score.
Output includes additional fields: customerid, sourceid, address2, country, phonenumber
Without SP1 installed I couldn't even get a few hundred thousand records to process
Two different servers - same problems. Note that SSIS and SQL Server are running locally on both
The higher end server has 4GB RAM, the other 2.5 GB RAM. Plenty of free disk space on both
SQL Server is configured to use 2 GB of RAM max
The page file is currently at 15GB
After running a number of test on both servers trying different batch sizes etc. the one thing I noticed is that it seems to always error out when SSIS takes over and starts chewing up all the available RAM. This happens after the index is created and SSIS starts "warming caches". On both servers SQL Server uses up about 1.6GB of RAM at this point while SSIS keeps taking over RAM until all physical RAM is used up.
Some questions:
Has anyone been able to process more then 3 million records and if so what is your hardware configuration?
Should we try running SSIS from a different server so it has access to the full amount of physical RAM? (so it doesn't have to fight for RAM with SQL Server)
Should we install Win 2003 Enterprise Server so we can add more RAM?
Any ideas why switching to the page file might be causing errors?
Thanks!!
Keith Doyle
View 17 Replies
View Related
Jun 10, 2008
Hey All, I'm trying to decide what's the 'best' to use. I've been designing and creating database for a while and have pretty much always used a surrogate key and not a normal one. I've finally had some free time to start studying more so in my spare time and read up and come accross a lot of guides, articles and stories that tout that normal keys should be used whenever possible as they're a better identifier and that surrogate keys should only be used when there is not a readily available normal key. Now perhaps I'd be open to accepting that but absolutely every database I come across tends to only use surrogate keys. For example I'm doing an authentication system from scratch and am looking at the User table. Now of course the user name has to be unique, should that be the primary key or should I have a seperate column with a guid or an incrementing int or the like as the primary key? I can certainly see that username could be used. I can also see how it may be easier when looking through the data tables to identify who/what a table is refering to with a surrogate key. However it still seems sort of sloppy, for lack of a better word, to me. Where now I could have somebody's username (or any other piece of data used for this purpose) spread accross a lot of other tables. And while writting this I just thought of the scenario that perhaps somebody needs their username changed, with this method now the ids need to be changed on all the related rows of all the other tables whereas with a surrogate key it wouldn't matter. Anyways I'm mostly looking for opinions on which way to go (not just with the user sample, but more in general).Thanks.
View 2 Replies
View Related
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
Oct 5, 2006
It's a dream of human beings to build machines that can think andbehave like human beings. The most important part of of such a machineis an artificial mind that can emulate the cognitive processing ofhuman mind.This book, "Next Generation Artificial Intelligence, Artificial Mind -Part One - Basic Architecture and Cognitive Structure" introduces abasic artificial mind architecture and computational model forcognitive processing. Inside the book, three important cognitiveprocess modeling components, mental objects network (MON),associative-learning mechanisms and a concept formation principle areintroduced. Based on the architecture and the computational model, onecan develop his own model of artificial mind according to his ownspecific requirements.The first edition of Artificial Mind - Part One is now available forpurchase from the author's personal web site. The price of the e-bookis USD7.00 (seven US dollars). An evaluation edition of this e-book isalso available for download from the web site.The author's personal web site:http://www.geocities.com/tomwingmak/
View 1 Replies
View Related
May 17, 2006
Hi...how can I write a dynamic sql statement to insert x number of child records? Thx in advance
View 4 Replies
View Related
Aug 5, 2014
I want to write a function, which accept 3 parameters, 1 TableName 2 ColumnName 3 DateValue, and returns number of records in that table for that particular date(in parameter date), I have written below function but it is not returning the desired result.
CREATE FUNCTION dbo.[f_Rec_cnt]
(@InTableName NVARCHAR(100),
@InDtColName NVARCHAR(50),
@InDate NVARCHAR(50)
)
RETURNS INT
[Code] .....
View 1 Replies
View Related
Aug 18, 2005
Arvind writes "i want to create a stored procedure returns an OUTPUT variable containing the no. of records given by a query, the query being dynamic.
Preferrably the query should also be passed as a parameter to the stored procedure...If not,it should be constructed in the SP and a Part of the where clause is dependant on the value of another variable passed to the SP.
How should the query be constructed, executed, and then the Count(*) value returned?
"WHERE <condition1> AND <condition 2> ;
"AND <condition 2> " may exist or may not exist in the query; it is dependant."
View 1 Replies
View Related
Jun 11, 2007
I'm trying to use the NEWID function in dynamic SQL and get an errormessage Incorrect syntax near the keyword 'ORDER'. Looks like I can'tdo an insert with an Order by clause.Here's the code:SELECT @SQLString = N'INSERT INTO TMP_UR_Randoms(Admit_DOCID,Client_ID, SelectDate, SelectType,RecordChosen)'SELECT @SQLString = @SQLString + N'(SELECT TOP ' + @RequFilesST + 'Admit_DOCID, Client_ID, SelectDate, SelectType, RecordChosen FROMFD__UR_Randoms 'SELECT @SQLString = @SQLString + N'WHERE SelectType = ' +@CodeRevTypeSt + ' AND SelectDate = ''' + @TodaySt + '''' + ' ORDERBY NEWID())'execute sp_executesql @SQLStringMy goal is to get a random percentage of records.The full SP follows. In a nutshell - I pull a set of records fromFD__Restart_Prog_Admit into a temporary table called FD__UR_Randoms.I need to retain the set of all records that COULD be eligible forselection. Based on the count of those records, I calculate how manyneed to be pulled - and then need to mark those records as "chosen".I'd just as soon not use the TMP_UR_Randoms table - I went that routebecause I ran into trouble with a #Tmp table in the above SQL.Can anyone help with this? Thanks in advance.Full SQL:CREATE PROCEDURE TP_rURRandomReview @ReviewType varchar(30)--Review type will fill using Crystal Parameter (setting defaults)AS/* 6.06.2007UR Requirements:(1) Initial 4-6 month review: 15% of eligible admissions(eligible via days in program and not yet discharged) must be reviewed4-6 months after admission. This review will be done monthly -meaning we'll have a moving target of names (with overlaps) whichcould be pulled from each month. (Minimum 5 records)(2) Subsequent 6-12 month review: Out of those already reviewed(in #1), we must review 25% of them (minimum of 5 records)(3) Initial 6-12 month review: Exclude any included in 1 or 2 -review 25% of admissions in program from 6-12 months (minimum 5)*/DECLARE @CodeRevType intDECLARE @PriorRec int -- number of records already markedeligible (in case user hits button more than once on same day for sametype of review)DECLARE @CurrRec int --number of eligible admitsDECLARE @RequFiles intDECLARE @SQLString nvarchar(1000)DECLARE @RequFilesSt varchar(100)DECLARE @CodeRevTypeSt char(1)DECLARE @TodayNotime datetimeDECLARE @TodaySt varchar(10)--strip the time off todaySELECT @TodayNotime = DateAdd(day,datediff(day,0,GetDate()),0)--convert the review type to a codeSelect @CodeRevType = Case @ReviewType when 'Initial 4 - 6 Month' then1 when 'Initial 6 - 12 Month' then 2 when 'Subsequent 6 - 12 month'then 3 END--FD__UR_Randoms always gets filled when this is run (unless it waspreviously run)--Check to see if the review was already pulled for this recordSELECT @PriorRec = (Select Count(*) FROM FD__UR_Randoms whereSelectType = @CodeRevType and SelectDate = @TodayNotime)If @PriorRec 0 GOTO ENDThis--************************************STEP A: Populate FD__UR_Randomstable with records that are candidates for review************************If @CodeRevType = 1BEGININSERT INTO FD__UR_Randoms (Admit_DOCID, Client_ID, SelectDate,SelectType,RecordChosen)(SELECT pa.OP__DOCID, pa.Client_ID,Convert(varchar(10),GetDate(),101) as SelectDate, @CodeRevType, 'F'FROM dbo.FD__RESTART_PROG_ADMIT paInner join FD__Client cOn pa.Client_ID = c.Client_IDWHERE Left(c.Fullname,2) <'TT' AND (Date_Discharge IS NULL)AND(DATEDIFF(d, Date_Admission, GETDATE()) 119)AND (DATEDIFF(d, Date_Admission, GETDATE()) <= 211)AND pa.OP__DOCID not in (Select Admit_DOCID from FD__UR_Randomswhere RecordChosen = 'T'))ENDIf @CodeRevType = 2--only want those that were selected in a batch 1 - in program 6-12months; selected for first reviewBEGININSERT INTO FD__UR_Randoms (Admit_DOCID, Client_ID, SelectDate,SelectType,RecordChosen)(SELECT pa.OP__DOCID, pa.Client_ID,Convert(varchar(10),GetDate(),101) as SelectDate, @CodeRevType, 'F'FROM dbo.FD__RESTART_PROG_ADMIT paInner join FD__Client cOn pa.Client_ID = c.Client_IDWHERE Left(c.Fullname,2) <'TT' AND (Date_Discharge IS NULL)AND(DATEDIFF(d, Date_Admission, GETDATE()) 211)AND (DATEDIFF(d, Date_Admission, GETDATE()) < 364)AND pa.OP__DOCID in (Select Admit_DOCID from FD__UR_Randomswhere SelectType = 1 AND RecordChosen= 'T'))ENDIf @CodeRevType = 3--only want those that were not in batch 1 or 2 - in program 6 to 12monthsBEGININSERT INTO FD__UR_Randoms (Admit_DOCID, Client_ID, SelectDate,SelectType,RecordChosen)(SELECT pa.OP__DOCID, pa.Client_ID,Convert(varchar(10),GetDate(),101) as SelectDate, @CodeRevType, 'F'FROM dbo.FD__RESTART_PROG_ADMIT paInner join FD__Client cOn pa.Client_ID = c.Client_IDWHERE Left(c.Fullname,2) <'TT' AND (Date_Discharge IS NULL)AND(DATEDIFF(d, Date_Admission, GETDATE()) 211)AND (DATEDIFF(d, Date_Admission, GETDATE()) < 364)AND pa.OP__DOCID NOT in (Select Admit_DOCID from FD__UR_Randomswhere SelectType < 3 AND RecordChosen= 'T'))ENDSELECT @CurrRec = (Select Count(*) FROM FD__UR_Randoms whereSelectType = @CodeRevType and SelectDate = @TodayNoTime)--*************************************STEP B Pick the necessarypercentage **************************************--if code type = 1, 15% otherwise 25%If @CodeRevType = 1BEGINSELECT @RequFiles = (@CurrRec * .15)ENDELSEBEGINSELECT @RequFiles = (@CurrRec * .25)END--make sure we have at least 5If @RequFiles < 5BEGINSELECT @RequFiles = 5End--*************************************STEP C Randomly select thatmany files**************************************--convert all variables to stringsSELECT @RequFilesSt = Convert(Varchar(100),@RequFiles)SELECT @CodeRevTypeSt = Convert(Char(1),@CodeRevType)SELECT @TodaySt = Convert(VarChar(10),@TodayNoTime,101)SELECT @SQLString = N'INSERT INTO TMP_UR_Randoms(Admit_DOCID,Client_ID, SelectDate, SelectType,RecordChosen)'SELECT @SQLString = @SQLString + N'(SELECT TOP ' + @RequFilesST + 'Admit_DOCID, Client_ID, SelectDate, SelectType, RecordChosen FROMFD__UR_Randoms 'SELECT @SQLString = @SQLString + N'WHERE SelectType = ' +@CodeRevTypeSt + ' AND SelectDate = ''' + @TodaySt + '''' + ' ORDERBY NEWID())'print @SQLStringexecute sp_executesql @SQLStringSELECT * FROM TMP_UR_Randoms/*--This select statement gives me what i want but I need to somehowmark these records and/or move this subset into the temp tableSelect Top @RequFilesFROM FD__UR_RandomsWHERE SelectType = @CodeRevType and SelectDate =Convert(varchar(10),GetDate(),101))ORDER BY NewID()*/ENDTHIS:GO
View 3 Replies
View Related
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
Mar 20, 2014
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29
output should be ......
ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29
View 0 Replies
View Related
Jan 28, 2004
Hi all
I have the following table
CREATE TABLE [dbo].[property_instance] (
[property_instance_id] [int] IDENTITY (1, 1) NOT NULL ,
[application_id] [int] NOT NULL ,
[owner_id] [nvarchar] (100) NOT NULL ,
[property_id] [int] NOT NULL ,
[owner_type_id] [int] NOT NULL ,
[property_value] [ntext] NOT NULL ,
[date_created] [datetime] NOT NULL ,
[date_modified] [datetime] NULL
)
I have created an 'artificial' primary key, property_instance_id. The 'true' primary key is application_id, owner_id, property_id and owner_type_id
In this specific instance
- property_instance_id will never be a foreign key into another table
- queries will generally use application_id, owner_id, property_id and owner_type_id in the WHERE clause when searching for a particular row
- Once inserted, none of the application_id, owner_id, property_id or owner_type_id columns will ever be modified
I generally like to create artificial primary keys whenever the primary key would otherwise consist of more than 2 columns.
What do people think the advantages and disadvantages of each technique are? Do you recommend I go with the existing model, or should I remove the artificial primary key column and just go with a 4 column primary key for this table?
Thanks Matt
View 5 Replies
View Related
Aug 25, 2007
Hi Craig/Kamal,
I got your email address from your web cast. I really enjoyed the web cast and found it to be
very informative.
Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question
regarding the product. I have looked for the information on the web, but was not able to find
relevant information.
We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets
Are generated using reporting services. On examining the excel sheet, I found out that the name
Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc.
And even the number of columns are not static. It depends upon the range of date selected by the user.
I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position
Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic
Number of columns.
Your help in this respect is highly appreciated!
Thanks,
Hi Anthony, I am glad the Web cast was helpful.
Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.
I suggest you post a question on the SSIS forum on MSDN and you should get some good answers.
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
Thanks
Craig Guyer
SQL Server Reporting Services
View 12 Replies
View Related
Nov 23, 2007
Hi,
I have a need to display on screen AND email a pdf report to email addresses specified at run time, executing the report with a parameter specified by the user. I have looked into data driven subscriptions, but it seems this is based on scheduling. Unfortunately for the majority of the project I will only have access to SQL 2005 Standard Edition (Production system is Enterprise), so I cannot investigate thoroughly.
So, is this possible using data driven subscriptions? Scenario is:
1. User enters parameter used for query, as well as email addresses.
2. Report is generated and displayed on screen.
3. Report is emailed to addresses specified by user.
Any tips on how to get this working?
Thanks
Mark Smith
View 3 Replies
View Related
May 2, 2007
If anyone could confirm...
SQL Server 2000 SP4 to multiple SQL Server 2005 Mobile Edition on PDAs. My DB on SQL2k is published with a single dynamic row filter using host_name() on my 'parent' table and also join filters from parent to child tables. The row filter uses joins to other tables elsewhere that are not published to evaluate what data is allowed through the filter.
E.g. Published parent table that contains suppliers names, etc. while child table is suppliers' products. The filter queries host_name(s) linked to suppliers in unpublished table elsewhere.
First initial sync with snapshot is correct and as I expected - PDA receives only the data from parent (and thus child tables) that matches the row filter for the host_name provided.
However - in my scenario host_name <--> suppliers may later be updated E.g. more suppliers assigned to a PDA for use or vice versa. But when I merge the mobile DB, the new data is not downloaded? Tried re-running snapshot, etc., no change.
Question: I thought the filters would remain dynamic and be applied on each sync?
I run a 'harmless' update on parent table using TSQL e.g. "update table set 'X' = 'X'" and re-sync. Now the new parent records are downloaded - but the child records are not!
Question: I wonder why if parent records are supplied, why not child records?
If I delete existing DB and sync new, I get the updated snapshot and all is well - until more data added back at server...
Any help would be greatly appreciated. Is it possible (or not) to have dynamic filters run during second or subsequent merge?
View 4 Replies
View Related
Mar 9, 2015
I have tried building an Inline TVF, as I assume this is how it would be used on the DB; however, I am receiving the following error on my code, I must be missing a step somewhere, as I've never done this before. I'm lost on how to implement this clr function on my db?
Error:
Msg 156, Level 15, State 1, Procedure clrDynamicPivot, Line 18
Incorrect syntax near the keyword 'external'.
CREATE FUNCTION clrDynamicPivot
(
-- Add the parameters for the function here
@query nvarchar(4000),
@pivotColumn nvarchar(4000),
[code]....
View 1 Replies
View Related
Mar 24, 2007
I have a Stored Procedure for processing a Bill of Material.
One column on the Assembly Table is a Function Name that contains some busniess rules.
OK, now I'm doing a Proof of Concept and I'm stumped.
Huuuuh!
I will ultimately have about 100 of these things. My plan was using Dynamic SQL to go execute the function.
Note: The function just returns a bit.
So; here's what I had in mind ...
if isnull(@FnNameYN,'') <> ''
exec spinb_CheckYN @FnNameYN, @InvLineID, @FnBit = @FnBit output
CREATE PROCEDURE dbo.spinb_CheckYN
@FnNameYN varchar(50),
@InvLineID int,
@FnBit bit output
AS
declare @SQL varchar(8000)
set @SQL = '
if dbo.' + @FnNameYN + ' (' + convert(varchar(31),@InvLineID) + ')) = 1
set @FnBit = 1
else
set @FnBit = 0'
exec (@SQL)
GO
Obviously; @FnBit is not defined in @SQL so that execution will not work.
Server: Msg 137, Level 15, State 1, Line 4
Must declare the variable '@FnBit'.
Server: Msg 137, Level 15, State 1, Line 5
Must declare the variable '@FnBit'.
So; is there a way to get a value out of a Dynamic SQL piece of code and get that value INTO my OUTPUT variable?
My many thanks to anyone who can solve this riddle for me.
Thank You!
Sigh: For now, it looks like I'll have a huge string of "IF" statements for each business rule function, as follows:
Hopefully a better solution comes to light.
------ Vertical Build1 - Std Vanes -----------
if @FnNameYN = 'fnb_YN_B1_14'
BEGIN
if dbo.fnb_YN_B1_14 (convert(varchar(31),@InvLineID) ) = 1
set @FnBit = 1
else
set @FnBit = 0
END
------ Vertical Build1 - Scissor Vanes -----------
if @FnNameYN = 'fnb_YN_B1_15'
BEGIN
if dbo.fnb_YN_B1_15 (convert(varchar(31),@InvLineID) ) = 1
set @FnBit = 1
else
set @FnBit = 0
END
.
.
.
etc.
View 10 Replies
View Related
Oct 24, 2004
I've looked up Books Online on Dynamic Cursor/ Dynamic SQL Statement.
Using the examples given in Books Online returns compilation errors. See below.
Does anyone know how to use Dynamic Cursor/ Dynamic SQL Statement?
James
-- SQL ---------------
EXEC SQL BEGIN DECLARE SECTION;
char szCommand[] = "SELECT au_fname FROM authors WHERE au_lname = ?";
char szLastName[] = "White";
char szFirstName[30];
EXEC SQL END DECLARE SECTION;
EXEC SQL
DECLARE author_cursor CURSOR FOR select_statement;
EXEC SQL
PREPARE select_statement FROM :szCommand;
EXEC SQL OPEN author_cursor USING :szLastName;
EXEC SQL FETCH author_cursor INTO :szFirstName;
--Error--------------------
Server: Msg 170, Level 15, State 1, Line 23
Line 23: Incorrect syntax near ';'.
Server: Msg 1038, Level 15, State 1, Line 24
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 1038, Level 15, State 1, Line 25
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 170, Level 15, State 1, Line 27
Line 27: Incorrect syntax near ';'.
Server: Msg 170, Level 15, State 1, Line 30
Line 30: Incorrect syntax near 'select_statement'.
Server: Msg 170, Level 15, State 1, Line 33
Line 33: Incorrect syntax near 'select_statement'.
Server: Msg 102, Level 15, State 1, Line 35
Incorrect syntax near 'author_cursor'.
Server: Msg 170, Level 15, State 1, Line 36
Line 36: Incorrect syntax near ':'.
View 2 Replies
View Related
Apr 15, 2008
I have a requirment which i have partly accomplished , but could not get through completely
i have a file which comes in a standard format ending with date and seq number ,
suppose , the file name is abc_yyyymmdd_01 , for first copy , if it is copied more then once the sequence number changes to 02 and 03 and keep going on .
then i need to transform those in to new file comma delimited destination file with a name abc_yyyymmdd,txt and others counting file counting record abc_count_yyyymmdd.txt. and move it to a designated folder. and the source file is then moved to archived folder
what i have taken apprach is
script task select source file --------------------> data flow task------------------------------------------> script task to destination file
dataflow task -------------------------> does count and copy in delimited format
what is happening here is i can accomlish a regular source file convert it to delimited destination file --------> and move it to destination folder with script task .
but cannot work the dynamic pick of a source file.
please advise with your comments or solution you have
View 14 Replies
View Related
Mar 2, 2014
I am trying to create an ssis package with dynamic csv file as output. and out format contains query output.
sample file name:
Unique identifier + query output + systemdate();
The expression is looking like this.
@[User::FilePath] + @[User::FileName] + ".CSV"
-- user filepath is a variable from ssis package. File name is the output from SQL query. using script task i have assigned the values to @[User::FileName] .
When I debugged the script task the value getting properly but same variable am using for Flafile destination. but its not working.
View 3 Replies
View Related
Mar 18, 2014
I have a situation where deleting old records is blocking updating latest records on highly transactional table and getting timeout errors from application.
In details, I have one table called Tran_table1 in OLTP database. This Tran_table1 is highly transactional table, it will receive data for insert/update continuously
While archiving 2 years old records from Tran_table1 into Tran_table1_archive in batches(using DELETE OUTPUT INTO clause), if there is any UPDATEs on Tran_table1,these updates are getting blocked and result is timeout errors in application.
Is there any SQL Server hints to avoid blocking ..
View 3 Replies
View Related
Sep 7, 2015
declare @table table (
ParentID INT,
ChildID INT,
Value float
)
INSERT INTO @table
SELECT 1,1,1.2
[code]....
This case ParentID - Child 1 ,1 & 2,2 and 3,3 records are called as parent where as null , 1 is child whoose parent is 1 similarly null,2 records are child whoose parent is 2 , .....
Now my requirement is to display parent records with value ascending and display next child records to the corresponding parent and parent records are sorted ascending
--Final output should be
PatentID ChildID VALUE
33 1.12
null3 56.7
null3 43.6
11 1.2
null1 4.8
null1 4.6
22 1.8
null1 1.4
View 2 Replies
View Related
