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 Quick Help Please

My boss wants a list of sites from our table with other information. Below i
s
the query I am using to get the information. Trouble is, the query returns
between 3 to 5 rows per site because of the history table that keeps a
history of each site. How can I get it to return only the history of the
first site found? Thank you for any help.
SELECT sites.client_site_id, sites.site_name, sites.address,
sites.city, sites.state, sites.zip, acct_number.acct_number_description,
status_types.name,
sites_status_history.status_date
FROM sites INNER JOIN
site_acct_number_join ON sites.site_id =
site_acct_number_join.site_id INNER JOIN
acct_number ON site_acct_number_join.acct_number_id =
acct_number.acct_number_id INNER JOIN
sites_status_history ON sites.site_id =
sites_status_history.site_id INNER JOIN
status_types ON sites_status_history.status_type_id =
status_types.type_id
WHERE (sites.client_site_id IN ('011151', '011152', '036112', '038328',
'059505', '081101', '081111', '081183', '081196', '081200', '081205',
'081213',
'081242', '081248', '081340', '081992', '088477',
'091001', '091003', '091036', '091054', '091055', '091072', '091075',
'091082', '091310', '091878',
'092448', '095005', '095006', '095007', '095327',
'095470', '095514', '095676', '096049', '096057', '096214', '097011',
'097019', '097297', '097306',
'097320', '097356', '097428', '097470', '097472',
'097480', '097625', '097787', '097801', '097805', '097807', '097809',
'097813', '097822', '510245',
'510266', '510268', '510809', '512001', '512009',
'512012', '512023', '512032', '512035', '512037', '512041', '512042',
'512043', '512044', '512048',
'512058', '512060', '512068', '512070', '512071',
'512073', '512076', '512078', '512079', '512080', '512081', '512086',
'512088', '512089', '512090',
'512097', '512107', '512111', '512113', '512125',
'512131', '512145', '512159', '512166', '512176', '512218', '512235',
'512459', '512491', '512492',
'512639', '512695', '512696', '512697', '512742',
'512791', '512819', '512857', '513003', '513028', '513030', '513093',
'513095', '513103', '513131',
'513154', '513268', '513982', '514004', '514007',
'514012', '514014', '514015', '514017', '514018', '514023', '514025',
'514027', '514030', '514031',
'514032', '514033', '514035', '514036', '514037',
'514038', '514039', '514041', '514042', '514043', '514044', '514049',
'514050', '514053', '514054',
'514058', '514060', '514061', '514065', '514067',
'514068', '514070', '514075', '514078', '514079', '514082', '514084',
'514085', '514086', '514098',
'514166', '514177', '514206', '514245', '514456',
'514477', '515401', '515430', '515451', '515629', '515667', '515689',
'516562', '516565', '517711',
'517744', '517771', '517773', '517774', '517787',
'517788', '517790', '517794', '517796', '517801', '517807', '517825',
'517834', '517835', '517836',
'517840', '517848', '517849', '517871', '620600',
'621900', '632100', '632700', '639200', '641400', '643100', '643300',
'647800', '652101', '860201',
'865344', '865637', '865690', 'IL0082', 'IL0083',
'IL0095', 'IL0096', 'IL0107', 'IL0112', 'IL0130', 'IL0133', 'IL0134',
'IL0175', 'IL0178', 'IL0180', 'IL0181',
'IL0214', 'IL0234', 'IL0239', 'IL0241', 'IL0262',
'IL0279', 'IL0290', 'IL0294', 'IL0474', 'IL0479', 'IL2041', 'IL2302',
'IN0041', 'IN0042', 'IN0043', 'IN0045',
'IN0048', 'IN0049', 'IN0051', 'IN0052', 'IN0053',
'IN0054', 'IN0055', 'IN0056', 'IN0057', 'IN0058', 'IN0060', 'IN0061',
'IN0062', 'IN0064', 'IN0066',
'IN0101', 'IN0102', 'IN0107', 'IN0108', 'IN0109',
'IN0128', 'IN0129', 'IN0138', 'IN0141', 'IN0144', 'IN0145', 'IN0146',
'IN0147', 'IN0149', 'IN0332',
'JTX001', 'JTX002', 'JTX003', 'JTX004', 'JTX005',
'JTX006', 'JTX007', 'JTX009', 'JTX010', 'JTX011', 'JTX012', 'JTX101',
'JTX102', 'JTX103', 'JTX104',
'JTX105', 'JTX106', 'JTX107', 'JTX108', 'JTX109',
'JTX231', 'JTX233', 'TX0043', 'TX0064', 'TX0086', 'TX0148', 'TX0149',
'TX0151', 'TX0157', 'TX0158',
'TX0159', 'TX0160', 'TX0161', 'TX0162', 'TX0163',
'TX0164', 'TX0167', 'TX0168', 'TX0174', 'TX0178', 'TX0179', 'TX0180',
'TX0181', 'TX0182', 'TX0183',
'TX0185', 'TX0186', 'TX0187', 'TX0189', 'TX0203',
'WFS113'))
ORDER BY sites.client_site_id, sites_status_history.status_date DESCI don't understand why do you need to list the SiteIDs. Anyway, try this
query \:
SELECT sites.client_site_id, sites.site_name, sites.address,
sites.city, sites.state, sites.zip, acct_number.acct_number_description,
status_types.name,
SH1.status_date
FROM sites INNER JOIN
site_acct_number_join ON sites.site_id =
site_acct_number_join.site_id INNER JOIN
acct_number ON site_acct_number_join.acct_number_id =
acct_number.acct_number_id INNER JOIN
sites_status_history SH1 ON sites.site_id =
SH1.site_id INNER JOIN
status_types ON sites_status_history.status_type_id =
status_types.type_id
WHERE SH1.status_date = (select min(SH2.status_date)
from
sites_status_history SH2
where SH1.site_id =
SH2.site_id )
and (your list of IDs)
Perayu
"Mike Collins" wrote:

> My boss wants a list of sites from our table with other information. Below
is
> the query I am using to get the information. Trouble is, the query returns
> between 3 to 5 rows per site because of the history table that keeps a
> history of each site. How can I get it to return only the history of the
> first site found? Thank you for any help.
> SELECT sites.client_site_id, sites.site_name, sites.address,
> sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> status_types.name,
> sites_status_history.status_date
> FROM sites INNER JOIN
> site_acct_number_join ON sites.site_id =
> site_acct_number_join.site_id INNER JOIN
> acct_number ON site_acct_number_join.acct_number_id
=
> acct_number.acct_number_id INNER JOIN
> sites_status_history ON sites.site_id =
> sites_status_history.site_id INNER JOIN
> status_types ON sites_status_history.status_type_id
=
> status_types.type_id
> WHERE (sites.client_site_id IN ('011151', '011152', '036112', '038328'
,
> '059505', '081101', '081111', '081183', '081196', '081200', '081205',
> '081213',
> '081242', '081248', '081340', '081992', '088477',
> '091001', '091003', '091036', '091054', '091055', '091072', '091075',
> '091082', '091310', '091878',
> '092448', '095005', '095006', '095007', '095327',
> '095470', '095514', '095676', '096049', '096057', '096214', '097011',
> '097019', '097297', '097306',
> '097320', '097356', '097428', '097470', '097472',
> '097480', '097625', '097787', '097801', '097805', '097807', '097809',
> '097813', '097822', '510245',
> '510266', '510268', '510809', '512001', '512009',
> '512012', '512023', '512032', '512035', '512037', '512041', '512042',
> '512043', '512044', '512048',
> '512058', '512060', '512068', '512070', '512071',
> '512073', '512076', '512078', '512079', '512080', '512081', '512086',
> '512088', '512089', '512090',
> '512097', '512107', '512111', '512113', '512125',
> '512131', '512145', '512159', '512166', '512176', '512218', '512235',
> '512459', '512491', '512492',
> '512639', '512695', '512696', '512697', '512742',
> '512791', '512819', '512857', '513003', '513028', '513030', '513093',
> '513095', '513103', '513131',
> '513154', '513268', '513982', '514004', '514007',
> '514012', '514014', '514015', '514017', '514018', '514023', '514025',
> '514027', '514030', '514031',
> '514032', '514033', '514035', '514036', '514037',
> '514038', '514039', '514041', '514042', '514043', '514044', '514049',
> '514050', '514053', '514054',
> '514058', '514060', '514061', '514065', '514067',
> '514068', '514070', '514075', '514078', '514079', '514082', '514084',
> '514085', '514086', '514098',
> '514166', '514177', '514206', '514245', '514456',
> '514477', '515401', '515430', '515451', '515629', '515667', '515689',
> '516562', '516565', '517711',
> '517744', '517771', '517773', '517774', '517787',
> '517788', '517790', '517794', '517796', '517801', '517807', '517825',
> '517834', '517835', '517836',
> '517840', '517848', '517849', '517871', '620600',
> '621900', '632100', '632700', '639200', '641400', '643100', '643300',
> '647800', '652101', '860201',
> '865344', '865637', '865690', 'IL0082', 'IL0083',
> 'IL0095', 'IL0096', 'IL0107', 'IL0112', 'IL0130', 'IL0133', 'IL0134',
> 'IL0175', 'IL0178', 'IL0180', 'IL0181',
> 'IL0214', 'IL0234', 'IL0239', 'IL0241', 'IL0262',
> 'IL0279', 'IL0290', 'IL0294', 'IL0474', 'IL0479', 'IL2041', 'IL2302',
> 'IN0041', 'IN0042', 'IN0043', 'IN0045',
> 'IN0048', 'IN0049', 'IN0051', 'IN0052', 'IN0053',
> 'IN0054', 'IN0055', 'IN0056', 'IN0057', 'IN0058', 'IN0060', 'IN0061',
> 'IN0062', 'IN0064', 'IN0066',
> 'IN0101', 'IN0102', 'IN0107', 'IN0108', 'IN0109',
> 'IN0128', 'IN0129', 'IN0138', 'IN0141', 'IN0144', 'IN0145', 'IN0146',
> 'IN0147', 'IN0149', 'IN0332',
> 'JTX001', 'JTX002', 'JTX003', 'JTX004', 'JTX005',
> 'JTX006', 'JTX007', 'JTX009', 'JTX010', 'JTX011', 'JTX012', 'JTX101',
> 'JTX102', 'JTX103', 'JTX104',
> 'JTX105', 'JTX106', 'JTX107', 'JTX108', 'JTX109',
> 'JTX231', 'JTX233', 'TX0043', 'TX0064', 'TX0086', 'TX0148', 'TX0149',
> 'TX0151', 'TX0157', 'TX0158',
> 'TX0159', 'TX0160', 'TX0161', 'TX0162', 'TX0163',
> 'TX0164', 'TX0167', 'TX0168', 'TX0174', 'TX0178', 'TX0179', 'TX0180',
> 'TX0181', 'TX0182', 'TX0183',
> 'TX0185', 'TX0186', 'TX0187', 'TX0189', 'TX0203',
> 'WFS113'))
> ORDER BY sites.client_site_id, sites_status_history.status_date DESC|||That is excellent. I still have some duplicates because of another table, bu
t
I should be able to follow your logic and get rid of those. Thank you so muc
h.
p.s. Boss wants to see the siteIDs.
"Perayu" wrote:
[vbcol=seagreen]
> I don't understand why do you need to list the SiteIDs. Anyway, try this
> query \:
> SELECT sites.client_site_id, sites.site_name, sites.address,
> sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> status_types.name,
> SH1.status_date
> FROM sites INNER JOIN
> site_acct_number_join ON sites.site_id =
> site_acct_number_join.site_id INNER JOIN
> acct_number ON site_acct_number_join.acct_number_id
=
> acct_number.acct_number_id INNER JOIN
> sites_status_history SH1 ON sites.site_id =
> SH1.site_id INNER JOIN
> status_types ON sites_status_history.status_type_id
=
> status_types.type_id
> WHERE SH1.status_date = (select min(SH2.status_date)
> from
> sites_status_history SH2
> where SH1.site_id =
> SH2.site_id )
> and (your list of IDs)
>
> Perayu
>
> "Mike Collins" wrote:
>|||I thought I could figure out how to get rid of the other duplicate record
that is showing up for most of the records, but have not been able to figure
this out yet.
Can you please help again? The acct_number_description field causes two
records to be returned for most records. Here is the select statement
(modified a little for what I need today). How can this select be modified t
o
keep the acct_number_description from returning two records per site?
SELECT sites.site_id, sites.client_site_id, sites.site_name, sites.address,
sites.city, sites.state, sites.zip, SH1.status_date, status_types.name,
acct_number.acct_number_description
FROM sites
INNER JOIN site_acct_number_join ON sites.site_id =
site_acct_number_join.site_id
INNER JOIN acct_number ON site_acct_number_join.acct_number_id =
acct_number.acct_number_id
INNER JOIN sites_status_history SH1 ON sites.site_id = SH1.site_id
INNER JOIN status_types ON SH1.status_type_id = status_types.type_id
WHERE SH1.status_date = (
select Top 1 SH2.status_date
from sites_status_history SH2
where SH1.site_id = SH2.site_id And SH2.status_type_id = 8)
Order By sites.client_site_id
Thank you so much for your assistance. I also see that you have looked at my
other problem of the day. One other question I have is how did you learn how
to do SQL statements like that? Is there a SQL Server book that covers these
techniques?
"Perayu" wrote:
[vbcol=seagreen]
> I don't understand why do you need to list the SiteIDs. Anyway, try this
> query \:
> SELECT sites.client_site_id, sites.site_name, sites.address,
> sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> status_types.name,
> SH1.status_date
> FROM sites INNER JOIN
> site_acct_number_join ON sites.site_id =
> site_acct_number_join.site_id INNER JOIN
> acct_number ON site_acct_number_join.acct_number_id
=
> acct_number.acct_number_id INNER JOIN
> sites_status_history SH1 ON sites.site_id =
> SH1.site_id INNER JOIN
> status_types ON sites_status_history.status_type_id
=
> status_types.type_id
> WHERE SH1.status_date = (select min(SH2.status_date)
> from
> sites_status_history SH2
> where SH1.site_id =
> SH2.site_id )
> and (your list of IDs)
>
> Perayu
>
> "Mike Collins" wrote:
>

Need Quick Help Please

My boss wants a list of sites from our table with other information. Below is
the query I am using to get the information. Trouble is, the query returns
between 3 to 5 rows per site because of the history table that keeps a
history of each site. How can I get it to return only the history of the
first site found? Thank you for any help.
SELECT sites.client_site_id, sites.site_name, sites.address,
sites.city, sites.state, sites.zip, acct_number.acct_number_description,
status_types.name,
sites_status_history.status_date
FROM sites INNER JOIN
site_acct_number_join ON sites.site_id =
site_acct_number_join.site_id INNER JOIN
acct_number ON site_acct_number_join.acct_number_id =
acct_number.acct_number_id INNER JOIN
sites_status_history ON sites.site_id =
sites_status_history.site_id INNER JOIN
status_types ON sites_status_history.status_type_id =
status_types.type_id
WHERE (sites.client_site_id IN ('011151', '011152', '036112', '038328',
'059505', '081101', '081111', '081183', '081196', '081200', '081205',
'081213',
'081242', '081248', '081340', '081992', '088477',
'091001', '091003', '091036', '091054', '091055', '091072', '091075',
'091082', '091310', '091878',
'092448', '095005', '095006', '095007', '095327',
'095470', '095514', '095676', '096049', '096057', '096214', '097011',
'097019', '097297', '097306',
'097320', '097356', '097428', '097470', '097472',
'097480', '097625', '097787', '097801', '097805', '097807', '097809',
'097813', '097822', '510245',
'510266', '510268', '510809', '512001', '512009',
'512012', '512023', '512032', '512035', '512037', '512041', '512042',
'512043', '512044', '512048',
'512058', '512060', '512068', '512070', '512071',
'512073', '512076', '512078', '512079', '512080', '512081', '512086',
'512088', '512089', '512090',
'512097', '512107', '512111', '512113', '512125',
'512131', '512145', '512159', '512166', '512176', '512218', '512235',
'512459', '512491', '512492',
'512639', '512695', '512696', '512697', '512742',
'512791', '512819', '512857', '513003', '513028', '513030', '513093',
'513095', '513103', '513131',
'513154', '513268', '513982', '514004', '514007',
'514012', '514014', '514015', '514017', '514018', '514023', '514025',
'514027', '514030', '514031',
'514032', '514033', '514035', '514036', '514037',
'514038', '514039', '514041', '514042', '514043', '514044', '514049',
'514050', '514053', '514054',
'514058', '514060', '514061', '514065', '514067',
'514068', '514070', '514075', '514078', '514079', '514082', '514084',
'514085', '514086', '514098',
'514166', '514177', '514206', '514245', '514456',
'514477', '515401', '515430', '515451', '515629', '515667', '515689',
'516562', '516565', '517711',
'517744', '517771', '517773', '517774', '517787',
'517788', '517790', '517794', '517796', '517801', '517807', '517825',
'517834', '517835', '517836',
'517840', '517848', '517849', '517871', '620600',
'621900', '632100', '632700', '639200', '641400', '643100', '643300',
'647800', '652101', '860201',
'865344', '865637', '865690', 'IL0082', 'IL0083',
'IL0095', 'IL0096', 'IL0107', 'IL0112', 'IL0130', 'IL0133', 'IL0134',
'IL0175', 'IL0178', 'IL0180', 'IL0181',
'IL0214', 'IL0234', 'IL0239', 'IL0241', 'IL0262',
'IL0279', 'IL0290', 'IL0294', 'IL0474', 'IL0479', 'IL2041', 'IL2302',
'IN0041', 'IN0042', 'IN0043', 'IN0045',
'IN0048', 'IN0049', 'IN0051', 'IN0052', 'IN0053',
'IN0054', 'IN0055', 'IN0056', 'IN0057', 'IN0058', 'IN0060', 'IN0061',
'IN0062', 'IN0064', 'IN0066',
'IN0101', 'IN0102', 'IN0107', 'IN0108', 'IN0109',
'IN0128', 'IN0129', 'IN0138', 'IN0141', 'IN0144', 'IN0145', 'IN0146',
'IN0147', 'IN0149', 'IN0332',
'JTX001', 'JTX002', 'JTX003', 'JTX004', 'JTX005',
'JTX006', 'JTX007', 'JTX009', 'JTX010', 'JTX011', 'JTX012', 'JTX101',
'JTX102', 'JTX103', 'JTX104',
'JTX105', 'JTX106', 'JTX107', 'JTX108', 'JTX109',
'JTX231', 'JTX233', 'TX0043', 'TX0064', 'TX0086', 'TX0148', 'TX0149',
'TX0151', 'TX0157', 'TX0158',
'TX0159', 'TX0160', 'TX0161', 'TX0162', 'TX0163',
'TX0164', 'TX0167', 'TX0168', 'TX0174', 'TX0178', 'TX0179', 'TX0180',
'TX0181', 'TX0182', 'TX0183',
'TX0185', 'TX0186', 'TX0187', 'TX0189', 'TX0203',
'WFS113'))
ORDER BY sites.client_site_id, sites_status_history.status_date DESC
I don't understand why do you need to list the SiteIDs. Anyway, try this
query \:
SELECT sites.client_site_id, sites.site_name, sites.address,
sites.city, sites.state, sites.zip, acct_number.acct_number_description,
status_types.name,
SH1.status_date
FROM sites INNER JOIN
site_acct_number_join ON sites.site_id =
site_acct_number_join.site_id INNER JOIN
acct_number ON site_acct_number_join.acct_number_id =
acct_number.acct_number_id INNER JOIN
sites_status_history SH1 ON sites.site_id =
SH1.site_id INNER JOIN
status_types ON sites_status_history.status_type_id =
status_types.type_id
WHERE SH1.status_date = (select min(SH2.status_date)
from
sites_status_history SH2
where SH1.site_id =
SH2.site_id )
and (your list of IDs)
Perayu
"Mike Collins" wrote:

> My boss wants a list of sites from our table with other information. Below is
> the query I am using to get the information. Trouble is, the query returns
> between 3 to 5 rows per site because of the history table that keeps a
> history of each site. How can I get it to return only the history of the
> first site found? Thank you for any help.
> SELECT sites.client_site_id, sites.site_name, sites.address,
> sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> status_types.name,
> sites_status_history.status_date
> FROM sites INNER JOIN
> site_acct_number_join ON sites.site_id =
> site_acct_number_join.site_id INNER JOIN
> acct_number ON site_acct_number_join.acct_number_id =
> acct_number.acct_number_id INNER JOIN
> sites_status_history ON sites.site_id =
> sites_status_history.site_id INNER JOIN
> status_types ON sites_status_history.status_type_id =
> status_types.type_id
> WHERE (sites.client_site_id IN ('011151', '011152', '036112', '038328',
> '059505', '081101', '081111', '081183', '081196', '081200', '081205',
> '081213',
> '081242', '081248', '081340', '081992', '088477',
> '091001', '091003', '091036', '091054', '091055', '091072', '091075',
> '091082', '091310', '091878',
> '092448', '095005', '095006', '095007', '095327',
> '095470', '095514', '095676', '096049', '096057', '096214', '097011',
> '097019', '097297', '097306',
> '097320', '097356', '097428', '097470', '097472',
> '097480', '097625', '097787', '097801', '097805', '097807', '097809',
> '097813', '097822', '510245',
> '510266', '510268', '510809', '512001', '512009',
> '512012', '512023', '512032', '512035', '512037', '512041', '512042',
> '512043', '512044', '512048',
> '512058', '512060', '512068', '512070', '512071',
> '512073', '512076', '512078', '512079', '512080', '512081', '512086',
> '512088', '512089', '512090',
> '512097', '512107', '512111', '512113', '512125',
> '512131', '512145', '512159', '512166', '512176', '512218', '512235',
> '512459', '512491', '512492',
> '512639', '512695', '512696', '512697', '512742',
> '512791', '512819', '512857', '513003', '513028', '513030', '513093',
> '513095', '513103', '513131',
> '513154', '513268', '513982', '514004', '514007',
> '514012', '514014', '514015', '514017', '514018', '514023', '514025',
> '514027', '514030', '514031',
> '514032', '514033', '514035', '514036', '514037',
> '514038', '514039', '514041', '514042', '514043', '514044', '514049',
> '514050', '514053', '514054',
> '514058', '514060', '514061', '514065', '514067',
> '514068', '514070', '514075', '514078', '514079', '514082', '514084',
> '514085', '514086', '514098',
> '514166', '514177', '514206', '514245', '514456',
> '514477', '515401', '515430', '515451', '515629', '515667', '515689',
> '516562', '516565', '517711',
> '517744', '517771', '517773', '517774', '517787',
> '517788', '517790', '517794', '517796', '517801', '517807', '517825',
> '517834', '517835', '517836',
> '517840', '517848', '517849', '517871', '620600',
> '621900', '632100', '632700', '639200', '641400', '643100', '643300',
> '647800', '652101', '860201',
> '865344', '865637', '865690', 'IL0082', 'IL0083',
> 'IL0095', 'IL0096', 'IL0107', 'IL0112', 'IL0130', 'IL0133', 'IL0134',
> 'IL0175', 'IL0178', 'IL0180', 'IL0181',
> 'IL0214', 'IL0234', 'IL0239', 'IL0241', 'IL0262',
> 'IL0279', 'IL0290', 'IL0294', 'IL0474', 'IL0479', 'IL2041', 'IL2302',
> 'IN0041', 'IN0042', 'IN0043', 'IN0045',
> 'IN0048', 'IN0049', 'IN0051', 'IN0052', 'IN0053',
> 'IN0054', 'IN0055', 'IN0056', 'IN0057', 'IN0058', 'IN0060', 'IN0061',
> 'IN0062', 'IN0064', 'IN0066',
> 'IN0101', 'IN0102', 'IN0107', 'IN0108', 'IN0109',
> 'IN0128', 'IN0129', 'IN0138', 'IN0141', 'IN0144', 'IN0145', 'IN0146',
> 'IN0147', 'IN0149', 'IN0332',
> 'JTX001', 'JTX002', 'JTX003', 'JTX004', 'JTX005',
> 'JTX006', 'JTX007', 'JTX009', 'JTX010', 'JTX011', 'JTX012', 'JTX101',
> 'JTX102', 'JTX103', 'JTX104',
> 'JTX105', 'JTX106', 'JTX107', 'JTX108', 'JTX109',
> 'JTX231', 'JTX233', 'TX0043', 'TX0064', 'TX0086', 'TX0148', 'TX0149',
> 'TX0151', 'TX0157', 'TX0158',
> 'TX0159', 'TX0160', 'TX0161', 'TX0162', 'TX0163',
> 'TX0164', 'TX0167', 'TX0168', 'TX0174', 'TX0178', 'TX0179', 'TX0180',
> 'TX0181', 'TX0182', 'TX0183',
> 'TX0185', 'TX0186', 'TX0187', 'TX0189', 'TX0203',
> 'WFS113'))
> ORDER BY sites.client_site_id, sites_status_history.status_date DESC
|||That is excellent. I still have some duplicates because of another table, but
I should be able to follow your logic and get rid of those. Thank you so much.
p.s. Boss wants to see the siteIDs.
"Perayu" wrote:
[vbcol=seagreen]
> I don't understand why do you need to list the SiteIDs. Anyway, try this
> query \:
> SELECT sites.client_site_id, sites.site_name, sites.address,
> sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> status_types.name,
> SH1.status_date
> FROM sites INNER JOIN
> site_acct_number_join ON sites.site_id =
> site_acct_number_join.site_id INNER JOIN
> acct_number ON site_acct_number_join.acct_number_id =
> acct_number.acct_number_id INNER JOIN
> sites_status_history SH1 ON sites.site_id =
> SH1.site_id INNER JOIN
> status_types ON sites_status_history.status_type_id =
> status_types.type_id
> WHERE SH1.status_date = (select min(SH2.status_date)
> from
> sites_status_history SH2
> where SH1.site_id =
> SH2.site_id )
> and (your list of IDs)
>
> Perayu
>
> "Mike Collins" wrote:
|||I thought I could figure out how to get rid of the other duplicate record
that is showing up for most of the records, but have not been able to figure
this out yet.
Can you please help again? The acct_number_description field causes two
records to be returned for most records. Here is the select statement
(modified a little for what I need today). How can this select be modified to
keep the acct_number_description from returning two records per site?
SELECT sites.site_id, sites.client_site_id, sites.site_name, sites.address,
sites.city, sites.state, sites.zip, SH1.status_date, status_types.name,
acct_number.acct_number_description
FROM sites
INNER JOIN site_acct_number_join ON sites.site_id =
site_acct_number_join.site_id
INNER JOIN acct_number ON site_acct_number_join.acct_number_id =
acct_number.acct_number_id
INNER JOIN sites_status_history SH1 ON sites.site_id = SH1.site_id
INNER JOIN status_types ON SH1.status_type_id = status_types.type_id
WHERE SH1.status_date = (
select Top 1 SH2.status_date
from sites_status_history SH2
where SH1.site_id = SH2.site_id And SH2.status_type_id = 8)
Order By sites.client_site_id
Thank you so much for your assistance. I also see that you have looked at my
other problem of the day. One other question I have is how did you learn how
to do SQL statements like that? Is there a SQL Server book that covers these
techniques?
"Perayu" wrote:
[vbcol=seagreen]
> I don't understand why do you need to list the SiteIDs. Anyway, try this
> query \:
> SELECT sites.client_site_id, sites.site_name, sites.address,
> sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> status_types.name,
> SH1.status_date
> FROM sites INNER JOIN
> site_acct_number_join ON sites.site_id =
> site_acct_number_join.site_id INNER JOIN
> acct_number ON site_acct_number_join.acct_number_id =
> acct_number.acct_number_id INNER JOIN
> sites_status_history SH1 ON sites.site_id =
> SH1.site_id INNER JOIN
> status_types ON sites_status_history.status_type_id =
> status_types.type_id
> WHERE SH1.status_date = (select min(SH2.status_date)
> from
> sites_status_history SH2
> where SH1.site_id =
> SH2.site_id )
> and (your list of IDs)
>
> Perayu
>
> "Mike Collins" wrote:

Need Quick Help Please

My boss wants a list of sites from our table with other information. Below is
the query I am using to get the information. Trouble is, the query returns
between 3 to 5 rows per site because of the history table that keeps a
history of each site. How can I get it to return only the history of the
first site found? Thank you for any help.
SELECT sites.client_site_id, sites.site_name, sites.address,
sites.city, sites.state, sites.zip, acct_number.acct_number_description,
status_types.name,
sites_status_history.status_date
FROM sites INNER JOIN
site_acct_number_join ON sites.site_id = site_acct_number_join.site_id INNER JOIN
acct_number ON site_acct_number_join.acct_number_id = acct_number.acct_number_id INNER JOIN
sites_status_history ON sites.site_id = sites_status_history.site_id INNER JOIN
status_types ON sites_status_history.status_type_id = status_types.type_id
WHERE (sites.client_site_id IN ('011151', '011152', '036112', '038328',
'059505', '081101', '081111', '081183', '081196', '081200', '081205',
'081213',
'081242', '081248', '081340', '081992', '088477',
'091001', '091003', '091036', '091054', '091055', '091072', '091075',
'091082', '091310', '091878',
'092448', '095005', '095006', '095007', '095327',
'095470', '095514', '095676', '096049', '096057', '096214', '097011',
'097019', '097297', '097306',
'097320', '097356', '097428', '097470', '097472',
'097480', '097625', '097787', '097801', '097805', '097807', '097809',
'097813', '097822', '510245',
'510266', '510268', '510809', '512001', '512009',
'512012', '512023', '512032', '512035', '512037', '512041', '512042',
'512043', '512044', '512048',
'512058', '512060', '512068', '512070', '512071',
'512073', '512076', '512078', '512079', '512080', '512081', '512086',
'512088', '512089', '512090',
'512097', '512107', '512111', '512113', '512125',
'512131', '512145', '512159', '512166', '512176', '512218', '512235',
'512459', '512491', '512492',
'512639', '512695', '512696', '512697', '512742',
'512791', '512819', '512857', '513003', '513028', '513030', '513093',
'513095', '513103', '513131',
'513154', '513268', '513982', '514004', '514007',
'514012', '514014', '514015', '514017', '514018', '514023', '514025',
'514027', '514030', '514031',
'514032', '514033', '514035', '514036', '514037',
'514038', '514039', '514041', '514042', '514043', '514044', '514049',
'514050', '514053', '514054',
'514058', '514060', '514061', '514065', '514067',
'514068', '514070', '514075', '514078', '514079', '514082', '514084',
'514085', '514086', '514098',
'514166', '514177', '514206', '514245', '514456',
'514477', '515401', '515430', '515451', '515629', '515667', '515689',
'516562', '516565', '517711',
'517744', '517771', '517773', '517774', '517787',
'517788', '517790', '517794', '517796', '517801', '517807', '517825',
'517834', '517835', '517836',
'517840', '517848', '517849', '517871', '620600',
'621900', '632100', '632700', '639200', '641400', '643100', '643300',
'647800', '652101', '860201',
'865344', '865637', '865690', 'IL0082', 'IL0083',
'IL0095', 'IL0096', 'IL0107', 'IL0112', 'IL0130', 'IL0133', 'IL0134',
'IL0175', 'IL0178', 'IL0180', 'IL0181',
'IL0214', 'IL0234', 'IL0239', 'IL0241', 'IL0262',
'IL0279', 'IL0290', 'IL0294', 'IL0474', 'IL0479', 'IL2041', 'IL2302',
'IN0041', 'IN0042', 'IN0043', 'IN0045',
'IN0048', 'IN0049', 'IN0051', 'IN0052', 'IN0053',
'IN0054', 'IN0055', 'IN0056', 'IN0057', 'IN0058', 'IN0060', 'IN0061',
'IN0062', 'IN0064', 'IN0066',
'IN0101', 'IN0102', 'IN0107', 'IN0108', 'IN0109',
'IN0128', 'IN0129', 'IN0138', 'IN0141', 'IN0144', 'IN0145', 'IN0146',
'IN0147', 'IN0149', 'IN0332',
'JTX001', 'JTX002', 'JTX003', 'JTX004', 'JTX005',
'JTX006', 'JTX007', 'JTX009', 'JTX010', 'JTX011', 'JTX012', 'JTX101',
'JTX102', 'JTX103', 'JTX104',
'JTX105', 'JTX106', 'JTX107', 'JTX108', 'JTX109',
'JTX231', 'JTX233', 'TX0043', 'TX0064', 'TX0086', 'TX0148', 'TX0149',
'TX0151', 'TX0157', 'TX0158',
'TX0159', 'TX0160', 'TX0161', 'TX0162', 'TX0163',
'TX0164', 'TX0167', 'TX0168', 'TX0174', 'TX0178', 'TX0179', 'TX0180',
'TX0181', 'TX0182', 'TX0183',
'TX0185', 'TX0186', 'TX0187', 'TX0189', 'TX0203',
'WFS113'))
ORDER BY sites.client_site_id, sites_status_history.status_date DESCI don't understand why do you need to list the SiteIDs. Anyway, try this
query \:
SELECT sites.client_site_id, sites.site_name, sites.address,
sites.city, sites.state, sites.zip, acct_number.acct_number_description,
status_types.name,
SH1.status_date
FROM sites INNER JOIN
site_acct_number_join ON sites.site_id = site_acct_number_join.site_id INNER JOIN
acct_number ON site_acct_number_join.acct_number_id = acct_number.acct_number_id INNER JOIN
sites_status_history SH1 ON sites.site_id = SH1.site_id INNER JOIN
status_types ON sites_status_history.status_type_id = status_types.type_id
WHERE SH1.status_date = (select min(SH2.status_date)
from
sites_status_history SH2
where SH1.site_id =SH2.site_id )
and (your list of IDs)
Perayu
"Mike Collins" wrote:
> My boss wants a list of sites from our table with other information. Below is
> the query I am using to get the information. Trouble is, the query returns
> between 3 to 5 rows per site because of the history table that keeps a
> history of each site. How can I get it to return only the history of the
> first site found? Thank you for any help.
> SELECT sites.client_site_id, sites.site_name, sites.address,
> sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> status_types.name,
> sites_status_history.status_date
> FROM sites INNER JOIN
> site_acct_number_join ON sites.site_id => site_acct_number_join.site_id INNER JOIN
> acct_number ON site_acct_number_join.acct_number_id => acct_number.acct_number_id INNER JOIN
> sites_status_history ON sites.site_id => sites_status_history.site_id INNER JOIN
> status_types ON sites_status_history.status_type_id => status_types.type_id
> WHERE (sites.client_site_id IN ('011151', '011152', '036112', '038328',
> '059505', '081101', '081111', '081183', '081196', '081200', '081205',
> '081213',
> '081242', '081248', '081340', '081992', '088477',
> '091001', '091003', '091036', '091054', '091055', '091072', '091075',
> '091082', '091310', '091878',
> '092448', '095005', '095006', '095007', '095327',
> '095470', '095514', '095676', '096049', '096057', '096214', '097011',
> '097019', '097297', '097306',
> '097320', '097356', '097428', '097470', '097472',
> '097480', '097625', '097787', '097801', '097805', '097807', '097809',
> '097813', '097822', '510245',
> '510266', '510268', '510809', '512001', '512009',
> '512012', '512023', '512032', '512035', '512037', '512041', '512042',
> '512043', '512044', '512048',
> '512058', '512060', '512068', '512070', '512071',
> '512073', '512076', '512078', '512079', '512080', '512081', '512086',
> '512088', '512089', '512090',
> '512097', '512107', '512111', '512113', '512125',
> '512131', '512145', '512159', '512166', '512176', '512218', '512235',
> '512459', '512491', '512492',
> '512639', '512695', '512696', '512697', '512742',
> '512791', '512819', '512857', '513003', '513028', '513030', '513093',
> '513095', '513103', '513131',
> '513154', '513268', '513982', '514004', '514007',
> '514012', '514014', '514015', '514017', '514018', '514023', '514025',
> '514027', '514030', '514031',
> '514032', '514033', '514035', '514036', '514037',
> '514038', '514039', '514041', '514042', '514043', '514044', '514049',
> '514050', '514053', '514054',
> '514058', '514060', '514061', '514065', '514067',
> '514068', '514070', '514075', '514078', '514079', '514082', '514084',
> '514085', '514086', '514098',
> '514166', '514177', '514206', '514245', '514456',
> '514477', '515401', '515430', '515451', '515629', '515667', '515689',
> '516562', '516565', '517711',
> '517744', '517771', '517773', '517774', '517787',
> '517788', '517790', '517794', '517796', '517801', '517807', '517825',
> '517834', '517835', '517836',
> '517840', '517848', '517849', '517871', '620600',
> '621900', '632100', '632700', '639200', '641400', '643100', '643300',
> '647800', '652101', '860201',
> '865344', '865637', '865690', 'IL0082', 'IL0083',
> 'IL0095', 'IL0096', 'IL0107', 'IL0112', 'IL0130', 'IL0133', 'IL0134',
> 'IL0175', 'IL0178', 'IL0180', 'IL0181',
> 'IL0214', 'IL0234', 'IL0239', 'IL0241', 'IL0262',
> 'IL0279', 'IL0290', 'IL0294', 'IL0474', 'IL0479', 'IL2041', 'IL2302',
> 'IN0041', 'IN0042', 'IN0043', 'IN0045',
> 'IN0048', 'IN0049', 'IN0051', 'IN0052', 'IN0053',
> 'IN0054', 'IN0055', 'IN0056', 'IN0057', 'IN0058', 'IN0060', 'IN0061',
> 'IN0062', 'IN0064', 'IN0066',
> 'IN0101', 'IN0102', 'IN0107', 'IN0108', 'IN0109',
> 'IN0128', 'IN0129', 'IN0138', 'IN0141', 'IN0144', 'IN0145', 'IN0146',
> 'IN0147', 'IN0149', 'IN0332',
> 'JTX001', 'JTX002', 'JTX003', 'JTX004', 'JTX005',
> 'JTX006', 'JTX007', 'JTX009', 'JTX010', 'JTX011', 'JTX012', 'JTX101',
> 'JTX102', 'JTX103', 'JTX104',
> 'JTX105', 'JTX106', 'JTX107', 'JTX108', 'JTX109',
> 'JTX231', 'JTX233', 'TX0043', 'TX0064', 'TX0086', 'TX0148', 'TX0149',
> 'TX0151', 'TX0157', 'TX0158',
> 'TX0159', 'TX0160', 'TX0161', 'TX0162', 'TX0163',
> 'TX0164', 'TX0167', 'TX0168', 'TX0174', 'TX0178', 'TX0179', 'TX0180',
> 'TX0181', 'TX0182', 'TX0183',
> 'TX0185', 'TX0186', 'TX0187', 'TX0189', 'TX0203',
> 'WFS113'))
> ORDER BY sites.client_site_id, sites_status_history.status_date DESC|||That is excellent. I still have some duplicates because of another table, but
I should be able to follow your logic and get rid of those. Thank you so much.
p.s. Boss wants to see the siteIDs.
"Perayu" wrote:
> I don't understand why do you need to list the SiteIDs. Anyway, try this
> query \:
> SELECT sites.client_site_id, sites.site_name, sites.address,
> sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> status_types.name,
> SH1.status_date
> FROM sites INNER JOIN
> site_acct_number_join ON sites.site_id => site_acct_number_join.site_id INNER JOIN
> acct_number ON site_acct_number_join.acct_number_id => acct_number.acct_number_id INNER JOIN
> sites_status_history SH1 ON sites.site_id => SH1.site_id INNER JOIN
> status_types ON sites_status_history.status_type_id => status_types.type_id
> WHERE SH1.status_date = (select min(SH2.status_date)
> from
> sites_status_history SH2
> where SH1.site_id => SH2.site_id )
> and (your list of IDs)
>
> Perayu
>
> "Mike Collins" wrote:
> > My boss wants a list of sites from our table with other information. Below is
> > the query I am using to get the information. Trouble is, the query returns
> > between 3 to 5 rows per site because of the history table that keeps a
> > history of each site. How can I get it to return only the history of the
> > first site found? Thank you for any help.
> >
> > SELECT sites.client_site_id, sites.site_name, sites.address,
> > sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> > status_types.name,
> > sites_status_history.status_date
> > FROM sites INNER JOIN
> > site_acct_number_join ON sites.site_id => > site_acct_number_join.site_id INNER JOIN
> > acct_number ON site_acct_number_join.acct_number_id => > acct_number.acct_number_id INNER JOIN
> > sites_status_history ON sites.site_id => > sites_status_history.site_id INNER JOIN
> > status_types ON sites_status_history.status_type_id => > status_types.type_id
> > WHERE (sites.client_site_id IN ('011151', '011152', '036112', '038328',
> > '059505', '081101', '081111', '081183', '081196', '081200', '081205',
> > '081213',
> > '081242', '081248', '081340', '081992', '088477',
> > '091001', '091003', '091036', '091054', '091055', '091072', '091075',
> > '091082', '091310', '091878',
> > '092448', '095005', '095006', '095007', '095327',
> > '095470', '095514', '095676', '096049', '096057', '096214', '097011',
> > '097019', '097297', '097306',
> > '097320', '097356', '097428', '097470', '097472',
> > '097480', '097625', '097787', '097801', '097805', '097807', '097809',
> > '097813', '097822', '510245',
> > '510266', '510268', '510809', '512001', '512009',
> > '512012', '512023', '512032', '512035', '512037', '512041', '512042',
> > '512043', '512044', '512048',
> > '512058', '512060', '512068', '512070', '512071',
> > '512073', '512076', '512078', '512079', '512080', '512081', '512086',
> > '512088', '512089', '512090',
> > '512097', '512107', '512111', '512113', '512125',
> > '512131', '512145', '512159', '512166', '512176', '512218', '512235',
> > '512459', '512491', '512492',
> > '512639', '512695', '512696', '512697', '512742',
> > '512791', '512819', '512857', '513003', '513028', '513030', '513093',
> > '513095', '513103', '513131',
> > '513154', '513268', '513982', '514004', '514007',
> > '514012', '514014', '514015', '514017', '514018', '514023', '514025',
> > '514027', '514030', '514031',
> > '514032', '514033', '514035', '514036', '514037',
> > '514038', '514039', '514041', '514042', '514043', '514044', '514049',
> > '514050', '514053', '514054',
> > '514058', '514060', '514061', '514065', '514067',
> > '514068', '514070', '514075', '514078', '514079', '514082', '514084',
> > '514085', '514086', '514098',
> > '514166', '514177', '514206', '514245', '514456',
> > '514477', '515401', '515430', '515451', '515629', '515667', '515689',
> > '516562', '516565', '517711',
> > '517744', '517771', '517773', '517774', '517787',
> > '517788', '517790', '517794', '517796', '517801', '517807', '517825',
> > '517834', '517835', '517836',
> > '517840', '517848', '517849', '517871', '620600',
> > '621900', '632100', '632700', '639200', '641400', '643100', '643300',
> > '647800', '652101', '860201',
> > '865344', '865637', '865690', 'IL0082', 'IL0083',
> > 'IL0095', 'IL0096', 'IL0107', 'IL0112', 'IL0130', 'IL0133', 'IL0134',
> > 'IL0175', 'IL0178', 'IL0180', 'IL0181',
> > 'IL0214', 'IL0234', 'IL0239', 'IL0241', 'IL0262',
> > 'IL0279', 'IL0290', 'IL0294', 'IL0474', 'IL0479', 'IL2041', 'IL2302',
> > 'IN0041', 'IN0042', 'IN0043', 'IN0045',
> > 'IN0048', 'IN0049', 'IN0051', 'IN0052', 'IN0053',
> > 'IN0054', 'IN0055', 'IN0056', 'IN0057', 'IN0058', 'IN0060', 'IN0061',
> > 'IN0062', 'IN0064', 'IN0066',
> > 'IN0101', 'IN0102', 'IN0107', 'IN0108', 'IN0109',
> > 'IN0128', 'IN0129', 'IN0138', 'IN0141', 'IN0144', 'IN0145', 'IN0146',
> > 'IN0147', 'IN0149', 'IN0332',
> > 'JTX001', 'JTX002', 'JTX003', 'JTX004', 'JTX005',
> > 'JTX006', 'JTX007', 'JTX009', 'JTX010', 'JTX011', 'JTX012', 'JTX101',
> > 'JTX102', 'JTX103', 'JTX104',
> > 'JTX105', 'JTX106', 'JTX107', 'JTX108', 'JTX109',
> > 'JTX231', 'JTX233', 'TX0043', 'TX0064', 'TX0086', 'TX0148', 'TX0149',
> > 'TX0151', 'TX0157', 'TX0158',
> > 'TX0159', 'TX0160', 'TX0161', 'TX0162', 'TX0163',
> > 'TX0164', 'TX0167', 'TX0168', 'TX0174', 'TX0178', 'TX0179', 'TX0180',
> > 'TX0181', 'TX0182', 'TX0183',
> > 'TX0185', 'TX0186', 'TX0187', 'TX0189', 'TX0203',
> > 'WFS113'))
> > ORDER BY sites.client_site_id, sites_status_history.status_date DESC|||I thought I could figure out how to get rid of the other duplicate record
that is showing up for most of the records, but have not been able to figure
this out yet.
Can you please help again? The acct_number_description field causes two
records to be returned for most records. Here is the select statement
(modified a little for what I need today). How can this select be modified to
keep the acct_number_description from returning two records per site?
SELECT sites.site_id, sites.client_site_id, sites.site_name, sites.address,
sites.city, sites.state, sites.zip, SH1.status_date, status_types.name,
acct_number.acct_number_description
FROM sites
INNER JOIN site_acct_number_join ON sites.site_id =site_acct_number_join.site_id
INNER JOIN acct_number ON site_acct_number_join.acct_number_id =acct_number.acct_number_id
INNER JOIN sites_status_history SH1 ON sites.site_id = SH1.site_id
INNER JOIN status_types ON SH1.status_type_id = status_types.type_id
WHERE SH1.status_date = (
select Top 1 SH2.status_date
from sites_status_history SH2
where SH1.site_id = SH2.site_id And SH2.status_type_id = 8)
Order By sites.client_site_id
Thank you so much for your assistance. I also see that you have looked at my
other problem of the day. One other question I have is how did you learn how
to do SQL statements like that? Is there a SQL Server book that covers these
techniques?
"Perayu" wrote:
> I don't understand why do you need to list the SiteIDs. Anyway, try this
> query \:
> SELECT sites.client_site_id, sites.site_name, sites.address,
> sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> status_types.name,
> SH1.status_date
> FROM sites INNER JOIN
> site_acct_number_join ON sites.site_id => site_acct_number_join.site_id INNER JOIN
> acct_number ON site_acct_number_join.acct_number_id => acct_number.acct_number_id INNER JOIN
> sites_status_history SH1 ON sites.site_id => SH1.site_id INNER JOIN
> status_types ON sites_status_history.status_type_id => status_types.type_id
> WHERE SH1.status_date = (select min(SH2.status_date)
> from
> sites_status_history SH2
> where SH1.site_id => SH2.site_id )
> and (your list of IDs)
>
> Perayu
>
> "Mike Collins" wrote:
> > My boss wants a list of sites from our table with other information. Below is
> > the query I am using to get the information. Trouble is, the query returns
> > between 3 to 5 rows per site because of the history table that keeps a
> > history of each site. How can I get it to return only the history of the
> > first site found? Thank you for any help.
> >
> > SELECT sites.client_site_id, sites.site_name, sites.address,
> > sites.city, sites.state, sites.zip, acct_number.acct_number_description,
> > status_types.name,
> > sites_status_history.status_date
> > FROM sites INNER JOIN
> > site_acct_number_join ON sites.site_id => > site_acct_number_join.site_id INNER JOIN
> > acct_number ON site_acct_number_join.acct_number_id => > acct_number.acct_number_id INNER JOIN
> > sites_status_history ON sites.site_id => > sites_status_history.site_id INNER JOIN
> > status_types ON sites_status_history.status_type_id => > status_types.type_id
> > WHERE (sites.client_site_id IN ('011151', '011152', '036112', '038328',
> > '059505', '081101', '081111', '081183', '081196', '081200', '081205',
> > '081213',
> > '081242', '081248', '081340', '081992', '088477',
> > '091001', '091003', '091036', '091054', '091055', '091072', '091075',
> > '091082', '091310', '091878',
> > '092448', '095005', '095006', '095007', '095327',
> > '095470', '095514', '095676', '096049', '096057', '096214', '097011',
> > '097019', '097297', '097306',
> > '097320', '097356', '097428', '097470', '097472',
> > '097480', '097625', '097787', '097801', '097805', '097807', '097809',
> > '097813', '097822', '510245',
> > '510266', '510268', '510809', '512001', '512009',
> > '512012', '512023', '512032', '512035', '512037', '512041', '512042',
> > '512043', '512044', '512048',
> > '512058', '512060', '512068', '512070', '512071',
> > '512073', '512076', '512078', '512079', '512080', '512081', '512086',
> > '512088', '512089', '512090',
> > '512097', '512107', '512111', '512113', '512125',
> > '512131', '512145', '512159', '512166', '512176', '512218', '512235',
> > '512459', '512491', '512492',
> > '512639', '512695', '512696', '512697', '512742',
> > '512791', '512819', '512857', '513003', '513028', '513030', '513093',
> > '513095', '513103', '513131',
> > '513154', '513268', '513982', '514004', '514007',
> > '514012', '514014', '514015', '514017', '514018', '514023', '514025',
> > '514027', '514030', '514031',
> > '514032', '514033', '514035', '514036', '514037',
> > '514038', '514039', '514041', '514042', '514043', '514044', '514049',
> > '514050', '514053', '514054',
> > '514058', '514060', '514061', '514065', '514067',
> > '514068', '514070', '514075', '514078', '514079', '514082', '514084',
> > '514085', '514086', '514098',
> > '514166', '514177', '514206', '514245', '514456',
> > '514477', '515401', '515430', '515451', '515629', '515667', '515689',
> > '516562', '516565', '517711',
> > '517744', '517771', '517773', '517774', '517787',
> > '517788', '517790', '517794', '517796', '517801', '517807', '517825',
> > '517834', '517835', '517836',
> > '517840', '517848', '517849', '517871', '620600',
> > '621900', '632100', '632700', '639200', '641400', '643100', '643300',
> > '647800', '652101', '860201',
> > '865344', '865637', '865690', 'IL0082', 'IL0083',
> > 'IL0095', 'IL0096', 'IL0107', 'IL0112', 'IL0130', 'IL0133', 'IL0134',
> > 'IL0175', 'IL0178', 'IL0180', 'IL0181',
> > 'IL0214', 'IL0234', 'IL0239', 'IL0241', 'IL0262',
> > 'IL0279', 'IL0290', 'IL0294', 'IL0474', 'IL0479', 'IL2041', 'IL2302',
> > 'IN0041', 'IN0042', 'IN0043', 'IN0045',
> > 'IN0048', 'IN0049', 'IN0051', 'IN0052', 'IN0053',
> > 'IN0054', 'IN0055', 'IN0056', 'IN0057', 'IN0058', 'IN0060', 'IN0061',
> > 'IN0062', 'IN0064', 'IN0066',
> > 'IN0101', 'IN0102', 'IN0107', 'IN0108', 'IN0109',
> > 'IN0128', 'IN0129', 'IN0138', 'IN0141', 'IN0144', 'IN0145', 'IN0146',
> > 'IN0147', 'IN0149', 'IN0332',
> > 'JTX001', 'JTX002', 'JTX003', 'JTX004', 'JTX005',
> > 'JTX006', 'JTX007', 'JTX009', 'JTX010', 'JTX011', 'JTX012', 'JTX101',
> > 'JTX102', 'JTX103', 'JTX104',
> > 'JTX105', 'JTX106', 'JTX107', 'JTX108', 'JTX109',
> > 'JTX231', 'JTX233', 'TX0043', 'TX0064', 'TX0086', 'TX0148', 'TX0149',
> > 'TX0151', 'TX0157', 'TX0158',
> > 'TX0159', 'TX0160', 'TX0161', 'TX0162', 'TX0163',
> > 'TX0164', 'TX0167', 'TX0168', 'TX0174', 'TX0178', 'TX0179', 'TX0180',
> > 'TX0181', 'TX0182', 'TX0183',
> > 'TX0185', 'TX0186', 'TX0187', 'TX0189', 'TX0203',
> > 'WFS113'))
> > ORDER BY sites.client_site_id, sites_status_history.status_date DESC

need me one them database thingies

my boss said i need to put all of my spreadsheets into a database.

is this hard?

do databases have an import utility or am I going to have to retype everything?

Is SQL harder than Excel or is it just about the same thing only fancier?

Do you forsee any problems with this approach?the only problem i foresee is that you're in the wrong forum

you want to be looking at Access, not SQL Server

other than that, it's a cakewalk|||i have to admit something kind sir. this is really homework for my 400 level systems srchitecture class this semester.

would you happen to have one of these ERD thingies and a complete project plan I can hand in on Monday. I was supposed to start on this in August, but me and the frat brothers have just been drinking every night and seducing frshmen girls. Been busy.|||Tell your boss that this is unnecessary. Excel is an excellent database itself, and more flexible since it does not impose any "rules" or "constraints" on the data. He'll see the sense in this, I'm sure. ;)|||ah a slow day troll

Maybe Lotus1-2-3|||You guys know that somebody is bound to take you seriously, and might even try to make sense of this drivel.

-PatP|||Well, an ERD (or Entity relationship Diagram) just shows how entities relate to each other. This is really just a family tree kind of concept. Google around for a copy of Thomas Jefferson's family tree, and use that for your assignment. As for the project plan, hardly anyone uses those in the "real world" it seems. it is unlikely that your professor has actually seen one outside of an academic setting. The one thing you need to know about making a project plan is that all project plans have one thing in common. A PowerPoint presentation. This one has been popular for some time, and should get you the grade you deserve in this class:

http://norvig.com/Gettysburg/sld001.htm|||So does that mean that ERDs are inherantly more complex in Kentucky?

If I build a project plan with only one step, failure, is that complete or is a prelimnary step required in a project plan?

Sooo many decisions! Now I see why those data guys get paid so much.

-PatP|||Easy... Print off your spreadsheets on to paper and file them away in your filing cabinet. That's just and old skool database, isn't it! You'll probably get high marks for research and originality for that one.|||You guys know that somebody is bound to take you seriously, and might even try to make sense of this drivel.

-PatP

don't be a hater, people are trying to help!

;)|||So does that mean that ERDs are inherantly more complex in Kentucky?

bwahahahaha|||Hmm...did someone in here just get a second monitor?

http://www.dilbert.com/comics/dilbert/archive/dilbert-20071016.html

Need help with User Defined Function

I am accustomed to doing most of my function work in Access, but the boss would really like it if I could shedule some cubes to do the stuff that takes forever when you run it live.

To that end, I have an Access function that I call to get a field value for a query. I would like to be able to create an User Defined Function on the SQL server and call that function as a field value in a view. I have searched the forums and have not really found anything that wants to make sense to me as to how to do this.

The access function is as follows:

Public Function BuyerDeltas(IFSDate As Date, PODate As Date) As Long

If IFSDate < (Date + 14) Then
BuyerDeltas = IFSDate - 3 - PODate
ElseIf IFSDate < (Date + 29) Then
BuyerDeltas = IFSDate - 7 - PODate
ElseIf IFSDate > (Date + 28) Then
BuyerDeltas = IFSDate - 10 - PODate
Else
MsgBox "This should not be possible!", vbOKOnly, "Fix It!!!!!!!"
End If

End Function

The view that this is called from contains the IFSDate and PODate fields and I am able to call the function from the access query, but this is completely different than what I have seen in the help files on SQL.

I would love to be able to keep plugging away at doing this myself, but the boss also is pushing me to get it done and he doesn't want me taking forever to do it.

Any direction would be greatly appreciated!

I still cannot get this proceedure set up so it will work. I have been playing with it all day and have gotten to the following point:

CREATE FUNCTION BuyerDeltas
(@.IFSDate DATE, @.PODate DATE)
RETURNS decimal (5,0)
AS
BEGIN
DECLARE @.BaseDate DATE
DECLARE @.IFS decimal (5,0)
DECLARE @.PO decimal (5,0)
DECLARE @.ThisDay decimal (5,0)
SET @.BaseDate = CONVERT(DATETIME, '2000-12-31 00:00:00', 102)
-- Used so I can use DateDiff to get numbers for days to use in equations
-- It is just an arbitrary date that will be before any dates in the system
SET @.IFS = DateDiff(day, @.BaseDate, @.IFSDate)
SET @.PO = DateDiff(day, @.BaseDate, @.PODate)
SET @.ThisDay = DateDiff(day, @.BaseDate, GetDate())
Where @.IFS < @.ThisDay + 14
RETURN @.IFS - 3 - @.PO
Where (@.IFS < @.ThisDay + 13) AND (@.IFS < @.ThisDay + 29)
RETURN @.IFS - 7 - @.PO
Where @.IFS < @.ThisDay + 28
RETURN @.IFS - 10 - @.PO
End

It gives me the following errors:

Server: Msg 443, Level 16, State 1, Procedure BuyerDeltas, Line 15
Invalid use of 'getdate' within a function.
Server: Msg 156, Level 15, State 1, Procedure BuyerDeltas, Line 18
Incorrect syntax near the keyword 'Where'.
Server: Msg 156, Level 15, State 1, Procedure BuyerDeltas, Line 20
Incorrect syntax near the keyword 'Where'.

I am really trying to understand this and get it to work right. If someone could just give me an example of a similarly structured function, I am sure I should be able to alter this one so that it works.

Calling the function from the view, actually, I guess I should just use the function in the INSERT code for the DTS package, might still drive me nuts when I try and use this, but I do really want to get this done right.

Thanks again!

|||

Hopefully this will help. We do not have a DATE datatype in SQL SERVER. We have a DATETIME datatype.

CREATE FUNCTION BuyerDeltas
(@.IFSDate DATETIME, @.PODate DATETIME)
RETURNS decimal (5,0)
AS
BEGIN
DECLARE @.BaseDate DATETIME
DECLARE @.IFS decimal (5,0)
DECLARE @.PO decimal (5,0)
DECLARE @.ThisDay decimal (5,0)
SET @.BaseDate = CONVERT(DATETIME, '2000-12-31 00:00:00', 102)

-- Used so I can use DateDiff to get numbers for days to use in equations
-- It is just an arbitrary date that will be before any dates in the system
SET @.IFS = DateDiff(day, @.BaseDate, @.IFSDate)
SET @.PO = DateDiff(day, @.BaseDate, @.PODate)
SET @.ThisDay = DateDiff(day, @.BaseDate, GetDate())

if (@.IFS < @.ThisDay + 14)
RETURN @.IFS - 3 - @.PO
else if ((@.IFS < @.ThisDay + 13) AND (@.IFS < @.ThisDay + 29))
RETURN @.IFS - 7 - @.PO
else if (@.IFS < @.ThisDay + 28)
RETURN @.IFS - 10 - @.PO

RETURN -1 --trying to indicate a failure

--Where @.IFS < @.ThisDay + 14
-- RETURN @.IFS - 3 - @.PO
--Where (@.IFS < @.ThisDay + 13) AND (@.IFS < @.ThisDay + 29)
--RETURN @.IFS - 7 - @.PO
--Where @.IFS < @.ThisDay + 28
-- RETURN @.IFS - 10 - @.PO
end
GO

|||

Thanks Asvin!

That is getting it very close, the only error I now get is:

Server: Msg 443, Level 16, State 1, Procedure BuyerDeltas, Line 16
Invalid use of 'getdate' within a function.

Not sure how else I can get today's date in this thing, but I keep trying. I am open to just getting the date as a variable, but when I tried that, I still go the error.

Thanks again!

|||

In 2000 you can't use the getdate function in a function (say that a few times fast) but you can in 2005.

The workaround is to add a parameter and pass in the date and time like:

create function functionName
(
@.parm1 datatype,
@.getdate datetime

Then add that to the call.

Another trick which I have never actually used is to create a view:

create view dateView
as
select getdate() as getdate

Then just add this to your queries that need the current date (using a cross join works nicely):

create view dateView
as
select getdate() as getdate
GO

select * from dateView
GO

Returns:

getdate
--
2006-04-07 23:58:30.217

select dateview.getdate, sysobjects.name
from sysobjects
cross join dateview

getdate name
--
2006-04-07 23:58:30.247 sysrowsetcolumns
2006-04-07 23:58:30.247 sysrowsets
2006-04-07 23:58:30.247 sysallocunits
.......

And so on.

|||

Thanks Louis,

I didn't actually use the ideas you put forth, but you did get me thinking. I realized that I could put GetDate() in the view that was used to make the table for the cube. I then do some alterations on the table based on what data is missing in some fields of the table. There are times that there are no dates in the fields I need to calculate the BuyerDeltas that this whole exercise is designed to calculate so I need to put dates in these fields before I can call the function anyway. After those updates, I can call the function and put the value I wanted calculated in the appropriate field of the table. There is probably a simpler and faster way to do it, but this works and the boss is breathing down my neck to get this finished.

Thanks again to all that helped, using a little bit from all of the responses has let me finally beat this thing.