Collation problem: queries

Showing posts with label queries. Show all posts

Thursday, March 29, 2012

Combining two queries

These similar queries do much the same thing: the first one gets a list of ticket ID's that have been bought as 'standalone' tickets by a particular user, along with the total quantity they purchased. The second one also gets a list of ticket ID's along with the quantity purchased by that user, but the list of ID's is driven by tickets that appear in their basket as part of packages, instead of standalone tickets.

I hope that's clear; if not, maybe the SQL will make it clearer:

SELECT
[tblTickets].[id] AS TicketId,
SUM([tblBasket].[ticket_quantity]) AS SingleTicketsTotal
FROM
[tblOrders]
INNER JOIN [tblBasket] ON [tblBasket].[order_id] = [tblOrders].[id]
INNER JOIN [tblTickets] ON [tblTickets].[id] = [tblBasket].[ticket_id]

WHERE [tblOrders].[id] IN (SELECT [id] FROM [tblOrders] WHERE [tblOrders].[user_id] = @.userID AND ([tblOrders].[order_status]=@.purchasedOrder OR [tblOrders].[id]=@.currentSessionOrder))

GROUP BY [tblTickets].[id]

SELECT
[tblCombinations_Tickets].[ticket_id] AS cTicketId,
SUM([tblBasket].[ticket_quantity]*[tblCombinations_Tickets].[quantity]) AS PackageTicketsTotal
FROM
[tblOrders]
INNER JOIN [tblBasket] ON [tblBasket].[order_id] = [tblOrders].[id]
INNER JOIN [tblCombinations_Tickets] ON [tblCombinations_Tickets].[combination_id] = [tblBasket].[combination_id]

WHERE [tblOrders].[id] IN (SELECT [id] FROM [tblOrders] WHERE [tblOrders].[user_id] = @.userID AND ([tblOrders].[order_status]=@.purchasedOrder OR [tblOrders].[id]=@.currentSessionOrder))

GROUP BY [tblCombinations_Tickets].[ticket_id]

I need to combine these. So that I get one result set with: ticketID, quantity bought as standalone, quantity bought as part of package.

I can't figure it out. I've tried inner joins, outer joins, left joins, right joins, nested subqueries and, briefly, banging on the screen. But every time, what happens is that I only get the rows where the ticket ID occurs in both queries. I need everything.

This has got to be laughably simple. But I'm stuck :( Can anyone help?first query goes here
UNION ALL
second query goes here|||first query goes here
UNION ALL
second query goes here

Fantastic :D I feel dim and enlightened at the same time :S Thankyou :)|||Fantastic :D I feel dim and enlightened at the same time :S Thankyou :)Rudy does have that affect :D

Tuesday, March 27, 2012

Combining Queries/ Results

I have created a search interface for a large table and I allow users to search on keywords. The users can enter multiple keywords and I build a SQL based on their input to search a full-text indexed table.
However the users want to be able to search like an old system they had, where they enter single words and then combine their searches to drill-down into the results.
What would be the best method to combine searches?
At the moment I can create a merged query from 2 queries if they have searched using single words, but I know down the line it will get far more complicated if they keep combining and merging even with multiple word entries.
Each time they search I store the 'where' section of each query, then if they choose to combine I have a function to build a new query through arrays (to eliminate duplicates and sort etc)
Is there a better way in SQL to combine queries as sometimes the logic of the combined query means no results are returned (because of OR/ AND conditions in the wrong places etc)
e.g.
1. Select count(ID) as myCount FROM myTable where (CONTAINS(title,'"run"') OR CONTAINS(subject,'"run"'))
2. Select count(ID) as myCount FROM myTable where (CONTAINS(title,'"level"') OR CONTAINS(subject,'"level"'))
Combined using my function creates:
Select count(ID) as myCount FROM myTable where (contains(title,'"level"') AND contains(title,'"run"')) OR (contains(subject,'"level"') AND contains(subject,'"run"'))

When I combine I'm drilling down, so if the first query returns a count of 400 (where thetitleORsubjectcontains 'run') and then the second query returns 600 records (where thetitleORsubjectcontains 'level') I need to combine so that I'm looking for records where thetitlecontains both keywords 'run' AND 'level' OR else thesubjectcontains both 'run' AND 'level' and I end up with say 50 records where the title has both keywords OR the subject holds both words.
I think the main trouble lies if they try combine a previously combines search with a new search. here my logic gets totally thrown and I'm not sure how to handle soemthing like this. Has anyone got any ideas or experience with this kind of functionality? In SQL or even in vb.net is there a method to combine searches easily?

You don't need to build it like you are. Just keep adding ANDswith the appropriate conditions. SQL will figure it out.
Example:
S1: WHERE (CONTAINS(title,'"run"') OR CONTAINS(subject,'"run"')
S2: WHERE (CONTAINS(title,'"run"') OR CONTAINS(subject,'"run"')
AND (CONTAINS(title,'"level"') OR CONTAINS(subject,'"level"'))
S3: WHERE (CONTAINS(title,'"run"') OR CONTAINS(subject,'"run"')
AND (CONTAINS(title,'"level"') OR CONTAINS(subject,'"level"'))
AND (CONTAINS(title,'"blah"') OR CONTAINS(subject,'"blah"'))
...

Combining PIVOT and INSERT queries

Can someone please help me modify the following pivot query into an INSERT INTO query (i.e. results are exported into a new table)...

SELECT RespondantID, [1]As Q1, [2]As Q2, [3]As Q3, [4]As Q4, [5]As Q5, [6]As Q6, [7]As Q7, [8]As Q8, [9]As Q9, [10]As Q10FROM (SELECT RespondantID, QuestionID, AnswerFROM [3_Temp]WHERE SurveyID=1)AS preData PIVOT (MAX(Answer)FOR QuestionIDIN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10]) )AS dataORDER BY RespondantID

Thanks,

Martin

You can use a CTE and a SELECT into to get your pivot result to a new table. You need to remove ORDER BY RespondantID clause first.

Here is the sql script.

WITH mycte

(SELECT RespondantID, [1]AS Q1, [2]AS Q2, [3]AS Q3, [4]AS Q4, [5]AS Q5, [6]AS Q6, [7]AS Q7, [8]AS Q8, [9]AS Q9, [10]AS Q10

FROM(SELECT RespondantID, QuestionID, Answer

FROM [3_Temp]

WHERE SurveyID= 1)AS preDataPIVOT(MAX(Answer)FOR QuestionIDIN([1], [2], [3], [4], [5], [6], [7], [8], [9], [10]))AS data

)

SELECT RespondantID, [Q1], [Q2], [Q3], [Q4], [Q5], [Q6], [Q7], [Q8], [Q9], [Q10]INTO [NewtableResult]FROM mycte

Combining Pass-Through Queries into a Stored Proc.

Is it possible to combine multiple Views into a Stored Procedure? Can I reference the results from one (1) View within the stored procedure? If so, how would I go about it?
Thanks!!Refer to Books online for Using Pass-Through Queries as Tables topic.

combining multiple tables into a single flat file destination

Hi,

I want to combine a series of outputs from tsql queries into a single flat file destination using SSIS.

Does anyone have any inkling into how I would do this.

I know that I can configure a flat file connection manager to accept the output from the first oledb source, but am having difficulty with subsequent queries.

e.g. output

personID, personForename, personSurname,
1, pf, langan

***Roles
roleID, roleName
1, developer
2, architect
3, business analyst
4, project manager
5, general manager
6, ceo

***joinPersonRoles
personID,roleID
1,1
1,2
1,3
1,4
1,5
1,6

Use Merge Joins to join your data sources. You'll need to use more than one because the Merge Join uses two inputs only.

http://msdn2.microsoft.com/en-us/library/ms141775.aspx

|||

You can do the merge join transformations as phil stated, or use a series of lookup transformations, or you could simply do a join on your initial data source query...

select out.personID, out.personForename, out.personSurname, role.roleID, role.roleName

from output as out

INNER JOIN

joinPersonRoles as pr

pr.personID = out.personID

INNER JOIN

roles as role

role.roleID = pr.roleID

Do you want your output to have a single row per person or are multiple rows ok? If you need it all in a single row you will also need to use a pivot transformation (or use pivot in your t-sql statement).

|||Performing the join in the source query via T-SQL as EWisdahl stated is the best route because it lets the database engine do the work.|||Ah, but you're missing my question.

I know how to do joins in order to produce a single recordset to output to a flat file.

I don't want to output a single set of records. I want to produce 3 sets, and output them all to the same file.

the example output I provided is exactly as I want the flat file.

Basically I want to run 3 data flow tasks in sequential order that appends their own output to the flat file destination.|||

So make three data flow tasks and three flat file connection managers, each referencing the same file.

Hook the data flow tasks up together in the control flow to enforce precedence.

What's the problem, I guess? It sounds like you've got it figured out: "Basically I want to run 3 data flow tasks in sequential order that appends their own output to the flat file destination."

|||

This is an odd request, however, you could potentially build up your own csv record.

Have each record be a single column text field and use a derived column transformation to string all of your current columns together for each of the sources.

After you pull these together do a union all.

If needed, you can do a select to grab the metadata information (i.e. table and column names) to push into the output as well. (i.e. select 'tablename'; select 'column1, column2, columnN'; etc...)

:edit - phil once again provided a working answer above while I was typing ... :

|||

great.. thank you so much!

Sunday, March 25, 2012

combining many contains()

Hi all,
I have a nvarchar(255) column on a tblKeyword table with many queries in a
verified FTS form. i.e-
"Microsoft Corporation"
("Hewelet Packard") OR HP
Google OR Froogle
Sun
I want to issue a combined FTS query that will gather all (*) information
from the indexed table, based on all existing queries in the tblKeyword
table.
Ie, something like:
SELECT * FROM myftstable
WHERE CONTAINS(*,[("Microsoft Corporation") OR (("Hewelet Packard") OR HP)
OR (Google OR Froogle) OR (Sun)])
Is it possible to do it dynamiclly in some sort? other then connecting all
strings and sending them all to the contains?
Thanks!
Guy,
Yes, there is a way to do this via a stored proc:
use pubs
go
-- DROP PROCEDURE usp_FTSearchPubsInfo
CREATE PROCEDURE usp_FTSearchPubsInfo ( @.vcSearchText varchar(7800))
AS
declare @.s as varchar (8000)
set @.s='select pub_id, pr_info from pub_info where
contains(pr_info,'+''''+@.vcSearchText+''''+')'
exec (@.s)
go
-- returns 2 rows
EXEC usp_FTSearchPubsInfo '("books" and "publisher")'
go
-- Using your example:
EXEC usp_FTSearchPubsInfo '("Microsoft Corporation" or ("Hewelet Packard" or
"HP") or ("Google" or "Froogle") or ("Sun"))'
Regards,
John
"Guy Brom" <guy_brom@.yahoo.com> wrote in message
news:Oz8p5UXKEHA.3728@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I have a nvarchar(255) column on a tblKeyword table with many queries in a
> verified FTS form. i.e-
> "Microsoft Corporation"
> ("Hewelet Packard") OR HP
> Google OR Froogle
> Sun
> I want to issue a combined FTS query that will gather all (*) information
> from the indexed table, based on all existing queries in the tblKeyword
> table.
> Ie, something like:
> SELECT * FROM myftstable
> WHERE CONTAINS(*,[("Microsoft Corporation") OR (("Hewelet Packard") OR HP)
> OR (Google OR Froogle) OR (Sun)])
> Is it possible to do it dynamiclly in some sort? other then connecting all
> strings and sending them all to the contains?
> Thanks!
>
|||Hi John,
I meant how to do it programatically, so that the queries exists in
tblKeyword will be populated automatically as a long (@.vcSearchText varchar)
Guy
"John Kane" <jt-kane@.comcast.net> wrote in message
news:%23nWpPQYKEHA.1272@.tk2msftngp13.phx.gbl...
> Guy,
> Yes, there is a way to do this via a stored proc:
> use pubs
> go
> -- DROP PROCEDURE usp_FTSearchPubsInfo
> CREATE PROCEDURE usp_FTSearchPubsInfo ( @.vcSearchText varchar(7800))
> AS
> declare @.s as varchar (8000)
> set @.s='select pub_id, pr_info from pub_info where
> contains(pr_info,'+''''+@.vcSearchText+''''+')'
> exec (@.s)
> go
> -- returns 2 rows
> EXEC usp_FTSearchPubsInfo '("books" and "publisher")'
> go
> -- Using your example:
> EXEC usp_FTSearchPubsInfo '("Microsoft Corporation" or ("Hewelet Packard"
or[vbcol=seagreen]
> "HP") or ("Google" or "Froogle") or ("Sun"))'
> Regards,
> John
>
>
> "Guy Brom" <guy_brom@.yahoo.com> wrote in message
> news:Oz8p5UXKEHA.3728@.TK2MSFTNGP12.phx.gbl...
a[vbcol=seagreen]
information[vbcol=seagreen]
HP)[vbcol=seagreen]
all
>
|||Guy,
I'm not sure what you're asking for here... Could you provide some examples?
Are you looking for a client-side (IE-based) solution or a server-side
(T-SQL based) solution? If the former, you may want to checkout KB article
246800 (Q246800) "INF: Correctly Parsing Quotation Marks in FTS Queries" at:
http://support.microsoft.com//defaul...b;EN-US;246800
Regards,
John
"Guy Brom" <guy_brom@.yahoo.com> wrote in message
news:OMfprgdKEHA.1312@.TK2MSFTNGP12.phx.gbl...
> Hi John,
> I meant how to do it programatically, so that the queries exists in
> tblKeyword will be populated automatically as a long (@.vcSearchText
varchar)[vbcol=seagreen]
> Guy
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:%23nWpPQYKEHA.1272@.tk2msftngp13.phx.gbl...
Packard"[vbcol=seagreen]
> or
in[vbcol=seagreen]
> a
> information
tblKeyword
> HP)
> all
>
|||John hi,
I need a server-side solution (T-SQL based) for connecting all of the
records appear in tblKeyword into 1 long string. Is it possible?
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OJ%238ruhKEHA.1340@.TK2MSFTNGP12.phx.gbl...
> Guy,
> I'm not sure what you're asking for here... Could you provide some
examples?
> Are you looking for a client-side (IE-based) solution or a server-side
> (T-SQL based) solution? If the former, you may want to checkout KB article
> 246800 (Q246800) "INF: Correctly Parsing Quotation Marks in FTS Queries"
at:[vbcol=seagreen]
> http://support.microsoft.com//defaul...b;EN-US;246800
> Regards,
> John
>
> "Guy Brom" <guy_brom@.yahoo.com> wrote in message
> news:OMfprgdKEHA.1312@.TK2MSFTNGP12.phx.gbl...
> varchar)
> Packard"
queries[vbcol=seagreen]
> in
> tblKeyword
OR[vbcol=seagreen]
connecting
>
|||Hi Guy,
Ok. I looked back over your original posting and I didn't realize that you
had two tables - tblKeyword and myftstable - and that in affect you wanted
to "pass" the tblKeyword table values to CONTAINS search_condition clause.
I'm sure there are other ways of doing this, but for now, I've developed two
cursor based solutions - assuming I'm understanding your question properly:
The below examples use the database (pubs) and the FT-enabled table
(authors) and the table (keyword) is your tblKeyword table:
use pubs
go
create table keyword(kword varchar(50))
go
insert into keyword values ('white')
insert into keyword values ('("john" or "paul")')
insert into keyword values ('Yokomoto')
go
select * from keyword
go
-- Simple Cursor Fetch with a CONTAINS statement...
SET NOCOUNT ON
DECLARE keyword_cursor CURSOR FAST_FORWARD
FOR
select kword from keyword
OPEN keyword_cursor
DECLARE @.keyword varchar(50)
FETCH NEXT FROM keyword_cursor INTO @.keyword
WHILE (@.@.fetch_status <> -1)
BEGIN
select * from authors where contains(*,@.keyword)
FETCH NEXT FROM keyword_cursor INTO @.keyword
END
CLOSE keyword_cursor
DEALLOCATE keyword_cursor
SET NOCOUNT OFF
GO
-- Complex Cursor Fetch into a temp table and then select from it...
set nocount on
DECLARE keyword_cursor CURSOR FAST_FORWARD
FOR
select kword from keyword
CREATE TABLE #authors_PK (author_pk char(11))
OPEN keyword_cursor
DECLARE @.keyword varchar(50), @.author_pks char(11), @.sql nvarchar(600)
-- Fetch the first row in the cursor.
FETCH NEXT FROM keyword_cursor INTO @.keyword
WHILE @.@.FETCH_STATUS = 0
BEGIN
select @.sql = 'insert into #authors_PK (author_pk) select au_id from
authors where contains(*, ''' + @.keyword + ''')'
exec(@.sql)
FETCH NEXT FROM keyword_cursor INTO @.keyword
END
CLOSE keyword_cursor
DEALLOCATE keyword_cursor
select * from #authors_PK
drop table #authors_PK
go
-- Clean-up
drop table keyword
go
Let me know if this is what you're looking for.
Regards,
John
"Guy Brom" <guy_brom@.yahoo.com> wrote in message
news:urOPK8jKEHA.3492@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> John hi,
> I need a server-side solution (T-SQL based) for connecting all of the
> records appear in tblKeyword into 1 long string. Is it possible?
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:OJ%238ruhKEHA.1340@.TK2MSFTNGP12.phx.gbl...
> examples?
article[vbcol=seagreen]
> at:
varchar(7800))[vbcol=seagreen]
> queries
Packard")
> OR
> connecting
>
|||Exactly!!
Thank you John!
"John Kane" <jt-kane@.comcast.net> wrote in message
news:uwMwhCnKEHA.1312@.TK2MSFTNGP12.phx.gbl...
> Hi Guy,
> Ok. I looked back over your original posting and I didn't realize that you
> had two tables - tblKeyword and myftstable - and that in affect you wanted
> to "pass" the tblKeyword table values to CONTAINS search_condition clause.
> I'm sure there are other ways of doing this, but for now, I've developed
two
> cursor based solutions - assuming I'm understanding your question
properly:[vbcol=seagreen]
> The below examples use the database (pubs) and the FT-enabled table
> (authors) and the table (keyword) is your tblKeyword table:
> use pubs
> go
> create table keyword(kword varchar(50))
> go
> insert into keyword values ('white')
> insert into keyword values ('("john" or "paul")')
> insert into keyword values ('Yokomoto')
> go
> select * from keyword
> go
> -- Simple Cursor Fetch with a CONTAINS statement...
> SET NOCOUNT ON
> DECLARE keyword_cursor CURSOR FAST_FORWARD
> FOR
> select kword from keyword
> OPEN keyword_cursor
> DECLARE @.keyword varchar(50)
> FETCH NEXT FROM keyword_cursor INTO @.keyword
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> select * from authors where contains(*,@.keyword)
> FETCH NEXT FROM keyword_cursor INTO @.keyword
> END
> CLOSE keyword_cursor
> DEALLOCATE keyword_cursor
> SET NOCOUNT OFF
> GO
> -- Complex Cursor Fetch into a temp table and then select from it...
> set nocount on
> DECLARE keyword_cursor CURSOR FAST_FORWARD
> FOR
> select kword from keyword
> CREATE TABLE #authors_PK (author_pk char(11))
> OPEN keyword_cursor
> DECLARE @.keyword varchar(50), @.author_pks char(11), @.sql nvarchar(600)
> -- Fetch the first row in the cursor.
> FETCH NEXT FROM keyword_cursor INTO @.keyword
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> select @.sql = 'insert into #authors_PK (author_pk) select au_id from
> authors where contains(*, ''' + @.keyword + ''')'
> exec(@.sql)
> FETCH NEXT FROM keyword_cursor INTO @.keyword
> END
> CLOSE keyword_cursor
> DEALLOCATE keyword_cursor
> select * from #authors_PK
> drop table #authors_PK
> go
> -- Clean-up
> drop table keyword
> go
> Let me know if this is what you're looking for.
> Regards,
> John
>
>
> "Guy Brom" <guy_brom@.yahoo.com> wrote in message
> news:urOPK8jKEHA.3492@.TK2MSFTNGP09.phx.gbl...
> article
Queries"
> varchar(7800))
> Packard")
>

combining datasets

I have two queries, one that brings back data from a cube and another that
returns records from a db. Is there anyway inside reporting services to
combine the two data sets?Only through subreports.
"Jessica C" <jesscobbe@.hotmail.com> wrote in message
news:ucK4AE02FHA.3272@.TK2MSFTNGP09.phx.gbl...
> I have two queries, one that brings back data from a cube and another that
> returns records from a db. Is there anyway inside reporting services to
> combine the two data sets?
>

Thursday, March 22, 2012

combining 2 sql queries

hello everyone

there is a smalllll problem facing mee...well i want to combine the result of 2 queries together
, the queries are :

select x1,x2,x3 from Table1 inner join Table2 on Table1.x1=table2.y inner join table3 on table1.2 = table3.z where table1.anything = 5

and the other query

select x1, x2 from Table1 where table1.anything = 5

is there anyway??

Thank youyou have to union the two queries.
Note using union, each select has to have the same output columns (same columncoun, and naming). Using Dummy Columns will help. 'In order to place a condition you have to treat the Resultset from the Union query as an table. See example below.
e.g.
for your two selects the syntax will be:


SELECT * FROM
(
 select x1,x2,x3, table1.anything from Table1 inner join Table2 on Table1.x1=table2.y inner join table3 on table1.2 = table3.z
 UNION
 select x1, x2, '' as x3, anything from Table1
) t
WHERE t.anything = 5

