Confused On Syntax - Combine Two Queries

Apr 3, 2008

I have two queries that I'm trying to combine, but can't figure out how to combine them ... successfully!?! The first query is pretty simple in that I'm looking at several fields from two different tables, no big deal.

The second query calculates the years, months, days between two dates that are used in the first query. I'm stumped on how to combine the queries so that they place nice with each other and return results.

Here's the first query ...
select
RTRIM(RTRIM(vpi.LastName) + ', ' + RTRIM(ISNULL(vpi.FirstName,''))) Employee,
convert(varchar,vpi.FromEffectiveDate,101) PositionStart,
convert(varchar,vpi.ToEffectiveDate,101) PositionChange,
convert(varchar,vpi.PositionStartDate,101) PositionStartDate,
vpi.PositionReason, vpi.PositionCode, vpc.PositionCodeDescription
from vhrl_positioninfo vpi
inner join position_codes vpc on vpi.PositionCode = vpc.PositionCode

Here's the second query ...
select
[Age] = convert(varchar, [Years]) + ' Years ' +
convert(varchar, [Months]) + ' Months ' +
convert(varchar, [Days]) + ' Days', *
from
(
select
[Years] = case when BirthDayThisYear <= Today
then datediff(year, BirthYearStart, CurrYearStart)
else datediff(year, BirthYearStart, CurrYearStart) - 1
end,
[Months]= case when BirthDayThisYear <= Today
then datediff(month, BirthDayThisYear, Today)
else datediff(month, BirthDayThisYear, Today) + 12
end,
[Days]= case when BirthDayThisMonth <= Today
then datediff(day, BirthDayThisMonth, Today)
else datediff(day, dateadd(month, -1, BirthDayThisMonth), Today)
end,
Birth = convert(varchar(10) ,Birth, 121),
Today = convert(varchar(10), Today, 121)
from
(
select BirthDayThisYear =
case when day(dateadd(year, datediff(year, BirthYearStart, CurrYearStart), Birth)) <> day(Birth)
then dateadd(day, 1, dateadd(year, datediff(year, BirthYearStart, CurrYearStart), Birth))
else dateadd(year, datediff(year, BirthYearStart, CurrYearStart), Birth)
end,
BirthDayThisMonth =
case when day(dateadd(month, datediff(month, BirthMonthStart, CurrMonthStart), Birth)) <> day(Birth)
then dateadd(day, 1, dateadd(month, datediff(month, BirthMonthStart, CurrMonthStart), Birth))
else dateadd(month, datediff(month, BirthMonthStart, CurrMonthStart), Birth)
end,
*
from
(
select BirthYearStart = dateadd(year, datediff(year, 0, Birth), 0),
CurrYearStart = dateadd(year, datediff(year, 0, Today), 0),
BirthMonthStart = dateadd(month, datediff(month, 0, Birth), 0),
CurrMonthStart = dateadd(month, datediff(month, 0, Today), 0),
*
from
(
select birth = convert(datetime, fromeffectivedate) ,
Today = case when convert(datetime, toeffectivedate) = '3000-01-01'
THEN convert(datetime, convert(int,getdate()))
else vpi.toeffectivedate
end

from vHRL_PositionInfo vpi inner join position_codes vpc
on vpi.PositionCode = vpc.PositionCode

) aaaa
) aaa
) aa
)a


Here's the sample data ...
vpi table ...
LastName FirstName FromEffectDate ToEffectDate PosStartDate PosReason PosCode
Doe John 2001-10-15 3000-01-01 10-15-2001 Transfer OperPack
Smith Tom 1994-11-28 2001-10-14 1994-11-28 New Hire OperDC

vpc table ...
PosCode PosDescription
OperPack Pack Line Operator
OperDC Descaler Operator

This is what the results should look like ...
John, Doe 2001-10-15 3000-01-01 10-15-2001 Transfer OperPack Pack Line Operator 6 Years 11 Months 16 Days
John, Doe 1994-11-28 2001-10-14 1994-11-28 New Hire OperDC Descaler Operator 6 Years 6 Months 19 Days

I know the date calculation piece adds 5 additional fields to the end, but they are not needed for the final report. Any help would be greatly appreciated! Thank you! Jena

View 5 Replies


ADVERTISEMENT

Confused On Syntax-combining Queries

Apr 3, 2008

I have two queries that I'm trying to combine, but can't figure out how to combine them ... successfully!?! The first query is pretty simple in that I'm looking at several fields from two different tables, no big deal.

The second query calculates the years, months, days between two dates that are used in the first query. I'm stumped on how to combine the queries so that they place nice with each other and return results.

I will post my feable attempt at merging them. No matter how I order the code, I continue to get the same error, pertaining to the last line of code ... Line 73: Incorrect syntax near 'vpi'.

Any help would be greatly appreciated! Thank you! Jena

select
RTRIM(RTRIM(vpi.LastName) + ', ' + RTRIM(ISNULL vpi.FirstName,''))) Employee,
convert(varchar,vpi.FromEffectiveDate,101) PositionStart,
convert(varchar,vpi.ToEffectiveDate,101) PositionChange,
convert(varchar,vpi.PositionStartDate,101) PositionStartDate,
datediff(hour,vpi.FromEffectiveDate,vpi.ToEffectiveDate) as time_diff,
vpi.PositionReason, vpi.PositionCode, vpc.PositionCodeDescription

