Grouping Views by Dates

I have a table with the date in a full format including hours, minutes and
seconds etc. I would like to group the information by yearmonthday and also
by yearmonth.Hi
CREATE TABLE #Test
(
dt DATETIME NOT NULL
)
--Change the data for your needs
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-01 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-02 13:46:59.707')
INSERT INTO #Test VALUES ('2006-01-01 13:46:59.707')
SELECT Year(dt),Count(*) FROM #Test
GROUP BY Year(dt)
Also lookup DAY(),MONTH() system functions in the BOL
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:2FED6491-6AFE-40B6-AFC5-3E1561D211C1@.microsoft.com...
>I have a table with the date in a full format including hours, minutes and
> seconds etc. I would like to group the information by yearmonthday and
> also
> by yearmonth.|||Thankyou, however perhaps I should have given more detail....
Currently the date is like
NAME TYPE DATE
Rothery Telephone call out 26/09/05 13:00:00
Ginnelly Auto Order 26/09/05 12:55:03
Aldcroft Customer Services 26/09/05 12:55:00
Aldcroft Auto Order 26/09/05 12:54:20
Broadbent Auto Reminder 26/09/05 12:54:07
Broadbent Auto Reminder 26/09/05 12:51:23
Pickles Telephone call out 26/09/05 12:50:00
Broadbent Auto Reminder 26/09/05 12:49:50
Pickles Telephone call in 26/09/05 12:43:00
Broadbent Auto Reminder 26/09/05 12:41:22
Race Auto Appointment 26/09/05 12:41:13
Race Auto Appointment 26/09/05 12:36:27
Broadbent Auto Reminder 26/09/05 12:36:04
Broadbent Auto Reminder 26/09/05 12:33:38
Broadbent Auto Reminder 26/09/05 12:30:01
Booth Telephone call out 26/09/05 12:25:32
Booth Telephone call out 26/09/05 12:23:00
Prior Auto Hire 26/09/05 12:18:40
for 18 months of data....
I would like to group these by like,
26/09/05 Davis Telephone Call 5
25/09/05 Davis Telephone Call 3
24/09/05 Davis Telephone Call 8
Also
2005-10 Davis Telephone Call 123
2005-09 Davis Telephone Call 111
2005-08 Davis Telephone Call 77
and so on...
thanks again.|||Pls post a complete DDL ( like in my example).
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:657D99E0-0C69-4180-B987-15FDE2EFB83F@.microsoft.com...
> Thankyou, however perhaps I should have given more detail....
> Currently the date is like
> NAME TYPE DATE
> Rothery Telephone call out 26/09/05 13:00:00
> Ginnelly Auto Order 26/09/05 12:55:03
> Aldcroft Customer Services 26/09/05 12:55:00
> Aldcroft Auto Order 26/09/05 12:54:20
> Broadbent Auto Reminder 26/09/05 12:54:07
> Broadbent Auto Reminder 26/09/05 12:51:23
> Pickles Telephone call out 26/09/05 12:50:00
> Broadbent Auto Reminder 26/09/05 12:49:50
> Pickles Telephone call in 26/09/05 12:43:00
> Broadbent Auto Reminder 26/09/05 12:41:22
> Race Auto Appointment 26/09/05 12:41:13
> Race Auto Appointment 26/09/05 12:36:27
> Broadbent Auto Reminder 26/09/05 12:36:04
> Broadbent Auto Reminder 26/09/05 12:33:38
> Broadbent Auto Reminder 26/09/05 12:30:01
> Booth Telephone call out 26/09/05 12:25:32
> Booth Telephone call out 26/09/05 12:23:00
> Prior Auto Hire 26/09/05 12:18:40
> for 18 months of data....
> I would like to group these by like,
> 26/09/05 Davis Telephone Call 5
> 25/09/05 Davis Telephone Call 3
> 24/09/05 Davis Telephone Call 8
> Also
> 2005-10 Davis Telephone Call 123
> 2005-09 Davis Telephone Call 111
> 2005-08 Davis Telephone Call 77
> and so on...
> thanks again.
>

Grouping Views by Dates

