Need to convert Date from "YYYY-MM-DD 00:00:00.0000" to "MM/DD/YYYY&qu

Hello, I was asked to run a query to retrieve some data for my boss. The
query works just fine, however, my boss wants to import the data into his
excel spreadsheet, and the date values as displayed in the result set in
query analyzer are in the format "YYYY-MM-DD 00:00:00.0000".
Somehow, I need to be able to convert the date to "MM/DD/YYYY" and trim off
all of the time stamp crap. I tried some CONVERT/CAST functions to no avail
.
This is sort of urgent, PLEASE HELP!!
Thank you...
RGAlso, my date does need the forward slashes too (/) as in "mm/dd/yyyyy".
Thanks.
RG
Robert G wrote:
>Hello, I was asked to run a query to retrieve some data for my boss. The
>query works just fine, however, my boss wants to import the data into his
>excel spreadsheet, and the date values as displayed in the result set in
>query analyzer are in the format "YYYY-MM-DD 00:00:00.0000".
>Somehow, I need to be able to convert the date to "MM/DD/YYYY" and trim off
>all of the time stamp crap. I tried some CONVERT/CAST functions to no avai
l.
>This is sort of urgent, PLEASE HELP!!
>Thank you...
>RG|||If its left in a datetime data type, the time portion will always be
returned. Instead, convert it to a string using something like the
following,
select convert(varchar(10), getdate(), 101)
--Brian
(Please reply to the newsgroups only.)
"Robert G via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:546B8E3963E09@.webservertalk.com...
> Hello, I was asked to run a query to retrieve some data for my boss. The
> query works just fine, however, my boss wants to import the data into his
> excel spreadsheet, and the date values as displayed in the result set in
> query analyzer are in the format "YYYY-MM-DD 00:00:00.0000".
> Somehow, I need to be able to convert the date to "MM/DD/YYYY" and trim
> off
> all of the time stamp crap. I tried some CONVERT/CAST functions to no
> avail.
> This is sort of urgent, PLEASE HELP!!
> Thank you...
> RG|||SQL Server does not have a "date only" data type, so you will need to jump
through some hoops...
Since your boss is importing the data into Excel, my first recommendation
would be to leave the data as it is, and just format it appropriately in
Excel. Excel can easily suppress the display of the time values, and it will
properly recognize the values as date values.
If that is not an option, you could try a kludge such as "SELECT
CONVERT(CHAR(8), DateColumn, 112), <other columns here> FROM UnknownTable",
which will output a character column formatted as 'YYYYMMDD'. Depending on
how you are making this output available to Excel, Excel may or may not
determine that this is a Date column, and may not provide proper sorting
functionality.
"Robert G via webservertalk.com" <forum@.webservertalk.com> wrote in message
news:546B8E3963E09@.webservertalk.com...
> Hello, I was asked to run a query to retrieve some data for my boss. The
> query works just fine, however, my boss wants to import the data into his
> excel spreadsheet, and the date values as displayed in the result set in
> query analyzer are in the format "YYYY-MM-DD 00:00:00.0000".
> Somehow, I need to be able to convert the date to "MM/DD/YYYY" and trim
> off
> all of the time stamp crap. I tried some CONVERT/CAST functions to no
> avail.
> This is sort of urgent, PLEASE HELP!!
> Thank you...
> RG|||References: <546B8E3963E09@.webservertalk.com>
In-Reply-To: <546B8E3963E09@.webservertalk.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Message-ID: <#Wf$YLkuFHA.860@.TK2MSFTNGP10.phx.gbl>
Newsgroups: microsoft.public.sqlserver.programming
NNTP-Posting-Host: 208.13.225.3
Path: TK2MSFTNGP08.phx.gbl!TK2MSFTNGP10.phx.gbl
Lines: 1
Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:552942
in addition to the other comments - you could simply change the boss's
spreadsheet column to display date format. :)
Robert G via webservertalk.com wrote:

>Hello, I was asked to run a query to retrieve some data for my boss. The
>query works just fine, however, my boss wants to import the data into his
>excel spreadsheet, and the date values as displayed in the result set in
>query analyzer are in the format "YYYY-MM-DD 00:00:00.0000".
>Somehow, I need to be able to convert the date to "MM/DD/YYYY" and trim off
>all of the time stamp crap. I tried some CONVERT/CAST functions to no avai
l.
>This is sort of urgent, PLEASE HELP!!
>Thank you...
>RG
>|||Hell yes, thanks Brian!!! That worked like a freakin charm !! I did the
following:
convert(varchar(10), [MyDateField], 101)
I'd really like to thank everyone else who responded as well !!! I hope I
can return the favor some day.
RG
Brian Lawton wrote:
>If its left in a datetime data type, the time portion will always be
>returned. Instead, convert it to a string using something like the
>following,
>select convert(varchar(10), getdate(), 101)
>
>[quoted text clipped - 10 lines]|||I know what it is like to have a boss who is a total idiot, but you
might wantot get him/her a copy of ISO-8601 and ask why he is smarter
than the entire world. I would love to hear his/her reply :)
The kludge for the moron is a CONVERT() in a VIEW.|||Hey thanks for your input. That does what you said it would, but excel
doesn't recognize it as a date format. Also, I could've parsed it in the
query and eventually ended up with what I needed, but it would've required a
lot more work than Brian's solution - which took like two seconds.
But thanks so much, I still learned from your response.
RG
Jeremy Williams wrote:
>SQL Server does not have a "date only" data type, so you will need to jump
>through some hoops...
>Since your boss is importing the data into Excel, my first recommendation
>would be to leave the data as it is, and just format it appropriately in
>Excel. Excel can easily suppress the display of the time values, and it wil
l
>properly recognize the values as date values.
>If that is not an option, you could try a kludge such as "SELECT
>CONVERT(CHAR(8), DateColumn, 112), <other columns here> FROM UnknownTable",
>which will output a character column formatted as 'YYYYMMDD'. Depending on
>how you are making this output available to Excel, Excel may or may not
>determine that this is a Date column, and may not provide proper sorting
>functionality.
>
>[quoted text clipped - 10 lines]
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200509/1

Need to convert a given date to fiscal year

In a Sql query I need to convert a given date to fiscal year. Fiscal year
start from 1-Apr to 31-Mar each year and I need the fiscal year in the
format like 2005-06 etc. ThanksOn Thu, 11 May 2006 15:02:15 -0700, Paul wrote:

>In a Sql query I need to convert a given date to fiscal year. Fiscal year
>start from 1-Apr to 31-Mar each year and I need the fiscal year in the
>format like 2005-06 etc. Thanks
>
Hi Paul,
http://www.aspfaq.com/show.asp?id=2519
Hugo Kornelis, SQL Server MVP

Need to collect serverinfo

Is there a way that I can run some batch file or query to go out to all SQL
Servers and collect some ServerInfo such as Windows Version/Service Pack,
SQL Version/Service Pack,CPU ( Number and Type) ,Server Model(HP,Dell),
Memory , Disk Space, NIC settingg(IP address)etc and stick it into a table
for reporting. The more info the better..
Hello,
The below extended proc will give info regarding OS, SQL Server, Memory and
CPU.
Master..xp_msver
Thanks
Hari
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23cHiWIOQHHA.3872@.TK2MSFTNGP06.phx.gbl...
> Is there a way that I can run some batch file or query to go out to all
> SQL Servers and collect some ServerInfo such as Windows Version/Service
> Pack, SQL Version/Service Pack,CPU ( Number and Type) ,Server
> Model(HP,Dell), Memory , Disk Space, NIC settingg(IP address)etc and stick
> it into a table for reporting. The more info the better..
>
sql

Need to collect serverinfo

Is there a way that I can run some batch file or query to go out to all SQL
Servers and collect some ServerInfo such as Windows Version/Service Pack,
SQL Version/Service Pack,CPU ( Number and Type) ,Server Model(HP,Dell),
Memory , Disk Space, NIC settingg(IP address)etc and stick it into a table
for reporting. The more info the better..Hello,
The below extended proc will give info regarding OS, SQL Server, Memory and
CPU.
Master..xp_msver
Thanks
Hari
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23cHiWIOQHHA.3872@.TK2MSFTNGP06.phx.gbl...
> Is there a way that I can run some batch file or query to go out to all
> SQL Servers and collect some ServerInfo such as Windows Version/Service
> Pack, SQL Version/Service Pack,CPU ( Number and Type) ,Server
> Model(HP,Dell), Memory , Disk Space, NIC settingg(IP address)etc and stick
> it into a table for reporting. The more info the better..
>

Need to collect serverinfo

Is there a way that I can run some batch file or query to go out to all SQL
Servers and collect some ServerInfo such as Windows Version/Service Pack,
SQL Version/Service Pack,CPU ( Number and Type) ,Server Model(HP,Dell),
Memory , Disk Space, NIC settingg(IP address)etc and stick it into a table
for reporting. The more info the better..Hello,
The below extended proc will give info regarding OS, SQL Server, Memory and
CPU.
Master..xp_msver
Thanks
Hari
"Hassan" <Hassan@.hotmail.com> wrote in message
news:%23cHiWIOQHHA.3872@.TK2MSFTNGP06.phx.gbl...
> Is there a way that I can run some batch file or query to go out to all
> SQL Servers and collect some ServerInfo such as Windows Version/Service
> Pack, SQL Version/Service Pack,CPU ( Number and Type) ,Server
> Model(HP,Dell), Memory , Disk Space, NIC settingg(IP address)etc and stick
> it into a table for reporting. The more info the better..
>

Need To CalcuThe Number Of Days Between The Current Date And A Stored Date

I need help with creating a query that compares the current date with a stored date field. If the difference between the two dates is greater or equal to 5 days for example, I need to be able to return these records. I am not sure if this can be done through a query alone but any help and suggestions would greatly be appreciated. Thanks in advance.yes it can be done through a query, but the standard sql for it will almost certainly not work in whatever database system you're using

date functions are notoriously non-standard, and each database system pretty much has its own proprietary functions

if you wouldn't mind mentioning which database system you're using, we could probably help you|||Nevermind I figured it out. I am using a SQL server 2000 database to query my data. I used the Datediff() function in conjunction with getdate(). See examples below for anyone else needing help with this topic.

DATEDIFF([day], dbo.table.field, GETDATE()) AS DAYS,
DATEDIFF([HOUR], dbo.table.field, GETDATE()) AS HOURS|||moved to SQL Server forum|||The solution you arrived at will work, but will not benefit from any indexing on your dbo.table.field column. If, instead, you used the DateAdd() function to find the date five days prior to the current date, then the optimizer will be able to use an index on dbo.table.field for comparisons:Where dbo.table.field >= DateAdd(day, -5, GetDate())

Need to build a query

Hey. It's probably a very simple query but I'm not at all good in writing
T-SQL. Anyways, My environment is SQL 2005.
I need to write a stored proc which returns 4 columns to a C# app. All the
data comes from one table only.
The first column will just have 'Name of months'
The second column will have a sum of amount
(-sum of amount in jan in first row,
-sum of amount in feb in row 2,
-sum of amount in feb in row 3,
-sum of amount in feb in row 4)
The third row will have a sum of won per month for this year(just like 2nd
column),
The fourth row will have a sum of Lost per month for this year(just like 2nd
column
I'm very new to SQL 2005.
Thank you.Before we can help you, please help us:
http://www.aspfaq.com/etiquette.asp?id=5006
ML
http://milambda.blogspot.com/|||It sounds like you are trying to get 4 different datasets returned as 4
distinct rows in a single query, simply so you can display them as one
dataset. While this is possible, it is not advisable.
Your best bet is to select whatever the core data is that you will be basing
your calculations on and then process it in your application.
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:5BE2F352-33C6-4A02-88B1-9EC75F843B96@.microsoft.com...
> Hey. It's probably a very simple query but I'm not at all good in writing
> T-SQL. Anyways, My environment is SQL 2005.
> I need to write a stored proc which returns 4 columns to a C# app. All the
> data comes from one table only.
> The first column will just have 'Name of months'
> The second column will have a sum of amount
> (-sum of amount in jan in first row,
> -sum of amount in feb in row 2,
> -sum of amount in feb in row 3,
> -sum of amount in feb in row 4)
> The third row will have a sum of won per month for this year(just like 2nd
> column),
> The fourth row will have a sum of Lost per month for this year(just like
2nd
> column
> I'm very new to SQL 2005.
> Thank you.|||Never mind my last post. In your other post you explained clearly what you
were trying to do. I misunderstood your goals entirely.
"Tejas Parikh" <TejasParikh@.discussions.microsoft.com> wrote in message
news:5BE2F352-33C6-4A02-88B1-9EC75F843B96@.microsoft.com...
> Hey. It's probably a very simple query but I'm not at all good in writing
> T-SQL. Anyways, My environment is SQL 2005.
> I need to write a stored proc which returns 4 columns to a C# app. All the
> data comes from one table only.
> The first column will just have 'Name of months'
> The second column will have a sum of amount
> (-sum of amount in jan in first row,
> -sum of amount in feb in row 2,
> -sum of amount in feb in row 3,
> -sum of amount in feb in row 4)
> The third row will have a sum of won per month for this year(just like 2nd
> column),
> The fourth row will have a sum of Lost per month for this year(just like
2nd
> column
> I'm very new to SQL 2005.
> Thank you.sql

Need textboxs in a table to show zeros if no record found - not a NoRows message

I need a way to have the text boxes in a table to show a 0 if there is no record found for the query (not looking for a NoRows message). I've tried setting a default value for the textbox, but it isn't displayed since the query is empty. Is there a way to setup the query to have an if statement that would return a value of zero for the fields as in: If recordcount =0 then set field to 0?

Here is an example of how to substitute data on the report when none is available in the database. The"0" is the character returned and displayed on the report. This example is from the layout designer and goes into your column. In this example CB0StockStart is the value from the database being returned. I think it is possible to use NULL instead of 0 but can't remember of the top of my head.

=Iif((Fields!CB0StockStart.Value)=0,"0",Fields!CB0StockStart.Value)

|||u should try NOTHING instead of NULL
there is also a COUNT()-function if i remember right
|||

The syntax below is placed within the <Value> expression for the textbox, unfortunantely the textbox still does not appear within the table if there is no data. I think the solution needs to be at the table/query level rather than at the textbox level since the table is associated with a <DataSetName>. Any suggestions on how to return default data with the query.

<Value>=Iif((Fields!SubTotalHours.Value)=Nothing,"0",(Fields!SubTotalHours.Value * Fields!ProcessPercent.Value)/100)</Value>

|||

try something like this in your query

isnull(sum(fieldabc),0)

That will return a '0' when the field is null.

Good luck.

|||Using the ISNULL, but the textbox still does not appear. This seems to be a field level solution, is there something that works at the record level? My hunch is that the table is driven from the record, not the field.|||

stupid question, but is your textbox set to visible?!?

try the expression ="test" and test if you see it, if this works,

the IIF should also work

you could also try to change the format of the cell

greets

|||

The textboxes are visible when the query returns data.

I solved it by creating a record on the database that had zeros and then selecting that record if the original query is null.

Basically the query is:

If exists(Select * from table1 where id='1') select * from table1 where id='1' else select * from table1 where id='0'

sql

Need textboxs in a table to show zeros if no record found - not a NoRows message

I need a way to have the text boxes in a table to show a 0 if there is no record found for the query (not looking for a NoRows message). I've tried setting a default value for the textbox, but it isn't displayed since the query is empty. Is there a way to setup the query to have an if statement that would return a value of zero for the fields as in: If recordcount =0 then set field to 0?

Here is an example of how to substitute data on the report when none is available in the database. The"0" is the character returned and displayed on the report. This example is from the layout designer and goes into your column. In this example CB0StockStart is the value from the database being returned. I think it is possible to use NULL instead of 0 but can't remember of the top of my head.

=Iif((Fields!CB0StockStart.Value)=0,"0",Fields!CB0StockStart.Value)

|||u should try NOTHING instead of NULL
there is also a COUNT()-function if i remember right|||

The syntax below is placed within the <Value> expression for the textbox, unfortunantely the textbox still does not appear within the table if there is no data. I think the solution needs to be at the table/query level rather than at the textbox level since the table is associated with a <DataSetName>. Any suggestions on how to return default data with the query.

<Value>=Iif((Fields!SubTotalHours.Value)=Nothing,"0",(Fields!SubTotalHours.Value * Fields!ProcessPercent.Value)/100)</Value>

|||

try something like this in your query

isnull(sum(fieldabc),0)

That will return a '0' when the field is null.

Good luck.

|||Using the ISNULL, but the textbox still does not appear. This seems to be a field level solution, is there something that works at the record level? My hunch is that the table is driven from the record, not the field.|||

stupid question, but is your textbox set to visible?!?

try the expression ="test" and test if you see it, if this works,

the IIF should also work

you could also try to change the format of the cell

greets

|||

The textboxes are visible when the query returns data.

I solved it by creating a record on the database that had zeros and then selecting that record if the original query is null.

Basically the query is:

If exists(Select * from table1 where id='1') select * from table1 where id='1' else select * from table1 where id='0'

Need syntax help in join from two DB

Hi

I have two databases on my server, I need a simple query with one join between one table from each database.
I looked in the help of FROM clause and found the Argument "table_source" where it explains this :
"If the table or view exists in another database on the same computer running Microsoft SQL Server, use a fully qualified name in the form database.owner.object_name".

Can someone please help me fill the variants ??
My DB name is "Forum" the owner is "DBRND\Administrator" and the table name is "TblUsers", so I tried to write in the FROM clause :
"FROM Forum.DBRND\Administrator.TblUsers" but it doesn't work... so anyone have any idea how should it be ?

Thanks,

Inon.select a.col, b.col
from db1..table1 a, db2..table2 b
where a.colX = b.colX
etc...|||Your object owner is DBRND\Administrator?

Look at the list of tables in Enterprise Manager. They are most likely owned by dbo:

select * FROM Forum.dbo.TblUsers

AND PLEASE DON'T LINK TABLES IN THE WHERE CLAUSE! (My pet peeve...)

select a.col, b.col
from db1..table1 a
inner join db2..table2 b on a.colX = b.colX

Code like a pro!|||(My pet peeve...)Oh, oh! I had a peeve once! Everybody kept feeding it, until it almost ate me.

I've gotten a wee bit jaded since then, but I suspect that you'd noticed.

-PatP|||Thanks A LOT!

Sorry about pushing this thread up... :)

BTW, is joining from two databases on the same server is recommended ? Because I have an option to combine the two databases, so how bad is it (if at all) to leave it like it is.

Inon.

Need summary rows in a query, but how?

I have ORDERS which contain a number of ITEMS. Both are stored as rows in a
single table, tblOrders.
I would like to make a query that returns a list of items, then a total for
the order as a whole. Something like...
ORDER ID PART ID NAME QUANTITY PRICE NET
1000 1 widget 10 10 100
1000 2 gazeeza 5 5 25
125 < summary row
It appears this is the idea behind CUBE or ROLLUP, but as is typical, the
documentation on this is useless. Does anyone have any examples of how to
actually use this and get the output the way you want?
It appears you have to use GROUP BY for this sort of thing, and this brings
up another question. In the examples above, I want the grouping for the
summary to work only on the ORDER ID. However, as far as I can tell, in order
to get any output at all you need to list every column in the GROUP BY. This
kind of defeats the purpose in this case.
Any pointers?
Maury
"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:1AFFFE59-A8F4-4BD1-8626-C9757BE6597C@.microsoft.com...

> ORDER ID PART ID NAME QUANTITY PRICE NET
> 1000 1 widget 10 10 100
> 1000 2 gazeeza 5 5 25
> 125 < summary row
>
That is not the result of a query. It's a report. It usually makes more
sense to use tools like reporting services for this kind of thing.
To do it in SQL you won't need CUBE/ROLLUP. UNION is probably more
appropriate:
SELECT order_id, tot, part_id, name, quantity, price, net
FROM
(SELECT 0 AS tot, order_id, part_id, name, quantity, price, net
FROM tbl_orders
UNION ALL
SELECT 1, order_id, NULL, NULL, NULL, NULL, SUM(net)
FROM tbl_orders
GROUP BY order_id) AS T
ORDER BY order_id, tot, part_id ;
Apparently your Order table is very denormalized. I hope and expect that you
are aware of that.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
sql

Need summary rows in a query, but how?

I have ORDERS which contain a number of ITEMS. Both are stored as rows in a
single table, tblOrders.
I would like to make a query that returns a list of items, then a total for
the order as a whole. Something like...
ORDER ID PART ID NAME QUANTITY PRICE NET
1000 1 widget 10 10 100
1000 2 gazeeza 5 5 25
125 < summary row
It appears this is the idea behind CUBE or ROLLUP, but as is typical, the
documentation on this is useless. Does anyone have any examples of how to
actually use this and get the output the way you want?
It appears you have to use GROUP BY for this sort of thing, and this brings
up another question. In the examples above, I want the grouping for the
summary to work only on the ORDER ID. However, as far as I can tell, in orde
r
to get any output at all you need to list every column in the GROUP BY. This
kind of defeats the purpose in this case.
Any pointers?
Maury"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:1AFFFE59-A8F4-4BD1-8626-C9757BE6597C@.microsoft.com...

> ORDER ID PART ID NAME QUANTITY PRICE NET
> 1000 1 widget 10 10 100
> 1000 2 gazeeza 5 5 25
> 125 < summary row
>
That is not the result of a query. It's a report. It usually makes more
sense to use tools like reporting services for this kind of thing.
To do it in SQL you won't need CUBE/ROLLUP. UNION is probably more
appropriate:
SELECT order_id, tot, part_id, name, quantity, price, net
FROM
(SELECT 0 AS tot, order_id, part_id, name, quantity, price, net
FROM tbl_orders
UNION ALL
SELECT 1, order_id, NULL, NULL, NULL, NULL, SUM(net)
FROM tbl_orders
GROUP BY order_id) AS T
ORDER BY order_id, tot, part_id ;
Apparently your Order table is very denormalized. I hope and expect that you
are aware of that.
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Need summary rows in a query, but how?

I have ORDERS which contain a number of ITEMS. Both are stored as rows in a
single table, tblOrders.
I would like to make a query that returns a list of items, then a total for
the order as a whole. Something like...
ORDER ID PART ID NAME QUANTITY PRICE NET
1000 1 widget 10 10 100
1000 2 gazeeza 5 5 25
125 < summary row
It appears this is the idea behind CUBE or ROLLUP, but as is typical, the
documentation on this is useless. Does anyone have any examples of how to
actually use this and get the output the way you want?
It appears you have to use GROUP BY for this sort of thing, and this brings
up another question. In the examples above, I want the grouping for the
summary to work only on the ORDER ID. However, as far as I can tell, in order
to get any output at all you need to list every column in the GROUP BY. This
kind of defeats the purpose in this case.
Any pointers?
Maury"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:1AFFFE59-A8F4-4BD1-8626-C9757BE6597C@.microsoft.com...
> ORDER ID PART ID NAME QUANTITY PRICE NET
> 1000 1 widget 10 10 100
> 1000 2 gazeeza 5 5 25
> 125 < summary row
>
That is not the result of a query. It's a report. It usually makes more
sense to use tools like reporting services for this kind of thing.
To do it in SQL you won't need CUBE/ROLLUP. UNION is probably more
appropriate:
SELECT order_id, tot, part_id, name, quantity, price, net
FROM
(SELECT 0 AS tot, order_id, part_id, name, quantity, price, net
FROM tbl_orders
UNION ALL
SELECT 1, order_id, NULL, NULL, NULL, NULL, SUM(net)
FROM tbl_orders
GROUP BY order_id) AS T
ORDER BY order_id, tot, part_id ;
Apparently your Order table is very denormalized. I hope and expect that you
are aware of that.
Hope this helps.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Need suggestion on TSql Query with Joins

Hi All,

Please suggest me is there any performance/other differences between the below two queries.

-query1

select T1.name,T1.Number, T2.Dept, T2.Desig

From T1 Inner Join T2 on T1.EID = T2.EID

-query2

select T1.name,T1.Number, T2.Dept, T2.Desig

From T1 Inner Join (Select Dept, Desig From T2) As T2 on T1.EID = T2.EID

Thanks

Senthil

There is no performance difference for your quires.

Both are executed in same way..

Suppose, if your query2, subquery has any distinct or group by clause then the query1 may perform better than query2.

|||Thanks Sekaran!

Need sql to return the result of a query as comma seperated values.

Hi,
I need a sql that returns thequery result as comma seperated list of values, instead of several rows. Below is the scenario...
Table Name - Customer
Columns - CustomerID, Join Date
Say below is the data of Customer table ...
CustomerID JoinDate
1 04/01/2005
2 01/03/2003
3 06/02/2004
4 01/05/2002
5 09/07/2005
Now i want to retrieve all the customerid's who have joined this year. Below is the query that i use for this case.
Select CustomerID from Customer where JoinDate between '01/01/2005' and GetDate()
This gives the below result as two rows.
CustomerID
1
5
But i need to get the result as '1,5' (comma seperated list of resulting values).
Any help is highly appreciated
Thanks in Advance
Ramesh

You need declare a variable :
declare @.value nvarchar(200)
Select@.value=case when @.value is null then '' else @.value+',' end+cast(CustomerID as varchar) from Customer where JoinDate between '01/01/2005' and GetDate()
|||Hi,
You can manage your goal by using COALESCE
Please check the following URLs for sample COALESCE usage for returning the column values of a tables in a string seperated by a delimeter character.
http://www.kodyaz.com/ShowPost.aspx?PostID=76
http://www.kodyaz.com/article.aspx?ArticleID=29

As a sample you can run the below code on Northwind database also
DECLARE @.s as nvarchar(4000)
DECLARE @.char as char(1)
SELECT @.char = ','
SELECT @.s = @.char
SELECT @.s = COALESCE(FirstName + @.char + @.s , '') FROM Employees
SELECT SUBSTRING(@.s, 0, LEN(@.s)-1) AS Employees

I hope this helps,
Eralper
|||

Hi All,

I Solved the problem with the below query...

DECLARE @.CustomerIDs VARCHAR(8000)

SELECT @.CustomerIDs = ISNULL(@.CustomerIDs + ',', '') + CAST(CustomerID AS VARCHAR(10))
FROM CUSTOMER
WHERE JoinDate BETWEEN '01/01/2005' and GetDate()

SELECT @.CustomerIDs AS CustomerID

|||It's great! It really worked fine. Thanks a lot...

Need SQL that will return all records which include a particular v

If I understand you, your table is of OrderItems... One record for each line
item on every order... If that's the case, then the query would look like th
is
Select Distinct OrderID
From OrderItems I
Where Exists
(Select * From OrderItems
Where OrderID = I.OrderID
And Item = 'A')
And Exists
(Select * From OrderItems
Where OrderID = I.OrderID
And Item = 'B')
or...
Select Distinct A.OrderID
From OrderItems A
Join OrderItems B
On B.OrderID = A.OrderID
Where A.Item = 'A'
And B.Item = 'B'
"Larry Woods" wrote:

> I have a situation where I have multiple records, let's say ORDERS, and I
> have a record for each line item included in an order. Now, I want to fin
d
> all ORDERS that includes item A and item G, for example. An ORDER could
> include many additional items but the ORDER #'s that I want returns must
> include AT LEAST item A and G.
> How do I do this?
> TIA,
> Larry Woods
>
>Thanks. I'm going with the join for now. But, here is the next question:
The output of the SELECT is a list of OrderID's. Assuming we have
'customerID' in the Order table AND we have 'customerState' in the Customer
table, how do I "expand" the selection to only give me customers from
California (value="CA"), for example?
Customer Table Order Table
orderID <<<< (SELECT
OrderID...etc.)
customerID <<<<<< customerID (foreign key)
customerState
Again, TIA,
Larry Woods
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:02D90644-CC28-4B8E-9AEF-41E6C30EB034@.microsoft.com...
> If I understand you, your table is of OrderItems... One record for each
line
> item on every order... If that's the case, then the query would look like
this
> Select Distinct OrderID
> From OrderItems I
> Where Exists
> (Select * From OrderItems
> Where OrderID = I.OrderID
> And Item = 'A')
> And Exists
> (Select * From OrderItems
> Where OrderID = I.OrderID
> And Item = 'B')
> or...
> Select Distinct A.OrderID
> From OrderItems A
> Join OrderItems B
> On B.OrderID = A.OrderID
> Where A.Item = 'A'
> And B.Item = 'B'
>
> "Larry Woods" wrote:
>
I
find|||On Sat, 12 Mar 2005 04:51:46 -0700, Larry Woods wrote:

>Thanks. I'm going with the join for now. But, here is the next question:
>The output of the SELECT is a list of OrderID's. Assuming we have
>'customerID' in the Order table AND we have 'customerState' in the Customer
>table, how do I "expand" the selection to only give me customers from
>California (value="CA"), for example?
>Customer Table Order Table
> orderID <<<< (SELECT
>OrderID...etc.)
> customerID <<<<<< customerID (foreign key)
> customerState
>Again, TIA,
Hi Larry,
SELECT o.OrderID
FROM Orders AS o
INNER JOIN Customers AS c
ON c.CustomerID = o.CustomerID
INNER JOIN (SELECT OrderID
FROM OrderItems
WHERE Item IN ('A', 'B')
GROUP BY OrderID
HAVING COUNT(*) = 2) AS oi
ON oi.OrderID = o.OrderID
WHERE c.CustomerState = 'CA'
(untested - see www.aspfaq.com/5006 for the steps required to get a
tested solution)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

need SQL Query Help

I need help with a query,
i have two tables,
name Qty orderID Store
Paper 1000 101 New York
Paper 2000 101 Chicago
Pen 2000 102 New York
Pen 5000 102 Chicago
table two
Purchase
orderID Qty price Date
101 100 $4 7/1/05
101 200 $5 7/15/05
101 360 $3.6 8/5/05
101 150 $5.2 8/30/05
102 400 $6 7/2/05
102 300 $6.5 7/12/05
102 500 $5 8/3/05
for the result, I only want to know the last purchase on each like:
Paper 150 $ 5.2 8/30/05
Pen 500 $ 5. 8/3/05
What should I do in this case? Thanks a lot for your help.
Michael
The following example may get you going. It does not handle time,
change the convert code accordingly.
Since both tables have multiple equivalent orderids, a composite where
will need to be built to ensure uniqueness.
select t1.name, t2.qty, t2.price, t2.date
from table2 t2
inner join table1 t1 on t1.orderid = t2.orderid
where str(t2.orderid) + convert(varchar, t2.date, 112) in
(
select top 1 str(t2.orderid) + convert(varchar, t2.date, 112)
from table2 t2
order by date desc
)
mli wrote:
> I need help with a query,
> i have two tables,
> name Qty orderID Store
> Paper 1000 101 New York
> Paper 2000 101 Chicago
> Pen 2000 102 New York
> Pen 5000 102 Chicago
> table two
> Purchase
> orderID Qty price Date
> 101 100 $4 7/1/05
> 101 200 $5 7/15/05
> 101 360 $3.6 8/5/05
> 101 150 $5.2 8/30/05
> 102 400 $6 7/2/05
> 102 300 $6.5 7/12/05
> 102 500 $5 8/3/05
> for the result, I only want to know the last purchase on each like:
> Paper 150 $ 5.2 8/30/05
> Pen 500 $ 5. 8/3/05
> What should I do in this case? Thanks a lot for your help.
> Michael

need SQL Query Help

I need help with a query,
i have two tables,
name Qty orderID Store
Paper 1000 101 New York
Paper 2000 101 Chicago
Pen 2000 102 New York
Pen 5000 102 Chicago
table two
Purchase
orderID Qty price Date
101 100 $4 7/1/05
101 200 $5 7/15/05
101 360 $3.6 8/5/05
101 150 $5.2 8/30/05
102 400 $6 7/2/05
102 300 $6.5 7/12/05
102 500 $5 8/3/05
for the result, I only want to know the last purchase on each like:
Paper 150 $ 5.2 8/30/05
Pen 500 $ 5. 8/3/05
What should I do in this case? Thanks a lot for your help.
MichaelThe following example may get you going. It does not handle time,
change the convert code accordingly.
Since both tables have multiple equivalent orderids, a composite where
will need to be built to ensure uniqueness.
select t1.name, t2.qty, t2.price, t2.date
from table2 t2
inner join table1 t1 on t1.orderid = t2.orderid
where str(t2.orderid) + convert(varchar, t2.date, 112) in
(
select top 1 str(t2.orderid) + convert(varchar, t2.date, 112)
from table2 t2
order by date desc
)
mli wrote:
> I need help with a query,
> i have two tables,
> name Qty orderID Store
> Paper 1000 101 New York
> Paper 2000 101 Chicago
> Pen 2000 102 New York
> Pen 5000 102 Chicago
> table two
> Purchase
> orderID Qty price Date
> 101 100 $4 7/1/05
> 101 200 $5 7/15/05
> 101 360 $3.6 8/5/05
> 101 150 $5.2 8/30/05
> 102 400 $6 7/2/05
> 102 300 $6.5 7/12/05
> 102 500 $5 8/3/05
> for the result, I only want to know the last purchase on each like:
> Paper 150 $ 5.2 8/30/05
> Pen 500 $ 5. 8/3/05
> What should I do in this case? Thanks a lot for your help.
> Michael

need SQL Query Help

I need help with a query,
i have two tables,
name Qty orderID Store
Paper 1000 101 New York
Paper 2000 101 Chicago
Pen 2000 102 New York
Pen 5000 102 Chicago
table two
Purchase
orderID Qty price Date
101 100 $4 7/1/05
101 200 $5 7/15/05
101 360 $3.6 8/5/05
101 150 $5.2 8/30/05
102 400 $6 7/2/05
102 300 $6.5 7/12/05
102 500 $5 8/3/05
for the result, I only want to know the last purchase on each like:
Paper 150 $ 5.2 8/30/05
Pen 500 $ 5. 8/3/05
What should I do in this case? Thanks a lot for your help.
MichaelThe following example may get you going. It does not handle time,
change the convert code accordingly.
Since both tables have multiple equivalent orderids, a composite where
will need to be built to ensure uniqueness.
select t1.name, t2.qty, t2.price, t2.date
from table2 t2
inner join table1 t1 on t1.orderid = t2.orderid
where str(t2.orderid) + convert(varchar, t2.date, 112) in
(
select top 1 str(t2.orderid) + convert(varchar, t2.date, 112)
from table2 t2
order by date desc
)
mli wrote:
> I need help with a query,
> i have two tables,
> name Qty orderID Store
> Paper 1000 101 New York
> Paper 2000 101 Chicago
> Pen 2000 102 New York
> Pen 5000 102 Chicago
> table two
> Purchase
> orderID Qty price Date
> 101 100 $4 7/1/05
> 101 200 $5 7/15/05
> 101 360 $3.6 8/5/05
> 101 150 $5.2 8/30/05
> 102 400 $6 7/2/05
> 102 300 $6.5 7/12/05
> 102 500 $5 8/3/05
> for the result, I only want to know the last purchase on each like:
> Paper 150 $ 5.2 8/30/05
> Pen 500 $ 5. 8/3/05
> What should I do in this case? Thanks a lot for your help.
> Michael

need sql query

Hi

i have 2 tables
the query that i have written is

select distinct mm.subscriber_id,count(mh.seq_memb_id),mm.PREV_SUB SCRIBER_ID,mm.subscriber_id,mm.DATE_OF_BIRTH,mm.ge nder,decode(mh.elig_status,'Y','YES'),decode(mh.el ig_status,'N','NO')
from hsd_member_master mm,hsd_member_elig_history mh
where mm.seq_memb_id = mh.seq_memb_id
and mm.seq_memb_id in (20621,20622,20623)
group by mm.subscriber_id,mh.seq_memb_id,mm.PREV_SUBSCRIBER _ID,mm.subscriber_id,mm.DATE_OF_BIRTH,mm.gender,mh .elig_status

my result should be
subscriber_id,PREV_SUBSCRIBER_ID,DATE_OF_BIRTH,gen der,no of 'yes' in elig_status,no of 'no' in hsd_member_elig_history.

there is multiple rows for each seq_memb_id in eliDo not use DISTINCT with GROUP BY ;)|||Hi

You are grouping by mh.seq_memb_id but it is not contained in the select clause (except as part of an aggregate function). This looks like a likely candidate for your problem

HTHsql