from
(

select [Age] = convert(varchar, [Years]) + ' Years ' +
convert(varchar, [Months]) + ' Months ' +
convert(varchar, [Days]) + ' Days',
*
from
(
select
[Years] = casewhen BirthDayThisYear <= Today
then datediff(year, BirthYearStart, CurrYearStart)
else datediff(year, BirthYearStart, CurrYearStart) - 1
end,
[Months]= casewhen BirthDayThisYear <= Today
then datediff(month, BirthDayThisYear, Today)
else datediff(month, BirthDayThisYear, Today) + 12
end,
[Days]= casewhen BirthDayThisMonth <= Today
then datediff(day, BirthDayThisMonth, Today)
else datediff(day, dateadd(month, -1, BirthDayThisMonth), Today)
end,
Birth = convert(varchar(10) ,Birth, 121),
Today = convert(varchar(10), Today, 121)

from
(
selectBirthDayThisYear =
casewhenday(dateadd(year, datediff(year, BirthYearStart, CurrYearStart), Birth)) <> day(Birth)
thendateadd(day, 1, dateadd(year, datediff(year, BirthYearStart, CurrYearStart), Birth))
elsedateadd(year, datediff(year, BirthYearStart, CurrYearStart), Birth)
end,
BirthDayThisMonth =
case when day(dateadd(month, datediff(month, BirthMonthStart, CurrMonthStart), Birth)) <> day(Birth)
thendateadd(day, 1, dateadd(month, datediff(month, BirthMonthStart, CurrMonthStart), Birth))
elsedateadd(month, datediff(month, BirthMonthStart, CurrMonthStart), Birth)
end,
*
from
(
selectBirthYearStart = dateadd(year, datediff(year, 0, Birth), 0),
CurrYearStart = dateadd(year, datediff(year, 0, Today), 0),
BirthMonthStart = dateadd(month, datediff(month, 0, Birth), 0),
CurrMonthStart = dateadd(month, datediff(month, 0, Today), 0),
*
from
(
select
birth = convert(datetime, fromeffectivedate) ,
Today = case when convert(datetime, toeffectivedate) = '3000-01-01'
THEN convert(datetime, convert(int,getdate()))
else toeffectivedate
end, *
from vHRL_PositionInfo

) aaaa
) aaa
) aa
)a

) vHRL_PositionInfo vpi inner join position_codes vpc on vpi.PositionCode = vpc.PositionCode

View 2 Replies View Related

Combine Sql Queries

Feb 19, 2008

Hello, I have these variables on my page:
userid = "10101"
RequestHost = "example.com"
What would be the best way performace wise to first check if the userid 10101 exists in my sql server db.  If it does exist I would then need to check if "example.com" exists for the userid in the userdomains table.  If these both exist I would then like to query some additional data.  I was hoping its possible to combine this into one query somehow.  I dont think this is the best solution:
 sqlcommand.CommandText = "SELECT UserId From Users Where UserID = '10101'"
Conn.Open()
dr = sqlcommand.ExecuteReader
if dr.hasrows then
sqlcommand2.CommandText = "SELECT UserDomain From UserDomains Where UserID = 'example.com'"
dr2 = sqlcommand2.ExecuteReader
if dr2.hasrows then
sqlcommand3.CommandText = 'Select Additional Data
dr3 = sqlcommand3.ExecuteReader
'read values
conn.close
else
conn.close
'do something
end if
else
conn.close
'do something
end if  Thanks Very Much!

View 2 Replies View Related

Combine Two Queries Into One

Jun 16, 2008

I need to combine to sql queries. Separately they work fine, but I need the "total qty" from second query put into the first query

Query 1

SELECT dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Region.CompanyRegionID, dbo.Job.Active,
dbo.Job.ChangeDate
FROM dbo.Job
LEFT OUTER JOIN dbo.Division ON dbo.Job.DivisionGuid = dbo.Division.DivisionGuid
LEFT OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid
LEFT OUTER JOIN dbo.JobType ON dbo.Job.JobTypeGuid = dbo.JobType.JobTypeGuid
WHERE dbo.job.CompanyJobId = 3505048
ORDER BY dbo.Job.CompanyJobId

Query 2

SELECT case dbo.SourceType.CompanySourceTypeId
when 'PR' then SUM(dbo.ProductionEvent.Quantity)
end
AS Ttl_Qty
FROM dbo.Batch INNER JOIN
dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN
dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN
dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid INNER JOIN
dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid INNER JOIN
dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid INNER JOIN
dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid LEFT OUTER JOIN
dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid left outer join
dbo.JobNoteEvent on Event.EventGuid = dbo.JobNoteEvent.EventGuid
WHERE dbo.Job.CompanyJobId = 3505048 and dbo.SourceType.CompanySourceTypeId = 'PR'
GROUP BY dbo.SourceType.CompanySourceTypeId

I have tried this but it doe not work:

SELECT dbo.Job.CompanyJobId, dbo.Job.Name, dbo.Region.CompanyRegionID, dbo.Job.Active,
dbo.Job.ChangeDate, Ttl_Qty
FROM dbo.Job
LEFT OUTER JOIN dbo.Division ON dbo.Job.DivisionGuid = dbo.Division.DivisionGuid
LEFT OUTER JOIN dbo.Region ON dbo.Job.RegionGuid = dbo.Region.RegionGuid
LEFT OUTER JOIN dbo.JobType ON dbo.Job.JobTypeGuid = dbo.JobType.JobTypeGuid
WHERE dbo.job.CompanyJobId = 3505048 and where Ttl_Qty =

(SELECT case dbo.SourceType.CompanySourceTypeId
when 'PR' then SUM(dbo.ProductionEvent.Quantity)
end
AS Ttl_Qty
FROM dbo.Batch INNER JOIN
dbo.Event ON dbo.Batch.BatchGuid = dbo.Event.BatchGuid INNER JOIN
dbo.Job ON dbo.Event.JobGuid = dbo.Job.JobGuid INNER JOIN
dbo.ProductionEvent ON dbo.Event.EventGuid = dbo.ProductionEvent.EventGuid INNER JOIN
dbo.Item ON dbo.Event.ItemGuid = dbo.Item.ItemGuid INNER JOIN
dbo.Source ON dbo.ProductionEvent.SourceGuid = dbo.Source.SourceGuid INNER JOIN
dbo.SourceType ON dbo.Source.SourceTypeGuid = dbo.SourceType.SourceTypeGuid LEFT OUTER JOIN
dbo.Product ON dbo.ProductionEvent.ProductGuid = dbo.Product.ProductGuid left outer join
dbo.JobNoteEvent on Event.EventGuid = dbo.JobNoteEvent.EventGuid
WHERE dbo.Job.CompanyJobId = 3505048 and dbo.SourceType.CompanySourceTypeId = 'PR'
GROUP BY dbo.SourceType.CompanySourceTypeId)

ORDER BY dbo.Job.CompanyJobId

View 4 Replies View Related

Combine SQL Queries

Mar 29, 2008

Hi,

I wonder if anyone can help. All I am trying to do is combine three SQL SELECT queries into one. They are:

SELECT COUNT(ISNULL(Result, 0)) AS Win FROM Games WHERE (CurrentSeason = 'True') AND Result = 'Win'AND ([MatchType] = @MatchType)

SELECT COUNT(ISNULL(Result, 0)) AS Lose FROM Games WHERE (CurrentSeason = 'True') AND Result = 'Lose'AND ([MatchType] = @MatchType)

SELECT COUNT(ISNULL(Result, 0)) AS Draw FROM Games WHERE (CurrentSeason = 'True') AND Result = 'Draw'AND ([MatchType] = @MatchType)

As you can see they are all doing pretty much the same thing. I have experimented by using GROUP and HAVING but end up with no results. Sorry if this is obvious but I am new to SQL!

Many Thanks

View 2 Replies View Related

Combine Two Queries - Help Please

Jul 23, 2005

I have a table that has two dates in it, a date opened and a dateclosed. I would like to create one query to give me the number ofrecords that have been opened each month plus, and this is the hardpart the number of those records that have been closed each month. Ican get the result with two seperate queries but have been unable toget it combined into one query with three values for each month, i.e.,the month, the number opened and the number of those that were openedin the month that have been subsequently closed.Here's my two queries. If anyone can help I'd appreciate.SELECT COUNT(*) AS [Number Closed], LEFT(DATENAME(m, DateOpened),3) + '' + CAST(YEAR(DateOpened) AS Char(5)) AS [Month Opened]FROM tableWHERE (DateClosed IS NOT NULL)GROUP BY CONVERT(CHAR(7), DateOpened, 120), LEFT(DATENAME(m,DateOpened), 3)+ ' ' + CAST(YEAR(DateOpened) AS Char(5))ORDER BY CONVERT(CHAR(7), DateOpened, 120)SELECT COUNT(*) AS [Number Opened], LEFT(DATENAME(m, DateOpened),3) + '' + CAST(YEAR(DateOpened) AS Char(5)) AS [Month Opened]FROM tableGROUP BY CONVERT(CHAR(7), DateOpened, 120), LEFT(DATENAME(m,DateOpened), 3)+ ' ' + CAST(YEAR(DateOpened) AS Char(5))ORDER BY CONVERT(CHAR(7), DateOpened, 120)TIABill

View 2 Replies View Related

Combine 2 Queries

Jul 20, 2005

Hi,I have 2 queries that I need to join. I have the following tables:attendancelog :headeridreportmonthattlogstuds:headeridsidclass:sidclassstatusyearcodelogdatecdatemidThe result must return all the classes that appear in query2 but notin query1.I am not sure how to join the 2 queries.Help will be appreciated :-)ThanksQUERY1select sid fromattlogstuds studsinner joinattendancelog attlogon studs.headerid=attlog.headeridwhere reportmonth=10query2-- students learning excl. studs left before 1th oct.select class.SID from classleft outer JOIN ( select * from class where yearcode=26 and status=6and ( logdate <'20041001' or CDate< '20041001' )) c6 ON c6.sid = class.sid and c6.mid=class.midand c6.cdate >= class.cdatewhere class.yearcode=26 and class.status in (3,5) andclass.cdate<'20041101' and c6.sid is null

