MS SQL Server :: Query Detail As A Field.

SQL Master/Detail (Master Query Based On Detail Values)

Mar 25, 2008

Hello,

I'm new to SQL and need help with a query. Not sure if this is the right place.

I have 2 tables, one MASTER and one DETAIL.

The MASTER table has a masterID, name and the DETAIL table has a detailID, masterID, and value columns.

I want to return a populated MASTER table with entries based on the DETAIL.value.

SELECT MASTER.*
FROM MASTER
WHERE DETAIL.value > 3

This is a simplified version of my problem. I can't figure out how to set the relationship between MASTER.masterID and DETAIL.masterID. If I do an INNER JOIN, the number of results are based on the number of DETAIL entries. I only want one entry per MASTER entry.

Hope this makes sense.

How can I do this?

GrkEngineer

View 9 Replies View Related

Selecting Detail Based On A Sum Of The Detail Lines

Sep 14, 2007

I am listing detail transaction lines in a table sorted by account and order number.
the problem is that I only want to see the detail if the sum of a value field is zero for all the transactions in an order otherwise ignore the detail for that order.

I was trying Group by and Having but this doesn't seem to do what I need.

Being relatively new to Reporting services, any nudge in the right direction would be useful.

View 4 Replies View Related

Query That Accumulate Detail Values

May 24, 2008

How would be a query that read detailed sales from several dates and accumulated them in a table that has months as fields

MSSQL 2005

Table : DETAIL
Customer_Date___________Value
1________01-Jan-2008____1,234.00
2________01-Feb-2008____3,456.00
3________01-Mar-2008______500.00
4________01-Apr-2008____1,567.00
1________01-Apr-2008____2,123.00

Table : TOTAL
Year__Customer_Jan______Feb______Mar_____Apr ...
2008__1________1,234.00_0________0_______2,123.00
2008__2________0________3,456.00_0_______0
2008__3________0________0________500.00__0
2008__4________0________0________0_______1,567.00

Thanks for your help

JG

View 5 Replies View Related

How To Query For Master-detail Output On Same Page

Jul 27, 2007

I'd like to create a master-detail output from SQL2005 to display in a page using classic ASP. Ideally, I want the output to contain a <div> to show/hide the order details beneathe the order header, as shown below.

OrderNo OrderDate
1001 7/27/2007
+ <div to show/hide>
Item Description
AAA desc_for_itemAAA
BBB desc_for_itemBBB
+ </div>

1002 7/26/2007
+ <div to show/hide>
Item Description
CCC desc_for_itemCCC
+ </div>

I currently just have a stored procedure returning the results and it is very slow as I'm simply querying the details section for each order header. Does anyone have any ideas on how I can create an efficient and fast method for returning these results in the format above?

View 5 Replies View Related

Tricky Query For Joing Master-detail Tables

Feb 28, 2002

I need to write a sql query which is a master-detail query. Here's the example structure of tables:

Master table:
ColID as longint, ColA as int, ColB as int, ColPartID as longint, ColPartName as longint

Child table -- Wheel:
ColID as longint, ColA as int, ColB as int
Child table -- Door:
ColID as longint, ColA as int, ColB as int
Child table -- Window:
ColID as longint, ColA as int, ColB as int
..... etc

From the master table, it needs to join with its child in order to get the detailed information. However, there're more than one child table for it to join. In other words, the query has to choose the correct child table to join for each row selectively. The use of correct child depends on one of the columns in its master table (ColPartName).

My question is: Does it worth of me keep finding a solution for this query or should I abandon this? I really need some advice, please.

Many thanks,
Leonard

View 1 Replies View Related

Sql Query Summerize The Detail Table In Order To Convert Its Trans_type Into Column

Jan 15, 2008

I have detail table like

date item_id grade in out
------------- --------------- ------- -------- ----------
01-01-08 001 A 10 0
02-01-08 001 O 8 0
01-02-08 002 O 1 0
03-01-08 001 T 0 10
02-01-08 003 O 20 0
02-01-08 003 T 0 10
02-01-08 003 B 0 8

Result View
=======

Item_id A B O T Total

001 10 0 8 -10 8
002 0 0 1 0 1
003 0 8 20 -10 18

where grade could be any Alphabet, the column of result query could varies
each grade contain sum of (in-out) of all item_id of detail table.

Smartsys

View 4 Replies View Related

Getting Job Detail

Aug 15, 2001

I am trying to create a function that will run when a job step fails. I want it to dynamically capture the name of the job and insert it into a table that I will use to send email alerts for. The reason I need to do this as opposed to sending an email from the job is that if the alert is not responded to after so many minutes it can be escalated to the next operator. Does anyone know how to dynamically capture the name of the current job being run.

thanks

Tom

View 1 Replies View Related

Process Detail (T-SQL)

Oct 30, 2006

Hello there :-)

Just want know where can we find the last T-SQL being executed on a process. This is the one that pops-up when we double-clik a process on the Process Info List. Thank You in Advance :-)

View 1 Replies View Related

Invoice Detail

Jan 4, 2007

Hi

I have a invoice that displays the company name in a rectangle on top then it has a list in the list it has details tables my problem is that the invoice detail goes on the next page I need to have the company name as well on the second page I cant put it in the same table as the detail because I have a few tables in the detail so it is actually no header is it possible to have Repeated the Company name information on the next page?

Thanks

View 4 Replies View Related

Master With Two Detail Views

Jan 7, 2007

Hello community,I think my problem is easy to solve even though I did not find a solution through different tutorials and help pages. Here it is (select statements are hier simplified):In the gridview "GridView1" I have a master record with person_id, which is the data-key-value. There is also another id-field named task_id (This record comes from a database view which joins the persons- and the tasks- table) SelectCommand="SELECT [id], [person_id], [task_id] FROM [ViewPersonTasks] WHERE ([id] =
@id)"For both fields I want to display details in two different detail-views. One for the person (depending on person_id) and one for the tasks (depending on the task_id).The first one is easy. I declare a details-view for the person data based on a SqlDataSource with a control-parameter like this: SelectCommand="SELECT [person_id], [first_name], [last_name], [birth_date] FROM [TabPersons] WHERE ([person_id] = @person_id)" .... <SelectParameters> <asp:ControlParameter ControlID="GridView1" Name="person_id" PropertyName="SelectedValue" Type="Int32" /> </SelectParameters>But now the problem: how should I declare a parameter @task_id for the task_id, so that the second select statement for the tasks-details-view retrievs the data for the tasks: SelectCommand="SELECT [task_id], [task_name],
[task_date], [task_description] FROM [TabTasks] WHERE ([task_id] =
@task_id)"@task_id should have the value from the task_id-field of the master record, displayd in the master grid-view.Thank you in advance for your help

View 1 Replies View Related

Header And Detail Records From DTS

Apr 16, 2004

I am creating a DTS package to export a text file. My question is: does anyone have any ideas on how for one read of the tables I can produce 2 lines of output. Here is how the file layout needs to be...

HEADER
DETAIL
HEADER
DETAIL
HEADER
DETAIL

I am a little confused about how I can stagger header and detail using the same data.

I appreciate any help you can give. Hopefully my explanation of the problem is understandable.

Thanks, Val

View 2 Replies View Related

Count From Detail Table

Feb 20, 2008

HiI've two tablesTableAidnameTableBidtableA_iddescoperAs you can see tableA is a master and tableB is a detail table wherewe can have many records for each related tableA record.I need to get all records for tableA with a count on some oper oftableB.I suppose I can got it with a join or a subselect but I don't use SQLoften so I'm getting crazy with this stupid query...Could somebody help ?Thanks in advanceC

View 2 Replies View Related

Get One Row From Detail/child Table

Jul 20, 2005

Hi,-- ddl/*create table #tmp (col1 int);insert into #tmp values(1);insert into #tmp values(2);insert into #tmp values(3);create table #tmpChild (col1 int, fkCol int, Num int);insert into #tmpChild values(1,1,3);insert into #tmpChild values(2,1,2);insert into #tmpChild values(3,2,1);*/-- get parent and child data (outer join)select *from #tmp t Left Outer JOIN #tmpChild tCON t.col1 = tC.fkCol-- resultset/*/col1 col1 fkCol Num----------- ----------- ----------- -----------1 1 1 31 2 1 22 3 2 13 NULL NULL NULL(4 row(s) affected)*/-- desired resultset/*/col1 col1 fkCol Num----------- ----------- ----------- -----------1 1 1 3-- eleminate next row, want only Max(Num) row from the child tbl withsame FK (parentID)-- 1 2 1 22 3 2 13 NULL NULL NULL*/In other words, desired results is as follows:/*/col1 col1 fkCol Num----------- ----------- ----------- -----------1 1 1 32 3 2 13 NULL NULL NULL*/How to accomplish this task? ENV: MS SQL Server 2000TIA

View 1 Replies View Related

Detail And Summary Sub Reports

Jan 10, 2008

Hello,

I am trying to create two subreports in the main report. One sub report should give detail data and other sub report shuold give sumamry data. Is detail and summary reports are possible in sub reports? Iappreciate your help on this.

Thanks,

View 13 Replies View Related

Master-&&>Detail Reporting

Aug 14, 2007

I'd like to put together a summary report for some of my management staff. The report should show the # of jobs that match about 20 different statuses. Each status has different criteria. For example, one might look for a date in a datetime field and the type of job. Another might look at whether a date has passed and the quantity we're shipping.

Sort of like this:
Status Items Jobs
Status #1: 50000 15
Status #2: 25251 3

I want the user to see this summary information, but also have drill down capability. I'd like the ability to print the summary or just a drill-down.

I'm also considering using Dundas Charts for RS on the report.

Can I accomplish this with Reporting Services? If so, any tips on how to do it? If this a bunch of sub-reports? Can I sum the # of jobs on a sub-report? Should I be looking at BI for this?

Or should I be working on a Forms-based?

Thanks!
Brian

View 7 Replies View Related

Charting Table Detail And Sum

Dec 13, 2005

I have a bar chart that displays the detail of a table with locations along the x-axis and number of employees on the y-axis. Is there any way to have the sum of all of the locations show as the final bar in the chart? (which would be the footer in the table)

View 3 Replies View Related

Detail Record Header

Mar 29, 2007

Hi,

I'm new to SSRS. I was just wondering how do I make the header for a detail record appear once per grouping rather than once per detail record?

Thanks.

View 1 Replies View Related

Transaction Log Detail Viewing

Jan 8, 2008

I have a client who has a database that they have recently moved to another server. The problem is that the transaction log of the previous version of the database is taking over 21GB of space on the old server. All of the transactions are listed with a status of 2 in the log file. Thus I can not simply shrink the file.

Is there a utility I can use to view the details of the records in the transaction log? I would also need to ensure the data is committed to the database and then change the status so that the transaction log can be shrunk.

This older database will be kept for historical reasons. During the change to the other server only the structure was moved so the data is not in that database.

View 1 Replies View Related

Linking Two Tables In A Detail View

Apr 10, 2007

I have a province table in a my database. I would like to link this province table to a resource table's Province_ID. This Province_ID is an int.
Vic Valentic
CEO/President
Open Door
2 Elite Dr. #33
Hamilton, Ontario
L8W 2N3
905-389-7492
http://www.wlu.ca/next/opendoor

View 6 Replies View Related

Obtaining Detail Of DTS Package Contents

Dec 17, 2002

I have quite a few DTS's to work through while analysing current SQL databases for a client. Problem is I can't seem to find a way in SQL (Enterprise Manager) to print the contents of a DTS package. This is quite frustrating in that I have to go an "Design" the package, then click on the properties for each step and copy and paste the contents into Notepad/Wordpad. Surely Microsoft have something to ease the pain?

If anyone can help me on this one, I would be extremely grateful.

View 4 Replies View Related

Tranact SQL : Get Total Of Detail Table

Jan 28, 1999

Hi,

I have a master and a detail table as defined :

Master : tmpOrder

Column_name Type
------------------------------ ------------------------------
OrderID int
Date DateTime

Detail : tmpOrderItem

Column_name Type
------------------------------ ------------------------------
OrderID int
Seq int
Qty float
Amount float

How can I get the master fields and sum of qty and amount in one query. I need the following result set

OrderID Date TotalQty TotalAmount
-------------- ---------- ---------- --------------

Thank you in advance
Allex

View 1 Replies View Related

How To Copy Detail Records To Another Header

Jun 4, 2004

Im trying to copy details from a specific header as details of a different header (eg. all sales items from invoice #10 copied as sales items of invoice #11).

So far I have two stored procedures:
1) sp_copyDetailsOne
/*Create a recordset of the desired items to be copied*/
CREATE PROCEDURE sp_copyDetailsOne @invoiceIdFrom INT, @outCrsr CURSOR VARYING OUTPUT AS
SELECT itemId, itemPrice, itemDescription, itemQuantity
FROM tblSalesItems
WHERE (invoiceId = @invoiceIdFrom)
OPEN @outCrsr

2) sp_copyDetailsTwo
CREATE PROCEDURE sp_copyDetailsTwo @invoiceIdFrom INT, @invoiceIdTo INT
/*Allocate a cursor variable*/
DECLARE @crsrVar CURSOR

