Help with SQL if statement and adding fields together

I have a query that I need a hand on. I am trying to add togther some
fiends based on values of another.

What I would like to add a billing total by saying more or less the
following:

SELECT labor_hours, labor_cost, expidite_fee, flat_rate,
include_repair_cost, include_cal, include_flat_rate, include_parts,
cur_bill,
(labor_hours * labor_cost) AS labor_total,
(ISNULL((SELECT TOP 1 cal_cost FROM calID WHERE orderID=79559 ORDER BY
dateCAL DESC),0)) AS cal_total,
(
ISNULL((SELECT SUM((qty * cost) + premium_charge) AS gptotal FROM
repair_partsID WHERE orderID=79559),0) +
ISNULL((SELECT SUM(qty_needed * cust_cost) AS gnptotal FROM
misc_part_assocID WHERE orderID=79559),0)
) AS parts_total,
(
(labor_hours * labor_cost) + expidite_fee + flat_rate +
ISNULL((SELECT TOP 1 cal_cost FROM calID WHERE orderID=79559 ORDER BY
dateCAL DESC),0) +
ISNULL((SELECT SUM((qty * cost) + premium_charge) AS gptotal FROM
repair_partsID WHERE orderID=79559),0) +
ISNULL((SELECT SUM(qty_needed * cust_cost) AS gnptotal FROM
misc_part_assocID WHERE orderID=79559),0)
) AS actual_total,
(
expidite_fee
IF include_repair_cost = 1
+ (labor_hours * labor_cost)
IF include_flat_rate = 1
+ flat_rate
IF include_cal = 1
+ ISNULL((SELECT TOP 1 cal_cost FROM calID WHERE orderID=79559 ORDER
BY dateCAL DESC),0)
IF include_parts = 1
+ ISNULL((SELECT SUM((qty * cost) + premium_charge) AS gptotal FROM
repair_partsID WHERE orderID=79559),0) +
ISNULL((SELECT SUM(qty_needed * cust_cost) AS gnptotal FROM
misc_part_assocID WHERE orderID=79559),0)
) AS billing_total
FROM view_inventory
WHERE orderID=79559

I know the IF part is whacked, that's where I need the help. Is this
type of thing even possible? Or even efficent? Is it wise to subquery
for totals (not like I have a choice based on the application
requirements)? help.On 18 Mar 2005 07:56:07 -0800, Rob Kopp wrote:
(snip)
>(
>expidite_fee
>IF include_repair_cost = 1
>+ (labor_hours * labor_cost)
>IF include_flat_rate = 1
>+ flat_rate
>IF include_cal = 1
>+ ISNULL((SELECT TOP 1 cal_cost FROM calID WHERE orderID=79559 ORDER
>BY dateCAL DESC),0)
>IF include_parts = 1
>+ ISNULL((SELECT SUM((qty * cost) + premium_charge) AS gptotal FROM
>repair_partsID WHERE orderID=79559),0) +
>ISNULL((SELECT SUM(qty_needed * cust_cost) AS gnptotal FROM
>misc_part_assocID WHERE orderID=79559),0)
>) AS billing_total
>FROM view_inventory
>WHERE orderID=79559
>I know the IF part is whacked, that's where I need the help. Is this
>type of thing even possible? Or even efficent?

Hi Rob,

You'll need to use CASE:

(
expidite_fee +
CASE WHEN include_repair_cost = 1
THEN (labor_hours * labor_cost)
ELSE 0 END +
CASE WHEN include_flat_rate = 1
THEN flat_rate
ELSE 0 END +
CASE WHEN include_cal = 1
THEN ISNULL((subquery cal_cost), 0)
ELSE 0 END +
CASE WHEN include_parts = 1
THEN ISNULL((subquery gptotal), 0) +
ISNULL((subquery gnptotal), 0)
ELSE 0 END
) AS billing_total

> Is it wise to subquery
>for totals (not like I have a choice based on the application
>requirements)? help.

Well, you can do some things to speed up the query.

Since you use the same subquery in two places, you could use a derived
table. Like this:

SELECT a, b, c, a + b + c AS GrandTotal
FROM (SELECT complicated_expression AS a,
complicated_expression AS b,
complicated expression AS c
FROM YourTable
WHERE ...) AS x

Another possibility is to use a join between your inventory table and
derived tables where the grouping has already been done:

SELECT ...,
gptotal,
...,
complicated expression using gptotal,
...
FROM view_inventory AS vi
LEFT OUTER JOIN (SELECT orderID,
SUM((qty * cost) + premium_charge) AS gptotal
FROM repair_partsID
GROUP BY orderID) AS a
ON a.orderID = vi.orderID
LEFT OUTER JOIN (...) AS b
ON b.orderID = vi.orderID
(etc)
WHERE vi.orderID = 79559

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||You are the man, Hugo. I bow to your majesty.sql

Help with sp_executesql

I have a full sql statement which was generated dynamicly, and need to
execute that string and then take the output and generate a
spreadsheet document based on the output. I'm new to sql and the book
I have doesn't really explain much. Anyone with an example of their
work would be appreaciated.
thank you.use output option
for example:

select @.sql = 'select @.bdate=min(effective_date) from ' + @.table
SET @.ParmDefinition = N'@.bdate datetime OUTPUT'
EXEC sp_executesql @.sql, @.ParmDefinition, @.bdate OUTPUT

"Ado" <a3vr6tur@.hotmail.com> wrote in message
news:848bd3a0.0401151001.7270c50b@.posting.google.c om...
> I have a full sql statement which was generated dynamicly, and need to
> execute that string and then take the output and generate a
> spreadsheet document based on the output. I'm new to sql and the book
> I have doesn't really explain much. Anyone with an example of their
> work would be appreaciated.
> thank you.|||"Ado" <a3vr6tur@.hotmail.com> wrote in message
news:848bd3a0.0401151001.7270c50b@.posting.google.c om...
> I have a full sql statement which was generated dynamicly, and need to
> execute that string and then take the output and generate a
> spreadsheet document based on the output. I'm new to sql and the book
> I have doesn't really explain much. Anyone with an example of their
> work would be appreaciated.
> thank you.

The Books Online syntax documentation for sp_executesql has two examples,
and the subject "Using sp_executesql" has several more. You'll probably also
find this useful:

http://www.sommarskog.se/dynamic_sql.html

If you're still having problems after checking those sources, perhaps you
could post a (simple) example of what you're trying to do.

Simonsql

Help with sequel statement please. Thanks.

Sorry for dup. Please ignore the previous one. Thanks.
Hi all,
How can I return the results below. Any help would greatly appreciate. The
business rule is show below.
IF OBJECT_ID('Tempdb.dbo.#Policy_nb', 'u') IS NOT NULL
DROP TABLE #Policy_nb
GO
CREATE TABLE #Policy_nb
(
Policy_Id INT NULL,
CurrAgent_id INT NULL,
UploadTp_id INT NULL
)
GO
INSERT #Policy_nb (Policy_Id, CurrAgent_id, UploadTp_id) VALUES (382099,
4894, 3)
INSERT #Policy_nb (Policy_Id, CurrAgent_id, UploadTp_id) VALUES (374943,
614, 3)
INSERT #Policy_nb (Policy_Id, CurrAgent_id, UploadTp_id) VALUES (376279,
4710, 2)
GO
IF OBJECT_ID('Tempdb.dbo.#HuonUpload_nb', 'u') IS NOT NULL
DROP TABLE #HuonUpload_nb
GO
CREATE TABLE #HuonUpload_nb
(
Policy_id INT NULL,
UploadTp_Id INT NULL,
UploadStatus_dt DATETIME NULL
)
GO
INSERT #HuonUpload_nb VALUES (382099, 3, '10/03/2005 11:27AM')
INSERT #HuonUpload_nb VALUES (382099, 2, '10/03/2005 11:25AM')
INSERT #HuonUpload_nb VALUES (382099, 1, '10/03/2005 11:21AM')
INSERT #HuonUpload_nb VALUES (382099, 4, '09/30/2005 9:24AM')
INSERT #HuonUpload_nb VALUES (382099, 1, '09/30/2005 9:22AM')
INSERT #HuonUpload_nb VALUES (382099, 2, '09/30/2005 9:21AM')
INSERT #HuonUpload_nb VALUES (382099, 2, '09/30/2005 9:12AM')
INSERT #HuonUpload_nb VALUES (382099, 1, '09/29/2005 11:36PM')
INSERT #HuonUpload_nb VALUES (382099, 4, '09/29/2005 09:45AM')
INSERT #HuonUpload_nb VALUES (382099, 2, '09/28/2005 01:18PM')
INSERT #HuonUpload_nb VALUES (374943, 3, '10/03/2005 11:26AM')
INSERT #HuonUpload_nb VALUES (374943, 2, '10/03/2005 10:20AM')
INSERT #HuonUpload_nb VALUES (374943, 1, '10/03/2005 10:15AM')
INSERT #HuonUpload_nb VALUES (376279, 1, '09/13/2005 2:19PM')
INSERT #HuonUpload_nb VALUES (376279, 2, '09/13/2005 2:36PM')
go
SELECT *
FROM #Policy_nb
GO
Policy_Id CurrAgent_id UploadTp_id
-- -- --
382099 4894 3
374943 614 3
376279 4710 2
SELECT *
FROM #HuonUpload_nb
GO
Policy_id UploadTp_Id UploadStatus_dt
-- -- --
382099 3 2005-10-03 11:27:00.000
382099 2 2005-10-03 11:25:00.000
382099 1 2005-10-03 11:21:00.000
382099 4 2005-09-30 09:24:00.000
382099 1 2005-09-30 09:22:00.000
382099 2 2005-09-30 09:21:00.000
382099 2 2005-09-30 09:12:00.000
382099 1 2005-09-29 23:36:00.000
382099 4 2005-09-29 09:45:00.000
382099 2 2005-09-28 13:18:00.000
374943 3 2005-10-03 11:26:00.000
374943 2 2005-10-03 10:20:00.000
374943 1 2005-10-03 10:15:00.000
376279 1 2005-09-13 14:19:00.000
376279 2 2005-09-13 14:36:00.000
-- Rules: Return only these rows which has UploadTp_id = 1, 2 and 3.
--Testing... Not working...
SELECT a.CurrAgent_id,
b.Policy_id,
b.UploadTp_Id,
b.UploadStatus_dt
FROM #Policy_nb AS a
JOIN #HuonUpload_nb AS b
ON a.Policy_id = b.Policy_id
JOIN (SELECT TOP 100 PERCENT Policy_id, UploadTp_id,
MAX(UploadStatus_dt) AS 'UploadStatus_dt'
FROM #HuonUpload_nb
WHERE UploadTp_id IN (1, 2, 3)
GROUP BY Policy_id, UploadTp_id
ORDER BY Policy_id ASC, UploadStatus_dt DESC) AS c
ON c.Policy_id = b.Policy_id
AND c.UploadTp_id = b.UploadTp_id
AND c.UploadStatus_dt = b.UploadStatus_dt
ORDER BY b.Policy_id DESC, b.UploadStatus_dt DESC
GO
--Result want:
CurrAgent_id Policy_id UploadTp_Id UploadStatus_dt
-- -- -- --
4894 382099 3 2005-10-03 11:27:00.000
4894 382099 2 2005-10-03 11:25:00.000
4894 382099 1 2005-10-03 11:21:00.000
614 374943 3 2005-10-03 11:26:00.000
614 374943 2 2005-10-03 10:20:00.000
614 374943 1 2005-10-03 10:15:00.000There is no duplicate (unless you mean your post from 8/29).
Please consider using a newsreader, which doesn't have as many
synchronization issues as the web-based interfaces.
http://www.aspfaq.com/5007|||Try this,
SELECT p.CurrAgent_id, h.Policy_id, h.UploadTp_Id, max(h.UploadStatus_dt) AS
UploadStatus_dt
FROM HuonUpload_nb h inner join Policy_nb p on h.policy_id = p.policy_id
WHERE h.UploadTp_Id <=3 AND p.CurrAgent_id IN
(SELECT currAgent_id
FROM (SELECT PN.currAgent_id
FROM HuonUpload_nb NB INNER JOIN POLICY_NB PN ON NB.policy_ID =
PN.policy_ID
WHERE (nb.UploadTp_Id <=3)
GROUP BY pn.currAgent_id, nb.Policy_id, nb.UploadTp_id) AS X
GROUP BY currAgent_id
HAVING count(currAgent_id) = 3)
GROUP BY p.CurrAgent_id, h.Policy_id, h.UploadTp_Id
ORDER BY h.Policy_id DESC, h.UploadTP_Id DESC
Note: # were removed
Regards,
David
"Lam Nguyen" wrote:

