Joins Performance Problem

Hello,
It is taking too long to run the following query:
Note: I have indexes on all of the columns in conditions. My temp db size
is 18 GB. Pds_txn table size is 165 GB.
The execution plan showing:
Table pool/easer spool operation â'
Row Count: 9 M
Disk i/o: 11k
Row size: 1089
Estimated cost: 11 k (57%)
CPU cost: 3.3
Sub tree cost: 20 K
Any help/hint will be appreciated.
Thanks,
Alim
-----
FROM
dbo.pds_txn T1
INNER JOIN
dbo.GROUPS T2 ON
T1.GROUP_ID = T2.ID_200
INNER JOIN
dbo.DIVISIONS T3 ON
T1.DIVISION = T3.ID_102
INNER JOIN
dbo.BILLING_AREAS T4 ON
T1.BILLING_AREA = T4.ID_202
INNER JOIN
dbo.PROVIDERS T6 ON
T1.PROVIDER = T6.ID_3
INNER JOIN
dbo.LOCATIONS T7 ON
T1.LOCATION = T7.ID_100
INNER JOIN
dbo.PROCEDURES T8 ON
T1.[PROCEDURE] = T8.ID_1
INNER JOIN
dbo.FSC T9 ON
T1.ORIG_FSC = T9.ID_19
INNER JOIN
dbo.DIAGNOSIS T10 ON
T1.TXN_DX_1 = T10.ID_36
INNER JOIN
dbo.pds_invoice T11 ON
T1.INVOICE_NUM = T11.INVOICE_NUM AND
T1.GROUP_ID = T11.GROUP_ID
LEFT OUTER JOIN
dbo.PROVIDERS T6A ON
T11.PERFORMING_PHYS = T6A.ID_3
WHERE
T1.POSTING_PD_DTE >= '05/01/2003' AND
T1.PAY_CODE = 21 AND
T2.EXCLUSION_FLAG = 0 AND
T3.DIV_NUM <> '2901'Hi Alim,
It is hard to guess what the issue might be without more information. Could
you attach the output of "statistics profile" or "statistics xml" (if you
are using SQL Server 2005) ?
Regards,
Leo
"alim" <alim@.discussions.microsoft.com> wrote in message
news:3873F521-EEF2-4A62-9C66-00C035E1A9CD@.microsoft.com...
> Hello,
> It is taking too long to run the following query:
> Note: I have indexes on all of the columns in conditions. My temp db size
> is 18 GB. Pds_txn table size is 165 GB.
> The execution plan showing:
> Table pool/easer spool operation -
> Row Count: 9 M
> Disk i/o: 11k
> Row size: 1089
> Estimated cost: 11 k (57%)
> CPU cost: 3.3
> Sub tree cost: 20 K
> Any help/hint will be appreciated.
> Thanks,
> Alim
> -----
>
> FROM
> dbo.pds_txn T1
> INNER JOIN
> dbo.GROUPS T2 ON
> T1.GROUP_ID = T2.ID_200
> INNER JOIN
> dbo.DIVISIONS T3 ON
> T1.DIVISION = T3.ID_102
> INNER JOIN
> dbo.BILLING_AREAS T4 ON
> T1.BILLING_AREA = T4.ID_202
> INNER JOIN
> dbo.PROVIDERS T6 ON
> T1.PROVIDER = T6.ID_3
> INNER JOIN
> dbo.LOCATIONS T7 ON
> T1.LOCATION = T7.ID_100
> INNER JOIN
> dbo.PROCEDURES T8 ON
> T1.[PROCEDURE] = T8.ID_1
> INNER JOIN
> dbo.FSC T9 ON
> T1.ORIG_FSC = T9.ID_19
> INNER JOIN
> dbo.DIAGNOSIS T10 ON
> T1.TXN_DX_1 = T10.ID_36
> INNER JOIN
> dbo.pds_invoice T11 ON
> T1.INVOICE_NUM = T11.INVOICE_NUM AND
> T1.GROUP_ID = T11.GROUP_ID
> LEFT OUTER JOIN
> dbo.PROVIDERS T6A ON
> T11.PERFORMING_PHYS = T6A.ID_3
> WHERE
> T1.POSTING_PD_DTE >= '05/01/2003' AND
> T1.PAY_CODE = 21 AND
> T2.EXCLUSION_FLAG = 0 AND
> T3.DIV_NUM <> '2901'
>
>|||Alim,
Need to provide the table/index structure and the query that you are
trying to run..
Jayesh
"Leo Giakoumakis [MS]" <leogia_removethis_@.microsoft.com> wrote in message
news:e8OMTRyiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Hi Alim,
> It is hard to guess what the issue might be without more information.
> Could you attach the output of "statistics profile" or "statistics xml"
> (if you are using SQL Server 2005) ?
> Regards,
> Leo
>
> "alim" <alim@.discussions.microsoft.com> wrote in message
> news:3873F521-EEF2-4A62-9C66-00C035E1A9CD@.microsoft.com...
>> Hello,
>> It is taking too long to run the following query:
>> Note: I have indexes on all of the columns in conditions. My temp db
>> size
>> is 18 GB. Pds_txn table size is 165 GB.
>> The execution plan showing:
>> Table pool/easer spool operation -
>> Row Count: 9 M
>> Disk i/o: 11k
>> Row size: 1089
>> Estimated cost: 11 k (57%)
>> CPU cost: 3.3
>> Sub tree cost: 20 K
>> Any help/hint will be appreciated.
>> Thanks,
>> Alim
>> -----
>>
>> FROM
>> dbo.pds_txn T1
>> INNER JOIN
>> dbo.GROUPS T2 ON
>> T1.GROUP_ID = T2.ID_200
>> INNER JOIN
>> dbo.DIVISIONS T3 ON
>> T1.DIVISION = T3.ID_102
>> INNER JOIN
>> dbo.BILLING_AREAS T4 ON
>> T1.BILLING_AREA = T4.ID_202
>> INNER JOIN
>> dbo.PROVIDERS T6 ON
>> T1.PROVIDER = T6.ID_3
>> INNER JOIN
>> dbo.LOCATIONS T7 ON
>> T1.LOCATION = T7.ID_100
>> INNER JOIN
>> dbo.PROCEDURES T8 ON
>> T1.[PROCEDURE] = T8.ID_1
>> INNER JOIN
>> dbo.FSC T9 ON
>> T1.ORIG_FSC = T9.ID_19
>> INNER JOIN
>> dbo.DIAGNOSIS T10 ON
>> T1.TXN_DX_1 = T10.ID_36
>> INNER JOIN
>> dbo.pds_invoice T11 ON
>> T1.INVOICE_NUM = T11.INVOICE_NUM AND
>> T1.GROUP_ID = T11.GROUP_ID
>> LEFT OUTER JOIN
>> dbo.PROVIDERS T6A ON
>> T11.PERFORMING_PHYS = T6A.ID_3
>> WHERE
>> T1.POSTING_PD_DTE >= '05/01/2003' AND
>> T1.PAY_CODE = 21 AND
>> T2.EXCLUSION_FLAG = 0 AND
>> T3.DIV_NUM <> '2901'
>>
>

Joins Performance Problem

Hello,
It is taking too long to run the following query:
Note: I have indexes on all of the columns in conditions. My temp db size
is 18 GB. Pds_txn table size is 165 GB.
The execution plan showing:
Table pool/easer spool operation –
Row Count: 9 M
Disk i/o: 11k
Row size: 1089
Estimated cost: 11 k (57%)
CPU cost: 3.3
Sub tree cost: 20 K
Any help/hint will be appreciated.
Thanks,
Alim
----
--
FROM
dbo.pds_txn T1
INNER JOIN
dbo.GROUPS T2 ON
T1.GROUP_ID = T2.ID_200
INNER JOIN
dbo.DIVISIONS T3 ON
T1.DIVISION = T3.ID_102
INNER JOIN
dbo.BILLING_AREAS T4 ON
T1.BILLING_AREA = T4.ID_202
INNER JOIN
dbo.PROVIDERS T6 ON
T1.PROVIDER = T6.ID_3
INNER JOIN
dbo.LOCATIONS T7 ON
T1.LOCATION = T7.ID_100
INNER JOIN
dbo.PROCEDURES T8 ON
T1.[PROCEDURE] = T8.ID_1
INNER JOIN
dbo.FSC T9 ON
T1.ORIG_FSC = T9.ID_19
INNER JOIN
dbo.DIAGNOSIS T10 ON
T1.TXN_DX_1 = T10.ID_36
INNER JOIN
dbo.pds_invoice T11 ON
T1.INVOICE_NUM = T11.INVOICE_NUM AND
T1.GROUP_ID = T11.GROUP_ID
LEFT OUTER JOIN
dbo.PROVIDERS T6A ON
T11.PERFORMING_PHYS = T6A.ID_3
WHERE
T1.POSTING_PD_DTE >= '05/01/2003' AND
T1.PAY_CODE = 21 AND
T2.EXCLUSION_FLAG = 0 AND
T3.DIV_NUM <> '2901'Hi Alim,
It is hard to guess what the issue might be without more information. Could
you attach the output of "statistics profile" or "statistics xml" (if you
are using SQL Server 2005) ?
Regards,
Leo
"alim" <alim@.discussions.microsoft.com> wrote in message
news:3873F521-EEF2-4A62-9C66-00C035E1A9CD@.microsoft.com...
> Hello,
> It is taking too long to run the following query:
> Note: I have indexes on all of the columns in conditions. My temp db size
> is 18 GB. Pds_txn table size is 165 GB.
> The execution plan showing:
> Table pool/easer spool operation -
> Row Count: 9 M
> Disk i/o: 11k
> Row size: 1089
> Estimated cost: 11 k (57%)
> CPU cost: 3.3
> Sub tree cost: 20 K
> Any help/hint will be appreciated.
> Thanks,
> Alim
> ----
--
>
> FROM
> dbo.pds_txn T1
> INNER JOIN
> dbo.GROUPS T2 ON
> T1.GROUP_ID = T2.ID_200
> INNER JOIN
> dbo.DIVISIONS T3 ON
> T1.DIVISION = T3.ID_102
> INNER JOIN
> dbo.BILLING_AREAS T4 ON
> T1.BILLING_AREA = T4.ID_202
> INNER JOIN
> dbo.PROVIDERS T6 ON
> T1.PROVIDER = T6.ID_3
> INNER JOIN
> dbo.LOCATIONS T7 ON
> T1.LOCATION = T7.ID_100
> INNER JOIN
> dbo.PROCEDURES T8 ON
> T1.[PROCEDURE] = T8.ID_1
> INNER JOIN
> dbo.FSC T9 ON
> T1.ORIG_FSC = T9.ID_19
> INNER JOIN
> dbo.DIAGNOSIS T10 ON
> T1.TXN_DX_1 = T10.ID_36
> INNER JOIN
> dbo.pds_invoice T11 ON
> T1.INVOICE_NUM = T11.INVOICE_NUM AND
> T1.GROUP_ID = T11.GROUP_ID
> LEFT OUTER JOIN
> dbo.PROVIDERS T6A ON
> T11.PERFORMING_PHYS = T6A.ID_3
> WHERE
> T1.POSTING_PD_DTE >= '05/01/2003' AND
> T1.PAY_CODE = 21 AND
> T2.EXCLUSION_FLAG = 0 AND
> T3.DIV_NUM <> '2901'
>
>|||Alim,
Need to provide the table/index structure and the query that you are
trying to run..
Jayesh
"Leo Giakoumakis [MS]" <leogia_removethis_@.microsoft.com> wrote in messa
ge
news:e8OMTRyiGHA.3848@.TK2MSFTNGP04.phx.gbl...
> Hi Alim,
> It is hard to guess what the issue might be without more information.
> Could you attach the output of "statistics profile" or "statistics xml"
> (if you are using SQL Server 2005) ?
> Regards,
> Leo
>
> "alim" <alim@.discussions.microsoft.com> wrote in message
> news:3873F521-EEF2-4A62-9C66-00C035E1A9CD@.microsoft.com...
>

