Help with complex query

Hi Everyone,

I need some help writing a query that joins a table to a UNION query. I was wondering what is the most efficent way to do this.

Tables

Employees (EMPID, FULLNAME)

DailySchedules (SCHID,EMPID, SCHDATE,DEPTID)

GeneralSegments (GSID,EMPID,STARTTIME,STOPTIME)

DetailSegments (DSID,EMPID,STARTTIME,STOPTIME)

I need to join the records from GeneralSegments and DetailSegments THEN inner join DailySchedules and Employees.

Query must output:

EMPID, FULLNAME, SCHDATE,STARTTIME,STOPDTIME

Thank You

Can you provide some sample data (preferrably in the form of insert statements) along with a data representation of what you want for output?

Thanks.

|||

Untested, but, it should give you an idea

Code Snippet

SELECT Segments.EMPID,
Employees.FULLNAME,
DailySchedules.SCHDATE,
Segments.STARTTIME,
Segments.STOPDTIME

FROM

(
SELECT EMPID, STARTTIME, STOPTIME FROM GeneralSegments
UNION
SELECT EMPID, STARTTIME, STOPTIME FROM DetailSegments
) Segments
INNER JOIN Employees
ON Employees.EMPID = Segments.EMPID
INNER JOIN DailySchedules
ON DailySchedules.EMPID = Segments.EMPID

|||

Hi David,

That example will work. One question though, I have to display the next 14 days (2 weeks) on the web. Where should I place my WHERE clause? In the outer query or include the date range in both union statements?

ie. WHERE SCHDATE BETWEEN GETDATE() AND GETDATE() + 14

Thank You

|||

Is SCHDATE column present in the tables in the DailySchedule table or the segments tables? In any case, it doesn't matter. SQL Server will automatically roll the schdate predicate into the inner queries as well based on their reference. For example, if you want to filter on STARTTIME then you can include just one WHERE clause like below and it will be applied to both GeneralSegments & DetailSegments

Code Snippet

SELECT Segments.EMPID,
Employees.FULLNAME,
d.SCHDATE,
Segments.STARTTIME,
Segments.STOPDTIME

FROM

(
SELECT EMPID, STARTTIME, STOPTIME FROM GeneralSegments
UNION
SELECT EMPID, STARTTIME, STOPTIME FROM DetailSegments
) Segments
INNER JOIN Employees
ON Employees.EMPID = Segments.EMPID
INNER JOIN DailySchedules
ON DailySchedules.EMPID = Segments.EMPID
WHERE Segments.STARTTIME >= @.Start

sql

Help with a SQL Union query please. Conversion errors!

Hi, I've created a SQL union query that grabs the top three values of a test and puts the results together on one line item by a recordnumber.

The only problem is, one value is generating the error: " Syntax error converting the varchar value '=' to a column of data type int."

This field is an operator field, so it will only display operator values ( <, >, = ). Everything seems to work BUT the union join on that column, which is a varchar data type. I need this union to work, and Im frustrated that I can't seem to figure out why I can't get the logic to work. Can someone take a look at this and help me with it?

The union query looks like this:

exec('
select

RecordNumber,
Sum(rank1) as HbA1CRank1,
max(Operator1) as HbA1COperator1,
sum(contentValue1) as HbA1CContentvalue1,
max(dos1) as HbA1CDOS1,
Sum(rank2) as HbA1CRank2,
max(Operator2) as HbA1COperator2,
sum(contentValue2) as HbA1CContentvalue2,
max(dos2) as HbA1CDOS2,
Sum(rank3) as HbA1CRank3,
max(Operator3) as HbA1COperator3,
sum(contentValue3) as HbA1CContentvalue3,
max(dos3) as HbA1CDOS3

from

(SELECT DISTINCT
TOP 100 PERCENT recordnumber, Rank AS rank1, cast(Operator as varchar) as Operator1, contentValue AS contentvalue1, DOS AS DOS1, 0 AS rank2, 0 as Operator2, 0 AS contentvalue2, 0 AS DOS2,
0 AS rank3, 0 as Operator3, 0 AS contentvalue3, 0 AS DOS3
FROM (SELECT (SELECT COUNT(*)
FROM tblDiabetic_HgbA1C_Total vw1
WHERE vw1.rECORDnUMBER = vw2.rECORDnUMBER AND vw1.ItemCode = vw2.ItemCode AND vw1.Operator = vw2.Operator AND vw1.DOS > vw2.DOS) AS Rank, *
FROM tblDiabetic_HgbA1C_Total vw2) vw3
WHERE (Rank = 0)
ORDER BY RecordNumber

UNION

SELECT DISTINCT
TOP 100 PERCENT recordnumber, 0 AS rank1, 0 as Operator1, 0 AS contentvalue1, 0 AS DOS1, Rank AS rank2, cast(Operator as varchar) as Operator2, contentValue AS contentvalue2, DOS AS DOS2,
0 AS rank3, 0 as operator3, 0 AS contentvalue3, 0 AS DOS3
FROM (SELECT (SELECT COUNT(*)
FROM tblDiabetic_HgbA1C_Total vw1
WHERE vw1.rECORDnUMBER = vw2.rECORDnUMBER AND vw1.ItemCode = vw2.ItemCode AND vw1.Operator = vw2.Operator AND vw1.DOS > vw2.DOS) AS Rank, *
FROM tblDiabetic_HgbA1C_Total vw2) vw3
WHERE (Rank = 1)
ORDER BY RecordNumber

UNION

SELECT DISTINCT
TOP 100 PERCENT recordnumber, 0 AS rank1, 0 as Operator1, 0 AS contentvalue1, 0 AS DOS1, 0 AS rank2, 0 as operator2, 0 AS contentvalue2, 0 AS DOS2, rank AS rank3, cast(Operator as varchar) as operator3,
contentvalue AS contentvalue3, DOS AS DOS3
FROM (SELECT (SELECT COUNT(*)
FROM tblDiabetic_HgbA1C_Total vw1
WHERE vw1.rECORDnUMBER = vw2.rECORDnUMBER AND vw1.ItemCode = vw2.ItemCode AND vw1.Operator = vw2.Operator AND vw1.DOS > vw2.DOS) AS Rank, *
FROM tblDiabetic_HgbA1C_Total vw2) vw3
WHERE (Rank = 2)
ORDER BY RecordNumber )tblHgA1C

group by RecordNumber

')
GO

Can anyone help? It looks right to me, I just can't figure out why the error keeps coming up =\

Thank you!

The error most likely comes from having "cast(Operator as varchar) as Operator1" in the first query and then "0 as Operator1" in the subsequent ones. It should be "'' as Operator1"

However,write your query like this instead, and you won't have to use a UNION at all

select

RecordNumber,
Sum(CASE WHEN Rank=0 THEN Rank ELSE NULL END) as HbA1CRank1, -- WILL BE 0
max(CASE WHEN Rank=0 THEN Operator ELSE NULL END) as HbA1COperator1,
sum(CASE WHEN Rank=0 THEN contentValue ELSE NULL END) as HbA1CContentvalue1,
max(CASE WHEN Rank=0 THEN DOS ELSE NULL END) as HbA1CDOS1,
Sum(CASE WHEN Rank=1 THEN Rank ELSE NULL END) as HbA1CRank2,
max(CASE WHEN Rank=1 THEN Operator ELSE NULL END) as HbA1COperator2,
sum(CASE WHEN Rank=1 THEN contentValue ELSE NULL END) as HbA1CContentvalue2,
max(CASE WHEN Rank=1 THEN DOS ELSE NULL END) as HbA1CDOS2,
Sum(CASE WHEN Rank=2 THEN Rank ELSE NULL END) as HbA1CRank3,
max(CASE WHEN Rank=2 THEN Operator ELSE NULL END) as HbA1COperator3,
sum(CASE WHEN Rank=2 THEN contentValue ELSE NULL END) as HbA1CContentvalue3,
max(CASE WHEN Rank=2 THEN DOS ELSE NULL END) as HbA1CDOS3

from ...|||

Gunteman! Oh man, thank you so much! I can't believe how easy that was, your solution worked perfectly. That's awesome, you made my night!

Thanks again =)

|||

Great! BTW, why did you use EXEC?

|||

about a year ago someone here recommended wrapping my union statements in the EXEC command when running them within stored procedures. I didnt' bother researching further... it seemed to work, so I just ran with it and kept doing it :)

Should I not bother with it?

sql

HELP with a Select/Union statement

I have 3 tables One table is the order Table, Bill to table and ship to table
I have to Views created as followed

This query uses the Ship to table to pull the ship to information to the shipping system.
SELECT Cust_address.NAME, Cust_address.ADDR_1, Cust_address.ADDR_2, Cust_address.ADDR_3, Cust_address.CITY, Cust_address.STATE, Cust_address.ZIPCODE, Cust_address.COUNTRY, Cust_address.SHIP_VIA, customer_order.ID
FROM Cust_address INNER JOIN customer_order ON (Cust_address.CUSTOMER_ID = customer_order.CUSTOMER_ID) AND (Cust_address.ADDR_NO = customer_order.SHIP_TO_ADDR_NO);

This query uss the Bill to as the ship to inforamtion
SELECT CUSTOMER.ID, CUSTOMER.SHIPTO_ID, CUSTOMER.NAME, CUSTOMER.ADDR_1, CUSTOMER.ADDR_2, CUSTOMER.ADDR_3, CUSTOMER.CITY, CUSTOMER.STATE, CUSTOMER.ZIPCODE, CUSTOMER.COUNTRY, CUSTOMER.SHIP_VIA, customer_order.ID, customer_order.SHIP_TO_ADDR_NO
FROM CUSTOMER INNER JOIN customer_order ON CUSTOMER.ID = customer_order.CUSTOMER_ID;

I need this infroamtion in one table which I have done in the UNION statement as followed:
SELECT Cust_address.NAME, Cust_address.ADDR_1, Cust_address.ADDR_2, Cust_address.ADDR_3, Cust_address.CITY, Cust_address.STATE, Cust_address.ZIPCODE, Cust_address.COUNTRY, Cust_address.SHIP_VIA, customer_order.ID
FROM Cust_address INNER JOIN customer_order ON (Cust_address.CUSTOMER_ID = customer_order.CUSTOMER_ID) AND (Cust_address.ADDR_NO = customer_order.SHIP_TO_ADDR_NO)
UNION ALL
SELECT CUSTOMER.NAME, CUSTOMER.ADDR_1, CUSTOMER.ADDR_2, CUSTOMER.ADDR_3, CUSTOMER.CITY, CUSTOMER.STATE, CUSTOMER.ZIPCODE, CUSTOMER.COUNTRY, CUSTOMER.SHIP_VIA, customer_order.ID
FROM CUSTOMER INNER JOIN customer_order ON CUSTOMER.ID=customer_order.CUSTOMER_ID;

Here is the problem when I pull information out of the ship to table I get 2 results as followed My key field to pull this information is the Last field Custoemr ID this custoemr ID exist in both tables but contains different information I want to ONLY pull in the info that I need in this case it would be the first line that is the Correct shipping information.

NAME ADDR_1 ADDR_2 ADDR_3 CITY STATE ZIPCODE COUNTRY SHIP_VIA ID
DIEBOLD INC (4076A) ATTN: RANCE AARON 343 MANOR DR PACIFICA CA 94044 18932
DIEBOLD, INC OH UPS #88X08X 18932

MY POINT: Is there a way to select a over all DISTINCT order ID.

Thank you for any help hope this make sense!...I want to ONLY pull in the info that I need in this case it would be the first line that is the Correct shipping information.Define what you mean by "first line". The order of data in a database has no meaning. Do you have a date field or some other value you can sort on to determine which shipping information is "first"?|||Sorry
In the Union query the first select statement will Always be the correct shipping information when the ORDER form has one(Shipping info) if it does not have a Shipping address printed on the form then the second select statement will pull from the bill table which will only have one record per the order number.

Example Order form:
Bill-to Ship to information
Bill to Compnay Ship to company
Bill to address Ship to address
Bill to city Ship to City
Bill to State Ship to state
Etc Etc

When this order occurs then there is one record in the bill to table with that info and 1 record in the Ship to table with the shipping info This is where the problme occurs when I do the UNION it looks in the Shipping table first and pulls the shipping info and then the other statement in the UNION pulls the BILL to info which give me 2 records in the Query so what happends in this case I pull the info using ODBC to my shipping system and it says it found 2 records but I only what the First select statement to show. Another problem is when the ship to is not fill out I need to use the second part of the UNION select statement in order ot pull the bill to info for the shipping info but this works correctly due to only 1 order number exists in the bill to tableEX:

Example Order form:
Bill-to Ship to information
Bill to Compnay SAME
Bill to address
Bill to city
Bill to State
Etc
When this occurs then I need to use the Bill to table for the shipping info.

Hopfully this clears things up.|||So this query:SELECT Cust_address.NAME,
Cust_address.ADDR_1,
Cust_address.ADDR_2,
Cust_address.ADDR_3,
Cust_address.CITY,
Cust_address.STATE,
Cust_address.ZIPCODE,
Cust_address.COUNTRY,
Cust_address.SHIP_VIA,
customer_order.ID
FROM Cust_address
INNER JOIN customer_order
ON Cust_address.CUSTOMER_ID = customer_order.CUSTOMER_ID
AND Cust_address.ADDR_NO = customer_order.SHIP_TO_ADDR_NO...contains all the orders that you need, but in some cases the address information is missing and you want to pull address information from the CUSTOMER table to fill it in?|||Yes Sir that is correct!