View 1 Replies View Related

Help: Need To Combine Multiple IF Queries

Apr 14, 2008

I hit a bit of a road block on a project I have been working on.  If anyone has a suggestion or a solution for how to combine my queries that use IFELSE that would be a huge help.  I noted my query below./* will remove for aspx page use */USE Database/* these params are on the page in drop down boxes*/DECLARE @ProductID int;DECLARE @BuildID int;DECLARE @StatusID int;/* static params for this sample */SET @ProductID = -1;SET @BuildID = -2SET @StatusID = -3/*the query that will build the datagrid.  currently this runs and produces three different result sets.How do I combine these statements so they produce a single set of results? */IF (@ProductID = -1) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (ProductID = @ProductID))IF (@BuildID = -2) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (BuildID = @BuildID))IF (@StatusID = -3) SELECT * FROM tblTestLog ELSE (SELECT * FROM tblTestLog WHERE (AnalystStatusID = @StatusID))

View 12 Replies View Related

Can Anyone Show Me How To Combine These Two SQL Queries Into One

Jan 29, 2004

Hello-

i have a fairly big SQL query that is used to display data into a datagrid. Each query grabs data from two seperate databases. Is there anyway to combine these queries into one so all the data appears in 1 datagrid and not 2.

here is the 1st query:

SQL = "SELECT sum(case when month(pb_report_shippers.shipper_date_time) = 1 then pb_report_shippers_lots.quantity else 0 end) as Jan ,sum(case when month(pb_report_shippers.shipper_date_time) = 2 then pb_report_shippers_lots.quantity else 0 end) as Feb ,sum(case when month(pb_report_shippers.shipper_date_time) = 3 then pb_report_shippers_lots.quantity else 0 end) as Mar ,sum(case when month(pb_report_shippers.shipper_date_time) = 4 then pb_report_shippers_lots.quantity else 0 end) as Apr ,sum(case when month(pb_report_shippers.shipper_date_time) = 5 then pb_report_shippers_lots.quantity else 0 end) as May ,sum(case when month(pb_report_shippers.shipper_date_time) = 6 then pb_report_shippers_lots.quantity else 0 end) as Jun ,sum(case when month(pb_report_shippers.shipper_date_time) = 7 then pb_report_shippers_lots.quantity else 0 end) as Jul ,sum(case when month(pb_report_shippers.shipper_date_time) = 8 then pb_report_shippers_lots.quantity else 0 end) as Aug ,sum(case when month(pb_report_shippers.shipper_date_time) = 9 then pb_report_shippers_lots.quantity else 0 end) as Sept ,sum(case when month(pb_report_shippers.shipper_date_time) = 10 then pb_report_shippers_lots.quantity else 0 end) as Oct ,sum(case when month(pb_report_shippers.shipper_date_time) = 11 then pb_report_shippers_lots.quantity else 0 end) as Nov ,sum(case when month(pb_report_shippers.shipper_date_time) = 12 then pb_report_shippers_lots.quantity else 0 end) as Dec FROM pb_customers INNER JOIN pb_jobs ON pb_customers.customer_id = pb_jobs.customer_id INNER JOIN pb_recipes_sub_recipes ON pb_jobs.recipe_id = pb_recipes_sub_recipes.recipe_id INNER JOIN pb_jobs_lots ON pb_jobs.job_id = pb_jobs_lots.job_id INNER JOIN pb_sub_recipes ON pb_recipes_sub_recipes.sub_recipe_id = pb_sub_recipes.sub_recipe_id INNER JOIN pb_report_shippers_lots ON pb_jobs_lots.intrack_lot_id = pb_report_shippers_lots.intrack_lot_id INNER JOIN pb_report_shippers ON pb_report_shippers_lots.job_id = pb_report_shippers.job_id AND pb_report_shippers_lots.shipper_id = pb_report_shippers.shipper_id WHERE pb_customers.customer_deleted <> 1 AND pb_jobs.job_deleted <> 1 AND pb_jobs_lots.lot_deleted <> 1 AND pb_report_shippers.shipper_date_time between cast('01/01/2003 00:01AM' as datetime) and cast('12/31/2003 23:59PM' as datetime)"


Here is the 2nd query:


SQL = "SELECT ISNULL(sum(case when month(nonconformance.nc_date) = 1 then nonconformance.nc_wafer_qty else 0 end),0) as Jan , ISNULL(sum(case when month(nonconformance.nc_date) = 2 then nonconformance.nc_wafer_qty else 0 end),0) as Feb ,ISNULL(sum(case when month(nonconformance.nc_date) = 3 then nonconformance.nc_wafer_qty else 0 end),0) as Mar ,ISNULL(sum(case when month(nonconformance.nc_date) = 4 then nonconformance.nc_wafer_qty else 0 end),0) as Apr , ISNULL(sum(case when month(nonconformance.nc_date) = 5 then nonconformance.nc_wafer_qty else 0 end),0) as May ,ISNULL(sum(case when month(nonconformance.nc_date) = 6 then nonconformance.nc_wafer_qty else 0 end),0) as Jun ,ISNULL(sum(case when month(nonconformance.nc_date) = 7 then nonconformance.nc_wafer_qty else 0 end),0) as Jul ,ISNULL(sum(case when month(nonconformance.nc_date) = 8 then nonconformance.nc_wafer_qty else 0 end),0) as Aug ,ISNULL(sum(case when month(nonconformance.nc_date) = 9 then nonconformance.nc_wafer_qty else 0 end),0) as Sept ,ISNULL(sum(case when month(nonconformance.nc_date) = 10 then nonconformance.nc_wafer_qty else 0 end),0) as Oct ,ISNULL(sum(case when month(nonconformance.nc_date) = 11 then nonconformance.nc_wafer_qty else 0 end),0) as Nov ,ISNULL(sum(case when month(nonconformance.nc_date) = 12 then nonconformance.nc_wafer_qty else 0 end),0) as Dec FROM nonconformance INNER JOIN nc_department on nonconformance.department_id = nc_department.department_id INNER JOIN nc_major_category ON nonconformance.major_category_id = nc_major_category.major_category_id AND nonconformance.status_id <> '5' WHERE nc_department.scrap_category = '1' AND nonconformance.nc_date between cast('01/01/2004 00:01AM' as datetime) and cast('12/31/2004 23:59PM' as datetime)"


I know there has to be someway to combine these into 1. The issue I have is they are in different databases.


ANY HELP would be appreciated.

View 2 Replies View Related

Combine Results From 2 Queries

Nov 9, 2005

I'm trying to create a list of orders in my db that has been created correctly (some orders are not dealt with correctly...) An order should go from "open -> assigned" to "assigned -> responded" status.

I got the following query:

select org.name, count(order) AS correct, NULL AS Total
from order
left join orderstatus o1 on order.id = o1.order_id
left join orderstatus o2 on order.id = o2.order_id
left join org on order.orgid on user.id
where
o1.status = 'Open -> Assigned'
and o2.status = 'Assigned -> Responded'
and o1.time_stamp < o2.time_stamp


This gives me a list of all organisations with the correct number of orders in the system...

But now I need to add the total number of tickets they got in the system. So I was thinking about a union with a query without the were constraints

UNION 'with the above query
select org.name, NULL AS correct, count(order) AS Total
from order
left join orderstatus o1 on order.id = o1.order_id
left join orderstatus o2 on order.id = o2.order_id
left join org on order.orgid on user.id

..but that gives me a list like this:

name correct total
org1 324 NULL
org1 NULL 423

How can I combine them, or maybe doing it a better way?

View 3 Replies View Related

Help: Need To Combine Multiple IF Queries

Apr 14, 2008

I hit a bit of a road block on a project I have been working on. If anyone has a suggestion or a solution for how to combine my queries that use IFELSE that would be a huge help. I noted my query below.

/* will remove for aspx page use */
USE Database

/* these params are on the page in drop down boxes*/
DECLARE @ProductID int;
DECLARE @BuildID int;
DECLARE @StatusID int;

/* static params for this sample */
SET @ProductID = -1;
SET @BuildID = -2
SET @StatusID = -3

/*
the query that will build the datagrid. currently this runs and produces three different result sets.
How do I combine these statements so they produce a single set of results?
*/

IF (@ProductID = -1) SELECT * FROM tblTestLog
ELSE (SELECT * FROM tblTestLog WHERE (ProductID = @ProductID))

IF (@BuildID = -2) SELECT * FROM tblTestLog
ELSE (SELECT * FROM tblTestLog WHERE (BuildID = @BuildID))

IF (@StatusID = -3) SELECT * FROM tblTestLog
ELSE (SELECT * FROM tblTestLog WHERE (AnalystStatusID = @StatusID))

View 15 Replies View Related

Combine Two SQL Queries With Separate Where Statements

Jun 18, 2008

I have two SQL queries that I would like to combine.  Each query is dependent on the same table, and the same rows, but they each have their own WHERE statements. I've thought about using some JOIN statements (left outer join in particular) but then I run into the problem of not having two separate tables, and don't see where I can put in two separate WHERE statements into the final query.  I've read into aliasing tables, but I'm not quite sure how that works (how to put it into code or a JOIN statement) , or if it would solve my question.  Do you have any ideas or examples of how to solve this scenario? 

View 9 Replies View Related

Combine Result From Diff Queries

Jun 18, 2008

I have 3 sql queries:ex:select * from table 1 where id = 2select * from table 1 where  name = 'name'select * from table 1 where date = 'date' I want to combine these three queries into one stored procedure.I am not sure how to do this.i want to display  some column data from these 3 queries on 3 table rows as:<td> colum1 </td><td> colum2 </td><td> colum3 </td>so my SP should return some datatable .any suggestiions 

View 3 Replies View Related

Combine 2 Queries In To One (joint Query)

May 3, 2007