Further information
http://www.really-fine.com/SQL_union.html
or
MSDN|||hii

im really thankfull for u, it worked perfectly :)

thank you again

best regards

combining 2 queries?

HI I have two queries I am trying to combine
the first one simply returns several integers
for the second part I am trying to insert these integers into another table.
Code below does not work together. Do I need an integer array for @.logids
SELECT @.logids field1 FROM dbo.table1
WHERE POC_ID = 15
INSERT INTO table2
(field2)
VALUES
(@.logids)
--
Paul G
Software engineer.You got it backwards, try
INSERT INTO table1 (field1)
SELECT table2.field2 FROM table2;
"Paul" wrote:
> HI I have two queries I am trying to combine
> the first one simply returns several integers
> for the second part I am trying to insert these integers into another table.
> Code below does not work together. Do I need an integer array for @.logids
> SELECT @.logids field1 FROM dbo.table1
> WHERE POC_ID = 15
> INSERT INTO table2
> (field2)
> VALUES
> (@.logids)
> --
> Paul G
> Software engineer.|||Paul,
If I understand your question correctly, it would be just
insert into table2 (field2)
SELECT field1 FROM dbo.table1
WHERE POC_ID = 15
hth
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:2E6FF844-22F3-466C-B527-D6EED7081A54@.microsoft.com...
> HI I have two queries I am trying to combine
> the first one simply returns several integers
> for the second part I am trying to insert these integers into another
> table.
> Code below does not work together. Do I need an integer array for @.logids
> SELECT @.logids field1 FROM dbo.table1
> WHERE POC_ID = 15
> INSERT INTO table2
> (field2)
> VALUES
> (@.logids)
> --
> Paul G
> Software engineer.|||Hi thanks for the response,
tried this below, but nothing is getting inserted,
SET @.pocid = 15
INSERT INTO table2
(field2 )
SELECT field1 FROM table1 WHERE POC_ID = @.pocid
since I have no Nulls allowed for table to I get the error can not insert
NULL.
so it is trying to insert a NULL
"Ash" wrote:
> You got it backwards, try
> INSERT INTO table1 (field1)
> SELECT table2.field2 FROM table2;
>
> "Paul" wrote:
> > HI I have two queries I am trying to combine
> > the first one simply returns several integers
> > for the second part I am trying to insert these integers into another table.
> > Code below does not work together. Do I need an integer array for @.logids
> >
> > SELECT @.logids field1 FROM dbo.table1
> > WHERE POC_ID = 15
> >
> > INSERT INTO table2
> > (field2)
> > VALUES
> > (@.logids)
> >
> > --
> > Paul G
> > Software engineer.|||Hi thanks for the response. I tried this but it tries to insert NULL
get error statement Cannot insert the value NULL into column 'field2', table
'table2'; column does not allow nulls. INSERT fails.
The statement has been terminated.
When I try the select statement without the insert it returns 83 values in
the
database output.
"Quentin Ran" wrote:
> Paul,
> If I understand your question correctly, it would be just
> insert into table2 (field2)
> SELECT field1 FROM dbo.table1
> WHERE POC_ID = 15
> hth
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2E6FF844-22F3-466C-B527-D6EED7081A54@.microsoft.com...
> > HI I have two queries I am trying to combine
> > the first one simply returns several integers
> > for the second part I am trying to insert these integers into another
> > table.
> > Code below does not work together. Do I need an integer array for @.logids
> >
> > SELECT @.logids field1 FROM dbo.table1
> > WHERE POC_ID = 15
> >
> > INSERT INTO table2
> > (field2)
> > VALUES
> > (@.logids)
> >
> > --
> > Paul G
> > Software engineer.
>
>|||It is working now thanks for the help.
"Ash" wrote:
> You got it backwards, try
> INSERT INTO table1 (field1)
> SELECT table2.field2 FROM table2;
>
> "Paul" wrote:
> > HI I have two queries I am trying to combine
> > the first one simply returns several integers
> > for the second part I am trying to insert these integers into another table.
> > Code below does not work together. Do I need an integer array for @.logids
> >
> > SELECT @.logids field1 FROM dbo.table1
> > WHERE POC_ID = 15
> >
> > INSERT INTO table2
> > (field2)
> > VALUES
> > (@.logids)
> >
> > --
> > Paul G
> > Software engineer.|||it is working now thanks for the help.
"Quentin Ran" wrote:
> Paul,
> If I understand your question correctly, it would be just
> insert into table2 (field2)
> SELECT field1 FROM dbo.table1
> WHERE POC_ID = 15
> hth
>
> "Paul" <Paul@.discussions.microsoft.com> wrote in message
> news:2E6FF844-22F3-466C-B527-D6EED7081A54@.microsoft.com...
> > HI I have two queries I am trying to combine
> > the first one simply returns several integers
> > for the second part I am trying to insert these integers into another
> > table.
> > Code below does not work together. Do I need an integer array for @.logids
> >
> > SELECT @.logids field1 FROM dbo.table1
> > WHERE POC_ID = 15
> >
> > INSERT INTO table2
> > (field2)
> > VALUES
> > (@.logids)
> >
> > --
> > Paul G
> > Software engineer.
>
>|||You need to make sure that if the field you are inserting into doesnt allow
nulls that the field you are selecting from also doesn't allow null.
Or unique...etc (ex. Primarykey)
INSERT into table2 (field2) SELECT field1 FROM dbo.table1 WHERE POC_ID = 15
If you have for example in table2 'field1' that is a PK then you need to
consider that.
ex INSERT into table2 (field1,field2) SELECT field1,field2 FROM dbo.table1
WHERE POC_ID = 15
But what you did is that you tried poplulating a row without assigning the
PK value.
"Paul" wrote:
> Hi thanks for the response. I tried this but it tries to insert NULL
> get error statement Cannot insert the value NULL into column 'field2', table
> 'table2'; column does not allow nulls. INSERT fails.
> The statement has been terminated.
> When I try the select statement without the insert it returns 83 values in
> the
> database output.
> "Quentin Ran" wrote:
> > Paul,
> >
> > If I understand your question correctly, it would be just
> >
> > insert into table2 (field2)
> > SELECT field1 FROM dbo.table1
> > WHERE POC_ID = 15
> >
> > hth
> >
> >
> > "Paul" <Paul@.discussions.microsoft.com> wrote in message
> > news:2E6FF844-22F3-466C-B527-D6EED7081A54@.microsoft.com...
> > > HI I have two queries I am trying to combine
> > > the first one simply returns several integers
> > > for the second part I am trying to insert these integers into another
> > > table.
> > > Code below does not work together. Do I need an integer array for @.logids
> > >
> > > SELECT @.logids field1 FROM dbo.table1
> > > WHERE POC_ID = 15
> > >
> > > INSERT INTO table2
> > > (field2)
> > > VALUES
> > > (@.logids)
> > >
> > > --
> > > Paul G
> > > Software engineer.
> >
> >
> >sql sql

combining 2 queries?

Combing 2 Queries into 1

I have spent several hours hurting my brain tring to solve the following. I am hoping one of you SQL gurus can help me out.

I have two tables (each with two fields):

Group with GroupName and GroupDescription

Here is some sample data:

HR HumanResources
IT Information Technology
Boston BostonOffice
NJ NewJerseyOffice

GroupMember with GroupName and UserID

Here is some sample data:

IT CMessineo
NJ CMessineo
Boston JSmith
IT JSmith

What I want is a single stored procedure that when passed a UserID will return a result set that lists all the groups and a 1 or ) if the UserID is a member of that group.

For example calling the procedure and passing CMessineo would produce this result:

HR 0
IT 1
Boston 0
NJ 1

Can this be done in a stored procedure?

This is what I have so far, but I am in over my head:

(
@.UserID varChar(40)
)
As
SELECT"GROUP".GROUPNAME, (SELECT ?
FROM"Group", GroupMember
Where"Group".GroupName = GroupMember.GroupName and GroupMember.UserID = @.UserID)

FROM"GROUP"

Thanks in advance,

ChrisI figured it out - it required an outer join (my first):

SELECT"Group".GroupName, COUNT(UserID) AS MEMBER
FROM"Group" Left Outer Join GroupMember
ON"Group".GroupName = GroupMember.GroupName and GroupMember.UserID=@.UserID
GROUP BY"Group".GroupName

Combine two queries - help please

I have a table that has two dates in it, a date opened and a date
closed. I would like to create one query to give me the number of
records that have been opened each month plus, and this is the hard
part the number of those records that have been closed each month. I
can get the result with two seperate queries but have been unable to
get 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 opened
in 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 table
WHERE (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 table
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)

TIA

BillTry:

SELECT MIN(dateopened),
COUNT(*),
COUNT(dateclosed)
FROM YourTable
GROUP BY YEAR(dateopened), MONTH(dateopened)

--
David Portas
SQL Server MVP
--|||David;

Thank you very much that works just fine. I appreciate the help.

Cheers;

Bill

Combine two queries

Hello, i am an SMS guy trying to write some SQL queries and having little
luck. I have two queries. The first returns the dept code which in my case
is the first 2 letters a computer and a count of computers with that dept
code(see below).
Total counts by dept code
SELECT LEFT(Name0, 2) AS [dept Code], COUNT(*) AS [Total Machines]
FROM v_R_System
GROUP BY LEFT(Name0, 2)
Second is a query that returns the dept code and a count of machines that
have the client installed.
Totals counts installed by dept code
SELECT LEFT(Name0, 2) AS [dept Code], COUNT(*) AS [Total Machines]
FROM v_R_System where client0=1
GROUP BY LEFT(Name0, 2)
My question is this... How the heck can I combine the two into one query so
I return Dept code, total machines, and total machines with client?
Thanks for helping out this rookie...
scottYou can use a CASE like:
SELECT LEFT( Name0, 2 ) AS "dept_code",
COUNT( * ) AS "total_machines",
SUM( CASE WHEN client0 = 1 THEN 1 ELSE 0 END ) AS "client0_count"
FROM v_R_System
GROUP BY LEFT( Name0, 2 ) ;
Anith|||Try,
SELECT
LEFT(Name0, 2) AS [dept Code],
client0,
COUNT(*) AS [Total Machines],
(select count(*) from v_R_System as t1 where LEFT(t1.Name0, 2) =
LEFT(v_R_System.Name0, 2)) as total_dept_comp
FROM
v_R_System
GROUP BY
LEFT(Name0, 2),
client0
order by
LEFT(Name0, 2),
client0
AMB
"scott" wrote:

> Hello, i am an SMS guy trying to write some SQL queries and having little
> luck. I have two queries. The first returns the dept code which in my ca
se
> is the first 2 letters a computer and a count of computers with that dept
> code(see below).
> Total counts by dept code
> SELECT LEFT(Name0, 2) AS [dept Code], COUNT(*) AS [Total Machines]
> FROM v_R_System
> GROUP BY LEFT(Name0, 2)
> Second is a query that returns the dept code and a count of machines that
> have the client installed.
> Totals counts installed by dept code
> SELECT LEFT(Name0, 2) AS [dept Code], COUNT(*) AS [Total Machines]
> FROM v_R_System where client0=1
> GROUP BY LEFT(Name0, 2)
> My question is this... How the heck can I combine the two into one query
so
> I return Dept code, total machines, and total machines with client?
> Thanks for helping out this rookie...
> scott
>sql sql

Tuesday, March 20, 2012

Combine SUMs

How can I combine the 2 Sum amounts below. Basically teh 2 queries are exactly the same, just hitting 2 different tables (pdc and pdcdeleted) with the same structure:

SELECT SUM(PQuery.Amount) as PDCs_IL
FROM
(SELECT c.name,
c.customer,
(SELECT Top 1 fd.Fee1 FROM FeeScheduleDetails fd
where c.feeSchedule = fd.code)
AS FeeSchedule,
m.branch,
pd.desk,
'PDC' AS Type,
pd.Active,
m.number,
pd.Amount,
CONVERT(money, 0) AS OverPaidAmt,
pd.OnHold
FROM Master m (NOLOCK)
LEFT JOIN pdc pd ON pd.number = m.number
INNER JOIN Customer c ON c.Customer = m.Customer
WHERE pd.Active = 1
AND m.Customer IN (SELECT Customer from Customer_DashboardGraphs where Illinois = 1)
AND pd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @.ProcessDate) + 1, @.ProcessDate) AND DATEADD(DAY, -DATEPART(DAY, @.ProcessDate), DATEADD(MONTH, 1, @.ProcessDate)) AND pd.Entered <> '1900-01-01 00:00:00.000'
AND pd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @.ProcessDate) + 1, @.ProcessDate) AND DATEADD(DAY, -DATEPART(DAY, @.ProcessDate), DATEADD(MONTH, 1, @.ProcessDate))
AND pd.Deposit IS NOT NULL
AND pd.OnHold IS NULL
AND c.customer <> '9999999'
) as PQuery

SELECT SUM(PQuery2.Amount) as PDCs_IL_deleted
FROM
(SELECT c.name,
c.customer,
(SELECT Top 1 fd.Fee1 FROM FeeScheduleDetails fd
where c.feeSchedule = fd.code)
AS FeeSchedule,
m.branch,
pd.desk,
'PDC' AS Type,
pd.Active,
m.number,
pd.Amount,
CONVERT(money, 0) AS OverPaidAmt,
pd.OnHold
FROM Master m (NOLOCK)
LEFT JOIN pdcdeleted pd ON pd.number = m.number
INNER JOIN Customer c ON c.Customer = m.Customer
WHERE pd.Active = 1
AND m.Customer IN (SELECT Customer from Customer_DashboardGraphs where Illinois = 1)
AND pd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @.ProcessDate) + 1, @.ProcessDate) AND DATEADD(DAY, -DATEPART(DAY, @.ProcessDate), DATEADD(MONTH, 1, @.ProcessDate)) AND pd.Entered <> '1900-01-01 00:00:00.000'
AND pd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @.ProcessDate) + 1, @.ProcessDate) AND DATEADD(DAY, -DATEPART(DAY, @.ProcessDate), DATEADD(MONTH, 1, @.ProcessDate))
AND pd.Deposit IS NOT NULL
AND pd.OnHold IS NULL
AND c.customer <> '9999999'
) as PQuery2

Since there is no group by, I will assume that you just working with one value, then you can simply do something like:

declare @.PDCs_IL int
select @.PDCs_IL = SUM(PQuery.Amount) as PDCs_IL
FROM
(SELECT c.name,

declare @.PDCs_IL_deleted int
select @.PDCs_IL_deleted = SUM(PQuery.Amount) as PDCs_IL
FROM
(SELECT c.name,

select @.PDCs_IL + @.PDCs_IL_deleted

select ( first query ) + ( second query )

as in

create table #test
(
value int
)
insert into #test values (1)
insert into #test values (1)
insert into #test values (1)

select ( select sum(value) from #test ) + ( select sum(value) from #test )

--note that you might need to check for a NULL value :)

You could probably just add another left join to the deleted table and include the where clause, but it is really messy to contemplate all that you are trying to do without more information. Either of these other methods should work fine if the two queries already work fine.

sql sql

Monday, March 19, 2012

Combine 3 Queries into One

Hello,
I have a large database that contains info about several types of business
industries. There is a table called Co_Ind_Sales which contains industry
sales that I need to sum up for 3 separate industries. There is an
Industry_Id in the Co_Ind_Sales table. I am using the following SQL in
Query Analyzer and I get the right result for the 1 industry I am using:
select SUM(cis.Sales)as Sales Into Lisa_TotalRev
From company_industry ci, co_ind_sales cis, company c
Where ci.company_id=cis.company_id
And cis.company_id=c.company_id
And cis.current_record='Y'
And ci.in_book='Y'
And cis.industry_id =9
And c.listing_type in ('H','S')
SELECT
'$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR(100) ,CONVERT(MONEY,Sales)
,1)),1,100))
AS 'Total Foodservice Revenues - Chain Restaurants'
From Lisa_TotalRev
This gives me the following results:
Total Foodservice Revenues - Chain Restaurants
$177,835,953,607.00
I now have 2 other industries that I need to do the same thing with, they
would be:
And cis.industry_id =42
And cis.industry_id =52
I need to combine all three result sets into one report, like such:
Total Foodservice Revenues
$177,835,953,607.00 Chain Restaurants
Total Foodservice Revenues
$16,077,196,215.00 Hotel/Motel
Total Foodservice Revenues
$30,244,812,996.00 Foodservice Management Operators
Please help. I am rather new to SQL so if you could add to my code the
pieces that I need that would be wonderful. I have trouble understanding
the help file. I do better with examples not just text. Thanks for any help
anyone can give.
On Thu, 12 May 2005 18:34:56 GMT, "Lisa Farina via droptable.com"
<forum@.nospam.droptable.com> wrote:
Something like:

>select
> SUM(cis.Sales)as Sales, MAX(ci.IndustryName) as IndustryName
>Into Lisa_TotalRev
>From company_industry ci, co_ind_sales cis, company c
>Where ci.company_id=cis.company_id
> And cis.company_id=c.company_id
> And cis.current_record='Y'
> And ci.in_book='Y'
> And cis.industry_id in (9, 42, 43)
> And c.listing_type in ('H','S')
>GROUP BY cis.industry_id
>SELECT
> '$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR(100) ,CONVERT(MONEY,Sales),1)),1,100))
> AS 'Total Foodservice Revenues',
> IndustryName
>From Lisa_TotalRev
and it would make me personally very happy if you learned to use the
newer ANSI join style!
Welcome to SQL!
Josh
|||Sorry I am using the older style. I will do my best to learn the newer way.
I'm not sure if you actually posted a solution because the messgage started
with Something like: [quoted text clipped - 17 lines]
and then it was cut off. Could you please repost. Thanks.
|||On Thu, 12 May 2005 19:33:46 GMT, "Lisa Farina via droptable.com"
<forum@.droptable.com> wrote:
>Sorry I am using the older style. I will do my best to learn the newer way.
>I'm not sure if you actually posted a solution because the messgage started
>with Something like: [quoted text clipped - 17 lines]
>and then it was cut off. Could you please repost. Thanks.
I think that's a display option you can turn off, but here's the
pseudo-code I posted with the quotes removed.
J.
select
SUM(cis.Sales)as Sales, MAX(ci.IndustryName) as IndustryName
Into Lisa_TotalRev
From company_industry ci, co_ind_sales cis, company c
Where ci.company_id=cis.company_id
And cis.company_id=c.company_id
And cis.current_record='Y'
And ci.in_book='Y'
And cis.industry_id in (9, 42, 43)
And c.listing_type in ('H','S')
GROUP BY cis.industry_id
SELECT
'$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR(100) ,CONVERT(MONEY,Sales),1)),1,100))
AS 'Total Foodservice Revenues',
IndustryName
From Lisa_TotalRev

Combine 3 Queries into One

Hello,
I have a large database that contains info about several types of business
industries. There is a table called Co_Ind_Sales which contains industry
sales that I need to sum up for 3 separate industries. There is an
Industry_Id in the Co_Ind_Sales table. I am using the following SQL in
Query Analyzer and I get the right result for the 1 industry I am using:
select SUM(cis.Sales)as Sales Into Lisa_TotalRev
From company_industry ci, co_ind_sales cis, company c
Where ci.company_id=cis.company_id
And cis.company_id=c.company_id
And cis.current_record='Y'
And ci.in_book='Y'
And cis.industry_id =9
And c.listing_type in ('H','S')
SELECT
'$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VA
RCHAR(100),CONVERT(MONEY,Sales)
,1)),1,100))
AS 'Total Foodservice Revenues - Chain Restaurants'
From Lisa_TotalRev
This gives me the following results:
Total Foodservice Revenues - Chain Restaurants
---
$177,835,953,607.00
I now have 2 other industries that I need to do the same thing with, they
would be:
And cis.industry_id =42
And cis.industry_id =52
I need to combine all three result sets into one report, like such:
Total Foodservice Revenues
---
$177,835,953,607.00 Chain Restaurants
Total Foodservice Revenues
---
$16,077,196,215.00 Hotel/Motel
Total Foodservice Revenues
---
$30,244,812,996.00 Foodservice Management Operators
Please help. I am rather new to SQL so if you could add to my code the
pieces that I need that would be wonderful. I have trouble understanding
the help file. I do better with examples not just text. Thanks for any help
anyone can give.On Thu, 12 May 2005 18:34:56 GMT, "Lisa Farina via droptable.com"
<forum@.nospam.droptable.com> wrote:
Something like:

>select
> SUM(cis.Sales)as Sales, MAX(ci.IndustryName) as IndustryName
>Into Lisa_TotalRev
>From company_industry ci, co_ind_sales cis, company c
>Where ci.company_id=cis.company_id
> And cis.company_id=c.company_id
> And cis.current_record='Y'
> And ci.in_book='Y'
> And cis.industry_id in (9, 42, 43)
> And c.listing_type in ('H','S')
>GROUP BY cis.industry_id
>SELECT
> '$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VA
RCHAR(100),CONVERT(MONEY,Sales)
,1)),1,100))
> AS 'Total Foodservice Revenues',
> IndustryName
>From Lisa_TotalRev
and it would make me personally very happy if you learned to use the
newer ANSI join style!
Welcome to SQL!
Josh|||Sorry I am using the older style. I will do my best to learn the newer way.
I'm not sure if you actually posted a solution because the messgage started
with Something like: [quoted text clipped - 17 lines]
and then it was cut off. Could you please repost. Thanks.|||On Thu, 12 May 2005 19:33:46 GMT, "Lisa Farina via droptable.com"
<forum@.droptable.com> wrote:
>Sorry I am using the older style. I will do my best to learn the newer way.
>I'm not sure if you actually posted a solution because the messgage started
>with Something like: [quoted text clipped - 17 lines]
>and then it was cut off. Could you please repost. Thanks.
I think that's a display option you can turn off, but here's the
pseudo-code I posted with the quotes removed.
J.
select
SUM(cis.Sales)as Sales, MAX(ci.IndustryName) as IndustryName
Into Lisa_TotalRev
From company_industry ci, co_ind_sales cis, company c
Where ci.company_id=cis.company_id
And cis.company_id=c.company_id
And cis.current_record='Y'
And ci.in_book='Y'
And cis.industry_id in (9, 42, 43)
And c.listing_type in ('H','S')
GROUP BY cis.industry_id
SELECT
'$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VA
RCHAR(100),CONVERT(MONEY,Sales),1)),
1,100))
AS 'Total Foodservice Revenues',
IndustryName
From Lisa_TotalRev

Combine 3 Queries into One

Hello,
I have a large database that contains info about several types of business
industries. There is a table called Co_Ind_Sales which contains industry
sales that I need to sum up for 3 separate industries. There is an
Industry_Id in the Co_Ind_Sales table. I am using the following SQL in
Query Analyzer and I get the right result for the 1 industry I am using:
select SUM(cis.Sales)as Sales Into Lisa_TotalRev
From company_industry ci, co_ind_sales cis, company c
Where ci.company_id=cis.company_id
And cis.company_id=c.company_id
And cis.current_record='Y'
And ci.in_book='Y'
And cis.industry_id =9
And c.listing_type in ('H','S')
SELECT
'$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR(100),CONVERT(MONEY,Sales)
,1)),1,100))
AS 'Total Foodservice Revenues - Chain Restaurants'
From Lisa_TotalRev
This gives me the following results:
Total Foodservice Revenues - Chain Restaurants
---
$177,835,953,607.00
I now have 2 other industries that I need to do the same thing with, they
would be:
And cis.industry_id =42
And cis.industry_id =52
I need to combine all three result sets into one report, like such:
Total Foodservice Revenues
---
$177,835,953,607.00 Chain Restaurants
Total Foodservice Revenues
---
$16,077,196,215.00 Hotel/Motel
Total Foodservice Revenues
---
$30,244,812,996.00 Foodservice Management Operators
Please help. I am rather new to SQL so if you could add to my code the
pieces that I need that would be wonderful. I have trouble understanding
the help file. I do better with examples not just text. Thanks for any help
anyone can give.On Thu, 12 May 2005 18:34:56 GMT, "Lisa Farina via SQLMonster.com"
<forum@.nospam.SQLMonster.com> wrote:
Something like:
>select
> SUM(cis.Sales)as Sales, MAX(ci.IndustryName) as IndustryName
>Into Lisa_TotalRev
>From company_industry ci, co_ind_sales cis, company c
>Where ci.company_id=cis.company_id
> And cis.company_id=c.company_id
> And cis.current_record='Y'
> And ci.in_book='Y'
> And cis.industry_id in (9, 42, 43)
> And c.listing_type in ('H','S')
>GROUP BY cis.industry_id
>SELECT
> '$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR(100),CONVERT(MONEY,Sales),1)),1,100))
> AS 'Total Foodservice Revenues',
> IndustryName
>From Lisa_TotalRev
and it would make me personally very happy if you learned to use the
newer ANSI join style!
Welcome to SQL!
Josh|||Sorry I am using the older style. I will do my best to learn the newer way.
I'm not sure if you actually posted a solution because the messgage started
with Something like: [quoted text clipped - 17 lines]
and then it was cut off. Could you please repost. Thanks.|||On Thu, 12 May 2005 19:33:46 GMT, "Lisa Farina via SQLMonster.com"
<forum@.SQLMonster.com> wrote:
>Sorry I am using the older style. I will do my best to learn the newer way.
>I'm not sure if you actually posted a solution because the messgage started
>with Something like: [quoted text clipped - 17 lines]
>and then it was cut off. Could you please repost. Thanks.
I think that's a display option you can turn off, but here's the
pseudo-code I posted with the quotes removed.
J.
select
SUM(cis.Sales)as Sales, MAX(ci.IndustryName) as IndustryName
Into Lisa_TotalRev
From company_industry ci, co_ind_sales cis, company c
Where ci.company_id=cis.company_id
And cis.company_id=c.company_id
And cis.current_record='Y'
And ci.in_book='Y'
And cis.industry_id in (9, 42, 43)
And c.listing_type in ('H','S')
GROUP BY cis.industry_id
SELECT
'$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR(100),CONVERT(MONEY,Sales),1)),1,100))
AS 'Total Foodservice Revenues',
IndustryName
From Lisa_TotalRev

Combine 3 huge queryies to produce another SUM

I have to combine 3 huge queries to get a GT for ProjGross (see calcs.xls on formulas). Please check out ProjGross here
http://www.photopizzaz.biz/projgross.txt
and see this for the logic for projgross
http://www.photopizzaz.biz/calcs.xls
I am trying to combine (Daily Run Rate * Days Remaining )+ InHouse1 as (stated in the xls) using the previous select statements respectively to get my final ProjGross

All queries so far in my projgross.txt work great...now I just need to combine what's needed to move forward in my calcs.xls to create ProjGross...at the Company level.

You'll see that I attempted to start the ProjGross Query in my .txt file

I have rewritten INHOUSE1 (START) as follows:

SELECT sub.CustomerNumber, sub.PostedAmount + sub.CCsOld + sub.CCsNew + sub.PDCsNew + sub.PDCsOld
FROM
( Select d0.CustomerNumber AS CustomerNumber,
CASE WHEN d0.Type = 'In-House'
THEN SUM(ISNULL(d0.PostedAmount,0))
ELSE 0
END AS PostedAmount,
CASE WHEN d0.Type = 'CC'
AND d0.EnteredDate NOT BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))
AND d0.dc_OnHoldDate IS NULL
THEN SUM(ISNULL(do.CC,0))
ELSE 0
END AS CCsOld,
CASE WHEN d0.Type = 'CC'
AND d0.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))
AND d0.dc_OnHoldDate IS NULL
THEN SUM(ISNULL(do.CC,0))
ELSE 0
END AS CCsNew,
CASE WHEN d0.Type = 'PDC'
AND d0.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))
AND d0.dc_OnHold IS NULL
THEN SUM(ISNULL(do.CC,0))
ELSE 0
END AS PDCsNew,
CASE WHEN d0.Type = 'PDC'
AND d0.EnteredDate NOT BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))
AND d0.dc_OnHold IS NULL
THEN SUM(ISNULL(do.CC,0))
ELSE 0
END AS PDCsOld
FROM DCR d0 With(NOLOCK)
JOIN CustomerNumber cno
ON cno.customernumber = d0.customernumber
AND (d0.branch = '00002' AND cno.CheckBranch = 1)
OR (cno.CheckBranch = 0)
WHERE d0.Type = 'In-House'
GROUP BY d0.CustomerNumber
) AS sub