/*Execute sp_copyDetailsOne to instantiate @crsrVar*/
EXEC sp_copyDetailsOne @invoiceIdFrom, @outCrsr = @crsrVar OUTPUT

/*Loop through the recordset and insert a new record using the new invoiceId*/
FETCH NEXT FROM @crsrVar
WHILE @@FETCH_STATUS = 0
BEGIN

/*Run the insert here*/
INSERT INTO tblSalesItems
(invoiceId, itemId, itemPrice, itemDescription, itemQuantity)
VALUES (@invoiceIdTo , 5, $25.00, N'Black T-Shirt', 30)

/*Fetch next record from cursor*/
FETCH NEXT FROM @crsrVar
END

CLOSE @crsrVar
DEALLOCATE @crsrVar

My question comes on the Insert of sp_copyDetailsTwo, as you can see the values are hard coded and I want them pulled from the cursor. However I don't know how to do this - do I need varables or can I access the cursor values directly in my VALUES clause? Or is this whole approach needing overhauled. Any advice is welcome.

Thanks

View 2 Replies View Related

Simultaneous Entry In Both Master & Detail

Apr 7, 2008

Hi, can anyone help me? I want to know whether is it possible to insert records simultaneously in both master & detail tables?
For elaboration, say there is a master table contains (orderid,orderdate,amount) and details table contains (orderid, productid,qty,price). 1 record of master table associated with n records of details table. Can it be possible to insert both the 1 record at master table with n records in details table in a single sql statement?

View 3 Replies View Related

Combining Detail Records From Different Tables

Sep 22, 2005

Following is a stored procedure that currently runs on the system (compacted version). I need to combine this data with data from another Table .. tblAdjustments. The schema for this table is fairly close to tblShipmentDet.

tblShipmentHdr --> tblShipmentDet (Key = ShipmentID)
tblAdjustments --> standalone

Result: combine tblShipmentHdr + attached tblShipmentDet records with
tblAdjustments records.

Would the best approach be to use a UNION SELECT?

@XToDate datetime = '7/31/2005' ,@XBegDate datetime = '7/1/2005'
AS
SELECT
SHPH.ProductID,
SHPH.ReceivedDate,
SHPH.ShipmentNo,
SHPD.Vendor,
SHPD.Quantity,
QRecvdDate = CASE WHEN SHPH.ReceivedDate < convert(varchar(40),@XBegDate,121)
THEN NULL ELSE SHPH.ReceivedDate
END,
QShipQty = CASE WHEN SHPD.TransCd = 'F'
THEN NULL
WHEN SHPH.ReceivedDate < convert(varchar(40),@XBegDate,121)
THEN NULL
ELSE SHPH.ShippingQty
END,
PROD.ProductName,
QOpenAccrual = CASE WHEN MEND.OpeningAccrual is Null
THEN 0 ELSE MEND.OpeningAccrual
END
FROM dbo.tblShipmentHdr SHPH
LEFT OUTER JOIN dbo.tblProducts as PROD ON Left(SHPH.ProductID,7) = Left(PROD.ProductID,7)
LEFT OUTER JOIN dbo.tblShipmentDet as SHPD ON SHPH.ShipmentID = SHPD.ShipmentID
LEFT OUTER JOIN dbo.tblMonthend as MEND ON SHPH.ProductID = MEND.ProductID And MEND.MEPeriod = convert(varchar(40),@XBegDate,121)
WHERE ((SHPH.ReceivedDate >= '7/1/2005' AND SHPH.ReceivedDate <= '7/31/2005') OR (SHPD.DatePaid >= '7/1/2005' AND SHPD.DatePaid <= '7/31/2005'))

View 1 Replies View Related

How Can I Sum By Group Total Instead Of By Each Detail Line

May 16, 2008

I hope someone can help with this problem. I'm new to SSRS 2005, having used Crystal Reports for several years

Our General Ledger Accounts consist of Dept Project AccountCode Each section is 4 digits.
22000000XXXX

I have the following sections in the report table:

Detail -- amount per transaction per account

Detail group -- sum of the transactions per account

Dept Project group ��sum of all the transactions for the Dept Project

DATE AMOUNT Budget
Detail 222100001234 4/5/2008 $100 $20,000
222100001234 4/10/2008 $200 $20,000

Detail group 222100001234 $300 $20,000