I have a table with the date in a full format including hours, minutes and
seconds etc. I would like to group the information by yearmonthday and also
by yearmonth.
Hi
CREATE TABLE #Test
(
dt DATETIME NOT NULL
)
--Change the data for your needs
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-01 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-02 13:46:59.707')
INSERT INTO #Test VALUES ('2006-01-01 13:46:59.707')
SELECT Year(dt),Count(*) FROM #Test
GROUP BY Year(dt)
Also lookup DAY(),MONTH() system functions in the BOL
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:2FED6491-6AFE-40B6-AFC5-3E1561D211C1@.microsoft.com...
>I have a table with the date in a full format including hours, minutes and
> seconds etc. I would like to group the information by yearmonthday and
> also
> by yearmonth.
|||Thankyou, however perhaps I should have given more detail....
Currently the date is like
NAME TYPE DATE
RotheryTelephone call out26/09/05 13:00:00
GinnellyAuto Order 26/09/05 12:55:03
AldcroftCustomer Services26/09/05 12:55:00
AldcroftAuto Order 26/09/05 12:54:20
BroadbentAuto Reminder26/09/05 12:54:07
BroadbentAuto Reminder26/09/05 12:51:23
PicklesTelephone call out26/09/05 12:50:00
BroadbentAuto Reminder26/09/05 12:49:50
PicklesTelephone call in26/09/05 12:43:00
BroadbentAuto Reminder26/09/05 12:41:22
RaceAuto Appointment26/09/05 12:41:13
RaceAuto Appointment26/09/05 12:36:27
BroadbentAuto Reminder26/09/05 12:36:04
BroadbentAuto Reminder26/09/05 12:33:38
BroadbentAuto Reminder26/09/05 12:30:01
BoothTelephone call out26/09/05 12:25:32
BoothTelephone call out26/09/05 12:23:00
PriorAuto Hire 26/09/05 12:18:40
for 18 months of data....
I would like to group these by like,
26/09/05 Davis Telephone Call 5
25/09/05 Davis Telephone Call 3
24/09/05 Davis Telephone Call 8
Also
2005-10 Davis Telephone Call 123
2005-09 Davis Telephone Call 111
2005-08 Davis Telephone Call 77
and so on...
thanks again.
|||Pls post a complete DDL ( like in my example).
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:657D99E0-0C69-4180-B987-15FDE2EFB83F@.microsoft.com...
> Thankyou, however perhaps I should have given more detail....
> Currently the date is like
> NAME TYPE DATE
> Rothery Telephone call out 26/09/05 13:00:00
> Ginnelly Auto Order 26/09/05 12:55:03
> Aldcroft Customer Services 26/09/05 12:55:00
> Aldcroft Auto Order 26/09/05 12:54:20
> Broadbent Auto Reminder 26/09/05 12:54:07
> Broadbent Auto Reminder 26/09/05 12:51:23
> Pickles Telephone call out 26/09/05 12:50:00
> Broadbent Auto Reminder 26/09/05 12:49:50
> Pickles Telephone call in 26/09/05 12:43:00
> Broadbent Auto Reminder 26/09/05 12:41:22
> Race Auto Appointment 26/09/05 12:41:13
> Race Auto Appointment 26/09/05 12:36:27
> Broadbent Auto Reminder 26/09/05 12:36:04
> Broadbent Auto Reminder 26/09/05 12:33:38
> Broadbent Auto Reminder 26/09/05 12:30:01
> Booth Telephone call out 26/09/05 12:25:32
> Booth Telephone call out 26/09/05 12:23:00
> Prior Auto Hire 26/09/05 12:18:40
> for 18 months of data....
> I would like to group these by like,
> 26/09/05 Davis Telephone Call 5
> 25/09/05 Davis Telephone Call 3
> 24/09/05 Davis Telephone Call 8
> Also
> 2005-10 Davis Telephone Call 123
> 2005-09 Davis Telephone Call 111
> 2005-08 Davis Telephone Call 77
> and so on...
> thanks again.
>

Grouping Views by Dates

I have a table with the date in a full format including hours, minutes and
seconds etc. I would like to group the information by yearmonthday and also
by yearmonth.Hi
CREATE TABLE #Test
(
dt DATETIME NOT NULL
)
--Change the data for your needs
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-26 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-09-27 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-01 13:46:59.707')
INSERT INTO #Test VALUES ('2005-10-02 13:46:59.707')
INSERT INTO #Test VALUES ('2006-01-01 13:46:59.707')
SELECT Year(dt),Count(*) FROM #Test
GROUP BY Year(dt)
Also lookup DAY(),MONTH() system functions in the BOL
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:2FED6491-6AFE-40B6-AFC5-3E1561D211C1@.microsoft.com...
>I have a table with the date in a full format including hours, minutes and
> seconds etc. I would like to group the information by yearmonthday and
> also
> by yearmonth.|||Thankyou, however perhaps I should have given more detail....
Currently the date is like
NAME TYPE DATE
Rothery Telephone call out 26/09/05 13:00:00
Ginnelly Auto Order 26/09/05 12:55:03
Aldcroft Customer Services 26/09/05 12:55:00
Aldcroft Auto Order 26/09/05 12:54:20
Broadbent Auto Reminder 26/09/05 12:54:07
Broadbent Auto Reminder 26/09/05 12:51:23
Pickles Telephone call out 26/09/05 12:50:00
Broadbent Auto Reminder 26/09/05 12:49:50
Pickles Telephone call in 26/09/05 12:43:00
Broadbent Auto Reminder 26/09/05 12:41:22
Race Auto Appointment 26/09/05 12:41:13
Race Auto Appointment 26/09/05 12:36:27
Broadbent Auto Reminder 26/09/05 12:36:04
Broadbent Auto Reminder 26/09/05 12:33:38
Broadbent Auto Reminder 26/09/05 12:30:01
Booth Telephone call out 26/09/05 12:25:32
Booth Telephone call out 26/09/05 12:23:00
Prior Auto Hire 26/09/05 12:18:40
for 18 months of data....
I would like to group these by like,
26/09/05 Davis Telephone Call 5
25/09/05 Davis Telephone Call 3
24/09/05 Davis Telephone Call 8
Also
2005-10 Davis Telephone Call 123
2005-09 Davis Telephone Call 111
2005-08 Davis Telephone Call 77
and so on...
thanks again.|||Pls post a complete DDL ( like in my example).
"akdavis2002" <akdavis2002@.discussions.microsoft.com> wrote in message
news:657D99E0-0C69-4180-B987-15FDE2EFB83F@.microsoft.com...
> Thankyou, however perhaps I should have given more detail....
> Currently the date is like
> NAME TYPE DATE
> Rothery Telephone call out 26/09/05 13:00:00
> Ginnelly Auto Order 26/09/05 12:55:03
> Aldcroft Customer Services 26/09/05 12:55:00
> Aldcroft Auto Order 26/09/05 12:54:20
> Broadbent Auto Reminder 26/09/05 12:54:07
> Broadbent Auto Reminder 26/09/05 12:51:23
> Pickles Telephone call out 26/09/05 12:50:00
> Broadbent Auto Reminder 26/09/05 12:49:50
> Pickles Telephone call in 26/09/05 12:43:00
> Broadbent Auto Reminder 26/09/05 12:41:22
> Race Auto Appointment 26/09/05 12:41:13
> Race Auto Appointment 26/09/05 12:36:27
> Broadbent Auto Reminder 26/09/05 12:36:04
> Broadbent Auto Reminder 26/09/05 12:33:38
> Broadbent Auto Reminder 26/09/05 12:30:01
> Booth Telephone call out 26/09/05 12:25:32
> Booth Telephone call out 26/09/05 12:23:00
> Prior Auto Hire 26/09/05 12:18:40
> for 18 months of data....
> I would like to group these by like,
> 26/09/05 Davis Telephone Call 5
> 25/09/05 Davis Telephone Call 3
> 24/09/05 Davis Telephone Call 8
> Also
> 2005-10 Davis Telephone Call 123
> 2005-09 Davis Telephone Call 111
> 2005-08 Davis Telephone Call 77
> and so on...
> thanks again.
>sql

Grouping common functionality in multiple stored procedures

Hi i have always used views in my code to group common functionality in my sql expressions and then i can simply call these views in my data access layer by saing:

SqlCommand cmd = new SqlCommand("SELECT * FROM vw_Documents WHERE CategoryID = @.CategoryID", cn);

However my view has become so complicated that i had to convert it to a stored procedure called sp_Documents. The problem now though is that is that i wish to do queries against the data returned but i can't simply say:

SqlCommand cmd = new SqlCommand("SELECT * FROM sp_Documents WHERE CategoryID = @.CategoryID", cn);

The only way i can see to do it is to create a stored procedure for every single senario i have passing in the appropriate values as parameters. This seems a pretty messy solution to me because i would have repeated logic in all my stored procedures. Therefore i was wondering if there's a simpler way for me to do this or am i just being lazy :).

Appreciate if someone could help,

Oops i found the solution straight after i posted. User defined functions. Never realized you could return more than one value with a function in sql server. If there is a better solution please let me know but this seems to tick all the boxes.

Edit: I have discovered that this is not going to work for me since my stored procedure produces different columns (based on values passed in) and it appears that the Multi-statement Table-Value User-Defined Function requires you to specify the structure you will be outputting.

|||

>SqlCommand cmd = new SqlCommand("SELECT * FROM vw_Documents WHERE CategoryID = @.CategoryID", cn);

It is preferable to select just the columns you require.

>However my view has become so complicated that i had to convert itto a stored procedure called sp_Documents.
>The problem now though isthat is that i wish to do queries against the data returned but I can'tsimply say:
>SqlCommand cmd = new SqlCommand("SELECT * FROM sp_Documents WHERE CategoryID = @.CategoryID", cn);
>The only way i can see to do it is to create a stored procedure forevery single scenario i have passing in the appropriate values asparameters
It is tempting to code complicated IF ... SELECT ... ELSE SELECT ..., however it is generally best to a code one stored procedure for each permutation as then the query engine can optimise each variation. There are some situations where serial scanning of a table is an acceptable perfomance hit and it is possible to use the COALESCE trick to search any combination of 1 to N columns for specific value. For example if table FRED has non-null columns A through D and the sp has args &A to &D and for simplicity the allowed values are non-zero integer then:
IF &A = 0 SET &A = NULL
IF &B = 0 SET &B = NULL
IF &C = 0 SET &C = NULL
IF &D = 0 SET &D = NULL
SELECT A, B, C, D FROM FRED
WHERE COALESCE(&A, A) = A
AND COALESCE(&B, B) = B AND COALESCE(&C, C) = C AND COALESCE(&D, D) = D

If say &A is the only non-zero parameter then the effect select simplifies to SELECT A, B, C, D FROM FRED WHERE &A = A, as COALESCE selects the first non-null value.

sql