Permutate Rows Based On Table
Jul 23, 2005
Hi,
I wish to create new rows of data based on a source table. Example: I
have a file that contains a SESSION (time roughly a calendar quarter
for a University), START_DT, END_DT.
I want to create rows that would be for each session and each day, so
for session 200102 that starts 09/10/2000 and ends 12/15/2000 I want 96
rows, with 200102 for Session, and the 96 days in DAY.
-- Source table:
CREATE TABLE [F___Example_Date] (
[SESSION_ID] [numeric](19, 0) NOT NULL ,
[START_DT] [datetime] NULL ,
[END_DT] [datetime] NULL
) ON [PRIMARY]
-- Sample data :
INSERT INTO [DS_V5_Source].[dbo].[F___Example_Date]([SESSION_ID],
[START_DT], [END_DT])
VALUES(200102, '2000-09-10', '2000-12-15')
INSERT INTO [DS_V5_Source].[dbo].[F___Example_Date]([SESSION_ID],
[START_DT], [END_DT])
VALUES(200103, '2001-01-04', '2001-03-26')
-- Example Target file to be populated:
CREATE TABLE [Target_Date] (
[SESSION_ID] [numeric](19, 0) NOT NULL ,
[Day] [datetime] not null
) ON [PRIMARY]
GO
-- Example of inserts to that represent the desired results (my insert
if for illustration only, not intended to be elegant)
INSERT INTO [DS_V5_Source].[dbo].[Target_Date]([SESSION_ID], [Day])
VALUES(200102,'2000-09-10')
INSERT INTO [DS_V5_Source].[dbo].[Target_Date]([SESSION_ID], [Day])
VALUES(200102,'2000-09-11')
INSERT INTO [DS_V5_Source].[dbo].[Target_Date]([SESSION_ID], [Day])
VALUES(200102,'2000-09-12')
INSERT INTO [DS_V5_Source].[dbo].[Target_Date]([SESSION_ID], [Day])
VALUES(200102,'2000-09-13')
-- <... and so forth for all days between 9/10/2000 and 12/15/2000...>
INSERT INTO [DS_V5_Source].[dbo].[Target_Date]([SESSION_ID], [Day])
VALUES(200102,'2000-12-15')
My need is getting into a new area of my SQL experiance and I'm not
sure how to approach solving this problem. I'm confident once I learn
how to solve this, I will be able to do a lot more with SQL.
TIA
Rob
View 3 Replies
ADVERTISEMENT
Jul 20, 2005
I have two tables that are related by keys. For instance,Table employee {last_name char(40) not null,first_name char(40) not null,department_name char(40) not null,age int not null,...}Employee table has a primary key (combination of last_name and first_name).Table address {last_name char(40) not null,first_name char(40) not null,street char(200) not null,city char(100) not null,...}Address table has a primary key (combination of last_name, first_name andstreet in which (last_name, first_name) reference (last_name, first_name) inemployee table.Now I want to delete some rows in Address table based on department_name inEmployee table. What is sql for this delete?I appreciate your help. Please ignore table design and I just use it for myproblem illustration.Jim
View 1 Replies
View Related
Mar 7, 2008
I need Insert rows in the OrderDetails Table based on values in the Orders Table
In the Orders table i have a columns called OrderID and ISale.
In the OrdersDetails i have columns called OrderID and SaleType
For each value in the OrderID Column of the Orders Table, anytime the ISale Column in the Orders table = 1, and the SalesType column in the OrderDetails table is empty, I want to add two rows in the OrderDetails table. One row with the value K and another row with the value KD.
That is a row will be added and the value in the SalesType column will be K, also a second row will be added and the value in the SalesType column will be KD
Also for each value in the OrderID Column of the Orders Table, anytime the ISale Column in the Orders table = 0, and the SalesType column in the OrderDetails table is empty, I want to add two rows in the OrderDetails table. One row with the value Q and another row with the value QD
That is a row will be added and the value in the SalesType column will be Q, also a second row will be added and the value in the SalesType column will be QD.
I need a SQL Script to accomplish this. thanks
View 6 Replies
View Related
Apr 23, 2008
I have 2 tables with 2 fields in common between them. How do I get a count of rows in table 1 but not in table 2 matching those 2 fields? Thanks.
View 7 Replies
View Related
Apr 12, 2007
Hello All
I am trying to figure out if what i am attempting to do is possible and whether or not my approach is wrong to begin with.
I am trying to build a custom report for our accounting system which is Traverse from Open systems. This is what i have done in the stored procedure thus far
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE rptArFLSalesByCustItemized_sp
@custId pCustID,
@dateFrom datetime,
@dateThru datetime,
@itemIdFrom pItemId,
@itemIdThru pItemId
as
set nocount on
-- define some variables for previous year
declare @LYqty int, @LyAmt money, @LYfrom datetime, @LYthru datetime
-- set defaults
SET @itemIdFrom=ISNULL(@itemIdFrom,(SELECT MIN(itemId) FROM tblInItem))
SET @itemIdThru=ISNULL(@itemIdThru,(SELECT MAX(itemId) FROM tblInItem))
SET @LYfrom=DATEADD(YEAR,-1,@dateFrom)
SET @LYthru=DATEADD(YEAR, -1, @dateThru)
-- create small temp table to hold customer info
Create Table #tmpArCustInfo
(
custId pCustID,
custName VARCHAR (30),
)
-- populate customer temp table with info
Insert into #tmpArCustInfo
select custId, custName
from tblArCust
WHERE custId = @custId
-- create a temp table to hold the Data for each Item
Create Table #tmpArSalesItemized
(
itemId pItemId,
productLine VARCHAR (12),
pLineDesc VARCHAR (35),
descr VARCHAR (35),
LYQtySold int,
LYTDQtySold int,
QtySold int,
LYTDsales money,
totalSales money,
LastInvDate datetime,
)
-- populate the temp table with all of the inventory items
insert into #tmpArSalesItemized
select ii.itemId, ii.productLine, ip.Descr, ii.Descr, 0,0,0,0,0, NULL
from tblInItem ii, tblInProductLine ip
where ip.productLine = ii.productLine
AND ii.itemId BETWEEN @itemIdFrom AND @itemIdThru
-- update table with this years quantities
update #tmpArSalesItemized
SET QtySold = (select SUM(QtyOrdSell) from tblArHistDetail hd
where TransId IN (select TransId from tblArHistHeader where custId = @custId)
AND orderDate IN (select OrderDate from tblArHistHeader where OrderDate BETWEEN @dateFrom AND @dateThru)
AND hd.partId BETWEEN @itemIdFrom AND @itemIdThru
GROUP BY hd.partId
)
-- Return the temp tables results
select * from #tmpArSalesItemized, #tmpArCustInfo
drop table #tmpArSalesItemized, #tmpArCustInfo
return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
My problems begin where i want to start updating all of the Qty's of the QtySold field. I have managed to get it to write the same sum in every field but i cannot figure out how to update each row based on the sum of the qty found for that item in the tblArHistDetails table, trouble is too that there is no reference to the custId in that table either. The custId resides in tblArHistHeader and is linked to the details table via the TransId column. So really i need to update many rows based on criteria from 2 other tables.
Can anyone please help? I dont have a clue how to make this work, and most of what i have learned about sql thus far has been from opening other stored procs etc in the accounting system and just reading to see how the developers have done things.
Thanks
Jamie
View 1 Replies
View Related
Aug 20, 2006
I wish to build a table based on values from another table.I need to populate a table between two dates from another table. Usingthe START_DT and END_DT, create records between those dates.I need a new column that is the days between the date and the MID_DTThe data I wish to end with would look something like this:PERIOD DATE DAY_NO200602 2005-07-06 -89200602 2005-07-07 -88200602 2005-07-08 -87<...>200602 2005-10-02 -2200602 2005-10-03 -1200602 2005-10-04 0200602 2005-10-05 1<...>200602 2005-12-18 75CREATE TABLE "dbo"."tblDates"("PERIOD" CHAR(6) NOT NULL,"START_DT" DATETIME NULL,"MID_DT" DATETIME NULL,"END_DT" DATETIME NOT NULL)INSERT INTO tblDates VALUES('200505',2005-04-12,2005-07-05,2005-09-12)INSERT INTO tblDates VALUES('200602',2005-07-06,2005-10-03,2005-12-18)INSERT INTO tblDates VALUES('200603',2005-10-04,2006-01-17,2006-03-27)INSERT INTO tblDates VALUES('200604',2006-01-18,2006-04-10,2006-06-19)INSERT INTO tblDates VALUES('200605',2006-04-11,2006-07-04,2006-09-11)INSERT INTO tblDates VALUES('200702',2006-07-05,2006-10-02,2006-12-18)
View 7 Replies
View Related
Mar 21, 2008
i have a report that contains a sub-report that is embeded in one of the textboxes of the table. what i am trying to do
is have the main table toggle visilibity based on the value of the sub-report...in my case the toggle is very simple - if the sub-report returns any value at all, then i need the row in the parent table to be 'visible'
any help would be greatly appreciated.
thanks!
-dk
View 4 Replies
View Related
Nov 21, 2007
I have a form to assign JOB SITES to previously created PROJECT. The JOB SITES appear in the DataList as it varies based on customer. It can be 3 to 50 JOB SITES per PROJECT.
I have "PROJECT" table with all necessary fields for project information and "JOBSITES" table for job sites. I also created a new table called "PROJECTSITES" which has only 2 columns: "ProjectId" and "SiteId".
What I am trying to do is to insert multiple rows into that "PROJECTSITES" table based on which checkbox was checked. The checkbox is located next to each site and I want to be able to select only the ones I need. Btw the Datalist is located inside of a formview and has it's own datasource which already distincts which JOBSITES to display.
Sample:
ProjectId - SiteId
1 - 5
1 - 9
1 - 16
1 - 18
1 - 20
1 - 27
1 - 31
ProjectId stays the same, only values for SiteId are being different.
I hope I explaining it right. Do I have to use some sort of loop to go through the automatically populated DataList records and how do I make a multiple inserts to database table? We use SQL Server 2005 and VB for code behind. Please ask if I missed on some information. Thank you in advance.
View 10 Replies
View Related
May 21, 2014
I have a table with PO#,Days_to_travel, and Days_warehouse fields. I take the distinct Days_in_warehouse values in the table and insert them into a temp table. I want a script that will insert all of the values in the Days_in_warehouse field from the temp table into the Days_in_warehouse_batch row in table 1 by PO# duplicating the PO records until all of the POs have a record per distinct value.
Example:
Temp table: (Contains only one field with all distinct values in table 1)
Days_in_warehouse
20
30
40
Table 1 :
PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20
2 5 30
3 7 40
Updated Table 1:
PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20 20
1 10 20 30
1 10 20 40
2 5 30 20
2 5 30 30
2 5 30 40
3 7 40 20
3 7 40 30
3 7 40 40
how can I update Table 1 to get desired results?
View 2 Replies
View Related
Oct 21, 2013
I have to select rows from a table
if the first 2 characters of a 12 char column are
'GB'
Select BFKEYC from table where
I have a hokey way of doing it but it looks embarrassing:
BFKEYC GT 'GA9999999999'
AND BFKEYC LT 'GC'
View 8 Replies
View Related
Apr 7, 2015
I have a table in which each record has a initial date and a final date. I would like to create I query that gives me one row for each month between the initial date and the final date. It would be something like this:
Original:
Product|price|initial_date|final_date
A|20.50|2014-08-10|2014-10-01
B|50|2015-01-15|2015-02-20
Resulting view:
A|20.5|2014-08-01
A|20.5|2014-09-01
A|20.5|2014-10-01
B|50|2015-01-01
B|50|2015-02-01
I would like to do that, because these dates correspond to the time in which the products are in possession of sellers, so I would to use this resulting query to generate a pivot chart in Excel to illustrate the value of the goods that are with our sellers in each month.
Is it possible to do it? I think I could do that direct in VBA, but I think that maybe it would be faster if I could do it directly in SQL.
By the way, I am using MS SQL Server and SQL Server Manegement Studio 2012.
View 1 Replies
View Related
May 20, 2014
I have a table with PO#,Days_to_travel, and Days_warehouse fields. I take the distinct Days_in_warehouse values in the table and insert them into a temp table. I want a script that will insert all of the values in the Days_in_warehouse field from the temp table into the Days_in_warehouse_batch row in table 1 by PO# duplicating the PO records until all of the POs have a record per distinct value.
Example:
Temp table: (Contains only one field with all distinct values in table 1)
Days_in_warehouse
20
30
40
Table 1 :
PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20
2 5 30
3 7 40
Updated Table 1:
PO# Days_to_travel Days_in_warehouse Days_in_warehouse_batch
1 10 20 20
1 10 20 30
1 10 20 40
2 5 30 20
2 5 30 30
2 5 30 40
3 7 40 20
3 7 40 30
3 7 40 40
How can I update Table 1 to see desired results?
View 3 Replies
View Related
Oct 1, 2015
The objective is to identify orders where an order fee has been applied incorrectly. I have multiple orders per customer, my table contains an orderID and a customerID. Currently if the customer places additional orders before the previous orders have been closed/cancelled, then additional fees are being applied.
Let's say I'm comparing order #1 to order #2. I need to identify these rows where the following is true:-
The CustID is the same.
Order #2 has a more recent order date.
Order #2 has a FeeDate Before the CancelledDate of Order #1 (or Order #1 has no cancellation date).
So in the table the orderID:2835692 of CustID: 24643 has a valid order fee. But all the subsequently placed orders have fees which were applied before the first order was cancelled and so I want to update the FeeInvalid column with a 'Y'. The first fee will always be valid.
I think I understand why the code I am trying doesn't achieve the result I want but I can't figure out how to write it correctly. Below is one example of code I've tried and also code to create the table and insert some test data.
update t1
SET FeeInvalid = 'Y'
FROM MockData t1 Join MockData t2 on t1.CustID = t2.CustID
WHERE t1.CustID = t2.CustID
AND t2.OrderDate > t1.OrderDate
AND t2.FeeDate > t1.CancelledDate
CREATE TABLE [dbo].[MockData](
[OrderID] [float] NULL,
[code]....
View 4 Replies
View Related
Nov 30, 2007
I have a results table that was created from many different sources in SSIS. I have done calculations and created derived columns in it. I am trying to figure out if there is a way to remove duplicate rows from this table without first writing it to a temp sql table and then parsing through it to remove them.
each row has a like key in a column - I would like to remove like rows keeping specific columns in the resulting row based on the data in this key field.
Ideas?
Thanks,
Ad.
View 7 Replies
View Related
May 8, 2008
I have the following variables VehicleID, TransactDate, TransactTime, OdometerReading, TransactCity, TransactState.
VehicleID is the unique vehicle ID, OdometerReading is the Odometer Reading, and the others are information related to the transaction time and location of the fuel card (similar to a credit card).
The records will be first grouped and sorted by VehicleID, TransactDate, TransactTime and OdometerReading. Then all records where the Vehicle ID and TransactDate is same for consecutive rows, AND TransactCity or TransactState are different for consecutive rows should be printed.
I also would like to add two derived variables.
1. Miles will be a derived variable that is the difference between consecutive odometer readings for the same Vehicle ID.
2. TimeDiff will be the second derived variable that will categorize the time difference for a particular vehicle on the same day.
My report should look like:
VehID TrDt TrTime TimeDiff Odometer Miles TrCity TrState
1296 1/30/2008 08:22:42 0:00:00 18301 000 Omaha NE
1296 1/30/2008 15:22:46 7:00:04 18560 259 KEARNEY NE
Can someone please help me here?
Thanks,
Romakanta
View 1 Replies
View Related
Mar 25, 2002
I'm stuck. I have a table that I want to pull some info from that I don''t know how to.
There are two colomuns, one is the call_id column which is not unique and the other is the call_status column which again is not unique. The call_status column can have several values, they are ('1 NEW','3 3RD RESPONDED','7 3RD RESOLVED','6 PENDING','3 SEC RESPONDED','7 SEC RESOLVED').
i.e example, this is the existing data.
Call_id Call_Status
555555 3 3RD RESPONDED
235252 7 SEC RESOLVED
555555 7 3RD RESOLVED
325252 6 PENDING
555555 6 PENDING
325235 3 SEC RESPONDED
555555 1 NEW
This is the data I want...
Call_id Call_Status
555555 3 3RD RESPONDED
555555 6 PENDING
555555 7 3RD RESOLVED
The call_id could be any number, I only want the 6 PENDING rows where there are other rows for that call_id which have either 3 3RD RESPONDED or 7 3RD RESOLVED. If someone knows how it would be a great help.
Cheers,
Chris
View 1 Replies
View Related
Jul 25, 2007
Hi All,
I have the following Table
Type Name Value
x M1 5
x M2 10
x M3 20
y M1 10
y M2 15
y M3 30
Now, i need to add four more rows to the table
Type Name Value
x M1 5
x M2 10
x M3 20
y M1 10
y M2 15
y M3 35
z1 Total 15 (xM1+XM2)
z1 Diff 5 (xM3-xM1+XM2)
z2 Total 25 (yM1+yM2)
z2 Diff 10 (yM3-yM1+yM2)
Please help me.
Many Thanks,
View 2 Replies
View Related
Feb 3, 2008
I know this may have been asked before but can someone pls hel mw out here. i even tried to use the Crosstab SP that i found out on this site but it is not for what i need.
I have a Table/View called [Shipment] with the data below.
ShipNo Supplier
=================
1 CFA
1 TFA
2 LRA
2 LRB
3 ABC
4 TFA
I want the following as my result.
ShipNo Supplier1 Supplier2
==========================
1 CFA TFA
2 LRA LRB
3 AB
4 TFA
Thx.
Rav
View 6 Replies
View Related
Oct 4, 2006
I am a beginner at SQL so thanks ahead of time.....How do I get unique rows based on studentID? Distinct and group by don't seem to workDESIRED RESULTSStudentID First Name Last Name Other Columns...............................................634565491 MARINA BALDERAZ 640484566 TERE BALDERAZCURRENT SQL AND RESULTS.....SELECT ClassRosterRecID, StudentDataRecID, StudentDataKey, StudentID, FirstName, LastName, CurrentGrade, Gender, Ethnicity, EconDisadvantaged, TitleI, Migrant, LEP, Bilingual, ESL, SpecialEducation, GiftedTalented, AtRisk, CareerTech, Dyslexia, LastName + ', ' + FirstName AS LastNameFirstName, EconDisadvantagedSort, TitleISort, MigrantSort, LEPSort, BilingualSort, ESLSort, SpecialEducationSort, GiftedTalentedSort, AtRiskSort, CareerTechSort, DyslexiaSort, DistrictID, CampusID FROM vClassDemographicsDetail WHERE (DistrictID = '057910') AND (CampusID = '057910101') AND (LastName LIKE '%BALDERAZ%')StudentID First Name Last Name Other Columns...............................................634565491 MARINA BALDERAZ 634565491 MARINA BALDERAZ 634565491 MARINA BALDERAZ 640484566 TERE BALDERAZ640484566 TERE BALDERAZ640484566 TERE BALDERAZ
View 1 Replies
View Related
Jul 10, 2001
I am trying to create a stored proc that will update exactly one row. Simple. For insurance purposes, I want to create some logic that will rollback the entire transaction if more than one row is updated.
I know that I could force the primary key into the WHERE clause, but I was looking for some logic that will allow me to bypass that.
Thanks,
Tom
View 1 Replies
View Related
Jun 22, 2014
Currently I have the below style data:
Name, StartPostion, EndPosition, Height
Person1, 10, 15, 5
Person2, 14,14,0
Person3, 20,21,1
What I am looking to do is, run through my table of data and create a record for each Name for each Position it takes up. For example
For Person1 the data will look like,
Name, StartPosition, EndPosition, Heigh, Position
Person1, 10,15,5, 10
Person1, 10,15,5, 11
Person1, 10,15,5, 12
Person1, 10,15,5, 13
Person1, 10,15,5, 14
Person1, 10,15,5, 15
View 8 Replies
View Related
Jun 9, 2008
I'll show my schema first, then I'll explain what I'm doing:
--------------------------------------------------
Events
--------------------------------------------------
ID | E_Title
--------------------------------------------------
--------------------------------------------------
EventOptionGroups
--------------------------------------------------
ID | EOG_EventID | EOG_OptionGroupID
--------------------------------------------------
--------------------------------------------------
OptionGroups
--------------------------------------------------
ID | OG_Title
--------------------------------------------------
--------------------------------------------------
Options
--------------------------------------------------
ID | O_OptionGroupID | O_Description
--------------------------------------------------
--------------------------------------------------
EventRegistration
--------------------------------------------------
ID | ER_EventID | ER_Name
--------------------------------------------------
--------------------------------------------------
RegistrantOptions
--------------------------------------------------
ID | RO_EventRegistrationID | RO_OptionGroupID | RO_Selection
--------------------------------------------------
There are several events. Each event has several different sessions (stored in EventOptionGroups), and each session has a certain number of options (stored in Options).
A user can sign up for an event, and their information is stored in EventRegistration. They can choose an option for each session in the event. For each option they choose, a new row is added to RegistrantOptions.
For each row in EventRegistration, I want to output the user's information, and then the option they chose for each session in the event. Like this:
----------------------------------------------------------------------
E_Title | ER_Name | OG_Title1 | OG_Title2 | OG_Title3
----------------------------------------------------------------------
Event | Bob | O_Description1 | O_Description2 | O_Description3
So in that example, that event had 3 sessions.
Right now, I can only output E_Title and ER_Name, I don't know how to output the session information
View 2 Replies
View Related
Mar 17, 2015
Here's my current SQL:
SELECT
RN_TEST_ID AS 'Test ID',
MAX(RN_EXECUTION_DATE) AS 'Last Execution Date',
MAX(RN_EXECUTION_TIME) AS 'Execution Time',
RN_DURATION AS 'Run Duration'
FROM RUN
[code]....
Here's a sample of data returned:
Test IDLast Execution DateExecution TimeRun Duration
86722/9/2015 0:0012:08:16180
86822/9/2015 0:0011:29:06181
86842/9/2015 0:0008:29:17119
105252/3/2015 0:0019:03:4089
105252/3/2015 0:0019:10:13305
106682/3/2015 0:0018:55:43103
106682/6/2015 0:0018:10:50123
114572/3/2015 0:0011:40:0726
What I need are two things:
1. The query should only return one record for each test id
2. The record returned should be the most recent. By most recent I mean the RN_EXECUTION_DATE and RN_EXECUTION_TIME of the returned row should be the most recent in time.
For example, in the sample data there are multiple rows with the same test id (for example 10668 and 10525. The 10525 is even more problematic since its execution date is the same for both rows returned - the execution times differ. Again, I want one record per test id and that record should be the most recent in time.
View 6 Replies
View Related
Mar 13, 2006
Hello,
I am unable to figure out how to proceed after trying for more than a day. Should I add a parameter to the stored proc? How do I proceed?
I need to be able to show data for EdgeID 2,3,5,6,20,21 and so on...Right now I am showing data for 1, 4, 19 and so on based on the ReltTotID based on the result set below. This is because the table that the query below is selecting from adds up all common EdgeIDs to give one row for example
EdgeID Desc TermType ReltTotID
1Global Edge Model w/ Fwd Earn II T 1
2Short Term Global Edge Model w Fwd Earn IIS 1
3Long Term Global Edge Model w Fwd Earn IIL 1
4Emerging Market Edge Model w Fwd Earn T 4
5Short Term EM Edge Model w Fwd Earn S 4
6Long Term EM Edge Model w Fwd Earn L 4
19SmallCap Edge Model w/ Fwd Earn T 19
20SmallCap Short Term Edge Model w/ Fwd EarnS 19
21SmallCap Long Term Edge Model w/ Fwd EarnL 19
35Global+EM Edge Model w Fwd Earn T 35
The final query result is :
EdgeID Description Short Desc PerID UnivID DefID
1Global Edge Global Developed 500622355938
4Emerging Market Emerging Markets 500632356039
19SmallCap Edge Small Cap Edge 500642364244
I would like it to be :
1Global Edge Global Developed 500622355938
2Short Term Global Developed NULL2355938
3Long TermGlobal Developed NULL2355938
4Emerging Market Emerging Markets 500632356039
5Short Term Emerging Markets NULL2356039
6Long Term Emerging Markets NULL2356039
19SmallCap Edge Small Cap Edge 500642364244
19Short Term Small Cap Edge NULL2364244
19Long Term Small Cap Edge NULL2364244
The stored proc query is as below:
SELECT
EdgeModelID = em.EdgeModelID
--, EdgeModelID = em.EdgeModelID
, Description = m.Description
, ShortDescription = ISNULL(emdn.ParameterValue, m.ShortDescription)
, ViewPermissionID = emdp_perm.ParameterValue
, EdgeUniverseID = univ.UniverseID
, EdgeDefinitionID = univ.MemberID
FROM OptMod..GO_EdgeModels em
JOIN OptMod..GO_Models m
ON em.EdgeModelID = m.ModelID
AND m.ModelType = 'E'
AND Status = 1
JOIN OptMod..GO_EdgeModelDisplayParameters emdp
ON emdp.EdgeModelID = em.EdgeModelID
AND emdp.ParameterName = 'NewEdge32 Screening'
LEFT JOIN OptMod..GO_EdgeModelDisplayParameters emdn
ON emdn.EdgeModelID = em.EdgeModelID
AND emdn.ParameterName = 'NewEdge32 Display Name'
LEFT JOIN OptMod..GO_ModelUniverses mu
ON em.EdgeModelID = mu.ModelID
LEFT JOIN OptMod..vUniverses univ
ON mu.UniverseID = univ.UniverseID
LEFT JOIN OptMod..GO_EdgeModelDisplayParameters emdp_perm
ON emdp_perm.EdgeModelID = em.EdgeModelID
AND emdp_perm.ParameterName = 'NewEdge32 Permissions'
WHERE em.EdgeModelID = em.RelatedTotalEdgeModelID
Thanks in advance!!!
sqlnovice123
View 1 Replies
View Related
Aug 20, 2007
Hi,
I have two tables : Students and StuHistory. The structure of the Student table is as follows :
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Student]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Student](
[RID] [int] NOT NULL,
[Class] [int] NULL,
[Section] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[SubSection] [int] NULL,
[RollNo] [int] NULL,
[DesiredRoll] [int] NULL,
[TrackingNo] [int] NULL,
[Original_rollno] [int] NULL,
[StudentStatus] [int] NULL
)
END
GO
A section has subsections where students are allocated rollno's. Every student has a unique roll no in that subsection. However he is also given a choice to enter his desired roll no. If more than one student choose the same desired roll no in that subsection/section, there is a [TrackingNo] field that then starts keeping a count. For the first unique desired roll no in that subsection/section the tracking no is always 0.
[StudentStatus] represents the following : (-1 for deleted, 0 for edited, 1 for newly inserted).
After every fortnight, i have to run a batchquery that does the following:
1. all students marked with -1 are moved to a table called StuHistory which has the same structure as that of Student.
2. Now oncethe -1 status students are moved, there will be a gap in the roll no. I want to reallocate the rollnos now, where rollnos = desired roll no taking into consideration the trackingno
So if 4 students have chosen the desired roll no as 5 and their current roll no is scattered in a subsection lets say 7, 10, 14,16, then while rearranging they will be together(grouped by subsection/section) and will be allocated roll no's 5,6,7,8. The other students will be moved down based on their desired roll nos. Over here i have to also fill the gaps caused because of the students who were deleted.
How do i write query for this? I have been struggling.
I thought of posting this as a new post as it was mixed in the previous post.
Script :
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (1, 1, N'A', 1, 1, 1, 0, 0, 1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (2, 1, N'A', 1, 2, 2, 0, 0, 1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (3, 1, N'A', 1, 3, 1, 1,0,1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (4, 1, N'A', 12, 1, 1, 0,-1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (5, 1, N'A', 12, 2, 1, 1, 0, 1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (6, 1, N'A', 12, 3, 2, 0, 0, 1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (7, 1, N'B', 5, 1, 3, 0, 0, 1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (8, 1, N'B', 5, 2, 3, 1, 0 ,1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (9, 1, N'B', 5, 3, 3, 2, 0, 1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (10, 1, N'B', 5, 4, 2, 0, 0, 1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (11, 1, N'B', 5, 5, 2, 1, 0, 1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (12, 1, N'B', 10, 1, 1, 0, 0, 1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (13, 1, N'B', 10, 2, 1, 1, 0, 1 )
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (14, 1, N'B', 10, 3, 1, 2, 0, -1)
INSERT [dbo].[Student] ([RID], [Class], [Section], [SubSection], [RollNo], [DesiredRoll], [TrackingNo], [Original_rollno], [StudentStatus] )
VALUES (15, 1, N'B', 10, 4, 2, 0, 0, 1)
Thanks.
View 22 Replies
View Related
May 24, 2006
Hello!
I have a table that, among other columns, has two datetime columns which indicate the initial and the final time. This would be an exemple of data in this table:
row1:
initial_time: 2006-05-24 8:00:00
final_time: 2006-05-24 8:30:00
row2:
initial_time: 2006-05-24 8:35:00
final_time: 2006-05-24 9:15:00
I would like to split a row in two new rows if final time's hour is different of initial time's hour, so I would like to split row2 into:
row2_a:
initial_time: 2006-05-24 8:35:00
initial_time: 2006-05-24 8:59:59
row2_b:
initial_time: 2006-05-24 9:00:00
initial_time: 2006-05-24 9:15:00
Is it possible to do it in a query, I mean, without using procedures?
Thank you!
View 3 Replies
View Related
Mar 18, 2007
Hi there, im still learning SQL so thanks in advance.I have a table with columns of customer's information, [customerID], [customerFirst], [customerLast], , [program] ... other columns ... There will be entries where there can be duplicate customerFirst and customerLast names. I would like to just return a single entry of the duplicate names and all associated row information. IE: [customerID], [customerFirst], [customerLast], [ email], [program] 01 Bill Smith bill.smith@hotmail.com ymca 02 Bill Smith bill.smith@hotmail.com Sports 03 jon doe jon.doe@hotmail.com AAA 04 jon doe jon.doe@hotmail.com Ebay 05 Paul Sprite paul.sprite@hotmail.com Rec Desired Returned result: 01 Bill Smith bill.smith@hotmail.com ymca 03 jon doe jon.doe@hotmail.com AAA
05 Paul Sprite paul.sprite@hotmail.com Rec So in my code i have this:dAdapter = new SqlDataAdapter("SELECT * FROM [Poc_" + suffix + "] WHERE (SELECT DISTINCT [CustomerLastName], [CustomerFirstName], [CustomerEmail] FROM [Poc_" + suffix + "])", cnStr); dAdapter.Fill(pocDS, "Data Set"); However this is throwing up an error when i build the app: An expression of non-boolean type specified in a context where a condition is expected, near ')'.
Description: An
unhandled exception occurred during the execution of the current web
request. Please review the stack trace for more information about the
error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException:
An expression of non-boolean type specified in a context where a
condition is expected, near ')'.
Source Error:
Line 52: //dAdapter = new SqlDataAdapter("SELECT DISTINCT * FROM [Poc_" + suffix + "] ORDER BY [CustomerLastName]", cnStr); Line 53: dAdapter = new SqlDataAdapter("SELECT * FROM [Poc_" + suffix + "] WHERE (SELECT DISTINCT [CustomerLastName], [CustomerFirstName], [CustomerEmail] FROM [Poc_" + suffix + "])", cnStr); Line 54: dAdapter.Fill(pocDS, "Data Set");Line 55: Line 56: //Dataset for name comparison 1: Can someone explain to me why this error is happening?2: Can soemone confirm that my intentions are correct with my code?3: If I'm completely off, can someone steer me in the right direction?Thanks alot!-Terry
View 12 Replies
View Related
Mar 21, 2006
Hello again,
Another combining multiple rows teaser, during a few routines I made a mistake and I would like to combine my efforts. Here is my data:
Code:
Table A
ID DSN VN AX Diag
1111296.54
3212318.00
Both DSNs share the same Patient_id in a seperate table which holds the DSN numbers and their corresponding patients.
Code:
Table B
DSN Patient_id
100000001
200000001
So what I need to do is maintain their unique 'ID' number in Table A but update their DSN numbers to reflect the first instance in Table B. So my data would look like this in both tables.
Code:
Table A
ID DSN VN AX Diag
1111296.54
3112318.00
Note: The second rows DSN changed to 1 from 2
Code:
Table B
DSN Patient_id
100000001
(Duplicate row removed with same patient_id)
The result would look like the above but as you noticed I need to remove the duplicate row that had the different DSN in Table B so that only one DSN remains that can map to multiple rows (IDs) in Table A.
Table A:
DSN can map to multiple rows (IDs)
IDs must be unique (aka kept to what they are currently)
Table B:
Second row with same DSN must be removed.
Any takes, ideas? I need to do this on a couple thousand rows....
Thanks, and im happy to clarify if needed.
View 1 Replies
View Related
Sep 6, 2011
I have a table with two columns refid and name and it has the following values
1 tom
1 jim
2 bob
1 bob
I need a resultset that would have the following values
1 tom, jim, bob
2 bob
I have tried couple of things one being:
DECLARE @namelist VARCHAR(1000)
SELECT @namelist = COALESCE(@namelist +', ' ,'') + name FROM sales where refid = 1
SELECT @namelist
But I am looking for a resultset with a unique refid and all the names comma separated for that refid.
View 2 Replies
View Related
Apr 15, 2004
I have a table that contains rows that I would like to delete based on a field and it's contents.
What is the correct syntax to script the removal of these rows based field parameter?
View 7 Replies
View Related
Mar 6, 2014
I have two columns which needs to repeat based on ID and number of distinct rows in that ID.
ID Date Created
1 1/1/2012 Sudheer
1 1/2/2013 Sudheer
1 3/3/2013 Sudheer
2 1/2/2014 Veera
2 2/5/2015 Veera
Results
ID Date Created Date Created Date Created
1 1/1/2012 Sudh 1/2/2013 Sudh 3/3/2013 Sudh
2 1/2/2014 Veera 2/5/2015 Veera
View 3 Replies
View Related
Sep 3, 2014
I am working on a report and the data source is Teradata. now I have situation where I want to get order id details based on the current quarter and year I am posting this same data. For TD related queries I do not where to post.
ACCT_ID ACCT_NMORD_NBRORD_DT ORD_AMT_USD
595709114ASDASD444447/28/2014 546
2224809440ASDASD444445/2/2012 546
1724031572ASDASD444446/22/2011 546
1702887651ASDASD444447/3/2014 546
1724020508ASDASD444447/16/2012 546
1148151895ASDASD444449/18/2013 546
2125154824ASDASD444449/2/2014 546
1503552723ASDASD4444412/20/2011 546
2224689808ASDASD4444410/4/2010 546
931387698ASDASD4444412/31/2010 546
View 4 Replies
View Related
Aug 16, 2014
I need to get 3 rows per set based on a date given (must be this date) but I want the rows to be based on this date as...
1 ( row where the date from the date column is the next date after given date )
0 ( row where the date is the closest date prior to the date given )
-1 ( prior to the date at 0 )
And add a column with the relative number.
** The dates for the same name and item will never repeat.
For example, a set of rows...
Row ID, Name, Item, Number, Date
1 Andy, Item1, 12030, 2014-06-30
2 Andy, Item1, 62030, 2014-03-31
3 Andy, Item1, 30300, 2013-12-31
4 Andy, Item1, 40030, 2013-10-31
5 Andy, Item1, 50030, 2013-08-30
6 John, Item2, 50240, 2014-04-30
7 John, Item2, 41400, 2014-03-31
8 John, Item2, 40509, 2014-01-31
9 Andy, Item2, 24004, 2014-03-31
10 Andy, Item2, 20144, 2013-12-31
11 Andy, Item2, 20450, 2013-09-30
12 Andy, Item2, 25515, 2013-06-30
If I have 2014-03-15 as the date and search for 'Andy', I expect...
Row ID, Item, Date, Relative Date
2, Item1, 2014-03-31, 1
3, Item1, 2013-12-31, 0
4, Item1, 2013-10-31, -1
9, Item2, 2014-03-31, 1
10, Item2, 2013-12-31, 0
11, Item2, 2013-09-30, -1
This is what I'm using which I have no issues switching if necessary...
DATEDIFF( quarter, 2014-03-31, date )
date BETWEEN DATEADD( quarter, -1, '20140315' ) AND
DATEADD( day, 1 ( DATEADD ( quarter, 2, '20140315' ) )
which returns...
Row ID, Item, Date, Relative Date
2, Item1, 2014-06-30, 1
3, Item1, 2014-03-31, 0
4, Item1, 2013-12-31, -1
9, Item2, 2014-03-31, 0
10, Item2, 2013-12-31, -1
Not sure if date math is the best option here. Perhaps using row_number() in some way?
I'm trying to avoid having to process the entire table programmatically given the size of the data set.
View 8 Replies
View Related