retrieve Datetime: returns

Showing posts with label returns. Show all posts

Friday, March 30, 2012

Return Date not DateTime

I am trying to count the amount of distinct dates (not datetime) in a table row. The call below returns the amount of distinct datetimes. How do I strip off the time when doing the SQL call?

SELECT COUNT(DISTINCT DT) FROM Event

SELECTConvert(Varchar,DT,101),Count(*))FROM EventGroup byConvert(Varchar,DT,101)

|||

SELECTCOUNT(DISTINCTDAY(DT)+' /'+MONTH(DT)+' /'+YEAR(DT))FROMEvent

Wednesday, March 28, 2012

Return all months within a range of dates

I currently have a stored procedure that returns a list of dates based on a date range a user enters.


CREATE PROCEDURE sp_GetContactScheduleDates
@.MonthFrom int,
@.YearFrom int,
@.MonthTo int,
@.YearTo int,
@.DaysInMonth int
AS
Select distinct s.ScheduleMonth, s.ScheduleYear
From OnCall_Schedules s
Where CAST(cast(s.ScheduleMonth as nvarchar) + '/' + cast(s.ScheduleDate as nvarchar) + '/' + cast(s.ScheduleYear as nvarchar) as smalldatetime)
 >= CAST(cast(@.MonthFrom as nvarchar) + '/' + cast('01' as nvarchar) + '/' + cast(@.YearFrom as nvarchar) as smalldatetime)
 And CAST(cast(s.ScheduleMonth as nvarchar) + '/' + cast(s.ScheduleDate as nvarchar) + '/' + cast(s.ScheduleYear as nvarchar) as smalldatetime)
 <= CAST(cast(@.MonthTo as nvarchar) + '/' + cast(@.DaysInMonth as nvarchar) + '/' + cast(@.YearTo as nvarchar) as smalldatetime)
Order by s.ScheduleYear, s.ScheduleMonth
GO

However, this only brings back those dates that are in the table. I need to get ALL dates within the range.

For example, the OnCall_Schedules table contains schedules that are saved by the user. If no one has ever saved a schedule at any time in May 2004 and the range of dates entered is January 2004 to June 2004, then May 2004 will not be returned. I need to get back all dates within that range regardless if it has something scheduled or not. How can this be done?

Note - I do not want to set up any dummy records or create a table with valid dates as the user will be allowed to choose any range of dates and we do not want to have to maintain anything.

Can some sort of function be used? What would the code look like?I would create a table variable with one field that will hold the date. The do a loop to populate it. I'd make sure @.startdate and @.enddate have the time stripped off. Not tested, but should work with minor tweaks.


set @.date = @.startdate
set @.x = datediff(d, @.startdate, @.enddate)
set @.y = 0
While @.y <= @.x
 Begin
 insert into @.table (datefield) values (dateadd(d, @.y, @.startdate))
 set @.y = @.y + 1
 End

|||ooo that's a nice loop. :)

return all ESNID one time, the most recent

This is more ASP SElect .

I need to return all the rows. Where the ESNnumber only returns the most recent one that is associsted with the Asset.

Basically, I need the info most current ESN number only.
They are 19,00 rows of each ESN number but it returns 40,000. Duplicates.

SELECT TOP (100) PERCENT dbo.AssetType.Description, dbo.AssetCustomAttributeDef.Name AS [Custom Asset], dbo.ESN.EsnNumber AS [ESN #],
dbo.AssetAttribute.AssetDescription AS [Description Detail], dbo.Asset.Barcode, dbo.Asset.SKU,
dbo.InventoryOrigin.WarehouseDescription AS [Inventory (W/H)], dbo.ESN.DateImplemented, dbo.ESNTracking.TraceTime,
dbo.ESNTracking.PreviousTraceTime, dbo.ESNTracking.HasMoved, dbo.ESNTracking.DistanceMiles, dbo.ESNTracking.Direction,
dbo.ESNTracking.Landmark, dbo.ESNTracking.FemaLocation AS Fema, dbo.ESNTracking.ReportTime AS [Report Time],
dbo.ESNTracking.CurrLocStreet AS Address, dbo.ESNTracking.CurrLocCity AS City, dbo.ESNTracking.CurrLocState AS State,
dbo.ESNTracking.CurrLocZip AS Zipcode, dbo.ESNTracking.CurrLocCounty AS County, dbo.ESNTracking.MapUrl AS [Map Link],
dbo.ESNTracking.ReplaceByDate AS [Replace Batt.], dbo.ESNTracking.CurrMileFromStratix AS [From Stratix Now],
dbo.ESNTracking.PrevMileFromStratix AS [From STratix Then]
FROM dbo.AssetType INNER JOIN
dbo.Asset ON dbo.AssetType.AssetTypeId = dbo.Asset.AssetTypeId INNER JOIN
dbo.InventoryOrigin ON dbo.Asset.WarehouseId = dbo.InventoryOrigin.WarehouseId INNER JOIN
dbo.AssetAttribute ON dbo.Asset.AssetAttributeId = dbo.AssetAttribute.AssetAttributeId INNER JOIN
dbo.EsnAsset ON dbo.Asset.AssetId = dbo.EsnAsset.AssetId INNER JOIN
dbo.ESN ON dbo.EsnAsset.EsnId = dbo.ESN.EsnId LEFT OUTER JOIN
dbo.ESNTracking ON dbo.EsnAsset.EsnId = dbo.ESNTracking.EsnId LEFT OUTER JOIN
dbo.AssetVehicle ON dbo.EsnAsset.AssetId = dbo.AssetVehicle.AssetId LEFT OUTER JOIN
dbo.AssetCustomAttribute ON dbo.EsnAsset.AssetId = dbo.AssetCustomAttribute.AssetId LEFT OUTER JOIN
dbo.AssetCustomAttributeDef ON dbo.AssetCustomAttribute.AssetTypeId = dbo.AssetCustomAttributeDef.AssetTypeId

ORDER BY dbo.AssetType.Description

If I have understood this correctly...

1. Drop a MULTICAST into your flow.

2. On one output, use AGGREGATE to work out the max ESN Number per asset.

3. Join that back to the other output using MERGE JOIN, joining on ESN Number and Asset

Does that work?

-Jamie

|||

Could you help me please.

I need help.

writiing the query with thos parameters

Return ADO recordset to worksheet

Any help would be appreciated.
I am trying to return a recordset to a worksheet using ADO. I want to go
dsn-less.
I confident that my recordset returns data, I've tested this in Access and
can return records there. I am a new excel programmer.
I am trying to use CopyFromRecordset to return the records. When I am in
the VBA editor I choose Run Sub but no records are returned to Sheet1.
I'd like the recordset to be refreshed every time the sheet is activated.
I've created a reference to Microsoft ActiveX Data Objects 2.8 library.
Am I missing something?
Sub ImportData()
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
Dim strConn As String
strConn = "PROVIDER=SQLOLEDB;"
strConn = strConn & "SERVER=MYSERVER;INITIAL CATALOG=MYDATABASE;"
strConn = strConn & " INTEGRATED SECURITY=sspi;"
cn.Open strConn
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
With rs
.ActiveConnection = cn
.Open "SELECT * FROM MyTable"
Sheet1.Range("A1").CopyFromRecordset rs
.Close
End With
cn.Close
Set rs = Nothing
Set cn = Nothing
End SubSorry wrong newsgroup please ignore.
"Terri" <terri@.cybernets.com> wrote in message
news:e6c2n6$vlf$1@.reader2.nmix.net...
> Any help would be appreciated.
>sql

Return a UNIQUEIDENTIFIER

Hi,

I am writing a C# application that uses a SQL server database to hold its data. I need to create a stored procedure that returns a particular row's primary key value. This is no problem if the primary key is an INT. But my primary key is a unique identifier, and the stored procedure doesn't want to let me return any values that aren't INTs. Can someone please tell me how to get around this?

Thanks in advance.

ScottYou'll have to declare it as an outparameter.
And if you want something easier to handle you can convert it to
a varchar using CONVERT(myguid,VARCHAR)

Regards
Fredr!k|||Fredrik2000,

Thank you so much. That is exactly what I needed. Also, for anyone else out there, it is actually in the format:

CONVERT(VARCHAR(36), myguid)

where of course 36 is the number of characters allocated for the datatype.

Sc0tt|||Ahh, I always get the order mixed up (didn't have a copy of books online at the computer
I'm posting from...)

Nice to hear you got it working.

Regards
Fredr!k

Return a todays Date query?

Hello , i want to writ a query that returns ruslts for today's date only,How to do it? i tried to filter the results using Now() function but it did`t work, any help please?

hi leonardo ,

try this

select columns from tablename where convert(varchar, column_with_date_datatype, 103) = convert(varchar,getdate(),103)

hope it helps

regards,

satish

|||

Hi Leo,

U can also use the split function to receive the date.

I think the now.tostring gives date<space>time

so,

dim dat() as string

dat=split(now.tostring)

Msgbox(dat(0).tostring)

dat(1)-->time

-PSK

|||

Thanks for the help Smile

but what i want is to build up a query in the SQL Server that returns today's date in SQL Statment

Select JoinDate

From Employee

Where **********;

how to have a result for a today's date , a week before and a month before ...

|||

you could use

dateadd() function in that case, for more help refer to sql books online.

thanks,

satish.

|||

You could get the start of the day like so DATEADD(day, DATEDIFF(day, 0, GetDate()), 0)..

This means you would do something like...

SELECT * FROM table WHERE Created >= DATEADD(day, DATEDIFF(day, 0, GetDate()), 0)

Enjoy, Steve

Return a resultset to XML?

Hello I am trying to pass a recordset to XML
Database: pubs
select * from titles for xml auto
but it returns me this
<titles title_id="BU1032" title="The Busy Executive's Database Guide"
type="business " pub_id="1389" price="20.0000" advance="5000.0000"
royalty="10" ytd_sales="4095" notes="An overview of available database
systems with emphasis on common business
royalty="16" ytd_sales="8780" notes="A survey of software for the naive
user, focusing on the 'friendliness' of each."
pubdate="1991-06-30T00:00:00"/><titles title_id="PC8888" title="Secrets of
Silicon Valley" type="popular_comp" pub_id="1389" pr
"psychology " pub_id="0736" price="8.0000" advance="4000.0000" royalty="10"
ytd_sales="3336" notes="Protecting yourself and your loved ones from undue
emotional stress in the modern world. Use of computer and nutritional aids
emphasized." pubdate="1991-06
I dont want that way
I would like more or less
<title>title1</title>
<type>type</type>
and so on.
ThanksHi
Look at the FOR EXPLICITY clause in books online and you may be able to do
what you require.
John
"Luis Esteban Valencia" wrote:
> Hello I am trying to pass a recordset to XML
> Database: pubs
> select * from titles for xml auto
> but it returns me this
> <titles title_id="BU1032" title="The Busy Executive's Database Guide"
> type="business " pub_id="1389" price="20.0000" advance="5000.0000"
> royalty="10" ytd_sales="4095" notes="An overview of available database
> systems with emphasis on common business
> royalty="16" ytd_sales="8780" notes="A survey of software for the naive
> user, focusing on the 'friendliness' of each."
> pubdate="1991-06-30T00:00:00"/><titles title_id="PC8888" title="Secrets of
> Silicon Valley" type="popular_comp" pub_id="1389" pr
> "psychology " pub_id="0736" price="8.0000" advance="4000.0000" royalty="10"
> ytd_sales="3336" notes="Protecting yourself and your loved ones from undue
> emotional stress in the modern world. Use of computer and nutritional aids
> emphasized." pubdate="1991-06
>
> I dont want that way
> I would like more or less
> <title>title1</title>
> <type>type</type>
> and so on.
> Thanks
>
>|||"Luis Esteban Valencia" <levalencia@.avansoft.com> wrote in
news:#VHO9XkgFHA.2180@.TK2MSFTNGP15.phx.gbl:
> Hello I am trying to pass a recordset to XML
> Database: pubs
> select * from titles for xml auto
> but it returns me this
> <titles title_id="BU1032" title="The Busy Executive's Database
> Guide" type="business " pub_id="1389" price="20.0000"
> advance="5000.0000" royalty="10" ytd_sales="4095" notes="An overview
> of available database systems with emphasis on common business
> royalty="16" ytd_sales="8780" notes="A survey of software for the
> naive user, focusing on the 'friendliness' of each."
> pubdate="1991-06-30T00:00:00"/><titles title_id="PC8888"
> title="Secrets of Silicon Valley" type="popular_comp" pub_id="1389" pr
> "psychology " pub_id="0736" price="8.0000" advance="4000.0000"
> royalty="10" ytd_sales="3336" notes="Protecting yourself and your
> loved ones from undue emotional stress in the modern world. Use of
> computer and nutritional aids emphasized." pubdate="1991-06
>
> I dont want that way
> I would like more or less
> <title>title1</title>
> <type>type</type>
> and so on.
> Thanks
>
>
select * from titles for xml auto, elements
--
Regards
JTC ^..^|||It doesnt return good. it seems to cut the strings
look at this
<titles><title_id>BU1032</title_id><title>The Busy Executive's Database
Guide</title><type>business
</type><pub_id>1389</pub_id><price>20.0000</price><advance>5000.0000</advanc
e><royalty>10</royalty><ytd_sales>4095</ytd_sales><notes>An overview of
/price><
"JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> escribió en el mensaje
news:Xns968BBE2E5474DdaveJTC@.213.123.26.234...
> "Luis Esteban Valencia" <levalencia@.avansoft.com> wrote in
> news:#VHO9XkgFHA.2180@.TK2MSFTNGP15.phx.gbl:
> > Hello I am trying to pass a recordset to XML
> > Database: pubs
> >
> > select * from titles for xml auto
> > but it returns me this
> > <titles title_id="BU1032" title="The Busy Executive's Database
> > Guide" type="business " pub_id="1389" price="20.0000"
> > advance="5000.0000" royalty="10" ytd_sales="4095" notes="An overview
> > of available database systems with emphasis on common business
> > royalty="16" ytd_sales="8780" notes="A survey of software for the
> > naive user, focusing on the 'friendliness' of each."
> > pubdate="1991-06-30T00:00:00"/><titles title_id="PC8888"
> > title="Secrets of Silicon Valley" type="popular_comp" pub_id="1389" pr
> > "psychology " pub_id="0736" price="8.0000" advance="4000.0000"
> > royalty="10" ytd_sales="3336" notes="Protecting yourself and your
> > loved ones from undue emotional stress in the modern world. Use of
> > computer and nutritional aids emphasized." pubdate="1991-06
> >
> >
> > I dont want that way
> > I would like more or less
> > <title>title1</title>
> > <type>type</type>
> >
> > and so on.
> >
> > Thanks
> >
> >
> >
> select * from titles for xml auto, elements
> --
> Regards
> JTC ^..^|||It is only Query Analyzer. You can configure where it cut a column (max is 8000). All xml is one
column in QA.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Luis Esteban Valencia" <levalencia@.avansoft.com> wrote in message
news:%23eMjeSlgFHA.3436@.tk2msftngp13.phx.gbl...
> It doesnt return good. it seems to cut the strings
> look at this
> <titles><title_id>BU1032</title_id><title>The Busy Executive's Database
> Guide</title><type>business
> </type><pub_id>1389</pub_id><price>20.0000</price><advance>5000.0000</advanc
> e><royalty>10</royalty><ytd_sales>4095</ytd_sales><notes>An overview of
> /price><
>
> "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> escribió en el mensaje
> news:Xns968BBE2E5474DdaveJTC@.213.123.26.234...
>> "Luis Esteban Valencia" <levalencia@.avansoft.com> wrote in
>> news:#VHO9XkgFHA.2180@.TK2MSFTNGP15.phx.gbl:
>> > Hello I am trying to pass a recordset to XML
>> > Database: pubs
>> >
>> > select * from titles for xml auto
>> > but it returns me this
>> > <titles title_id="BU1032" title="The Busy Executive's Database
>> > Guide" type="business " pub_id="1389" price="20.0000"
>> > advance="5000.0000" royalty="10" ytd_sales="4095" notes="An overview
>> > of available database systems with emphasis on common business
>> > royalty="16" ytd_sales="8780" notes="A survey of software for the
>> > naive user, focusing on the 'friendliness' of each."
>> > pubdate="1991-06-30T00:00:00"/><titles title_id="PC8888"
>> > title="Secrets of Silicon Valley" type="popular_comp" pub_id="1389" pr
>> > "psychology " pub_id="0736" price="8.0000" advance="4000.0000"
>> > royalty="10" ytd_sales="3336" notes="Protecting yourself and your
>> > loved ones from undue emotional stress in the modern world. Use of
>> > computer and nutritional aids emphasized." pubdate="1991-06
>> >
>> >
>> > I dont want that way
>> > I would like more or less
>> > <title>title1</title>
>> > <type>type</type>
>> >
>> > and so on.
>> >
>> > Thanks
>> >
>> >
>> >
>> select * from titles for xml auto, elements
>> --
>> Regards
>> JTC ^..^
>|||Hi
If you still have problems cutting the tags then put in dummy data values
that contain a carriage return. See http://tinyurl.com/e4rla
John
"Luis Esteban Valencia" wrote:
> It doesnt return good. it seems to cut the strings
> look at this
> <titles><title_id>BU1032</title_id><title>The Busy Executive's Database
> Guide</title><type>business
> </type><pub_id>1389</pub_id><price>20.0000</price><advance>5000.0000</advanc
> e><royalty>10</royalty><ytd_sales>4095</ytd_sales><notes>An overview of
> /price><
>
> "JTC ^..^" <dave@.(nospam)JazzTheCat.co.uk> escribiÃ³ en el mensaje
> news:Xns968BBE2E5474DdaveJTC@.213.123.26.234...
> > "Luis Esteban Valencia" <levalencia@.avansoft.com> wrote in
> > news:#VHO9XkgFHA.2180@.TK2MSFTNGP15.phx.gbl:
> >
> > > Hello I am trying to pass a recordset to XML
> > > Database: pubs
> > >
> > > select * from titles for xml auto
> > > but it returns me this
> > > <titles title_id="BU1032" title="The Busy Executive's Database
> > > Guide" type="business " pub_id="1389" price="20.0000"
> > > advance="5000.0000" royalty="10" ytd_sales="4095" notes="An overview
> > > of available database systems with emphasis on common business
> > > royalty="16" ytd_sales="8780" notes="A survey of software for the
> > > naive user, focusing on the 'friendliness' of each."
> > > pubdate="1991-06-30T00:00:00"/><titles title_id="PC8888"
> > > title="Secrets of Silicon Valley" type="popular_comp" pub_id="1389" pr
> > > "psychology " pub_id="0736" price="8.0000" advance="4000.0000"
> > > royalty="10" ytd_sales="3336" notes="Protecting yourself and your
> > > loved ones from undue emotional stress in the modern world. Use of
> > > computer and nutritional aids emphasized." pubdate="1991-06
> > >
> > >
> > > I dont want that way
> > > I would like more or less
> > > <title>title1</title>
> > > <type>type</type>
> > >
> > > and so on.
> > >
> > > Thanks
> > >
> > >
> > >
> >
> > select * from titles for xml auto, elements
> >
> > --
> > Regards
> > JTC ^..^
>
>

Return a resultset to XML?

Return a horizontal table for result

Has any one had luck writing a query that returns a result as follows:
CheckID PayAmtDeductAmt Tax Amt
1Admin200.00401k70.00FUTA13.00
1SAL1500.00FICA25.00
1MED30.00
1SOC107.00
I have three tables (Pay, Dedcut, and Tax) that are connected to a check table
Check Table
pcKey
pcEmployee
PayCheckDeduction
pcdKey
pcdPayCheck
pcdDeductionDescription
pcdAmount
etc...
the diffuculty I'm having is getting a horizontal return as describe above
for two or more tables connected to one table.
Please post the exact DDL (CREATE TABLE statements) for all of the tables
involved. It sounds like what you want is a pivot table, which can be
easily done.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John" <John@.discussions.microsoft.com> wrote in message
news:05C2EB45-F667-4968-858A-D545DB9D8F41@.microsoft.com...
Has any one had luck writing a query that returns a result as follows:
CheckID Pay Amt Deduct Amt Tax Amt
1 Admin 200.00 401k 70.00 FUTA 13.00
1 SAL 1500.00 FICA 25.00
1 MED 30.00
1 SOC 107.00
I have three tables (Pay, Dedcut, and Tax) that are connected to a check
table
Check Table
pcKey
pcEmployee
PayCheckDeduction
pcdKey
pcdPayCheck
pcdDeductionDescription
pcdAmount
etc...
the diffuculty I'm having is getting a horizontal return as describe above
for two or more tables connected to one table.
|||Here you go Tom, I hope this helps
====================
DROP database zzPayroll
GO
CREATE database zzPayroll
GO
USE zzPayroll
GO
CREATE TABLE PayCheck (
pcKeyintNOT NULL ,
pcPayroll int NULL,
pcEmployee int NOT NULL
)
GO
CREATE TABLE PayType (
paytKeyintNOT NULL,
paytDescriptionvarchar(80)NOT NULL DEFAULT '',
-- 0 = Regular
-- 1 = Salary
-- 2 = Premium 1
-- 3 = Premium 2
-- 4 = Premium 3
-- 5 = Sick pay
-- 6 = Vacation pay
-- 7 = Other pay
-- 8 = Other in/out
-- 9 = Reimbursement
-- 10 = Accrual
paytType tinyint NOT NULL DEFAULT 0
)
GO
CREATE TABLE PayCheckTax (
pctKeyintNOT NULL ,
pctPayCheckintNOT NULL,
pctTaxTypeintNOT NULL,
pctAmount decimal(19,2) NOT NULL DEFAULT 0
)
GO
CREATE TABLE DeductionType (
dedtKeyintNOT NULL,
dedtDescriptionvarchar(80)NOT NULL DEFAULT ''
)
GO
CREATE TABLE TaxType (
taxtKeyintNOT NULL ,
taxtDescriptionvarchar(80)NOT NULL DEFAULT '',
-- 0 = Federal withholding
-- 1 = Federal unemployment
-- 2 = Earned income credit
-- 3 = Social Security
-- 4 = Medicare
-- 5 = State withholding,
-- 6 = State disability
-- 7 = State unemployment
-- 8 = Workers compensation
-- 9 = Local
-- 10 = Other
taxtTypetinyintNOT NULL DEFAULT 10
)
GO
CREATE TABLE PayCheckDeduction (
pcdKeyintNOT NULL,
pcdPayCheckintNOT NULL,
pcdDeductionTypeintNOT NULL,
pcdAmount decimal(19,2)NOT NULL DEFAULT 0
)
GO
CREATE TABLE Pays (
pKey int NOT NULL ,
pPayCheck int NULL,
pPayType int NULL,
pHours decimal(19,2) NOT NULL DEFAULT 0,
pAmount decimal(19,4) NULL
)
GO
INSERT PayType(paytKey,paytDescription,paytType) VALUES(0,'Regular',0)
INSERT PayType(paytKey,paytDescription,paytType) VALUES(1,'Salary',1)
INSERT PayType(paytKey,paytDescription,paytType) VALUES(2,'Overtime',2)
INSERT PayType(paytKey,paytDescription,paytType) VALUES(3,'Doubletime',3)
INSERT PayType(paytKey,paytDescription,paytType)
VALUES(4,'Vacation_Accrual',4)
INSERT DeductionType(dedtKey,dedtDescription) VALUES(0,'401k')
INSERT DeductionType(dedtKey,dedtDescription) VALUES(1,'Employee Medical')
INSERT TaxType(taxtKey,taxtDescription, taxtType) VALUES(0,'FUTA',1)
INSERT TaxType(taxtKey,taxtDescription, taxtType) VALUES(1,'FWH',0)
INSERT TaxType(taxtKey,taxtDescription, taxtType) VALUES(2,'Medicare',4)
INSERT TaxType(taxtKey,taxtDescription, taxtType) VALUES(3,'SOC',3)
INSERT TaxType(taxtKey,taxtDescription, taxtType) VALUES(4,'ORSDI',6)
INSERT TaxType(taxtKey,taxtDescription, taxtType) VALUES(5,'ORSWH',5)
INSERT Pays(pKey,pPayCheck, pPayType, pHours,pAmount)
VALUES(0,1,0,40.00,900.00)
INSERT Pays(pKey,pPayCheck, pPayType, pHours,pAmount)
VALUES(1,1,2,2.00,60.00)
INSERT Pays(pKey,pPayCheck, pPayType, pHours,pAmount)
VALUES(3,1,4,8.00,0.00)
INSERT PayCheckDeduction(pcdKey, pcdPayCheck, pcdDeductionType, pcdAmount)
VALUES(0,1,0,72.00)
INSERT PayCheckDeduction(pcdKey, pcdPayCheck, pcdDeductionType, pcdAmount)
VALUES(0,1,1,25.00)
INSERT PayCheckTax(pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,0,25.00)
INSERT PayCheckTax(pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,1,100.00)
INSERT PayCheckTax(pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,2,35.00)
INSERT PayCheckTax(pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,4,75.00)
INSERT PayCheckTax(pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,5,105.00)
INSERT PayCheck(pcKey, pcPayroll,pcEmployee) VALUES(1,2,3)
================================================
"Tom Moreau" wrote:

> Please post the exact DDL (CREATE TABLE statements) for all of the tables
> involved. It sounds like what you want is a pivot table, which can be
> easily done.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:05C2EB45-F667-4968-858A-D545DB9D8F41@.microsoft.com...
> Has any one had luck writing a query that returns a result as follows:
> CheckID Pay Amt Deduct Amt Tax Amt
> 1 Admin 200.00 401k 70.00 FUTA 13.00
> 1 SAL 1500.00 FICA 25.00
> 1 MED 30.00
> 1 SOC 107.00
> I have three tables (Pay, Dedcut, and Tax) that are connected to a check
> table
>
> Check Table
> pcKey
> pcEmployee
> PayCheckDeduction
> pcdKey
> pcdPayCheck
> pcdDeductionDescription
> pcdAmount
> etc...
> the diffuculty I'm having is getting a horizontal return as describe above
> for two or more tables connected to one table.
>
>
|||Looking at the original post, you have an assortment of results, with an
inconsistent number or columns in each row. What exactly are the business
requirements? Do you want a row for each type of pay - Reg, Overtime, etc.?
Do you want all deductions spread horizontally?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John" <John@.discussions.microsoft.com> wrote in message
news:60E58185-B97D-4891-9ED2-0785A79E51B9@.microsoft.com...
Here you go Tom, I hope this helps
====================
DROP database zzPayroll
GO
CREATE database zzPayroll
GO
USE zzPayroll
GO
CREATE TABLE PayCheck (
pcKey int NOT NULL ,
pcPayroll int NULL,
pcEmployee int NOT NULL
)
GO
CREATE TABLE PayType (
paytKey int NOT NULL,
paytDescription varchar(80) NOT NULL DEFAULT '',
-- 0 = Regular
-- 1 = Salary
-- 2 = Premium 1
-- 3 = Premium 2
-- 4 = Premium 3
-- 5 = Sick pay
-- 6 = Vacation pay
-- 7 = Other pay
-- 8 = Other in/out
-- 9 = Reimbursement
-- 10 = Accrual
paytType tinyint NOT NULL DEFAULT 0
)
GO
CREATE TABLE PayCheckTax (
pctKey int NOT NULL ,
pctPayCheck int NOT NULL,
pctTaxType int NOT NULL,
pctAmount decimal(19,2) NOT NULL DEFAULT 0
)
GO
CREATE TABLE DeductionType (
dedtKey int NOT NULL,
dedtDescription varchar(80) NOT NULL DEFAULT ''
)
GO
CREATE TABLE TaxType (
taxtKey int NOT NULL ,
taxtDescription varchar(80) NOT NULL DEFAULT '',
-- 0 = Federal withholding
-- 1 = Federal unemployment
-- 2 = Earned income credit
-- 3 = Social Security
-- 4 = Medicare
-- 5 = State withholding,
-- 6 = State disability
-- 7 = State unemployment
-- 8 = Workers compensation
-- 9 = Local
-- 10 = Other
taxtType tinyint NOT NULL DEFAULT 10
)
GO
CREATE TABLE PayCheckDeduction (
pcdKey int NOT NULL,
pcdPayCheck int NOT NULL,
pcdDeductionType int NOT NULL,
pcdAmount decimal(19,2) NOT NULL DEFAULT 0
)
GO
CREATE TABLE Pays (
pKey int NOT NULL ,
pPayCheck int NULL,
pPayType int NULL,
pHours decimal(19,2) NOT NULL DEFAULT 0,
pAmount decimal(19,4) NULL
)
GO
INSERT PayType (paytKey,paytDescription,paytType) VALUES(0,'Regular',0)
INSERT PayType (paytKey,paytDescription,paytType) VALUES(1,'Salary',1)
INSERT PayType (paytKey,paytDescription,paytType) VALUES(2,'Overtime',2)
INSERT PayType (paytKey,paytDescription,paytType) VALUES(3,'Doubletime',3)
INSERT PayType (paytKey,paytDescription,paytType)
VALUES(4,'Vacation_Accrual',4)
INSERT DeductionType (dedtKey,dedtDescription) VALUES(0,'401k')
INSERT DeductionType (dedtKey,dedtDescription) VALUES(1,'Employee Medical')
INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(0,'FUTA',1)
INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(1,'FWH',0)
INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(2,'Medicare',4)
INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(3,'SOC',3)
INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(4,'ORSDI',6)
INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(5,'ORSWH',5)
INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
VALUES(0,1,0,40.00,900.00)
INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
VALUES(1,1,2,2.00,60.00)
INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
VALUES(3,1,4,8.00,0.00)
INSERT PayCheckDeduction (pcdKey, pcdPayCheck, pcdDeductionType, pcdAmount)
VALUES(0,1,0,72.00)
INSERT PayCheckDeduction (pcdKey, pcdPayCheck, pcdDeductionType, pcdAmount)
VALUES(0,1,1,25.00)
INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,0,25.00)
INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,1,100.00)
INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,2,35.00)
INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,4,75.00)
INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
VALUES(0,1,5,105.00)
INSERT PayCheck (pcKey, pcPayroll,pcEmployee) VALUES(1,2,3)
================================================
"Tom Moreau" wrote:

> Please post the exact DDL (CREATE TABLE statements) for all of the tables
> involved. It sounds like what you want is a pivot table, which can be
> easily done.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:05C2EB45-F667-4968-858A-D545DB9D8F41@.microsoft.com...
> Has any one had luck writing a query that returns a result as follows:
> CheckID Pay Amt Deduct Amt Tax Amt
> 1 Admin 200.00 401k 70.00 FUTA 13.00
> 1 SAL 1500.00 FICA 25.00
> 1 MED 30.00
> 1 SOC 107.00
> I have three tables (Pay, Dedcut, and Tax) that are connected to a check
> table
>
> Check Table
> pcKey
> pcEmployee
> PayCheckDeduction
> pcdKey
> pcdPayCheck
> pcdDeductionDescription
> pcdAmount
> etc...
> the diffuculty I'm having is getting a horizontal return as describe above
> for two or more tables connected to one table.
>
>
|||I am looking for a way show for a given check all the deductions, Pays, and
Taxes where the Deductions, Pays and Taxes appear in there own columns.
Chk Pay Ded Tax
1 x y z
1 null y z
1 null null z
"Tom Moreau" wrote:

> Looking at the original post, you have an assortment of results, with an
> inconsistent number or columns in each row. What exactly are the business
> requirements? Do you want a row for each type of pay - Reg, Overtime, etc.?
> Do you want all deductions spread horizontally?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:60E58185-B97D-4891-9ED2-0785A79E51B9@.microsoft.com...
> Here you go Tom, I hope this helps
> ====================
> DROP database zzPayroll
> GO
> CREATE database zzPayroll
> GO
> USE zzPayroll
> GO
> CREATE TABLE PayCheck (
> pcKey int NOT NULL ,
> pcPayroll int NULL,
> pcEmployee int NOT NULL
> )
> GO
> CREATE TABLE PayType (
> paytKey int NOT NULL,
> paytDescription varchar(80) NOT NULL DEFAULT '',
> -- 0 = Regular
> -- 1 = Salary
> -- 2 = Premium 1
> -- 3 = Premium 2
> -- 4 = Premium 3
> -- 5 = Sick pay
> -- 6 = Vacation pay
> -- 7 = Other pay
> -- 8 = Other in/out
> -- 9 = Reimbursement
> -- 10 = Accrual
> paytType tinyint NOT NULL DEFAULT 0
> )
> GO
> CREATE TABLE PayCheckTax (
> pctKey int NOT NULL ,
> pctPayCheck int NOT NULL,
> pctTaxType int NOT NULL,
> pctAmount decimal(19,2) NOT NULL DEFAULT 0
> )
> GO
> CREATE TABLE DeductionType (
> dedtKey int NOT NULL,
> dedtDescription varchar(80) NOT NULL DEFAULT ''
> )
> GO
> CREATE TABLE TaxType (
> taxtKey int NOT NULL ,
> taxtDescription varchar(80) NOT NULL DEFAULT '',
> -- 0 = Federal withholding
> -- 1 = Federal unemployment
> -- 2 = Earned income credit
> -- 3 = Social Security
> -- 4 = Medicare
> -- 5 = State withholding,
> -- 6 = State disability
> -- 7 = State unemployment
> -- 8 = Workers compensation
> -- 9 = Local
> -- 10 = Other
> taxtType tinyint NOT NULL DEFAULT 10
> )
> GO
> CREATE TABLE PayCheckDeduction (
> pcdKey int NOT NULL,
> pcdPayCheck int NOT NULL,
> pcdDeductionType int NOT NULL,
> pcdAmount decimal(19,2) NOT NULL DEFAULT 0
> )
> GO
> CREATE TABLE Pays (
> pKey int NOT NULL ,
> pPayCheck int NULL,
> pPayType int NULL,
> pHours decimal(19,2) NOT NULL DEFAULT 0,
> pAmount decimal(19,4) NULL
> )
> GO
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(0,'Regular',0)
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(1,'Salary',1)
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(2,'Overtime',2)
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(3,'Doubletime',3)
> INSERT PayType (paytKey,paytDescription,paytType)
> VALUES(4,'Vacation_Accrual',4)
> INSERT DeductionType (dedtKey,dedtDescription) VALUES(0,'401k')
> INSERT DeductionType (dedtKey,dedtDescription) VALUES(1,'Employee Medical')
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(0,'FUTA',1)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(1,'FWH',0)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(2,'Medicare',4)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(3,'SOC',3)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(4,'ORSDI',6)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(5,'ORSWH',5)
> INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
> VALUES(0,1,0,40.00,900.00)
> INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
> VALUES(1,1,2,2.00,60.00)
> INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
> VALUES(3,1,4,8.00,0.00)
> INSERT PayCheckDeduction (pcdKey, pcdPayCheck, pcdDeductionType, pcdAmount)
> VALUES(0,1,0,72.00)
> INSERT PayCheckDeduction (pcdKey, pcdPayCheck, pcdDeductionType, pcdAmount)
> VALUES(0,1,1,25.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,0,25.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,1,100.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,2,35.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,4,75.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,5,105.00)
> INSERT PayCheck (pcKey, pcPayroll,pcEmployee) VALUES(1,2,3)
> ================================================
> "Tom Moreau" wrote:
>
>
|||So, IOW, there is no direct correlation between a given deduction and a
given pay. Rather the correlation is between the deduction and the check
only. Is that right?
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John" <John@.discussions.microsoft.com> wrote in message
news:8ACE7E95-F5E3-4A74-8813-BEE2C9D2DDEF@.microsoft.com...
I am looking for a way show for a given check all the deductions, Pays, and
Taxes where the Deductions, Pays and Taxes appear in there own columns.
Chk Pay Ded Tax
1 x y z
1 null y z
1 null null z
"Tom Moreau" wrote:

> Looking at the original post, you have an assortment of results, with an
> inconsistent number or columns in each row. What exactly are the business
> requirements? Do you want a row for each type of pay - Reg, Overtime,
> etc.?
> Do you want all deductions spread horizontally?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:60E58185-B97D-4891-9ED2-0785A79E51B9@.microsoft.com...
> Here you go Tom, I hope this helps
> ====================
> DROP database zzPayroll
> GO
> CREATE database zzPayroll
> GO
> USE zzPayroll
> GO
> CREATE TABLE PayCheck (
> pcKey int NOT NULL ,
> pcPayroll int NULL,
> pcEmployee int NOT NULL
> )
> GO
> CREATE TABLE PayType (
> paytKey int NOT NULL,
> paytDescription varchar(80) NOT NULL DEFAULT '',
> -- 0 = Regular
> -- 1 = Salary
> -- 2 = Premium 1
> -- 3 = Premium 2
> -- 4 = Premium 3
> -- 5 = Sick pay
> -- 6 = Vacation pay
> -- 7 = Other pay
> -- 8 = Other in/out
> -- 9 = Reimbursement
> -- 10 = Accrual
> paytType tinyint NOT NULL DEFAULT 0
> )
> GO
> CREATE TABLE PayCheckTax (
> pctKey int NOT NULL ,
> pctPayCheck int NOT NULL,
> pctTaxType int NOT NULL,
> pctAmount decimal(19,2) NOT NULL DEFAULT 0
> )
> GO
> CREATE TABLE DeductionType (
> dedtKey int NOT NULL,
> dedtDescription varchar(80) NOT NULL DEFAULT ''
> )
> GO
> CREATE TABLE TaxType (
> taxtKey int NOT NULL ,
> taxtDescription varchar(80) NOT NULL DEFAULT '',
> -- 0 = Federal withholding
> -- 1 = Federal unemployment
> -- 2 = Earned income credit
> -- 3 = Social Security
> -- 4 = Medicare
> -- 5 = State withholding,
> -- 6 = State disability
> -- 7 = State unemployment
> -- 8 = Workers compensation
> -- 9 = Local
> -- 10 = Other
> taxtType tinyint NOT NULL DEFAULT 10
> )
> GO
> CREATE TABLE PayCheckDeduction (
> pcdKey int NOT NULL,
> pcdPayCheck int NOT NULL,
> pcdDeductionType int NOT NULL,
> pcdAmount decimal(19,2) NOT NULL DEFAULT 0
> )
> GO
> CREATE TABLE Pays (
> pKey int NOT NULL ,
> pPayCheck int NULL,
> pPayType int NULL,
> pHours decimal(19,2) NOT NULL DEFAULT 0,
> pAmount decimal(19,4) NULL
> )
> GO
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(0,'Regular',0)
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(1,'Salary',1)
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(2,'Overtime',2)
> INSERT PayType (paytKey,paytDescription,paytType) VALUES(3,'Doubletime',3)
> INSERT PayType (paytKey,paytDescription,paytType)
> VALUES(4,'Vacation_Accrual',4)
> INSERT DeductionType (dedtKey,dedtDescription) VALUES(0,'401k')
> INSERT DeductionType (dedtKey,dedtDescription) VALUES(1,'Employee
> Medical')
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(0,'FUTA',1)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(1,'FWH',0)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(2,'Medicare',4)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(3,'SOC',3)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(4,'ORSDI',6)
> INSERT TaxType (taxtKey,taxtDescription, taxtType) VALUES(5,'ORSWH',5)
> INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
> VALUES(0,1,0,40.00,900.00)
> INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
> VALUES(1,1,2,2.00,60.00)
> INSERT Pays (pKey,pPayCheck, pPayType, pHours,pAmount)
> VALUES(3,1,4,8.00,0.00)
> INSERT PayCheckDeduction (pcdKey, pcdPayCheck, pcdDeductionType,
> pcdAmount)
> VALUES(0,1,0,72.00)
> INSERT PayCheckDeduction (pcdKey, pcdPayCheck, pcdDeductionType,
> pcdAmount)
> VALUES(0,1,1,25.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,0,25.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,1,100.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,2,35.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,4,75.00)
> INSERT PayCheckTax (pctKey, pctPayCheck, pctTaxType, pctAmount)
> VALUES(0,1,5,105.00)
> INSERT PayCheck (pcKey, pcPayroll,pcEmployee) VALUES(1,2,3)
> ================================================
> "Tom Moreau" wrote:
>
>
|||Yes that is correct.
"Tom Moreau" wrote:

> So, IOW, there is no direct correlation between a given deduction and a
> given pay. Rather the correlation is between the deduction and the check
> only. Is that right?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:8ACE7E95-F5E3-4A74-8813-BEE2C9D2DDEF@.microsoft.com...
> I am looking for a way show for a given check all the deductions, Pays, and
> Taxes where the Deductions, Pays and Taxes appear in there own columns.
> Chk Pay Ded Tax
> 1 x y z
> 1 null y z
> 1 null null z
>
> "Tom Moreau" wrote:
>
>
|||Well, that is an odd requirement. The following may suffice:
select
pc.pcKey
, x.Type
, p.pAmount Pay
, pcd.pcdAmount Deduction
, pct.pctAmount Tax
from
(
select 0 union all
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 5 union all
select 6 union all
select 7 union all
select 8 union all
select 9 union all
select 10
) as x (Type)
cross
join
PayCheck pc
left
join
Pays p on p.pPayType = x.Type
and p.pPayCheck = pc.pcKey
left
join PayCheckDeduction pcd on pcd.pcdPayCheck = pc.pcKey
and pcd.pcdDeductionType = x.Type
left
join PayCheckTax pct on pct.pctPayCheck = pc.pcKey
and pct.pctTaxType = x.Type
where not
(
p.pAmount is null
and pcd.pcdAmount is null
and pct.pctAmount is null
)
Note that you will get gaps, i.e. there may be nulls interspersed within a
column.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John" <John@.discussions.microsoft.com> wrote in message
news:BB0D6BE3-1081-4381-9473-345B93DFA54B@.microsoft.com...
Yes that is correct.
"Tom Moreau" wrote:

> So, IOW, there is no direct correlation between a given deduction and a
> given pay. Rather the correlation is between the deduction and the check
> only. Is that right?
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:8ACE7E95-F5E3-4A74-8813-BEE2C9D2DDEF@.microsoft.com...
> I am looking for a way show for a given check all the deductions, Pays,
> and
> Taxes where the Deductions, Pays and Taxes appear in there own columns.
> Chk Pay Ded Tax
> 1 x y z
> 1 null y z
> 1 null null z
>
> "Tom Moreau" wrote:
>
>
|||Thank you very much!
While working through this I found something similar using the union all but
this is more straight forward.
Thanks for you time on this!
"Tom Moreau" wrote:

> Well, that is an odd requirement. The following may suffice:
> select
> pc.pcKey
> , x.Type
> , p.pAmount Pay
> , pcd.pcdAmount Deduction
> , pct.pctAmount Tax
> from
> (
> select 0 union all
> select 1 union all
> select 2 union all
> select 3 union all
> select 4 union all
> select 5 union all
> select 6 union all
> select 7 union all
> select 8 union all
> select 9 union all
> select 10
> ) as x (Type)
> cross
> join
> PayCheck pc
> left
> join
> Pays p on p.pPayType = x.Type
> and p.pPayCheck = pc.pcKey
> left
> join PayCheckDeduction pcd on pcd.pcdPayCheck = pc.pcKey
> and pcd.pcdDeductionType = x.Type
> left
> join PayCheckTax pct on pct.pctPayCheck = pc.pcKey
> and pct.pctTaxType = x.Type
> where not
> (
> p.pAmount is null
> and pcd.pcdAmount is null
> and pct.pctAmount is null
> )
> Note that you will get gaps, i.e. there may be nulls interspersed within a
> column.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:BB0D6BE3-1081-4381-9473-345B93DFA54B@.microsoft.com...
> Yes that is correct.
> "Tom Moreau" wrote:
>
>
|||My pleasure. Speaking of UNION ALL, have you considered FOR XML EXPLICIT?
select
1 as Tag
, NULL as Parent
, pcKey as [Check!1!CheckID]
, NULL as [Pay!2!Pay]
, NULL as [Deduction!3!Deduction]
, NULL as [Tax!4!Tax]
from
PayCheck
union all
select
2
, 1
, pPayCheck
, pAmount
, NULL
, NULL
from
Pays
union all
select
3
, 1
, pcdPayCheck
, NULL
, pcdAmount
, NULL
from
PayCheckDeduction
union all
select
4
, 1
, pctPayCheck
, NULL
, NULL
, pctAmount Tax
from
PayCheckTax
order by
[Check!1!CheckID]
for xml explicit
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John" <John@.discussions.microsoft.com> wrote in message
news:B68BD197-6E5A-4A8A-8A98-A81CBF848F46@.microsoft.com...
Thank you very much!
While working through this I found something similar using the union all but
this is more straight forward.
Thanks for you time on this!
"Tom Moreau" wrote:

> Well, that is an odd requirement. The following may suffice:
> select
> pc.pcKey
> , x.Type
> , p.pAmount Pay
> , pcd.pcdAmount Deduction
> , pct.pctAmount Tax
> from
> (
> select 0 union all
> select 1 union all
> select 2 union all
> select 3 union all
> select 4 union all
> select 5 union all
> select 6 union all
> select 7 union all
> select 8 union all
> select 9 union all
> select 10
> ) as x (Type)
> cross
> join
> PayCheck pc
> left
> join
> Pays p on p.pPayType = x.Type
> and p.pPayCheck = pc.pcKey
> left
> join PayCheckDeduction pcd on pcd.pcdPayCheck = pc.pcKey
> and pcd.pcdDeductionType = x.Type
> left
> join PayCheckTax pct on pct.pctPayCheck = pc.pcKey
> and pct.pctTaxType = x.Type
> where not
> (
> p.pAmount is null
> and pcd.pcdAmount is null
> and pct.pctAmount is null
> )
> Note that you will get gaps, i.e. there may be nulls interspersed within a
> column.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "John" <John@.discussions.microsoft.com> wrote in message
> news:BB0D6BE3-1081-4381-9473-345B93DFA54B@.microsoft.com...
> Yes that is correct.
> "Tom Moreau" wrote:
>
>
sql

Return 2 entries per group based on the two lowest values in group

Below is a query that I need to modify so that it returns just the 2
MastNUMs, Stores, and Distances where the Distances are the two lowest for a
mastnum. So the results would be more like this
MastNUM Store Distance
-- -- --
000000067 76 3.358330
000000067 70 7.082444
000000068 76 4.447685
000000068 70 5.516853
000000069 70 3.836682
000000069 76 6.331691
000000070 76 3.729323
SELECT TOP 15 MastNUM, Store, Distance
FROM DistTest
GROUP BY MastNUM, Store, Distance
ORDER BY MastNUM, Distance
MastNUM Store Distance
-- -- --
000000067 76 3.358330
000000067 70 7.082444
000000067 69 8.112116
000000067 112 19.924702
000000068 76 4.447685
000000068 70 5.516853
000000068 69 6.874022
000000068 112 18.622366
000000068 71 19.396528
000000069 70 3.836682
000000069 76 6.331691
000000069 69 8.924779
000000069 112 16.709897
000000069 71 17.462224
000000070 76 3.729323
--
KoryI can get the lowest for each with the following but how can I get the
lowest 2?
SELECT TOP 15 t1.MastNUM, t1.Store, t1.Distance
FROM DistTest t1,
( SELECT MastNUM, MIN(Distance) AS MinDist
FROM DistTest
GROUP BY MastNum ) as Dmin
WHERE t1.MastNum = Dmin.MastNUm AND t1.Distance = Dmin.MinDist
go
MastNUM Store Distance
-- -- --
000000067 76 3.358330
000000068 76 4.447685
000000069 70 3.836682
000000070 76 3.729323
000000071 76 4.046238
000000072 70 3.928709
000000073 76 4.663551
000000074 76 3.206388
000000076 76 4.745636
000000077 76 5.338428
000000078 70 5.121837
000000079 70 4.580213
000000080 70 4.338181
000000081 76 4.069455
000000082 76 4.465975
"Kory Yingling" <Mister2zx3@.yahoo.com> wrote in message
news:%23OJC2n8WDHA.536@.TK2MSFTNGP10.phx.gbl...
> Below is a query that I need to modify so that it returns just the 2
> MastNUMs, Stores, and Distances where the Distances are the two lowest for
a
> mastnum. So the results would be more like this
> MastNUM Store Distance
> -- -- --
> 000000067 76 3.358330
> 000000067 70 7.082444
> 000000068 76 4.447685
> 000000068 70 5.516853
> 000000069 70 3.836682
> 000000069 76 6.331691
> 000000070 76 3.729323
> SELECT TOP 15 MastNUM, Store, Distance
> FROM DistTest
> GROUP BY MastNUM, Store, Distance
> ORDER BY MastNUM, Distance
> MastNUM Store Distance
> -- -- --
> 000000067 76 3.358330
> 000000067 70 7.082444
> 000000067 69 8.112116
> 000000067 112 19.924702
> 000000068 76 4.447685
> 000000068 70 5.516853
> 000000068 69 6.874022
> 000000068 112 18.622366
> 000000068 71 19.396528
> 000000069 70 3.836682
> 000000069 76 6.331691
> 000000069 69 8.924779
> 000000069 112 16.709897
> 000000069 71 17.462224
> 000000070 76 3.729323
> --
> Kory
>|||I think I have found a way to get the lowest and 2nd lowest, but I hope
their is a better way than the following..
SELECT TOP 15 t1.MastNUM, t1.Store, t1.Distance
FROM DistTest t1,
( SELECT MastNUM, MIN(Distance) AS MinDist
FROM DistTest
GROUP BY MastNum ) as Dmin
WHERE t1.MastNum = Dmin.MastNUm AND t1.Distance = Dmin.MinDist
go
SELECT TOP 15 t1.MastNUM, t1.Store, t1.Distance
FROM DistTest t1,
(
SELECT MastNUM, MIN(Distance) AS MinDist
FROM DistTest t2
WHERE Distance > (
SELECT MIN(Distance)
FROM DistTest d2
WHERE t2.MastNum = d2.MastNum
GROUP BY MastNum
)
GROUP BY MastNum
) as Dmin
WHERE t1.MastNum = Dmin.MastNUm AND t1.Distance = Dmin.MinDist
order by T1.mASTnUM
go
MastNUM Store Distance
-- -- --
000000067 76 3.358330
000000068 76 4.447685
000000069 70 3.836682
000000070 76 3.729323
000000071 76 4.046238
000000072 70 3.928709
000000073 76 4.663551
000000074 76 3.206388
000000076 76 4.745636
000000077 76 5.338428
000000078 70 5.121837
000000079 70 4.580213
000000080 70 4.338181
000000081 76 4.069455
000000082 76 4.465975
15 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
[Executed: 8/6/03 12:05:51 AM CDT ] [Execution: 16/ms]
MastNUM Store Distance
-- -- --
000000067 70 7.082444
000000068 70 5.516853
000000069 76 6.331691
000000070 70 6.957127
000000071 70 5.944537
000000072 76 6.277529
000000073 70 5.487792
000000074 70 7.193161
000000076 70 5.201532
000000077 70 5.412091
000000078 76 5.295454
000000079 76 5.395715
000000080 76 5.950446
000000081 70 5.920339
000000082 70 5.529576
15 record(s) selected [Fetch MetaData: 0/ms] [Fetch Data: 0/ms]
[Executed: 8/6/03 12:05:52 AM CDT ] [Execution: 93/ms]
"Kory Yingling" <Mister2zx3@.yahoo.com> wrote in message
news:uVgDQB9WDHA.2268@.TK2MSFTNGP11.phx.gbl...
> I can get the lowest for each with the following but how can I get the
> lowest 2?
> SELECT TOP 15 t1.MastNUM, t1.Store, t1.Distance
> FROM DistTest t1,
> ( SELECT MastNUM, MIN(Distance) AS MinDist
> FROM DistTest
> GROUP BY MastNum ) as Dmin
> WHERE t1.MastNum = Dmin.MastNUm AND t1.Distance = Dmin.MinDist
> go
> MastNUM Store Distance
> -- -- --
> 000000067 76 3.358330
> 000000068 76 4.447685
> 000000069 70 3.836682
> 000000070 76 3.729323
> 000000071 76 4.046238
> 000000072 70 3.928709
> 000000073 76 4.663551
> 000000074 76 3.206388
> 000000076 76 4.745636
> 000000077 76 5.338428
> 000000078 70 5.121837
> 000000079 70 4.580213
> 000000080 70 4.338181
> 000000081 76 4.069455
> 000000082 76 4.465975
>
> "Kory Yingling" <Mister2zx3@.yahoo.com> wrote in message
> news:%23OJC2n8WDHA.536@.TK2MSFTNGP10.phx.gbl...
> > Below is a query that I need to modify so that it returns just the 2
> > MastNUMs, Stores, and Distances where the Distances are the two lowest
for
> a
> > mastnum. So the results would be more like this
> > MastNUM Store Distance
> > -- -- --
> > 000000067 76 3.358330
> > 000000067 70 7.082444
> > 000000068 76 4.447685
> > 000000068 70 5.516853
> > 000000069 70 3.836682
> > 000000069 76 6.331691
> > 000000070 76 3.729323
> >
> > SELECT TOP 15 MastNUM, Store, Distance
> > FROM DistTest
> > GROUP BY MastNUM, Store, Distance
> > ORDER BY MastNUM, Distance
> >
> > MastNUM Store Distance
> > -- -- --
> > 000000067 76 3.358330
> > 000000067 70 7.082444
> > 000000067 69 8.112116
> > 000000067 112 19.924702
> > 000000068 76 4.447685
> > 000000068 70 5.516853
> > 000000068 69 6.874022
> > 000000068 112 18.622366
> > 000000068 71 19.396528
> > 000000069 70 3.836682
> > 000000069 76 6.331691
> > 000000069 69 8.924779
> > 000000069 112 16.709897
> > 000000069 71 17.462224
> > 000000070 76 3.729323
> >
> > --
> >
> > Kory
> >
> >
>|||The initial step creating this list is as follows:
SELECT g.MASTNUM, s.STORE, master.dbo.DistanceMiles( g.lat, g.long, s.LAT,
s.LONG ) AS Distance
INTO DistTest
FROM geomailing g, geoStore s
WHERE master.dbo.DistanceMiles( g.lat, g.long, s.LAT, s.LONG ) <= 20
GROUP BY g.Mastnum, s.Store, master.dbo.DistanceMiles( g.lat, g.long, s.LAT,
s.LONG )
ORDER BY g.Mastnum, master.dbo.DistanceMiles( g.lat, g.long, s.LAT, s.LONG )
Can anyone suggest a way that it only inserts into this table the 2 lowest
Distances ( master.dbo.DistanceMiles( g.lat, g.long, s.LAT, s.LONG ) ) ? So
as to avoid having to pull out the two lowest distances later?
Thanks.
Kory wrote in message news:%23OJC2n8WDHA.536@.TK2MSFTNGP10.phx.gbl...
> Below is a query that I need to modify so that it returns just the 2
> MastNUMs, Stores, and Distances where the Distances are the two lowest for
a
> mastnum. So the results would be more like this
> MastNUM Store Distance
> -- -- --
> 000000067 76 3.358330
> 000000067 70 7.082444
> 000000068 76 4.447685
> 000000068 70 5.516853
> 000000069 70 3.836682
> 000000069 76 6.331691
> 000000070 76 3.729323
> SELECT TOP 15 MastNUM, Store, Distance
> FROM DistTest
> GROUP BY MastNUM, Store, Distance
> ORDER BY MastNUM, Distance
> MastNUM Store Distance
> -- -- --
> 000000067 76 3.358330
> 000000067 70 7.082444
> 000000067 69 8.112116
> 000000067 112 19.924702
> 000000068 76 4.447685
> 000000068 70 5.516853
> 000000068 69 6.874022
> 000000068 112 18.622366
> 000000068 71 19.396528
> 000000069 70 3.836682
> 000000069 76 6.331691
> 000000069 69 8.924779
> 000000069 112 16.709897
> 000000069 71 17.462224
> 000000070 76 3.729323
> --
> Kory
>|||Does anyone have any suggestions on how to get just the 2 lowest values
grouped?|||I would imagine a lot of people have a lot of ideas but with what you have
given us it would be stabbing in the dark.
1. Can you post a simple table structure
2. Post sample data to enter in
3. Tell us what you expect to see as the end result.
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Kory Yingling" <kory@.removeme-mlsc.com> wrote in message
news:OPqoHUDXDHA.208@.tk2msftngp13.phx.gbl...
> Does anyone have any suggestions on how to get just the 2 lowest values
> grouped?
>

Monday, March 26, 2012

Return 0 if null

Hi all,
I have got a query that returns values based on a date range and grouped by
week. I need to return a value of 0 if there are no entries for that week. At
the moment it just skips that week all together.
Any ideas?
Thanks
Without more details, I'd suggest lookign into IsNull( variable, 0 ) or a
left outer join if you're using multiple tables and a lack of entries for
that week table is eliminating the week row. But to give a specific answer,
we'd need more details.
-Paul Nielsen, SQL Server MVP
SQL Server 2000 Bible, Wiley Press
"Andrew Jurgens" <AndrewJurgens@.discussions.microsoft.com> wrote in message
news:EBB765D2-2A3D-4151-A241-0B52A9B6E66C@.microsoft.com...
> Hi all,
> I have got a query that returns values based on a date range and grouped
> by
> week. I need to return a value of 0 if there are no entries for that week.
> At
> the moment it just skips that week all together.
> Any ideas?
> Thanks
|||Hi Paul,
My current query is as follows.
SELECT Format([Counting Type_QRY].Date,'ww') AS Expr1, [Counting
Type_QRY].Branch, [Counting Type_QRY].Type, Sum([Counting
Type_QRY].CountOfType) AS SumOfCountOfType
FROM (Branch INNER JOIN Type ON Branch.ID = Type.ID) INNER JOIN [Counting
Type_QRY] ON Branch.ID = [Counting Type_QRY].Branch
GROUP BY Format([Counting Type_QRY].Date,'ww'), [Counting Type_QRY].Branch,
[Counting Type_QRY].Type;
This works great counting my entries and putting then grouping by week.
Trouble is if there is no info for a week it skipps that week.
34 = 4
35 = 2
37 = 7
I need it to return
34 = 4
35 = 2
36 = 0
37 = 7
I am fairly new to this so please excuse the query if it is not great. Just
trying to grow my skills in the real world.
Thanks
"Paul Nielsen" wrote:

> Without more details, I'd suggest lookign into IsNull( variable, 0 ) or a
> left outer join if you're using multiple tables and a lack of entries for
> that week table is eliminating the week row. But to give a specific answer,
> we'd need more details.
> --
> -Paul Nielsen, SQL Server MVP
> SQL Server 2000 Bible, Wiley Press
>
> "Andrew Jurgens" <AndrewJurgens@.discussions.microsoft.com> wrote in message
> news:EBB765D2-2A3D-4151-A241-0B52A9B6E66C@.microsoft.com...
>
>
|||Andrew Jurgens wrote:
> Hi Paul,
> My current query is as follows.
> SELECT Format([Counting Type_QRY].Date,'ww') AS Expr1, [Counting
> Type_QRY].Branch, [Counting Type_QRY].Type, Sum([Counting
> Type_QRY].CountOfType) AS SumOfCountOfType
> FROM (Branch INNER JOIN Type ON Branch.ID = Type.ID) INNER JOIN
> [Counting Type_QRY] ON Branch.ID = [Counting Type_QRY].Branch
> GROUP BY Format([Counting Type_QRY].Date,'ww'), [Counting
> Type_QRY].Branch, [Counting Type_QRY].Type;
> This works great counting my entries and putting then grouping by
> week. Trouble is if there is no info for a week it skipps that week.
> 34 = 4
> 35 = 2
> 37 = 7
> I need it to return
> 34 = 4
> 35 = 2
> 36 = 0
> 37 = 7
> I am fairly new to this so please excuse the query if it is not
> great. Just trying to grow my skills in the real world.
> Thanks
Sounds like you need an OUTER JOIN. I don't know your data, so use an
outer join against the table that may not have a foreign key
relationship. If you wanted all Accounts from the accounts table even if
some of the accounts didn't have a comment in the comments table, you
would:
From Accounts Outer Join Comments on Accounts.id = Comments.id
David Gugick
Imceda Software
www.imceda.com
|||Thanks David,
My problem is that I am querying a date range but there may not be entries
for all dates within that range in the table. I still need to return all
dates even if there is no data. Hence my previous
This works great counting my entries and putting then grouping by[vbcol=seagreen]
I really appreciate your input. Have been stuck for a bit and need to get
out of this hole!
Thanks.
"David Gugick" wrote:

> Andrew Jurgens wrote:
>
> Sounds like you need an OUTER JOIN. I don't know your data, so use an
> outer join against the table that may not have a foreign key
> relationship. If you wanted all Accounts from the accounts table even if
> some of the accounts didn't have a comment in the comments table, you
> would:
> From Accounts Outer Join Comments on Accounts.id = Comments.id
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>
|||On Thu, 14 Oct 2004 01:59:20 -0700, Andrew Jurgens wrote:

>Thanks David,
>My problem is that I am querying a date range but there may not be entries
>for all dates within that range in the table. I still need to return all
>dates even if there is no data. Hence my previous
Hi Andrew,
Looks like you need a calendar table.
See http://www.aspfaq.com/show.asp?id=2516.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Friday, March 23, 2012

Retrieving time zone adjustment

I am trying to use sp_help_targetserver to retrieve the time_zone_adjustment
from msdb on the local server. "EXEC sp_help_targetserver" returns the field
s
with no data. "EXEC sp_help_targetserver 'servername'" returns an error "The
specified @.server_name ('servername') does not exist.". I tried adding the
server group and the locsl server name by using sp_add_targetservergroup
(which itself said it was successful in both cases), but I get the same
results from sp_help_targetserver. What's am I missing?Hi
sp_help_targetserver is located in msdb and is a SQL Server Agent SP, used
for Master/Target Jobs.
It is not an information SP that can be used in the way you want to.
Regards
Mike
"Lauren" wrote:

> I am trying to use sp_help_targetserver to retrieve the time_zone_adjustme
nt
> from msdb on the local server. "EXEC sp_help_targetserver" returns the fie
lds
> with no data. "EXEC sp_help_targetserver 'servername'" returns an error "T
he
> specified @.server_name ('servername') does not exist.". I tried adding th
e
> server group and the locsl server name by using sp_add_targetservergroup
> (which itself said it was successful in both cases), but I get the same
> results from sp_help_targetserver. What's am I missing?
>|||Lauren wrote:
> I am trying to use sp_help_targetserver to retrieve the
> time_zone_adjustment from msdb on the local server. "EXEC
> sp_help_targetserver" returns the fields with no data. "EXEC
> sp_help_targetserver 'servername'" returns an error "The specified
> @.server_name ('servername') does not exist.". I tried adding the
> server group and the locsl server name by using
> sp_add_targetservergroup (which itself said it was successful in both
> cases), but I get the same results from sp_help_targetserver. What's
> am I missing?
You can probably get the time zone adjustment using:
select DATEDIFF(n, GETUTCDATE(), GETDATE())
David Gugick
Imceda Software
www.imceda.com|||Thanks. The getutcdate will get me what I need.
"David Gugick" wrote:

> Lauren wrote:
> You can probably get the time zone adjustment using:
> select DATEDIFF(n, GETUTCDATE(), GETDATE())
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>sql

Wednesday, March 21, 2012

Retrieving Return value from stored procedure declaratively

Hi.

I have a stored procedure "sp1" which returns a value (with the sql statement Return @.ReturnValue).

Is it possible for my asp.net page to retrieve this return value, and to do it declaratively (meaning without writing code to connect to the database in the code behind). If it is possible to do it like this please tell me how, and if not please tell me how to do it with code.

Thanks in advance .

i do not know what will you sp return but i suppose that it is and INT

so you write this way;

int retrunvalue=sqlcommad.excutenonequery();

so the returned value will be passed to you int.

hope this will help

|||

this is sample code, it can help you:

Here is a sample sproc that populates output parameters
from the Northwind Products table:

CREATE PROCEDURE CustOrderOne
@.CustomerID nchar(5),
@.ProductName varchar(50) output,
@.Quantity int output

AS
SELECT TOP 1 @.ProductName=PRODUCTNAME, @.Quantity =quantity
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @.CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID

And here is an example of some C# code to return and display the output parameters:

using System;
using System.Data;
using System.Data.SqlClient;
namespace OutPutParms
{
class OutputParams
{
[STAThread]
static void Main(string[] args)
{
using(SqlConnection cn = new SqlConnection("server=(local);Database=Northwind;user id=sa;password=;"))
{
SqlCommand cmd = new SqlCommand("CustOrderOne", cn);
cmd.CommandType=CommandType.StoredProcedure ;
SqlParameter parm=new SqlParameter("@.CustomerID",SqlDbType.NChar) ;
parm.Value="ALFKI";
parm.Direction =ParameterDirection.Input ;
cmd.Parameters.Add(parm);
SqlParameter parm2=new SqlParameter("@.ProductName",SqlDbType.VarChar);
parm2.Size=50;
parm2.Direction=ParameterDirection.Output;
cmd.Parameters.Add(parm2);
SqlParameter parm3=new SqlParameter("@.Quantity",SqlDbType.Int);
parm3.Direction=ParameterDirection.Output;
cmd.Parameters.Add(parm3);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
Console.WriteLine(cmd.Parameters["@.ProductName"].Value);
Console.WriteLine(cmd.Parameters["@.Quantity"].Value.ToString());
Console.ReadLine();
}
}
}
}

|||

The above 2 replies does not actually get the return value, which is a special parameter.

The first reply returns the row affected count and the second reply just gets the value out output parameters.

I am afraid I do not know how to retrieve the return value declaratively using controls like object data sources.

However of you are familiar with using SqlCommands then the following code shows you how to get the return values from stored procedures assuming your stored procedure is returning values which is different to result sets, row counts, and output parameters.

SqlCommand cmd =new SqlCommand("this is the query", connection);//create a parameter for the return valueSqlParameter param =new SqlParameter();param.Direction = ParameterDirection.ReturnValue;param.ParameterName ="returnValue";//add to parameter to collectioncmd.Parameters.Add(param);//execute commandcmd.ExecuteNonQuery();//get the return valueint retVal =int.Parse(cmd.Parameters["returnValue"].Value.ToString);

Retrieving nt_username from Sysprocesses

Hi,
We have a stored procedure that returns the nt_username, amongst other
columns, from master.sysprocesses. This works fine under SQL2000 SP2 under
W2K SP4. We are testing W2K3 and find that this information is missing now.
We are testing with SQL2000 SP3 build 818 on W2K3.
Do we need to authorize something in W2K3?
Thanks
Chris Wood
Alberta Department of Energy
CANADATry:
select suser_sname(sid) from sysprocesses
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:Odpj8ryQEHA.3140@.TK2MSFTNGP11.phx.gbl...
Hi,
We have a stored procedure that returns the nt_username, amongst other
columns, from master.sysprocesses. This works fine under SQL2000 SP2 under
W2K SP4. We are testing W2K3 and find that this information is missing now.
We are testing with SQL2000 SP3 build 818 on W2K3.
Do we need to authorize something in W2K3?
Thanks
Chris Wood
Alberta Department of Energy
CANADA|||Tom,
Has this behaviour happened in SQL2000 SP3 or W2K3?
Thanks
Chris
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OiV0iyyQEHA.3580@.TK2MSFTNGP11.phx.gbl...
> Try:
> select suser_sname(sid) from sysprocesses
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:Odpj8ryQEHA.3140@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We have a stored procedure that returns the nt_username, amongst other
> columns, from master.sysprocesses. This works fine under SQL2000 SP2 under
> W2K SP4. We are testing W2K3 and find that this information is missing
now.
> We are testing with SQL2000 SP3 build 818 on W2K3.
> Do we need to authorize something in W2K3?
> Thanks
> Chris Wood
> Alberta Department of Energy
> CANADA
>|||Chris,
I have a SQL Server 2000 server with build 818 running on both Windows 2000
and Windows 2003 and the nt_username is present in the output of select *
from sysprocesses onboth .
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Rand,
What are the Client network settings on your workstation and the Server
network settings on the server please?
Thanks
Chris
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:RqUFvYzQEHA.1516@.cpmsftngxa10.phx.gbl...
> Chris,
> I have a SQL Server 2000 server with build 818 running on both Windows
2000
> and Windows 2003 and the nt_username is present in the output of select *
> from sysprocesses onboth .
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>|||I have SQL 2000 under Windows 2003 and only some users show the nt_username
in sysprocesses.
All of the 2000 Clients do (9 of 9) only one of the XP clients do ( 1 of 15
). Application using BDE/DBNETLIB to connect to server.
Tim S

Retrieving nt_username from Sysprocesses

Hi,
We have a stored procedure that returns the nt_username, amongst other
columns, from master.sysprocesses. This works fine under SQL2000 SP2 under
W2K SP4. We are testing W2K3 and find that this information is missing now.
We are testing with SQL2000 SP3 build 818 on W2K3.
Do we need to authorize something in W2K3?
Thanks
Chris Wood
Alberta Department of Energy
CANADATry:
select suser_sname(sid) from sysprocesses
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:Odpj8ryQEHA.3140@.TK2MSFTNGP11.phx.gbl...
Hi,
We have a stored procedure that returns the nt_username, amongst other
columns, from master.sysprocesses. This works fine under SQL2000 SP2 under
W2K SP4. We are testing W2K3 and find that this information is missing now.
We are testing with SQL2000 SP3 build 818 on W2K3.
Do we need to authorize something in W2K3?
Thanks
Chris Wood
Alberta Department of Energy
CANADA|||Tom,
Has this behaviour happened in SQL2000 SP3 or W2K3?
Thanks
Chris
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OiV0iyyQEHA.3580@.TK2MSFTNGP11.phx.gbl...
> Try:
> select suser_sname(sid) from sysprocesses
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:Odpj8ryQEHA.3140@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We have a stored procedure that returns the nt_username, amongst other
> columns, from master.sysprocesses. This works fine under SQL2000 SP2 under
> W2K SP4. We are testing W2K3 and find that this information is missing
now.
> We are testing with SQL2000 SP3 build 818 on W2K3.
> Do we need to authorize something in W2K3?
> Thanks
> Chris Wood
> Alberta Department of Energy
> CANADA
>|||Chris,
I have a SQL Server 2000 server with build 818 running on both Windows 2000
and Windows 2003 and the nt_username is present in the output of select *
from sysprocesses onboth .
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Rand,
What are the Client network settings on your workstation and the Server
network settings on the server please?
Thanks
Chris
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:RqUFvYzQEHA.1516@.cpmsftngxa10.phx.gbl...
> Chris,
> I have a SQL Server 2000 server with build 818 running on both Windows
2000
> and Windows 2003 and the nt_username is present in the output of select *
> from sysprocesses onboth .
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>|||I have SQL 2000 under Windows 2003 and only some users show the nt_username in sysprocesses
All of the 2000 Clients do (9 of 9) only one of the XP clients do ( 1 of 15 ). Application using BDE/DBNETLIB to connect to server
Tim S

Retrieving nt_username from Sysprocesses

Hi,
We have a stored procedure that returns the nt_username, amongst other
columns, from master.sysprocesses. This works fine under SQL2000 SP2 under
W2K SP4. We are testing W2K3 and find that this information is missing now.
We are testing with SQL2000 SP3 build 818 on W2K3.
Do we need to authorize something in W2K3?
Thanks
Chris Wood
Alberta Department of Energy
CANADA
Try:
select suser_sname(sid) from sysprocesses
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
news:Odpj8ryQEHA.3140@.TK2MSFTNGP11.phx.gbl...
Hi,
We have a stored procedure that returns the nt_username, amongst other
columns, from master.sysprocesses. This works fine under SQL2000 SP2 under
W2K SP4. We are testing W2K3 and find that this information is missing now.
We are testing with SQL2000 SP3 build 818 on W2K3.
Do we need to authorize something in W2K3?
Thanks
Chris Wood
Alberta Department of Energy
CANADA
|||Tom,
Has this behaviour happened in SQL2000 SP3 or W2K3?
Thanks
Chris
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OiV0iyyQEHA.3580@.TK2MSFTNGP11.phx.gbl...
> Try:
> select suser_sname(sid) from sysprocesses
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com/sql
>
> "Chris Wood" <anonymous@.discussions.microsoft.com> wrote in message
> news:Odpj8ryQEHA.3140@.TK2MSFTNGP11.phx.gbl...
> Hi,
> We have a stored procedure that returns the nt_username, amongst other
> columns, from master.sysprocesses. This works fine under SQL2000 SP2 under
> W2K SP4. We are testing W2K3 and find that this information is missing
now.
> We are testing with SQL2000 SP3 build 818 on W2K3.
> Do we need to authorize something in W2K3?
> Thanks
> Chris Wood
> Alberta Department of Energy
> CANADA
>
|||Chris,
I have a SQL Server 2000 server with build 818 running on both Windows 2000
and Windows 2003 and the nt_username is present in the output of select *
from sysprocesses onboth .
Rand
This posting is provided "as is" with no warranties and confers no rights.
|||Rand,
What are the Client network settings on your workstation and the Server
network settings on the server please?
Thanks
Chris
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:RqUFvYzQEHA.1516@.cpmsftngxa10.phx.gbl...
> Chris,
> I have a SQL Server 2000 server with build 818 running on both Windows
2000
> and Windows 2003 and the nt_username is present in the output of select *
> from sysprocesses onboth .
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>
|||I have SQL 2000 under Windows 2003 and only some users show the nt_username in sysprocesses.
All of the 2000 Clients do (9 of 9) only one of the XP clients do ( 1 of 15 ). Application using BDE/DBNETLIB to connect to server.
Tim S
sql

Tuesday, March 20, 2012

Retrieving ID after insert

Hi, I have a Stored procedure doind an INSERT which then returns @.@.Identity.

I have set this parameters direction to output, however, when i run it I get an error saying procedure is expecting this output parameter..

Not sure where I am going wrong...

Can someone please help with retrieving the ID after an insert. What is the correct code in .NET?

Many ThanksShow your SP, and how you call it. You can get info like htis back as an output parameter, or as a return code. Absent knowing what you are doing, it is impossible to help.|||This is just a suggestion.

First define the input parameters and assign its values.

'Define the output parameter
With cmd.Parameters.Add("@.outputField", SqlDbType.Int)
.Direction = ParameterDirection.Output
End With

'Read the result
Dim dr As SqlDataReader = cmd.ExecuteReader
Dim returnId As Integer
If dr.Read Then
returnId = dr("OutputField")
End If
dr.Close()

My cmd is the reference to the SqlCommand.
OutputField – Field which you want to return.

Hope this might give you some help.|||Use Scope_Identity() instead of @.@.Identity.

Also..

cmd.ExecuteNonReader()

returnID = cmd.Parameters("@.outputField").Value
cmd.Dipose()

Saturday, February 25, 2012

Retrieve ONLY the Report Items that a user has permissions for

Using ListChildren(@."\",true) returns all items in the report server that a
user has access to, however if the user does not have access to view the home
folder, the call fails.
How do I retrieve ALL the folders/reports that a user has permission for
from an application running with the users default credentials?You would have to write or run the program with an admin account & then once
you get a list of all the children,
you'll have to loop through all of them to determine which one belong to the
X-User.
Edgar,
"DaveH" wrote:
> Using ListChildren(@."\",true) returns all items in the report server that a
> user has access to, however if the user does not have access to view the home
> folder, the call fails.
> How do I retrieve ALL the folders/reports that a user has permission for
> from an application running with the users default credentials?

Retrieve multiple variables from Stored Procedure (SQLHelper)

Hi all,

I am using SQLHelper to run a Stored Procedure.
The Stored Procedure returns 3 variables:

ie:


SELECT @.Hits = COUNT(DISTINCT StatID) FROM Stats

...etc...

The SP is currently called as below:


SqlParameter[] sqlParams = new SqlParameter[]
 {
 new SqlParameter("@.FromDate", Request["FromDate"]),
 new SqlParameter("@.ToDate", Request["ToDate"]),
 };

My question is this: How do I retrieve these variables?

I know I need to declare a new SqlParameter and change it's Direction to Output but I am unsure of the exact syntax required to do this.

Thanks in advance,

PeteHi.

Could u please expain how you are returning the values.. all 3 as output parameter?

as the code will depend on the way the values are returned..|||my full Stored Procedure looks as so:


CREATE PROC GetGeneralStats
@.FromDate smalldatetime,
@.ToDate smalldatetimeAS
Declare @.uniqueHits int
Declare @.noOfSearches int
SELECT @.uniqueHits = COUNT(DISTINCT StatID) FROM Stats
WHERE (StatDate >= @.FromDate) AND (StatDate <= @.ToDate)
-- No of times Searchresults Shown
SELECT @.NoOfSearches = COUNT(SectionID) FROM Stats
WHERE (StatDate >= @.FromDate) AND (StatDate <= @.ToDate)
AND SectionID = 3
Go

Cheers,

Pete|||This procedure is retieving and storing values in 2 variables and returning nothing.

you need to add 2 output parameters to the procedure..

CREATE PROC GetGeneralStats@.FromDate smalldatetime,
@.ToDate smalldatetime,
@.uniqueHits int OUTPUT,
@.noOfSearches int OUTPUT
AS
SELECT @.uniqueHits = COUNT(DISTINCT StatID) FROM Stats
WHERE (StatDate >= @.FromDate) AND (StatDate <= @.ToDate)
-- No of times Searchresults Shown
SELECT @.NoOfSearches = COUNT(SectionID) FROM Stats
WHERE (StatDate >= @.FromDate) AND (StatDate <= @.ToDate)
AND SectionID = 3

|||Hi,

Thanks for the reply.

As you said (and now having looked in the documentation) this is how it works but....

Im getting


Procedure 'GetGeneralStats' expects parameter '@.uniqueHits', which was not supplied.

So its thinking that I want them as Input variables? Even though I have them as:

@.uniqueHits int OUTPUT,
@.noOfSearches int OUTPUT

pete|||Arrgghhh!!! Everywhere I look Im told to do it this way but Im getting this stupid error message!

Im tearing my hair out over this!!!!!!!

retrieve Datetime

Friday, March 30, 2012

Return Date not DateTime

Wednesday, March 28, 2012

Return all months within a range of dates

return all ESNID one time, the most recent

Return ADO recordset to worksheet

Return a UNIQUEIDENTIFIER

Return a todays Date query?

Return a resultset to XML?

Return a resultset to XML?

Return a resultset to XML?

Return a horizontal table for result

Return 2 entries per group based on the two lowest values in group

Monday, March 26, 2012

Return 0 if null

Friday, March 23, 2012

Retrieving time zone adjustment

Wednesday, March 21, 2012

Retrieving Return value from stored procedure declaratively

Retrieving nt_username from Sysprocesses

Retrieving nt_username from Sysprocesses

Retrieving nt_username from Sysprocesses

Tuesday, March 20, 2012

Retrieving ID after insert

Saturday, February 25, 2012

Retrieve ONLY the Report Items that a user has permissions for

Retrieve multiple variables from Stored Procedure (SQLHelper)

Followers

Blog Archive

About Me