> Sorry for dup. Please ignore the previous one. Thanks.
> Hi all,
> How can I return the results below. Any help would greatly appreciate. T
he
> business rule is show below.
>
> IF OBJECT_ID('Tempdb.dbo.#Policy_nb', 'u') IS NOT NULL
> DROP TABLE #Policy_nb
> GO
> CREATE TABLE #Policy_nb
> (
> Policy_Id INT NULL,
> CurrAgent_id INT NULL,
> UploadTp_id INT NULL
> )
> GO
> INSERT #Policy_nb (Policy_Id, CurrAgent_id, UploadTp_id) VALUES (382099,
> 4894, 3)
> INSERT #Policy_nb (Policy_Id, CurrAgent_id, UploadTp_id) VALUES (374943,
> 614, 3)
> INSERT #Policy_nb (Policy_Id, CurrAgent_id, UploadTp_id) VALUES (376279,
> 4710, 2)
> GO
> IF OBJECT_ID('Tempdb.dbo.#HuonUpload_nb', 'u') IS NOT NULL
> DROP TABLE #HuonUpload_nb
> GO
> CREATE TABLE #HuonUpload_nb
> (
> Policy_id INT NULL,
> UploadTp_Id INT NULL,
> UploadStatus_dt DATETIME NULL
> )
> GO
> INSERT #HuonUpload_nb VALUES (382099, 3, '10/03/2005 11:27AM')
> INSERT #HuonUpload_nb VALUES (382099, 2, '10/03/2005 11:25AM')
> INSERT #HuonUpload_nb VALUES (382099, 1, '10/03/2005 11:21AM')
> INSERT #HuonUpload_nb VALUES (382099, 4, '09/30/2005 9:24AM')
> INSERT #HuonUpload_nb VALUES (382099, 1, '09/30/2005 9:22AM')
> INSERT #HuonUpload_nb VALUES (382099, 2, '09/30/2005 9:21AM')
> INSERT #HuonUpload_nb VALUES (382099, 2, '09/30/2005 9:12AM')
> INSERT #HuonUpload_nb VALUES (382099, 1, '09/29/2005 11:36PM')
> INSERT #HuonUpload_nb VALUES (382099, 4, '09/29/2005 09:45AM')
> INSERT #HuonUpload_nb VALUES (382099, 2, '09/28/2005 01:18PM')
> INSERT #HuonUpload_nb VALUES (374943, 3, '10/03/2005 11:26AM')
> INSERT #HuonUpload_nb VALUES (374943, 2, '10/03/2005 10:20AM')
> INSERT #HuonUpload_nb VALUES (374943, 1, '10/03/2005 10:15AM')
> INSERT #HuonUpload_nb VALUES (376279, 1, '09/13/2005 2:19PM')
> INSERT #HuonUpload_nb VALUES (376279, 2, '09/13/2005 2:36PM')
> go
> SELECT *
> FROM #Policy_nb
> GO
> Policy_Id CurrAgent_id UploadTp_id
> -- -- --
> 382099 4894 3
> 374943 614 3
> 376279 4710 2
> SELECT *
> FROM #HuonUpload_nb
> GO
> Policy_id UploadTp_Id UploadStatus_dt
> -- -- --
> 382099 3 2005-10-03 11:27:00.000
> 382099 2 2005-10-03 11:25:00.000
> 382099 1 2005-10-03 11:21:00.000
> 382099 4 2005-09-30 09:24:00.000
> 382099 1 2005-09-30 09:22:00.000
> 382099 2 2005-09-30 09:21:00.000
> 382099 2 2005-09-30 09:12:00.000
> 382099 1 2005-09-29 23:36:00.000
> 382099 4 2005-09-29 09:45:00.000
> 382099 2 2005-09-28 13:18:00.000
> 374943 3 2005-10-03 11:26:00.000
> 374943 2 2005-10-03 10:20:00.000
> 374943 1 2005-10-03 10:15:00.000
> 376279 1 2005-09-13 14:19:00.000
> 376279 2 2005-09-13 14:36:00.000
>
> -- Rules: Return only these rows which has UploadTp_id = 1, 2 and 3.
> --Testing... Not working...
> SELECT a.CurrAgent_id,
> b.Policy_id,
> b.UploadTp_Id,
> b.UploadStatus_dt
> FROM #Policy_nb AS a
> JOIN #HuonUpload_nb AS b
> ON a.Policy_id = b.Policy_id
> JOIN (SELECT TOP 100 PERCENT Policy_id, UploadTp_id,
> MAX(UploadStatus_dt) AS 'UploadStatus_dt'
> FROM #HuonUpload_nb
> WHERE UploadTp_id IN (1, 2, 3)
> GROUP BY Policy_id, UploadTp_id
> ORDER BY Policy_id ASC, UploadStatus_dt DESC) AS c
> ON c.Policy_id = b.Policy_id
> AND c.UploadTp_id = b.UploadTp_id
> AND c.UploadStatus_dt = b.UploadStatus_dt
> ORDER BY b.Policy_id DESC, b.UploadStatus_dt DESC
> GO
> --Result want:
> CurrAgent_id Policy_id UploadTp_Id UploadStatus_dt
> -- -- -- --
> 4894 382099 3 2005-10-03 11:27:00.000
> 4894 382099 2 2005-10-03 11:25:00.000
> 4894 382099 1 2005-10-03 11:21:00.000
> 614 374943 3 2005-10-03 11:26:00.000
> 614 374943 2 2005-10-03 10:20:00.000
> 614 374943 1 2005-10-03 10:15:00.000
>

Help With Select Statement

My Current Query:

select rpg.rpg_sortorder,rpg.rpg_groupname,
act.act_cardprocid, sum(act.act_trxamtn) as Amount from
actlog act
right outer join
report_groups rpg on rpg.rpg_groupcode = act.act_CardProcID
WHERE
(rpg.rpg_report = 'SS') AND
(rpg.rpg_groupname <> 'not on report')
group by rpg.rpg_groupname, rpg.rpg_sortorder, act.act_cardprocid
order by rpg_sortorder

Report_groups looks like this

rpg_sortorder rpg_groupname rpg_groupcode
2 debit cards db
3 discover ds
4 visa vs
8 food stamps ef
10 gift cards gc
14 fleet cards wx
15 fleet cards mf
16 fleet cards vy
17 ach ac

Actlog looks like this:

act_cardprocid Amount
db 25.00
db 25.00
vs 100.00
vs 200.00

resultset I wish to achieve

rpg_sortorder rpg_groupname act_cardprocid Amount
2 debit cards db 50.00
3 Discover null null
4 Visa vs 300.00
8 food stamps null null
10 gift cards null null
14 Fleet Cards null null
17 ach null null

Note that in the join, I only need one record to represent group name and sortorder.
If there happens to be three records in report_groups for the same groupname, I only want
the top record. Hence, I do NOT want the following to showup in my results:

15 Fleet cards null null
16 Fleet cards null null

How can I filter out these unwanted records?

This seems to produce your desired output.

Code Snippet

SET NOCOUNT ON

DECLARE @.Report_Groups table
( Rpg_SortOrder int,
Rpg_GroupName varchar(20),
Rpg_GroupCode char(2)
)

INSERT INTO @.Report_Groups VALUES ( 2, 'debit cards', 'db' )
INSERT INTO @.Report_Groups VALUES ( 3, 'discover', 'ds' )
INSERT INTO @.Report_Groups VALUES ( 4, 'visa', 'vs' )
INSERT INTO @.Report_Groups VALUES ( 8, 'food stamps', 'ef' )
INSERT INTO @.Report_Groups VALUES ( 10, 'gift cards', 'gc' )
INSERT INTO @.Report_Groups VALUES ( 14, 'fleet cards', 'wx' )
INSERT INTO @.Report_Groups VALUES ( 15, 'fleet cards', 'mf' )
INSERT INTO @.Report_Groups VALUES ( 16, 'fleet cards', 'vy' )
INSERT INTO @.Report_Groups VALUES ( 18, 'ach', 'ac' )

DECLARE @.Actlog table
( Act_CardProcID char(2),
Act_TrxAmtn decimal(10,2)
)

INSERT INTO @.ActLog VALUES ( 'db', 25.00 )
INSERT INTO @.ActLog VALUES ( 'db', 25.00 )
INSERT INTO @.ActLog VALUES ( 'vs', 100.00 )
INSERT INTO @.ActLog VALUES ( 'vs', 200.00 )

SELECT
SortOrder = min( r.Rpg_SortOrder ),
GroupName = r.Rpg_GroupName,
CardProdID = min( a.Act_CardProcID ),
Amount = sum( a.Act_TrxAmtn )
FROM @.Report_Groups r
LEFT JOIN @.ActLog a
ON r.Rpg_GroupCode = a.Act_CardProcID
GROUP BY r.Rpg_GroupName
ORDER BY SortOrder

SortOrder GroupName CardProdID Amount
-- -- -
2 debit cards db 50.00
3 discover NULL NULL
4 visa vs 300.00
8 food stamps NULL NULL
10 gift cards NULL NULL
14 fleet cards NULL NULL
18 ach NULL NULL

Help with select statement

Hi guys,

I have the following data

CaseNumber ConnectionToCase PersonID

-

00001 A 500

00001 J 235

00001 6 014

00001 K 016

00002 A 500

00002 B 477

00002 6 251

00002 L 355

00003 F 577

00003 J 235

00003 C 744

00003 K 563

00005 A 501

00005 K 455

00009 R 500

00009 6 017

00009 K 011

I would like to select all columns based on casenumber that contains only ConnectionToCase = '6'.

Therefore i need a query that will return this result:

CaseNumber ConnectionToCase PersonID

00001 A 500

00001 J 235

00001 6 014

00001 K 016

00002 A 500

00002 B 477

00002 6 251

00009 R 500

00009 6 017

00009 K 011

How can i do that? thanks guys

Jul.

Something like this should work:

Code Snippet

SELECT
CaseNumber,
ConnectionToCase,
PersonID
FROM MyTable
WHERE CaseNumber IN ( SELECT CaseNumber
FROM MyTable
WHERE ConnectionToCase = '6'
)

|||

See if this works for you:

Code Snippet

select a.CaseNumber, a.ConnectionToCase, a.PersonID

from casedata a

inner join casedata b

on a.CaseNumber = b.CaseNumber

and b.ConnectionToCase = '6'

|||

Here you go....

Code Snippet

Create Table #casedata (

[CaseNumber] Varchar(100) ,

[ConnectionToCase] Varchar(100) ,

[PersonID] Varchar(100)

);

Insert Into #casedata Values('00001','A','500');

Insert Into #casedata Values('00001','J','235');

Insert Into #casedata Values('00001','6','014');

Insert Into #casedata Values('00001','K','016');

Insert Into #casedata Values('00002','A','500');

Insert Into #casedata Values('00002','B','477');

Insert Into #casedata Values('00002','6','251');

Insert Into #casedata Values('00002','L','355');

Insert Into #casedata Values('00003','F','577');

Insert Into #casedata Values('00003','J','235');

Insert Into #casedata Values('00003','C','744');

Insert Into #casedata Values('00003','K','563');

Insert Into #casedata Values('00005','A','501');

Insert Into #casedata Values('00005','K','455');

Insert Into #casedata Values('00009','R','500');

Insert Into #casedata Values('00009','6','017');

Insert Into #casedata Values('00009','K','011');

Select

*

From

#casedata

where

[CaseNumber] in (

Select

[CaseNumber]

From

#casedata

Where [ConnectionToCase]='6'

)

|||

SELECT * FROM myTable

WHERE CaseNumber IN (SELECT CaseNumber FROM myTable WHERE ConnectionToCase = 6)

Adamus

|||

Msg 512, Level 16, State 1, Line 1

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I've got this error when ran this query:

SELECT
CaseNumber,
ConnectionToCase,
PersonID
FROM MyTable
WHERE CaseNumber = ( SELECT CaseNumber
FROM MyTable
WHERE ConnectionToCase = '6'
)

|||Thanks guys!!

Help with Select Statement

Edited by moderator XIII, please don't use [ code ] tags around your code. Instead change your Profile | Site settings to use the Rich editor (with code support) to insert colorized code and line numbers:

I have a select statement that follows:

SELECT Events.legendID AS Events_legendID,
Units.unitID AS Units_unitID,
Units.msbbID, Units.belongsTo,
Units.name AS unitName,
Legend.legendID AS Legend_legendID,
Legend.Color,
Legend.Name legendName,
Legend.Active,
Events.unitID AS Events_UnitID,
Events.userID AS eventUserID,
Events.startDate,
Events.endDate,
Events.eventID,
Events.Title
FROM Events INNER JOIN Units ON Events.unitID = Units.unitID
AND Events.unitID = Units.unitID INNER JOIN Legend ON Events.legendID = Legend.legendID
WHERE startDate BETWEEN convert(datetime, '4/01/2006') AND convert(datetime, '6/30/2006')
ORDER BY unitName ASC;

This code works great with the exception of the events that start and end outside of the quarter dates I am entering. For example if there is an event that start in January and ends in August then I need to show that event in the 1st quarter, 2nd quarter and 3rd quarter.

any help on how to get those events that start and end outside the 'active' quarter BUT pertain to the active quarter?

thanks in advance!

Hello, if i get you right, maybe this would work:

WHERE
startDate <= convert(datetime, '6/30/2006') AND
endDate >= convert(datetime, '4/01/2006')

Anyway, i'd suggest you craft your data structure a bit more, to work on "quarters" rather than generic (unconstrained) dates...

HTH -LV

|||what do you mean by "... work on quarters rather then generic..."?|||

Mmm, what's unclear? So to say, i'd rather not write a treaty...

Also, i'm not such an expert with SqlServer either, so i guess we'll need someone else for a "ground-breaking" implementation sample...

-LV

Help with select statement

if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FK_tblScan_tblAsset]') and OBJECTPROPERTY(id,
N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblScan] DROP CONSTRAINT FK_tblScan_tblAsset
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblAsset]') and OBJECTPROPERTY(id, N'IsUserTable') =
1)
drop table [dbo].[tblAsset]
GO
CREATE TABLE [dbo].[tblAsset] (
PK[AssetID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT
NULL ,
[AssetName] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[AssetTypeID] [int] NULL ,
[MAC] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DatelastScanned] [smalldatetime] NULL ,
[NextScanDate] [smalldatetime] NULL ,
[DateCreated] [smalldatetime] NULL ,
[LastModified] [smalldatetime] NULL ,
[Deleted] [bit] NULL
) ON [PRIMARY]
GO
----
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblEmployee]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblEmployee]
GO
CREATE TABLE [dbo].[tblEmployee] (
PK[EmpID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,
[EmployeeID] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[DateCreated] [datetime] NULL ,
[LastModified] [datetime] NULL ,
[Deleted] [bit] NULL
) ON [PRIMARY]
GO
---
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[tblEmployeeAsset]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[tblEmployeeAsset]
GO
CREATE TABLE [dbo].[tblEmployeeAsset] (
PK[EmpID] [int] NOT NULL ,
PK[AssetID] [int] NOT NULL ,
[DateCreated] [datetime] NULL ,
[LastModified] [datetime] NULL ,
[Deleted] [bit] NULL
) ON [PRIMARY]
GO
---
tblAsset
AssetID AssetName
1 RyanPC
--
tblEMployee
EmpID EmployeeID
1 Ryan
--
tblemployeeasset
EmpID AssetID
1 1
I want a select statement that gives me this result...
Employee Asset
Ryan RyanPC"rhaazy" <rhaazy@.gmail.com> wrote in message
news:1151698511.458117.256010@.d30g2000cwa.googlegroups.com...
> I want a select statement that gives me this result...
> Employee Asset
> Ryan RyanPC
>
SELECT E.EmployeeID, A.AssetName
FROM tblAsset AS A
JOIN tblEmployeeAsset AS T
ON A.AssetID = T.AssetID
JOIN tblEmployee AS E
ON T.EmpID = E.EmpID ;
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
--|||This works exactly the way I want in the querly analyzer thank you.
However, I am calling this select statement from within some C# code
via a dataadapter, when I run the query I get an error, any idea why?
SqlDataAdapter da = new SqlDataAdapter("select e.EmployeeID,e.EmpID,
a.AssetName, a.AssetId from tblAsset a join tblEmployeeAsset t on
a.assetID = t.AssetID join tblEmployee e on t.EmpID = E.EmpID", str);
DataSet ds = new DataSet();
da.Fill(ds);
dataGrid1.DataSource = ds;
The connection exists, and every other thing works, but why not this?
If this is beyond your scope i will promptly post in a C# forum...
David Portas wrote:
> "rhaazy" <rhaazy@.gmail.com> wrote in message
> news:1151698511.458117.256010@.d30g2000cwa.googlegroups.com...
>
> SELECT E.EmployeeID, A.AssetName
> FROM tblAsset AS A
> JOIN tblEmployeeAsset AS T
> ON A.AssetID = T.AssetID
> JOIN tblEmployee AS E
> ON T.EmpID = E.EmpID ;
> --
> 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
> --

Help with SELECT statement

Hi,
I have two tables with a UserID column and need to construct a query that
lists all UserIDs from Table A that is not present in Table B.
Any help with this select statement would be appreciated
NiclasSelect A.* from TableA as A where Not Exists (select * from TableB as B
where B.UserId = A.UserId)
You can also go with a Left Outer Join but it's a little more complicated to
understand:
Select A.* from TableA as A Left Outer Join TableB as B on A.UserId =
B.UserId
Where B.UserId is Null
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Niclas" <lindblom_niclas@.hotmail.com> wrote in message
news:eipps755FHA.4076@.tk2msftngp13.phx.gbl...
> Hi,
> I have two tables with a UserID column and need to construct a query that
> lists all UserIDs from Table A that is not present in Table B.
> Any help with this select statement would be appreciated
> Niclas
>|||A third possibility would be to use the IN clause:
Select A.* from TableA as A where A.UserId Not IN (select UserId from TableB
Where UserId is not Null)
The condition Where B.UserId is Not Null is a necessity if there is a
possibility that B.UserId can be Null; otherwise the result won't be good if
the IN clause encounter a Null value.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF
"Niclas" <lindblom_niclas@.hotmail.com> wrote in message
news:eipps755FHA.4076@.tk2msftngp13.phx.gbl...
> Hi,
> I have two tables with a UserID column and need to construct a query that
> lists all UserIDs from Table A that is not present in Table B.
> Any help with this select statement would be appreciated
> Niclas
>|||Many thanks !
Niclas
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:emU3YG65FHA.2888@.tk2msftngp13.phx.gbl...
>A third possibility would be to use the IN clause:
> Select A.* from TableA as A where A.UserId Not IN (select UserId from
> TableB Where UserId is not Null)
> The condition Where B.UserId is Not Null is a necessity if there is a
> possibility that B.UserId can be Null; otherwise the result won't be good
> if the IN clause encounter a Null value.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: http://cerbermail.com/?QugbLEWINF
>
> "Niclas" <lindblom_niclas@.hotmail.com> wrote in message
> news:eipps755FHA.4076@.tk2msftngp13.phx.gbl...
>

help with Select statement

Hi!

This is my table:

CREATE TABLE [Query_Result] (
sifrob VARCHAR(13),
katbroj VARCHAR(15),
kol FLOAT
)

This is some values:

INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49501879', 'G-46052', 1)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49501879', 'G-46052', 3)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49501910', 'G-46935', 1)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49508122', 'G-46944', 1)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49508698', 'G-50314', 1)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49502695', 'G-51201', 1)

As you can see some vaules are duplicated and only the 'kol' is
different. I would like to write a SELECT statements that will get all the
values from table, but on the duplicated ones that I get it once and the
'kol' column is sum of the two 'kol' values ( so if we have 499501879 the
'kol must be 4.

Please help.

Best regards,
Zvonkoselect sifrob,katbroj,sum(kol) as kol
from Query_Result
group by sifrob,katbroj

Denis the SQL Menace
http://sqlservercode.blogspot.com/
Zvonko wrote:

Quote:

Originally Posted by

Hi!
>
This is my table:
>
CREATE TABLE [Query_Result] (
sifrob VARCHAR(13),
katbroj VARCHAR(15),
kol FLOAT
)
>
>
This is some values:
>
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49501879', 'G-46052', 1)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49501879', 'G-46052', 3)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49501910', 'G-46935', 1)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49508122', 'G-46944', 1)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49508698', 'G-50314', 1)
INSERT INTO [Query_Result] ([sifrob], [katbroj], [kol])
VALUES
('49502695', 'G-51201', 1)
>
As you can see some vaules are duplicated and only the 'kol' is
different. I would like to write a SELECT statements that will get all the
values from table, but on the duplicated ones that I get it once and the
'kol' column is sum of the two 'kol' values ( so if we have 499501879 the
'kol must be 4.
>
Please help.
>
Best regards,
Zvonko

Help with Select statement

I need help creating a select statement that will show users who have
attempted to fill out a form more than once and have not completed it
at least once.
Here is the basic table layout.
ID int PK
PersonID int
Completed bit
Date DateTime
The data looks like this
ID Person ID Completed Date
1 101 True 5/1/06
2 102 True 5/2/06
3 103 False 5/3/06
4 104 True 5/3/06
5 103 True 5/3/06
6 105 False 5/3/06
7 105 True 5/4/06
8 105 False 5/4/06
9 106 True 5/4/06
10 104 True 5/4/06
I need to select all users who have attempted to fill out the form
multiple times and have failed to complete the form(a False in the
Completed column) at least one of those times. So for the above data
I would want the results of the select to be
ID Person ID Completed Date
3 103 False 5/3/06
5 103 True 5/3/06
6 105 False 5/3/06
7 105 True 5/4/06
8 105 False 5/4/06
Can anyone help me with this.
Thanksselect ID,
PersonID,
Completed,
Date
from mytable
where PersonID in (
select PersonID
from mytable
group by PersonID
having count(*)>1
and min(cast(Completed as int))=0)|||It worked great. Thank You.

Help with Select statement

I have 2 columns with data in different sequences in one table referencing a single column in different table.

I'm trying to learn SQL using SQLserver 2000.
I need some help Please!!

I'm having trouble creating a view that will give me the information that i need correctly.
I listed all the tables and the view that I tried but it's not working I dont think i have the view right.

Here is some info to help you understand what I'm trying to get:

Examples of the data that I'm having trouble with
only conscerns two of the tables

CREATE TABLE TDrivers
(
intDriverID INTEGER NOT NULL, <---
strFirstName VARCHAR(25) NOT NULL,
strMiddleName VARCHAR(25) NOT NULL,
strLastName VARCHAR(25) NOT NULL,
strAddress VARCHAR(25) NOT NULL,
strCity VARCHAR(25) NOT NULL,
strState VARCHAR(25) NOT NULL,
strZipCode VARCHAR(10) NOT NULL,
strPhoneNumber VARCHAR(14) NOT NULL,
CONSTRAINT TDriveres_PK PRIMARY KEY (intDriverID)
)
CREATE TABLE TScheduledRoutes
(
intRouteID INTEGER NOT NULL,
intScheduleTimeID INTEGER NOT NULL,
intBusID INTEGER NOT NULL,
intDriverID INTEGER NOT NULL, <Both ref above table
intAlternateDriverID INTEGER NOT NULL, <Both ref above table
CONSTRAINT TScheduleRoutes_PK PRIMARY KEY (intRouteID,intScheduleTimeID)
)

TDrivers Table has
intDriverID 1, 2, 3, 4, 5 and each id is associated with a name

1 = john
2 = mike
3 = sam
4 = jim
5 = tony

TScheduledRoutes Table
has column
intDriverID
and data is 1, 2, 3, 4, 5 that references TDrivers.intDriverID

and has column
intAlternateDriverID
and data is 5, 3, 1, 2, 4 that references TDrivers.intDriverID also

NOTICE the two have different sequence.

I need to get a select statement that would give me a list of
TScheduledRoutes.intDriverID full name
and their assciated alternate driver
TScheduledRoutes.intAlternateDriverID

output would give this as example

(intdriverID 1) john would have alt driverId 5 tony

I can't create a select statement that will give me both names at the same time.

Below is a list of the actual code and the view I cant get to do what I want it to and still keep the database in 3rd normal form.

Any suggestions would be greatly appreciated.

CREATE TABLE TRoutes
(
intRouteID INTEGER NOT NULL,
strRoute VARCHAR(30) NOT NULL,
strRouteDescription VARCHAR(50) NOT NULL,
CONSTRAINT TRoutes_PK PRIMARY KEY (intRouteID)
)

CREATE TABLE TBuses
(
intBusID INTEGER NOT NULL,
strBus VARCHAR(25) NOT NULL,
intCapacity INTEGER NOT NULL,
CONSTRAINT TBuses_PK PRIMARY KEY (intBusID)
)

CREATE TABLE TDrivers
(
intDriverID INTEGER NOT NULL,
strFirstName VARCHAR(25) NOT NULL,
strMiddleName VARCHAR(25) NOT NULL,
strLastName VARCHAR(25) NOT NULL,
strAddress VARCHAR(25) NOT NULL,
strCity VARCHAR(25) NOT NULL,
strState VARCHAR(25) NOT NULL,
strZipCode VARCHAR(10) NOT NULL,
strPhoneNumber VARCHAR(14) NOT NULL,
CONSTRAINT TDriveres_PK PRIMARY KEY (intDriverID)
)

CREATE TABLE TScheduleTimes
(
intScheduleTimeID INTEGER NOT NULL,
strScheduleTime DATETIME NOT NULL,
CONSTRAINT TScheduleTimes_PK PRIMARY KEY (intScheduleTimeID)
)

every column below is a foreign key to other tables

CREATE TABLE TScheduledRoutes
(
intRouteID INTEGER NOT NULL,
intScheduleTimeID INTEGER NOT NULL,
intBusID INTEGER NOT NULL,
intDriverID INTEGER NOT NULL,
intAlternateDriverID INTEGER NOT NULL,
CONSTRAINT TScheduleRoutes_PK PRIMARY KEY (intRouteID,intScheduleTimeID)
)

CREATE NONCLUSTERED INDEX TRoutes_NI ON TRoutes(strRoute)
CREATE NONCLUSTERED INDEX TBuses_NI ON TBuses(strBus)
CREATE NONCLUSTERED INDEX TDrivers_NI ON TDrivers (strLastName,strFirstName)

ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TBuses_TScheduledRoutes_FK
FOREIGN KEY (intBusID)REFERENCES TBuses(intBusID)

ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TRoutes_TScheduledRoutes_FK
FOREIGN KEY (intRouteID)REFERENCES TRoutes(intRouteID)

ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TDrivers_TScheduledRoutes_FK
FOREIGN KEY (intDriverID)REFERENCES TDrivers(intDriverID)

ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TADrivers_TScheduledRoutes_FK
FOREIGN KEY (intAlternateDriverID)REFERENCES TAltDrivers(intAltDriverID)

ALTER TABLE TScheduledRoutes
ADD CONSTRAINT TScheduleTimes_TScheduledRoutes_FK
FOREIGN KEY (intScheduleTimeID)REFERENCES TScheduleTimes(intScheduleTimeID)

Here is what I tried but its not working. Is there better way to get the information I need and
keep the database in 3rd Normal form

CREATE VIEW V_SchedualedRoutes AS

SELECT TRoutes.strRoute,
TBuses.strBus,
(TDrivers.strLastName + ', '+ TDrivers.strFirstName)
AS strDriverFullName,
(SELECT TDrivers.strLastName + ', '
+ TDrivers.strFirstName)
FROM TDrivers
INNER JOIN TScheduledRoutes
ON TDrivers.intDriverID =
TScheduledRoutes.intDriverID
WHERE TScheduledRoutes.intAlternateDriverID=
TDrivers.intDriverI)
AS strAltDriFullName, TScheduleTimes.strScheduleTime
FROM TBuses
INNER JOIN TScheduledRoutes
ON TBuses.intBusID = TScheduledRoutes.intBusID
INNER JOIN TScheduleTimes
ON TScheduledRoutes.intScheduleTimeID =
TScheduleTimes.intScheduleTimeID
INNER JOIN TDrivers
ON TScheduledRoutes.intDriverID = TDrivers.intDriverID
AND TScheduledRoutes.intAlternateDriverID =
TDrivers.intDriverID
INNER JOIN TRoutes
ON TScheduledRoutes.intRouteID = TRoutes.intRouteIDI am having trouble following your query, but in essence it appears that the trouble you are having is knowing how to access the same table twice in a query - once for the main driver and once for the alternate driver. The solution is to use table aliases:

SELECT d1.strLastName MainDriver,
d1.strLastName AlternateDriver
FROM TScheduledRoutes sr
INNER JOIN TDrivers d1 ON sr.intDriverID = d1.intDriverID
INNER JOIN TDrivers d2 ON sr.intAlternateDriverID = d2.intDriverID

In your query, this bit looks to me like a syntax error:

(SELECT TDrivers.strLastName + ', ' + TDrivers.strFirstName) FROM TDrivers

... unless SQL Server has a very different SQL syntax that the one I know.|||Sorry about the confusion but thats it. Thank yousql

Help with Select statement

Help me with the following query.
I have employee table as follows
EmpID (Number)
FullName (Text)
ReportTo(Number)
ReportTo field contain number from EmpID
Sample Data from the table
EmpIDFullNameReports To
1Nancy Davolio2
2Andrew Fuller
3Janet Leverling2
4Margaret Peacock2
5Steven Buchanan2
6Michael Suyama5
7Robert King5
8Laura Callahan2
9Anne Dodsworth5
10Andrew Leverling3
11Michael Miller5
12Robert Davolio3
13Nancy Suyama6
14Margaret King7
I would like to create a single query (T-SQL), when I pick an
employee ID, I would like to have the entire list of employees come
under that employee including that employee and all below him/her.
Example. If I pick ID = 5, should get the following list.
EmpIDFullNameReports To
5Steven Buchanan2
6Michael Suyama5
7Robert King5
9Anne Dodsworth5
11Michael Miller5
13Nancy Suyama6
14Margaret King7
The list above shows all staff who report to ID 5 directly, But I want very
who are reporting to staff reporting to staff report to ID 5 and below in the
hieratical organization.
How do I do that query?
Thanks
Harry
Harry J Nathan
see
http://www.mindsdoor.net/SQLTsql/Ret...Hierarchy.html
"Harry J Nathan" wrote:

> Help me with the following query.
> I have employee table as follows
> EmpID (Number)
> FullName (Text)
> ReportTo(Number)
> ReportTo field contain number from EmpID
> Sample Data from the table
> EmpIDFullNameReports To
> 1Nancy Davolio2
> 2Andrew Fuller
> 3Janet Leverling2
> 4Margaret Peacock2
> 5Steven Buchanan2
> 6Michael Suyama5
> 7Robert King5
> 8Laura Callahan2
> 9Anne Dodsworth5
> 10Andrew Leverling3
> 11Michael Miller5
> 12Robert Davolio3
> 13Nancy Suyama6
> 14Margaret King7
>
> I would like to create a single query (T-SQL), when I pick an
> employee ID, I would like to have the entire list of employees come
> under that employee including that employee and all below him/her.
> Example. If I pick ID = 5, should get the following list.
> EmpIDFullNameReports To
> 5Steven Buchanan2
> 6Michael Suyama5
> 7Robert King5
> 9Anne Dodsworth5
> 11Michael Miller5
> 13Nancy Suyama6
> 14Margaret King7
> The list above shows all staff who report to ID 5 directly, But I want very
> who are reporting to staff reporting to staff report to ID 5 and below in the
> hieratical organization.
> How do I do that query?
> Thanks
> Harry
> --
> Harry J Nathan

HELP with Running value totals

HI,
I have a table created. I need to have static fields.
The table has one group, where I use an IIF statement to point the values
into one of the three static fields. The reason I am using a table is that I
have to show months/loan programs with zero as well. The fixed static fields
are The loan types. I need to add a sum to the group footer so for each
month I can show the grand total of the three loan programs. Please help me
with this...I have been working on this for two days now and just can not
figure it out. I tried the following: =Sum(ReportItems!Textbox21.Value +
ReportItems!Guar_Dollar_Amt.Value + ReportItems!Textbox47.Value) but receive
an error
that states "The value expression for the textbox â'textbox31â' refers to the
report item â'Textbox21â'. Report item expressions can only refer to other
report items within the same grouping scope or a containing grouping scope."
Okay so here it is:
FY2003 FY2004 FY2005
Oct Loan1 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan1" and
Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value), CDbl(0)),
Sum, Nothing)
Loan2 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan2"
and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
CDbl(0)), Sum, Nothing)
Loan3 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan3" and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
CDbl(0)), Sum, Nothing)All of the texboxes have to be in the same scope... (the same level in the
table etc...), and you probably need to supply the scope name ie the group
name etc.. You may even have to split up the sums ie
=Sum(Reportitems!textbox1.Value,"mygroup") +
sum(ReportItems!Textbox2.Value,"mygroup") ...etc
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Susan" wrote:
> HI,
> I have a table created. I need to have static fields.
> The table has one group, where I use an IIF statement to point the values
> into one of the three static fields. The reason I am using a table is that I
> have to show months/loan programs with zero as well. The fixed static fields
> are The loan types. I need to add a sum to the group footer so for each
> month I can show the grand total of the three loan programs. Please help me
> with this...I have been working on this for two days now and just can not
> figure it out. I tried the following: =Sum(ReportItems!Textbox21.Value +
> ReportItems!Guar_Dollar_Amt.Value + ReportItems!Textbox47.Value) but receive
> an error
> that states "The value expression for the textbox â'textbox31â' refers to the
> report item â'Textbox21â'. Report item expressions can only refer to other
> report items within the same grouping scope or a containing grouping scope."
> Okay so here it is:
> FY2003 FY2004 FY2005
> Oct Loan1 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan1" and
> Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value), CDbl(0)),
> Sum, Nothing)
> Loan2 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan2"
> and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)
> Loan3 =RunningValue( iif(Fields!Loan_type_Code.value => "Loan3" and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)|||This is exactly the problem that I'm having. I notice that there has been
no reply to this request in a month. Is that because there's no way to make
it work?
"Susan" <Susan@.discussions.microsoft.com> wrote in message
news:817F9F2D-3BE0-489A-8575-325DFE42CC20@.microsoft.com...
> HI,
> I have a table created. I need to have static fields.
> The table has one group, where I use an IIF statement to point the values
> into one of the three static fields. The reason I am using a table is
> that I
> have to show months/loan programs with zero as well. The fixed static
> fields
> are The loan types. I need to add a sum to the group footer so for each
> month I can show the grand total of the three loan programs. Please help
> me
> with this...I have been working on this for two days now and just can not
> figure it out. I tried the following: =Sum(ReportItems!Textbox21.Value +
> ReportItems!Guar_Dollar_Amt.Value + ReportItems!Textbox47.Value) but
> receive
> an error
> that states "The value expression for the textbox 'textbox31' refers to
> the
> report item 'Textbox21'. Report item expressions can only refer to other
> report items within the same grouping scope or a containing grouping
> scope."
> Okay so here it is:
> FY2003 FY2004 FY2005
> Oct Loan1 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan1"
> and
> Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)),
> Sum, Nothing)
> Loan2 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan2"
> and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)
> Loan3 =RunningValue( iif(Fields!Loan_type_Code.value => "Loan3" and Fields!FCLYR.Value = 2002,
> CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)

Help with query to fill in missing records

The sql statement:
select
TransactionYear TYear,
InstallationYear IYear,
sum(SumAmount) Amount
from
AgedCostDataRecords
where
TransactionYear = '1970'
group by
TransactionYear,
installationYear
Returns the following 4 records
TYear IYear Amount
1970 1964 -20305.6000
1970 1965 -5338.0000
1970 1969 -722.8000
1970 1970 218625.8000
The source table has no records for years 1966, 1967, and 1968.
I am looking for a query that will return the above records AND that will
generate records for missing years, so I am lookig for a set of return
records as shown below:
TYear IYear Amount
1970 1964 -20305.6000
1970 1965 -5338.0000
1970 1966 0.0
1970 1967 0.0
1970 1968 0.0
1970 1969 -722.8000
1970 1970 218625.8000
Can anyone suggest a query for this?
I am using SQL Server 2000On Thu, 9 Mar 2006 16:33:08 -0500, Gary Rynearson wrote:
(snip)
>The source table has no records for years 1966, 1967, and 1968.
>
>I am looking for a query that will return the above records AND that will
>generate records for missing years, so I am lookig for a set of return
>records as shown below:
(snip)
Hi Gary,
Quite easy if you have a table of numbers (see
http://www.aspfaq.com/show.asp?id=2516):
SELECT '1970' AS TYear,
n.Number AS IYear,
SUM(a.SumAmount) AS Amount
FROM Numbers AS n
LEFT OUTER JOIN AgedCostDataRecords AS a
ON a.InstallationYear = n.Number
AND a.TransactionYear = '1970'
GROUP BY n.Number
(Untested - see www.aspfaq.com.5006 if you prefer a tested reply)
--
Hugo Kornelis, SQL Server MVP

Help with query that only returns filds with certain characters

Hi,
I need to write a sql statement that only returns records if
a certain field DOESN'T contain a letter between a and d, a number, a #,
and a asterisk. If a different character is found there, it should
return that record.
For example. If we have these records
1. 43242#
2. %3499
3. $$#
4. ak
5. abd43#
6. 4242#44z
7. abc_
8. 342#ab*
9. *(
My query should return 2, 3, 4, 6, 7 and 9
I tried the following:
select * from Table1
where MyField LIKE '%[^0-9]%'
and MyField like '%[^a-d]%'
and it works fine for the numbers and letters, but I don't how to
include the # and the *
Thanks a lot.You can negate the LIKE clause by putting NOT in front of it.
So
SELECT * FROM Table1
WHERE MyField LIKE '%[^0-9]%'
AND MyField LIKE '%[^a-d]%'
AND MyField NOT LIKE '%#%'
AND MyField NOT LIKE '%*%'
One note though, the criteria you mentioned and the examples you gave do not
seem to match up.

But you said you should return rows (and your sample code that you said
works as intended) that do NOT contain a number. Some of those rows clearly
contain a number. Just not sure where you were going with that.
HTH,
John Scragg
"Star" wrote:
> Hi,
> I need to write a sql statement that only returns records if
> a certain field DOESN'T contain a letter between a and d, a number, a #,
> and a asterisk. If a different character is found there, it should
> return that record.
> For example. If we have these records
> 1. 43242#
> 2. %3499
> 3. $$#
> 4. ak
> 5. abd43#
> 6. 4242#44z
> 7. abc_
> 8. 342#ab*
> 9. *(
>
> My query should return 2, 3, 4, 6, 7 and 9
> I tried the following:
> select * from Table1
> where MyField LIKE '%[^0-9]%'
> and MyField like '%[^a-d]%'
> and it works fine for the numbers and letters, but I don't how to
> include the # and the *
> Thanks a lot.
>|||John,
Yes, I think my explanation was a little bit confusing.
I will try to rephrase it.
The query should return rows if the field
does not contain one of these characters:
- Numbers
- Letters (a-d)
- #
- *
For example, the query should return %3499 because
there is a % symbol there and that symbol is not on that list.
I had already tried what you suggested, but doesn't work for me.
If I run it, I wouldn't get 4242#44z back, and I should because it
contains a 'z'
John Scragg wrote:
> You can negate the LIKE clause by putting NOT in front of it.
> So
> SELECT * FROM Table1
> WHERE MyField LIKE '%[^0-9]%'
> AND MyField LIKE '%[^a-d]%'
> AND MyField NOT LIKE '%#%'
> AND MyField NOT LIKE '%*%'
> One note though, the criteria you mentioned and the examples you gave do n
ot
> seem to match up.
>
>
> But you said you should return rows (and your sample code that you said
> works as intended) that do NOT contain a number. Some of those rows clear
ly
> contain a number. Just not sure where you were going with that.
> HTH,
> John Scragg
>
> "Star" wrote:
>|||
You may also need to consider the escape character
e.g.
select * from (select 'a%b' col1 union select 'cde') x
where col1 LIKE '%\%%' ESCAPE ''
returns only 'a%b'|||Thanks, Steven. I will keep in mind.
However, I still haven't found a solution for this problem...
If I do this
select myfield from mytable
where myfield LIKE '%[^0-9]%'
and myfield like '%[^a-d]%'
and myfield not LIKE '%*%'
and myfield not LIKE '%#%'
and myfield LIKE '%\%%' ESCAPE ''
I only get %3499 back.
I really don't know what else to try...|||> I really don't know what else to try...
I've finally worked out (I think) what it is that you need
All records that contain one (or more) characters that are not in
(1234567890abcd#*)
Would that be a fair assumption ?|||
> I really don't know what else to try...
Is this SQL 2000 or SQL2005 ?
You could use a regex match
OR:
select col1 , patindex('%[^abcd1234567890#*]%',col1)
from
(
select
'43242#' col1
UNION SELECT
'%3499' UNION SELECT
'$$#' UNION SELECT
'ak' UNION SELECT
'abd43#' UNION SELECT
'4242#44z' UNION SELECT
'abc_' UNION SELECT
'342#ab*' UNION SELECT
'*('
) x
where patindex('%[^abcd1234567890#*]%',col1) <>0|||On Wed, 02 Nov 2005 14:05:46 -0500, Star wrote:

>John,
>Yes, I think my explanation was a little bit confusing.
>I will try to rephrase it.
>The query should return rows if the field
>does not contain one of these characters:
>- Numbers
>- Letters (a-d)
>- #
>- *
>For example, the query should return %3499 because
>there is a % symbol there and that symbol is not on that list.
Hi Star,
You write "does not contain one of these characters", but your example
suggests that you mean "contains at least one character not in this
list". For '%3499' does contain a number (even four!), yet you want it
returned.
SELECT MyField, other columns
FROM MyTable1
WHERE MyField LIKE '%[^0-9a-d#*]%'
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Yes... I apologize again. I had a really bad day. I should have thought
twice my question.
Sorry about that and thanks for your help.|||Awesome!
That worked. To be honest, I didn't know about that patindex function. I
won't forget next time.
I really really appreciate your help and time.

Help with parameter

Hi,
I am querying a sybase database for my table & need to add a start date &
end date to the query.
my where statement looks like this
WHERE (ACDCallDetail.CallStartDt>={ts '2005-08-09 08:00:00'} AND
ACDCallDetail.CallStartDt<{ts '2005-08-09 20:00:01'})
can i please have some assistance around inserting the parameters start_date
& end_date
i have tried
WHERE (ACDCallDetail.CallStartDt>=:start_date AND
ACDCallDetail.CallStartDt<:end_date) and also WHERE
(ACDCallDetail.CallStartDt>=@.start_date AND
ACDCallDetail.CallStartDt<@.end_date) to no avaial.
i also need to have the query that extracts the month only (for a month to
date) out of say the start_date & the end_date (for month to date figures)
Thankyou in adavance for any help
toddWhat happens when you use
WHERE ACDCallDetail.CallStartDt >= @.start_date
AND ACDCallDetail.CallStartDt < @.end_date
?
Derrick
"Tango" <Tango@.discussions.microsoft.com> wrote in message
news:3401F98B-707F-4FA2-BEB7-33699994222D@.microsoft.com...
> Hi,
> I am querying a sybase database for my table & need to add a start date &
> end date to the query.
> my where statement looks like this
> WHERE (ACDCallDetail.CallStartDt>={ts '2005-08-09 08:00:00'} AND
> ACDCallDetail.CallStartDt<{ts '2005-08-09 20:00:01'})
> can i please have some assistance around inserting the parameters
> start_date
> & end_date
> i have tried
> WHERE (ACDCallDetail.CallStartDt>=:start_date AND
> ACDCallDetail.CallStartDt<:end_date) and also WHERE
> (ACDCallDetail.CallStartDt>=@.start_date AND
> ACDCallDetail.CallStartDt<@.end_date) to no avaial.
> i also need to have the query that extracts the month only (for a month to
> date) out of say the start_date & the end_date (for month to date figures)
> Thankyou in adavance for any help
> todd|||The parameters box comes up & i enter dates (tried both 01/08/2005 or
01/08/2005 12:00 AM) & i get following error message
must declare variable '@.start_date'
Thanks
Todd
"Derrick Van Hoeter" wrote:
> What happens when you use
> WHERE ACDCallDetail.CallStartDt >= @.start_date
> AND ACDCallDetail.CallStartDt < @.end_date
> ?
> Derrick
>
> "Tango" <Tango@.discussions.microsoft.com> wrote in message
> news:3401F98B-707F-4FA2-BEB7-33699994222D@.microsoft.com...
> > Hi,
> > I am querying a sybase database for my table & need to add a start date &
> > end date to the query.
> > my where statement looks like this
> > WHERE (ACDCallDetail.CallStartDt>={ts '2005-08-09 08:00:00'} AND
> > ACDCallDetail.CallStartDt<{ts '2005-08-09 20:00:01'})
> >
> > can i please have some assistance around inserting the parameters
> > start_date
> > & end_date
> >
> > i have tried
> >
> > WHERE (ACDCallDetail.CallStartDt>=:start_date AND
> > ACDCallDetail.CallStartDt<:end_date) and also WHERE
> > (ACDCallDetail.CallStartDt>=@.start_date AND
> > ACDCallDetail.CallStartDt<@.end_date) to no avaial.
> >
> > i also need to have the query that extracts the month only (for a month to
> > date) out of say the start_date & the end_date (for month to date figures)
> >
> > Thankyou in adavance for any help
> >
> > todd
>
>|||Todd,
In what environment are you running the query?
Derrick
"Tango" <Tango@.discussions.microsoft.com> wrote in message
news:6E1BE054-6750-4126-A455-643185384088@.microsoft.com...
> The parameters box comes up & i enter dates (tried both 01/08/2005 or
> 01/08/2005 12:00 AM) & i get following error message
> must declare variable '@.start_date'
> Thanks
> Todd
> "Derrick Van Hoeter" wrote:
>> What happens when you use
>> WHERE ACDCallDetail.CallStartDt >= @.start_date
>> AND ACDCallDetail.CallStartDt < @.end_date
>> ?
>> Derrick
>>
>> "Tango" <Tango@.discussions.microsoft.com> wrote in message
>> news:3401F98B-707F-4FA2-BEB7-33699994222D@.microsoft.com...
>> > Hi,
>> > I am querying a sybase database for my table & need to add a start date
>> > &
>> > end date to the query.
>> > my where statement looks like this
>> > WHERE (ACDCallDetail.CallStartDt>={ts '2005-08-09 08:00:00'} AND
>> > ACDCallDetail.CallStartDt<{ts '2005-08-09 20:00:01'})
>> >
>> > can i please have some assistance around inserting the parameters
>> > start_date
>> > & end_date
>> >
>> > i have tried
>> >
>> > WHERE (ACDCallDetail.CallStartDt>=:start_date AND
>> > ACDCallDetail.CallStartDt<:end_date) and also WHERE
>> > (ACDCallDetail.CallStartDt>=@.start_date AND
>> > ACDCallDetail.CallStartDt<@.end_date) to no avaial.
>> >
>> > i also need to have the query that extracts the month only (for a month
>> > to
>> > date) out of say the start_date & the end_date (for month to date
>> > figures)
>> >
>> > Thankyou in adavance for any help
>> >
>> > todd
>>|||Use
WHERE
(ACDCallDetail.CallStartDt>=@.start_date AND
ACDCallDetail.CallStartDt<@.end_date)
You must add parameters to the report Called start_date and end_date ( use
the same case as the where clause)
Then open the data set and check the Parameters tab to ensure there is a
mapping between the parameter and the variable..
To get a month value from a date in SQL
select Datepart(mm,Getdate())
This returns the number of the month...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Tango" <Tango@.discussions.microsoft.com> wrote in message
news:3401F98B-707F-4FA2-BEB7-33699994222D@.microsoft.com...
> Hi,
> I am querying a sybase database for my table & need to add a start date &
> end date to the query.
> my where statement looks like this
> WHERE (ACDCallDetail.CallStartDt>={ts '2005-08-09 08:00:00'} AND
> ACDCallDetail.CallStartDt<{ts '2005-08-09 20:00:01'})
> can i please have some assistance around inserting the parameters
> start_date
> & end_date
> i have tried
> WHERE (ACDCallDetail.CallStartDt>=:start_date AND
> ACDCallDetail.CallStartDt<:end_date) and also WHERE
> (ACDCallDetail.CallStartDt>=@.start_date AND
> ACDCallDetail.CallStartDt<@.end_date) to no avaial.
> i also need to have the query that extracts the month only (for a month to
> date) out of say the start_date & the end_date (for month to date figures)
> Thankyou in adavance for any help
> todd|||Thanks Guys
I am using the where statement as suggested, the 2 parameters in the report.
Still doesnt work.
when i run the query in generic query designer, i get asked for parameters
where i have tried entering date format dd/mm/yyyy 12:00 AM or just
dd/mm/yyyy. I have also tried changing the parameter type from datetime to
string & i get the following message
Error [HY000] [DataDirect] [ODBC Sybase Driver][SQL Server] Must declare
variable '@.start_date'.
Its no use using the sql query designer as the system just locks up when i
have dates pre coded or gives error message when i attempt to run the query
"Providor can not derive parameter information and setparameterinfo has not
been called"
Look forward to hearing from you.
Todd
Im not sure if this means anything but i am querying a sybase database via
an ODBC (type) datasource.
"Wayne Snyder" wrote:
> Use
> WHERE
> (ACDCallDetail.CallStartDt>=@.start_date AND
> ACDCallDetail.CallStartDt<@.end_date)
> You must add parameters to the report Called start_date and end_date ( use
> the same case as the where clause)
> Then open the data set and check the Parameters tab to ensure there is a
> mapping between the parameter and the variable..
> To get a month value from a date in SQL
> select Datepart(mm,Getdate())
> This returns the number of the month...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Tango" <Tango@.discussions.microsoft.com> wrote in message
> news:3401F98B-707F-4FA2-BEB7-33699994222D@.microsoft.com...
> > Hi,
> > I am querying a sybase database for my table & need to add a start date &
> > end date to the query.
> > my where statement looks like this
> > WHERE (ACDCallDetail.CallStartDt>={ts '2005-08-09 08:00:00'} AND
> > ACDCallDetail.CallStartDt<{ts '2005-08-09 20:00:01'})
> >
> > can i please have some assistance around inserting the parameters
> > start_date
> > & end_date
> >
> > i have tried
> >
> > WHERE (ACDCallDetail.CallStartDt>=:start_date AND
> > ACDCallDetail.CallStartDt<:end_date) and also WHERE
> > (ACDCallDetail.CallStartDt>=@.start_date AND
> > ACDCallDetail.CallStartDt<@.end_date) to no avaial.
> >
> > i also need to have the query that extracts the month only (for a month to
> > date) out of say the start_date & the end_date (for month to date figures)
> >
> > Thankyou in adavance for any help
> >
> > todd
>
>|||Todd,
I have received the same error message before when I have changed the query
around the parameters. I don't have Reporting Service installed on this
machine so I'm going from memory, but in the generic query design pane,
there is a button to refresh data. Find that button and refresh the data,
then try to run the query with the parameters again. You may still get an
error but it should be a different error.
Let me know how it goes.
Derrick
"Tango" <Tango@.discussions.microsoft.com> wrote in message
news:58575D27-7103-4748-86C4-D4B2D3CF27E9@.microsoft.com...
> Thanks Guys
> I am using the where statement as suggested, the 2 parameters in the
> report.
> Still doesnt work.
> when i run the query in generic query designer, i get asked for parameters
> where i have tried entering date format dd/mm/yyyy 12:00 AM or just
> dd/mm/yyyy. I have also tried changing the parameter type from datetime to
> string & i get the following message
> Error [HY000] [DataDirect] [ODBC Sybase Driver][SQL Server] Must declare
> variable '@.start_date'.
> Its no use using the sql query designer as the system just locks up when i
> have dates pre coded or gives error message when i attempt to run the
> query
> "Providor can not derive parameter information and setparameterinfo has
> not
> been called"
> Look forward to hearing from you.
> Todd
> Im not sure if this means anything but i am querying a sybase database via
> an ODBC (type) datasource.
>
> "Wayne Snyder" wrote:
>> Use
>> WHERE
>> (ACDCallDetail.CallStartDt>=@.start_date AND
>> ACDCallDetail.CallStartDt<@.end_date)
>> You must add parameters to the report Called start_date and end_date (
>> use
>> the same case as the where clause)
>> Then open the data set and check the Parameters tab to ensure there is a
>> mapping between the parameter and the variable..
>> To get a month value from a date in SQL
>> select Datepart(mm,Getdate())
>> This returns the number of the month...
>> --
>> Wayne Snyder, MCDBA, SQL Server MVP
>> Mariner, Charlotte, NC
>> www.mariner-usa.com
>> (Please respond only to the newsgroups.)
>> I support the Professional Association of SQL Server (PASS) and it's
>> community of SQL Server professionals.
>> www.sqlpass.org
>> "Tango" <Tango@.discussions.microsoft.com> wrote in message
>> news:3401F98B-707F-4FA2-BEB7-33699994222D@.microsoft.com...
>> > Hi,
>> > I am querying a sybase database for my table & need to add a start date
>> > &
>> > end date to the query.
>> > my where statement looks like this
>> > WHERE (ACDCallDetail.CallStartDt>={ts '2005-08-09 08:00:00'} AND
>> > ACDCallDetail.CallStartDt<{ts '2005-08-09 20:00:01'})
>> >
>> > can i please have some assistance around inserting the parameters
>> > start_date
>> > & end_date
>> >
>> > i have tried
>> >
>> > WHERE (ACDCallDetail.CallStartDt>=:start_date AND
>> > ACDCallDetail.CallStartDt<:end_date) and also WHERE
>> > (ACDCallDetail.CallStartDt>=@.start_date AND
>> > ACDCallDetail.CallStartDt<@.end_date) to no avaial.
>> >
>> > i also need to have the query that extracts the month only (for a month
>> > to
>> > date) out of say the start_date & the end_date (for month to date
>> > figures)
>> >
>> > Thankyou in adavance for any help
>> >
>> > todd
>>|||Thanks for your interest Derrick & Tom,
in generic query designer the error message after entering dates is 'must
declare variable '@.start''.
when i refresh the pane i get error message That data extension odbc does
not support named paramters. use unnmaed paramaters instaed.
Todd
"Derrick Van Hoeter" wrote:
> Todd,
> I have received the same error message before when I have changed the query
> around the parameters. I don't have Reporting Service installed on this
> machine so I'm going from memory, but in the generic query design pane,
> there is a button to refresh data. Find that button and refresh the data,
> then try to run the query with the parameters again. You may still get an
> error but it should be a different error.
> Let me know how it goes.
> Derrick
>
> "Tango" <Tango@.discussions.microsoft.com> wrote in message
> news:58575D27-7103-4748-86C4-D4B2D3CF27E9@.microsoft.com...
> > Thanks Guys
> >
> > I am using the where statement as suggested, the 2 parameters in the
> > report.
> > Still doesnt work.
> > when i run the query in generic query designer, i get asked for parameters
> > where i have tried entering date format dd/mm/yyyy 12:00 AM or just
> > dd/mm/yyyy. I have also tried changing the parameter type from datetime to
> > string & i get the following message
> > Error [HY000] [DataDirect] [ODBC Sybase Driver][SQL Server] Must declare
> > variable '@.start_date'.
> > Its no use using the sql query designer as the system just locks up when i
> > have dates pre coded or gives error message when i attempt to run the
> > query
> > "Providor can not derive parameter information and setparameterinfo has
> > not
> > been called"
> >
> > Look forward to hearing from you.
> > Todd
> > Im not sure if this means anything but i am querying a sybase database via
> > an ODBC (type) datasource.
> >
> >
> > "Wayne Snyder" wrote:
> >
> >> Use
> >> WHERE
> >> (ACDCallDetail.CallStartDt>=@.start_date AND
> >> ACDCallDetail.CallStartDt<@.end_date)
> >>
> >> You must add parameters to the report Called start_date and end_date (
> >> use
> >> the same case as the where clause)
> >> Then open the data set and check the Parameters tab to ensure there is a
> >> mapping between the parameter and the variable..
> >>
> >> To get a month value from a date in SQL
> >>
> >> select Datepart(mm,Getdate())
> >>
> >> This returns the number of the month...
> >>
> >> --
> >> Wayne Snyder, MCDBA, SQL Server MVP
> >> Mariner, Charlotte, NC
> >> www.mariner-usa.com
> >> (Please respond only to the newsgroups.)
> >>
> >> I support the Professional Association of SQL Server (PASS) and it's
> >> community of SQL Server professionals.
> >> www.sqlpass.org
> >>
> >> "Tango" <Tango@.discussions.microsoft.com> wrote in message
> >> news:3401F98B-707F-4FA2-BEB7-33699994222D@.microsoft.com...
> >> > Hi,
> >> > I am querying a sybase database for my table & need to add a start date
> >> > &
> >> > end date to the query.
> >> > my where statement looks like this
> >> > WHERE (ACDCallDetail.CallStartDt>={ts '2005-08-09 08:00:00'} AND
> >> > ACDCallDetail.CallStartDt<{ts '2005-08-09 20:00:01'})
> >> >
> >> > can i please have some assistance around inserting the parameters
> >> > start_date
> >> > & end_date
> >> >
> >> > i have tried
> >> >
> >> > WHERE (ACDCallDetail.CallStartDt>=:start_date AND
> >> > ACDCallDetail.CallStartDt<:end_date) and also WHERE
> >> > (ACDCallDetail.CallStartDt>=@.start_date AND
> >> > ACDCallDetail.CallStartDt<@.end_date) to no avaial.
> >> >
> >> > i also need to have the query that extracts the month only (for a month
> >> > to
> >> > date) out of say the start_date & the end_date (for month to date
> >> > figures)
> >> >
> >> > Thankyou in adavance for any help
> >> >
> >> > todd
> >>
> >>
> >>
>
>

Help with nearest neighbour problem

Hi,

I need help with creating an sql statement that determines the nearest neighbour in a lookup table to my dataset by its date column. My attempts minimizing the date difference in a cross join are lacking performance.

I have two tables:

Table 1 (Data; 13000 datasets):

ID, date
==========
1, 12.12.2006
2, 28.12.2006
3, 05.01.2007

and Table 2 (Lookup; 4000 datasets):

date, margin
==========
05.12.2006, 2.80
27.12.2006, 2.86
01.01.2007, 3.01
10.01.2007, 2.99

Expected result:

ID, date, margin
==========
1, 12.12.2006, 2.80
2, 28.12.2006, 2.86
3, 05.01.2007, 3.01

Any help is much desired

Kaicould you explain please how to calculate "nearest"|||could you explain please how to calculate "nearest"

For a given date in the data table I'm looking for the closest date in the lookup table, e.g. MIN(Datediff(dd,date(data),date(lookup))).|||that datediff might produce negative numbers, and MIN will take the largest negative number

do you perhaps mean MIN(ABS(...)) ?|||that datediff might produce negative numbers, and MIN will take the largest negative number

do you perhaps mean MIN(ABS(...)) ?

You're right, it's MIN(ABS(...)). But how do I integrate this into a view to do effective lookups?|||effective? i would imagine this to depend on the existence of appropriate indexes

the following works (i tested it on your data) but i dunno how slow it's gonna be for your large tables...with X
( ID
, TDate
, a
, LDate
, margin
)
as (
select T.ID
, T.Date as TDate
, abs(datediff(dd,T.Date,L.Date)) as a
, L.Date as LDate
, L.margin
from table1 as T
cross
join lookup as L
)
select ID
, TDate
, a
, LDate
, margin
from X as D1
where a =
( select min(a)
from X
where ID = D1.ID )|||That's quite some nifty code. Unfortunately I had to rewrite the code to not use the "WITH" statement as MS SQL Server 2000 apparently doesn't support this. Anyway I've came down to 1 min. processing time from 57 min. without touching the indexes, so thanks a lot.

This is my final code:

Select ID
, TDate
, a
, LDate
, margin
from
(
select T.ID
, T.Date as TDate
, abs(datediff(dd,T.Date,L.Date)) as a
, L.Date as LDate
, L.margin
from table1 as T
cross
join lookup as L
) X
where a =
(
select min(abs(datediff(dd,T.Date,L.Date))) as amin
from table1 as T
cross
join lookup as L
where T.ID = X.ID
)

Help with JOINS in Stored Procedures

I am making a stored procedure, and I need to set some variables based on the results of a JOIN statement.

If I something like:

SELECT table.field, table.field2 FROM table INNER JOIN table2 ON table.field = string

How could I get the results and set them in variables?

Any help would be greatly appreciated.

GKC

Are you trying to do something like this?

CREATE PROCEDURE dbo.TestProcedure
(
@.SomeString varchar(50)
)
AS
SET NOCOUNT ON
SELECT t1.Field1, t1.Field2
FROM Table AS t1
INNER JOIN Table2 AS t2 ON t2.Field = t1.Field1
WHERE t1.Field1 = @.SomeString

HTH,
Ryan

|||No, basically I want to do this:

SELECT field1, field2, field3
FROM first_table
INNER JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield

And take the results and put set them in some variables|||

DECLARE @.f1 ...
DECLARE @.f2 ...
DECLARE @.f3 ...

SELECT @.f1=field1, @.f2=field2, @.f3=field3
FROM first_table
INNER JOIN second_table
ON first_table.keyfield = second_table.foreign_keyfield

|||

GENIUS! So simple, I never thought!

Thank you!

Help with JOIN logic...

Arrrgggg, nothing makes any sence any more. I need to write a select statement that will display results based on one of two things...

Zip Code OR City State

By the way this is a Table of addresses with zip codes, I also have a Lat/Long table associated to all the different zip codes that need to be joined.

The Psuedo code is somethinglike this...

SHOW All People WITH Appropreate Longditude and Latitude
Who Live
In @.This ZipCode
OR
(In @.State AND @.City)

------
Table Dealers
------
dlrID - varchar
dlrName - varchar
dlrAddress - varchar
dlrCity - varchar
dlrState - varchar
dlrZip - varchar
dlrCountry - varchar
------

------
Table ZipCodes
------
zipCode
zipCity
zipState
zipCountrty
zipLat
zipLong
...
------

I have achieved this with Joins but the query takes 2+ minutes to execute

What am I doing wrong? I am sure there is a better approach to this!

Thanks,
--LitoOption A: Would it be worth attempting a UNION query to achieve this?

SHOW all people in this ZIP Code

UNION

SHOW all people in this City and State.

Option B: Write a stored Procedure:

CREATE PROC spShowDealers (
@.ZipCode varchar(10) = '',
@.City varchar(30) = '',
@.State char(2) = ''

AS

IF ZipCode = ''
BEGIN
SHOW all dealers where City = @.City and State = @.State
END

ELSE
SHOW all dealers where ZipCode = @.ZipCode
END|||Thanks for the suggestion,

the problem with UNION is that I will end up with duplicates, because the zip code could be valid as well as the city and state info.

Stored Procedure might work,

something like
if zip code doesn't return a record,
try city and state combination.

Maybe :)

I'll give it a shot, thanks again.

--Lito|||You won't end up with duplicates using UNION. You will end up with duplicates if you use UNION ALL. Big difference here. Unfortunately, there's also a big difference in performance.|||You won't end up with duplicates using UNION. You will end up with duplicates if you use UNION ALL. Big difference here. Unfortunately, there's also a big difference in performance.

I do end up with duplicates, and I am not sure what to do about it.

here is the query...
SELECT DISTINCT(UTIL_DLR_ONLY.DealerID), UTIL_DLR_ONLY.City, UTIL_DLR_ONLY.State, ZCUG.Long, ZCUG.Lat
FROM ZCUG, UTIL_DLR_ONLY WHERE UTIL_DLR_ONLY.Zip = ZCUG.ZIP

UNION
SELECT DISTINCT(UTIL_DLR_ONLY.DealerID), UTIL_DLR_ONLY.City, UTIL_DLR_ONLY.State, ZCUG.Long, ZCUG.Lat
FROM ZCUG, UTIL_DLR_ONLY WHERE UTIL_DLR_ONLY.City = ZCUG.City AND UTIL_DLR_ONLY.State = ZCUG.St

And here are the results...
...

Mesa AZ 111.788 33.3970 C014900
Mesa AZ 111.789 33.3976 C014900
Mesa AZ 111.804 33.4484 C014900
Mesa AZ 111.842 33.3901 C014900
Mesa AZ 111.847 33.4411 C014900
Mesa AZ 111.848 33.4340 C014900
Mesa AZ 111.873 33.3827 C014900
Mesa AZ 111.875 33.3845 C014925
Phoenix AZ 111.964 33.6787 C014925
Phoenix AZ 111.973 33.4505 C014925
Phoenix AZ 111.981 33.4998 C014925
Phoenix AZ 111.981 33.5003 C014925
Phoenix AZ 111.987 33.4647 C014925
Phoenix AZ 111.987 33.4650 C014925
Phoenix AZ 111.987 33.4651 C014925
Phoenix AZ 111.987 33.4653 C014925

...

I know it's because of many->one relatonship between LONG and LAT and the City/State, but what can I do about it?

Thanks,
Lito|||I'm not seeing the duplicate rows. There are multiple rows for the same city, but they have different coordinates (probably for the postal center).

Are you looking to get only one row returned by city and state? If so, just group on the city and state columns.

-PatP|||Pat Phelan,

That's what I mean when I get duplicates is that I only need one city and state to be listed...

I have tried GROUP BY and I get an error...
Column 'ZCUG.Long' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

If I include 'ZCUG.Long' in the group clause I get the same result...

This thing should not be this difficult ...

Does any one know a better way of doing this?|||If you don't want duplicates on Long/Lat, then you need to exclude those columns from the SELECT.|||What you appear to want is:SELECT
UTIL_DLR_ONLY.City, UTIL_DLR_ONLY.State
FROM ZCUG
JOIN UTIL_DLR_ONLY
ON (UTIL_DLR_ONLY.Zip = ZCUG.ZIP )
UNION SELECT
UTIL_DLR_ONLY.City, UTIL_DLR_ONLY.State
FROM ZCUG
JOIN UTIL_DLR_ONLY
ON (UTIL_DLR_ONLY.City = ZCUG.City
AND UTIL_DLR_ONLY.State = ZCUG.St)Give that a try and see if it does what you want.

-PatP|||If you don't want duplicates on Long/Lat, then you need to exclude those columns from the SELECT.

The problem is I need Long/Lat, but only one set per City/State .|||The problem is I need Long/Lat, but only one set per City/State .Oh, you should have said that up front! Then I'd use:SELECT City, State, Min(Long), Min(Lat)
FROM (
SELECT
UTIL_DLR_ONLY.City, UTIL_DLR_ONLY.State, ZCUG.Long, ZCUG.Lat
FROM ZCUG
JOIN UTIL_DLR_ONLY
ON (UTIL_DLR_ONLY.Zip = ZCUG.ZIP )
UNION SELECT
UTIL_DLR_ONLY.City, UTIL_DLR_ONLY.State, ZCUG.Long, ZCUG.Lat
FROM ZCUG
JOIN UTIL_DLR_ONLY
ON (UTIL_DLR_ONLY.City = ZCUG.City
AND UTIL_DLR_ONLY.State = ZCUG.St)) AS a
GROUP BY 1, 2-PatP|||I really appreciate you helping me with this, it is driving me nuts!

PatP,
This produces almost exactly what I want, except I also need, DealerID and the (Long, Lat) cordiantes , and as soon as I add those to the query, everything breaks, because of the many to one relationship of long,lat and the city/state combo, is there a way to specify, only the first set of long/lat per city?

Thanks.

What you appear to want is:SELECT
UTIL_DLR_ONLY.City, UTIL_DLR_ONLY.State
FROM ZCUG
JOIN UTIL_DLR_ONLY
ON (UTIL_DLR_ONLY.Zip = ZCUG.ZIP )
UNION SELECT
UTIL_DLR_ONLY.City, UTIL_DLR_ONLY.State
FROM ZCUG
JOIN UTIL_DLR_ONLY
ON (UTIL_DLR_ONLY.City = ZCUG.City
AND UTIL_DLR_ONLY.State = ZCUG.St)Give that a try and see if it does what you want.

-PatP|||Try the revised query I posted using Min to see if that gets closer. If it does, in the case of a large city which dealership do you want to use (lowest or highest DealerID/Long/Lat)? How do you deal with ties if they are possible (same lat or long, etc)?

-PatP|||Try the revised query I posted using Min to see if that gets closer. If it does, in the case of a large city which dealership do you want to use (lowest or highest DealerID/Long/Lat)? How do you deal with ties if they are possible (same lat or long, etc)?

-PatP

Thank you so much
This is as good as it gets!!!

If there happen to be 2 dealers in the same Long/Lat, it doesn't matter which one, so long as we find one of them. I sort the list by DealerID, so I guess the lowest Dealer ID will get pulled. I made some changes to the revised query to accomodate my needs, but other then that This is unbeliveable, I have spen about 6 hours trying all sorts of combinations to produce this result and you solve it in mins.

Thanks again PatP.

--Lito

modified...
SELECT DealerID, City, State, Zip, Min(Long) 'Long', Min(Lat) 'Lat'
FROM (
SELECT
UTIL_DLR_ONLY.DealerID, UTIL_DLR_ONLY.City, UTIL_DLR_ONLY.State, UTIL_DLR_ONLY.Zip, ZCUG.Long, ZCUG.Lat
FROM ZCUG
JOIN UTIL_DLR_ONLY
ON UTIL_DLR_ONLY.Zip = ZCUG.ZIP
WHERE UTIL_DLR_ONLY.Country = 'CA'
UNION SELECT
UTIL_DLR_ONLY.DealerID, UTIL_DLR_ONLY.City, UTIL_DLR_ONLY.State, UTIL_DLR_ONLY.Zip, ZCUG.Long, ZCUG.Lat
FROM ZCUG
JOIN UTIL_DLR_ONLY
ON (UTIL_DLR_ONLY.City = ZCUG.City
AND UTIL_DLR_ONLY.State = ZCUG.St)
WHERE UTIL_DLR_ONLY.Country = 'CA')
AS a
GROUP BY DealerID,City, State, Zip
ORDER BY DealerID

Help with Insert Statement!!

Can somebody help me with an Insert statement. I'm trying to insert data from one table into another table.
My structure that i wrote isn't working at all.
Please Helpif both tables have the same structure:
insert into Another_Table
select * from One_Table

if you wish to only insert data from a few attributes:
insert into Another_Table (First, Last, Middle)
select FirstName, LastName, MiddleName from One_Table

Of course you can add a where clause when needed