You should be able to get the rest using the same principle. You are using the same table each time, so all this can be done in one query, albeit a *** of a query, and you can do away with the ...customernumber in... by joining to that table, which should boost peformance a lot. Let me know if you have any more problems. That should work, but you may get a compilation errors, as I do not have the db here in front of me to test.

|||yea but is it going to go through each Case? each case statement will evaluate to true because there are records for each instance that you're checking so I don't think CASE is good here..only one can evaluate true in a CASE|||

I've actually already solved it on my own just earlier today. Thanks for your input though!

Select @.ProjFee = SUM(ProjFee)

FROM

(

Select d1.CustomerNumber, ((((ISNULL(PostedTable.PostedAmount,0) + ISNULL(CCsNewTable.NewCCs,0) + ISNULL(PDCsNewTable.NewPDCs,0)) / (select CurrentPostingDay from v_CurrentPostingDay))) + ((ISNULL(PostedTable.PostedAmount,0) + ISNULL(CCsOldTable.OldCCs,0) + ISNULL(CCsNewTable.NewCCs,0) + ISNULL(PDCsNewTable.NewPDCs,0) + ISNULL(PDCsOldTable.OldPDCs,0)))) * d1.FeeSchedule / 100 as ProjFee

FROM (Select distinct CustomerNumber, FeeSchedule From DCR) d1

- $Posted

LEFT JOIN

(Select d0.CustomerNumber, SUM(ISNULL(d0.PostedAmount,0)) AS PostedAmount

FROM DCR d0 (NOLOCK)

WHERE d0.Type = 'In-House'

AND ((d0.branch = '00002' and d0.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))

OR (d0.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

GROUP BY d0.CustomerNumber) as PostedTable ON PostedTable.CustomerNumber = d1.CustomerNumber

- OldCCs

LEFTJOIN

(Select d3.CustomerNumber, SUM(ISNULL(d3.CC,0)) AS OldCCs

FROM DCR d3 (NOLOCK)

WHERE d3.EnteredDate NOT BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))

AND d3.Type = 'CC'

AND ((d3.branch = '00002' and d3.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))

OR (d3.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

AND d3.dc_OnHoldDate IS NULL

GROUP BY d3.CustomerNumber) as CCsOldTable ON CCsOldTable.CustomerNumber = d1.CustomerNumber

- NewCCs

LEFTJOIN

(Select d4.CustomerNumber, SUM(ISNULL(d4.CC,0)) AS NewCCs

FROM DCR d4 (NOLOCK)

WHERE d4.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))

AND d4.Type = 'CC'

AND ((d4.branch = '00002' and d4.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))

OR (d4.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

AND d4.dc_OnHoldDate IS NULL

GROUP BY d4.CustomerNumber) as CCsNewTable ON CCsNewTable.CustomerNumber = d1.CustomerNumber

- NewPDCs

LEFTJOIN

(Select d5.CustomerNumber, SUM(ISNULL(d5.PDC,0)) AS NewPDCs

FROM DCR d5 (NOLOCK)

WHERE d5.EnteredDate BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))

AND d5.Type = 'PDC'

AND ((d5.branch = '00002' and d5.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))

OR (d5.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

AND d5.pdc_OnHold IS NULL

GROUP BY d5.CustomerNumber) as PDCsNewTable ON PDCsNewTable.CustomerNumber = d1.CustomerNumber

- OldPDCs

LEFTJOIN

(Select d6.CustomerNumber, SUM(ISNULL(d6.PDC,0)) AS OldPDCs

FROM DCR d6 (NOLOCK)

WHERE d6.EnteredDate NOT BETWEEN DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today) AND DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))

AND d6.Type = 'PDC'

AND ((d6.branch = '00002' and d6.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 1))

OR (d6.customernumber IN (select CustomerNumber from CustomersAZ where CheckBranch = 0)))

AND d6.pdc_OnHold IS NULL

GROUP BY d6.CustomerNumber) as PDCsOldTable ON PDCsOldTable.CustomerNumber = d1.CustomerNumber

GROUP BY d1.CustomerNumber,

PostedTable.PostedAmount,

CCsOldTable.OldCCs,

CCsNewTable.NewCCs,

PDCsNewTable.NewPDCs,

PDCsOldTable.OldPDCs,

d1.FeeSchedule

) as z

SELECT ProjFee = @.ProjFee

|||Ok, but my query is much simpler, and there are only two cases for each case statement, true, so add value, or false so add 0. Try it and see. It will only add the to the approriate totals for the relevant records, the rest it will ignore by adding 0. The query you have written is way too complicated and thus probably very slow. I would try and simplify it, hopefully my example will help.

combine 2 queries

Hi,

I have 2 queries that I need to join. I have the following tables:
attendancelog :
headerid
reportmonth

attlogstuds:
headerid
sid

class:
sid
class
status
yearcode
logdate
cdate
mid

The result must return all the classes that appear in query2 but not
in query1.
I am not sure how to join the 2 queries.

Help will be appreciated :-)

Thanks

QUERY1

select sid from
attlogstuds studs
inner join
attendancelog attlog
on studs.headerid=attlog.headerid
where reportmonth=10

query2
-- students learning excl. studs left before 1th oct.

select class.SID from class

left outer JOIN ( select * from class where yearcode=26 and status=6

and ( logdate <'20041001' or CDate
< '20041001' )

) c6 ON c6.sid = class.sid and c6.mid=class.mid
and c6.cdate >= class.cdate

where class.yearcode=26 and class.status in (3,5) and
class.cdate<'20041101' and c6.sid is null[posted and mailed, please reply in news]

avital (avitalnagar@.walla.co.il) writes:
> The result must return all the classes that appear in query2 but not
> in query1.
> I am not sure how to join the 2 queries.

Sounds like the EXCEPT operator would have come in hand, but that
operator is not in SQL Server.

It's always a good idea to include the following:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired output given the sample.

This makes it possible to post a tested solution.

The below is an untested solution. I may have missed something but
it seems to me that a simple NOT EXISTS clause is what you need.

select class.SID
from class
left JOIN (select *
from class
where yearcode=26
and status=6
and ( logdate <'20041001' or
CDate < '20041001' )
) c6
ON c6.sid = class.sid
and c6.mid = class.mid
and c6.cdate >= class.cdate
where class.yearcode=26
and class.status in (3,5)
and class.cdate<'20041101'
and and c6.sid is null
and NOT EXISTS (select *
from attlogstuds studs
join attendancelog attlog
on studs.headerid=attlog.headerid
where studs.sid = class.sid)
--
Erland Sommarskog, SQL Swhere reportmonth=10erver MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Thursday, March 29, 2012

Combining two queries

Tuesday, March 27, 2012

Combining Queries/ Results

Combining PIVOT and INSERT queries

Combining Pass-Through Queries into a Stored Proc.

combining multiple tables into a single flat file destination

Sunday, March 25, 2012

combining many contains()

combining datasets

Thursday, March 22, 2012

combining 2 sql queries

combining 2 queries?

combining 2 queries?

combining 2 queries?

Combing 2 Queries into 1

Combine two queries - help please

Combine two queries

Tuesday, March 20, 2012

Combine SUMs

Monday, March 19, 2012

Combine 3 Queries into One

Combine 3 Queries into One

Combine 3 Queries into One

Combine 3 huge queryies to produce another SUM

combine 2 queries

Collation problem

Blog Archive

About Me