I have these 2 queries that I need to combine into one. What is the best way of doing it? This website is made up of 293 tables so it gets confusing.


(Query 1)
SELECT category_products.category, category_products.product, category_products.position, data.base_price, data.custom_description, models.manufacturer_id, models.custom_search_text, models.name, models.image_location
FROM category_products, data, models
WHERE category_products.category = '36'
AND category_products.product = data.model_id
AND data.model_id = models.id
AND data.active = 'y'

$manufacturer_id=$data["manufacturer_id"];


(Query 2)
SELECT inventory_types.manufacturer_id, inventory_types.default_vendor_id, vendors.id, vendors.name
FROM inventory_types, vendors
WHERE inventory_types.manufacturer_id = '$manufacturer_id'
AND inventory_types.default_vendor_id = vendors.id

View 3 Replies View Related

Transact SQL :: Max Date Combine With Two Queries

Oct 18, 2015

I have 2 tables, i need to take the max date from PAY and Combine in MEN 

MEN 
======
id  |  Fname  
========
1   |  AAA
2      |   BBB
3      |   CCC

PAY
===
id    |    Tdate
=========
1    |   01.01.2015
1    |   02.01.2015
1    |   03.01.2015
2    |   06.01.2015
3    |   09.01.2015
3    |   10.01.2015i

I need to show this:

id  |  Fname  |  Tdate
=============
1   |  AAA      |   03.01.2015
2  |  BBB      |   06.01.2015
3   |  CCC      |  10.01.2015

View 5 Replies View Related

Combine 2 Queries To Produce One Result Table

Jan 9, 2014

I would like to pull all the columns from a table where the date column is within 6 months from the max date (i.e. Jul, Aug, Sep, Oct, Nov, & Dec). In addition to that, I would like to pull another column -the summary column - from the same table where the date = max(date) (Dec only).

I have written 2 queries and they produce the correct data. However, I don't know how to combine them into one resultant table. I tried to do a left join and had difficulties dealing with the different where statements from the 2 queries..

Here is query #1:

select investor, full_date, month_end_summary, category, loan_count
from cust_table
where datediff(month,full_date,(select max(full_date) from cust_table)) < 6
group by investor, full_date, month_end_summary, category, loan_count
order by investor, full_date

Here is query #2:

select investor, full_date, month_end_summary
from cust_table
where datediff(month,full_date,(select max(full_date) from cust_table)) =0
order by investor, full_date

Can they be combined into one query to produce one result table??

View 3 Replies View Related

Combine Delete Queries Running Separately

May 21, 2014

I have for delete queries which I run separately. Could I have a one statement to run instead.

DELETE FROM dbo.PatientHistory
FROM dbo.PatientHistory INNERJOIN TEST_PATS ON dbo.PatientHistory.PatientGuidDigest = TEST_PATS.PatientGuidDigest

DELETE FROM dbo.PostcodeScores
FROM dbo.PostcodeScores INNERJOIN TEST_PATS
ON dbo.PostcodeScores.PatientGuidDigest = TEST_PATS.PatientGuidDigest

[Code] ....

View 2 Replies View Related

Transact SQL :: Finding Syntax To Combine IN And CASE In A WHERE Clause?

Oct 29, 2015

I cannot seem to find the syntax to combine IN + CASE in a WHERE clause

WHERE
ses.BK_MS_SESSION <= '2015-03'
AND vis.CAT_DRAW_STATUS =
(CASE ses.BK_MS_SESSION
WHEN '2015-03' THEN vis.CAT_DRAW_STATUS
ELSE
CASE stat.BK_MS_VISIT_STATUS
WHEN 'T' THEN 'X'
ELSE vis.CAT_DRAW_STATUS
END
END
) IN ('D','R')

View 7 Replies View Related

Correct Syntax Using Variables Inside SQL Queries (SqlCommand)

Mar 30, 2008

I have a test page where I'm using SqlConnection and SqlCommand to update a simple database table (decrease a number).
I'm trying to figure out how to make a number in the database table to decrease by 1 each time a button is being pressed. I know how to update the number by whatever I want to, but I have no idea what the correct syntax is for putting variables inside the query etc. Like "count -1" for instance.
The database table is called "friday" and the one and only column is called "Tickets".
Here's the code behind the button:protected void Button1_Click(object sender, EventArgs e)
{SqlConnection conn;
conn = new SqlConnection("Data Source=***;Initial Catalog=***;Persist Security Info=True;User ID=***;Password=***");
conn.Open();int count = -1;
 SqlCommand cmd = new SqlCommand("select Tickets from friday", conn);
count = (int)cmd.ExecuteScalar();if (count > 0)
{
 string updateString = @"
update friday
set Tickets = 500" ;   <------ Here I want to set Tickets like "current count -1"SqlCommand cmd2 = new SqlCommand(updateString);
cmd2.Connection = conn;
cmd2.ExecuteNonQuery();
}
else
{
}
conn.Close();
}

View 3 Replies View Related

Odd Syntax Error With Shape Queries SQL2000/Win2003

Jul 20, 2005

Hi,I am running SQL Server 2000 SP3 on Windows Server 2003 and since recentlyhave a strange problem executing shape queries from COM+ components usingADO.Until 4 days ago, they worked, then from one moment to the next (I must havechanged something, but I have no clue what other than restoring a 1.2 GBdatabase) they started failing with this error:Microsoft OLE DB Provider for SQL Server error '80040e14'Syntax error or access violationI have no problem executing non-shape queries, it is just the shape queriesthat fail.Any clues what may have gone wrong? Or how I can fix it?Cheers,Rsa Myh

View 1 Replies View Related

Confused :-(

Sep 13, 2004

Having serious problems trying to insert date into database using sqladapter.update method gives an error saying "Converting DateTime from Character string". the funniest thing is that it works on my developement box, but when i upload to the server with thesame settings in my development box, it does not work.

View 2 Replies View Related

M Confused About Using Dbo And ..

Jan 4, 2007

Hi,
I will give someone a script that creates a database using :
create database mydatabase
my question: can I use myDatabase.dbo...... and myDatabase..Whatevertable in order to manipulate the database objects or should I be careful with putting dbo in my script.

The reason is that I will have to give the following script to someone to execute on his instance and I don t want it to fail.

The script creates a database mosaikDB737, create a table called FileListInput in that database and populates a second table called DBlistOutput with the list of names of all databases in the instance.

Please let me know if there are any (BAD) chances for the following script to fail.


create database mosaikDB737
go
use mosaikDB737

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FileListInput]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[FileListInput](
[FileName] [char](50) NULL
) ON [PRIMARY]
END

use master
select name into mosaikDB737.dbo.DBlistOutput from sysdatabases where name not in ('master','tempdb','model','msdb')
select * from mosaikDB737.dbo.DBlistOutput

View 5 Replies View Related

So Confused

Dec 15, 2007

OK, so I'm new to SQL server, which I'm sure you'll all see from my question below.

I am trying to migrate an access DB with queries over to sql server 2005. simple queries I can handle, but I've come accross a query that calls another query and does an update based off of my first query. The below queries work perfectly fine in access but I dont know how to get this going in SQL server. From my VERY minimal understanding in of SQL server i thought we couldnt call stored procedure (query1) and have it update the underlying tables. If I'm wrong, please show me how its done, If I'm right please show me the right way of doing this.
If you see spelling errors in the queries please ignore, that is not the full queries, it is just a cut down version to explain what I need to be able to do.

Query1

SELECT table1.assettag, table1.City, table2.Status, table2.ScheduleItems
FROM Table1 Inner join on table1.assettag = table2.assettag
where Status = "Scrubbed" or Status = "Initial"


Query2

Update Query1
SET query1.ScheduledItems = True
Where query1.Status = Scrubbed


thank you for any information or help.

View 3 Replies View Related

Very New And Very Confused!!

Mar 5, 2008

Hi,

I have never used coding before (just learning) and I need to collect username and password and check it against my SQL database. I am using the below code as a sample guide for me to figure this out. Does anyone point me to a sample code page that I may look at that actually is doing what I want to do??

Sondra





Protected Sub submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles submit.Click

Dim myReader As Data.SqlClient.SqlDataReader

Dim mySqlConnection As Data.SqlClient.SqlConnection

Dim mySqlCommand As Data.SqlClient.SqlCommand





'Establish the SqlConnection by using the configuration manager to get the connection string in our web.config file.

mySqlConnection = New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ToString())

Dim sql As String = "SELECT UserLogonID, UserPassword FROM MyUsers WHERE UserLogonID = '" & Me.userid.Text & "' " And "Userpassword = '" & Me.psword.Text & "'"

mySqlCommand = New Data.SqlClient.SqlCommand(sql, mySqlConnection)





Try

mySqlConnection.Open()

myReader = mySqlCommand.ExecuteReader()





If (myReader.HasRows) Then

'Read in the first row to initialize the DataReader; we will on read the first row.

myReader.Read()





Dim content As ContentPlaceHolder

content = Page.Master.FindControl("main")

Dim lbl As New Label()

lbl.Text = "The Last Name you choose, " & Me.dlLastName.Text & ", has a first name of " & myReader("FirstName")

content.Controls.Add(lbl)

End If

Catch ex As Exception

Console.WriteLine(ex.ToString())

Finally

If Not (myReader Is Nothing) Then

myReader.Close()

End If

If (mySqlConnection.State = Data.ConnectionState.Open) Then

mySqlConnection.Close()

End If

End Try





End Sub

View 1 Replies View Related

Confused About Permission

Aug 29, 2007

I read a few articles on best SQL practices and they kept coming back to using a Least Privileged Account.  So I did so and gave that account read only permissions.  The articles also said to do updates use Stored Procedures - so I created stored procedures for updating/deleting data.So here's my problem - I connect to the database using the Least Privileged Account, I use the Stored Procedures, but .NET keeps saying I lack permissions.  If I GRANT the Least Privileged Account UPDATE/DELETE permission on the table, the Stored Procedures run perfectly.  But isn't that EXACTLY what I'm trying to avoid?My greatest concern is someone hacks my website and using the Least Privileged Account, they delete all my data using that account.  So I don't want to give the Least Privileged Account the Update/Delete privileges.Thanks a MILLION in advance! 

View 3 Replies View Related

Nullable Got Me Confused

May 30, 2006

I have just started on a project which will be based on an existing MS SQL Server database. It has many columns which can be, and sometimes are, null. My basic DataReader code throws an SqlNullValueException when I try to GetInt32 but not when I try GetString. Why the difference?
Also, how do I model my class? Do I have to make all fields into nullable types? If I do that I notice a simple GridView will not show a column for that field! I am confused.

View 3 Replies View Related

SP - Dazed And Confused

Mar 13, 2000

Hello,

I am calling a sql 7.0 stored procedure (sp) from an active server page(asp).

The sp is a simple insert. I need to read the return the value of the sp in my asp. If the insert is
successful, my return value is coming back correctly (to whatever i set it)....but if there is an error
such as a Uniqueness Constraint, I can't get the return code(set in the SP) to come back to the ASP.
It comes back blank. (The literature I've read says that processing should continue in the SP, so you
can perform error processing...is that right?)

I set the return var in my ASP as:
objCommand.Parameters.Append objCommand.CreateParameter("return",_
adInteger,adParamReturnValue,4)
and read it back as:
strReturn = objCommand.Parameters("return").Value


In my SP I simply do;

INSERT blah blah
if @@error = 0
return(100)
else
return(200)

(Idon't ever get back "200")

Any ideas???

Thanks for your help.

View 1 Replies View Related

Confused About Replication

Dec 18, 2006

Hi

I have study Microsoft online books for few days no, about repliction but I'am even more confused about replication. please help somebody

My goal:

I have been written a GPS program that has an database.
The database will be replicated to an central web server.
That will say one web server and x numbers of laptops that has same GPS program. :)
All laptops uses internet to replicate.

Now... I have no problem to create publications and subscriptions on server BUT HOW do I do it on client????? :confused:

Microsoft do not write nothing about client side of replication. everyting is SERVER, SERVER,SERVER and SERVER.The Microsoft HOW TO is only How to click forward, its dosen't really explain anything.

Problaby I need some kind of an database on client and subscription to make the replication. :confused:

please help me, I'am almost finished with my project only replication part is over my head :(

if someone can point me to right direction in this issue. I would be greateful. :cool:

Thanks

KK

View 13 Replies View Related

Xp_sendMail (Confused)????

Jul 2, 2002

I want to use xp_sendmail against a database other than the master. When I run a test using the master database, it sends a test message w/no problems. However when I try to use xp_sendmail against a database I've created, it gives me an error stating:

Server: Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'xp_sendmail'.

How can I use xp_sendmail using a dabase other than Master?? Please Help.

View 1 Replies View Related

Confused Joins

Mar 21, 2007

I am using this query to create a single transactions from data that is distributed over several databases. So essentially i have created several variable tables and now I have to join them together.
So what I wanted to have happen was display all rows from temptalbel and then join the other tables to create one transaction row. The problem that occurs is within the where statement and I dont understand why. In some cases, you can have two instances of x but y will be different. In that case the joins work perfectly. In the event that there are only a single instance of x associated with a single instance of y this join does not work. Can anyone help me understand why this is happening?

select somedata, somedata, somedata, somedata
From kpi..temptablel l
left outer join @temps s on l.x = s.x
left outer join @tempf f on l.x = f.x
left outer join kpi..temptablee e on l.x = e.x
left outer join @tempn n on l.x = n.x

where l.y = s.y and l.y = f.y and l.y = e.y and l.y = n.y


The Yak Village Idiot

View 4 Replies View Related

Confused. Need Some Help About SQL Coding

Jun 14, 2007

hi im a little bit confused. are the two pieces of code similar? what are the differences. i really need to know that coz i wont get access to a SQL machine until monday.


selectlastname
fromemp
wheresex = 'F' and
salary>(selectavg(salary)
fromemp
group by sex
havingsex='M')



selectlastname
fromemp
wheresex = 'F' and
salary>(selectavg(salary)
fromemp
wheresex='M')


also is it wise to use Group by and having in sub-queries?

View 2 Replies View Related

Confused About Creating A Dup Mdf

Mar 13, 2008

Hi
I have an slq Express mdf at path X and I copy it to path Y. When I open it up (from the Y path) using sql mgmt studio, it shows that it's from path X.
Why? How can I get sql mgmt studio to recognize it as a separate mdf, distinct from the one at path X?

TIA

rank beginner

View 2 Replies View Related

Alias Has Confused Me.

Jul 23, 2005

I'm trying to learn how to make and use aliases for two tables in inthis update statement:ALTER PROCEDURE dbo.UpdateStatusAS UPDATE dbo.npfieldsSET Status = N'DROPPED'FROM dbo.npfields NPF, dbo.importparsed IMPLEFT JOIN IMPON (NPF.pkey = IMP.pkey)WHERE (IMP.pkey IS NULL) AND((NPF.Status = N'ERR1') OR (NPF.Status = N'ERR2') OR (NPF.Status =N'ERR3'))I thought I could define the aliases in the FROM statement.I'm using Access as a front end to SQL server if that makes adifference in the queries.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved