MS SQL Server :: Grouping Select Query

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

Nov 26, 2007

I'm really stumped on this one. I'm a self taught SQL guy, so there is probobly something I'm overlooking.

I'm trying to get information like this in to a report:

WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Detail #etc
WO#
-WO Line #
--(Details)
--Work Order Line Detail #1
--Work Order Line Detail #2
--Work Order Line Detail #3
--Work Order Line Detail #etc
--(Parts)
--Work Order Line Parts #1
--Work Order Line Parts #2
--Work Order Line Parts #etc

I'm unable to get the grouping right on this. Since the line details and line parts both are children of the line #, how do you do "parallel groups"?

There are 4 tables:

Work Order Header
Work Order Line
Work Order Line Details
Work Order Line Requisitions

The Header has a unique PK.
The Line uses the Header and a Line # as foreign keys that together are unique.
The Detail and requisition tables use the header and line #'s in addition to their own line number foreign keys. My queries ends up looking like this:

WO WOL WOLR WOLD
226952 10000 10000 10000
226952 10000 10000 20000
226952 10000 10000 30000
226952 10000 10000 40000
226952 10000 20000 10000
226952 10000 20000 20000
226952 10000 20000 30000
226952 10000 20000 40000
399999 10000 NULL 10000
375654 10000 10000 NULL
etc

Hierarchy:
WO > WOL > WOLD
WO > WOL > WOLR

It probobly isn't best practice, but I'm kinda new so I need some guidance. I'd really appreciate any help! Here's my query:

SELECT [Work Order Header].No_ AS WO_No, [Work Order Line].[Line No_] AS WOL_No,
[Work Order Requisition].[Line No_] AS WOLR_No, [Work Order Line Detail].[Line No_] AS WOLD_No
FROM [Work Order Header] LEFT OUTER JOIN
[Work Order Line] ON [Work Order Header].No_ = [Work Order Line].[Work Order No_] LEFT OUTER JOIN
[Work Order Line Detail] ON [Work Order Line].[Work Order No_] = [Work Order Line Detail].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Line Detail].[Work Order Line No_] LEFT OUTER JOIN
[Work Order Requisition] ON [Work Order Line].[Work Order No_] = [Work Order Requisition].[Work Order No_] AND
[Work Order Line].[Line No_] = [Work Order Requisition].[Work Order Line No_]

View 1 Replies View Related

SELECT Grouping

Sep 16, 2007

If I have a table which as the following columns/data

item boxes quantity
---- ----- --------
CH 20 16
CH 10 20
CH 15 16

how can I get all the CH items so that they are grouped by quantity and the boxes are totalled to give:

CH 35 16
10 20

please?

View 1 Replies View Related

Grouping Select Statements With Where Clause

Aug 10, 2004

Hello

What I need to do is be able to group the results of my select statements in different columns. And end having the result work like this.

campaign Col1 Col2

<<Data>> <<Select counT(*) where field= value>> <<Select counT(*) where field= value>>

View 1 Replies View Related

SELECT Question, Grouping Values

Jan 22, 2006

What is the best way to build a SQL query to extract data from a table in this manner:

COLA COLB COLC DATE
01 01 01 04/15/1988
01 01 01 11/23/1997
01 01 01 02/02/2005
01 02 01 6/14/1990
01 02 01 10/30/1999
01 02 02 4/24/2001

How would I select Row 3, 5, and 6 above (In other words, from each set where COL A B & C are equivalent, grab the most recent date?)

Thanks!!!

View 1 Replies View Related

Grouping Columns From Select Statement

Jan 23, 2015

I am retrieving some data from Invoices, Customers and Companies tables as follows, but would like to make the customerName and the Companies.Name as single column such Name and similarly for customerID/companyID and customerCode/companyCode.

Code:
with cte
as
(
selectdistinct i.invoiceNumber, itemID, customers.customerID, Companies.companyID
,SUM(net_weight) as totalWeight, rate
,(select SUM(net_weight) * rate) as amount

[code]....

View 6 Replies View Related

T-SQL (SS2K8) :: Select Statement That Needs To Use Custom Grouping

Aug 10, 2015

I am trying to SELECT data based on custom groups of that data. For example and in its simplest form:

SELECT COUNT(*)
FROMdbo.People
WHERE Current_Status = ‘A’
GROUP BY People_Code

The People_Code is the difficult part. The code represents the building that they work in. However, some buildings have multiple People_Codes. Kind of like multiple departments within a building.

For example:

Building NamePeople_CodeEmployee Count
Building A617535
Building B985665
Building C529212
Building C529932
Building C419816
Building D326974
Building D781024
Building E25365

Each building has a main People_code which, for this example, could be any one of the codes for the building. For example: Main code for building C can be 5292 and for building D it can be 7810.

Applying a variation (which is what I cannot figure out) of the SELECT statement above to this table, the result set for Building C must be the combined employee count of all three People_codes and must be represented by the main code of 5292 as a single row. Building D would have a row using code 7810 but will combine the employee count of codes 7810 and 3269.

I built a conversion table that would match up the main code with all of its related codes but just couldn’t seem to make it dance the way I want it to.

People_CodeNameGroupNameGroupPeopleCode
6175Building ABuilding A6175
9856Building BBuilding B9856
5292Building CBuildingCGroup5292
5299Building C AnnexBuildingCGroup5292
4198Building C Floor6BuildingCGroup5292
Etc…

The whole query is much more involved than just the simple SELECT statement used here, but if I can get this to work, I’m sure I can apply it to the full query.

View 5 Replies View Related

Select With Grouping For Multiple Date Columns

Sep 22, 2007

Hi All,

Thanks for dropping by my post.

I have a table which is of this form.

ID
MS030_A
MS030_F
MS036_A
MS036_F
MS040_A
MS040_F

ZZ0023
2/16/06
2/16/06
8/10/07
8/10/07
11/21/05
11/21/05

ZZ0031
8/10/07
4/5/07
8/9/07
8/9/07
3/22/07
3/22/07

ZZ0077
8/9/07
9/7/07
8/10/07
8/10/07
8/10/07
9/7/07

ZZ0078
8/10/07
9/7/07
8/9/07
8/9/07
8/9/07
9/7/07

ZZ0079
8/9/07
8/10/07
10/26/05
10/26/05
8/10/07
8/10/07

ZZ1030
3/31/05
8/10/07
9/1/05
9/1/05
8/9/07

ZZ1033
3/24/06
8/9/07
8/9/07
8/9/07
3/31/05

ZZ1034
8/10/07
8/10/07
8/9/07
8/9/07
3/24/06

ZZ1037
8/9/07
8/9/07
9/24/07

9/24/07

ZZ1040
10/26/05
10/26/05
9/24/07

9/24/07

ZZ1041
9/1/05
9/1/05
9/24/07

9/24/07

ZZ1042
8/9/07
8/9/07
9/24/07

9/24/07
11/21/05

The goal is to group all this transactions by Month and Year.

Something like this....

MS030_A
MS030_F
MS036_A
MS036_F
MS040_A
MS040_F
Month
Year

3
2
2
2
2
2
1
2006

4
4
7
9
8
9
2
2006

10
10
6
8
8

3
2006

4
4
5
5
3
2
4
2006

5
6
8
3
7
1
5
2006
For just one date column it is pretty straight forward i.e., just do a select count and group by DATEPART ( Mm, DateField)
but for multiple columns i am in a total fix....

can please someone help me out...

appreciate your help

View 6 Replies View Related

SQL Query - Need Help With Grouping

Oct 22, 2007

Hi,
I have this query...
cmd = New SqlCommand("SELECT name, webd_category_desc.category_id, (name + cast(webd_category_desc.category_id as nvarchar)) as CNameID, link_id FROM webd_category_desc left outer join webd_link_category on webd_category_desc.category_id = webd_link_category.category_id where display = 'True' order by CNameID, link_id ;", SqlConnection1)
It produces the following output (trunctated by me for this post example).

name
category_id
CNameID
link_id

Accounting/Bookkeeping
2
Accounting/Bookkeeping2
7

Accounting/Bookkeeping
2
Accounting/Bookkeeping2
22

Accounting/Bookkeeping
2
Accounting/Bookkeeping2
24

Accounting/Bookkeeping
2
Accounting/Bookkeeping2
40

Accounting/Bookkeeping
2
Accounting/Bookkeeping2
45

Accounting/Bookkeeping
2
Accounting/Bookkeeping2
89

Accounting/Bookkeeping
2
Accounting/Bookkeeping2
134

Accounting/Bookkeeping
2
Accounting/Bookkeeping2
137

Architecture
5
Architecture5
37

Architecture
5
Architecture5
90
I would like it to display instead (where 8 and 2 are the counts):
Accounting/Bookkeeping   8
Architecture   2
Seeing that I had to join a few tables to get the above output, how can I now group on it to get the name, count(name) output I desire.I'm using ADO.NET in a VB.NET/ASP.NET 2.0 webapp. The data is in SQL Server 2000. I was hoping to do it in one SqlCommand statement if possible.  I guess I can drop it into a view and then run my group by query on the view if I had to.
I am getting a variety of 'field in select list must be used in a function or aggregate' errors in the attempts I have tried so far.
Thanks in advance,
Stewart

View 6 Replies View Related

Grouping With SQL Query

Oct 29, 2007

Hi folks. Hopefully this is a simple question. What's the easiest and most efficient way to group by a dateTime field in an SQL query? Here is exactly what I'm trying to do. I have a database table that contains transactions from an email maillog, so there are dateTime entries every second or so. I'm trying to build a query that will group a count of messages per hour for a given day. How can I make an hourly grouping of the total number of messages?SELECT count(*)
FROM emailTable
WHERE (delivDate >= '2007-10-03 00:00' AND delivDate < '2007-10-03' 01:00)
Thanks, Chris

View 6 Replies View Related

Grouping By Sub Query

Jan 3, 2007

is grouping by sub query possible?

ie.

Code:

select
(select fieldx from tabley where pk = tz.fk) as field1,
field2
from
tablez tz
group by
field1

this doesn't work..i get an error that field1 is not valid...so is there a way to do this that does work?

please realize that the above example is exactly that..and had i needed to do something that easy, join would be the easy choice..what i'm trying to do requires a sub query

View 14 Replies View Related

Grouping (sum) Query

Jan 15, 2004

I have the following information in a table

ACCNORundateTRDCAPTRANQTYDLPRCE NOTEAMNT
27547920031202A-170002150000
27547920031202A-27412150000
27547920031202A-259215000-42501729

I need to create a query that totals TRANQTY and arrives at a result as in the following record.

ACCNORundateTRDCAPTRANQTYDLPRCE NOTEAMNT
27547920031202A-20000215000-42501729

and now for the $1M question...How ? I've tried the following select, but it is not working the way I want it to..

SELECT c2.ACCNO, c2.Rundate, c2.TrdCap, c2.TRANQTY, c2.DLPRCE, c2.NOTEAMNT
FROM CLIENTSHAREDEALS c2 FULL OUTER JOIN
(SELECT c1.ACCNO, c1.SHARENAME, SUM(c1.TRANQTY) AS Expr1
FROM CLIENTSHAREDEALS c1
WHERE (c1.ACCNO = '275479')
GROUP BY c1.ACCNO, c1.RUNDATE, c1.SHARENAME) c1 ON c1.ACCNO = c2.ACCNO AND c1.RUNDATE = c2.RUNDATE
WHERE (c2.ACCNO = '275479')
ORDER BY c1.RUNDATE

Thanks

View 2 Replies View Related

Help With Grouping Query...

Dec 5, 2005

View 4 Replies View Related

Can This Sub Query Code Can Be Done In Grouping

Feb 26, 2008

HI all, I got a tsql that needs to be simplified.
Select * from Table1 where condition1 and id not in
    ( Select id from table1 where condition2 and id in
             ( Select id from Table1 where condition1 )
    )
basicly all records thats in condition1 but that doesnt have condition2 but limited to condition1. I'm probably maken this to complicated. but im tired and im losing time just on one stupid query. Thanks for the help.

View 9 Replies View Related

Min/Maximum Grouping Query

Jun 13, 2004

I know this has been posted before, but I can't find the previous threads so please bear with me....

I want to grab the very 1st record of each product in a table like this

ID CLIENTID PRODID
1 a 1
2 b 1
3 c 1
4 a 2
5 b 2
6 c 2
7 a 3
8 b 3
9 c 3

so that I'd get a record set like:

ID CLIENTID PRODID
1 a 1
4 a 2
7 a 3

Thanks for the hellp guru's

View 4 Replies View Related

How To Query By Grouping One Column

Jan 5, 2005

Hello, everyone:

I have a table like:

Col1Col2
1A
2B
1D
1P
2F
2W

How to query this table to return by Col1 like

Col1Col2
1A,D,P
2B,F,W

Thanks a lot
ZYT

View 11 Replies View Related

Query For Grouping And Join?

Aug 22, 2014

I have below data in one of my table:

Calls|Status
4|-6
12|-11
1|0
1|-10

Desired Output required :

Total_calls|Zero|Non-Zero
18|1|17

View 3 Replies View Related

Tricky Grouping Query

Jul 20, 2005

I'm having much difficulty figuring out how to write the followingquery. Please help!I have this table:EventEventId int Primary KeyPatientId intSeverityLevel intWhat I want returned in my query is a list of all (distinct)PatientIds appearing in Event, with the *most severe* EventId returnedfor each Patient. The higher the value of SeverityLevel, the moresevere that Event is considered to be.The problem I am having is that I can't figure out how to (a) group byPatientId, AND (b) return the EventId of the highest-severity Eventfor *each* PatientId (Order By SeverityLevel Desc).So if my table contained:EventId PatientId SeverityLevel------- --------- -------------1 1 02 1 13 1 54 2 55 2 2I would want my result set to be:PatientId EventId--------- -------1 32 4since events 3 and 4 are the most severe events for patients 1 and 2,respectively.Any help would be greatly appreciated. This seems to be something thatcould be handled easily with a FIRST() aggregate operator (as in MSAccess) but this is apparently lacking in SQL Server. Also note theremay be multiple Events with a given PatientId and SeverityLevel, inthat case I'd want only one of the EventIds (the Max() one).Many thanks,Joel ThorntonDeveloper, Total Living Choices<joelt@tlchoices.com>(206) 709-2801 x24

View 7 Replies View Related

Grouping A Query In 30 Seconds

Apr 13, 2006

Hi,

How can I make a query and group the registries in a interval of 30 seconds...like

for each line I have a datetime field that have all the day, and I need it to return just like

TIME Contador_type1 Contador_type2 Total

01-01-2006 00:00:30.000 2 5 7

01-01-2006 00:01:00.000 3 7 10

It's just an example...but that's the result that I need and my table is

data_hora -- datetime field

tipo - 1 or 2 -- count

nrtelefone - that's is the number dialed.

Thanks

View 16 Replies View Related

SQL Server Grouping Query

Dec 12, 2007

Hi,

Below is my DB Table..

Owner varchar(500)
contains...
OwnerA
OwnerB

Book varchar(500)
contains values...
Book1
Book2
Book3

Book1 might be owned by OwnerA
and Book2 might be owned by OwnerA and OwnerB

So in the Table is this...

ID Book Owner
1 Book1 OwnerA
2 Book2 OwnerA
3 Book2 OwnerB

How would I output this relationship in sql?

Thanks.

View 1 Replies View Related

Need Help With A Simple Query - Grouping Data

Jul 30, 2007

Hello,

I have a table similar to the following (XYZ). I would like to write a select statement that will return the count of the unique items for each user that also happen to be less than 1 year old. The less than one year old part is rather easy dateadd(year, -1, getdate()), but I seem to be having a hard time figuring out how to get my desired result without using subselects. Any help greatly appreciated. Thanks in advance - Dan.

So my goal results are:

User Count
Dan 2
Dave 1

Table XYZ
ID User Item Value Date

1 Dan 1 20 5/5/2007

2 Dan 1 30 6/5/2007

3 Dave 2 25 6/1/2007

4 Dan 2 22 5/1/2007
5 Dan 3 23 5/1/2006

View 6 Replies View Related

Transact SQL :: Query To Generate XML And Grouping?

Jul 29, 2015

I am trying to generate XML path from a SQL Server Table. Table Structure and sample data as below

CREATE TABLE #OfferTransaction
( [OfferLoanAmount1] INT
,[offferid1ProgramName] VARCHAR(100)
,[Offer1LenderName] VARCHAR(100)
,[offerid1LenderNMLSID] INT

[code]....

what changes do I need in my query so that the XML looks like the one above ( DESIRED XML). Is it possible via query changes?

View 3 Replies View Related

Displaying Results In Aggregate Query Without Grouping Everything?

Mar 13, 2012

I have a query where I have customers, date they ordered a swatch, date they ordered an item, and eh date diff between the two. I want to show the MIN date diff for each customer, and also show the swatch date and item date as well. But to use the MIN aggregate, it forces me to group everything, where I just want to group by customer, and have the 2 dates tag along, because i only want one record per customer. What is the easiest way for me to accomplish this?

SAMPLE:

CustKeySwatchDateRugDateDateDiff
10903963126678366
10903963126837525
10903963126960648
10913962286550322
1115886193625764
1129666456646711
1146986229625324
1146986229627647
11469862296667438
1146986656666711
1146986624666743

DESIRED RESULTS:

CustKeySwatchDateRugDateDateDiff
10903963126678366
1115886193625764
1129666456646711
1146986656666711

View 7 Replies View Related

SQL Server 2008 :: Query Grouping By Month

Mar 5, 2015

I have the query below which produces a succesful output but as there is more than one course date the month appears for example three times where there are three courses in Jan as the example output below how can I change the query to group these

MonthYear CCG AttendedCity CCG DNACity CCG Cancelled
Oct2014010
Jan2015000
Jan2015000
Jan2015100
Feb2015000
Mar2015210
May2015010

SQL QUERY
SELECT CONVERT(char(3), dbo.tblCourses.CourseDate, 0) AS Month, YEAR(dbo.tblCourses.CourseDate) AS Year, SUM(CASE WHEN a.AttendanceStatus IN (9)
THEN 1 ELSE 0 END) AS [City CCG Attended], SUM(CASE WHEN a.AttendanceStatus IN (3) THEN 1 ELSE 0 END) AS [City CCG DNA],

[Code] ....

View 2 Replies View Related

Question About Sub Queries In A Main Query When Grouping

May 22, 2008

I have a query that gets a supplier, a month, a year, status and sum of recpits.
returning the following. but my problem is I also need a col of totals. i tried to put a sub grouped query
in the select statement but keep getting an error
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

AR162600 ARROW ELECTRONICS 10424 Early 1 2008 2007-12-31 23:59:59.997
AR162600 ARROW ELECTRONICS 516 Late 1 2008 2007-12-31 23:59:59.997
AR162600 ARROW ELECTRONICS 279603 On-Time 1 2008 2007-12-31 23:59:59.997

my qurey is below. how can I get another col called total it will be the same value on each row.

AR162600 ARROW ELECTRONICS 10424 290543 Early 1 2008 2007-12-31 23:59:59.997
AR162600 ARROW ELECTRONICS 516 290543 Late 1 2008 2007-12-31 23:59:59.997
AR162600 ARROW ELECTRONICS 279603 290543 On-Time 1 2008 2007-12-31 23:59:59.997

SELECT ot_ven_num, ot_ven_name, sum(ot_rec_qty) as ot_rec_qty,
ot_rec_stat, datepart(Month,ot_rec_dt) as mth,
datepart(year,ot_rec_dt) as ryear,
DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(mm, 0, ot_rec_dt), 0)) as ot_rpt_date,
(SELECT ot_ven_num, ot_ven_name, sum(ot_rec_qty) as ot_rec_qty, 'Totals' as ot_rec_stat,
datepart(Month,ot_rec_dt) as mth,
datepart(year,ot_rec_dt) as ryear,
DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(mm, 0, ot_rec_dt), 0)) as ot_rpt_date
FROM supplierOT where ot_ven_name = 'ARROW ELECTRONICS' and datepart(year,ot_rec_dt) > 2007
group by ot_ven_num, ot_ven_name, DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(mm, 0, ot_rec_dt), 0)),
datepart(Month,ot_rec_dt),
datepart(year,ot_rec_dt)) as total

FROM supplierOT where ot_ven_name = 'ARROW ELECTRONICS' and datepart(year,ot_rec_dt) > 2007
group by ot_ven_num, ot_ven_name, ot_rec_stat, DATEADD(ms, - 3, DATEADD(mm, DATEDIFF(mm, 0, ot_rec_dt), 0)),
datepart(Month,ot_rec_dt),
datepart(year,ot_rec_dt)

View 9 Replies View Related

Return The Results Of A Select Query In A Column Of Another Select Query.

Feb 8, 2008

Not sure if this is possible, but maybe. I have a table that contains a bunch of logs.
I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID.
I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages.
When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID).
Any thoughts as to how I could accomplish such a daring feat?

View 9 Replies View Related

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

Oct 6, 2015

I have query on grouping data on weekly basis..

1. Week should start from Monday to Sunday

2. It should not consider current week data(suppose user clicks on report on Tuesday, it should display the data for last week).

3. I want output like below

Week1,week2,week3..... week12,AverageofWeek
12 , 10 ,0.........0 12

View 1 Replies View Related

Sorting And Grouping Question By Allowing Users To Select The Sorting Field

Feb 11, 2007

I have a report where I am giving the users a parameter so that they can select which field they would like to sort on.The report is also grouping by that field. I have a gruping section, where i have added code to group on the field I want based on this parameter, however I also would like to changing the sorting order but I checked around and I did not find any info.

So here is my example. I am showing sales order info.The user can sort and group by SalesPerson or Customer. Right now, I have code on my dataset to sort by SalesPerson Code and Order No.So far the grouping workds, however the sorting does not.

Any suggestions would help.

Thanks

View 1 Replies View Related

Select Query Based Upon Results Of Another Select Query??

Sep 6, 2006

Hi, not exactly too sure if this can be done but I have a need to run a query which will return a list of values from 1 column. Then I need to iterate this list to produce the resultset for return.
This is implemented as a stored procedure

declare @OwnerIdent varchar(7)
set @OwnerIdent='A12345B'

SELECT table1.val1 FROM table1 INNER JOIN table2
ON table1. Ident = table2.Ident
WHERE table2.Ident = @OwnerIdent

'Now for each result of the above I need to run the below query

SELECT Clients.Name , Clients.Address1 ,
Clients.BPhone, Clients.email
FROM Clients INNER JOIN Growers ON Clients.ClientKey = Growers.ClientKey
WHERE Growers.PIN = @newpin)

'@newpin being the result from first query

Any help appreciated

View 4 Replies View Related

Result Sets Using Select In Query Anlyzer Vs BCP Vs Select Into

Jul 9, 2002

When I run simple select against my view in Query Analyzer, I get result set in one sort order. The sort order differs, when I BCP the same view. Using third technique i.e. Select Into, I have observed the sort order is again different in the resulting table. My question is what is the difference in mechanisim of query analyzer, bcp, and select into.
Thanks

View 1 Replies View Related

Date Select Query - Select Between Two Dates

Aug 22, 2006

have a table with students details in it, i want to select all the students who joined a class on a particular day and then i need another query to select all students who joined classes over the course of date range eg 03/12/2003 to 12/12/2003.

i have tried with the following query, i need help putting my queries together
select * from tblstudents where classID='1' and studentstartdate between ('03/12/2004') and ('03/12/2004')

when i run this query i get this message

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

the studentstartdate field is set as datetime 8 and the date looks like this in the table 03/12/2004 03:12:15

please help
mustfa

View 6 Replies View Related

SQL Server 2012 :: Adding Count To Query Without Duplicating Original Select Query

Aug 5, 2014

I have the following code.

SELECT _bvSerialMasterFull.SerialNumber, _bvSerialMasterFull.SNStockLink, _bvSerialMasterFull.SNDateLMove, _bvSerialMasterFull.CurrentLoc,
_bvSerialMasterFull.CurrentAccLink, _bvSerialMasterFull.StockCode, _bvSerialMasterFull.CurrentAccount, _bvSerialMasterFull.CurrentLocationDesc,
_bvSerialNumbersFull.SNTxDate, _bvSerialNumbersFull.SNTxReference, _bvSerialNumbersFull.SNTrCodeID, _bvSerialNumbersFull.SNTransType,
_bvSerialNumbersFull.SNWarehouseID, _bvSerialNumbersFull.TransAccount, _bvSerialNumbersFull.TransTypeDesc,

[code]...

However, as you can see, the original select query is run twice and joined together.What I was hoping for is this to be done in the original query without the need to duplicate the original query.

View 2 Replies View Related

SQL Server 2012 :: How To Pull Value Of Query And Not Value Of Variable When Query Using Select Top 1 Value From Table

Jun 26, 2015

how do I get the variables in the cursor, set statement, to NOT update the temp table with the value of the variable ? I want it to pull a date, not the column name stored in the variable...

create table #temptable (columname varchar(150), columnheader varchar(150), earliestdate varchar(120), mostrecentdate varchar(120))
insert into #temptable
SELECT ColumnName, headername, '', '' FROM eddsdbo.[ArtifactViewField] WHERE ItemListType = 'DateTime' AND ArtifactTypeID = 10
--column name
declare @cname varchar(30)

[code]...

View 4 Replies View Related