Function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Function

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-08-02 : 08:06:16

I am converting an old Access db to SQL and windows forms. In the old db I have a query based on a query. I have worked out (from googling) that I need to create a function (as it will need parameters) and base my stored procedure on that. My question though, is where do I create the function ? Do I put it at the beginning of my sp ?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-02 : 08:13:05

Can you post the Access queries, and describe what each is meant to do? It's possible (even likely) it can be done with a single stored procedure.

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-08-02 : 08:25:30

Query 1
SELECT tblTickets.DatePurchased, tblTickets.rptTime, tblTickets.TimePurchased, tblTickets.BookType, tblTickets.NoBooks, tblTickets.AmtPaid, tblTickets.SoldBy
FROM tblTickets
WHERE (((tblTickets.rptTime)>=[Forms]![ReportMenu]![StartParam] And (tblTickets.rptTime)<=[Forms]![ReportMenu]![EndParam]) AND ((tblTickets.SoldBy) Like "*" & [Forms]![ReportMenu]![OfficerCbo] & "*"))
ORDER BY tblTickets.DatePurchased, tblTickets.TimePurchased, tblTickets.BookType, tblTickets.SoldBy;

Query 2

SELECT qrySalesSummary1.DatePurchased, qrySalesSummary1.BookType, Sum(qrySalesSummary1.NoBooks) AS SumOfNoBooks, Sum(qrySalesSummary1.AmtPaid) AS SumOfAmtPaid
FROM qrySalesSummary1
GROUP BY qrySalesSummary1.DatePurchased, qrySalesSummary1.BookType
ORDER BY qrySalesSummary1.DatePurchased, qrySalesSummary1.BookType;

I haven't tried to do it with one sp yet..........

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-02 : 09:46:41

I'm assuming the first query is named qrySalesSummary1?

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-08-02 : 10:01:46

Yes, that's right

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-02 : 10:04:56

[code]CREATE PROCEDURE SalesSummary @start datetime, @end datetime, @OfficerCBO varchar(50) AS
SET NOCOUNT ON;
SELECT DatePurchased, BookType, Sum(NoBooks) AS SumOfNoBooks, Sum(AmtPaid) AS SumOfAmtPaid
FROM tblTickets
WHERE rptTime BETWEEN @start And @end AND SoldBy Like '%' + @OfficerCbo + '%'
GROUP BY DatePurchased, BookType
ORDER BY DatePurchased, BookType;
GO

-- example:
EXEC SalesSummary '01/01/2011', '08/01/2011', 'Rob'[/code]

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-08-02 : 10:29:15

Thank you for that, but as the date is stored like this 2011-07-29 10:22:00.000 it does not sum for each day but treats every datetime as unique (which it is) so in effect groups on each one and just shows a list of each transaction

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-08-02 : 10:36:57

[code]CREATE PROCEDURE SalesSummary @start datetime, @end datetime, @OfficerCBO varchar(50) AS
SET NOCOUNT ON;
SELECT DateAdd(day, Datediff(day, 0, DatePurchased), 0) DatePurchased, BookType, Sum(NoBooks) AS SumOfNoBooks, Sum(AmtPaid) AS SumOfAmtPaid
FROM tblTickets
WHERE rptTime BETWEEN @start And @end AND SoldBy Like '%' + @OfficerCbo + '%'
GROUP BY DateAdd(day, Datediff(day, 0, DatePurchased), 0), BookType
ORDER BY DateAdd(day, Datediff(day, 0, DatePurchased), 0), BookType;[/code]

Pinto
Aged Yak Warrior

590 Posts

Posted - 2011-08-02 : 10:55:20

Thanks - I just sorted it this way which seems to work too - thanks for your help :-)

SELECT CONVERT(Date DatePurchased), 1) DatePurchased, BookType, Sum(NoBooks) AS SumOfNoBooks, Sum(AmtPaid) AS SumOfAmtPaid
FROM tblTickets
WHERE rptTime BETWEEN @start And @end AND SoldBy Like '%' + @OfficerCbo + '%'
GROUP BY CONVERT(Date DatePurchased), 1)), BookType
ORDER BY CONVERT(Date DatePurchased), 1)), BookType;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources