Distinct list

Let’s say we have two tables with the following values:
Table p1: Product Table p2: Product
-- --
A B
B C
C D
C D
Task #1 - In a single query, generate a unique list of values from both
tables (i.e. A, B, C, D).Hi Danlin,
Select Distinct Product From P1
Union
Select Distinct Product From P2
<Throw in an order by here if you want>
Order By
Product
That should do it.
Richard|||SELECT product
FROM P1
UNION
SELECT product
FROM P2
David Portas
SQL Server MVP
--|||Someone throw me this problem and he wants a single query solution not using
union. Sorry about the confusion. Thanks.
"danlin" wrote:

> Let’s say we have two tables with the following values:
>
> Table p1: Product Table p2: Product
> -- --
> A B
> B C
> C D
> C D
>
> Task #1 - In a single query, generate a unique list of values from both
> tables (i.e. A, B, C, D).
>|||SELECT DISTINCT
COALESCE(P1.product, P2.product)
FROM P1
FULL JOIN P2
ON P1.product = P2.product
--
David Portas
SQL Server MVP
--|||Thanks, you're graet David.
"David Portas" wrote:

> SELECT DISTINCT
> COALESCE(P1.product, P2.product)
> FROM P1
> FULL JOIN P2
> ON P1.product = P2.product
> --
> David Portas
> SQL Server MVP
> --
>
>sql

Displaying first instance of data

Hi,

I'm having some trouble with a report I'm trying to generate. It is essentially activity status of a bunch of users that are on the system. The raw data displays the date and time, user, what state they are currently in and what state they changed to and duration.

My problem is I only need the first and last instance for each day. Since a user can go through many states in a day, the data is quite long and to have to filter through the data by hand is quite cumbersome.

Is there a way display the data but just show only the first instance for that date and last instance for the date assuming i have a parameter that is a date range?

ThanksSelect top 1 [fieldnames ...] from [Tablename] .... Order by Primary key desc

delievers the first and

Select top 1 [fieldnames ...] from [Tablename] .... Order by [Primary key] asc

delievers the last entry.|||I'm not quite sure where to put this:

Here is more information on the problem.

I have 3 group names. First is Name, second is time (Day MM/DD/YY), third is time (H:MM)

What I would like to do is for each day, calculate the maximum and minimum of the time

However when I do Minimum (Date) I get the minimum of ALL the days, and not just for that particular date.

For example, my data has 8 users, and has data for about 30 days on each user. Each day a user can have multiple activities, ranging from 8am to 6pm. All I want to calculate or see, is the first time and the last time for that particular day

Any ideas? I'm trying to create a formula using the minimum function but to no avail.|||What is the database you are using?
Post table structures, sample data and the result you want

Display text in SQL reports

Good evening. This may be an easy question but I am using SMS Reporting
(which is SQL Reporting Services) to generate reports for inventory
collections, etc. I am not the greatest with SQL but is / how can I display
plain text in my reports? This text is descriptive / comment text and not
part of the SQL data itself.
Thanks in advance for any assistance,
UCGOn Jun 26, 4:53 pm, UnderCoverGuy
<UnderCover...@.discussions.microsoft.com> wrote:
> Good evening. This may be an easy question but I am using SMS Reporting
> (which is SQL Reporting Services) to generate reports for inventory
> collections, etc. I am not the greatest with SQL but is / how can I display
> plain text in my reports? This text is descriptive / comment text and not
> part of the SQL data itself.
> Thanks in advance for any assistance,
> UCG
On the Layout view, in the toolbox, select a textbox control and add
it to the report and then click inside the textbox and enter the text.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks - but unfortunately it won't work for this situation. What I have to
do is directly edit SQL code / statements - no controls or GUI.
Any other thoughts anyway?
Thanks Enrique,
UCG|||On Jun 26, 8:06 pm, UnderCoverGuy
<UnderCover...@.discussions.microsoft.com> wrote:
> Thanks - but unfortunately it won't work for this situation. What I have to
> do is directly edit SQL code / statements - no controls or GUI.
> Any other thoughts anyway?
> Thanks Enrique,
> UCG
Could you explain the situation in more detail? I'm not quite
following you.
Enrique Martinez
Sr. Software Consultant|||See below:
---
select distinct
v_GS_Computer_system.Name0 AS "PC Name"
, v_GS_Computer_system.UserName0 AS "User last logged on"
, v_gs_Operating_system.csdversion0 AS "SP Level"
, v_GS_Computer_system.manufacturer0 AS "Make"
, v_GS_Computer_system.model0 AS "Model"
, v_GS_x86_PC_memory.totalphysicalmemory0 AS "RAM Installed"
, v_gs_processor.maxclockspeed0 AS "CPU Speed"
, v_gs_disk.Size0 AS "Disk Size"
, v_R_System.Active0 AS "Active"
from
v_gs_computer_system,
v_GS_x86_PC_memory,
v_GS_processor,
v_GS_video_controller,
v_GS_Operating_system,
v_GS_Network_Adapter_Configur,
v_R_System,
v_gs_disk
WHERE
v_gs_operating_system.resourceid = v_gs_computer_system.resourceid
and v_gs_computer_system.resourceid = v_gs_computer_system.resourceid
and v_GS_x86_PC_memory.resourceid = v_gs_computer_system.resourceid
and v_GS_disk.resourceid = v_gs_computer_system.resourceid
and v_GS_processor.resourceid = v_gs_computer_system.resourceid
and v_GS_Operating_system.caption0 like '%Microsoft Windows 2000 Pro%'
and v_GS_video_controller.CurrentHorizontalResolution0 <> ""
and v_GS_x86_PC_memory.totalphysicalmemory0 > '512384'
and v_gs_processor.maxclockspeed0 > '1400'
and v_gs_disk.Size0 > '10000'
and v_R_System.Active0 = 1
Order by
v_gs_computer_system.name0
---
This isn't the entire report but it should help you get the gist of what I
am trying to do. SMS Reporting (which is a SQL Reporting Services back-end)
is how this is being done (no GUI, no wizards, no controls, etc. but only a
SQL code editor). This report will gather info from the SQL db (such as HD
size, CPU speed, etc.) and display the systems where the criteria is met.
There is a header with the "entire" report that will explain what the data is
for. Now, I need to show more reports with different criteria (but from
within the same report). So, what I have done is copy this report (above)
and paste it at the end of what I have and change the criteria (where RAM <
512000, etc.) so that we see which systems meet different criteria - so that
we can install XP on it. Next, copy / paste the same report to the end of
what is already there and again, change the criteria (maybe HD size needs).
Basically, combining several reports into one. I could break these into
individual reports and link to each of them from the main report but I don't
know how to do that. I need to display a header (or comments) at the
beginning of each so that the reader of the report knows which section is
which. So, unless I can figure out how to "chain" reports, i.e.,
sub-reports, then what I need to do is try and display comments (like a
header - text for each report) along the way at the start of each section
(i.e., comment - "This section shows HD's needing to be upgraded / replaced",
comments like that).
Hope this helps explain it.
Thanks in advance,
UCG|||I think this may be helpful. You can simply select text and give it a
column header and include it in your query. Each row will then have the
text (I'm calling it group_text) and you can group on it and produce your
header row so that it's not repeated. See the following example:
select distinct
'This section shows HDs needing to be upgraded / replaced' AS group_text,
v_GS_Computer_system.Name0 AS "PC Name"
, v_GS_Computer_system.UserName0 AS "User last logged on"
, v_gs_Operating_system.csdversion0 AS "SP Level"
, v_GS_Computer_system.manufacturer0 AS "Make"
, v_GS_Computer_system.model0 AS "Model"
, v_GS_x86_PC_memory.totalphysicalmemory0 AS "RAM Installed"
, v_gs_processor.maxclockspeed0 AS "CPU Speed"
, v_gs_disk.Size0 AS "Disk Size"
, v_R_System.Active0 AS "Active"
from
v_gs_computer_system,
v_GS_x86_PC_memory,
v_GS_processor,
v_GS_video_controller,
v_GS_Operating_system,
v_GS_Network_Adapter_Configur,
v_R_System,
v_gs_disk
WHERE
v_gs_operating_system.resourceid = v_gs_computer_system.resourceid
and v_gs_computer_system.resourceid = v_gs_computer_system.resourceid
and v_GS_x86_PC_memory.resourceid = v_gs_computer_system.resourceid
and v_GS_disk.resourceid = v_gs_computer_system.resourceid
and v_GS_processor.resourceid = v_gs_computer_system.resourceid
and v_GS_Operating_system.caption0 like '%Microsoft Windows 2000 Pro%'
and v_GS_video_controller.CurrentHorizontalResolution0 <> ""
and v_GS_x86_PC_memory.totalphysicalmemory0 > '512384'
and v_gs_processor.maxclockspeed0 > '1400'
and v_gs_disk.Size0 > '10000'
and v_R_System.Active0 = 1
Order by
v_gs_computer_system.name0
"UnderCoverGuy" <UnderCoverGuy@.discussions.microsoft.com> wrote in message
news:5E037208-3BA5-47CF-8B6A-C84F6A1EDC6F@.microsoft.com...
> See below:
> ---
> select distinct
> v_GS_Computer_system.Name0 AS "PC Name"
> , v_GS_Computer_system.UserName0 AS "User last logged on"
> , v_gs_Operating_system.csdversion0 AS "SP Level"
> , v_GS_Computer_system.manufacturer0 AS "Make"
> , v_GS_Computer_system.model0 AS "Model"
> , v_GS_x86_PC_memory.totalphysicalmemory0 AS "RAM Installed"
> , v_gs_processor.maxclockspeed0 AS "CPU Speed"
> , v_gs_disk.Size0 AS "Disk Size"
> , v_R_System.Active0 AS "Active"
> from
> v_gs_computer_system,
> v_GS_x86_PC_memory,
> v_GS_processor,
> v_GS_video_controller,
> v_GS_Operating_system,
> v_GS_Network_Adapter_Configur,
> v_R_System,
> v_gs_disk
> WHERE
> v_gs_operating_system.resourceid = v_gs_computer_system.resourceid
> and v_gs_computer_system.resourceid = v_gs_computer_system.resourceid
> and v_GS_x86_PC_memory.resourceid = v_gs_computer_system.resourceid
> and v_GS_disk.resourceid = v_gs_computer_system.resourceid
> and v_GS_processor.resourceid = v_gs_computer_system.resourceid
> and v_GS_Operating_system.caption0 like '%Microsoft Windows 2000 Pro%'
> and v_GS_video_controller.CurrentHorizontalResolution0 <> ""
> and v_GS_x86_PC_memory.totalphysicalmemory0 > '512384'
> and v_gs_processor.maxclockspeed0 > '1400'
> and v_gs_disk.Size0 > '10000'
> and v_R_System.Active0 = 1
> Order by
> v_gs_computer_system.name0
> ---
> This isn't the entire report but it should help you get the gist of what I
> am trying to do. SMS Reporting (which is a SQL Reporting Services
> back-end)
> is how this is being done (no GUI, no wizards, no controls, etc. but only
> a
> SQL code editor). This report will gather info from the SQL db (such as
> HD
> size, CPU speed, etc.) and display the systems where the criteria is met.
> There is a header with the "entire" report that will explain what the data
> is
> for. Now, I need to show more reports with different criteria (but from
> within the same report). So, what I have done is copy this report (above)
> and paste it at the end of what I have and change the criteria (where RAM
> <
> 512000, etc.) so that we see which systems meet different criteria - so
> that
> we can install XP on it. Next, copy / paste the same report to the end of
> what is already there and again, change the criteria (maybe HD size
> needs).
> Basically, combining several reports into one. I could break these into
> individual reports and link to each of them from the main report but I
> don't
> know how to do that. I need to display a header (or comments) at the
> beginning of each so that the reader of the report knows which section is
> which. So, unless I can figure out how to "chain" reports, i.e.,
> sub-reports, then what I need to do is try and display comments (like a
> header - text for each report) along the way at the start of each section
> (i.e., comment - "This section shows HD's needing to be upgraded /
> replaced",
> comments like that).
> Hope this helps explain it.
>
> Thanks in advance,
> UCG
>|||That was exactly what I needed. You response is MUCH appreciated.
Thanks again,
UCG

Display image in Crystal Report 10

I have to generate a report containing data and images in the database, e.g. product information + product image (kept as "image" type in MSSQL database). The information and images are kept in one table named "ProductInfo". The no. of images may vary depending on user input, e.g. on-shelf date. The expected output of the report is similar to below:

<<image1>>
Item: ABC Shampoo
Product No.: 1111

<<image2>>
Item: XXX Toothpaste
Product No.: 2222

<<image3>>
Item: YYY Shaver
Product No.: 3333

User will input the date range in a VB form and a Crystal Report Preview will show the output. I am currently using VB.NET 2003, Crystal Report 10, MSSQL 2000. Would you please help? Thanks very much.

Regards,Store image path in database field, then use picture box in detail section to display pictures.

U can use graphic location property in format editor of picture box...