Hey All,
I wondering how you do a calculated field in Reporting
Services. I need to be able to iterate through two
seperate tables and figure out the activation rate. So I
thought I could use a calcuated field but when I do it, it
tries to apply the sum function when I drag that field out
of the fields list. When I remove the sum function I get
an error.
= Fields!ID.Value, "MTReg"/ Fields!ID.Value, "MTAct" I
don't get it how do I do this?I'm afraid that's not possible.
You'll need to calculate it in a query
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"bmurtha" <anonymous@.discussions.microsoft.com> wrote in message
news:3c9f01c472c5$eb5b27c0$a401280a@.phx.gbl...
> Hey All,
> I wondering how you do a calculated field in Reporting
> Services. I need to be able to iterate through two
> seperate tables and figure out the activation rate. So I
> thought I could use a calcuated field but when I do it, it
> tries to apply the sum function when I drag that field out
> of the fields list. When I remove the sum function I get
> an error.
> = Fields!ID.Value, "MTReg"/ Fields!ID.Value, "MTAct" I
> don't get it how do I do this?
Showing posts with label figure. Show all posts
Showing posts with label figure. Show all posts
Sunday, March 25, 2012
Thursday, March 22, 2012
Calculate Unit % by Category
I have a report that is grouped by Category and on each detail line I need to show the % Units Sold per Category. I can't figure out how to get the total Units Sold per Category before I write the individual lines. Can someone, please, walk me through this process?
Thank you very much!!!
ChrisRight click on the filed select summary and summary type as sum and Check the button "show as Percentage" and select again the filed
Thank you very much!!!
ChrisRight click on the filed select summary and summary type as sum and Check the button "show as Percentage" and select again the filed
Tuesday, March 20, 2012
Calculate fiscal periods (T-SQL)
Hi Experts,
I would like to be able to take in a year and figure out its 12 month period
based on Sunday as starting day of the 1st week of that month and each perio
d
has 4 weeks.
For example:
Input: 2007
Output:
P1: 12/31/2006 - 1/27/2007
P2: 1/28/2007 - 2/24/2007
P3: 2/25/2007 - 3/31/2007
P4: 4/1/2007 - 4/28/2007
P5: 4/29/2007 - 5/26/2007
P6: 5/27/2007 - 6/30/2007
P7: 7/1/2007 - 7/28/2007
P8: 7/29/2007 - 8/25/2007
.
.
.
P12: 11/25/2007 -12/29/2007
P1 starts on 12/31/2006 because that falls on Sunday of the 1st week of
January/2007. P1 ends on 1/27/2007 because that's last day (Saturday) of 4th
week
and so on
My accounting dept folks call this fiscal period.
Anyone out there have done this or know how to do this type of calculation
using T-SQL.
I appreciate so much for any help.
Thanhexamnotes <ThanhNguyen@.discussions.microsoft.com>
wrote in news:ABAF7E37-0534-42EB-95A6-23E95963D54A@.microsoft.com:
> Hi Experts,
> I would like to be able to take in a year and figure out its 12 month
> period based on Sunday as starting day of the 1st week of that month
> and each period has 4 weeks.
> For example:
> Input: 2007
> Output:
> P1: 12/31/2006 - 1/27/2007
> P2: 1/28/2007 - 2/24/2007
> P3: 2/25/2007 - 3/31/2007
> P4: 4/1/2007 - 4/28/2007
> P5: 4/29/2007 - 5/26/2007
> P6: 5/27/2007 - 6/30/2007
> P7: 7/1/2007 - 7/28/2007
> P8: 7/29/2007 - 8/25/2007
> .
> .
> .
> P12: 11/25/2007 -12/29/2007
>
> P1 starts on 12/31/2006 because that falls on Sunday of the 1st week
> of January/2007. P1 ends on 1/27/2007 because that's last day
> (Saturday) of 4th week
> and so on
> My accounting dept folks call this fiscal period.
> Anyone out there have done this or know how to do this type of
> calculation using T-SQL.
> I appreciate so much for any help.
> Thanh
>
See DATEADD(dd, ... ) and DATEPART(dw, ...) in BOL, but first you will
need an unambiguous definition for fiscal period.|||Below is one method. I suggest you materialize these results in a fiscal
period table.
DECLARE @.first_of_year datetime
SET @.first_of_year = '20070101'
SELECT
DATEADD(day, (DATEPART(dw, DATEADD(month, month_number - 1,
@.first_of_year)) - 1) * -1,
DATEADD(month, month_number - 1, @.first_of_year)) AS PeriodStart,
DATEADD(day, (DATEPART(dw, DATEADD(month, month_number,
@.first_of_year))) * -1,
DATEADD(month, month_number, @.first_of_year)) AS PeriodEnd
FROM (SELECT 1 AS month_number UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 AS month_number UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 AS month_number UNION ALL SELECT 8 UNION ALL SELECT 9
UNION ALL SELECT 10 AS month_number UNION ALL SELECT 11 UNION ALL SELECT
12)
AS month_numbers
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris.Cheney" <Chris.CheneyXXNOSPAMXX@.tesco.net> wrote in message
news:Xns9924696609362ChrisCheneytesconet
@.80.5.182.99...
> examnotes <ThanhNguyen@.discussions.microsoft.com>
> wrote in news:ABAF7E37-0534-42EB-95A6-23E95963D54A@.microsoft.com:
>
> See DATEADD(dd, ... ) and DATEPART(dw, ...) in BOL, but first you will
> need an unambiguous definition for fiscal period.|||Thank you very much, Dan.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:3CFD7275-D58D-4D46-B5CB-DA1F3BBEC96A@.microsoft.com...
> Below is one method. I suggest you materialize these results in a fiscal
> period table.
> DECLARE @.first_of_year datetime
> SET @.first_of_year = '20070101'
> SELECT
> DATEADD(day, (DATEPART(dw, DATEADD(month, month_number - 1,
> @.first_of_year)) - 1) * -1,
> DATEADD(month, month_number - 1, @.first_of_year)) AS PeriodStart,
> DATEADD(day, (DATEPART(dw, DATEADD(month, month_number,
> @.first_of_year))) * -1,
> DATEADD(month, month_number, @.first_of_year)) AS PeriodEnd
> FROM (SELECT 1 AS month_number UNION ALL SELECT 2 UNION ALL SELECT 3
> UNION ALL SELECT 4 AS month_number UNION ALL SELECT 5 UNION ALL SELECT
> 6
> UNION ALL SELECT 7 AS month_number UNION ALL SELECT 8 UNION ALL SELECT
> 9
> UNION ALL SELECT 10 AS month_number UNION ALL SELECT 11 UNION ALL
> SELECT 12)
> AS month_numbers
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Chris.Cheney" <Chris.CheneyXXNOSPAMXX@.tesco.net> wrote in message
> news:Xns9924696609362ChrisCheneytesconet
@.80.5.182.99...
>
I would like to be able to take in a year and figure out its 12 month period
based on Sunday as starting day of the 1st week of that month and each perio
d
has 4 weeks.
For example:
Input: 2007
Output:
P1: 12/31/2006 - 1/27/2007
P2: 1/28/2007 - 2/24/2007
P3: 2/25/2007 - 3/31/2007
P4: 4/1/2007 - 4/28/2007
P5: 4/29/2007 - 5/26/2007
P6: 5/27/2007 - 6/30/2007
P7: 7/1/2007 - 7/28/2007
P8: 7/29/2007 - 8/25/2007
.
.
.
P12: 11/25/2007 -12/29/2007
P1 starts on 12/31/2006 because that falls on Sunday of the 1st week of
January/2007. P1 ends on 1/27/2007 because that's last day (Saturday) of 4th
week
and so on
My accounting dept folks call this fiscal period.
Anyone out there have done this or know how to do this type of calculation
using T-SQL.
I appreciate so much for any help.
Thanhexamnotes <ThanhNguyen@.discussions.microsoft.com>
wrote in news:ABAF7E37-0534-42EB-95A6-23E95963D54A@.microsoft.com:
> Hi Experts,
> I would like to be able to take in a year and figure out its 12 month
> period based on Sunday as starting day of the 1st week of that month
> and each period has 4 weeks.
> For example:
> Input: 2007
> Output:
> P1: 12/31/2006 - 1/27/2007
> P2: 1/28/2007 - 2/24/2007
> P3: 2/25/2007 - 3/31/2007
> P4: 4/1/2007 - 4/28/2007
> P5: 4/29/2007 - 5/26/2007
> P6: 5/27/2007 - 6/30/2007
> P7: 7/1/2007 - 7/28/2007
> P8: 7/29/2007 - 8/25/2007
> .
> .
> .
> P12: 11/25/2007 -12/29/2007
>
> P1 starts on 12/31/2006 because that falls on Sunday of the 1st week
> of January/2007. P1 ends on 1/27/2007 because that's last day
> (Saturday) of 4th week
> and so on
> My accounting dept folks call this fiscal period.
> Anyone out there have done this or know how to do this type of
> calculation using T-SQL.
> I appreciate so much for any help.
> Thanh
>
See DATEADD(dd, ... ) and DATEPART(dw, ...) in BOL, but first you will
need an unambiguous definition for fiscal period.|||Below is one method. I suggest you materialize these results in a fiscal
period table.
DECLARE @.first_of_year datetime
SET @.first_of_year = '20070101'
SELECT
DATEADD(day, (DATEPART(dw, DATEADD(month, month_number - 1,
@.first_of_year)) - 1) * -1,
DATEADD(month, month_number - 1, @.first_of_year)) AS PeriodStart,
DATEADD(day, (DATEPART(dw, DATEADD(month, month_number,
@.first_of_year))) * -1,
DATEADD(month, month_number, @.first_of_year)) AS PeriodEnd
FROM (SELECT 1 AS month_number UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 AS month_number UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 AS month_number UNION ALL SELECT 8 UNION ALL SELECT 9
UNION ALL SELECT 10 AS month_number UNION ALL SELECT 11 UNION ALL SELECT
12)
AS month_numbers
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris.Cheney" <Chris.CheneyXXNOSPAMXX@.tesco.net> wrote in message
news:Xns9924696609362ChrisCheneytesconet
@.80.5.182.99...
> examnotes <ThanhNguyen@.discussions.microsoft.com>
> wrote in news:ABAF7E37-0534-42EB-95A6-23E95963D54A@.microsoft.com:
>
> See DATEADD(dd, ... ) and DATEPART(dw, ...) in BOL, but first you will
> need an unambiguous definition for fiscal period.|||Thank you very much, Dan.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:3CFD7275-D58D-4D46-B5CB-DA1F3BBEC96A@.microsoft.com...
> Below is one method. I suggest you materialize these results in a fiscal
> period table.
> DECLARE @.first_of_year datetime
> SET @.first_of_year = '20070101'
> SELECT
> DATEADD(day, (DATEPART(dw, DATEADD(month, month_number - 1,
> @.first_of_year)) - 1) * -1,
> DATEADD(month, month_number - 1, @.first_of_year)) AS PeriodStart,
> DATEADD(day, (DATEPART(dw, DATEADD(month, month_number,
> @.first_of_year))) * -1,
> DATEADD(month, month_number, @.first_of_year)) AS PeriodEnd
> FROM (SELECT 1 AS month_number UNION ALL SELECT 2 UNION ALL SELECT 3
> UNION ALL SELECT 4 AS month_number UNION ALL SELECT 5 UNION ALL SELECT
> 6
> UNION ALL SELECT 7 AS month_number UNION ALL SELECT 8 UNION ALL SELECT
> 9
> UNION ALL SELECT 10 AS month_number UNION ALL SELECT 11 UNION ALL
> SELECT 12)
> AS month_numbers
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Chris.Cheney" <Chris.CheneyXXNOSPAMXX@.tesco.net> wrote in message
> news:Xns9924696609362ChrisCheneytesconet
@.80.5.182.99...
>
Calculate fiscal periods (T-SQL)
Hi Experts,
I would like to be able to take in a year and figure out its 12 month period
based on Sunday as starting day of the 1st week of that month and each period
has 4 weeks.
For example:
Input: 2007
Output:
P1: 12/31/2006 - 1/27/2007
P2: 1/28/2007 - 2/24/2007
P3: 2/25/2007 - 3/31/2007
P4: 4/1/2007 - 4/28/2007
P5: 4/29/2007 - 5/26/2007
P6: 5/27/2007 - 6/30/2007
P7: 7/1/2007 - 7/28/2007
P8: 7/29/2007 - 8/25/2007
.
.
.
P12: 11/25/2007 -12/29/2007
P1 starts on 12/31/2006 because that falls on Sunday of the 1st week of
January/2007. P1 ends on 1/27/2007 because that's last day (Saturday) of 4th
week
and so on
My accounting dept folks call this fiscal period.
Anyone out there have done this or know how to do this type of calculation
using T-SQL.
I appreciate so much for any help.
Thanh=?Utf-8?B?VGhhbmggTmd1eWVu?= <ThanhNguyen@.discussions.microsoft.com>
wrote in news:ABAF7E37-0534-42EB-95A6-23E95963D54A@.microsoft.com:
> Hi Experts,
> I would like to be able to take in a year and figure out its 12 month
> period based on Sunday as starting day of the 1st week of that month
> and each period has 4 weeks.
> For example:
> Input: 2007
> Output:
> P1: 12/31/2006 - 1/27/2007
> P2: 1/28/2007 - 2/24/2007
> P3: 2/25/2007 - 3/31/2007
> P4: 4/1/2007 - 4/28/2007
> P5: 4/29/2007 - 5/26/2007
> P6: 5/27/2007 - 6/30/2007
> P7: 7/1/2007 - 7/28/2007
> P8: 7/29/2007 - 8/25/2007
> .
> .
> .
> P12: 11/25/2007 -12/29/2007
>
> P1 starts on 12/31/2006 because that falls on Sunday of the 1st week
> of January/2007. P1 ends on 1/27/2007 because that's last day
> (Saturday) of 4th week
> and so on
> My accounting dept folks call this fiscal period.
> Anyone out there have done this or know how to do this type of
> calculation using T-SQL.
> I appreciate so much for any help.
> Thanh
>
See DATEADD(dd, ... ) and DATEPART(dw, ...) in BOL, but first you will
need an unambiguous definition for fiscal period.|||Below is one method. I suggest you materialize these results in a fiscal
period table.
DECLARE @.first_of_year datetime
SET @.first_of_year = '20070101'
SELECT
DATEADD(day, (DATEPART(dw, DATEADD(month, month_number - 1,
@.first_of_year)) - 1) * -1,
DATEADD(month, month_number - 1, @.first_of_year)) AS PeriodStart,
DATEADD(day, (DATEPART(dw, DATEADD(month, month_number,
@.first_of_year))) * -1,
DATEADD(month, month_number, @.first_of_year)) AS PeriodEnd
FROM (SELECT 1 AS month_number UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 AS month_number UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 AS month_number UNION ALL SELECT 8 UNION ALL SELECT 9
UNION ALL SELECT 10 AS month_number UNION ALL SELECT 11 UNION ALL SELECT
12)
AS month_numbers
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris.Cheney" <Chris.CheneyXXNOSPAMXX@.tesco.net> wrote in message
news:Xns9924696609362ChrisCheneytesconet@.80.5.182.99...
> =?Utf-8?B?VGhhbmggTmd1eWVu?= <ThanhNguyen@.discussions.microsoft.com>
> wrote in news:ABAF7E37-0534-42EB-95A6-23E95963D54A@.microsoft.com:
>> Hi Experts,
>> I would like to be able to take in a year and figure out its 12 month
>> period based on Sunday as starting day of the 1st week of that month
>> and each period has 4 weeks.
>> For example:
>> Input: 2007
>> Output:
>> P1: 12/31/2006 - 1/27/2007
>> P2: 1/28/2007 - 2/24/2007
>> P3: 2/25/2007 - 3/31/2007
>> P4: 4/1/2007 - 4/28/2007
>> P5: 4/29/2007 - 5/26/2007
>> P6: 5/27/2007 - 6/30/2007
>> P7: 7/1/2007 - 7/28/2007
>> P8: 7/29/2007 - 8/25/2007
>> .
>> .
>> .
>> P12: 11/25/2007 -12/29/2007
>>
>> P1 starts on 12/31/2006 because that falls on Sunday of the 1st week
>> of January/2007. P1 ends on 1/27/2007 because that's last day
>> (Saturday) of 4th week
>> and so on
>> My accounting dept folks call this fiscal period.
>> Anyone out there have done this or know how to do this type of
>> calculation using T-SQL.
>> I appreciate so much for any help.
>> Thanh
> See DATEADD(dd, ... ) and DATEPART(dw, ...) in BOL, but first you will
> need an unambiguous definition for fiscal period.|||Thank you very much, Dan.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:3CFD7275-D58D-4D46-B5CB-DA1F3BBEC96A@.microsoft.com...
> Below is one method. I suggest you materialize these results in a fiscal
> period table.
> DECLARE @.first_of_year datetime
> SET @.first_of_year = '20070101'
> SELECT
> DATEADD(day, (DATEPART(dw, DATEADD(month, month_number - 1,
> @.first_of_year)) - 1) * -1,
> DATEADD(month, month_number - 1, @.first_of_year)) AS PeriodStart,
> DATEADD(day, (DATEPART(dw, DATEADD(month, month_number,
> @.first_of_year))) * -1,
> DATEADD(month, month_number, @.first_of_year)) AS PeriodEnd
> FROM (SELECT 1 AS month_number UNION ALL SELECT 2 UNION ALL SELECT 3
> UNION ALL SELECT 4 AS month_number UNION ALL SELECT 5 UNION ALL SELECT
> 6
> UNION ALL SELECT 7 AS month_number UNION ALL SELECT 8 UNION ALL SELECT
> 9
> UNION ALL SELECT 10 AS month_number UNION ALL SELECT 11 UNION ALL
> SELECT 12)
> AS month_numbers
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Chris.Cheney" <Chris.CheneyXXNOSPAMXX@.tesco.net> wrote in message
> news:Xns9924696609362ChrisCheneytesconet@.80.5.182.99...
>> =?Utf-8?B?VGhhbmggTmd1eWVu?= <ThanhNguyen@.discussions.microsoft.com>
>> wrote in news:ABAF7E37-0534-42EB-95A6-23E95963D54A@.microsoft.com:
>> Hi Experts,
>> I would like to be able to take in a year and figure out its 12 month
>> period based on Sunday as starting day of the 1st week of that month
>> and each period has 4 weeks.
>> For example:
>> Input: 2007
>> Output:
>> P1: 12/31/2006 - 1/27/2007
>> P2: 1/28/2007 - 2/24/2007
>> P3: 2/25/2007 - 3/31/2007
>> P4: 4/1/2007 - 4/28/2007
>> P5: 4/29/2007 - 5/26/2007
>> P6: 5/27/2007 - 6/30/2007
>> P7: 7/1/2007 - 7/28/2007
>> P8: 7/29/2007 - 8/25/2007
>> .
>> .
>> .
>> P12: 11/25/2007 -12/29/2007
>>
>> P1 starts on 12/31/2006 because that falls on Sunday of the 1st week
>> of January/2007. P1 ends on 1/27/2007 because that's last day
>> (Saturday) of 4th week
>> and so on
>> My accounting dept folks call this fiscal period.
>> Anyone out there have done this or know how to do this type of
>> calculation using T-SQL.
>> I appreciate so much for any help.
>> Thanh
>>
>> See DATEADD(dd, ... ) and DATEPART(dw, ...) in BOL, but first you will
>> need an unambiguous definition for fiscal period.
>
I would like to be able to take in a year and figure out its 12 month period
based on Sunday as starting day of the 1st week of that month and each period
has 4 weeks.
For example:
Input: 2007
Output:
P1: 12/31/2006 - 1/27/2007
P2: 1/28/2007 - 2/24/2007
P3: 2/25/2007 - 3/31/2007
P4: 4/1/2007 - 4/28/2007
P5: 4/29/2007 - 5/26/2007
P6: 5/27/2007 - 6/30/2007
P7: 7/1/2007 - 7/28/2007
P8: 7/29/2007 - 8/25/2007
.
.
.
P12: 11/25/2007 -12/29/2007
P1 starts on 12/31/2006 because that falls on Sunday of the 1st week of
January/2007. P1 ends on 1/27/2007 because that's last day (Saturday) of 4th
week
and so on
My accounting dept folks call this fiscal period.
Anyone out there have done this or know how to do this type of calculation
using T-SQL.
I appreciate so much for any help.
Thanh=?Utf-8?B?VGhhbmggTmd1eWVu?= <ThanhNguyen@.discussions.microsoft.com>
wrote in news:ABAF7E37-0534-42EB-95A6-23E95963D54A@.microsoft.com:
> Hi Experts,
> I would like to be able to take in a year and figure out its 12 month
> period based on Sunday as starting day of the 1st week of that month
> and each period has 4 weeks.
> For example:
> Input: 2007
> Output:
> P1: 12/31/2006 - 1/27/2007
> P2: 1/28/2007 - 2/24/2007
> P3: 2/25/2007 - 3/31/2007
> P4: 4/1/2007 - 4/28/2007
> P5: 4/29/2007 - 5/26/2007
> P6: 5/27/2007 - 6/30/2007
> P7: 7/1/2007 - 7/28/2007
> P8: 7/29/2007 - 8/25/2007
> .
> .
> .
> P12: 11/25/2007 -12/29/2007
>
> P1 starts on 12/31/2006 because that falls on Sunday of the 1st week
> of January/2007. P1 ends on 1/27/2007 because that's last day
> (Saturday) of 4th week
> and so on
> My accounting dept folks call this fiscal period.
> Anyone out there have done this or know how to do this type of
> calculation using T-SQL.
> I appreciate so much for any help.
> Thanh
>
See DATEADD(dd, ... ) and DATEPART(dw, ...) in BOL, but first you will
need an unambiguous definition for fiscal period.|||Below is one method. I suggest you materialize these results in a fiscal
period table.
DECLARE @.first_of_year datetime
SET @.first_of_year = '20070101'
SELECT
DATEADD(day, (DATEPART(dw, DATEADD(month, month_number - 1,
@.first_of_year)) - 1) * -1,
DATEADD(month, month_number - 1, @.first_of_year)) AS PeriodStart,
DATEADD(day, (DATEPART(dw, DATEADD(month, month_number,
@.first_of_year))) * -1,
DATEADD(month, month_number, @.first_of_year)) AS PeriodEnd
FROM (SELECT 1 AS month_number UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 AS month_number UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 AS month_number UNION ALL SELECT 8 UNION ALL SELECT 9
UNION ALL SELECT 10 AS month_number UNION ALL SELECT 11 UNION ALL SELECT
12)
AS month_numbers
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris.Cheney" <Chris.CheneyXXNOSPAMXX@.tesco.net> wrote in message
news:Xns9924696609362ChrisCheneytesconet@.80.5.182.99...
> =?Utf-8?B?VGhhbmggTmd1eWVu?= <ThanhNguyen@.discussions.microsoft.com>
> wrote in news:ABAF7E37-0534-42EB-95A6-23E95963D54A@.microsoft.com:
>> Hi Experts,
>> I would like to be able to take in a year and figure out its 12 month
>> period based on Sunday as starting day of the 1st week of that month
>> and each period has 4 weeks.
>> For example:
>> Input: 2007
>> Output:
>> P1: 12/31/2006 - 1/27/2007
>> P2: 1/28/2007 - 2/24/2007
>> P3: 2/25/2007 - 3/31/2007
>> P4: 4/1/2007 - 4/28/2007
>> P5: 4/29/2007 - 5/26/2007
>> P6: 5/27/2007 - 6/30/2007
>> P7: 7/1/2007 - 7/28/2007
>> P8: 7/29/2007 - 8/25/2007
>> .
>> .
>> .
>> P12: 11/25/2007 -12/29/2007
>>
>> P1 starts on 12/31/2006 because that falls on Sunday of the 1st week
>> of January/2007. P1 ends on 1/27/2007 because that's last day
>> (Saturday) of 4th week
>> and so on
>> My accounting dept folks call this fiscal period.
>> Anyone out there have done this or know how to do this type of
>> calculation using T-SQL.
>> I appreciate so much for any help.
>> Thanh
> See DATEADD(dd, ... ) and DATEPART(dw, ...) in BOL, but first you will
> need an unambiguous definition for fiscal period.|||Thank you very much, Dan.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:3CFD7275-D58D-4D46-B5CB-DA1F3BBEC96A@.microsoft.com...
> Below is one method. I suggest you materialize these results in a fiscal
> period table.
> DECLARE @.first_of_year datetime
> SET @.first_of_year = '20070101'
> SELECT
> DATEADD(day, (DATEPART(dw, DATEADD(month, month_number - 1,
> @.first_of_year)) - 1) * -1,
> DATEADD(month, month_number - 1, @.first_of_year)) AS PeriodStart,
> DATEADD(day, (DATEPART(dw, DATEADD(month, month_number,
> @.first_of_year))) * -1,
> DATEADD(month, month_number, @.first_of_year)) AS PeriodEnd
> FROM (SELECT 1 AS month_number UNION ALL SELECT 2 UNION ALL SELECT 3
> UNION ALL SELECT 4 AS month_number UNION ALL SELECT 5 UNION ALL SELECT
> 6
> UNION ALL SELECT 7 AS month_number UNION ALL SELECT 8 UNION ALL SELECT
> 9
> UNION ALL SELECT 10 AS month_number UNION ALL SELECT 11 UNION ALL
> SELECT 12)
> AS month_numbers
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Chris.Cheney" <Chris.CheneyXXNOSPAMXX@.tesco.net> wrote in message
> news:Xns9924696609362ChrisCheneytesconet@.80.5.182.99...
>> =?Utf-8?B?VGhhbmggTmd1eWVu?= <ThanhNguyen@.discussions.microsoft.com>
>> wrote in news:ABAF7E37-0534-42EB-95A6-23E95963D54A@.microsoft.com:
>> Hi Experts,
>> I would like to be able to take in a year and figure out its 12 month
>> period based on Sunday as starting day of the 1st week of that month
>> and each period has 4 weeks.
>> For example:
>> Input: 2007
>> Output:
>> P1: 12/31/2006 - 1/27/2007
>> P2: 1/28/2007 - 2/24/2007
>> P3: 2/25/2007 - 3/31/2007
>> P4: 4/1/2007 - 4/28/2007
>> P5: 4/29/2007 - 5/26/2007
>> P6: 5/27/2007 - 6/30/2007
>> P7: 7/1/2007 - 7/28/2007
>> P8: 7/29/2007 - 8/25/2007
>> .
>> .
>> .
>> P12: 11/25/2007 -12/29/2007
>>
>> P1 starts on 12/31/2006 because that falls on Sunday of the 1st week
>> of January/2007. P1 ends on 1/27/2007 because that's last day
>> (Saturday) of 4th week
>> and so on
>> My accounting dept folks call this fiscal period.
>> Anyone out there have done this or know how to do this type of
>> calculation using T-SQL.
>> I appreciate so much for any help.
>> Thanh
>>
>> See DATEADD(dd, ... ) and DATEPART(dw, ...) in BOL, but first you will
>> need an unambiguous definition for fiscal period.
>
Calculate fiscal periods (T-SQL)
Hi Experts,
I would like to be able to take in a year and figure out its 12 month period
based on Sunday as starting day of the 1st week of that month and each period
has 4 weeks.
For example:
Input: 2007
Output:
P1: 12/31/2006 - 1/27/2007
P2: 1/28/2007 - 2/24/2007
P3: 2/25/2007 - 3/31/2007
P4: 4/1/2007 - 4/28/2007
P5: 4/29/2007 - 5/26/2007
P6: 5/27/2007 - 6/30/2007
P7: 7/1/2007 - 7/28/2007
P8: 7/29/2007 - 8/25/2007
..
..
..
P12: 11/25/2007 -12/29/2007
P1 starts on 12/31/2006 because that falls on Sunday of the 1st week of
January/2007. P1 ends on 1/27/2007 because that's last day (Saturday) of 4th
week
and so on
My accounting dept folks call this fiscal period.
Anyone out there have done this or know how to do this type of calculation
using T-SQL.
I appreciate so much for any help.
Thanh
=?Utf-8?B?VGhhbmggTmd1eWVu?= <ThanhNguyen@.discussions.microsoft.com>
wrote in news:ABAF7E37-0534-42EB-95A6-23E95963D54A@.microsoft.com:
> Hi Experts,
> I would like to be able to take in a year and figure out its 12 month
> period based on Sunday as starting day of the 1st week of that month
> and each period has 4 weeks.
> For example:
> Input: 2007
> Output:
> P1: 12/31/2006 - 1/27/2007
> P2: 1/28/2007 - 2/24/2007
> P3: 2/25/2007 - 3/31/2007
> P4: 4/1/2007 - 4/28/2007
> P5: 4/29/2007 - 5/26/2007
> P6: 5/27/2007 - 6/30/2007
> P7: 7/1/2007 - 7/28/2007
> P8: 7/29/2007 - 8/25/2007
> .
> .
> .
> P12: 11/25/2007 -12/29/2007
>
> P1 starts on 12/31/2006 because that falls on Sunday of the 1st week
> of January/2007. P1 ends on 1/27/2007 because that's last day
> (Saturday) of 4th week
> and so on
> My accounting dept folks call this fiscal period.
> Anyone out there have done this or know how to do this type of
> calculation using T-SQL.
> I appreciate so much for any help.
> Thanh
>
See DATEADD(dd, ... ) and DATEPART(dw, ...) in BOL, but first you will
need an unambiguous definition for fiscal period.
|||Below is one method. I suggest you materialize these results in a fiscal
period table.
DECLARE @.first_of_year datetime
SET @.first_of_year = '20070101'
SELECT
DATEADD(day, (DATEPART(dw, DATEADD(month, month_number - 1,
@.first_of_year)) - 1) * -1,
DATEADD(month, month_number - 1, @.first_of_year)) AS PeriodStart,
DATEADD(day, (DATEPART(dw, DATEADD(month, month_number,
@.first_of_year))) * -1,
DATEADD(month, month_number, @.first_of_year)) AS PeriodEnd
FROM (SELECT 1 AS month_number UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 AS month_number UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 AS month_number UNION ALL SELECT 8 UNION ALL SELECT 9
UNION ALL SELECT 10 AS month_number UNION ALL SELECT 11 UNION ALL SELECT
12)
AS month_numbers
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris.Cheney" <Chris.CheneyXXNOSPAMXX@.tesco.net> wrote in message
news:Xns9924696609362ChrisCheneytesconet@.80.5.182. 99...
> =?Utf-8?B?VGhhbmggTmd1eWVu?= <ThanhNguyen@.discussions.microsoft.com>
> wrote in news:ABAF7E37-0534-42EB-95A6-23E95963D54A@.microsoft.com:
>
> See DATEADD(dd, ... ) and DATEPART(dw, ...) in BOL, but first you will
> need an unambiguous definition for fiscal period.
|||Thank you very much, Dan.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:3CFD7275-D58D-4D46-B5CB-DA1F3BBEC96A@.microsoft.com...
> Below is one method. I suggest you materialize these results in a fiscal
> period table.
> DECLARE @.first_of_year datetime
> SET @.first_of_year = '20070101'
> SELECT
> DATEADD(day, (DATEPART(dw, DATEADD(month, month_number - 1,
> @.first_of_year)) - 1) * -1,
> DATEADD(month, month_number - 1, @.first_of_year)) AS PeriodStart,
> DATEADD(day, (DATEPART(dw, DATEADD(month, month_number,
> @.first_of_year))) * -1,
> DATEADD(month, month_number, @.first_of_year)) AS PeriodEnd
> FROM (SELECT 1 AS month_number UNION ALL SELECT 2 UNION ALL SELECT 3
> UNION ALL SELECT 4 AS month_number UNION ALL SELECT 5 UNION ALL SELECT
> 6
> UNION ALL SELECT 7 AS month_number UNION ALL SELECT 8 UNION ALL SELECT
> 9
> UNION ALL SELECT 10 AS month_number UNION ALL SELECT 11 UNION ALL
> SELECT 12)
> AS month_numbers
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Chris.Cheney" <Chris.CheneyXXNOSPAMXX@.tesco.net> wrote in message
> news:Xns9924696609362ChrisCheneytesconet@.80.5.182. 99...
>
sql
I would like to be able to take in a year and figure out its 12 month period
based on Sunday as starting day of the 1st week of that month and each period
has 4 weeks.
For example:
Input: 2007
Output:
P1: 12/31/2006 - 1/27/2007
P2: 1/28/2007 - 2/24/2007
P3: 2/25/2007 - 3/31/2007
P4: 4/1/2007 - 4/28/2007
P5: 4/29/2007 - 5/26/2007
P6: 5/27/2007 - 6/30/2007
P7: 7/1/2007 - 7/28/2007
P8: 7/29/2007 - 8/25/2007
..
..
..
P12: 11/25/2007 -12/29/2007
P1 starts on 12/31/2006 because that falls on Sunday of the 1st week of
January/2007. P1 ends on 1/27/2007 because that's last day (Saturday) of 4th
week
and so on
My accounting dept folks call this fiscal period.
Anyone out there have done this or know how to do this type of calculation
using T-SQL.
I appreciate so much for any help.
Thanh
=?Utf-8?B?VGhhbmggTmd1eWVu?= <ThanhNguyen@.discussions.microsoft.com>
wrote in news:ABAF7E37-0534-42EB-95A6-23E95963D54A@.microsoft.com:
> Hi Experts,
> I would like to be able to take in a year and figure out its 12 month
> period based on Sunday as starting day of the 1st week of that month
> and each period has 4 weeks.
> For example:
> Input: 2007
> Output:
> P1: 12/31/2006 - 1/27/2007
> P2: 1/28/2007 - 2/24/2007
> P3: 2/25/2007 - 3/31/2007
> P4: 4/1/2007 - 4/28/2007
> P5: 4/29/2007 - 5/26/2007
> P6: 5/27/2007 - 6/30/2007
> P7: 7/1/2007 - 7/28/2007
> P8: 7/29/2007 - 8/25/2007
> .
> .
> .
> P12: 11/25/2007 -12/29/2007
>
> P1 starts on 12/31/2006 because that falls on Sunday of the 1st week
> of January/2007. P1 ends on 1/27/2007 because that's last day
> (Saturday) of 4th week
> and so on
> My accounting dept folks call this fiscal period.
> Anyone out there have done this or know how to do this type of
> calculation using T-SQL.
> I appreciate so much for any help.
> Thanh
>
See DATEADD(dd, ... ) and DATEPART(dw, ...) in BOL, but first you will
need an unambiguous definition for fiscal period.
|||Below is one method. I suggest you materialize these results in a fiscal
period table.
DECLARE @.first_of_year datetime
SET @.first_of_year = '20070101'
SELECT
DATEADD(day, (DATEPART(dw, DATEADD(month, month_number - 1,
@.first_of_year)) - 1) * -1,
DATEADD(month, month_number - 1, @.first_of_year)) AS PeriodStart,
DATEADD(day, (DATEPART(dw, DATEADD(month, month_number,
@.first_of_year))) * -1,
DATEADD(month, month_number, @.first_of_year)) AS PeriodEnd
FROM (SELECT 1 AS month_number UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 AS month_number UNION ALL SELECT 5 UNION ALL SELECT 6
UNION ALL SELECT 7 AS month_number UNION ALL SELECT 8 UNION ALL SELECT 9
UNION ALL SELECT 10 AS month_number UNION ALL SELECT 11 UNION ALL SELECT
12)
AS month_numbers
Hope this helps.
Dan Guzman
SQL Server MVP
"Chris.Cheney" <Chris.CheneyXXNOSPAMXX@.tesco.net> wrote in message
news:Xns9924696609362ChrisCheneytesconet@.80.5.182. 99...
> =?Utf-8?B?VGhhbmggTmd1eWVu?= <ThanhNguyen@.discussions.microsoft.com>
> wrote in news:ABAF7E37-0534-42EB-95A6-23E95963D54A@.microsoft.com:
>
> See DATEADD(dd, ... ) and DATEPART(dw, ...) in BOL, but first you will
> need an unambiguous definition for fiscal period.
|||Thank you very much, Dan.
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:3CFD7275-D58D-4D46-B5CB-DA1F3BBEC96A@.microsoft.com...
> Below is one method. I suggest you materialize these results in a fiscal
> period table.
> DECLARE @.first_of_year datetime
> SET @.first_of_year = '20070101'
> SELECT
> DATEADD(day, (DATEPART(dw, DATEADD(month, month_number - 1,
> @.first_of_year)) - 1) * -1,
> DATEADD(month, month_number - 1, @.first_of_year)) AS PeriodStart,
> DATEADD(day, (DATEPART(dw, DATEADD(month, month_number,
> @.first_of_year))) * -1,
> DATEADD(month, month_number, @.first_of_year)) AS PeriodEnd
> FROM (SELECT 1 AS month_number UNION ALL SELECT 2 UNION ALL SELECT 3
> UNION ALL SELECT 4 AS month_number UNION ALL SELECT 5 UNION ALL SELECT
> 6
> UNION ALL SELECT 7 AS month_number UNION ALL SELECT 8 UNION ALL SELECT
> 9
> UNION ALL SELECT 10 AS month_number UNION ALL SELECT 11 UNION ALL
> SELECT 12)
> AS month_numbers
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Chris.Cheney" <Chris.CheneyXXNOSPAMXX@.tesco.net> wrote in message
> news:Xns9924696609362ChrisCheneytesconet@.80.5.182. 99...
>
sql
Friday, February 24, 2012
c# signup form into sql database
Hi all,I'm trying to do an extremely simple task here: add information from a signup form into an SQL database. Sadly, I can't figure out how to get the values into the DB.Here's my form:
<form id="form1" runat="server">
<div>
<asp:Label ID="lblName" runat="server" Text="Your Name:"></asp:Label>
<br />
<asp:TextBox ID="txtName" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="txtName"
ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator><br />
<asp:Label ID="lblEmail" runat="server" Text="Your Email Address:"></asp:Label>
<br />
<asp:TextBox ID="txtEmail" runat="server"></asp:TextBox>
<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtEmail"
ErrorMessage="RequiredFieldValidator"></asp:RequiredFieldValidator>
<asp:RegularExpressionValidator ID="RegularExpressionValidator1" runat="server" ControlToValidate="txtEmail"
ErrorMessage="RegularExpressionValidator" ValidationExpression="\w+([-+.']\w+)*@.\w+([-.]\w+)*\.\w+([-.]\w+)*"></asp:RegularExpressionValidator><br />
<br />
<asp:Label ID="lblSubscribe" runat="server" AssociatedControlID="radSubscribe" Text="Subscribe"></asp:Label>
<asp:RadioButton ID="radSubscribe" runat="server" Checked="True" /><br />
<br />
<asp:Label ID="lblUnsubscribe" runat="server" AssociatedControlID="radUnsubscribe"
Text="Unsubscribe"></asp:Label>
<asp:RadioButton ID="radUnsubscribe" runat="server" /><br />
<br />
<asp:Button ID="btnSignup" runat="server" Text="Signup" OnClick="btnSignup_Click" /></div>
</form>
And here's my Stored procedure I've written:
create procedure sp_customerSignups
@.name varchar(50),
@.email varchar (50),
@.subscribed int
as
BEGIN
INSERT INTO tblCustomerSignups(Name, EmailAddress, Subscribed)
VALUES(@.name, @.email, @.subscribed)
END
How do I get the values from the form into the DB and forward them to a 'successful' message. Also if the user clicks the radSubscribe button I want the field in SQL to be 1 and if the user clicks radUnsubscribe the field to be 0.I'm trying to do this in C# and I feel as if my brain is about to burst with all I've learnt over the past weeks!Thanks in advance,Brett
Solved.
Subscribe to:
Posts (Atom)