T-SQL (SS2K8) :: Shredding Complex XML Structure?
Jan 28, 2015
I have an xml file that I need to read out into columns and I'm having a problem with the section that has the persons address in it, a snippit of the file is shown below:-
DECLARE @xml XML
SET @xml = '
<PersonStructure>
<PersonName>
<GivenName>Mike</GivenName>
<FamilyName>Myers</FamilyName>
[code].....
Now the problem I am getting is, it is pulling out all the fields except it repeats the first part of the 'PersonAddress' field 3 times instead of giving me the whole address?
Is there a way of gaining access to all the parts within the 'PersonAddress' node even though all 3 parts within it are named the same (Address)?
View 2 Replies
ADVERTISEMENT
Jun 20, 2006
Hi,
I have generated a sample XML document from the XSD file using XMLSpy and used these in XML Source stage.
While I was parsing/Shredding the XML file and to write to sequential files I got the following error message.
The XML Source Adapter does not support mixed content model on Complex Types.
Additional Information:
Pipeline component has returned HRESULT error code )xC02092A1 from a method call. [Microsoft.SqlServer.DTSPipelineWrap]
Can anybody give a sample code with Complex XML type.
-----------------------------------
Find attached the XSD format. This is FYI.
View 4 Replies
View Related
Dec 30, 2011
I need to get the replacement records between the 2 tables. I have table A and table B with same structure. I have 5 fields. Table A has 50,000 records and table B has 20,000 records. I have fields id , name, address,meter_flag,end_Date.
Some of the records in Table B are just replacement records of table A. I mean for example I have records like this in Table A
id name address meter_flag end_date
23 john 1201 salt lake dr no 2011-12-28
24 tom 1222 gibson ln yes 2011-12-16
25 alex 1334 qayak dr no 2011-12-17
In Table B
23 john 1344 mc kinney st yes 2011-12-18
24 tom 1222 gibson ln yes 2011-12-16
56 gary 1335 pruitt rd no 2011-12-18
25 alex 1334 qayak dr no 2011-12-17
So here in Table B i have an update for john with id 23 in table A in address field and meter_flag has changed to yes. There is new record with id 25 in table b but that is not in table A. so I need to find all these difference records by querying these 2 table
View 9 Replies
View Related
Jan 24, 2015
I am working on creating a timetable app for school. I have been given a requirement as below
1. The weekdays like Monday,Tuesday,...should be displayed on top
2. Timing slot would be 8:00, 9:00,... which would be displayed vertically
the table would display as below
MondayTuesday WednesdayThursdayFriday Saturday
07:00Physics Chemistry Maths BiologySocialScience Craft
08:00MathsValueEducation Chemistry EnglishBiology Yoga
09:00GamesComputerScience Science Chemistry Hindi Maths
to display data like above would be simpler. The above timetable would be similar all through the year.
But my 3rd requirement is that, there is a option wherein the school admin can edit the timetable for any particular day or a week based on presence/absence of a particular teacher. So if a teacher who is teaching Maths is not present for 2 days, they might assign a different subject(eg., Physics) for those 2days to a different teacher.
so the table should look like below for those two days only based on the date selection.
Timings MondayTuesday WednesdayThursday Friday Saturday
07:00Physics Chemistry Physics Biology SocialScience Craft
08:00MathsValueEducation Chemistry English Biology Yoga
09:00GamesComputerScience Science Chemistry Hindi Physics
I am not sure how exactly I can mention the dates wherein I can allocate the days when the periods are changed and how exactly same timetable should be shown for the entire year if there is no change.
suppose the timetable is changed for two days 24/01/2015 and 25/01/2015 wherein Maths is replaced with Physics subject, how to retrieve the changed data for only those two days.
View 9 Replies
View Related
Jun 3, 2015
I have the following table:
SELECT 'A' as Item, '1' as Version, 0 as Counter, '01-01-2011' as CreatedDate UNION ALL
SELECT 'A' as Item, '1.1' as Version, 1 as Counter, '01-02-2011' as CreatedDate UNION ALL
SELECT 'A' as Item, '1.2' as Version, 2 as Counter, '01-03-2011' as CreatedDate UNION ALL
SELECT 'B' as Item, '1.2' as Version, 0 as Counter, '01-01-2011' as CreatedDate UNION ALL
[Code] .....
I want to write a script where if a user enters the version number, then the output should show all the upward and downward nodes..e.g. if a user selects '1.2' version then following should be the output
View 3 Replies
View Related
Feb 14, 2006
I have a package that starts by loading a recordset into an object variable. The recordset is then enumerated with a ForEach loop. The loop sets some string variables. Within the loop container I have a Script task that uses a MsgBox to show the results for testing purposes. The package uses checkpoint restart (if that matters?).
The first time I run the package the 1st record is displayed in the MsgBox, then the 2nd, but then the loop is stuck on the 2nd record forever. I break the run, and when I rerun it the 1st record is displayed followed by each subsequent record correctly and the package completes successfully. Now, if I were to run again the same problem would occur on rec 2 and I would have to break the run, and then the next run everything would work fine.
Why does the script get caught in an infinite loop the first time it's run, but works fine when restarting from the checkpoint?
Here's my relevant code:
ForEach: Enumerator=ADO Enumerator, Enumeration Mode=Rows in first table
Public Sub Main()
Dts.Variables("User::SQL1").Value = "SELECT src.* FROM " & Trim(Dts.Variables("User::Src1Tbl").Value.ToString)
MsgBox("sql=" & Dts.Variables("User::SQL1").Value.ToString)
Dts.TaskResult = Dts.Results.Success
End Sub
View 8 Replies
View Related
Nov 12, 2003
I have web forms with about 10-15 optional search parameters (fields) for a give table. Each item (textbox) in the form is treated as an AND condition.
Right now I build complex WHERE clauses based on wheather data is present in a textbox and AND each one in the clause. Also, if a particular field is "match any word", i get a ANDed set of OR's. As you can imagine, the WHERE clause gets quite large.
I build clauses like this (i.e., 4 fields shown):
SELECT * from tableName WHERE (aaa like '%data') AND (bbb = 'data') AND (ccc like 'data%') AND ( (xxx like '%data') OR (yyy like '%data%') )
My question is, are stored procedures better for building such dynamic SQL clauses? I may have one field or all fifteen. I've written generic code for building the clauses, but I don't know much about stored procedures and am wondering if I'm making this more difficult on myself.
View 7 Replies
View Related
Jul 20, 2005
Hi,I'm using DB2 UDB 7.2.Also I'm doing some tests on SQL Server 2000 for some statements touse efectively.I didn't find any solution on Sql Server about WITH ... SELECTstructure of DB2.Is there any basic structure on Sql Server like WITH ... SELECTstructure?A Sample statement for WITH ... SELECT on DB2 like belowWITHtotals (code, amount)AS (SELECT code, SUM(amount) FROM trans1 GROUP BY codeUNION ALLSELECT code, SUM(amount) FROM trans2 GROUP BY code)SELECTcode, SUM(amount)FROM totalsGROUP BY code.............................Note: 'creating temp table and using it' maybe a solution.However i need to know the definition of the result set of Unionclause. I don't want to use this way.CREATE TABLE #totals (codechar(10), amount dec(15))GOINSERT INTO #totalsSELECT code, SUM(amount) FROM trans1 GROUP BY codeUNION ALLSELECT code, SUM(amount) FROM trans2 GROUP BY codeGOSELECT code, sum(amount) FROM #totals GROUP BY codeGOAny help would be appreciatedThanks in advanceMemduh
View 3 Replies
View Related
Apr 26, 2006
Hello,
Lets look at this table :
CREATE TABLE [dbo].[TableHisto](
[Id] [int] NOT NULL,
[Week] [nvarchar](50) COLLATE French_CI_AS NULL,
[Project] [int] NOT NULL
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Identifiant d''enregistrement' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'TableHisto', @level2type=N'COLUMN', @level2name=N'Id'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date de l''enregistrement' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'TableHisto', @level2type=N'COLUMN', @level2name=N'Week'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Projet de référence' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'TableHisto', @level2type=N'COLUMN', @level2name=N'Project'
It is a table where i store projects week reports.
I want to make a request to display a table with project ID in Row, Weeks in columns and either TableHisto.id or Null value in cell.
I use SQL 2005. Thanks for any help
View 9 Replies
View Related
Sep 3, 2006
I need some help on how to structure a sql statement. I am creating a membership directory and I need the stored procedure to output the Last Name, First Name (and if married) Spouse First Name. Like this Flinstone, Fred & Wilma All members are in one directory linked by two fields. [Family ID] all the family members have the same family id and then there is a Family position id that shows if they are the Husband, Wife or Kids. I have no problem with this part select (LastName + ',' + FirstName) as Name, [Phone 1] as Phone, [Unit Name] as WD, [Street 1] as Street, SUBSTRING(City,1,3) as City, SUBSTRING(Postal,1,5) as Zipfrom Membership Where [HH Order]=1 Order By LastName ASC Could someone help me on how to display the " & Spouse FirstName " as part of the name field only if there is a spouse [HH Order]=2 for the current [Family ID]????
View 6 Replies
View Related
Nov 13, 2006
I need to get multiple values for each row in a database, then do a calculation and insert the calculation and the accountnumber related to the calculation the data, into a different column. I get an error trying it this way...there is no real identifier, it is jsut something that needs to get done per row...any ideas on how I can accomplish this?
Declare @NetCommission decimal
Declare @AccountNumber varchar(50)
Set @NetCommission = (select (CommissionRebate * Quantity)
from Account A
Join Trades T on A.AccountNumber = T.AccountNumber)
Set @AccountNumber = (select A.AccountNumber
from cmsAccount A
Join Trades T on A.AccountNumber = T.AccountNumber)
Insert into Transaction
(
Payee
,Deposit
,AccountNumber
)
Values
(
'Account Credit'
,@NetCommission
,@AccountNumber
)
View 13 Replies
View Related
Dec 1, 2006
Hello,
could someone help with this query in a stored proc.?
SET @SQL = 'SET ''' + @avgwgt + ''' = '
'(SELECT AVG(AverageWeight)
FROM CageFishHistory where CageID IN (' + @cagearray + ')
and ItemDate =''' + CONVERT(varchar(23),@startdate) + ''')'
EXEC @SQL
I'm trying to get an average value across dynamically selected rows. (I'm using a list array to deliver the selection to the stored proc). I need to re-use the average value within the procedure,so it's not enough to output it as a column of the resultset - EG. 'Select AVG(AverageWeight) as AvgWgt' . If I take out the @avgwgt line it works fine, but otherwise I'm getting this error:
"Incorrect syntax near '(SELECT AVG(AverageWeight)
FROM CageFishHistory where CageID IN ('."
It may be that I can access a column of the resultset in the rest of the procedure, and that would help avoid the use of pesky apostrophes, but I don't know how to do it.
View 3 Replies
View Related
Jan 6, 2007
I've written a lot of queries in the past, but I'm having a lot of trouble with this one.
View 4 Replies
View Related
Mar 19, 2007
I'm sure there is a way of cracking this, but I can't think of a good solution. Right now I am not happy with the solutions I come up with, one of which takes 4 minutes to run on SQL Server
The scenario: User is presented with search page where one or more search terms can be entered/selected. There are no required parameters. It can be any or all of the possibilities presented. Below is a model of the search parameters presented.
The user will either select to show more options under Profile ABC, or go down to Profile STU or Profile XYZ to show more options, or even select all Profiles and then select from Type 1 and either a. or. b. or. c. or ALL of the above.
I cannot predict what a user will make part of the search query so I have to have a stored procedure ready which can handle any or all of the parameters a user may select.
Am I biting off more than I can chew (it seems so)? Or is there an elegant way of handling the unknown combination of search parameters that a user might throw into my sql query?
I'm running this under ASP 1.0 and SQL Server 2000.
[check to show the options below] Profile ABC
[check to shore more options] Type 1
A. Contains fields for entering another data string and selecting from drop-down boxes
B. ditto
C. ditto
D. ditto
[check to show more options] Type 2
A. Contains fields for entering another data string and selecting from drop-down boxes
B. ditto
C. ditto
D. ditto
[check to show more options] Type 3
A. Contains fields for entering another data string and selecting from drop-down boxes
B. ditto
C. ditto
D. ditto
[check to select more options] Type 4
A. Contains fields for entering another data string and selecting from drop-down boxes
B. ditto
C. ditto
D. ditto
[check to show more options] Profile XYZ (as above)
[check to select more options] Profile STU (as above)
View 6 Replies
View Related
Sep 7, 2007
I'm working on a system that used to load control dynamically into a table structure based on "Row" and "Column" properties in the item object.
The system is now being revamped, and instead of a table structure, it's being loaded into a list, which will be controled by css. The new relevant variables are "Sequence" and "Width."
Since there are already thousands of existing items in the database, I have to write a script that can take a really good guess at legacy items' Row and Col, and input values for Sequence and Width.
Since all items exist on "tabs," I can query for all items on a given tabID, Ordered By Row, Col -- that will give me a sequence.
Width isn't literal, it has 6 presets: Whole, Half, Third, Quarter, Two Thirds, Three Quarters, represented in the table as 0,1,2,3,4,5 -- for our purposes, I'll assume that all items on a row are equal in width. We can determine width by figuring out the number of items within the same row, so if there is only one in the row, it's a Whole (0), if there are three in the row it'll be a Third (2), etc.
I'd like to create a query that gets all items by tab, assigns the appropriate sequence, and figures out how many items are in the row with a given item, to assign the correct width.... but I have no idea how to make t-sql do that. I don't mind multiple queries to get the whole process done, and it doesn't need to be efficient -- this is a one-off script to run to give legacy items values that we can work with.
Where would I start?
View 1 Replies
View Related
Oct 30, 2007
HI.
I have 3 tables
1- std with : stdID , programID.
2- Programs with :ProgramID , Cost
3 - Movements with : stdID , balance.
the first table contain the stdID and ProgramID , some times the std hasn't programID that mean he hasn't programID. then we return null.
if the std has programID there is to cases.
the first one he have a movement on his balance then we get the biggest balance for the std.
the second case he hasn't any moventen then we get his balance from Programs table by the ProgramID .
I need sql server function that return table like this
stdID , Balance
that means every std with his Balance.
Regards.
View 11 Replies
View Related
May 13, 2008
This is too complex anyone know how to make it less complex.
I am trying to get all the selected fields from contacts into a datagrid where the other fields contain a string in textbox1.
This works
SELECT [company], [contactname], [emailaddress], [secondemailaddress], [phonenumber], [webpage] FROM [contacts] WHERE (([AB] LIKE '%' + ? + '%') AND ([AL] LIKE '%' + ? + '%'))
When i add all the rest of the fields it says its too complex. Please Help
SELECT [company], [contactname], [emailaddress], [secondemailaddress], [phonenumber], [webpage] FROM [contacts] WHERE (([AB] LIKE '%' + ? + '%') AND ([AL] LIKE '%' + ? + '%') AND ([B] LIKE '%' + ? + '%') AND ([BB] LIKE '%' + ? + '%') AND ([BD] LIKE '%' + ? + '%') AND ([BA] LIKE '%' + ? + '%') AND ([BH] LIKE '%' + ? + '%') AND ([BL] LIKE '%' + ? + '%') AND ([BN] LIKE '%' + ? + '%') AND ([BR] LIKE '%' + ? + '%') AND ([BS] LIKE '%' + ? + '%') AND ([BT] LIKE '%' + ? + '%') AND ([CA] LIKE '%' + ? + '%') AND ([CB] LIKE '%' + ? + '%') AND ([CF] LIKE '%' + ? + '%') AND ([CH] LIKE '%' + ? + '%') AND ([CM] LIKE '%' + ? + '%') AND ( LIKE '%' + ? + '%') AND ([CR] LIKE '%' + ? + '%') AND ([CT] LIKE '%' + ? + '%') AND ([CV] LIKE '%' + ? + '%') AND ([CW] LIKE '%' + ? + '%') AND ([DA] LIKE '%' + ? + '%') AND ([DD] LIKE '%' + ? + '%') AND ([DE] LIKE '%' + ? + '%') AND ([DG] LIKE '%' + ? + '%') AND ([DH] LIKE '%' + ? + '%') AND ([DL] LIKE '%' + ? + '%') AND ([DN] LIKE '%' + ? + '%') AND ([DT] LIKE '%' + ? + '%') AND ([DY] LIKE '%' + ? + '%') AND ([E] LIKE '%' + ? + '%') AND ([EC] LIKE '%' + ? + '%') AND ([EH] LIKE '%' + ? + '%') AND ([EN] LIKE '%' + ? + '%') AND ([EX] LIKE '%' + ? + '%') AND ([FK] LIKE '%' + ? + '%') AND ([FY] LIKE '%' + ? + '%') AND ([G] LIKE '%' + ? + '%') AND ([GL] LIKE '%' + ? + '%') AND ([GU] LIKE '%' + ? + '%') AND ([GY] LIKE '%' + ? + '%') AND ([HA] LIKE '%' + ? + '%') AND ([HD] LIKE '%' + ? + '%') AND ([HG] LIKE '%' + ? + '%') AND ([HP] LIKE '%' + ? + '%') AND ( LIKE '%' + ? + '%') AND ([HU] LIKE '%' + ? + '%') AND ([HX] LIKE '%' + ? + '%') AND ([IM] LIKE '%' + ? + '%') AND ([IP] LIKE '%' + ? + '%') AND ([IV] LIKE '%' + ? + '%') AND ([JE] LIKE '%' + ? + '%') AND ([KA] LIKE '%' + ? + '%') AND ([KT] LIKE '%' + ? + '%') AND ([KW] LIKE '%' + ? + '%') AND ([KY] LIKE '%' + ? + '%') AND ([L] LIKE '%' + ? + '%') AND ([LA] LIKE '%' + ? + '%') AND ([LD] LIKE '%' + ? + '%') AND ([LE] LIKE '%' + ? + '%') AND ([LL] LIKE '%' + ? + '%') AND ([LN] LIKE '%' + ? + '%') AND ([LS] LIKE '%' + ? + '%') AND ([LU] LIKE '%' + ? + '%') AND ([M] LIKE '%' + ? + '%') AND ([ME] LIKE '%' + ? + '%') AND ([MK] LIKE '%' + ? + '%') AND ([ML] LIKE '%' + ? + '%') AND ([N] LIKE '%' + ? + '%') AND ([NE] LIKE '%' + ? + '%') AND ([NG] LIKE '%' + ? + '%') AND ([NN] LIKE '%' + ? + '%') AND ([NP] LIKE '%' + ? + '%') AND ([NR] LIKE '%' + ? + '%') AND ([NW] LIKE '%' + ? + '%') AND ([OL] LIKE '%' + ? + '%') AND ([OX] LIKE '%' + ? + '%') AND ([PA] LIKE '%' + ? + '%') AND ([PE] LIKE '%' + ? + '%') AND ([PH] LIKE '%' + ? + '%') AND ([PL] LIKE '%' + ? + '%') AND ([PO] LIKE '%' + ? + '%') AND ([PR] LIKE '%' + ? + '%') AND ([RG] LIKE '%' + ? + '%') AND ([RH] LIKE '%' + ? + '%') AND ([RM] LIKE '%' + ? + '%') AND ([S] LIKE '%' + ? + '%') AND ([SA] LIKE '%' + ? + '%') AND ([SE] LIKE '%' + ? + '%') AND ([SG] LIKE '%' + ? + '%') AND ([SK] LIKE '%' + ? + '%') AND ([SL] LIKE '%' + ? + '%') AND ([SM] LIKE '%' + ? + '%') AND ([SN] LIKE '%' + ? + '%') AND ([SO] LIKE '%' + ? + '%') AND ([SP] LIKE '%' + ? + '%') AND ([SR] LIKE '%' + ? + '%') AND ([SS] LIKE '%' + ? + '%') AND ([ST] LIKE '%' + ? + '%') AND ([SW] LIKE '%' + ? + '%') AND ([SY] LIKE '%' + ? + '%') AND ([TA] LIKE '%' + ? + '%') AND ([TF] LIKE '%' + ? + '%') AND ([TN] LIKE '%' + ? + '%') AND ([TQ] LIKE '%' + ? + '%') AND ([TR] LIKE '%' + ? + '%') AND ([TS] LIKE '%' + ? + '%') AND ([TW] LIKE '%' + ? + '%') AND ([UB] LIKE '%' + ? + '%') AND ([W] LIKE '%' + ? + '%') AND ([WA] LIKE '%' + ? + '%') AND ([WC] LIKE '%' + ? + '%') AND ([WD] LIKE '%' + ? + '%') AND ([WN] LIKE '%' + ? + '%') AND ([WR] LIKE '%' + ? + '%') AND ([WS] LIKE '%' + ? + '%') AND ([WV] LIKE '%' + ? + '%') AND ([YO] LIKE '%' + ? + '%'))
View 10 Replies
View Related
May 20, 2008
hi how are you please help me in my problem which i can't make it.
Now, i have a project in ASP.NET and SQL Server 2005. let's call the project an image gallery, in my project i have a table named "Category" in which all the categories are in this table. also while adding a new category a new table will be created automatically with the name of that category.
now, what i want is that to build a query that reads the contents of the tables that the tables name are the names of the each record in the "Category" table.
is that possible ?
please if any one help can me in my problem.
View 4 Replies
View Related
Feb 10, 2004
I have the following SQL 2000 database table:
NEWS (IDNews, Country, PublishDate, Title)
I have to get a dataset containing only one record for each country, having most recent publish date.
Any suggestions? Thanks.
View 3 Replies
View Related
Feb 8, 2005
I have 2 tables, say table1, and table2. There is a DocID (primary key) in table1. In table2, DocID is the foriegn key. There can be more than 1 DocID.
this is the table structure (sample)
Table 1:
DocID DocName OtherID etc
1 test 2
2 test2 3
Table2:
TblID DocID OtherID
1 1 10
2 1 13
3 1 25
how do I join these two tables, such that I get all the otherID's for each DocID.
ie.,
DocID OtherID
1 2 and 10 and 13 and 25
2 3
i am writing this query to display search results on a search page (with keyword search) and so, if I display the result in more than one row, then the user might think that there is more than document...whereas the case is that there is only one document with more than one other ID's.
is there any way I can do this? display...more than 1otherID in the same row for the same DociD?
Currently, I am using a left outer join of table1 and table2.
An suggestions on how to do this?
View 6 Replies
View Related
Oct 1, 2005
I cant get "order by" to work in this sql query..I use this query:
"SELECT DISTINCT TOP 12 name,total = COUNT(*) FROM products where kat = 'music' group by namn"and I want to add this some where to get 12 random records: "ORDER BY NewID()"I tried this: "SELECT DISTINCT TOP 12 name,total = COUNT(*) FROM products where kat = 'music' group by namn ORDER BY NewID()"" but get the error:"ORDER BY items must appear in the select list if SELECT DISTINCT is specified"I can´t figure out how I should write the query..Somebody have any ideas??/Radiwoi
View 2 Replies
View Related
Apr 11, 2003
Hi,
I need a way to retrieve records from a table with a 30 min interval between the records.
For e.g., Lets say I have the following data in a table :-
userid hitdt
1 4/1/2003 10:00 AM
1 4/1/2003 10:15 AM
1 4/1/2003 10:31 AM
1 4/1/2003 11:10 AM
1 4/1/2003 11:30 AM
1 4/1/2003 11:41 AM
I need a query which would return me the following recordset :-
userId hitId
1 4/1/2003 10:00 AM
1 4/1/2003 10:31 AM
1 4/1/2003 11:10 AM
1 4/1/2003 11:41 AM
Is there a way to do this without using a cursor ?
Thanks
View 7 Replies
View Related
Sep 7, 2003
table_a has patient_id, tran_id and other fields a,b,c
table_b has patient_id,tran_id, key_id
and other fileds d,e,f
table_a patien_id + tran_id is unique
table_b patient_id + tran_id is not unique, could be duplicated.
I have to create a query which will retrieve fields from table a a,b,c and fields d,e from table b where
table a. patient_id + tran_id =
tableb.patient_id + tran_id and table_b.key_id is the min key_id for that patient_id + tran_id.
I should retrieve just one record.
How would I be able to do that?
Please help!!
Thanks
View 2 Replies
View Related
Jun 13, 2001
I have a sp which requires a somewhat(at least for me) where clause. It needs a standard clause but then needs to differentiate the where based on whether a certain field is null or not. I didn't think an if would work but neither is my case. Below is the where clause. Thanks
where(OTHER_ORDER_DATES_.DF_RESTRICTION1 in
('Dental hold', 'Medical hold', 'Mental health hold')
AND
case when OTHER_ORDER_DATES_.DF_RES1TO is not null
then
OTHER_ORDER_DATES_.DF_RES1FROM <=@sdate AND
OTHER_ORDER_DATES_.DF_RES1TO >= @edate
when OTHER_ORDER_DATES_.DF_RES1TO is null
OTHER_ORDER_DATES_.DF_RES1FROM <=@edate
View 6 Replies
View Related
Jun 10, 2005
Code:
ID GroupID User
1 101 Tom
2 101 Mark
3 101 Clark
4 102 Tom
5 102 Mark
6 103 Tom
7 103 Clark
8 104 Tom
9 104 Clark
10 105 Tom
11 105 Bred
the users of Group 101 are Tom,Mark,Clark
the users of Group 102 are Tom,Mark
the users of Group 103 are Tom,Clark
the users of Group 104 are Tom,Clark
the users of Group 105 are Tom,Bred
I want to show Tom that
Both You and Clark are together in 3 groups
Both You and Mark are together in 2 groups
Both You and Bred are together in 1 group
View 5 Replies
View Related
Jan 27, 2007
I might have to redesign the tables for this, but I'll ask anyway. I have a table with the following fields:
Email - VarChar
Seminar - Int
PeckingOrder - Int.
As I add addresses to the table, each one has a Seminar, and then each Seminar has a Pecking Order Value. If an email address shows up for more than one seminar, it can have multiple records. IE:
email1 - 1523 - 424
email1 - 1526 - 124
email1 - 1524 - 235
email2 - 1526 - 124
email2 - 1524 - 235
for address, seminar, and pecking order would be sample entries into the table. Give or Take 1000 records in the table at any given time. What I want to pull out is:
Distinct Email Addresses
For each Email Address - The Max(PeckingOrder)
And the Seminar that's associated with Max(PeckingOrder)
For the sample data set above, I'd want to see these two records returned by the query:
email1 - 1523 - 424
email2 - 1524 - 235
I can't seem to get the Having / Where clause right to pull those two records properly. Anyone have any suggestions?
Thanks,
--Daniel
Edited - Didn't realize the BB removed email addresses.
View 1 Replies
View Related
Nov 23, 2006
Here is my data
------------------------------
BookName | Price
Book 1 | 75
Book 2 | 100
Book 3 | 45
Book 4 | 10
I want to make a report eg:
Category | Total
>= 75 | 2
25 - 74 | 1
< 25 | 1
View 1 Replies
View Related
Feb 15, 2007
okay;
i have a table called tblSlots
tblSlots is a list of Start datetimes and End datetimes.
every day has the same list of 10 slots.
tblSlots:
PKSlotINDEX
datStartTime
datEndTime
then i have a table called tblPersons
tblPersons:
PKPersonINDEX
txtLast
txtFirst
then i have a table called tblSchedule
tblSchedule:
PKScheduleINDEX
fkSlotINDEX
fkPersonINDEX
i want to write a query that takes any one specific person's schedule for an entire specific day, adds an arbitrary number of days to datStartTime, and finally inserts the PKSlotINDEX corresponding to the calcultated StartTime and fkPersonINDEX.
wow complicated isnt it...
the goal is to take the schedule of one day for one person and copy it to another day; i can scrap my current layout if necessary.
thanks!
View 1 Replies
View Related
Oct 10, 2007
Code:
SELECT
goto_last_name as 'Manager',
advisor_name,
advisor_ao_number as 'AO',
COUNT (CASE advisor_termination_date WHEN '<Null>' THEN 1 END) as '#Advisors',
SUM(Weekly_TOS_GDC) as 'TOS GDC'
FROM weekly_condensed_tb
WHERE advisor_platform_number = 1
AND report_date BETWEEN CAST(CONVERT(VARCHAR,'9/11/2007',112) AS DATETIME) -28
AND CAST(CONVERT(VARCHAR,'9/11/2007',112) AS DATETIME)
GROUP BY goto_last_name,advisor_ao_number,advisor_name
HAVING COUNT (CASE advisor_termination_date WHEN '<Null>' THEN 1 END) > 1
In the query above i select a range from the date - 28 days and do a sum on the TOS GDC column. What i am trying to do is have another query where the range will be - 56 and maybe - 86 so i get the SUMS for that as well. I need to display this in SQL Reporting Services and i can only have one DataSet returned otherwise i will not be able to bind it to one table.
How can i go about this so i return one set of data for 3 different date ranges.
Thanks
View 2 Replies
View Related
Aug 26, 2005
Hey all,
I have 3 tables: 1 table with Module information (1 quiz-like part of an Exam), 1 table with the relationships between Modules and Exams (ratios) and 1 table with User information, that contains the Exam number, Module number, and score. I am trying to get a full list of modules' names for 1 exam, and the user's score for those modules they taken. All this would pertain to 1 single Exam. I am only getting records returned for those modules that the user has taken; I want the full list with NULL values for the scores of modules the user has not taken. Hope that made sense, here is my attempt:
Code:
SELECT ExamModules.Name, ExamUsers.Score
FROM ExamToModule LEFT OUTER JOIN
ExamModules ON ExamToModule.ModuleID = ExamModules.ID LEFT OUTER JOIN
ExamUsers ON ExamToModule.ModuleID = ExamUsers.MID AND ExamToModule.ExamID = ExamUsers.EID
WHERE (ExamToModule.ExamID = 1) AND (ExamUsers.UserName = 'dizzle')
In this case the user is Dizzle and the Exam's ID (primary key) is 1. I've changed LEFT to RIGHT and FULL, they all return the same few records. Help?
View 4 Replies
View Related
Jun 2, 2004
I'm struggling with a syntax error in my sp. I'm hoping someone can give it a fresh look and straighten me out. Thanks.
Background:
1. The innermost query (using the dynamic statements) will run fine on its own.
2. I can wrap that in another query (with the innermost as a DERIVEDTBL) and it will run fine UNLESS I apply the GROUP BY. Then I get syntax errors.
3. Also, the entire query runs fine as a view (with hard coded vals for column and value).
4. I don't know what kind of formatting this message will apply, so just know that I've checked my line truncations and they all include a trailing '+. Also, I know that Occured is misspelled. That is the correct object name. Not all our DB admins are lit majors.
Here's the code:
CREATE PROCEDURE [dbo].[sp_ninetydayavgtotals]
@column nvarchar(100),
@value smallint
AS
DECLARE @SelectString nvarchar(500)
SET @SelectString = 'SELECT AVG(total_attacks) AS avg_attacks ' +
'FROM (SELECT TOP 100 PERCENT thedate AS [day], COUNT(total) AS total_attacks, SUM(enemyKIA) AS EKIA, SUM(enemyWIA) AS EWIA, SUM(coalitionKIA) AS CKIA, SUM(coalitionWIA) AS CWIA ' +
'FROM SELECT(TOP 100 PERCENT CONVERT(nvarchar, dateOccured, 11) AS thedate, txtCategory AS total, enemyKIA, enemyWIA, coalitionKIA, coalitionWIA ' +
'FROM dbo.v_maindata ' +
'WHERE (CONVERT(nvarchar, dateOccured, 11) > CONVERT(nvarchar, (getdate()-90), 11)) AND (CONVERT(nvarchar, dateOccured, 11) != CONVERT(nvarchar, getdate(), 11)) AND ' + @column + ' = @value ' +
'ORDER BY dateOccured DESC) DERIVEDTBL ' +
'GROUP BY thedate ' +
'ORDER BY thedate DESC) DERIVEDTBL'
EXEC sp_executesql @SelectString, N'@column nvarchar(100), @value smallint', @column, @value
GO
-------------------------------------------------------------------------
Owen Eustice
MNC-I Webmaster
Victory Base South
View 5 Replies
View Related
Aug 11, 2004
Result of the select from dbUsers is
db_name User Group
DB1 U1 db_datareader
DB1 U1 db_datawriter
DB1 U1 db_ddladmin
Db2 U1 db_dataReader
Db2 U1 db_datawriter
Db2 U2 db_dataReader
Db2 U2 db_datawriter
I somehow want it in a way so that the Db_name and USer_name are not repeated .
IS it possible ?
thanks
View 3 Replies
View Related
Oct 29, 2004
Hi,
I have a sql table with corpcode, EmpName, rate, reportdate and Amount fields. I need to write a query that can return corpcode, EmpName, rate and sum of Amount for January, Sum of Amount for Feburary, Sum of Amount for March, Sum of Amount for April, ..........., Sum of Amount for December, Total for All months, Average for all months.
I tried few option, it didn't work for me, Is it possible to do? Have some tried like this earlier?
Thanks!
View 12 Replies
View Related