Joins on uniqueidentifier columns

I have recently introduced Microsoft'suser/roles facility by running aspnet_regsql.exe against my database so that Iwould be able to link activities with staff members.

In testing I was able to

1. Join 2 tables (Reservations and aspnet_Users) with the join fields being of type uniqueidentifier, using SQL Server Manager Studio Express diagram facility;
2. Create a record in aspnet_Users using Microsoft's Website Admin tool
3. Create a record in Reservations and paste in the contents of uniqueidentifier field in aspnet_Users using SQL Server Manager Studio Express

All the basic tests had been fine so I created a web pagewith a detailsview of the Reservations table and made a templated field (in theinsertitemtemplate), replaced the text box with a dropdown and did a bind ofthat dropdown to the relevant records in the aspnet_Users table i.e. UserNameand UserId columns with UserName being displayed. The source is as follows:

<asp:TemplateFieldHeaderText="Taken By"SortExpression="RES_Taken_By_Staff_ID">

<EditItemTemplate>

<asp:TextBoxID="TextBox5"runat="server"Text='<%#Bind("RES_Taken_By_Staff_ID")%>'></asp:TextBox>

</EditItemTemplate>

<InsertItemTemplate>

<asp:DropDownListID="DropDownList5"runat="server"DataSourceID="SDSStaff"DataTextField="UserName"

DataValueField="UserId">

</asp:DropDownList><br/>

<asp:SqlDataSourceID="SDSStaff"runat="server"ConnectionString="<%$ ConnectionStrings:ReservationsDBConnectionString%>"

SelectCommand="SELECT[UserId], [UserName] FROM [vw_aspnet_Users]"></asp:SqlDataSource>

</InsertItemTemplate>

<ItemTemplate>

<asp:LabelID="Label5"runat="server"Text='<%#Bind("RES_Taken_By_Staff_ID")%>'></asp:Label>

</ItemTemplate>

</asp:TemplateField>

Everything looks OK, the correct data appears in the dropdownbut when I hit the INSERT button I receive the following failure message:

Implicit conversion from data type sql_variant to uniqueidentifier isnot allowed. Use the CONVERT function to run this query.

Description:An unhandled exceptionoccurred during the execution of the current web request. Please review thestack trace for more information about the error and where it originated in thecode.

Exception Details:System.Data.SqlClient.SqlException: Implicitconversion from data type sql_variant to uniqueidentifier is not allowed. Usethe CONVERT function to run this query.

I can't quite see what I should do so I would begrateful for any help.

HI

I hopeInserting with a SqlDataSource Using uniqueidentifier Parameters can help you.

|||

Hi,

Thank you for this. The references did not cover Template Fields where SqlDataSource does not have a type attribute so I'm still stymied!

|||

Me,too. There is clearly something strange about inserting a uniqueidentifier that is not your primary key column into SQL from ASP.net (like a foreign key).

I followed the same advice quoted in the article above. It worked for my select & update statements, but not for my insert statements.

I cannot insert a foreign key into a table from ASP.net at all. Please help if you found a fix or workaround for this problem.

|||

Hi,

Yes, very odd! I've reported this as a bug so maybe confirmation/correction may be available from MS

|||

My workaround for now is to convert the uniqueidentifier foreign key to an integer.

I managed to get it working, but it's not ideal

- Roger

|||

Please try delete all TemplateField filds in grid.

Bag in grid:

I haved

<asp:GridView ID="GridViewMain" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataKeyNames="FAQID" DataSourceID="SqlDataSourceMain"
EmptyDataText="There are no data records to display.">
<Columns>
<asp:BoundField DataField="LotName" HeaderText="LotName" SortExpression="LotName" />
<asp:TemplateField HeaderText="From User" SortExpression="fromUser">
<ItemTemplate>
<asp:HyperLink ID="LinkFromUser" runat="server" Text='<%# Bind("[fromUser]") %>'
NavigateUrl='<%# "~/UserManagment/UserView.aspx?Filter=" + Eval("[From]") %>'>
</asp:HyperLink>
</ItemTemplate>
<EditItemTemplate>
</EditItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>

Warning !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
<EditItemTemplate>
</EditItemTemplate>

without EditItemTemplate i have error

Implicit conversion from data type sql_variant to uniqueidentifier isnot allowed. Use the CONVERT function to run this query.

sql

Joining two columns together to be searched as one

Hi all,

I have three questions to ask that hopefully can be answered. My database server is MySQL 4.1 and my database table contains the following example data:

Id | Store | Make | Model | Price
----------------
1 | Store1 | JVC | AV36D502 | 100.00
2 | Store2 | JVC | AV36D502 | 400.00
3 | Store1 | Panasonic | CT32HX41 | 250.00
4 | Store2 | Panasonic | CT32HX41 | 350.00
5 | Store1 | Sony | KV32S42 | 400.00
6 | Store2 | Sony | KV32S42 | 500.00

(1) Is it possibe to have in my select statement the following: "WHERE ((Make, Model) = 'Sony KV32S42')", I have already tried this but it throws up errors. What I'm trying to do is to some how join together the Make and Model columns and then to see if it matches the search term.

(2) If I was to "SELECT DISTINCT Make" or equivalent, rather than returning 3 rows is there anyway to return just the one row but with the value of "JVC Panasonic Sony" i.e. a string of all Makes, or is this impossible.

and (3) I have another select statement "SELECT DISTINCT Make, MIN(Price), MAX(Price)" with the output shown below. If I "ORDER BY Price ASC" the default is that it orders it by the maximum price, what I want to do is to order it by the minimum price only. "ORDER BY MIN(Price) ASC" has errors or again is this impossible.

Make | MIN(Price) | MAX(Price)
------------
JVC | 100.00 | 400.00
Panasonic | 250.00 | 350.00
Sony | 400.00 | 500.00

I know I'm asking a lot but again I just can't find any good information on the net. Thanking you in advance.(1) where concat_ws(' ',make.model) = 'Sony KV32S42'

(2) with the GROUP_CONCAT function

(3) you can't use DISTINCT here

select Make, MIN(Price) as minprice, MAX(Price) as maxprice
from yourtable group by Make order by minprice|||(1) WHERE make = 'Sony' AND model = 'KV32S42'.
(2) AFAIK, this is impossible with "standard" SQL.
(3) See r937's solution, or alternatively "ORDER BY 2".|||Many thanks guys,

(1) and (3) work perfectly now, (2) will work once I upgrade to mySQL 4.1 or 5.0|||btw, your design is denormalized... Is there a reason you don't store make and model in a seperate table?|||I'm sorry I don't understand, why would you want to put make and model in seperate tables, surely that would make the design more complex? Or would it be better (more efficient) if I just had the one column called "product" which would store the make and model, i.e. Sony KV32S42.|||It would make the model slighly more "complex" in that you will have an extra table, sure. However it will make the model much simpler in that you will have far more flexibility with a "normalized" design. Read up at this site (http://r937.com/relational.html). It has some good models for what I'm talking about.

You do have the right general idea though. If you were to put make and model in a seperate table with a unique key, you could then use that key to store prices for competitors. When it comes time to report, your queries become far easier and more intuitive, with less margin for error.

Joining tables based on string key - bad idea?

The data already in the tables allows me to obtain what I need by drawing a relationship based on two columns that have nvarchar values but I have noticed that generally tables are related through integer keys. I want to know whether there are downsides to doing this, specifically if the join condition could 'wrongly' fail due to the string nature of the join criteria and thus cause missing rows in my resulting table.

Its typically not that great of an idea because it is going to take SQL SErver longer to join on these fields because they are larger than integer fields. The larger the field value, the longer it takes to compare (and in this case, join).
Tim|||

As Tim indicated, string values for JOINs is generally not a good idea. It has to do with how many bytes of data that has to be stored and read from the indexes. The shorter the values, the quicker index searching becomes.

However, if the string values are 'short' ( < 10 characters ), and the columns are indexed, it will most likely perform fine for you. The variables include the total number of rows in the table, amount of table activity, etc. I would NOT allow these string keys to be easily (if ever) changed.

|||

If you use character columns...

it's also a good idea to put adequate constraints on the columns or define foreign references to help ensure the quality of the data, so you don't end up with broken relationships

Joining Dummy Columns

Is it possible to create a join on two tables when one of the columns has been derived.

e.g.

Table 1

ID Name Price Qty

1 Sand 12,00 2

Table 2

Week Top_Sales

1 24

e.g.

Select a.ID, a.price, a.qty, a.qty *a.price as "Total Sales"

from Table 1 a

inner join on table 2 b

on

b.top_sales = Total Sales

Is this possible to perform this join without creating a temporary table or a view?

Sure, but you can't use the named column from the SELECT clause because that name hasn't been materialized yet. You will need to do:

Select a.ID, a.price, a.qty, a.qty *a.price as "Total Sales"
from Table 1 a
inner join on table 2 b
on b.top_sales = a.qty *a.price

This seems a very dangerous query, if you ask me though. In your table2, why not include the primary key of the top sales rows? Then you wouldn't have to do this wonky join and you could join on keys. I guess this because you have a fixed amount for the top seller, so you had to have calculated it from the quantity and price somewhere.

Joining 2 queries in one table

I am trying to join 2 queries within one table, but as the results return 2 columns and 12 rows for each query, I am having problems working out how to join them together. (This is actually part of a far larger join of many queries, but this is the bit I am stuck on!)

Any help would be appreciated.

Thanks

SELECT CONVERT(nvarchar,MONTH(Job.job_cdate))+'/'+CONVERT(nvarchar,YEAR(Job.job_cdate)) AS 'Month',COUNT(*) AS 'TotalJobsIn Year' FROM Job
WHERE Job.job_cdate BETWEEN DATEADD(mm, DATEDIFF(mm,0,GETDATE()-341), 0)
AND DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1, 0))
GROUP BY MONTH(Job.job_cdate),YEAR(Job.job_cdate)

SELECT CONVERT(nvarchar,MONTH(Appointment.actual_end_date))+'/'+CONVERT(nvarchar,YEAR(Appointment.actual_end_date)) AS 'Month',COUNT(*) AS 'TotalAppsPerMonth' FROM Appointment
WHERE Appointment.actual_end_date BETWEEN DATEADD(mm, DATEDIFF(mm,0,GETDATE()-341), 0)
AND DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1, 0))
GROUP BY MONTH(Appointment.actual_end_date),YEAR(Appointment.actual_end_date)

You want to UNION them from what I can see.

SELECT CONVERT(nvarchar,MONTH(Job.job_cdate))+'/'+CONVERT(nvarchar,YEAR(Job.job_cdate)) AS 'Month',COUNT(*) AS 'TotalJobsIn Year' FROM Job
WHERE Job.job_cdate BETWEEN DATEADD(mm, DATEDIFF(mm,0,GETDATE()-341), 0)
AND DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1, 0))
GROUP BY MONTH(Job.job_cdate),YEAR(Job.job_cdate)
UNION ALL
SELECT CONVERT(nvarchar,MONTH(Appointment.actual_end_date))+'/'+CONVERT(nvarchar,YEAR(Appointment.actual_end_date)) AS 'Month',COUNT(*) AS 'TotalAppsPerMonth' FROM Appointment
WHERE Appointment.actual_end_date BETWEEN DATEADD(mm, DATEDIFF(mm,0,GETDATE()-341), 0)
AND DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1, 0))
GROUP BY MONTH(Appointment.actual_end_date),YEAR(Appointment.actual_end_date)

Adamus

|||

Here You Go...

Code Snippet

Select

Isnull(JobData.[Month],AppointmentData.[Month]) as [Month]

,JobData.[TotalJobsIn Month]

,AppointmentData.[TotalAppsPer Month]

From

(

SELECT

CONVERT(nvarchar,MONTH(Job.job_cdate))+'/'+CONVERT(nvarchar,YEAR(Job.job_cdate)) AS 'Month'

,COUNT(*) AS 'TotalJobsIn Month'

FROM

Job

WHERE

Job.job_cdate BETWEEN DATEADD(mm, DATEDIFF(mm,0,GETDATE()-341), 0)

AND DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1, 0))

GROUP BY

MONTH(Job.job_cdate),YEAR(Job.job_cdate)

) as JobData

FULL OUTER Join

(

SELECT

CONVERT(nvarchar,MONTH(Appointment.actual_end_date))+'/'+CONVERT(nvarchar,YEAR(Appointment.actual_end_date)) AS 'Month'

,COUNT(*) AS 'TotalAppsPer Month'

FROM

Appointment

WHERE

Appointment.actual_end_date BETWEEN DATEADD(mm, DATEDIFF(mm,0,GETDATE()-341), 0)

AND DATEADD(ms,-3,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1, 0))

GROUP BY

MONTH(Appointment.actual_end_date),YEAR(Appointment.actual_end_date)

) as AppointmentData On JobData.[Month] = AppointmentData.[Month]

|||Thanks Manivannan. That is exactly what I needed - I knew I couldn't use the union clause as I wanted the results next to each other.
Thanks so much
K

Joing Multiple Columns

Hi All,

Bit of a newbie question i'm afraid, so sorry if this is a really
stupid question,

I am not sure if I am trying to do this in the right place as I am sure
SQL Server has a far better way of doing this

What I am trying to do is join the contents of several columns and
present the entry into a new column. the entries of all the columns I
am trying to join are different types such as dates and numbers. I want
to join all columns as a text string. To make it just a even more
tricky I wish to insert a # between all fields.

Just in case my poor description has got you thinking What the F*&K
here is an example of what I am trying to do

C1 C2 C3 C4 C5
Ian 1234 22/02/2006 123456789 Ian#1234#22/02/2006#1234567890
I have been trying to do this in the Formula in the design veiw of the
table.

Any Advice would be fantastic.

Many Thank

IanTry:

select
C1 + '#' + cast (C2 as varchar (10)) + '#' + convert (char (10), C3,
103) + '#' + cast (C4 as varchar (10))
from
MyTable

--
Tom

----------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"Crumb" <rowan.ian@.gmail.com> wrote in message
news:1140655591.245227.133490@.f14g2000cwb.googlegr oups.com...
Hi All,

Bit of a newbie question i'm afraid, so sorry if this is a really
stupid question,

I am not sure if I am trying to do this in the right place as I am sure
SQL Server has a far better way of doing this

What I am trying to do is join the contents of several columns and
present the entry into a new column. the entries of all the columns I
am trying to join are different types such as dates and numbers. I want
to join all columns as a text string. To make it just a even more
tricky I wish to insert a # between all fields.

Just in case my poor description has got you thinking What the F*&K
here is an example of what I am trying to do

C1 C2 C3 C4 C5
Ian 1234 22/02/2006 123456789 Ian#1234#22/02/2006#1234567890
I have been trying to do this in the Formula in the design veiw of the
table.

Any Advice would be fantastic.

Many Thank

Ian

Join records of each group

Hello, thanks in advance for your help / comments

I have 2 tables:

SampleInfo contains 2 columns: SampleID & SampleName
Analysis contains 2 columns: SampleID & Elements

I link these 2 tables, get the SampleName & Elements out by the code:

SELECT SampleInfo.SampleName, Analysis.Elements

FROM SampleInfo INNER JOIN Analysis ON (SampleInfo.SampleID = Analysis.SampleID)

It would display

SampleName | Elements
A | a
A | b
A | c
A | f
B | a
B | g
B | l
C | c
C | s
C | o
C | m
C | n

I need to display the report as following:

SampleName | Elements
A | a, b, c, f
B | a, g, l
C | c, s, o, m, n

QUESTION: is it possible? If it is, how should I do this?

FYI, I use CR10 & SQLServer 2000 database

Regards,
tHi,

I found some solution to yor post.

I created a report with excel as datasource.

Grouped the report on Sample Name field.
Created two formula fields to get the results.
1. Elements -- Formula Field
Code for formula filed as follows :
whileprintingrecords;
shared stringvar Elements;
if Elements = "" then
Elements:= {Sheet1_.Elements}
else
Elements:= Elements & "," & {Sheet1_.Elements};
Elements;
2.ResetElementValue -- Formula Field
whileprintingrecords;
shared stringvar Elements;
Elements:="";
-- Place the @.Elements fromula filed in detail section and suppress the section.
-- Place the Group Name in Group Footer section
-- Place @.Elements in the same group Footer section
-- Place the ResetElementValue formula filed in the Group Header section and suppres

Try with the following format and let me know will it fulfills your requirement.

Thanks,
Vidu.
-- Group|||Vidu,
Thanks for your help. It was a great start. I only had to change the Elements (formula field) a little as below. If I don't have the ELSE IF, it will double the last element in each group.

whileprintingrecords;
shared stringvar Elements;
if Elements = "" then
Elements:= {Sheet1_.Elements}
else IF Elements <> Right(Elements, length({Sheet1_.Elements}) then
Elements:= Elements & "," & {Sheet1_.Elements};
Elements;

Again, your help is truly appreciated.|||or place the formula in group footer and suppress the details and group header

Join Query

Hi,

Please help me in writing a join query.

I have three tables with three columns each. Now I want to retrieve data from all the three

tables as one.

Table 1: EmpId, Date, Points
Table 2: EmpId, Date, Points
Table 3: EmpId, Date, Points

These are related to three different divisions. So, on analysis we have get the no. of points

accumulated on a day. On any day the points can be in all three divisions or in any one or two

divisions. My database SQL Server 2000.

So, how to get this sort of output.

Empid Date Pts(DIv1) Pts(DIv2) Pts(DIv3)
V001 07-24-2004 Null Null 25
V002 07-24-2004 20 Null 25
V003 07-24-2004 Null 30 NUll
V001 07-23-2004 15 Null NUll
V002 07-23-2004 10 25 25
V001 07-22-2004 Null 10 25

I'm badly in need of help. Any sort of help is appreciated.

M.L.Srinivas<code>
SELECT T1.EmpId,T1.Date,T1.points as Div1pts,T2.points as Div2pts,T3.points as Div3pts FROM Table1 as T1
INNER JOIN Table2 as T2 on T1.EmpId=T2.EmpId and T1.Date=T2.Date
INNER JOIN Table3 as T3 on T2.EmpId=T3.EmpId and T2.Date=T3.Date
WHERE <i>condition</i>
</code>|||Hi,

Thanks for your reply..as you used inner join it works only for dates which are available in table1. What if the person scored only in second or third div. on a day and not in first div.

Please see my data sample carefully..i beleive we have to use full outer join...i was succesful for two tables..but how about three tables...

here is my query

select 'Tdate'= case
when a.date is null then b.date
else a.date
end,
a.points,b.points from table1 as a full outer join table2 as b
on a.empid=b.empid and a.date=b.date

Please guide me

M.L.Srinivas|||<code>
SELECT T.EmpId,T.Date,T1.points as Div1pts,T2.points as Div2pts,T3.points as Div3pts FROM
(SELECT EmpId,Date FROM Table1
UNION
SELECT EmpId,Date FROM Table2
UNION
SELECT EmpId,Date FROM Table3
) as T
LEFT JOIN Table1 as T1 ON T.EmpId=T1.EmpId and T.Date=T1.Date
LEFT JOIN Table2 as T2 ON T.EmpId=T2.EmpId and T.Date=T2.Date
LEFT JOIN Table3 as T3 ON T.EmpId=T3.EmpId and T.Date=T3.Date
WHERE condition
</code>

Join Problem, I do not understand

I have some tables that I need to join. On two that I need to join the
columns are the same except for one, but I want to join them once and from
then on, only when a value has changed. How can this be done.
Thank you
DeeWe need some more details. Please provide DDL for your tables and DML to
load some sample data into those tables, and tell us the result you want
from that sample data.
Tom
"Dee" <Dee@.discussions.microsoft.com> wrote in message
news:2AE674DF-C48C-46FF-9567-F239A62535BC@.microsoft.com...
>I have some tables that I need to join. On two that I need to join the
> columns are the same except for one, but I want to join them once and from
> then on, only when a value has changed. How can this be done.
> Thank you
> Dee|||This is very lengthy:
CREATE TABLE [dbo].[GG Products](
[id] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[path] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[name] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[abstract] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[code] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[price] [decimal](18, 2) NULL,
[sale-price] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[cost] [decimal](18, 2) NULL,
[margin] [int] NULL,
[ship-weight] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[availability] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[orderable] [bit] NOT NULL,
[taxable] [bit] NOT NULL,
[seat-size1] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[arm-height] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[back-size] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[capacity] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[weight] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[exterior-size] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[height] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[width] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[overall-dimensions] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[main] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[bridge] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[return] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[sale-price-text] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[seat-height] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[seat-thickness] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[seat-width] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[recliner] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ottoman] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[shipping-info] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[shipping-options] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[color-options] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[dims] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[diameter] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[quantity-per-carton] [nvarchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[right-column] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[out-of-stock] [bit] NOT NULL,
[custom-order] [bit] NOT NULL,
[quickship] [bit] NOT NULL,
[60-day-guarantee] [bit] NOT NULL,
[free-shipping] [bit] NOT NULL,
[is-top-seller] [bit] NOT NULL,
[template-number] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[credenza] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[hutch] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[custom-options] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[contents] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[optional] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[related-samples] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[caption] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[flat-ship-rate1] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[shipping-cost] [int] NULL,
[Manufacturer] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[mpn] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[UPC] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ISBN] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ASIN-Amazon Number] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[collection] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[options] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[delivery] [real] NULL,
[shipping-price] [int] NULL,
[keywords] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[product-type] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[made-in] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[age-range] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Promo] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Free-shipping2] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[department] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[materials] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[gender] [nvarchar](6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[artist-designer] [nvarchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[green-certified] [bit] NOT NULL,
[CA Product Summary] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[CA Product Short Name] [nvarchar](100) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL,
[CA Promo Text] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[product-url] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Vendor] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Completed] [bit] NOT NULL,
[Updated] [datetime] NULL,
[map-price] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[leaf] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[depth] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[interior-size] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[length] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[degree-of-swivel] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[seat-depth] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[back-height-from-seat] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL,
[UserID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Inside-delivery] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[s_GUID] [uniqueidentifier] NULL,
[Gen_abstract] [int] NULL,
[Gen_caption] [int] NULL,
[Gen_color-options] [int] NULL,
[Gen_contents] [int] NULL,
[Gen_custom-options] [int] NULL,
[Gen_dims] [int] NULL,
[Gen_mpn] [int] NULL,
[Gen_optional] [int] NULL,
[Gen_options] [int] NULL,
[Gen_Promo] [int] NULL,
[Gen_related-samples] [int] NULL,
[Gen_right-column] [int] NULL,
[s_ColLineage] [image] NULL,
[s_Generation] [int] NULL,
[s_Lineage] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Both tables are pretty much the same except for one added column in the
other table. I would like to first combine them into another table, then
update that table only when a value has changed. In other words how to I
check the values. I know that one thing I could do to combine the tables is
add a column to the first table.
Also if other columns are added I do not understand how to do a left join.
This info is coming from an access data base and has to be updated nightly
so I need to know how to do that as well. I do know what I can use the
maintenance tool for that, but do not know how to put to the script I will
use.
Thank you
Dee
"Tom Cooper" wrote:
> We need some more details. Please provide DDL for your tables and DML to
> load some sample data into those tables, and tell us the result you want
> from that sample data.
> Tom
> "Dee" <Dee@.discussions.microsoft.com> wrote in message
> news:2AE674DF-C48C-46FF-9567-F239A62535BC@.microsoft.com...
> >I have some tables that I need to join. On two that I need to join the
> > columns are the same except for one, but I want to join them once and from
> > then on, only when a value has changed. How can this be done.
> >
> > Thank you
> > Dee
>
>

Join multiple tables

Hi
I'm new to SQL and I have the following problem.
I've 3 tables: table1 (with columns a1, b1, c1), table2 (a2, b2, c2), table3
(a3, b3, c3). I have to build joined table with columns like this:
col1 (a1 or a2 or a3), col2 (b1 or b2 or b3), col3 (c1), col4(c2), col5(c3).
a1=a2=a3, b1=b2=b3
How to build query for this?
Thanks in advance.Try,
SELECT T1.a1, T1.a2, T1.c1, T2.c2, T3.c3
FROM T1
JOIN T2 ON T1.a1 = T2.a2 AND T1.b1 = T2.b2
JOIN T3 ON T1.a1 = T3.a3 AND T1.b1 = T3.b3
BG, SQL Server MVP
www.SolidQualityLearning.com
"GrzesB" <GrzesB@.discussions.microsoft.com> wrote in message
news:866BC885-A828-4758-AA23-DE5DC84C6DE0@.microsoft.com...
> Hi
> I'm new to SQL and I have the following problem.
> I've 3 tables: table1 (with columns a1, b1, c1), table2 (a2, b2, c2),
> table3
> (a3, b3, c3). I have to build joined table with columns like this:
> col1 (a1 or a2 or a3), col2 (b1 or b2 or b3), col3 (c1), col4(c2),
> col5(c3).
> a1=a2=a3, b1=b2=b3
> How to build query for this?
> Thanks in advance.
>|||It will help us to understand better your request if you post DDL, sample
data and expected result.
Please provide DDL and sample data.
http://www.aspfaq.com/etiquette.asp?id=5006
AMB
"GrzesB" wrote:

> Hi
> I'm new to SQL and I have the following problem.
> I've 3 tables: table1 (with columns a1, b1, c1), table2 (a2, b2, c2), tabl
e3
> (a3, b3, c3). I have to build joined table with columns like this:
> col1 (a1 or a2 or a3), col2 (b1 or b2 or b3), col3 (c1), col4(c2), col5(c3
).
> a1=a2=a3, b1=b2=b3
> How to build query for this?
> Thanks in advance.
>