Detail 222100005678 4/12/2008 $400 $40,000
222100005678 4/11/2008 $500 $40,000

Detail group 222100005678 $900 $40,000

DeptProjectGroup 22210000 $1200 $60,000

As shown above, I sum the amounts per account in the detail group, and the amounts per DeptProject in the DeptProject Group. This works fine for the amounts.

I also placed the budget amount per account in the detail group section. This works also. But when I try to total the budget amount per DeptProjectGroup, I get the total for each transaction instead. In Crystal I would have it total by group, but I don't know how to do that in Reporting Services.

Any help would be much appreciated.

Thanks,

Sue

View 6 Replies View Related

Master/Detail Table Insertion.

Sep 20, 2007

Hi Experts,

I need to know the best approach to save data in master table and then in detail table.
I know this method but i know it's not a good approach why i will explain

Insertion in Master Table..................................... A

Insertion in Detail Table........................................B

Now if there is any exception occurred while step A then the step B will not take place which is ok but if there is exception while step B then the process A will have completed
i.e the data in master table will be Inserted/Deleted/Updated but there will not be a corresponding action in Detail table which is not good approach.

So please can any one tell me a good approach for this.

View 6 Replies View Related

Roll Up Filtered Detail Group

Apr 4, 2007

I the following report with one group:

Month Number of Sales Total Sales

+ Jan 2007 100 $1,000.00

When you dril down on MonthYear you get the detail data:

Month Number of Sales Total Sales

- Jan 2007 10 $610.00

1 $10.00

1 $20.00

1 $30.00

1 $40.00

1 $50.00

1 $60.00

1 $70.00

1 $80.00

1 $100.00

1 $150.00

My question is. I added a filter to the detail data to give the Bottom % =75 of sales. So My detail data only displays the following rows:

Month Number of Sales Total Sales

- Jan 2007 10 $610.00

1 $10.00

1 $20.00

1 $30.00

1 $40.00

1 $50.00

My problem is the group still displays the total of my dataset (as seen above), but I want it to display the total of the detail data group, like below:

Month Number of Sales Total Sales

- Jan 2007 5 $150.00

1 $10.00

1 $20.00

1 $30.00

1 $40.00

1 $50.00

If I change the fields in the group to look at the detail data ,for instance =count(Fields!NumberofSales.Value,"Details_Group") I get an scope error.

How can I display the totals of the detail data in the parent group after I added a filter to the detail data?

View 6 Replies View Related

Images Displayed In A Detail Record

Dec 13, 2007

I have a problem with a report, and I am running out of ideas on how to approach it. I have a report with groupings of data displayed, and once per group I need to display an image. When I place the image in a record, it shrinks it down to the height of the record and repeats the image for each record despite what formulas I put in the hidden property.

View 1 Replies View Related

Export Master-Detail Records

Nov 30, 2007

I need to export data to a text file in the following format:

Master Record
Detail Record Detail Record
Detail Record
Master Record
Detail Record
...

Example would be:

Master Record Format:
----------------------------------------------------------------------
RecordType|FirstName|LastName

Detail Record Format:
----------------------------------------------------------------------
RecordType|Order#|OrderedItem

Sample Data:

M|Micheal|Smith
D|123|1-123-1
D|123|1-123-2
M|John|Smith
D|142|1-444-1
D|142|1-444-3

Could someone direct me how I can acomlish this task using SSIS?

Thanks -

View 8 Replies View Related

Can An Image Be Shown In Matrix Detail?

Dec 31, 2007

I have a matrix based report running, it shows a pivoted statistical value in the detail cells. Now I'd like 1 of 2 images to show instead, in those cells, based on the value of the statistic. Can this be done in a matrix?

View 1 Replies View Related

Transaction Processing Detail Records

Apr 30, 2007

Hello,

I am fairly new to SQL Server 2005 and was curious if this was possible.

In my VB applications I always used Begin Transaction, Commit and Rollback to process records. I just found out that I can perform the same functionality in a stored procedure.

So the question is, if I have an order record and four line item records is there anyway to pass all that to the stored procedure as a unit. I can pass the order record as individual parameters but it is the four (or however many) detail records that is the reason for my question. How can I pass the detail records at one time? Can I pass these as an array or a dataset or something else or am I just out of luck? SQL Server 2005 has impressed me a few times already with what you can do and I am really hoping this can be accomplished also.

Cheers,

Richard

View 3 Replies View Related

SSIS Header And Detail To A File

Feb 19, 2008

View 3 Replies View Related